数据查询 | 查询目前系统的锁情况 | #查询SQL:主要是查看是否有长时间的锁存在; select s.sid,s.serial#,p.spid, rtrim(o.object_type) object_type,rtrim(o.owner) || '.' || o.object_name object_name, decode(a.lmode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', 'Unknown') LockMode, decode(a.request, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', 'Unknown') RequestMode ,a.ctime, a.block, s.username,s.machine,s.module,s.action, decode(a.type, 'MR', 'Media Recovery', 'RT','Redo Thread', 'UN','User Name', 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', 'DX', 'Distributed Xaction', 'CF', 'Control File', 'IS', 'Instance State', 'FS', 'File Set', 'IR', 'Instance Recovery', 'ST', 'Disk Space Transaction', 'TS', 'Temp Segment', 'IV', 'Library Cache Invalida-tion', 'LS', 'Log Start or Switch', 'RW', 'Row Wait', 'SQ', 'Sequence Number', 'TE', 'Extend Table', 'TT', 'Temp Table', 'Unknown') LockType from (select * from v$lock) a, all_objects o,v$session s,v$process p where a.sid > 6 and object_name<>'OBJ$' and a.id1 = o.object_id and a.sid=s.sid and p.addr=s.paddr and s.sid > 53 and rtrim(owner)<>'SYS'; 查询结果样例: SID SERIAL# SPID OBJECT_TYPE OBJECT_NAME LOCKMODE REQUESTMODE CTIME BLOCK USERNAME MACHINE MODULE ACTION LOCKTYPE ------ ---------- ------- -------------- -------------------- ----------- -------------- -------- -------- ----------- ---------- --------- --------- ----------- 423 39578 18947 TABLE LYJ.TEST_INTERACTLOG Row-X None 935 0 LYJ ocs22 SQL*Plus (null) DML |
数据查询 | 查看Session分布情况 | #1.按照机器操作系统用户访问进程三个字段分组,统计Session占用情况: sqlplus "/as sysdba" set line 1024; select a.machine,a.osuser,a.program, count(*) from v$session a group by a.machine,a.osuser,a.program order by count(*) desc; #2. 按照机器操作系统用户访问进程,和数据库用户名四个字段分组,统计Session占用情况: sqlplus "/as sysdba" set line 1024; select a.machine,a.osuser,a.username,a.program,count(*) from v$session a group by a.machine,a.osuser,a.username,a.program order by count(*) desc; select a.machine,a.osuser,a.username,a.program,count(*) from v$session a where a.status='INACTIVE' group by a.machine,a.osuser,a.username,a.program order by count(*) desc; select a.machine,a.osuser,a.username,a.program,count(*) from v$session a where a.status='ACTIVE' group by a.machine,a.osuser,a.username,a.program order by count(*) desc; 备注: a.machine==》发起连接的客户端机器名 a.osuser ==》发起连接的客户端操作系统用户名 a.username ==》发起连接的鉴权的数据库用户名 a.program==》发起连接的客户端,进程名 |
数据查询 | 查看数据库long操作进度 | #根据sid查询这个session正在执行的SQL,进程百分比和所消耗的时间: sqlplus "/as sysdba" set line 1024; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; SELECT SID, decode(totalwork, 0, 0, round(100 * sofar/totalwork, 2)) "Percent", message "Message", start_time, elapsed_seconds, time_remaining , inst_id from GV$Session_longops where TIME_REMAINING>0; 说明:Percent:表示完成百分比;Message:运行执行步骤信息;elapsed_seconds:已执行的时间(单位:秒); time_remaining:预计还需要执行的时间(单位:秒) |
修改操作 | 清理备份 | rman target /; rman> list backup; rman> CROSSCHECK BAKCUP; rman> delete archivelog all; rman> delete backup; rman> quit; 手工强制删除:delete noprompt force archivelog all completed before 'sysdate-1'; |
数据查询 | 检查表空间占用情况 | set line 1024; SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME=F.TABLESPACE_NAME UNION ALL select d.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",NVL(FREE_SPACE,0) "FREE_SPACE(M)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME=F.TABLESPACE_NAME order by "USED_RATE(%)" desc; |
数据查询 | 查看数据库状态 | sqlplus "/as sysdba" SQL> select INSTANCE_NAME,STATUS from V$instance; INSTANCE_NAME STATUS ---------------- ------------ BMPDB OPEN SQL> select NAME,OPEN_MODE from v$database; NAME OPEN_MODE --------- ---------- BMPDB READ WRITE --》数据库主机正常模式; |
数据查询 | 查询索引字段和索引状态 | 如果快速查询用户级别的表:dba_indexes,dba_ind_columns 1)登录和字段长度格式设置: sqlplus "/as sysdba" set line 1024; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; col table_name format a20; col index_name format a20; col uniqueness format a10; col tablespace_name format a15; col last_analyzed format a15; col column_name format a15; col status format a8; 场景1:根据表名(自行替换)快速查询表的索引状态和字段名称:(非sysdba登录场景) select b.owner, a.table_name, a.index_name, b.tablespace_name, b.uniqueness, b.status, b.num_rows, b.last_analyzed, a.column_position as col_posttion, a.column_name from dba_ind_columns a, dba_indexes b where b.index_name = a.index_name and b.table_name = a.table_name and b.owner = a.table_owner and b.table_name = upper('BC_QUERY_RECORD') and b.owner = upper('USR_510'); 场景2:根据索引名(自行替换)查询索引状态和索引字段名:(非sysdba登录场景) select b.owner, a.table_name, a.index_name, b.tablespace_name, b.uniqueness, b.status, b.num_rows, b.last_analyzed, a.column_position as col_posttion, a.column_name from dba_ind_columns a, dba_indexes b where b.index_name = a.index_name and b.table_name = a.table_name and b.owner = a.table_owner and a.index_name = upper('IDX_TEST_INTERACTLOG') and b.owner = upper('USR_510'); |
数据查询 | 查询索引字段和索引状态 | 如果快速查询用户级别的表:user_indexes,user_ind_columns 1)登录和字段长度格式设置: sqlplus username/passwd set line 1024; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; col table_name format a20; col index_name format a20; col uniqueness format a10; col tablespace_name format a15; col last_analyzed format a15; col column_name format a15; col status format a8; 场景1:根据表名(自行替换)快速查询表的索引状态和字段名称:(非sysdba登录场景) select a.table_name, a.index_name, b.tablespace_name, b.uniqueness, b.status, b.num_rows, b.last_analyzed, a.column_position, a.column_name from user_ind_columns a, user_indexes b where b.index_name = a.index_name and b.table_name = a.table_name and b.table_name = upper('BC_QUERY_RECORD'); 场景2:根据索引名(自行替换)查询索引状态和索引字段名:(非sysdba登录场景) select a.table_name, a.index_name, b.tablespace_name, b.uniqueness, b.status, b.num_rows, b.last_analyzed, a.column_position, a.column_name from user_ind_columns a, user_indexes b where b.index_name = a.index_name and b.table_name = a.table_name and a.index_name = upper('IDX_TEST_INTERACTLOG'); |
数据查询 | 查询分区表和分区索引字段和索引状态 | sqlplus "/as sysdba" set line 1024; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; 1)根据表名查询分区索引统计更新时间和状态: select index_owner,tablespace_name,index_name,partition_name,logging,num_rows,last_analyzed,status from dba_ind_partitions where index_name in (select index_name from dba_part_indexes where table_name=upper('sum_dyn_pos_201204_101')) and index_owner=upper('V_USR9'); 2)根据表名查询分区表统计更新时间和状态: select owner,table_name,partitioning_type,partition_count,status,def_tablespace_name,def_logging from dba_part_tables t where t.table_name=upper('sum_dyn_pos_201204_101') and owner=upper('v_usr9'); select table_owner,table_name,partition_name,logging,num_rows,last_analyzed, global_stats from dba_tab_partitions t where t.table_name=upper('sum_dyn_pos_201204_101') and TABLE_OWNER=upper('V_USR9'); 3)查询某个用户的某个表的索引以及对应的字段信息: select TABLE_NAME ,TABLE_OWNER,INDEX_NAME,COLUMN_NAME from dba_ind_columns where index_name in (select index_name from dba_part_indexes where table_name=upper('sum_dyn_pos_201204_101')) and TABLE_OWNER=upper('V_USR9'); 4)根据表名查看索引归属表名,默认表空间,分区类型,分区数,locality(local/global)标志是全局索引还是本地索引: 如果全局索引是分区索引,需要查询分区索引表:DBA_PART_INDEXES表: select owner,def_tablespace_name,table_name,index_name,partitioning_type,partition_count,partitioning_key_count,locality from DBA_PART_INDEXES where table_name=upper('sum_dyn_pos_201204_101') and owner=upper('V_USR9'); |
数据查询 | 当前运行进程和SQL信息查看 | 出现问题时迅速查询出来存在的进程号,运行的进程,等待事件和SQL语句: sqlplus "/as sysdba" set line 1024; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; select p.spid, s.sid,s.SERIAL#, s.last_call_et, s.machine, s.program, s.terminal, w.event,q.sql_id,q.sql_text from v$process p, v$session s, v$sqltext q, v$session_wait w where p.addr = s.paddr and s.sql_address = q.address and s.status = 'ACTIVE' and p.background is null and s.sid = w.sid and s.last_call_et > 1 order by s.last_call_et desc, s.sid, q.piece; |
数据查询 | 会话阻塞时间超过阈值告警 | 备注:通过这个SQL可以直接查询出,阻塞与被阻塞者 blocker:锁的持有者;waiters:被阻塞者:(常用) select distinct 'blocker('||lb.sid||':'||sb.username||')-sql:'|| qb.sql_text blockers, 'waiter ('||lw.sid||':'||sw.username||')-sql:'|| qw.sql_text waiters from v$lock lb, v$lock lw, v$session sb, v$session sw, v$sql qb, v$sql qw where lb.sid=sb.sid and lw.sid=sw.sid and sb.prev_sql_addr=qb.address and sw.sql_address=qw.address and lb.id1=lw.id1 and sw.lockwait is not null and sb.lockwait is null and lb.block=1; I2000监控告警使用的查询的SQL:(ctime>120就会告警) select /*+rule */sid, ctime from v$lock a where a.BLOCK = 0 and a.id2 in (select id2 from v$lock b where b.BLOCK = 1 and a.sid= b.sid) 根据SID查询对应的操作: #1.查询session阻塞其它Session的个数:(如果block_count最大的那个blocking_session,可能就是出现问题的操作) select BLOCKING_SESSION,count(*) from v$session where BLOCKING_SESSION!=0 group by BLOCKING_SESSION; select sid,event,sql_id,program,machine,blocking_session,blocking_instance,p1,p2 from v$session where BLOCKING_SESSION!=0 order by blocking_session; #2.根据第一步查询出来的异常的blocking_session代入下面SQL进行查询确认是什么操作:(然后再考虑是否需要杀死对应的Session) select a.osuser,a.machine,a.sid,a.serial#,a.program,a.status,b.sql_text from v$session a,v$sqlarea b where a.sql_hash_value=b.hash_value and a.sid in (select s.sid from v$session s,v$process p where s.sid=1566 and s.paddr=p.addr); |
数据查询 | 查询目前系统的锁情况 | set line 1024; SELECT A.OWNER, A.OBJECT_NAME, B.XIDUSN, B.XIDSLOT, B.XIDSQN, B.SESSION_ID, B.ORACLE_USERNAME, B.OS_USER_NAME, B.PROCESS, B.LOCKED_MODE, C.MACHINE, C.STATUS, C.SERVER, C.SID, C.SERIAL#, C.PROGRAM FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C WHERE ( A.OBJECT_ID = B.OBJECT_ID ) AND (B.PROCESS = C.PROCESS ) ORDER BY 1,2; |
数据查询 | 查询目前系统的锁情况 | 1、如果业务感知到阻塞,可以用如下sql查询当前的会话阻塞情况 SQL> select sid,event,sql_id,program,machine,blocking_session,blocking_instance,p1,p2 from v$session where event not like 'S%'; |
数据查询 | 查询目前系统的锁情况 | set line 1024; select V$SESSION.sid,v$session.SERIAL#,v$process.spid, rtrim(object_type) object_type,rtrim(owner) || '.' || object_name object_name, decode(lmode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', 'Unknown') LockMode, decode(request, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', 'Unknown') RequestMode ,ctime, block b, v$session.username,MACHINE,MODULE,ACTION, decode(A.type, 'MR', 'Media Recovery', 'RT','Redo Thread', 'UN','User Name', 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', 'DX', 'Distributed Xaction', 'CF', 'Control File', 'IS', 'Instance State', 'FS', 'File Set', 'IR', 'Instance Recovery', 'ST', 'Disk Space Transaction', 'TS', 'Temp Segment', 'IV', 'Library Cache Invalida-tion', 'LS', 'Log Start or Switch', 'RW', 'Row Wait', 'SQ', 'Sequence Number', 'TE', 'Extend Table', 'TT', 'Temp Table', 'Unknown') LockType from (SELECT * FROM V$LOCK) A, all_objects,V$SESSION,v$process where A.sid > 6 and object_name<>'OBJ$' and A.id1 = all_objects.object_id and A.sid=v$session.sid and v$process.addr=v$session.paddr and v$session.sid > 53 and rtrim(owner)<>'SYS'; |
数据查询 | 查询目前系统的锁情况 | sqlplus "/as sysdba" set line 1024; col OBJECT_NAME format a30; select object_name,s.sid,s.serial#,p.spid from v$locked_object l,dba_objects o,v$session s,v$process p where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr; |
数据查询 | 查询目前系统的事务情况 | sqlplus "/as sysdba" set line 1024; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; select b.sid,b.serial#,p.spid,b.STATUS ses_status, a.STATUS trans_status,b.program, a.start_date,sysdate current_date, a.xidusn, a.xidslot, a.used_ublk from v$transaction a, v$session b, v$process p where a.ses_addr=b.saddr and p.addr=b.paddr; |
修改操作 | 异常锁表处理 | 1)通过查找已被锁定的数据库表以及相关的sid,serial#,spid; select o.object_name,o.object_type,s.sid,s.serial#,p.spid from v$locked_object l,dba_objects o,v$session s,v$process p where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr; 2)在数据库中杀死Session alter system kill session 'sid,serial#'; --sid,serial#是上面查询出来的结果; 3)杀死对应的应用程序 kill -9 spid 批量生成: 如果想把所有存在锁表的session都停止,可以批量生成sql。 SQL> set head off; SQL> select 'alter system kill session '||chr(39)||s.sid||','||s.serial#||chr(39)||';' from v$locked_object l,dba_objects o,v$session s,v$process p where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr and S.USERNAME='db_username'); |
数据查询 | 获取当前窗口的sessionid和进程号 | #获取当前登录窗口的 s.sid,s.serial#,p.spid: select s.sid,s.serial#,p.spid from v$session s,v$process p where s.sid in (select sid from v$session where audsid=userenv('sessionid')) and s.paddr=p.addr; #获取当前sqlplus登录窗口的sid和serial#: select sid, serial# from v$session where sid in (select userenv('sid') from dual); |
数据查询 | 收集历史Session和SQL信息 | #执行前,请根据实际情况,修改一下时间点; sqlpluas "/as sysdba" SQL> set markup html on spool on SQL> spool SQL_History.html SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; SQL> select * from DBA_HIST_ACTIVE_SESS_HISTORY where SAMPLE_TIME between to_date('2012/04/13 08:00:00','yyyy/mm/dd hh24:mi:ss') and to_date('2012/04/13 09:10:59','yyyy/mm/dd hh24:mi:ss') order by sample_time; SQL> select * from DBA_HIST_SQLTEXT; SQL> spool off SQL> set markup html off |
数据查询 | 根据Oracle进程号查询对应SQL语句 | 场景1:根据进程号查询对应的SQL操作: sqlplus "/as sysdba" set line 1024; select a.osuser,a.machine,a.sid,a.serial#,a.program,a.status,b.sql_text from v$session a,v$sqlarea b where a.sql_hash_value=b.hash_value and a.sid in (select s.sid from v$session s,v$process p where p.spid=27697 and s.paddr=p.addr); 27697 --》替换为占用CPU高的进程号,进行查询; 场景2:根据Sessionid号查询对应的SQL操作: sqlplus "/as sysdba" set line 1024; select a.osuser,a.machine,a.sid,a.serial#,a.program,a.status,b.sql_text from v$session a,v$sqlarea b where a.sql_hash_value=b.hash_value and a.sid in (select s.sid from v$session s,v$process p where s.sid=27697 and s.paddr=p.addr); 27697 --》替换为占用CPU高的Sid,进行查询; |
数据查询 | 如何记录登陆客户端IP地址: | #1.创建触发器:(作用:新的连接建立时在v$session 中的client_info字段记录客户端IP地址) sqlplus "/as sysdba" create or replace trigger login_on after logon on database begin dbms_application_info.set_client_info(sys_context('userenv', 'ip_address')); end; / #2.根据CPU搞的进程好查询对应的SQL操作: set line 1024; select a.osuser,a.machine,a.sid,a.serial#,p.spid,a.program,a.status,a.client_info,b.sql_text from v$session a,v$sqlarea b where a.sql_hash_value=b.hash_value and a.sid in (select s.sid from v$session s,v$process p where p.spid=27697 and s.paddr=p.addr); 27697 --》替换为占用CPU高的进程号,进行查询; #3.根据CPU搞的进程好查询对应的SQL操作: set line 1024; select a.osuser,a.machine,a.sid,a.serial#,p.spid,a.program,a.status,a.client_info,b.sql_text from v$session a,v$sqlarea b where a.sql_hash_value=b.hash_value and a.sid in (select s.sid from v$session s,v$process p where s.sid=17697 and s.paddr=p.addr); 17697 --》替换为需要查询的SID,进行查询; |
数据查询 | 查看数据库状态 | SQL> select INSTANCE_NAME,STATUS from V$instance; INSTANCE_NAME STATUS ---------------- ------------ BMPDB OPEN SQL> select NAME,OPEN_MODE from v$database; NAME OPEN_MODE --------- ---------- BMPDB READ WRITE |
数据查询 | l 列出最消费资源的SQL | #1.SQL in Shared Pool with High Disk Reads to Executions Ratio: select sql_text from (select sql_text from v$sqlarea where disk_reads > 5000*executions Order by executions desc) where rownum < 5; #2.SQL in Shared Pool with High Buffer Gets to Executions Ratio: select sql_text from (select sql_text from v$sqlarea where buffer_gets > 5000*executions order by executions desc) where rownum<5; #3.SQL in Shared Pool with High Loads: Select sql_text from (select sql_text from v$sqlarea order by loads desc) where rownum<5; |
数据查询 | l 列出数据库TOP5等待事件 | select event from ( select event ,count(*) from v$session_wait where wait_class#<>6 group by event order by count(*) desc ) where rownum <6 |
数据查询 | 统计表记录数 | #1.表记录超过500万; set markup html on spool on; spool count_table.html; select owner,TABLESPACE_NAME,table_name,num_rows,PARTITIONED,LOGGING,LAST_ANALYZED from dba_tables where num_rows>5000000 order by owner,num_rows desc; spool off; set markup html off; |
数据查询 | 查询对象状态和创建时间 | #1.查询dba_objects获取对象状态,类型,创建时间,最后一次DDL修改时间: sqlplus "/as sysdba" SQL> set line 1024; SQL> col OWNER format a10; SQL> col OBJECT_NAME format a20; SQL> col OBJECT_TYPE format a15; SQL> col CREATED format a15; SQL> col LAST_DDL_TIME format a15; SQL> col STATUS format a8; SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED,LAST_DDL_TIME,STATUS from dba_objects where OBJECT_ID=2; SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED,LAST_DDL_TIME,STATUS from dba_objects where OBJECT_NAME=upper('C_OBJ#'); OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME STATUS ---------- -------------------- --------------- --------------- --------------- -------- SYS C_OBJ# CLUSTER 20071014150341 20071014150341 VALID |
数据查询 | 查询是否有失效的触发器 | select TRIGGER_NAME, STATUS from user_triggers where STATUS='DISABLED'; |
数据查询 | 查看归档日志最新的100条 | sqlplus "/as sysdba" col name for a35 col start_time for a20 col end_time for a20 set line 1024 select * from ( select name, to_char(first_time, 'yyyy-mm-dd hh24:mi:ss') start_time, to_char(next_time, 'yyyy-mm-dd hh24:mi:ss') end_time, trunc((next_time - first_time) * 24 * 60) internal_time from v$archived_log order by recid desc) where rownum<100; |
数据查询 | 查询昨天归档日志总大小 | select sum(blocks*block_size)/1024/1024 from v$archived_log where completion_time>sysdate-1 ; SUM(BLOCKS*BLOCK_SIZE)/1024/1024 -------------------------------- 47712.0547 |
修改操作 | 清理过期备份 | rman target /; RMAN>CROSSCHECK BAKCUP; RMAN>DELETE EXPIRED BACKUP; RMAN>DELETE OBSOLETE; |
数据查询 | 查询归档日志状态 | set line 1024 alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; select * from v$log; SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- 1 1 7122 1048576000 1 YES INACTIVE 1.2249E+10 12/08/2011 02:47:08 2 1 7120 1048576000 1 YES INACTIVE 1.2240E+10 12/07/2011 22:26:53 3 1 7119 1048576000 1 YES INACTIVE 1.2239E+10 12/07/2011 21:40:11 4 1 7123 1048576000 1 NO CURRENT 1.2249E+10 12/08/2011 02:47:23 5 1 7118 1048576000 1 YES INACTIVE 1.2238E+10 12/07/2011 20:58:03 6 1 7121 1048576000 1 YES INACTIVE 1.2241E+10 12/07/2011 23:36:26 6 rows selected. |
数据查询 | 查看监听状态 | lsnrctl status 或则 tnsping 监听名称 |
修改操作 | Oracle手动强制回滚未执行的事务 | #查询pengding事务: set line 1024; select a.local_tran_id,a.state,a.tran_comment,a.fail_time,a.retry_time, a.force_time from dba_2pc_pending a; #再用这个语句去强制结束这个local_tran_id: COMMIT FORCE 'local_tran_id'; #既执行:然后就应当ok了(假如查询出来的是:6.4.7613,执行下面操作,强制提交事务) COMMIT FORCE '6.4.7613'; |
修改操作 | 手动锁表 | 命令格式:lock table tablename in modename mode [nowait] 命令格式:lock table 表名 in exclusive mode nowait; --exclusive 专用(X):独立访问使用 加锁后,当遇到commit 或 rollback就会自动解锁! lock table lyj in exclusive mode nowait; 执行样例:(当commit之后,锁就自动释放了) SQL> lock table lyj in exclusive mode nowait; Table(s) Locked. SQL> insert into LYJ (ID, NAME, EXCALLAUTH, EXCALLAUTH1) values (5, '22', '00000000000000000000000000000000', '00000000000000000000000000000000'); 1 row created. SQL> commit; Commit complete. |
数据查询 | 检查表空间占用情况 | sqlplus "/as sysdba" set line 1024; SELECT A.TABLESPACE_NAME ,A.BYTES/1024/1024 "TOTAL(M)",B.BYTES/1024/1024 "USED(M)",C.BYTES/1024/1024 "FREE(M)",(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE" FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; --单独查看temp表空间大小: select d.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",NVL(FREE_SPACE,0) "FREE_SPACE(M)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME=F.TABLESPACE_NAME order by "USED_RATE(%)" desc; |
数据查询 | 检查表空间datafiles | #1.查看datafile和表空间对应关系: sqlplus "/as sysdba" set line 1024; col file_name format a40; col tablespace_name format a25; select tablespace_name ,file_id,file_name,BYTES/1024/1024 "SIZE(M)",status,online_status,autoextensible from dba_data_files order by tablespace_name; select tablespace_name ,file_id,file_name ,BYTES/1024/1024 "SIZE(M)",STATUS,autoextensible from dba_temp_files; 1、执行查看逻辑日志成员和状态: sqlplus "/as sysdba" set line 1024; col member format a50; col status format a10; select lf.GROUP#,lg.STATUS, lg.ARCHIVED,lg.BYTES/1024/1024 size_mb, lf.MEMBER from v$logfile lf,v$log lg where lf.GROUP#=lg.GROUP#; 检查主机是否有创建在本地盘的:datafile文件 场景:不少局点实施/维护不规范,导致部分添加的部分datafile直接使用文件,并且创建在本地盘,导致双机切换后,找不到对应的datafile,引起数据库启动失败; 检查方法: sqlplus "/as sysdba" select name from v$datafile union all select name from v$tempfile union all select member from v$logfile union all select name from v$controlfile union all select value from v$parameter where name = 'spfile' order by name; |
数据查询 | 检查表空间datafiles部署 | 检查主机是否有创建在本地盘的:datafile文件 场景:不少局点实施/维护不规范,导致部分添加的部分datafile直接使用文件,并且创建在本地盘,导致双机切换后,找不到对应的datafile,引起数据库启动失败;(datafile,redo,controlfile,spfile),对于11gR1:都应该在双机共享的裸设备上;11gR2使用ASM管理,都应该分布在diskgroup上; 检查方法: sqlplus "/as sysdba" select name from v$datafile union all select name from v$tempfile union all select member from v$logfile union all select name from v$controlfile union all select value from v$parameter where name = 'spfile' order by name; |
数据查询 | 检查表空间datafiles | #1.查看对应datafile上的对象: select distinct OWNER,TABLESPACE_NAME,FILE_ID,SEGMENT_NAME,SEGMENT_TYPE from dba_extents where file_id=14; |
数据查询 | 查看数据库用户默认表空间 | sqlplus "/as sysdba" set line 1024; col USERNAME format a20; col ACCOUNT_STATUS format a17; col PROFILE format a18; col DEFAULT_TABLESPACE format a18; col TEMPORARY_TABLESPACE format a20; select USERNAME,ACCOUNT_STATUS,CREATED,LOCK_DATE,EXPIRY_DATE,PROFILE,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users; 过滤掉Oracle的系统账户: select USERNAME,ACCOUNT_STATUS,CREATED,LOCK_DATE,EXPIRY_DATE,PROFILE,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where USERNAME not in ('ANONYMOUS', 'AURORA$', 'AURORA', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'DVF', 'DVSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 'ODM_MTR', 'OE', 'OLAPSYS', 'ORACLE_OCM', 'ORAWSM', 'ORDPLUGINS', 'ORDSYS', 'OSE', 'OUTLN', 'PERFSTAT', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 'QS_ES', 'QS_OS', 'QS_WS', 'REPADMIN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TRACESVR', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WK_TEST', 'WKUSER', 'WMSYS', 'XDB','FLOWS_030000'); |
数据查询 | 查看数据库用户默认密码 | Oracle数据库创建起来后,会创建很多辅助用户,这些用户帐号如果不去修改密码,他们使用的全部是默认密码,有些帐号的默认密码已经是公开了的密秘,给数据库的安全带来威胁。 通过下面SQL查询:检查数据库用户那些使用的还是默认密码,存在安全隐患; sqlplus "/as sysdba" set line 1024; col USERNAME format a20; col ACCOUNT_STATUS format a17; col PROFILE format a18; col DEFAULT_TABLESPACE format a18; col TEMPORARY_TABLESPACE format a20; select t1.USERNAME,ACCOUNT_STATUS,CREATED,LOCK_DATE,expiry_date,PROFILE,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users t1,dba_users_with_defpwd t2 where t1.username=t2.username; |
修改操作 | 修改数据库用户默认表空间 | 场景1:SDPDB指数据库用户名,SCUDATATBS新的表空间名称; sqlplus "/as sysdba" alter user SDPDB DEFAULT TABLESPACE SCUDATATBS; 场景2:修改数据库指定用户默认临时表空间 sqlplus "/as sysdba" alter user SDPDB default temporary tablespace temp2; 场景3:修改数据库所有用户默认临时表空间 sqlplus "/as sysdba" alter database default temporary tablespace temp2; |
数据查询 | 查看回滚段的使用情况 | select s.username, u.name from v$transaction t,v$rollstat r, v$rollname u,v$session s where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username; |
数据查询 | 查看回滚段状态 | SELECT n.usn,n.name,r.tablespace_name,s.xacts, s.status, s.extents, s.rssize/1024/1024,s.hwmsize , s.shrinks,(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, max_extents, s.curext CurExtent FROM dba_rollback_segs r,v$rollname n, v$rollstat s WHERE r.segment_id=n.usn and n.usn = s.usn order by s.rssize; |
数据查询 | 检查单个表占用表空间 | #根据对象名(表名)查询单个对象(表)的表空间占用情况,使用时替换下面account表名即可; sqlplus "/as sysdba" col OWNER format a20; col segment_name format a20; col segment_type format a20; col Space_M format 999999.99; select owner,segment_name,segment_type,sum(bytes)/1024/1024 Space_M FROM dba_segments where segment_name=upper('account') group by OWNER,segment_name,segment_type order by Space_M DESC; |
数据查询 | 检查大表占用表空间排名 | sqlplus "/as sysdba" set markup html on spool on; spool count_segmentSize.html; SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME=F.TABLESPACE_NAME UNION ALL select d.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",NVL(FREE_SPACE,0) "FREE_SPACE(M)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME=F.TABLESPACE_NAME order by "USED_RATE(%)" desc; select owner,TABLESPACE_NAME,segment_name,segment_type,sum(bytes)/1024/1024 Space_M FROM dba_segments group by OWNER,TABLESPACE_NAME,segment_name,segment_type order by Space_M DESC; spool off; set markup html off; exit |
数据查询 | 检查大表占用表空间排名 | #可以根据:数据库空间名,归属用户名,查看TABLE类型空间占用情况,按照降序排列: select segment_name,sum(bytes)/1024/1024 Space_M FROM dba_segments WHERE segment_type ='TABLE' and TABLESPACE_NAME ='SCUDATATBS' and OWNER ='MTNCBPDB' group by segment_name order by Space_M DESC; |
数据查询 | 检查索引占用表空间排名 | #可以根据:数据库空间名,归属用户名,查看Index索引类型空间占用情况,按照降序排列: select segment_name,sum(bytes)/1024/1024 Space_M FROM dba_segments WHERE segment_type ='INDEX' and TABLESPACE_NAME ='SCUDATATBS' and OWNER ='MTNCBPDB' group by segment_name order by Space_M DESC; |
数据查询 | 检查对象与数据文件关系 | #查询各种对象,占用表空间文件之间的关系: sqlplus "/as sysdba" select owner,segment_name,segment_type FROM dba_extents where file_id=20; |
数据查询 | 检查对象占用表空间排名 | #查看各种segment分布和占用情况: set markup html on spool on; spool count_segmentSize.html; select owner,segment_name,segment_type,sum(bytes)/1024/1024 Space_M FROM dba_segments group by OWNER,segment_name,segment_type order by Space_M DESC; select owner,table_name,num_rows,LAST_ANALYZED from dba_tables where num_rows>5000000 order by owner,num_rows desc; spool off; set markup html off; |
数据查询 | 检查temp表空间被那些应用占用 | SELECT su.username,se.sid,se.serial#,se.sql_address,se.machine,se.program,su.tablespace,su.segtype,su.contents FROM v$session se,v$sort_usage su WHERE se.saddr=su.session_addr; |
数据查询 | undo使用率查询 | [undo 使用率] --------------------------------- select a.tablespace_name, used_undo, total_undo, trunc(used_undo / total_undo * 100, 2) || '%' used_rag from (select nvl(sum(bytes / 1024 / 1024), 0) used_undo, tablespace_name from dba_undo_extents where status = 'ACTIVE' group by tablespace_name) a, (select tablespace_name, sum(bytes / 1024 / 1024) total_undo from dba_data_files where tablespace_name in (select value from v$spparameter where name = 'undo_tablespace' and (sid = (select instance_name from v$instance) or sid = '*')) group by tablespace_name) b where a.tablespace_name = b.tablespace_name; [监控谁在使用UNDO] ----------------------------------------- SELECT r.name rbs, nvl(s.username, 'None') ouser, s.osuser client, p.username puser, s.sid, s.serial#, p.spid pid, TO_CHAR(sysdate - (s.last_call_et) / 86400, 'mm/dd/yyhh24:mi:ss') as last_txn, trunc(t.used_ublk * TO_NUMBER(x.value) / 1024 / 1024,2) as undo_mb FROM v$process p, v$rollname r, v$session s, v$transaction t, v$parameter x WHERE s.taddr = t.addr AND s.paddr = p.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size' ORDER BY r.name; |
数据查询 | 检查回收站中对象 | sqlplus “/as sysdba” set line 1024; col owner format a15; col object_name format a35; col ORIGINAL_NAME format a35; col CREATETIME format a20; col DROPTIME format a20; select * from (select a.owner,a.OBJECT_NAME,a.ORIGINAL_NAME,a.CREATETIME,a.DROPTIME,b.bytes/1024/1024 MB from dba_recyclebin a,dba_segments b where a.object_name=b.segment_name order by MB desc) where rownum <11; |
数据查询 | 检查回收站中对象 | set line 1024; col owner format a15; col object_name format a35; col ORIGINAL_NAME format a35; col CREATETIME format a20; col DROPTIME format a20; select a.owner,a.OBJECT_NAME,a.ORIGINAL_NAME,a.CREATETIME,a.DROPTIME,b.bytes/1024/1024 MB from dba_recyclebin a,dba_segments b where a.object_name=b.segment_name order by MB desc; |
修改操作 | 清理回收站中对象 | select count(*) from dba_recyclebin; purge dba_recyclebin; |
数据查询 | 统计索引记录数 | #索引记录超过500万; set markup html on spool on; spool count_indexes.html; select owner,table_name,index_name,tablespace_name,num_rows,last_analyzed,status,uniqueness,logging,degree from dba_indexes where num_rows>5000000 order by num_rows desc; spool off; set markup html off; |
数据查询 | 查询是否有失效的索引 | #查询失效索引: 1)登录和字段长度格式设置: sqlplus "/as sysdba" set line 1024; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; col owner format a10; col index_owner format a10; col table_name format a20; col index_name format a20; col partition_name format a20; col uniqueness format a10; col tablespace_name format a15; col last_analyzed format a19; col status format a8; col degree format a6; col logging format a7; #2.查询无效的普通表索引: select owner,table_name,index_name,tablespace_name,num_rows,last_analyzed,status,uniqueness,logging,degree from dba_indexes where STATUS!='VALID' and PARTITIONED='N'; #查询无效分区索引: select index_owner,index_name,partition_name,tablespace_name,num_rows,last_analyzed,logging,status from DBA_IND_PARTITIONS where status!='USABLE'; |
数据查询 | 查询索引历史执行计划 | 通过查询可以判断sql语句的执行计划是否发生过变化:(判断:通一个sqlid的PLAN_HASH_VALUE的值不同,表示执行计划发生过变化,需要进行分析) 1)查询历史执行计划: select * from DBA_HIST_SQL_PLAN where sql_id= '2npv3wttghy0s' order by TIMESTAMP; 2)查询当前执行计划: select * from v$sql_plan where sql_id= '2npv3wttghy0s' order by TIMESTAMP; 3)根据已知的PLAN_HASH_VALUE查询sql_id: 查询历史执行计划:select * from DBA_HIST_SQL_PLAN where PLAN_HASH_VALUE=2459189408 order by TIMESTAMP; 查询当前执行计划:select * from v$sql_plan where PLAN_HASH_VALUE=2459189408 order by TIMESTAMP; 收集输出为HTML格式结果:(方面后台收集以及后续结果查看) sqlplus "/as sysdba" set markup html on spool on; spool sql_plans.html; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; select * from DBA_HIST_SQL_PLAN where sql_id= '2npv3wttghy0s' order by TIMESTAMP; select * from v$sql_plan where sql_id= '2npv3wttghy0s' order by TIMESTAMP; spool off; set markup html off; |
数据查询 | 获取SQL的执行计划 | 1、使用Explain Plan测试。使用Explain Plan for {statement}来显示语句statement的执行计划。通过输出的执行计划,可以查看是否使用到了正确的索引。 SQL> explain plan for select * from exttest; SQL> select * from table(dbms_xplan.display); --执行之后就会显示是否使用索引,或则是全表扫描; 2、也可以使用AutoTrace工具。执行set autotrace traceonly explain即可打开自动跟踪功能,set autotrace off关闭跟踪功能。 SQL> set autotrace traceonly explain SQL>查询的SQL语句;――执行之后就会显示是否使用索引,或则是全表扫描; SQL> set autotrace off |
修改操作 | 新建索引 | 建议:停业务操作 在线重建索引:alter index A_ID rebuild online; 并行在线创建索引: create index IDX_TS_POINSTANCE_3 on TS_POINSTANCE(EXTERNALID) nologging TABLESPACE cbs_user_idx parallel 6 online; 并行创建索引: create index IDX_TS_POINSTANCE_3 on TS_POINSTANCE(EXTERNALID) nologging TABLESPACE cbs_user_idx parallel 6; 恢复logging模式和设置并行度: alter index IDX_TS_POINSTANCE_3 logging noparallel; |
修改操作 | 大表重建索引步骤 | 说明:下面以u_uvc_supplyment表为例(如果是别的表,直接把操作步骤copy到文本中替换一下表名和表归属的owner(数据库用户名:例如:sysdb,userdb),形成新的操作步骤) #1.查看索引状态: sqlplus "/as sysdba" set line 1024; 查看表的记录条数(不是特别准,可以参考数量级) select owner, table_name , index_name, status,LAST_ANALYZED from dba_indexes where owner=upper('userdb') and table_name= upper('u_uvc_supplyment'); select owner,table_name,num_rows,LAST_ANALYZED from dba_tables where owner=upper('userdb') and table_name= upper('u_uvc_supplyment'); #2.根据表名生成rebuild重建索引的sql语句: select 'alter index '||owner||'.'||index_name||' rebuild online nologging parallel 4;' from dba_indexes where owner=upper('userdb') and table_name=upper('u_uvc_supplyment'); 两个加快创建索引速度的参数: nologging – 表示不写redo日志; parallel 4 –指定并行度;) 确认:parallel_max_servers 参数配置; show parameter parallel_max_servers 注意:如果parallel_max_servers显示结果是0,则表示不支持并行,可以通过下面修改支持并行(修改立即生效,无需重启数据库),修改后,待重建完索引,恢复现网原始的值; alter system set parallel_max_servers=30 scope=memory; #3.根据第二步生成的创建索引语句进行在线创建索引;(注意:在业务闲时创建) #4.创建过程监控:(创建索引要两个步骤,一个全表扫描“Table Scan”, 一个“Sort Output” 排序)可以监控每个步骤完成的百分比和所消耗的时间: #根据sid查询这个session正在执行的SQL,进程百分比和所消耗的时间: sqlplus "/as sysdba" set line 1024; SELECT SID, decode(totalwork, 0, 0, round(100 * sofar/totalwork, 2)) "Percent", message "Message", start_time, elapsed_seconds, time_remaining , inst_id from GV$Session_longops where TIME_REMAINING>0; #5.根据表名修改索引为logging模式并行度恢复为1的sql语句: select 'alter index '||owner||'.'||index_name||' logging parallel(degree 1);' from dba_indexes where owner=upper('userdb') and table_name=upper('u_uvc_supplyment'); #6. 根据执行第#5步生成的sql语句; 4)创建过程监控:(创建索引要两个步骤,一个全表扫描“Table Scan”, 一个“Sort Output” 排序)可以监控每个步骤完成的百分比和所消耗的时间: #根据sid查询这个session正在执行的SQL,进程百分比和所消耗的时间: set line 1024; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; SELECT SID, decode(totalwork, 0, 0, round(100 * sofar/totalwork, 2)) "Percent", message "Message", start_time, elapsed_seconds, time_remaining , inst_id from GV$Session_longops where TIME_REMAINING>0; |
数据查询 | 查看索引重建进展 | 创建过程监控:(创建索引要两个步骤,一个全表扫描“Table Scan”, 一个“Sort Output” 排序) 可以监控每个步骤完成的百分比和所消耗的时间: #根据SQLPlus对应的进程号,查询出来对应的sid: set line 1024; select s.machine,s.sid,s.serial#,s.program,s.status,p.spid from v$session s, v$process p where p.spid=15353 and s.paddr=p.addr; #根据sid查询这个session正在执行的SQL: select b.sql_text from v$session a , v$sqlarea b where a.sql_hash_value=b.hash_value and a.sid =335; #根据sid查询这个session正在执行的SQL,进程百分比和所消耗的时间: set line 1024; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; SELECT SID, decode(totalwork, 0, 0, round(100 * sofar/totalwork, 2)) "Percent", message "Message", start_time, elapsed_seconds, time_remaining , inst_id from GV$Session_longops where sid=303 ; |
数据查询 | 号段信息统计生成SQL | select 'select count(*) from numservice where servicenumber between '||chr(39)||ISDNSTART||chr(39)||' and '||chr(39)||ISDNSTOP||chr(39) from w_uvs_isdntoscp where cbeid=101; |
数据查询 | 查询最近对某个表操作的SQL | set line 1024; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; select SQL_ID,SQL_TEXT,LAST_ACTIVE_TIME from v$sql where upper(SQL_TEXT) like '%U_UVC_VMPLICENSE%'; |
数据查询 | 绑定变量的SQL如何解析对应的变量值 | #设置一下当前session的时间格式: alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; #根据表名赛选对应的SQL: select sql_id,dbms_sqltune.extract_binds(bind_data) bind_context from v$sql WHERE SQL_TEXT LIKE '%FROM TEST1_TABLE%'; #或则根据对应的SQL_ID进行解析: select sql_id,sql_text ,dbms_sqltune.extract_binds(bind_data) bind_context from v$sql WHERE sql_id='9kkuyygw5hnza'; #SQL_ID最后一次执行的时候的绑定变量: select SQL_ID, name, position, value_string from v$sql_bind_capture where SQL_ID='1h7zt6jks80pa'; #通过查询:dba_hist_sqlbind视图强大的地方在于,它记录了每个AWR报告里的SQL的绑定变量值,当然这个绑定变量值也是AWR生成的时候从v$sql_bind_capture采样获得的。通过这个视图,我们能够获得比较多的绑定变量值,对于我们排查问题 select snap_id, name, position, value_string,last_captured,WAS_CAPTURED from dba_hist_sqlbind where sql_id = '576c1s91gua19' and snap_id='20433'; |
数据查询 | 通过redo日志分析历史操作 | sqlplus "/as sysdba" SQL> exec dbms_logmnr.add_logfile(logfilename=>'+DG_ORA/prbmpdb1/ora_redo02_2', options=>dbms_logmnr.new); SQL> exec dbms_logmnr.add_logfile(logfilename=>'+DG_ORA/prbmpdb1/ora_redo03_3', options=>dbms_logmnr.addfile); SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog); SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; SQL> set markup html on spool on SQL> spool LOGMNR_CONTENTS_DML_Result.html SQL> SELECT * FROM V$LOGMNR_CONTENTS WHERE seg_name='TS_INTERNUMBERHEAD'; --》根据表名过滤; SQL> spool off SQL> set markup html off SQL> execute dbms_logmnr.end_logmnr; |
数据查询 | #查询snap信息: SELECT NVL(MIN(s.snap_id), 0) ||chr(124)|| NVL(MAX(s.snap_id), 0) ||chr(124)|| COUNT(*) ||chr(124)|| NVL(MAX(s.dbid),0) ||chr(124)|| NVL(MAX(s.instance_number),0) FROM dba_hist_snapshot s , dba_hist_database_instance di WHERE di.dbid = s.dbid AND di.instance_number = s.instance_number AND di.startup_time = s.startup_time AND s.end_interval_time BETWEEN to_date(TO_CHAR(sysdate-0,'YYYY-MM-DD')||' 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND to_date(TO_CHAR(sysdate-0,'YYYY-MM-DD')||' 23:59:59','YYYY-MM-DD HH24:MI:SS'); #查询snap信息: SELECT NVL(s.snap_id, 0) ||chr(124)|| to_char(s.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') ||chr(124)|| to_char(s.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') ||chr(124)|| NVL(s.dbid,0) ||chr(124)|| NVL(s.instance_number,0) FROM dba_hist_snapshot s , dba_hist_database_instance di WHERE di.dbid = s.dbid AND di.instance_number = s.instance_number AND di.startup_time = s.startup_time AND s.end_interval_time BETWEEN to_date(TO_CHAR(sysdate-0,'YYYY-MM-DD')||' 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND to_date(TO_CHAR(sysdate-0,'YYYY-MM-DD')||' 23:59:59','YYYY-MM-DD HH24:MI:SS'); | |
修改操作 | 循环语句初始化表数据 | 【如何循环语句初始化表数据】 向其中插入100条数据 begin for i in 1 .. 100 loop insert into exttest values( i , 'Name_' || i,'remark_'||i ); end loop; end; / commit; |
数据查询 | 检查配置数据的失效时间 | SET SERVEROUTPUT ON; DECLARE cursor c1 is select OWNER || '.' || TABLE_NAME table_name, 'SELECT COUNT(*) FROM ' || OWNER || '.' || TABLE_NAME || ' WHERE ' || COLUMN_NAME || ' BETWEEN SYSDATE and SYSDATE+100 AND ROWNUM<2' v_sql from dba_tab_columns where (COLUMN_NAME LIKE '%END%' OR COLUMN_NAME LIKE '%EXP%' OR COLUMN_NAME LIKE '%FINISH%') and data_type = 'DATE' and OWNER in ('GENER63', 'ADMIN63'); v_count number; begin for rec in c1 loop execute immediate rec.v_sql into v_count; if v_count > 0 then --dbms_output.put_line('Excute Sql : '||rec.v_sql); dbms_output.put_line('Warning TBL: '||rec.table_name); end if; end loop; end; / |
修改操作 | 置触发器失效的方法 |
select b.sid, b.program, a.start_date, a.xidusn, a.xidslot, a.used_ublk, c.SPID from v$transaction a, v$session b, v$process c where a.ses_addr = b.saddr and c.addr = b.paddr | ||
获取执行计划变更的SQL_ID: select distinct t1.SQL_ID from v$sql_plan t1,v$sql_plan t2 where t1.sql_id=t2.sql_id and t1.PLAN_HASH_VALUE!=t2.PLAN_HASH_VALUE order by t1.sql_id; | ||
datafile的io动态性能视图 | SQL>SELECT SYSDATE,DF.TABLESPACE_NAME NAME,DF.FILE_NAME "FILE",F.PHYRDS PYR, F.PHYBLKRD PBR,F.PHYWRTS PYW, F.PHYBLKWRT PBW FROM V$FILESTAT F, DBA_DATA_FILES DF WHERE F.FILE# = DF.FILE_ID ORDER BY DF.TABLESPACE_NAME; | |
LibCache命中率SQL是否重用检查 | select sum(pins) "Executions", sum(pinhits) "Hits",((sum(pinhits) /sum(pins)) * 100) "PinHitRatio",sum(reloads) "Misses", ((sum(pins) / (sum(pins)+ sum(reloads))) * 100) "RelHitRatio" from v$librarycache; | |
select sql_id, count(*) bind_count from v$sql_bind_capture where child_number = 0 group by sql_id having count(*) > 20 order by count(*); #2.查看有问题的SQL: select sql_text, users_executing, executions, users_opening, buffer_gets from v$sqlarea where sql_id = '4vs91dcv7u1p6' order by buffer_gets; | ||
数据高速缓存区命中率 | select name,val from v$sysstat where name in ('physical reads','db block gets','consistent gets'); | |
数据查询 | 共享区字典缓存区命中率 | select sum(gets-getmisses-usage-fixed)/sum(gets) from v$rowcache; |
数据查询 | 检测回滚段的争用 | select sum(gets),sum(waits),sum(waits)/sum(gets) from v$rollstat; |
数据查询 | 检测回滚段收缩次数 | select name,shrinks from v$rollstat, v$rollname where v$rollstat.usn = v$rollname.usn; |
数据查询 | 查看排序多的SQL | SELECT SQL_ID, HASH_VALUE, SQL_TEXT, SORTS, EXECUTIONS FROM V$SQLAREA ORDER BY SORTS DESC; |
数据查询 | 查找磁盘读写最多的SQL | SELECT * FROM (SELECT SQL_ID,sql_text,disk_reads "total disk" , executions "total exec",disk_reads/executions "disk/exec" FROM v$sql WHERE executions>0 and is_obsolete='N' ORDER BY 5 desc) WHERE ROWNUM<11 ; |
修改操作 | 刷新cache和buffer | 刷新shareed_pool: alter system flush shared_pool; 刷新buffer_cache: alter system flush buffer_cache; |
11gR2:查询磁盘组和磁盘信息 | #1.查询磁盘组成员(grid用户): 11gR2:grid用户登录查询磁盘组和磁盘信息,命令行模式; |