Admin

Session Long ops

SET LINESIZE 130

SET PAGESIZE 1000

COLUMN SID FORMAT 999

COLUMN SERIAL# FORMAT 9999999

COLUMN MACHINE FORMAT A25

COLUMN PROGRESS_PCT FORMAT 99999999.00

COLUMN ELAPSED FORMAT A10

COLUMN REMAINING FORMAT A10

COLUMN STIME FORMAT A10

COLUMN SQL_HASH_VALUE FORMAT 9999999999

SELECT S.SID,

       S.SERIAL#,

       S.MACHINE,

            SL.OPNAME,

            TO_CHAR(SL.START_TIME,'DD-MON-YY HH24:MI:SS') STIME,

            SL.USERNAME,

            SL.SQL_HASH_VALUE,

       TRUNC(SL.ELAPSED_SECONDS/60) || ':' || MOD(SL.ELAPSED_SECONDS,60) ELAPSED,

       TRUNC(SL.TIME_REMAINING/60) || ':' || MOD(SL.TIME_REMAINING,60) REMAINING,

       ROUND(SL.SOFAR/SL.TOTALWORK*100, 2) PROGRESS_PCT

FROM   V$SESSION S,

       V$SESSION_LONGOPS SL

WHERE  S.SID     = SL.SID

AND    S.SERIAL# = SL.SERIAL#

AND       SL.TOTALWORK <> 0

AND    SL.SOFAR/SL.TOTALWORK < 1;

Expired UNDO

SELECT SUM(BYTES)/1024/1024 ,TABLESPACE_NAME,STATUS FROM DBA_UNDO_EXTENTS GROUP BY TABLESPACE_NAME,STATUS;

IMPORT MONITORING

SELECT SUBSTR(SQL_TEXT,INSTR(SQL_TEXT,'INTO "'),30) TABLE_NAME,

ROWS_PROCESSED,

ROUND((SYSDATE - TO_DATE(FIRST_LOAD_TIME,'YYYY-MM-DD HH24:MI:SS'))*24*60,1) MINUTES,

TRUNC(ROWS_PROCESSED/((SYSDATE-TO_DATE(FIRST_LOAD_TIME,'YYYY-MM-DD HH24:MI:SS'))*24*60)) ROWS_PER_MINUTE

FROM SYS.V_$SQLAREA

WHERE SQL_TEXT LIKE 'INSERT %INTO "%'

AND  COMMAND_TYPE = 2 AND   OPEN_VERSIONS > 0;

DDL Locks

COL USERNAME FOR A12

COL OBJECT_LOCKED FOR A40

COL MACHINE/PID FOR A30

COL PROGRAM FOR A30

SET LINESIZE 132

SET VERIFY OFF

SELECT S.USERNAME, S.SID, S.PROGRAM, D.OWNER || '.' || D.NAME OBJECT_LOCKED, DECODE(S.PROCESS, NULL, S.MACHINE, S.MACHINE || ' PID: ' || TO_CHAR(S.PROCESS)) "MACHINE/PID"

FROM V$SESSION S, DBA_DDL_LOCKS D WHERE S.SID = D.SESSION_ID AND D.TYPE LIKE '%/Function/%' AND D.OWNER = 'USER1' AND D.NAME = 'OVERRIDE';

Sort Usage

