常用 SQL

--========= 查询外键关联表（父表）
select distinct allc1.table_name      表名,
                allc1.constraint_name 外键名,
                dcc1.column_name      约束字段,
                --dccm.comments 字段中文名,
                allc2.table_name 外键关联表,
                dcc2.column_name 外键关联字段,
                dtc.comments     表中文名
--allc2.constraint_name 对应主键       
  from dba_constraints allc1
  Join dba_constraints allc2
    on allc1.r_constraint_name = allc2.constraint_name
  join dba_cons_columns dcc1
    on dcc1.constraint_name = allc1.constraint_name
  join dba_cons_columns dcc2
    on dcc2.constraint_name = allc2.constraint_name
  Join dba_tab_comments dtc
    on allc2.table_name = dtc.table_name
--Join dba_col_comments dccm on dcc.column_name = dccm.column_name
 where allc1.table_name = 'POL_MAIN'
   and allc1.constraint_type = 'R';


--=========表分区操作
--添加二级分区
alter table test modify partition p01 add subpartition p01max  values less than (to_date('20101001 00:00:00','YYYYMMDD HH24:MI:SS'))
--删除二级分区
alter table test drop subpartition p02201006
--split 二级分区
ALTER TABLE test SPLIT SUBPARTITION p01max AT (to_date('20100801 00:00:00','YYYYMMDD HH24:MI:SS')) INTO (SUBPARTITION p02201007, SUBPARTITION p01max);
--merge 分区
ALTER TABLE test merge SUBPARTITIONs p02201007,p01max  into SUBPARTITION p01max;
--修改子分区名
ALTER TABLE PAYABLE_FEE_RECORD RENAME SUBPARTITION PAYABLE_FEE_RD_G00_MAX_SPT_SPT TO PAYABLE_FEE_RD_G00_MAX_SPT ;
--修改分区名
ALTER TABLE PAYABLE_FEE_RECORD RENAME PARTITION PAYABLE_FEE_RD_G00 TO PAYABLE_FEE_RD_G00_PT ;


--查看存储过程进度
select sid, serial#, 
       sql_id, 
       p.object_name, 
       p.procedure_name
  from v$session s, dba_procedures p
 where s.plsql_entry_object_id = p.object_id(+)
   and s.plsql_entry_subprogram_id = p.subprogram_id(+)
   and p.object_name = '&object_name'; --package_name/procedure_name

--等待事件
select *
  from v$session_wait
 where sid in (select sid
                 from v$session
                where status = 'ACTIVE'
                  and username = 'GBSMAN')
   and wait_class <> 'Idle'

--等待最多的SQL
SELECT   a.program, a.session_id, a.user_id, d.username, s.sql_text,
         SUM (a.wait_time + a.time_waited) total_wait_time
    FROM v$active_session_history a, v$sqlarea s, dba_users d
   WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
     AND a.sql_id = s.sql_id
     AND a.user_id = d.user_id
GROUP BY a.program, a.session_id, a.user_id, s.sql_text, d.username
order by total_wait_time desc ;  

--查询会话执行的实际SQL
SELECT a.SID, a.username, s.sql_text
  FROM v$session a, v$sqltext s
 WHERE a.sql_address = s.address
   AND a.sql_hash_value = s.hash_value
   AND a.status = 'ACTIVE'
 ORDER BY a.username, a.SID, s.piece;

----查找当前表级锁的SQL
select sess.sid,
       sess.serial#,
       psid.SPID,
       lo.oracle_username,
       lo.os_user_name,
       ao.object_name,
       lo.locked_mode
  from v$locked_object lo, dba_objects ao, v$session sess, v$process psid
 where ao.object_id = lo.object_id
   and lo.session_id = sess.sid
   and sess.PADDR = psid.ADDR;

select B.SID, b.USERNAME, b.MACHINE
  FROM V$ACCESS A, V$SESSION B
 WHERE A.SID = B.SID
   and a.OBJECT = '％IPRS_POS_REPORT_NEW_PACKAGE％'
   and a.TYPE = 'PACKAGE'

