查找有问题的语句

--当前执行的SQL语句
SELECT A.SID,
       A.EVENT,
       A.SQL_ID,
       A.MACHINE,
       B.SQL_TEXT,
       B.SQL_FULLTEXT,
       B.FIRST_LOAD_TIME,
       B.LAST_LOAD_TIME,
       B.LAST_ACTIVE_TIME
  FROM V$SESSION A, V$SQL B
 WHERE A.SQL_ID = B.SQL_ID
   AND A.USERNAME IS NOT NULL

   AND A.STATUS = 'ACTIVE';

--查看有问题会话
SELECT T.SID,
       T.SERIAL#,
       P.TYPE || '-' || P.ID1 || '-' || P.ID2 AS lock_name,
       d.object_name,
       P.LMODE,
       P.REQUEST,
       P.CTIME,
       P.BLOCK,
       H.SQL_TEXT,
       M.NAME,
       M.WAIT_CLASS,
       N.STATUS,
       Y.USN,
       N.XIDSLOT,
       N.XIDSQN,
       Y.NAME
  FROM V$SESSION T
  LEFT JOIN V$PROCESS F ON T.PADDR = F.ADDR
  LEFT JOIN V$LOCK P ON T.SID = P.SID
  LEFT JOIN V$SQL H ON T.PREV_SQL_ID = H.SQL_ID
  LEFT JOIN V$EVENT_NAME M ON T.EVENT = M.NAME
  LEFT JOIN V$TRANSACTION N ON T.TADDR = N.ADDR
  LEFT JOIN dba_objects d ON p.ID1=d.object_id
  LEFT JOIN V$ROLLNAME Y ON N.XIDUSN = Y.USN
  WHERE t.SID=1527

 --如果name是direct path read temp活direct path write temp,那么查看林时段是否在一直增加
  
  SELECT TOTAL_BLOCKS,USED_BLOCKS FROM V$SORT_SEGMENT;

--查找僵死进程并杀死

select a.owner, 
a.object_name, 
b.session_id, 
b.oracle_username, 
b.os_user_name, 
b.process, 
b.locked_mode, 
c.sid, 
c.serial#, 
c.program 
from all_objects a, 
v$locked_object b, 
sys.gv_$session c 
where ( a.object_id = b.object_id ) 
and (b.process = c.process ) 
and a.object_name='DW_DIM_PROD_AREA'; 


ALTER SYSTEM KILL SESSION '2651,2818' IMMEDIATE;

--查找本机的会话情况

SELECT action,t.* from V$SESSION t WHERE machine='WORKGROUP\BQZZY5CDPOICF4C'

--检查表大小
SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MBYTESE
  FROM USER_SEGMENTS
 WHERE SEGMENT_TYPE = 'TABLE'
   AND SEGMENT_NAME IN
       (SELECT TABLE_NAME
          FROM ALL_TABLES
         WHERE OWNER = 'SWH'
           AND TABLE_NAME = 'T_SALES_FACT')
 GROUP BY SEGMENT_NAME;


--检查索引大小


SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MBYTESE
  FROM USER_SEGMENTS
 WHERE SEGMENT_TYPE = 'INDEX'
   AND SEGMENT_NAME ='IDX_SALES_FACT_ID'
 GROUP BY SEGMENT_NAME