常用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) || '%') USAGEFROM ( 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 ALLSELECT '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 SQLIDfrom v$session a, v$process b, v$sqlarea cwhere a.paddr = b.addrand a.sql_hash_value = c.hash_valueand 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 qWHERE l.object_id = o.object_idand s.paddr = p.addrAND l.session_id = s.sidand s.sql_hash_value = q.hash_valueORDER 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.typeFROM v$session s, v$lock lWHERE s.sid = l.sidAND s.username IS NOT NULLORDER 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_timeFROM v$locked_object l, all_objects o, v$session sWHERE l.object_id = o.object_idAND l.session_id = s.sidORDER BY sid, s.serial#;
-----根据操作系统进程号查询正在执行的SQL语句-----SELECT /*+ ORDERED */sql_textFROM v$sqltext aWHERE (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 bWHERE b.paddr = (SELECT addrFROM v$process cWHERE c.spid = 10769))ORDER BY piece ASC;
-----观察等待事件总数及大致分类-----select a.wait_class#,wait_class_id,wait_class,count(*)from v$event_name agroup by wait_class#,wait_class_id,wait_classorder by wait_class#;
-----查询latch地址-----select addr,LATCH#,CHILD#,gets,misses,sleepsfrom 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_namefrom 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.dbablkand a.block_id + a.blocks > b.dbablkand 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.objorder 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_liofrom DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SSwhere sql_id = '14nrwtwftffq5'and ss.snap_id = S.snap_idand ss.instance_number = S.instance_numberand executions_delta > 0order by 1, 2, 3
-----BLOCKING TREE-----with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiterfrom 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 lkunion allselect distinct 'root', blocker from lkwhere blocker not in (select waiter from lk)) connect by prior waiter=blocker start with blocker='root';
-----查询所有依赖对象（存储过程，触发器等基于那张表）的信息-----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) || '%') USAGEFROM ( 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 ALLSELECT '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 SQLIDfrom v$session a, v$process b, v$sqlarea cwhere a.paddr = b.addrand a.sql_hash_value = c.hash_valueand 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 qWHERE l.object_id = o.object_idand s.paddr = p.addrAND l.session_id = s.sidand s.sql_hash_value = q.hash_valueORDER 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.typeFROM v$session s, v$lock lWHERE s.sid = l.sidAND s.username IS NOT NULLORDER 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_timeFROM v$locked_object l, all_objects o, v$session sWHERE l.object_id = o.object_idAND l.session_id = s.sidORDER BY sid, s.serial#;
-----根据操作系统进程号查询正在执行的SQL语句-----SELECT /*+ ORDERED */sql_textFROM v$sqltext aWHERE (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 bWHERE b.paddr = (SELECT addrFROM v$process cWHERE c.spid = 10769))ORDER BY piece ASC;
-----观察等待事件总数及大致分类-----select a.wait_class#,wait_class_id,wait_class,count(*)from v$event_name agroup by wait_class#,wait_class_id,wait_classorder by wait_class#;
-----查询latch地址-----select addr,LATCH#,CHILD#,gets,misses,sleepsfrom 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_namefrom 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.dbablkand a.block_id + a.blocks > b.dbablkand 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.objorder 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_liofrom DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SSwhere sql_id = '14nrwtwftffq5'and ss.snap_id = S.snap_idand ss.instance_number = S.instance_numberand executions_delta > 0order by 1, 2, 3
-----BLOCKING TREE-----with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiterfrom 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 lkunion allselect distinct 'root', blocker from lkwhere blocker not in (select waiter from lk)) connect by prior waiter=blocker start with blocker='root';
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