--查询历史SQL的执行情况
SELECT CAST(begin_interval_time AS DATE) sample_time,
       sql_id,
       executions_delta executions,
       rows_processed_delta rows_processed,
       ROUND(rows_processed_delta / NULLIF(executions_delta, 0)) rows_per_exec,
       ROUND(buffer_gets_delta / NULLIF(executions_delta, 0)) lios_per_exec,
       ROUND(disk_reads_delta / NULLIF(executions_delta, 0)) blkrd_per_exec,
       ROUND(cpu_time_delta / NULLIF(executions_delta, 0) / 1000) cpu_ms_per_exec,
       ROUND(elapsed_time_delta / NULLIF(executions_delta, 0) / 1000) ela_ms_per_exec,
       ROUND(iowait_delta / NULLIF(executions_delta, 0) / 1000) iow_ms_per_exec,
       ROUND(clwait_delta / NULLIF(executions_delta, 0) / 1000) clw_ms_per_exec,
       ROUND(apwait_delta / NULLIF(executions_delta, 0) / 1000) apw_ms_per_exec,
       ROUND(ccwait_delta / NULLIF(executions_delta, 0) / 1000) ccw_ms_per_exec
  FROM dba_hist_snapshot NATURAL
  JOIN dba_hist_sqlstat
 WHERE begin_interval_time > SYSDATE - 7
   AND sql_id = '&1'
 ORDER BY sample_time

--ddl 锁查询
Select a.name,b.SID,b.SERIAL#
  From dba_ddl_locks a, v$session b
Where a.session_id= b.SID
  And a.name like '%IPRS_POS_REPORT_NEW_PACKAGE%'

--查看死锁信息
SELECT (SELECT username
          FROM v$session
         WHERE SID = a.SID) blocker, a.SID, 'is blocking',
       (SELECT username
          FROM v$session
         WHERE SID = b.SID) blockee, b.SID
  FROM v$lock a, v$lock b
 WHERE a.BLOCK = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2;

