82 lines
2.4 KiB
SQL
82 lines
2.4 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display temporary space usage
|
|
* Compability: 9.x, 10.x, 11.x
|
|
* Parameters : 1 - INST_ID (Use % as wildcard)
|
|
* 2 - TOP_ROWCOUNT
|
|
* 3 - WHERE CLAUSE
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 26-May-04 Vishal Gupta Initial Version
|
|
* 15-Jun-12 Vishal Gupta formatted output
|
|
*/
|
|
|
|
|
|
DEFINE INST_ID=&1
|
|
DEFINE TOP_ROWCOUNT=&2
|
|
DEFINE WHERE_CLAUSE="&3"
|
|
|
|
|
|
|
|
|
|
COLUMN used HEADING "Size|(MB)" FORMAT 9,999,999
|
|
COLUMN extents FORMAT 999,999
|
|
COLUMN segtype FORMAT a9
|
|
COLUMN tablespace FORMAT a20
|
|
COLUMN inst_id HEADING "I#" FORMAT 99
|
|
COLUMN sid FORMAT 9999
|
|
COLUMN serial# FORMAT 99999
|
|
COLUMN logon_time FORMAT a15
|
|
COLUMN status FORMAT a10
|
|
COLUMN spid FORMAT a5
|
|
COLUMN username FORMAT a20
|
|
COLUMN machine FORMAT a20 TRUNCATE
|
|
COLUMN program FORMAT a20 TRUNCATE
|
|
COLUMN osuser FORMAT a20 TRUNCATE
|
|
|
|
PROMPT ***********************************************************
|
|
PROMPT ******* TOP &TOP_ROWCOUNT SORT Segments (Ordered by Size)
|
|
PROMPT ***********************************************************
|
|
|
|
SELECT *
|
|
FROM
|
|
(
|
|
SELECT /*+ ORDERED */
|
|
(su.BLOCKS * par.value )/1024/1024 used
|
|
-- , su.EXTENTS
|
|
, su.segtype
|
|
, su.tablespace
|
|
, s.sid
|
|
, s.inst_id
|
|
, to_char(s.logon_time,'DD-MON-YY HH24:MI') logon_time
|
|
, s.status
|
|
, p.spid
|
|
, s.username
|
|
, s.machine
|
|
, s.program
|
|
, s.osuser
|
|
, s.sql_id
|
|
FROM gv$sort_usage su
|
|
JOIN gv$session s ON s.inst_id = su.inst_id AND su.session_addr = s.saddr AND su.session_num = s.serial#
|
|
JOIN gv$process p ON p.inst_id = s.inst_id AND p.addr = s.paddr
|
|
JOIN gv$system_parameter par ON su.inst_id = par.inst_id AND par.NAME = 'db_block_size'
|
|
WHERE su.inst_id like '&&INST_ID'
|
|
&&WHERE_CLAUSE
|
|
order by 1 desc
|
|
)
|
|
WHERE ROWNUM <= &TOP_ROWCOUNT
|
|
/
|
|
|
|
|
|
UNDEFINE INST_ID
|
|
UNDEFINE TOP_ROWCOUNT
|
|
UNDEFINE WHERE_CLAUSE
|
|
|
|
@@footer
|