oracle
1、建用户、表空间、授权=======================================================
--创建表空间
CREATE TABLESPACE TableSpaceName
NOLOGGING
DATAFILE 'path/tablespacename.dbf' SIZE 20M
autoExtend on next 10M
permanent online;
--创建用户
CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE TsinghuaLMS ACCOUNT UNLOCK;
--授权
GRANT "CONNECT" TO username;
GRANT "RESOURCE" TO username;
GRANT "DBA" TO username;
--修改用户密码
alter user 用户名 identified by 新密码；
alter user ecology account unlock;// 解锁
alter profile default limit failed_login_attempts unlimited;// 修改登录错误次数无限制
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';//查询是否无限制
--强制删除主键或unique索引
--drop index SYS_C00183607 ; 这样是无法删除的
alter table SF_WORKFLOW_WORKVIEW drop constraint SYS_C0029894;
--alter table SF_WORKFLOW_WORKVIEW add unique (PROCESSID, USERID,DEPTID);
--添加数据文件
alter tablespace TableSpaceName add datafile '路径/users02.dbf' size 2g;
alter tablespace joa4_lob add datafile '路径/users02.dbf' size 2g;
修改数据文件大小
alter database datafile 'H:\ORACLE\PRODUCT\10.1.0\ORADATA\ORACLE\USERS01.DBF' RESIZE 10240M;
监时表空间数据文件
alter tablespace temp add tempfile 'D:\APP\ADMINISTRATOR\ORADATA\SANJOA\TEMP02.DBF' size 2G autoextend on next 50m maxsize unlimited;
1.查看用户的proifle是哪个，一般是default
sql>SELECT username,PROFILE FROM dba_users;
2.查看对应的概要文件（如default）的密码有效期设置：
sql>SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
3.将概要文件(如default)的密码有效期由默认的180天修改成“无限制”：
sql>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
---修改之后不需要重启动数据库，会立即生效。
update SF_WORKFLOW_WORKITEM t set t.createdate=to_date('2021-01-03 17:18','yyyy-MM-dd hh24:mi'),t.completedate=to_date('2021-01-05 08:40','yyyy-MM-dd hh24:mi') where t.id='ef59154e9c0149658da599d06bb20ed0';
--删除用户
drop user "JOA4" CASCADE;
2、修改字符集==============================
（查看字符集select userenv('language') from dual;）
d:\>sqlplus /nolog
SQL>conn sys/sys@sanjoa as sysdba;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT EXCLUSIVE;
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE OPEN;
SQL>ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE UTF8;
SQL>ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
SQL>SHUTDOWN immediate;
SQL>startup;
注：启动数据库到mount状态，startup mount，发生报错：ora-12514 ,参考下面的解决办法
注：如果改完字符集出现exp错误6552，则解决如下：
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
COL VALUE NEW_VALUE CHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
COL VALUE NEW_VALUE NCHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
--INTERNAL_USE是没有写在文档中的参数，用以强制完成字符集一致化
ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
SHUTDOWN IMMEDIATE;
STARTUP;
3、ora-12514================================
Oracle错误 ora-12514 解决方法----重写listener.ora文件(蓝色为增加部分,ORACLE_HOME参考上面的写)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = f:\oracle\product\10.1.0\Db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = f:\oracle\product\10.1.0\Db_1)
(SID_NAME = orcl)
)
)
解决oracle报 ORA-12560错误，只有服务器重启恢复正常的问题
问题：检查各种服务和设置都显示正常，但就是不能访问数据库，报“ORA-12560: TNS: 协议适配器错误”。
原因：会话连接数过多导致。
解决办法：修改（增大）oracle的会话连接数（默认是150）
4、修改optimizer_mode====================================
修改optimizer_mode
C:\Users\Administrator>sqlplus /nolog
SQL> conn sys/system@orcl11 as sysdba
已连接。
SQL> show parameter optimizer_mode;
NAME TYPE VALUE
------------------------------------ ----------- ---------------------
optimizer_mode string ALL_ROWS
SQL> alter system set optimizer_mode=first_rows;
系统已更改。
5、分析表和索引（当有时候不走索引时可以执行）===================================================================
analyze table tablename compute statistics;
analyze index indexname compute statistics;
6、修改sessions ===================================================================
修改session和连接最大数(必须重启oracle服务器才能生效sessions=(1.1*process+5))
SQL> show parameter processes
SQL> show parameter sessions
SQL> alter system set processes=300 scope=spfile;
SQL> alter system set sessions=335 scope=spfile;
查询数据库当前进程的连接数：
select count(*) from v$process;
查看数据库当前会话的连接数：
select count(*) from v$session;
查看数据库的并发连接数：
select count(*) from v$session where status='ACTIVE';
查看当前数据库建立的会话情况：
select sid,serial#,username,program,machine,status from v$session;
查询数据库允许的最大连接数：
select value from v$parameter where name = 'processes';
或者：show parameter processes;
重启数据库：
shutdown immediate;
startup;
7、数据库起不来====================================
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
解决办法：（强行起动服务实例）
D:\oracle\product\10.2.0\db_1\database看日志
D:\>D:\oracle\product\10.2.0\db_1\bin\oradim.exe -startup -sid orcl -usrpwd * -log oradim.log -nocheck 0
8、移动表空间数据文件 启动时出现ORA-01157和ORA-01110问题================
1>关闭数据库>剪切数据文件到想到的目录
d:\>sqlplus /nolog
SQL>conn sys/sys@sanjoa as sysdba;
SQL> startup mount;
SQL> alter database open;
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 12: 'c:/app/administrator/oracle11.01/oradata/orcldata/joa3'
SQL> archive log list; 查看是否为归档模式
归档模式下
SQL> alter database datafile '/oradata/orcldata/sacproduce02.dbf' offline
非归档模式下
SQL> alter database datafile '/oradata/orcldata/sacproduce02.dbf'offline drop
SQL> alter database rename file 'c:/app/administrator/oracle11.01/oradata/orcldata/joa3' to 'd:/app/administrator/oracle11.01/oradata/orcldata/joa3';
SQL> alter database datafile 'd:/app/administrator/oracle11.01/oradata/orcldata/joa3' online;
ORA-01113: file 12 needs media recovery
ORA-01110: data file 12: '/oradata/orcldata/sacproduce02.dbf'
SQL> recover datafile 'd:/app/administrator/oracle11.01/oradata/orcldata/joa3'
SQL>alter database datafile 'd:/app/administrator/oracle11.01/oradata/orcldata/joa3' online;
SQL> alter database open;
完成!
8.2、temp表空间清理=================================
C:\Users\Administrator>sqlplus /nolog
SQL> conn sys /sys@sanjoa as sysdba;
已连接。
SQL> create temporary tablespace temp02 tempfile 'E:\oracle\product\10.2.0\oradata\sanjoa\temp02_1.dbf' size 512M;
表空间已创建。
SQL> alter database default temporary tablespace temp02;
数据库已更改。
SQL> drop tablespace temp including contents and datafiles;
表空间已删除。
SQL> create temporary tablespace temp tempfile 'E:\oracle\product\10.2.0\oradata\sanjoa\temp01.dbf' size 512M autoextend on maxsize 30G;
表空间已创建。
SQL> alter database default temporary tablespace temp;
数据库已更改。
SQL> drop tablespace temp02 including contents and datafiles;
表空间已删除。
SQL>
9、LogMiner的使用======================================
alter system set utl_file_dir='D:\oracle\product\10.2.0\logs' SCOPE=SPFILE;
startup force;
1、创建数据字典文件（data-dictionary）
execute dbms_logmnr_d.build(dictionary_filename => 'dict.ora',dictionary_location => 'D:/oracle/product/10.2.0/logs');
2、创建要分析的日志文件列表
execute dbms_logmnr.add_logfile(LogFileName => 'D:/oracle/product/10.2.0/oradata/orcl10/redo01.log',Options => dbms_logmnr.new);
execute dbms_logmnr.add_logfile(LogFileName => 'D:/oracle/product/10.2.0/oradata/orcl10/redo02.log',Options => dbms_logmnr.ADDFILE);
execute dbms_logmnr.add_logfile(LogFileName => 'D:/oracle/product/10.2.0/oradata/orcl10/redo03.log',Options => dbms_logmnr.ADDFILE);
3、使用LogMiner进行日志分析(具体要查询什么内容可以自己修改)
（1）无限制条件
execute dbms_logmnr.start_logmnr(dictfilename=>'D:\oracle\product\10.2.0\logs\dict.ora');
（2）有限制条件
execute dbms_logmnr.start_logmnr(
DictFileName => 'D:\oracle\product\10.2.0\logs\dict.ora',
StartTime => to_date('2007-9-18 00:00:00','YYYY-MM-DD HH24:MI:SS')
EndTime => to_date(''2007-9-18 23:59:59','YYYY-MM-DD HH24:MI:SS '));
4、观察分析结果（v$logmnr_contents）
SELECT sql_redo,TIMESTAMP FROM v$logmnr_contents WHERE sql_redo like'delete%' and username='joa4' AND TABLE_NAME='SS_M_ATTACHMENT';
5、 结束LogMiner会话
EXECUTE DBMS_LOGMNR.END_LOGMNR;
10、Oracle备份数据导入到其他的表空间(只设置默认表空间无效)=======================
(revoke方式不支持BLOB 建议以下方案)
1、exp USER/PASSOWRD@SID file=c:\test.dmp ROWS=N
导出表结构和数据库其他对象
2、用UE打开test.dmp（切换到16进制编辑模式）文件替换旧的表空间为目标表空间如TABLESPACE "USERS" 换成 TABLESPACE "JOA4"。
3、IMP USER/PASSOWRD@SID file=c:\test.dmp full=y，导入表结构和其他对象
4、exp USER/PASSOWRD@SID file=c:\data.dmp 导出数据
5、IMP USER/PASSOWRD@SID file=c:\data.dmp full=y ignore=y
执行以上5步，实现了表空间的转换，大字段表也能轻松导入。
11、expdp, impdp 两边的数据库服务器也需要操作 )==================
动作，管理员登陆 sqlplus，建立目录 --创建dump文件目录 select * from dba_directories;（查看目录）
>sqlplus /nolog >conn sys/sys@joa4 as sysdba;
>create directory dmp_test as 'D:\dmp'
--授权读写权限给你要操作的用户
>grant read, write on directory dmp_test to joa4
--oracle 10 查询一下版本，假如结果是 “10.2.0.1.0”
>SELECT * FROM V$VERSION;
cmd下导出，导出文件我们为 2012
expdp name/pwd@orcl schemas=joa4new directory=dmp_test dumpfile=2012.dmp logfile=2012.log version=10.2.0.1.0
如果只是要导出某些表，可以使用include
expdp name/pwd@orcl schemas=joa4new directory=dmp_test dumpfile=2012.dmp logfile=2012.log version=10.2.0.1.0 include=table:\"like \'CT%\'\"
把导出到d:\dmp的dmp跟log文件，压缩再传到 oracle 10 服务器的 d:\dmp ，cmd下导入
impdp name/pwd schemas=joa4 dumpfile=2012.dmp directory=dmp_test
这个schemas 指的是source schema。
--导入到另一个用户使用remap_schema，joa4new
impdp name/pwd@orcl10 remap_schema=old_schema_name:new_schema_name dumpfile=2012.dmp directory=dmp_test
pause
注意用户名的大小写，一般是大写
13、ORACLE11G 导出空表报EXP-00011：table不存在=============
原因：11g默认创建一个表时不分配segment，只有在插入数据时才会产生（当然也可以强制分配），以节省磁盘空间。
1>用以下这句查找空表：
--还是用下面的语句select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;
select 'alter table '||table_name||' allocate extent;' from user_tables t where t.segment_created='NO';
2>把查询结果导出，执行导出的语句；
3>再导出就可以了。
注：对之后建的表默认分配segment，需要修改alter system set deferred_segment_creation=false;
14、exp导出当天数据===================================
exp joa4_lob/joa4@sansoftXE file=d:\%date:~0,4%%date:~5,2%%date:~8,2%.dmp tables=(sf_attachment_lob) query=\"WHERE createdate<to_date(to_char(sysdate,'yymmdd') || '23:59:59','yymmdd HH24:MI:SS') and createdate>to_date(to_char(sysdate,'yymmdd'),'yymmdd')\"
附件表加日期，用于按天备份EXP
导出某个用户的数据OWNER=joa4
导入 IMP joa4/joa4@orcl BUFFER=10240000 FILE=C:\SONIC.DMP FROMUSER=joa4 TOUSER=joa4_hn
导入某些表IMP SONIC/SONIC BUFFER=10240000 FILE=C:\SONIC.DMP OWNER=SONIC TABLES=(SONIC,table2)
imp joa3_jsjjyjy/joa3_jsjjyjy@orcl11 ignore=y file=20190729.dmp tables=(saneis_org_user)
默认buffer4096字节
一般看bai物理机器du内存大小
buffer是指数据行的缓zhi冲区大小，默认值dao根据系统而定，通常zhuan应设置为高值，exp的buffer最好〉64000，imp的buffer最好>;100000,1024000是一个比较好的值，这也是别人的经验。回滚端的问题主要出现在commit选项，大数据量的exp和 imp中commit=y可以避免回滚段益出。
15、distinct的执行效率和用group by的效率================
小数据量distinct 稍稍快点
稍大的数据量group by 稍稍快点
这2个东西用在不同的需求场合，没有明确的快和慢，和你的表结构还有sql也有关系的
16、归档模式的打开及关闭 ========================
>sqlplus "sys/sys as sysdba"
SQL> shutdown immediate;
SQL> startup mount;
SQL>alter database archivelog; -- alter database noarchivelog;关闭
SQL> alter database open;
alter system archive log start;
SQL> archive log list;--查看 归档情况
更改归档路径
alter system set log_archive_dest_1='location=D:\oracle\archlog';
show parameter log_archive_format
更改归档文件名
alter system set log_archive_format='BOP%t_%s_%r.dbf' scope=spfile;
切换归档
alter system switch logfile;
select * from v$flash_recovery_area_usage;--查看使用情况
SQL> alter system set db_recovery_file_dest_size=21474836480;－－－设置日志使用空间大小（20*1024*1024*1024）,20GB
System altered
SQL> show parameter db_recovery_file_dest;－－－查看归档日志路径限额
防止Oracle归档日志满后数据库无法正常运行
归档日志满后错误ora-00257
每次rman备份完成后执行delete noprompt archivelog all backed up 1 times to device type disk;意思就是删除已经备份过的归档日志。这样可以防止归档日志累计起来的容量过大。
进入rman程序删除归档日志
rman target sys/pass@prjdb
crosscheck archivelog all;
delete archivelog until time 'sysdate'; --删除所有日志
delete expired archivelog all;--删除过期日志
delete archivelog all completed before 'sysdate-7';
说明
SYSDATA-7，表明当前的系统时间7天前，before关键字表示在7天前的归档日志，如果使用了闪回功能，也会删除闪回的数据。
同样道理，也可以删除从7天前到现在的全部日志，不过这个命令要考虑清楚，做完这个删除，最好马上进行全备份数据库
delete/list archivelog until time 'sysdate-1'; 此时是按照归档日志开始时间即fisrt_time作为截止时间
delete/list archivelog all completed before 'sysdate-1' 是按照归档日志完成时间即completion_time作为截止
17、服务器意外关机，oracle没启来
ORA-00600
SQL> shutdown immediate;
SQL> startup mount
SQL> recover database;
完成介质恢复。
SQL> alter database open;
数据库已更改。
ORA-28547 连接服务器失败，可能是Oracle Net 管理错误
解决办法：listener.ora 文件中 (PROGRAM = extproc)
把这句话注释掉#重启监听和service服务就好了
18、监听器日志 listener.log文件过大，oracle连接非常慢，假死，注意alert.log
For oracle 9i/10g在下面的目录下：
$ORACLE_HOME/network/log/listener_$ORACLE_SID.log
For oracle 11g/12c在下面的目录下：
$ORACLE_BASE/diag/tnslsnr/主机名称/listener/trace/listener.log
1.删除...oracle\product\10.2.0\db_1\NETWORK\log下listener.log(或重命名以备份)，重启监听器
cmd下
>rename listener.log listener.old
>lsnrctl stop
>lsnrctl start
2.上一种方法会中断数据库的使用，为了最小化数据库中断时间，采用先关闭再开启listener日志功能的方法
>lsnrctl set log_status off
>rename listener.log listener.old
>lsnrctl set log_status on
使用以上两种方法中的任意一种之后，listener.log都被重新创建了
19、ORA-16038: 日志 2 序列号 180 无法归档
C:\Documents and Settings\Administrator.SERVER-2.004>sqlplus/nolog
SQL> conn sys/12345678 as sysdba
已连接。
SQL> shutdown immediate;
SQL> startup;
ORACLE 例程已经启动。
...
数据库装载完毕。
ORA-16038: 日志 2 序列号 48 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 2 线程 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\JC\REDO02.LOG'
SQL> select group#,sequence#,archived,status from v$log;
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 50 NO CURRENT
3 49 NO INACTIVE
2 48 NO INACTIVE
SQL> alter database clear unarchived logfile group 3;
数据库已更改。
SQL> alter database open;
数据库已更改。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup;
ORACLE 例程已经启动。
...
数据库装载完毕。
数据库已经打开。
浙公网安备 33010602011771号