-- SQL  相关的锁
select 
  username, 
  osuser, 
  machine, 
  s.module, 
  l.sid, 
  decode(l.type, 
     'MR', 'Media Recovery', 
     'RT', 'Redo Thread', 
     'UN', 'User Name', 
     'TX', 'Transaction', 
     'TM', 'DML', 
     'UL', 'PL/SQL User Lock', 
     'DX', 'Distributed Xaction', 
     'CF', 'Control File', 
     'IS', 'Instance State', 
     'FS', 'File Set', 
     'IR', 'Instance Recovery', 
     'ST', 'Disk Space Transaction', 
     'TS', 'Temp Segment', 
     'IV', 'Library Cache Invalidation', 
     'LS', 'Log Start or Switch', 
     'RW', 'Row Wait', 
     'SQ', 'Sequence Number', 
     'TE', 'Extend Table', 
     'TT', 'Temp Table', l.type) type, 
  decode(lmode, 
     0, 'None', 
     1, 'Null', 
     2, 'Row-S (SS)', 
     3, 'Row-X (SX)', 
     4, 'Share', 
     5, 'S/Row-X (SSX)', 
     6, 'Exclusive', lmode) lock_mode, 
  decode(request, 
     0, 'None', 
     1, 'Null', 
     2, 'Row-S (SS)', 
     3, 'Row-X (SX)', 
     4, 'Share', 
     5, 'S/Row-X (SSX)', 
     6, 'Exclusive', request) request, 
  decode(block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global', block) block, 
  owner, 
  object_name, 
  a.sql_text 
from v$locked_object lo, all_objects ao, v$lock l, v$session s, v$sqlarea a 
where lo.object_id = ao.object_id 
  and l.sid = lo.session_id 
  and s.sid = l.sid 
  and a.address = s.sql_address(+) 
  and a.hash_value = s.sql_hash_value(+) 
order by username

--杀掉锁表进程
alter system kill session '436,35123';

--RAC环境中锁查找
SELECT inst_id,DECODE(request,0,'Holder: ','Waiter: ')||sid sess, 
        id1, id2, lmode, request, type,block,ctime
  FROM GV$LOCK
 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
ORDER BY id1, request;

--监控当前数据库谁在运行什么SQL语句
select osuser, username, sql_text
  from v$session a, v$sqltext b
 where a.sql_address = b.address
 order by address, piece;

--找出过去4个小时内登陆且在近30分钟调用过的sql
SELECT s.sid,
       s.serial#,
       p.spid as "OS PID",
       s.username,
       s.module,
       st.value / 100 as "CPU sec"
  FROM v$sesstat st, v$statname sn, v$session s, v$process p
 WHERE sn.name = 'CPU used by this session' -- CPU
   AND st.statistic# = sn.statistic#
   AND st.sid = s.sid
   AND s.paddr = p.addr
   AND s.last_call_et < 1800 -- active within last 1/2 hour
   AND s.logon_time > (SYSDATE - 240 / 1440) -- sessions logged on within 4 hours
 ORDER BY st.value;

 --查看等待最严重的会话
-- sessions with the highest time for a certain wait
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name'
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;

--10g or higher: 查看占用DB time 最高的会话
-- sessions with highest DB Time usage
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND  sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;

--找使用CPU多的用户session 
select a.sid,
       spid,
       status,
       substr(a.program, 1, 40) prog,
       a.terminal,
       osuser,
       value / 60 / 100 value
  from v$session a, v$process b, v$sesstat c
 where c.statistic# = 12
   and c.sid = a.sid
   and a.paddr = b.addr
 order by value desc;

--查看消耗资源最多的SQL
  SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls
    FROM V$SQLAREA
   WHERE buffer_gets > 10000000 OR disk_reads > 1000000
ORDER BY buffer_gets + 100 * disk_reads DESC;

--查看SQL运行信息
select rownum as rank, a.*
  from (select PARSING_SCHEMA_NAME RUN_USER, --执行用户
               SQL_FULLTEXT, --SQL文本,全，带格式 
               sql_text, --SQL文本前面部分 
               elapsed_Time, --单位是微秒，语句执行完的总时间，包括CPU时间和等待时间 
               cpu_time, --CPU时间，语句运行的时间 
               elapsed_Time - cpu_time wait_time,
               trunc((elapsed_Time - cpu_time) * 100 / elapsed_Time, 2) "wait_time_per%", --等待时间所占百分比 
               executions, --执行次数 
               elapsed_Time / (executions + 1) Per_Time, --每次执行平均时间, 
               buffer_gets, -- 
               disk_reads, --磁盘读 
               hash_value, --SQL的hash_value，以备获取完整SQL 
               USER_IO_WAIT_TIME,
               SORTS --排序次数 
          from v$sqlarea t
         where elapsed_time > 20000　and PARSING_SCHEMA_NAME <> 'SYS'
         order by elapsed_time desc) a
 where rownum < 21
 order by elapsed_time desc

--查历史的SQL执行情况
SELECT distinct s.snap_id,
                PLAN_HASH_VALUE,
                to_char(s.BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24mi') ||
                to_char(s.END_INTERVAL_TIME, '_hh24mi') Date_Time,
                SQL.executions_delta,
                SQL.buffer_gets_delta /
                decode(nvl(SQL.executions_delta, 0),0, 1, SQL.executions_delta) avg_lio,
                --SQL.ccwait_delta,
                (SQL.cpu_time_delta / 1000000) / decode(nvl(SQL.executions_delta, 0), 0, 1, SQL.executions_delta) avg_cputime_s,
                (SQL.elapsed_time_delta / 1000000) / decode(nvl(SQL.executions_delta, 0), 0, 1, SQL.executions_delta) avg_etime_s,
                SQL.DISK_READS_DELTA / decode(nvl(SQL.executions_delta, 0), 0,1, SQL.executions_delta) avg_pio,
                SQL.rows_processed_total / decode(nvl(SQL.executions_delta, 0), 0, 1, SQL.executions_delta) avg_row
                --,SQL.sql_profile
  FROM dba_hist_sqlstat SQL, dba_hist_snapshot s
 WHERE SQL.instance_number = (select instance_number from v$instance)
   and SQL.dbid = (select dbid from v$database)
   and s.snap_id = SQL.snap_id
   AND sql_id in ('&sql_id')
 order by s.snap_id; 

--查询表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
       　　D.TOT_GROOTTE_MB "表空间大小(M)",
       　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
       　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /
                       D.TOT_GROOTTE_MB * 100, 2), '990.99') || '%' "使用比",
       　　F.TOTAL_BYTES "空闲空间(M)",
       　　F.MAX_BYTES "最大块(M)" 　　
     FROM (SELECT TABLESPACE_NAME,
                  ROUND(SUM(BYTES) /(1024 * 1024), 2) TOTAL_BYTES,
                  ROUND(MAX(BYTES) /(1024 * 1024), 2) MAX_BYTES
             FROM DBA_FREE_SPACE 　　GROUP BY TABLESPACE_NAME) F,
       　　 (SELECT DD.TABLESPACE_NAME,
                    ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
               FROM DBA_DATA_FILES DD 　　
           GROUP BY DD.TABLESPACE_NAME) D 　　
     WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME 　　
  ORDER BY 1;

