80 lines
2.2 KiB
SQL
80 lines
2.2 KiB
SQL
@@header
|
|
SET echo off
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display temporary space usage
|
|
* Compability : 10.x, 11.x
|
|
* Parameters : None
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 26-May-11 Vishal Gupta Initial Version
|
|
*/
|
|
set term on
|
|
SET lines 10000
|
|
|
|
DEFINE topsessioncount=30
|
|
|
|
COLUMN MB FORMAT 99,999
|
|
COLUMN "SID,SER#,@INST" FORMAT a15
|
|
|
|
COLUMN extents FORMAT 9999 ON
|
|
COLUMN segtype FORMAT a9 ON
|
|
COLUMN tablespace FORMAT a20 ON
|
|
COLUMN inst_id HEADING "Inst" FORMAT 99 ON
|
|
COLUMN sid FORMAT 9999 ON
|
|
COLUMN serial# FORMAT 99999 ON
|
|
COLUMN logon_time FORMAT a15 ON
|
|
COLUMN status FORMAT a10 ON
|
|
COLUMN spid FORMAT a5 ON
|
|
COLUMN username FORMAT a20 ON
|
|
COLUMN machine FORMAT a20 ON
|
|
COLUMN program FORMAT a20 ON TRUNCATE
|
|
COLUMN osuser FORMAT a20 ON TRUNCATE
|
|
|
|
|
|
PROMPT
|
|
PROMPT ###### TOP &topsessioncount Temporary Segments (Ordered by Size) ##########
|
|
|
|
SELECT *
|
|
FROM
|
|
(
|
|
SELECT SUM(t.blocks * p.value)/1024/1024 MB
|
|
, t.tablespace
|
|
, t.segtype
|
|
, s.inst_id
|
|
, s.sid
|
|
, s.serial#
|
|
-- , s.sid || ',' || s.serial# || ',@' || s.inst_id As "SID,SER#,@INST"
|
|
, s.username
|
|
, s.osuser
|
|
, s.program
|
|
, s.sql_id
|
|
FROM gv$tempseg_usage t
|
|
, gv$session s
|
|
, v$system_parameter p
|
|
WHERE t.inst_id = s.inst_id
|
|
AND t.session_addr = s.saddr
|
|
AND t.session_num = s.serial#
|
|
AND p.name = 'db_block_size'
|
|
GROUP BY t.tablespace
|
|
, t.segtype
|
|
, s.inst_id
|
|
, s.sid
|
|
, s.serial#
|
|
-- , s.sid || ',' || s.serial# || ',@' || s.inst_id
|
|
, s.username
|
|
, s.osuser
|
|
, s.program
|
|
, s.sql_id
|
|
ORDER BY 1
|
|
)
|
|
WHERE ROWNUM <= &topsessioncount
|
|
;
|
|
|
|
@@footer
|