常用SQL语句整理
-----表空间使用率-----
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 --如果有临时表空间
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;
-----查询所有依赖对象（存储过程，触发器等基于那张表）的信息-----
select * from dba_dependencies where referenced_owner='USERNAME';
-----查询表上的外键关系-----
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;
-----查询所有主外键关系-----
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;
-----查看没有主键的表-----
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自动任务结果-----
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;
如果没有启用用，1级备份的时候会比较所有的数据块文件，所以备份时间并不会比0级备份少，甚至可能会更长。可通过如下语句启用：
alter system set DB_CREATE_FILE_DEST='/home/oracle/backup' scope=both; --配置追踪文件存放位置
alter database enable block change tracking; ---开启块跟踪
-----查看正在执行的sql语句-----
select a.program 请求程序,
a.username 登录oracle用户名,
a.sid oracleSID,
a.SERIAL#,
a.machine 计算机名,
b.spid 操作系统ID,
c.sql_text 正在执行的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 登录机器用户名,
p.spid 操作系统ID,
s.machine 机器名,
s.terminal 终端用户名,
o.object_name 被锁对象名,
s.logon_time 登录数据库时间,
q.sql_id SQLID,
q.sql_text SQL语句
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隐含参数-----
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;
-----查看那个用户那个进程造成死锁-----
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#;
-----根据操作系统进程号查询正在执行的SQL语句-----
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;
-----观察等待事件总数及大致分类-----
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;
-----根据latch地址确定数据块-----
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);
-----根据数据块确认具体对象-----
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';
-----查询数据库中的热点块-----
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历史执行时间-----
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';