-- 查询表空间最近30天的使用情况
  select a.name, b.*
  from v$tablespace a,
       (select tablespace_id,
               trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) datetime,
               max(tablespace_usedsize * 8 / 1024) used_size
          from dba_hist_tbspc_space_usage
         where trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) >
               trunc(sysdate - 30)
         group by tablespace_id,
                  trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))
         order by tablespace_id,
                  trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))) b
 where a.ts# = b.tablespace_id
--查询表空间的free space
select tablespace_name,
　   　count(*) as extends,
　   　round(sum(bytes) / 1024 / 1024, 2) as MB,
　   　sum(blocks) as blocks
　from dba_free_space
group by tablespace_name;

--查询表空间的总容量
select tablespace_name, sum(bytes) / 1024 / 1024 as MB
  from dba_data_files
group by tablespace_name;

--查询表空间使用率
select total.tablespace_name,
　　   round(total.MB, 2) as Total_MB,
　　   round(total.MB - free.MB, 2) as Used_MB,
　　   round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
　from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
　　      from dba_free_space
　　  group by tablespace_name) free,
　　   (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
　　      from dba_data_files
　　  group by tablespace_name) total
where free.tablespace_name = total.tablespace_name; 

--表空间
select ddf.TABLESPACE_NAME,
       ddf.BYTES tablespace_size,
       ddf.BYTES - nvl(DFS.BYTES, 0) used,       
       nvl(dfs.BYTES, 0) free,
       round(((ddf.BYTES - nvl(dfs.BYTES, 0)) / ddf.BYTES) * 100, 2) pct_used,
       round((1 - ((ddf.BYTES - nvl(dfs.BYTES, 0)) / ddf.BYTES)) * 100, 2) pct_free
  from (select t.name TABLESPACE_NAME, sum(BYTES) bytes
          from v$datafile d, v$tablespace t
         where t.ts# = d.ts#
         group by t.name) ddf,
       (select TABLESPACE_NAME, sum(BYTES) bytes
          from user_free_space
         group by TABLESPACE_NAME) dfs
 where ddf.TABLESPACE_NAME = dfs.TABLESPACE_NAME(+)
 order by ((ddf.BYTES - nvl(dfs.BYTES, 0)) / ddf.BYTES) desc

--临时表空间使用情况:
Select f.tablespace_name,
       sum(f.bytes_free + f.bytes_used) / 1024 / 1024 / 1024 "total GB",
       sum((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024 / 1024 "Free GB",
       sum(nvl(p.bytes_used, 0)) / 1024 / 1024 / 1024 "Used GB"
  from sys.v_$temp_space_header f,
       dba_temp_files           d,
       sys.v_$temp_extent_pool  p
 where f.tablespace_name(+) = d.tablespace_name
   and f.file_id(+) = d.file_id
   and p.file_id(+) = d.file_id
 group by f.tablespace_name

-- 数据文件的IO状况查看
select df.tablespace_name 表空间,
       df.file_name       数据文件,
       f.phyrds           读次数,
       f.phyblkrd         读块数,
       f.phywrts          写次数,
       f.phyblkwrt        写块数
  from v$filestat f, dba_data_files df
 where f.file# = df.file_id
 order by df.tablespace_name;

--某个SID临时表空间使用情况
select b.tablespace,
       b.segfile#,
       b.segblk#,
       b.blocks,
       b.blocks * 32 / 1024 / 1024,
       a.sid,
       a.serial#,
       a.username,
       a.osuser,
       a.status,
       c.sql_text,
       b.contents
  from v$session a, v$sort_usage b, v$sql c
 where a.saddr = b.session_addr
   and a.sql_address = c.address(+)
 order by b.blocks desc

--回滚段相关查询
Select rn.Name "Rollback Segment",
       rs.RSSize / 1024 "Size (KB)",
       rs.Gets "Gets",
       rs.waits "Waits",
       (rs.Waits / rs.Gets) * 100 "% Waits",
       rs.Shrinks "# Shrinks",
       rs.Extends "# Extends"
  from v$RollName rn, v$RollStat rs
 where rn.usn = rs.usn

--表、索引的存储情况检查  
select segment_name, sum(bytes /1024/1024/1024) size_G, count(*) ext_num
  from dba_extents
 where tablespace_name = 'GBS'
   and segment_type = 'TABLE'
   and segment_name = 'POL_MAIN'
 group by tablespace_name, segment_name;

select segment_name, count(*)
  from dba_extents
 where segment_type = 'INDEX'
   and owner = &owner
 group by segment_name;