oracle常用笔记总结.docx

1、oracle常用笔记总结1、用dba权限的用户查看数据库都有哪些锁select t2.username,t2.sid,t2.serial#,t2.logon_timefrom v$locked_object t1,v$session t2where t1.session_id=t2.sid order by t2.logon_time;2、查看有哪些对象被锁了select b.owner,b.object_name,a.session_id,a.locked_modefrom v$locked_object a,dba_objects bwhere b.object_id = a.object

2、_id3、根据sid查找进程idselect pro.spid from v$session ses,v$process pro where ses.sid=129 and ses.paddr=pro.addr;select ses.sid from v$session ses,v$process pro where pro.spid=129 and ses.paddr=pro.addr;4：根据这个spid对应linux下的进程pidlinux命令： ps -ef | gre | ora (找到所有ora开头的进程) kill -9 (pid)5、解决delete后表的高水位的问题初步判断应

3、该是之前有大量数据，后来delete了，选成高水位，用shrink清除高水位：alter table t_mobile_client_tp_prov enable row movement;alter table t_mobile_client_tp_prov shrink space;重新收集统计信息：begin dbms_stats.gather_table_stats(ownname=traffic,tabname=T_MOBILE_CLIENT_TP_PROV);end;6、ORA-02064 distributed operation not supportedCause:One o

4、f the following unsupported operations was attempted:Array execute of a remote update with a subquery that references a database link, orAn update of a long column with bind variable and an update of a second column with a subquery that both references a database link and a bind variable, orA commit

5、 is issued in a coordinated session from an RPC with OUT parameters.Action:Simplify the remote update statement.问题: 数据库A ,B 通过DBlink互相访问, 数据库A 调用数据库B的存储过程pro_b , pro_b 过程中有out 输出参数,并且里面有dml语句,之后commit ,或rollback. 这时数据库A 通过DBlink 的调用pro_bB就会产生这个错误.解决办法:a. 去掉pro_b中的commit, rollback ,统一有数据库B来控制事务.(当然也可

6、以去掉out参数,这样也破坏是事务的完整性)b.用自制事务来控制pro_b , 在pro_b中添加.PRAGMA AUTONOMOUS_TRANSACTION; 优点:不用去修改调用的所有方法.当然这样也不能控制事务的完整.7、建表开启并行 v_table_sql := CREATE TABLE T_MOBILE_CAP_DETA82_|in_date | parallel (degree 4) as | select /*+parallel(t1 4) */ * from T_MOBILE_CAP_DETAIL_|in_date|QAS82 t1; EXECUTE IMMEDIATE v_t

