/**************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/ SELECT A.tablespace_name TABLESPACE, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP BY A.tablespace_name, D.mb_total TABLESPACE MB_TOTAL MB_USED MB_FREE ------------------------------- -------- -------------------------------------- ------------------- TEMP01 4096 120 3976 /**************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/ SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.MODULE, S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.TABLESPACE, COUNT(*) sort_ops FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.TABLESPACE = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.MODULE, S.program, TBS.block_size, T.TABLESPACE ORDER BY sid_serial SID_SERIAL USERNAME OSUSER SPID MODULE PROGRAM MB_USED TABLESPACE SORT_OPS --------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------ ------------------------------------------------ ------------------------------------------------ -------------------------------------- ------------------------------- -------------------------------------- 14,41977 JAGANNK1 kiran.jagannath 3932 SQLNav5.exe SQLNav5.exe 120 TEMP01 3 /**************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/ SELECT b.TABLESPACE, b.segfile#, b.segblk#, b.blocks, a.SID, a.serial#, a.username, a.osuser, a.status FROM v$session a, v$sort_usage b WHERE a.saddr = b.session_addr ORDER BY b.blocks, b.TABLESPACE TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS ------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- ------------------------------ ------------------------------ ---------- TEMP01 203 5129 2560 14 41977 JAGANNK1 kiran.jagannath ACTIVE TEMP01 203 20489 2560 14 41977 JAGANNK1 kiran.jagannath ACTIVE TEMP01 203 15369 10240 14 41977 JAGANNK1 kiran.jagannath ACTIVE /**************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/ SELECT S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.TABLESPACE, T.sqladdr address, Q.hash_value, Q.sql_text FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address (+) AND T.TABLESPACE = TBS.tablespace_name ORDER BY S.sid SID_SERIAL USERNAME MB_USED TABLESPACE ADDRESS HASH_VALUE SQL_TEXT --------------------------------------------------------------------------------- ------------------------------ -------------------------------------- ------------------------------- ---------- -------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 14,41977 JAGANNK1 20 TEMP01 2C83AA18 3731122198 SELECT DISTINCT DMA.MEMO_TYPE, DMA.MEMO_ALERT_ID, DUP.PROJECT_NAME, DUP.PROJECT_DESCRIPTION, DUP.ACTIVITY_NAME, DUP.ACTIVITY_DESCRIPTION, DUP.REPORT_OR_STUDY_NO, DUP.START_DATE, DUP.END_DATE, DUP.REG_DUE_DATE, DUP.ACTIVITY_STATUS, DUP.ACTIVITY_OBS, DUP.MEMO_SENT, DUP.VER, ADM.PKG_MEMO10.F_GET_FULL_NAME(E2.RESP1) AS FUNCTIONAL_MGR, ADM.PKG_MEMO10.F_GET_FULL_NAME(DUP.RESPONSIBLE_PERSON) AS RESPONSIBLE_PERSON, ADM.PKG_MEMO10.F_GET_FULL_NAME(RQ.RN) AS RESOURCE_NAME, DUP.TST_SUBST_CODE, NVL(DOC.DREQ_OUT2,'NO SIF_LINK'), NVL(ADM.PKG_MEMO10.F_COA_URL_NEW(DUP.TST_SUBST_CODE, COA.EXPIRATION_DATE),'NO COA_LINK') FROM ADM.MEMO_MV DUP, AV_ELEMENT E1, AV_ELEMENT E2, AV_RESREQ RQ, ADM.MEMO_ALERTS DMA, ADM.DUP_DOCBASE_REQUESTS DOC, ADM.DUP_TESTSUB_COAEXP_NRM COA WHERE DUP.PROJECT_NAME = DMA.PROJECT AND DUP.ACTIVITY_NAME = DMA.ACTIVITY AND DUP.PROJECT_NAME = RQ.PROJ AND DUP.ACTIVITY_NAME = RQ.ACT AND RQ.VER IN ( 0,97 ) AND E1.RESP1<>E2.RESP1 AND (E2.CATEGORY = 'GT OBS' AND E2.ELEMENT = DUP.ACTIVITY_O 14,41977 JAGANNK1 20 TEMP01 2C83AA18 3731122198 SELECT DISTINCT DMA.MEMO_TYPE, DMA.MEMO_ALERT_ID, DUP.PROJECT_NAME, DUP.PROJECT_DESCRIPTION, DUP.ACTIVITY_NAME, DUP.ACTIVITY_DESCRIPTION, DUP.REPORT_OR_STUDY_NO, DUP.START_DATE, DUP.END_DATE, DUP.REG_DUE_DATE, DUP.ACTIVITY_STATUS, DUP.ACTIVITY_OBS, DUP.MEMO_SENT, DUP.VER, ADM.PKG_MEMO10.F_GET_FULL_NAME(E2.RESP1) AS FUNCTIONAL_MGR, ADM.PKG_MEMO10.F_GET_FULL_NAME(DUP.RESPONSIBLE_PERSON) AS RESPONSIBLE_PERSON, ADM.PKG_MEMO10.F_GET_FULL_NAME(RQ.RN) AS RESOURCE_NAME, DUP.TST_SUBST_CODE, NVL(DOC.DREQ_OUT2,'NO SIF_LINK'), NVL(ADM.PKG_MEMO10.F_COA_URL_NEW(DUP.TST_SUBST_CODE, COA.EXPIRATION_DATE),'NO COA_LINK') FROM ADM.MEMO_MV DUP, AV_ELEMENT E1, AV_ELEMENT E2, AV_RESREQ RQ, ADM.MEMO_ALERTS DMA, ADM.DUP_DOCBASE_REQUESTS DOC, ADM.DUP_TESTSUB_COAEXP_NRM COA WHERE DUP.PROJECT_NAME = DMA.PROJECT AND DUP.ACTIVITY_NAME = DMA.ACTIVITY AND DUP.PROJECT_NAME = RQ.PROJ AND DUP.ACTIVITY_NAME = RQ.ACT AND RQ.VER IN ( 0,97 ) AND E1.RESP1<>E2.RESP1 AND (E2.CATEGORY = 'GT OBS' AND E2.ELEMENT = DUP.ACTIVITY_O 14,41977 JAGANNK1 80 TEMP01 2C83AA18 3731122198 SELECT DISTINCT DMA.MEMO_TYPE, DMA.MEMO_ALERT_ID, DUP.PROJECT_NAME, DUP.PROJECT_DESCRIPTION, DUP.ACTIVITY_NAME, DUP.ACTIVITY_DESCRIPTION, DUP.REPORT_OR_STUDY_NO, DUP.START_DATE, DUP.END_DATE, DUP.REG_DUE_DATE, DUP.ACTIVITY_STATUS, DUP.ACTIVITY_OBS, DUP.MEMO_SENT, DUP.VER, ADM.PKG_MEMO10.F_GET_FULL_NAME(E2.RESP1) AS FUNCTIONAL_MGR, ADM.PKG_MEMO10.F_GET_FULL_NAME(DUP.RESPONSIBLE_PERSON) AS RESPONSIBLE_PERSON, ADM.PKG_MEMO10.F_GET_FULL_NAME(RQ.RN) AS RESOURCE_NAME, DUP.TST_SUBST_CODE, NVL(DOC.DREQ_OUT2,'NO SIF_LINK'), NVL(ADM.PKG_MEMO10.F_COA_URL_NEW(DUP.TST_SUBST_CODE, COA.EXPIRATION_DATE),'NO COA_LINK') FROM ADM.MEMO_MV DUP, AV_ELEMENT E1, AV_ELEMENT E2, AV_RESREQ RQ, ADM.MEMO_ALERTS DMA, ADM.DUP_DOCBASE_REQUESTS DOC, ADM.DUP_TESTSUB_COAEXP_NRM COA WHERE DUP.PROJECT_NAME = DMA.PROJECT AND DUP.ACTIVITY_NAME = DMA.ACTIVITY AND DUP.PROJECT_NAME = RQ.PROJ AND DUP.ACTIVITY_NAME = RQ.ACT AND RQ.VER IN ( 0,97 ) AND E1.RESP1<>E2.RESP1 AND (E2.CATEGORY = 'GT OBS' AND E2.ELEMENT = DUP.ACTIVITY_O /**************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/