常用SQL語(yǔ)句整理
-----表空間使用率-----
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
"USED_RATE(%)",
FREE_SPACE || 'M' "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 --如果有臨時(shí)表空間
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
NVL (FREE_SPACE, 0) || 'M' "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 1;
-----查詢所有依賴對(duì)象（存儲(chǔ)過(guò)程，觸發(fā)器等基于那張表）的信息-----
select * from dba_dependencies where referenced_owner='USERNAME';
-----查詢表上的外鍵關(guān)系-----
select A.* from user_constraints A, user_constraints B WHERE b.table_name = 'TABLENAME' and a.constraint_type = 'R' and a.r_constraint_name = b.constraint_name;
-----查詢所有主外鍵關(guān)系-----
select a.table_name 主表,
b.table_name 子表,
column_name 鍵,
substr(position,1,1) P
from user_constraints a, user_constraints b, user_cons_columns c
where a.constraint_name = b.r_constraint_name
and a.constraint_name = c.constraint_name
order by 1, 2, 4;
-----查看沒(méi)有主鍵的表-----
SELECT *
FROM dba_tables A
WHERE owner='CCPS'
AND NOT EXISTS (
SELECT *
FROM dba_constraints b
WHERE A .table_name = b.table_name
AND b.constraint_type = 'P'
);
-----session_cached_cursor和open_cursor使用率-----
SELECT 'session_cached_cursors' PARAMETER, LPAD(VALUE, 5) VALUE, DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * USED / VALUE) || '%') USAGE
FROM (
SELECT MAX(S.VALUE) USED
FROM V$STATNAME N, V$SESSTAT S
WHERE N.NAME = 'session cursor cache count'
AND S.STATISTIC# = N.STATISTIC#
), (
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'session_cached_cursors'
)
UNION ALL
SELECT 'open_cursors', LPAD(VALUE, 5), TO_CHAR(100 * USED / VALUE) || '%'
FROM (
SELECT MAX(SUM(S.VALUE)) USED
FROM V$STATNAME N, V$SESSTAT S
WHERE N.NAME IN (
'opened cursors current'
,'session cursor cache count'
)
AND S.STATISTIC# = N.STATISTIC#
GROUP BY S.SID
), (
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'open_cursors'
);
-----查詢所有表和索引的大小-----
select segment_name,sum(bytes)/1024/1024/1024 size_GB from dba_extents where owner='USERNAME' group by segment_name order by 2 desc;
-----查看oracle自動(dòng)任務(wù)結(jié)果-----
select f.task_name,o.type,o.attr1,attr3,message,more_info,execution_type,e.execution_start,e.execution_end from dba_advisor_findings f,dba_advisor_objects o,dba_advisor_executions e where o.task_id=f.task_id and o.object_id=f.object_id and f.task_id=e.task_id and e.execution_start >sysdate -1;
select dbms_sqltune.report_auto_tuning_task from dual;
-----查看oracle是否啟用塊跟蹤-----
select status from v$block_change_tracking;
如果沒(méi)有啟用用，1級(jí)備份的時(shí)候會(huì)比較所有的數(shù)據(jù)塊文件，所以備份時(shí)間并不會(huì)比0級(jí)備份少，甚至可能會(huì)更長(zhǎng)?？赏ㄟ^(guò)如下語(yǔ)句啟用：
alter system set DB_CREATE_FILE_DEST='/home/oracle/backup' scope=both; --配置追蹤文件存放位置
alter database enable block change tracking; ---開(kāi)啟塊跟蹤
-----查看正在執(zhí)行的sql語(yǔ)句-----
select a.program 請(qǐng)求程序,
a.username 登錄oracle用戶名,
a.sid oracleSID,
a.SERIAL#,
a.machine 計(jì)算機(jī)名,
b.spid 操作系統(tǒng)ID,
c.sql_text 正在執(zhí)行的SQL,
c.SQL_ID SQLID
from v$session a, v$process b, v$sqlarea c
where a.paddr = b.addr
and a.sql_hash_value = c.hash_value
and a.username is not null;
-----查看鎖定的表及SQL-----
SELECT l.session_id sid,
s.serial#,
l.locked_mode 鎖模式,
l.oracle_username 登錄用戶,
l.os_user_name 登錄機(jī)器用戶名,
p.spid 操作系統(tǒng)ID,
s.machine 機(jī)器名,
s.terminal 終端用戶名,
o.object_name 被鎖對(duì)象名,
s.logon_time 登錄數(shù)據(jù)庫(kù)時(shí)間,
q.sql_id SQLID,
q.sql_text SQL語(yǔ)句
FROM v$locked_object l, all_objects o, v$session s,v$process p,v$sql q
WHERE l.object_id = o.object_id
and s.paddr = p.addr
AND l.session_id = s.sid
and s.sql_hash_value = q.hash_value
ORDER BY sid, s.serial#;
-----查看oracle隱含參數(shù)-----
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm = '_optim_peek_user_binds';
修改：SQL> alter system set "_optimizer_max_permutations"=200 scope=both sid='*';
-----查看被鎖的表-----
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
-----查看那個(gè)用戶那個(gè)進(jìn)程造成死鎖-----
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
-----查出鎖表的sid, serial#,os_user_name, machine_name, os processes，terminal，鎖的type,mode-----
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,s.process,s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;
-----查看鎖定的表-----
SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial#;
-----根據(jù)操作系統(tǒng)進(jìn)程號(hào)查詢正在執(zhí)行的SQL語(yǔ)句-----
SELECT /*+ ORDERED */
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,
0, prev_hash_value,
sql_hash_value
),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = 10769))
ORDER BY piece ASC;
-----觀察等待事件總數(shù)及大致分類-----
select a.wait_class#,wait_class_id,wait_class,count(*)
from v$event_name a
group by wait_class#,wait_class_id,wait_class
order by wait_class#;
-----查詢latch地址-----
select addr,LATCH#,CHILD#,gets,misses,sleeps
from v$latch_children
where name = 'cache buffers chains'
and rownum < 21;
-----根據(jù)latch地址確定數(shù)據(jù)塊-----
select dbarfil,dbablk
from x$bh
where hladdr in
(select addr
from (select addr
from v$latch_children
order by sleeps desc)
where rownum < 1000);
-----根據(jù)數(shù)據(jù)塊確認(rèn)具體對(duì)象-----
select distinct a.owner,a.segment_name
from dba_extents a,
(select dbarfil,dbablk
from x$bh
where hladdr in
(select addr
from (select addr
from v$latch_children
order by sleeps desc)
where rownum < 20)
) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk
and a.block_id + a.blocks > b.dbablk
and a.owner='USERNAME';
-----查詢數(shù)據(jù)庫(kù)中的熱點(diǎn)塊-----
select /*+ rule */a.owner,a.object_name,b.tch
from dba_objects a,x$bh b
where a.data_object_id=b.obj
order by tch desc;
-----查看sql歷史執(zhí)行時(shí)間-----
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '14nrwtwftffq5'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
-----BLOCKING TREE-----
with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter from gv$session where blocking_instance is not null and blocking_session is not null)
select lpad(' ',2*(level-1))||waiter lock_tree from
(select * from lk
union all
select distinct 'root', blocker from lk
where blocker not in (select waiter from lk))
connect by prior waiter=blocker start with blocker='root';