7、able_sql;8、创建dblinkCREATE DATABASE LINK QAS65.REGRESS.RDBMS.DEV.US.ORACLE.COMCONNECT TO TRAFFICIDENTIFIED BY pwdUSING (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.228.65)(PORT=1521)(CONNECT_DATA=(SID=qas)9、创建临时表 a.create global temporary table temp_tbl(col_a varchar2(30) on commit

8、 preserve rows（当提交后保留数据，session结束时才清除数据） b.create global temporary table temp_tbl(col_a varchar2(30) on commit delete rows （当提交后不保留数据，session结束时也清除数据）10、drop后的表被放在回收站(user_recyclebin)里，而不是直接删除掉。这样，回收站里的表信息就可以被恢复，或彻底清除。 a.通过查询回收站user_recyclebin获取被删除的表信息，然后使用语句 flashback table to before drop rename to

9、 ; 将回收站里的表恢复为原名称或指定新名称，表中数据不会丢失。 若要彻底删除表，则使用语句：drop table purge; b.清除回收站里的信息 清除指定表：purge table ; 清除当前用户的回收站：purge recyclebin; 清除所有用户的回收站：purge dba_recyclebin; 11、REGEXP_SUBSTR函数格式如下：function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)_srcstr ：需要进行正则处理的字符串_pattern ：进行匹配的正则表达式_posit

10、ion ：起始位置，从第几个字符开始正则表达式匹配（默认为1）_occurrence ：标识第几个匹配组，默认为1_modifier ：模式（i不区分大小写进行检索；c区分大小写进行检索。默认为c。）例：select regexp_substr(call_logs,d*,instr(call_logs,duration:)+12,1,i),call_logs from t_mobile_callogs_sdk_20130519 where upper(client_type) like %C8812%12、alter table T_MON_APP_COOP modify mark varch

11、ar(10)13、alter table T_MOBILE_CAP_USE_20130822 drop column is_run14、ORACLE利用TNS控制远程访问的IP地址 利用TNS可以实现IP地址的过滤。如下：在D:oracleora92networkadmin的sqlnet.ora文件中添加你需要排除或者允许访问的地址按照规定格式TCP.VALIDNODE_CHECKING = YES 该参数表明会对连接进行验证TCP.EXCLUDED_NODES= (10.83.25.44) 该参数表明排除该地址的访问TCP.INVITED_NODES= (10.83.25.125) 该参数表

12、明允许该地址的访问如果有多个地址怎么搞？很简单，全部都搞就好了。TCP.INVITED_NODES= (10.83.25.125, 10.83.25.1)中间用逗号隔开。首先查看当前使用的数据库实例:select name from V$database;切换两个数据库实例在sqlplus里connect username/passWordsid或host set ORACLE_SID=sid或cmd里set ORACLE_SID=sid或修改注册表里ORACLE_SID项更改oracle 密码 ALTER USER TRAFFIC IDENTIFIED BY TRAFFIC209;密码过期解

13、决办法1. 查看用户的profile设置：SELECT username,profile FROM dba_users; 2. 查看系统profiles中PASSWORD_LIFE_TIME设置：SELECT * FROM dba_profiles s WHERE s.profile=DEFAULT AND resource_name=PASSWORD_LIFE_TIME; 3. 修改DBA_PROFILES中PASSWORD_LIFE_TIM的设置，改为ULIMITED ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; 4.

14、已经被报告了密码快要过期的账户必须再改一次密码（需要DBA权限）以system用户为例alter user system identified by root; job执行、删除exec dbms_job.run(4);exec dbms_job.remove(4);commit;恢复delete的数据select * from T_MOBILE_REPORT_LOG as of timestamp to_timestamp(20131220 9:40:00,yyyy-mm-dd hh24:mi:ss)where error_info like %ORA% order by op_date d

15、esc其中时间点只要在删除之前就行了ALTER SYSTEM SET db_block_buffers=1677721 SCOPE=BOTH;生成awr报告/home/oracle/product/10.2.0/rdbms/admin/awrrpt.sql;/home/oracle/product/11.2.0/rdbms/admin/awrrpt.sql;/home/oracle/11g/oracle/product/11.2.0/dbhome_1/rdbms/admin/awrrpt.sql;oracle : oinstall asmdba dba oper警告日志目录select * f

16、rom v$diag_info;oracle 中 NLSSORT函数的用法NLSSORT()，用来进行语言排序拼音SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,NLS_SORT = SCHINESE_PINYIN_M)笔划SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,NLS_SORT = SCHINESE_STROKE_M)部首SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,NLS_SORT = SCHINESE_RADICAL_M)plsq求余if mod(to_number(su

17、bstr(in_date,7,2),2)=0 thenend ifselect * from dba_objects a,v$locked_object b where a.object_id = b.object_id;ORACLE里锁有以下几种模式:0：none1：null 空2：Row-S 行共享(RS)：共享表锁，sub share 3：Row-X 行独占(RX)：用于行的修改，sub exclusive 4：Share 共享锁(S)：阻止其他DML操作，share5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive 6：exclusive

18、 独占(X)：独立访问使用，exclusiveselect object_name,machine,s.sid,s.serial# from v$locked_object l,dba_objects o ,v$session s where l.object_id = o.object_id and l.session_id=s.sid;ps -ef|grep LOCAL=NO|grep -v grep|cut -c 9-15|xargs kill -9dbms_space_admin.segment_drop_corruptexec dbms_space_admin.segment_cor

19、rupt(TRAFFIC,153,977835);exec dbms_space_admin.segment_drop_corrupt(TRAFFIC,153,977835);exec dbms_space_admin.tablespace_verify (TRAFFIC);select exec dbms_space_admin.segment_corrupt(TRAFFIC,|HEADER_FILE|,|HEADER_BLOCK|); FROMdba_segments where segment_type like TEMP% and tablespace_name=TRAFFIC让数据文

20、件脱机alter database datafile /oradata/qas/traffic23.dbf offline drop;alter database datafile /oradata/qas/system02.dbf offline drop;系统全局区SGA： SGA包含的组件：共享池（shared pool）；数据库缓冲区高速缓存（database buffer cache）；日志缓冲区（redo buffer cache）；大池；Java池；流池等。 SGA相关视图： v$sga：V$SGA这个视图包括了SGA的的总体情况，只包含两个字段：name（SGA内存区名字）和v

21、alue（内存区的值，单位为字节）。它的结果和show sga的结果一致。 v$sgastat：10g之前用于查看各SGA组件大小。V$SGAINFO的作用基本和V$SGA一样，只不过把Variable size的部分更细化了一步 v$sgainfo：10g及10g之后才有的。用于查看SGA组件大小更简便。 v$sga_dynamic_components：这个视图记录了SGA各个动态内存区的情况，它的统计信息是基于已经完成了的，针对SGA动态内存区大小调整的操作。 v$sga_dynamic_free_memory：这个视图只有一个字段就是用来表示SGA当前可以用于调整各个组件的剩余大小。

22、v$sga_target_advice：该视图可用于建议SGA大小设置是否合理。 SELECT a.sga_size,-sga期望大小 a.sga_size_factor,-期望sga大小与实际sga大小的百分比 a.estd_db_time,-sga设置为期望的大小后，其dbtime消耗期望的变化 a.estd_db_time_factor,-修改sga为期望大小后，dbtime消耗的变化与修改前的变化百分比 a.estd_physical_reads-修改前后物理读的差值 FROM v$sga_target_advice a;Oracle Sqlplus设置行数和宽度 设置页面显示总行数s

23、how pagesize; set pagesize 100; 设置行的宽度show linesize; set linesize 100;设置默认的值在Oracle的安装目录下找到glogin.sql，加入set linesize 300 ;set pagesize 100 ;declarein_date VARCHAR2(10);V_TOTAL_DAYS number;v_date VARCHAR2(10);BEGIN in_date:=201402; V_TOTAL_DAYS:=last_day(to_date(in_date|01,yyyymmdd)-to_date(in_date|0

24、1,yyyymmdd)+1; for X IN 1.V_TOTAL_DAYS LOOP-循环开始 v_date:=to_char(to_date(in_date|01,yyyymmdd)+X-1,yyyymmdd); dbms_output.put_line(v_date); end loop; end;设置job数量，如果为0，job就不会执行ALTER SYSTEM SET job_queue_processes = 20;commit;启用/禁用sys用户远程登陆alter system set remote_login_passwordfile=exclusive scope=spfi

25、le; alter system set remote_login_passwordfile=none scope=spfile; 查看占用system表空的用户 select username, default_tablespace, temporary_tablespace from dba_users where username not in (SYS,SYSTEM) and (default_tablespace=SYSTEM or temporary_tablespace=SYSTEM);11g中oracle的审计功能是默认开启的（10g是默认关闭的），统计信息会影响数据库性能，也

26、会给system表空间带来巨大的压力问题处理方法：1.如果审计不是必须的，可以关掉审计功能；SQL alter system set audit_trail=none scope=spfile;SQLshowdown immediate;SQLstartup2.删除已有的审计信息可以直接truncate表aud$,或者采取dbms_audit_mgmt来清除。3.或者将aud$表移到另外一个表空间下，以减少system表空间的压力和被撑爆的风险sqlplus下查看执行计划：1、EXPLAIN PLAN FOR select * from dual;然后查看2、SELECT * FROM TAB

27、LE(DBMS_XPLAN.DISPLAY);强制使用索引select /*+ index(t i_t) */ * from t where username=EYGLE;遇到ORA-39006: internal errorORA-39213: Metadata processing is notavailable执行sqlplus / as sysdbaexecute sys.dbms_metadata_util.load_stylesheets;ORA-00020:maximum number of processes (150) exceeded 错误解决方法1、批量杀死过多的进程ps

28、 x|grep oracleqas|grep -v grep |awk print $1|xargs kill -9启动OEMemctl start dbconsole查看地址https:/172.16.22.151:1158/em/查询dblink的信息select count(*) from link$;查看某个表的索引select distinct index_name from dba_indexes where table_name=T_MOBILE_MID_USE_P;删除某一列alter table T_MOBILE_APP_USE_201403301 drop column r

29、n更改某用户的环境变量，使之永久生效如，在/home/oracle/.bash_profile中添加 export NLS_LANG=american_america.utf8source /home/oracle/.bash_profile 让更改的配置立即生效，不然得重新登陆此用户才有效注意：更改/etc/profile文件对所有用户有效果查看redo log日志的切换时间SELECT b.recid,TO_CHAR(b.first_time,dd-mon-yy hh24:mi:ss) start_time, a.recid,TO_CHAR(a.first_time,dd-mon-yy h

30、h24:mi:ss) end_time,ROUND(a.first_time-b.first_time)*25)*60,2) minutesFROM v$log_history a, v$log_history bWHERE a.recid = b.recid+1ORDER BY a.first_time DESC用create table as 创建事务级的临时表create global temporary table t_app2 ON COMMIT PRESERVE ROWS parallel (degree 4) as select /*+parallel(t 4) */ * from t_mobile_application设置oracle闪回alter system set recyclebin = on | off;flashback table tb_employees to before drop; 清理某个分区alter table t_mobile_client_p truncate partition P14查看分区索引SELECT INDEX_NAME NAME, NUM_