常用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#;

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#;

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;

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

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';