SELECT SUM( U.BLOCKS * BLK.BLOCK_SIZE)/1024/1024 "MB. IN SORT SEGMENTS" , (HWM.MAX * BLK.BLOCK_SIZE)/1024/1024 "MB. HIGH WATER MARK" FROM V$SORT_USAGE U, (SELECT BLOCK_SIZE FROM DBA_TABLESPACES WHERE CONTENTS = 'TEMPORARY') BLK , (SELECT SEGBLK#+BLOCKS MAX FROM V$SORT_USAGE WHERE SEGBLK# = (SELECT MAX(SEGBLK#) FROM V$SORT_USAGE) ) HWM GROUP BY HWM.MAX * BLK.BLOCK_SIZE/1024/1024;

Find out session details

ps -e -o pcpu,pid,user,args | sort

SELECT A.USERNAME,A.OSUSER,A.PROGRAM,SPID,SID,A.SERIAL# FROM V$SESSION A, V$PROCESS B WHERE A.PADDR=B.ADDR AND SPID=

ACCEPT SID PROMPT 'SID: '

ACCEPT SERIAL PROMPT 'SERIAL#: '

SELECT B.USERNAME,A.SQL_TEXT FROM V$SQL A, V$SESSION B WHERE B.SQL_ADDRESS=A.ADDRESS AND B.SQL_HASH_VALUE=A.HASH_VALUE AND B.SID=&SID AND B.SERIAL#='&SERIAL'

Rollback Segment Usage By User

SELECT R.NAME "RBS", S.SID, S.SERIAL#, S.USERNAME "USER", T.STATUS,T.CR_GET, T.PHY_IO, T.USED_UBLK, T.NOUNDO ,SUBSTR(S.PROGRAM, 1, 18) "COMMAND" FROM   SYS.V_$SESSION S, SYS.V_$TRANSACTION T, SYS.V_$ROLLNAME R WHERE  T.ADDR = S.TADDR AND  T.XIDUSN = R.USN ORDER  BY 1

BLOCKING SESSION

SELECT L1.SID, ' IS BLOCKING ', L2.SID FROM V$LOCK L1, V$LOCK L2 WHERE L1.BLOCK =1 AND L2.REQUEST > 0 AND L1.ID1=L2.ID1 AND L1.ID2=L2.ID2

SELECT S1.USERNAME || '@' || S1.MACHINE || ' ( SID=' || S1.SID || ' )  IS BLOCKING ' || S2.USERNAME || '@' || S2.MACHINE || ' ( SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS

FROM V$LOCK L1, V$SESSION S1, V$LOCK L2, V$SESSION S2 WHERE S1.SID=L1.SID AND S2.SID=L2.SID AND L1.BLOCK=1 AND L2.REQUEST > 0 AND L1.ID1 = L2.ID1 AND L2.ID2 = L2.ID2 ;

SELECT SID,SERIAL#,USERNAME,STATUS FROM V$SESSION WHERE SID IN (131,184,141);

RMAN BACKUP SIZE

SELECT CTIME "DATE", DECODE(BACKUP_TYPE, 'L', 'ARCHIVE LOG', 'D', 'FULL', 'I', 'INCREMENTAL') BACKUP_TYPE,

 BSIZE "SIZE MB" FROM (SELECT TRUNC(BP.COMPLETION_TIME) CTIME,BACKUP_TYPE,ROUND(SUM(BP.BYTES/1024/1024),2) BSIZE

 FROM V$BACKUP_SET BS, V$BACKUP_PIECE BP WHERE BS.SET_STAMP = BP.SET_STAMP AND BS.SET_COUNT  = BP.SET_COUNT

  AND BP.STATUS = 'A' GROUP BY TRUNC(BP.COMPLETION_TIME), BACKUP_TYPE) ORDER BY 1, 2;

SELECT CTIME "DATE", DECODE(BACKUP_TYPE, 'L', 'ARCHIVE LOG', 'D', 'FULL', 'I', 'INCREMENTAL') BACKUP_TYPE,

 BSIZE "SIZE MB" FROM (SELECT TRUNC(BP.COMPLETION_TIME) CTIME,BS.BACKUP_TYPE,ROUND(SUM(BP.BYTES/1024/1024),2) BSIZE

FROM RC_BACKUP_SET BS, RC_BACKUP_PIECE BP WHERE BP.DB_ID='2399412135' AND BS.SET_STAMP = BP.SET_STAMP AND BS.SET_COUNT  = BP.SET_COUNT

 AND BP.STATUS = 'A' GROUP BY TRUNC(BP.COMPLETION_TIME), BS.BACKUP_TYPE) ORDER BY 1, 2

SELECT CTIME "DATE", DECODE(BACKUP_TYPE, 'L', 'ARCHIVE LOG', 'D', 'FULL', 'I', 'INCREMENTAL') BACKUP_TYPE,

 BSIZE "SIZE MB" FROM (SELECT COUNT(*),TRUNC(BP.COMPLETION_TIME) CTIME,BS.BACKUP_TYPE,ROUND(SUM(BP.BYTES/1024/1024),2) BSIZE

FROM RC_BACKUP_SET BS, RC_BACKUP_PIECE BP WHERE BS.SET_STAMP = BP.SET_STAMP AND BS.SET_COUNT  = BP.SET_COUNT

 AND BP.STATUS = 'A' GROUP BY TRUNC(BP.COMPLETION_TIME), BS.BACKUP_TYPE)ORDER BY 1, 2;

SELECT COUNT(*), TRUNC(BP.COMPLETION_TIME) CTIME,BACKUP_TYPE,ROUND(SUM(BP.BYTES/1024/1024),2) BSIZE

 FROM V$BACKUP_SET BS, V$BACKUP_PIECE BP WHERE BS.SET_STAMP = BP.SET_STAMP AND BS.SET_COUNT  = BP.SET_COUNT

  AND BP.STATUS = 'A' GROUP BY TRUNC(BP.COMPLETION_TIME), BACKUP_TYPE

SELECT COUNT(*),TRUNC(BP.COMPLETION_TIME) CTIME,BS.BACKUP_TYPE,ROUND(SUM(BP.BYTES/1024/1024),2) BSIZE

FROM RC_BACKUP_SET BS, RC_BACKUP_PIECE BP WHERE BS.SET_STAMP = BP.SET_STAMP AND BS.SET_COUNT  = BP.SET_COUNT

 AND BP.STATUS = 'A' GROUP BY TRUNC(BP.COMPLETION_TIME), BS.BACKUP_TYPE ORDER BY CTIME;