DBA日常維護SQL腳本_自己編寫的
查詢碎片程度高（實際使用率小于30%）的表，也就是可以收縮的表
條件為什么block>100，因為一些很小的表，只有幾行數據實際大小很小，但是block一次性分配就是5個（11g開始默認一次性分配1M的block大小了，見create table storged的NEXT參數），5個block相對于幾行小表數據來說就相差太大了
算法中/0.9是因為塊的pfree一般為10%，所以一個塊最多只用了90%，而且一行數據大于8KB時容易產生行鏈接，把一行分片存儲，一樣的一個塊連90%都用不滿
AVG_ROW_LEN還是比較準的，比如個人實驗情況一表6個字段，一個number，其他5個都是char(100)但是實際數據都是’1111111’7位，AVG_ROW_LEN顯示依然為513
SELECT TABLE_NAME,(BLOCKS*8192/1024/1024)"理論大小M",
(NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)"實際大小M",
round((NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024),3)*100||'%' "實際使用率%"
FROM USER_TABLES where blocks>100 and (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024)<0.3
order by (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024) desc
查詢索引碎片的比例（索引刪除行數除以索引總行數的百分比>30%即認為索引碎片大），也就是需要重建的索引
select name,del_lf_rows,lf_rows, round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)||'%' frag_pct from index_stats where round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)>30;
集群因子clustering_factor高的表
集群因子越接近塊數越好，接近行數則說明索引列的列值相等的行分布極度散列，可能不走索引掃描而走全表掃描
select tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor,
round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行數"
from user_tables tab, user_indexes ind where tab.table_name=ind.table_name
and tab.blocks>100
and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3
或
select tab.owner,tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor,
round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行數"
from dba_tables tab, dba_indexes ind where tab.table_name=ind.table_name and tab.owner
not in ('SYS','SYSTEM','WMSYS','DBSNMP','CTXSYS','XDB','ORDDATA','SYSMAN','CATALOG','APEX_030200','MDSYS','OLAPSYS','EXFSYS')
and tab.blocks>100
and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3
根據sid查spid或根據spid查sid
select s.sid,s.serial#,p.spid,s.terminal,s.LOGON_TIME,s.status,s.PROGRAM,s.CLIENT_IDENTIFIER,s.machine,s.action,s.PROCESS "客戶端機器進程號",s.osuser from v$session s,v$process p where s.paddr=p.addr and s.sid=XX or p.spid=YY
根據sid查看具體的sql語句(不要加條件v$session.status=’ ACTIVE’,比如toad對同一數據庫開兩個連接會話，都執行了一些語句，其中一個窗口查詢select * from v$session時會發現另一個窗口在v$session.status是INACTIVE，并不代表另一個窗口沒有執行過sql語句，而當前窗口是active狀態，對應的sql_id對應的語句就是select * from v$session而不是之前執行過的sql語句，ACTIVE表示當前正在執行sql。一個sid可能執行過很多個sql，所以有時需要的sql通過如下查不到是正常的，比如查詢到某死鎖源sid，通過如下查詢可能只是個select語句，而真正引起死鎖的sql卻查不到，是因為可能這個sid持續了很長時間，這個sid之前執行的一些sql在v$sql可能已經被清除了。)
select username,sid,SERIAL#,LOGON_TIME,status,PROGRAM,CLIENT_IDENTIFIER,machine,action,PROCESS "客戶端機器進程號",osuser,sql_text from v$session a,v$sqltext_with_newlines b
where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value and a.sid=&sid order by piece;
或
select username,sid,SERIAL#,LOGON_TIME,status,sql_fulltext,PROGRAM,CLIENT_IDENTIFIER,machine,a.action,PROCESS "客戶端機器進程號",osuser from v$session a,v$sql b
where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value and a.sid=&sid
根據spid查詢具體的sql語句(不要加條件v$session.status=’ ACTIVE’,比如toad對同一數據庫開兩個連接會話，都執行了一些語句，其中一個窗口查詢select * from v$session時會發現另一個窗口在v$session.status是INACTIVE，并不代表另一個窗口沒有執行過sql語句，而當前窗口是active狀態，對應的sql_id對應的語句就是select * from v$session而不是之前執行過的sql語句，ACTIVE表示當前正在執行sql。)
Select ss.SID,ss.SERIAL#,ss.LOGON_TIME,pr.SPID,sa.SQL_FULLTEXT,ss.machine, ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.CLIENT_IDENTIFIER,ss.action,ss.PROCESS "客戶端機器進程號", ss.STATUS, ss.OSUSER,ss.status,ss.last_call_et,sa.sql_text
from v$process pr, v$session ss, v$sql sa
where pr.ADDR = ss.PADDR
and DECODE(ss.sql_hash_value, 0, prev_hash_value, sql_hash_value)=sa.hash_value
and pr.spid=&spid
查看歷史session_id的SQL來自哪個IP(當然這是個誤解，都是歷史的了，怎么可能還查到spid，其實查看trace文件名就可以知道spid,trace文件里面有sid和具體sql，如果trace存在incident，那trace就看不到具體sql，但是可以在incident文件中看到具體的sql，如DW_ora_17751.trc中17751就是spid，里面有這樣的內容Incident 115 created, dump file: /XX/incident/incdir_115/DW_ora_17751_i115.trc，那么在DW_ora_17751_i115.trc就可以看到具體的sql語句)
DB_ora_29349.trc中出現如下
*** SESSION ID:(5057.12807) 2016-10-26 14:45:52.726
通過表V$ACTIVE_SESSION_HISTORY來查，如下
select a.sql_id,a.machine,a.* from V$ACTIVE_SESSION_HISTORY a where a.session_id=5057 and a.SESSION_SERIAL#=12807
查詢上面的machine的IP是多少
select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal from v$session s,v$process p where s.paddr=p.addr and s.machine='localhost'
通過上面的spid在oracle服務器上執行netstat -anp |grep spid即可
[oracle@dwdb trace]$ netstat -anp |grep 17630
tcp 210 0 192.168.64.228:11095 192.168.21.16:1521 ESTABLISHED 17630/oracleDB
tcp 0 0 ::ffff:192.168.64.228:1521 ::ffff:192.168.64.220:59848 ESTABLISHED 17630/oracleDB
出現兩個，說明來自220，連接了228數據庫服務器，但是又通過228服務器的dblink去連接了16服務器
查詢死鎖堵塞的會話sid
最簡單的一個SQL
select * from V$SESSION_BLOCKERS
select * from dba_waiters
最常用的一個SQL
select sid,status,LOGON_TIME,sql_id,blocking_session "死鎖直接源",FINAL_BLOCKING_SESSION "死鎖最終源",event,seconds_in_wait "會話鎖住時間_S",LAST_CALL_ET "會話STATUS持續時間_S" from v$session where state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID'
可以把兩者SID放入v$session，發現LOGON_TIME字段FINAL_BLOCKING_SESSION比SID要早
BLOCKING_SESSION:Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID.
FINAL_BLOCKING_SESSION:Session identifier of the blocking session. This column is valid only if FINAL_BLOCKING_SESSION_STATUS has the value VALID.
如果遇到RAC環境，一定要用gv$來查，并且執行alter system kill session 'sid,serial#'要到RAC對應的實例上去執行
把上面被堵塞會話的sid代入如下語句，可以發現鎖住的對象和對象的哪一行(如果sid是堵塞源的會話，則row_wait_obj#=-1，表示鎖持有者，就是死鎖源了)
select s.sid,s.username,d.owner,d.object_name,s.row_wait_obj#,s.row_wait_row#,s.row_wait_file#,s.row_wait_block# from v$session s,dba_objects d where s.row_wait_obj#=d.object_id and s.sid in(XX,XX)
查詢鎖住的DDL對象
select d.session_id,s.SERIAL#,d.name from dba_ddl_locks d,v$session s where d.owner='MKLMIGEM' and d.SESSION_ID=s.sid
查詢超過兩個小時的不活動會話
select s.sid,s.serial#,p.spid,s.LOGON_TIME,s.LAST_CALL_ET,s.status,s.PROGRAM,s.CLIENT_IDENTIFIER,s.machine,s.terminal,s.action,s.PROCESS "客戶端機器進程號",s.osuser from v$session s,v$process p
where s.paddr=p.addr and s.sid in (select sid from v$session where machine<>&DB服務器名稱 and status='INACTIVE' and sql_id is null and LAST_CALL_ET>7200)
查詢堵塞別的會話超過30分鐘且自身是不活動的會話
select username,sid,serial#,status,seconds_in_wait,LAST_CALL_ET from v$session where sid in (select FINAL_BLOCKING_SESSION from v$session where state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID') and status='INACTIVE' and sql_id is null and seconds_in_wait>1800
查詢可能存在連接池空閑初始配置過大的連接（來自同一臺機器的同一個程序的狀態為INACTIVE的連接非常多）
select count(ss.SID),ss.machine,ss.status,ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.CLIENT_IDENTIFIER
from v$session ss group by ss.machine,ss.status,ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.CLIENT_IDENTIFIER having count(ss.SID)>10
查詢當前正在執行的sql
SELECT s.sid,s.serial#,s.username,spid,v$sql.sql_id,machine,s.terminal,s.program,sql_text
FROM v$process,v$session s,v$sql
WHERE addr=paddr and s.sql_id=v$sql.sql_id AND sql_hash_value=hash_value and s.STATUS='ACTIVE'
查詢正在執行的SCHEDULER_JOB
select owner,job_name,sid,b.SERIAL#,b.username,spid from ALL_SCHEDULER_RUNNING_JOBS,v$session b,v$process where session_id=sid and paddr=addr
查詢正在執行的dbms_job
select job,b.sid,b.SERIAL#,b.username,spid from DBA_JOBS_RUNNING a ,v$session b,v$process where a.sid=b.sid and paddr=addr
查詢一個會話session、process平均消耗多少PGA內存，查看下面avg_used_M值
select round(sum(pga_used_mem)/1024/1024,0) total_used_M, round(sum(pga_used_mem)/count(1)/1024/1024,0) avg_used_M,
round(sum(pga_alloc_mem)/1024/1024,0) total_alloc_M, round(sum(pga_alloc_mem)/count(1)/1024/1024,0) avg_alloc_M from v$process;
TOP 10 執行次數排序
select *
from (select executions,username,PARSING_USER_ID,sql_id,sql_text
from v$sql,dba_users where user_id=PARSING_USER_ID order by executions desc)
where rownum <=5;
TOP 10 物理讀排序（消耗IO排序，即最差性能SQL、低效SQL排序）
select *
from (select DISK_READS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text
from v$sql,dba_users where user_id=PARSING_USER_ID order by DISK_READS desc)
where rownum <=5;
（不要使用DISK_READS/ EXECUTIONS來排序，因為任何一條語句不管執行幾次都會耗邏輯讀和cpu，可能不會耗物理讀（遇到LRU還會耗物理讀，LRU規則是執行最不頻繁的且最后一次執行時間距離現在最久遠的就會被交互出buffer cache），是因為buffer cache存放的是數據塊，去數據塊里找行一定會消耗cpu和邏輯讀的。Shared pool執行存放sql的解析結果，sql執行的時候只是去share pool中找hash value，如果有匹配的就是軟解析。所以物理讀邏輯讀是在buffer cache中，軟解析硬解析是在shared pool）
TOP 10 邏輯讀排序（消耗內存排序）
select *
from (select BUFFER_GETS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text
from v$sql,dba_users where user_id=PARSING_USER_ID order by BUFFER_GETS desc)
where rownum <=5;
（不要使用BUFFER_GETS/ EXECUTIONS來排序，因為任何一條語句不管執行幾次都會耗邏輯讀和cpu，可能不會耗物理讀（遇到LRU還會耗物理讀，LRU規則是執行最不頻繁的且最后一次執行時間距離現在最久遠的就會被交互出buffer cache），是因為buffer cache存放的是數據塊，去數據塊里找行一定會消耗cpu和邏輯讀的。Shared pool執行存放sql的解析結果，sql執行的時候只是去share pool中找hash value，如果有匹配的就是軟解析。所以物理讀邏輯讀是在buffer cache中，軟解析硬解析是在shared pool）
TOP 10 CPU排序(單位秒=cpu_time/1000000)
select *
from (select CPU_TIME/1000000,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text
from v$sql,dba_users where user_id=PARSING_USER_ID order by CPU_TIME/1000000 desc)
where rownum <=5;
（不要使用CPU_TIME/ EXECUTIONS來排序，因為任何一條語句不管執行幾次都會耗邏輯讀和cpu，可能不會耗物理讀（遇到LRU還會耗物理讀，LRU規則是執行最不頻繁的且最后一次執行時間距離現在最久遠的就會被交互出buffer cache），是因為buffer cache存放的是數據塊，去數據塊里找行一定會消耗cpu和邏輯讀的。Shared pool執行存放sql的解析結果，sql執行的時候只是去share pool中找hash value，如果有匹配的就是軟解析。所以物理讀邏輯讀是在buffer cache中，軟解析硬解析是在shared pool）
查詢等待事件
select event,sum(decode(wait_time,0,0,1)) "之前等待次數", sum(decode(wait_time,0,1,0)) "正在等待次數",count(*) from v$session_wait group by event order by 4 desc
查詢當前等待事件對應的對象
select distinct
wait_class#,wait_class from
v$session_wait_class order by
1
以上sql發現wait_class#=6的是空閑等待
select * from
(select sid,event,p1text,p1,p2text,p2,p3text,p3,WAIT_TIME,SECONDS_IN_WAIT,wait_class#
from
v$session_wait where wait_class# <>
6 order by
wait_time desc)
where rownum
<=10;
能查出等待的對象是否來自數據文件（如果以上查到p1text是file#或file number）
select * from
(select owner,segment_name,segment_type,block_id,bytes
from
dba_extents where file_id=p1 and
block_id<p2 order="" by="" block_id="" desc)
where rownum<2
把上面第二個sql結果的p1、p2值代入上述sql的file_id、block_id
通過AWR的top
sql或v$sql.sql_text查看是否有該對象的語句，檢查該語句的執行計劃就可以查出問題所在
查詢當前正在消耗臨時空間的sql語句
Select distinct
se.username,
se.sid,
su.blocks
* to_number(rtrim(p.value))/1024/1024 as space_G,
su.tablespace,
sql_text
from
V$TEMPSEG_USAGE su, v$parameter p,
v$session se, v$sql s
where
p.name
=
'db_block_size'
and
su.session_addr=se.saddr
and
su.sqlhash=s.hash_value
and
su.sqladdr=s.address
and
se.STATUS='ACTIVE'
或
select
v$sql.sql_id,v$sql.sql_fulltext,swa.TEMPSEG_SIZE/1024/1024
TEMPSEG_M,
swa.* from v$sql_workarea_active swa,v$sql
where
swa.sql_id=v$sql.sql_id
and
swa.NUMBER_PASSES>0
查詢因PGA不足而使用臨時表空間的最頻繁的10條SQL語句
select * from
(
select OPERATION_TYPE,ESTIMATED_OPTIMAL_SIZE,ESTIMATED_ONEPASS_SIZE,
sum(OPTIMAL_EXECUTIONS) optimal_cnt,sum(ONEPASS_EXECUTIONS) as onepass_cnt,
sum(MULTIPASSES_EXECUTIONS) as mpass_cnt,s.sql_text
from V$SQL_WORKAREA swa, v$sql s
where swa.sql_id=s.sql_id
group by OPERATION_TYPE,ESTIMATED_OPTIMAL_SIZE,ESTIMATED_ONEPASS_SIZE,sql_text
having sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0
order by sum(ONEPASS_EXECUTIONS) desc
)
where rownum<10
查詢正在消耗PGA的SQL
select s.sql_text, sw.EXPECTED_SIZE, sw.ACTUAL_MEM_USED,sw.NUMBER_PASSES, sw.TEMPSEG_SIZE
from v$sql_workarea_active sw, v$sql s where sw.sql_id=s.sql_id;
查詢需要使用綁定變量的sql,10G以后推薦第二種
（任何一條執行過的語句不管執行了幾次在V$SQL中都只有一條記錄，V$SQL中會記錄執行了幾次。兩條一模一樣的語句但是在不同的schema下執行的兩種結果，如select * from t1.test在sye、system下執行則V$SQL只有一條記錄(誰先執行則PARSING_SCHEMA_NAME顯示誰)。如在sys和system都執行select * from test則V$SQL中有兩條記錄，兩條記錄的CHILD_NUMBER和PARSING_SCHEMA_NAME不一樣。同一個用戶下執行一樣的語句如果大小寫不一樣或加了hint的話則會出現多個V$SQL記錄，說明V$SQL對應的sql語句必須一模一樣，如果alter system flush shared_pool（主站慎用）后再執行一樣的語句，發現語句在V$SQL中的SQL_ID和HASH_VALUE與之前的一樣，說明SQL_ID和HASH_VALUE應該是oracle自己的一套算法來的，只是根據sql語句內容來進行轉換，sql語句不變則SQL_ID和HASH_VALUE也不變。）
第一種
select * from (
select count(*),sql_id, substr(sql_text,1,40)
from v$sql
group by sql_id, substr(sql_text,1,40) having count(*) > 10 order by count(*) desc) where rownum<10
第二種
count(1)>10表示類語句運行了10次以上
select sql_id, FORCE_MATCHING_SIGNATURE, sql_text
from v$SQL
where FORCE_MATCHING_SIGNATURE in
(select /*+ unnest */
FORCE_MATCHING_SIGNATURE
from v$sql
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 10)
查看數據文件可用百分比(dba_free_space并不會包含所有file_id，如果該數據文件滿了，則dba_free_space.file_id沒有該數據文件，所以以下sql中a.file_id=b.file_id的條件過濾后是不會有所有file_id的)
select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,
ROUND(b.MAXBYTES/1024/1024/1024,2) ||'G' "文件最大可用總容量",
ROUND(b.bytes/1024/1024/1024,2) ||'G' "文件總容量",
ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件已用容量",
ROUND(sum(nvl(a.bytes,0))/1024/1024/1024,2)||'G' "文件可用容量",
ROUND(sum(nvl(a.bytes,0))/(b.bytes),2)*100||'%' "文件可用百分比"
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTES
order by b.tablespace_name;
--如下為標準版
select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,
ROUND(b.MAXBYTES/1024/1024/1024,2) ||'G' "文件最大可用總容量",
ROUND(b.bytes/1024/1024/1024,2) ||'G' "文件當前總容量",
ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件當前已用容量",
ROUND((decode(AUTOEXTENSIBLE,'NO',b.BYTES,b.MAXBYTES)+sum(nvl(a.bytes,0))-b.bytes)/1024/1024/1024,2)||'G' "文件可用容量",
ROUND((decode(AUTOEXTENSIBLE,'NO',b.BYTES,b.MAXBYTES)+sum(nvl(a.bytes,0))-b.bytes)/(decode(AUTOEXTENSIBLE,'NO',b.BYTES,b.MAXBYTES)),2)*100||'%' "文件可用百分比"
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTES
order by decode(AUTOEXTENSIBLE,'NO',b.BYTES,b.MAXBYTES)+sum(nvl(a.bytes,0))-b.bytes;
查看數據庫文件的實際總量，單位G
select a.datafile_size+b.tempfile_size-c.free_size from
(select sum(bytes/1024/1024/1024) datafile_size from dba_data_files ) a,
(select sum(bytes/1024/1024/1024) tempfile_size from dba_temp_files ) b,
(select sum(bytes/1024/1024/1024) free_size from dba_free_space ) c
查看表空間可用百分比（dba_free_space不會包含所有tablespace，如果一個表空間的數據文件都滿了，則這個表空間不會出現在dba_free_space中）
select b.tablespace_name,a.maxsize max_M,a.total total_M,b.free free_M,round((b.free/a.total)*100) "% Free" from
(select tablespace_name, sum(bytes/(1024*1024)) total ,sum(MAXBYTES/(1024*1024)) maxsize from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name order by "% Free";
--如下為標準版
select b.tablespace_name,a.maxsize max_M,a.total total_M,b.free free_M,round(((a.maxsize+b.free-a.total)/a.maxsize)*100) "% Free" from
(select tablespace_name, sum(bytes/(1024*1024)) total ,sum((decode(AUTOEXTENSIBLE,'NO',BYTES,MAXBYTES))/(1024*1024)) maxsize from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name order by "% Free";
查看臨時表空間使用率
SELECT temp_used.tablespace_name,round(total),used,
round(total - used) as "Free",
round(nvl(total-used, 0) * 100/total,1) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used
FROM GV$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(decode(autoextensible,'YES',MAXBYTES,bytes))/1024/1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name
或
SELECT a.tablespace_name, round(a.BYTES/1024/1024) total_M, round(a.bytes/1024/1024 - nvl(b.bytes/1024/1024, 0)) free_M,
round(b.bytes/1024/1024) used,round(b.using/1024/1024) using
FROM (SELECT tablespace_name, SUM (decode(autoextensible,'YES',MAXBYTES,bytes)) bytes FROM dba_temp_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (bytes_cached) bytes,sum(bytes_used) using FROM v$temp_extent_pool GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
查詢undo表空間使用情況
select tablespace_name,status,sum(bytes)/1024/1024 M from dba_undo_extents group by tablespace_name,status
查詢使用undo比較多的SQL
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d
WHERE a.statistic# = b.statistic#
AND a.inst_id = c.inst_id
AND a.sid = c.sid
AND c.inst_id = d.inst_id
AND c.saddr = d.ses_addr
AND a.statistic# = 284
AND a.value>0
ORDER BY a.value DESC
估計undo需要多大
SELECT (UR * (UPS * DBS)) AS "Bytes"
FROM (select max(tuned_undoretention) AS UR from v$undostat),
(SELECT undoblks/((end_time-begin_time)*86400) AS UPS
FROM v$undostat
WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));
產生undo的當前活動會話是哪些
即dba_undo_extents.status=active對應的v$session.sid
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d
WHERE a.statistic# = b.statistic#
AND a.inst_id = c.inst_id
AND a.sid = c.sid
AND c.inst_id = d.inst_id
AND c.saddr = d.ses_addr
AND a.statistic# = 284
AND a.value>0
ORDER BY a.value DESC
查看ASM磁盤組使用率
select name,round(total_mb/1024) "總容量",round(free_mb/1024) "空閑空間",round((free_mb/total_mb)*100) "可用空間比例" from gv$asm_diskgroup
統計每個用戶使用表空間率
SELECT c.owner "用戶",
a.tablespace_name "表空間名",
total/1024/1024 "表空間大小M",
free/1024/1024 "表空間剩余大小M",
( total - free )/1024/1024 "表空間使用大小M",
Round(( total - free ) / total, 4) * 100 "表空間總計使用率 %",
c.schemas_use/1024/1024 "用戶使用表空間大小M",
round((schemas_use)/total,4)*100 "用戶使用表空間率 %"
FROM (SELECT tablespace_name,
Sum(bytes) free
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
Sum(bytes) total
FROM DBA_DATA_FILES
GROUP BY tablespace_name) b,
(Select owner ,Tablespace_Name,
Sum(bytes) schemas_use
From Dba_Segments
Group By owner,Tablespace_Name) c
WHERE a.tablespace_name = b.tablespace_name
and a.tablespace_name =c.Tablespace_Name
order by "用戶","表空間名"
查看閃回區\快速恢復區空間使用率
select sum(percent_space_used)||'%' "已使用空間比例" from V$RECOVERY_AREA_USAGE
或
select round(100*(a.space_used/space_limit),2)||'%' "已使用空間比例",a.* from v$recovery_file_dest a;
查看僵死進程，分兩種（一種是會話不在的，另一種是會話標記為killed的但是會話還在的）
alter system kill session一執行則session即標記為KILLED，但是如果會話產生的數據量大則這個kill可能會比較久，在這個過程中session標記為KILLED但是這個會話還在V$session中，則V$session.paddr還在，所以可以匹配到V$process.addr，所以process進程還在；當kill過程執行完畢，則這個會話即不在V$session中
會話不在的
select * from v$process where addr not in (select paddr from v$session) and pid not in (1,17,18)
會話還在的，但是會話標記為killed
select * from v$process where addr in (select paddr from v$session where status='KILLED')
再根據上述結果中的SPID通過如下命令可以查看到process的啟動時間
ps auxw|head -1;ps auxw|grep SPID
查看行遷移或行鏈接的表
select * From dba_tables where nvl(chain_cnt,0)<>0
chain_cnt ：Number of rows in the table that are chained from one data block to another or that have migrated to a new block, requiring a link to preserve the old rowid. This column is updated only after you analyze the table.
數據緩沖區命中率（百分比小于90就要加大db_cache_size）
SELECT a.VALUE+b.VALUE logical_reads, c.VALUE phys_reads,
round(100*(1-c.value/(a.value+b.value)),2)||'%' hit_ratio
FROM v$sysstat a,v$sysstat b,v$sysstat c
WHERE a.NAME='db block gets'
AND b.NAME='consistent gets'
AND c.NAME='physical reads';
或
SELECT DB_BLOCK_GETS+CONSISTENT_GETS Logical_reads,PHYSICAL_READS phys_reads,
round(100*(1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS))),2)||'%' "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS WHERE NAME='DEFAULT';
共享池命中率（百分比小于90就要加大shared_pool_size）
以下兩者應該都可以，看個人怎么理解
select sum(pinhits)/sum(pins)*100 from v$librarycache;
select sum(pinhits-reloads)/sum(pins)*100 from v$librarycache;
查詢歸檔日志切換頻率
select sequence#,to_char(first_time,'yyyymmdd_hh34:mi:ss')
firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from
v$log_history where first_time > sysdate - 3 order by first_time,minutes;
或
select sequence#,to_char(first_time,'yyyy-mm-dd hh34:mi:ss') First_time,First_change#,switch_change# from
v$loghist where first_time>sysdate-3 order by 1;
或
SELECT TO_CHAR(first_time, 'MM/DD') DAY,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '00', 1, 0)) H00,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '01', 1, 0)) H01,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '02', 1, 0)) H02,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '03', 1, 0)) H03,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '04', 1, 0)) H04,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '05', 1, 0)) H05,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '06', 1, 0)) H06,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '07', 1, 0)) H07,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '08', 1, 0)) H08,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '09', 1, 0)) H09,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '10', 1, 0)) H10,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '11', 1, 0)) H11,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '12', 1, 0)) H12,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '13', 1, 0)) H13,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '14', 1, 0)) H14,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '15', 1, 0)) H15,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '16', 1, 0)) H16,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '17', 1, 0)) H17,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '18', 1, 0)) H18,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '19', 1, 0)) H19,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '20', 1, 0)) H20,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '21', 1, 0)) H21,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '22', 1, 0)) H22,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '23', 1, 0)) H23,
COUNT(*) TOTAL
FROM (SELECT ROWNUM RN, FIRST_TIME FROM V$LOG_HISTORY WHERE first_time>sysdate-18
and FIRST_TIME>ADD_MONTHS(SYSDATE,-1) ORDER BY FIRST_TIME)
GROUP BY TO_CHAR(first_time, 'MM/DD')
ORDER BY MIN(RN);
查詢lgwr進程寫日志時每執行一次lgwr需要多少秒，在state是waiting的情況下，某個等待編號seq#下，seconds_in_wait達多少秒，就是lgwr進程寫一次IO需要多少秒
select event,state,seq#,seconds_in_wait,program from v$session where program like '%LGWR%' and state='WAITING'
查詢沒有索引的表
Select table_name from user_tables where table_name not in (select table_name from user_indexes)
Select table_name from user_tables where table_name not in (select table_name from user_ind_columns)
查詢一個AWR周期內的平均session數、OS平均負載、平均db time、平均每秒多少事務
select to_char(max(BEGIN_TIME),'yyyy-mm-dd hh34:mi')||to_char(max(end_time),'--hh34:mi') time,
snap_id,
trunc(sum(case metric_name when 'Session Count' then average end),2) sessions,
trunc(sum(case metric_name when 'Current OS Load' then average end),2) OS_LOAD,
(trunc(sum(case metric_name when 'Database Time Per Sec' then average end),2)/100)*(ceil((max(end_time)-max(BEGIN_TIME))*24*60*60)) Database_Time_second,
trunc(sum(case metric_name when 'User Transaction Per Sec' then average end),2) User_Transaction_Per_Sec
from dba_hist_sysmetric_summary
group by snap_id
order by snap_id;
--Database Time Per Sec對應值的單位是百分一秒/每秒
--(/100)*(ceil((max(end_time)-max(BEGIN_TIME))*24*60*60))是代表每個snap周期內的總秒數，oracle 兩個時間相減默認的是天數,*24*60*60 為相差的秒數
--這個SQL查到的DB TIME比較準確，和awr上面的db time比較一致
查詢產生熱塊較多的對象
x$bh .tch(Touch)表示訪問次數越高，熱點快競爭問題就存在
SELECT e.owner, e.segment_name, e.segment_type
FROM dba_extents e,
(SELECT *
FROM (SELECT addr,ts#,file#,dbarfil,dbablk,tch
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM < 11) b
WHERE e.relative_fno = b.dbarfil
AND e.block_id <= b.dbablk
AND e.block_id + e.blocks > b.dbablk;
手工創建快照的語句
exec dbms_workload_repository.create_snapshot;
AWR設置每隔30分鐘收集一次報告，保留14天的報告
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>14*24*60, interval=>30);
select * from dba_hist_wr_control;
AWR基線查看和創建
select * from dba_hist_baseline;
exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id=>7550,end_snap_id=>7660,baseline_name=>'am_baseline');
導出AWR報告的SQL語句
select * from dba_hist_snapshot
select * from table(dbms_workload_repository.awr_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid))
select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid, DBID, INSTANCE_NUMBER, startsnapid,endsnapid));
導出最新ADDM的報告（需要sys用戶）
select dbms_advisor.get_task_report(task_name) from dba_advisor_tasks
where task_id =(
select max(t.task_id) from dba_advisor_tasks t, dba_advisor_log l where
t.task_id=l.task_id and t.advisor_name='ADDM' and l.status='COMPLETED' );
select task_id,task_name,description from dba_advisor_tasks order by 1 desc
select dbms_advisor.get_task_report(task_name) from dba_advisor_tasks where task_id =XX
查詢某個SQL的執行計劃
select * from table(dbms_xplan.display_cursor('sql_id',0,'advanced'));
上面的0表示v$sql.child_number為0，如果一個sql_id在v$sql中有多行說明有多個child_number，要看哪兒child_number的執行計劃，就寫哪個的值，比如要看child_number為2的執行計劃，就把上面sql的0改為2
官方文檔對display_cursor這個函數的說明里面沒有advanced這個參數值，只有BASIC、TYPICAL、ALL這幾個，不過實踐中發現advanced這個參數值顯示的內容比這幾個參數值顯示的都多
含順序的
select * from table(xplan.display_cursor('v$sql.sql_id',0,'advanced'));
不過要先創建xplan包，再執行
SQL> CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN;
SQL> grant execute on sys.xplan to public;
查詢Rman的配置信息
SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION;
查詢Rman備份集詳細信息（未過期的，過期并已刪除的查不到）
SELECT B.RECID BackupSet_ID,
A.SET_STAMP,
DECODE (B.INCREMENTAL_LEVEL,
'', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
1, 'Incr-1級',
0, 'Incr-0級',
B.INCREMENTAL_LEVEL)
"Type LV",
B.CONTROLFILE_INCLUDED "包含CTL",
DECODE (A.STATUS,
'A', 'AVAILABLE',
'D', 'DELETED',
'X', 'EXPIRED',
'ERROR')
"STATUS",
A.DEVICE_TYPE "Device Type",
A.START_TIME "Start Time",
A.COMPLETION_TIME "Completion Time",
A.ELAPSED_SECONDS "Elapsed Seconds",
A.BYTES/1024/1024/1024 "Size(G)",
A.COMPRESSED,
A.TAG "Tag",
A.HANDLE "Path"
FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'
ORDER BY A.COMPLETION_TIME DESC;
查詢Rman備份進度
SELECT SID, SERIAL#, opname,ROUND(SOFAR/TOTALWORK*100)||'%' "%_COMPLETE",
TRUNC(elapsed_seconds/60) || ':' || MOD(elapsed_seconds,60) elapsed,
TRUNC(time_remaining/60) || ':' || MOD(time_remaining,60) remaining,
CONTEXT,target,SOFAR, TOTALWORK
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;
查詢執行過全表掃描的sql語句的SQL_ID和sql_fulltext
select s.sid,s.serial#,s.inst_id,s.sql_id,s.username,s.target,s.ELAPSED_SECONDS,s.START_TIME,s.LAST_UPDATE_TIME,v.sql_fulltext
from gv$session_longops s,gv$sql v
where s.OPNAME = 'Table Scan'
and s.SQL_PLAN_OPERATION = 'TABLE ACCESS'
and s.SQL_PLAN_OPTIONS = 'FULL'
and s.sql_id=v.sql_id
order by s.LAST_UPDATE_TIME desc
查詢死事務需要多長的回滾時間
X$KTUXE：[K]ernel [T]ransaction [U]ndo Transa[x]tion [E]ntry (table)
X$KTUXE表的一個重要功能是，可以獲得無法通過v$transaction來觀察的死事務信息，當一個數據庫發生異常中斷，或者進行延遲事務恢復時，數據庫啟動后，無法通過V$TRANSACTION來觀察事務信息，但是X$KTUXE可以幫助我們獲得這些信息。該表中的KTUXECFL代表了事務的Flag標記，通過這個標記可以找到那些Dead事務：
SQL> select distinct KTUXECFL,count(*) from x$ktuxe group by KTUXECFL;
KTUXECFL COUNT(*)
------------------------ ----------
DEAD 1
NONE 2393
KTUXESIZ用來記錄事務使用的回滾段塊數，可以通過觀察這個字段來評估恢復進度,例如如下事務回滾經過測算需要大約3小時：：
SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL ='DEAD';
ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ
---------------- ---------- ---------- ---------- ----------
FFFFFFFF7D07B91C 10 39 2567412 1086075
SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL ='DEAD';
ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ
---------------- ---------- ---------- ---------- ----------
FFFFFFFF7D07B91C 10 39 2567412 1086067
SQL> declare
l_start number;
l_end number;
begin
select ktuxesiz into l_start from x$ktuxe where KTUXEUSN=10 and KTUXESLT=39;
dbms_lock.sleep(60);
select ktuxesiz into l_end from x$ktuxe where KTUXEUSN=10 and KTUXESLT=39;
dbms_output.put_line('time_H:'|| round(l_end/(l_start -l_end)/60,2));
end;
/
time_H:3
把XXX用戶下面的某些YYY表賦權給user,XXX\YYY要大寫
set serveroutput on
--XXX要大寫
declare tablename varchar2(200);
begin
for x IN (SELECT * FROM dba_tables where owner='XXX' and table_name like '%YYY%') loop
tablename:=x.table_name;
dbms_output.put_line('GRANT SELECT ON XXX.'||tablename||' to user');
EXECUTE IMMEDIATE 'GRANT SELECT ON XXX.'||tablename||' TO user';
end loop;
end;
Oracle查出一個用戶具有的所有系統權限和對象權限
系統權限（和用戶自己查詢select * from session_privs的結果一致）
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = '用戶名'
UNION ALL
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN
(SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = '用戶名');
對象權限（和用戶自己查詢select * FROM TABLE_PRIVILEGES where GRANTEE='當前用戶'的結果一致）
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = '用戶名'
UNION ALL
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE IN
(SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = '用戶名');
查詢某個用戶擁有的角色
select * from dba_role_privs where GRANTEE='用戶名';
查詢擁有DBA角色權限的用戶
select * from dba_role_privs where GRANTED_ROLE='DBA';
查詢某個角色擁有的系統權限
select * from ROLE_SYS_PRIVS where role='角色名'
清除某個SQL的執行計劃
Exec DBMS_SHARED_POOL.PURGE('v$sqlarea.ADDRESS,v$sqlarea.HASH_VALUE','c')
查詢密碼是否有過期限制，默認是180天，一般修改為unlimited
select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME like 'PASSWORD%';
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED
查詢和修改隱含參數（必須在sysdba權限下操作）
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx and a.ksppinm like '%_small_table_threshold%'
alter system set "_small_table_threshold"=value scope=both sid='*';
不加sid則說明在默認在RAC的所有實例中修改
需要注意的是一定要加上雙引號, 另外引號內不能有空格, 只能包含參數的名字
評估PGA該設置多少
select PGA_TARGET_FOR_ESTIMATE from (select * from V$PGA_TARGET_ADVICE where ESTD_OVERALLOC_COUNT=0 order by 1) where rownum=1;
評估SGA該設置多少
select SGA_SIZE from (select * from V$SGA_TARGET_ADVICE where ESTD_DB_TIME_FACTOR=1 order by 1) where rownum=1;
查看shared pool還剩多少
select * from v$sgastat where name='free memory' and pool='shared pool';
統計所有表的容量大小(含分區字段、LOB字段)
一般先執行select distinct SEGMENT_TYPE from dba_segments where owner<>'SYS' and tablespace_name<>'SYSAUX'查看到所有的segment_type
一般如下SQL就足夠了
SELECT
owner,table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type = 'TABLE'
UNION ALL
SELECT s.segment_name table_name, pt.owner, s.bytes
FROM dba_segments s, dba_part_tables pt
WHERE s.segment_name = pt.table_name
AND s.owner = pt.owner
AND s.segment_type = 'TABLE PARTITION'
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type = 'INDEX'
UNION ALL
SELECT pi.table_name, pi.owner, s.bytes
FROM dba_part_indexes pi, dba_segments s
WHERE s.segment_name = pi.index_name
AND s.owner = pi.owner
AND s.segment_type = 'INDEX PARTITION'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX'
union all
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOB PARTITION'
)
GROUP BY owner,table_name
HAVING SUM(bytes)/1024/1024 > 10
ORDER BY SUM(bytes) desc
查看當前會話的SID
select * from V$MYSTAT where rownum<2
查詢某個SID的某個統計信息，比如consistent gets一致性讀
select A.SID,A.STATISTIC#,A.VALUE SID_VALUE,B.NAME,B.VALUE ALL_SID_VALUE from V$SESSTAT A ,V$SYSSTAT B where A.STATISTIC#=B.STATISTIC#
and A.SID=1187 and B.NAME='consistent gets'
V$SYSSTAT統計整個DB的統計信息，V$SYSSTAT已經取代了V$STATNAME，并且多了VALUE這一列
V$SESSTAT統計每個用戶的統計信息
查詢某個SID的某個等待事件的信息，比如log file sync
select A.SID,A.EVENT,C.NAME,C.PARAMETER1,C.PARAMETER2,C.PARAMETER3,
A.TIME_WAITED SID_TIMEWAITED,B.TIME_WAITED ALL_SID_TIMEWAITED,A.TOTAL_WAITS SID_TOTALWAITS,B.TOTAL_WAITS ALL_SID_TOTALWAITS
from V$SESSION_EVENT A ,V$SYSTEM_EVENT B,V$EVENT_NAME C where A.EVENT=B.EVENT and A.EVENT=C.NAME and A.SID=1 and C.NAME='log file sync'
V$SESSION_EVENT描述每個用戶的等待事件信息
V$SYSTEM_EVENT描述整個DB等待事件信息
V$EVENT_NAME描述等待事件信本身的信息(比如V$ACTIVE_SESSION_HISTORY的P1TEXT、P2TEXT、P2TEXT匹配V$EVENT_NAME的PARAMETER1、PARAMETER2、PARAMETER3)
RAC跨節點殺會話
alter system kill session 'SID,serial#,@1' --殺掉1節點的進程
alter system kill session 'SID,serial#,@2' --殺掉2節點的進程
Truncate分區的SQL
ALTER TABLE table_name TRUNCATE PARTITION p1 DROP STORAGE UPDATE GLOBAL INDEXES;
Drop分區的SQL
ALTER TABLE table_name DROP PARTITION p1 UPDATE GLOBAL INDEXES;
DATAGUARD主備延遲多少時間的查詢方法
備庫sqlplus>select value from v$dataguard_stats where name='apply lag'
或
備庫sqlplus>select ceil((sysdate-next_time)*24*60) "M" from v$archived_log where applied='YES' AND SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE applied='YES');
查看某個包或存儲過程是否正在被調用,如果如下有結果，則此時不能編譯，否則會鎖住
select * from V$DB_OBJECT_CACHE where pin>0 and name=upper('XX')
