143 lines
5.2 KiB
SQL
143 lines
5.2 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display temporary space usage
|
|
* Compability: 9.x, 10.x, 11.x
|
|
* Parameters : 1 - INST_ID - Default Value - %, (Use % as wildcard)
|
|
* 2 - TablespaceName - Default Value - %, (Use % as wildcard)
|
|
* 3 - TOP_ROWCOUNT - Default Value - 30
|
|
* 4 - WHERE CLAUSE - Default Value - ''
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 01-Mar-13 Vishal Gupta Added schema level summary in output
|
|
* 02-Jul-12 Vishal Gupta Added tablespace name as input
|
|
* 15-Jun-12 Vishal Gupta FORMATted output
|
|
* 26-May-04 Vishal Gupta Created
|
|
*/
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE INST_ID
|
|
UNDEFINE TABLESPACE_NAME
|
|
UNDEFINE TOP_ROWCOUNT
|
|
UNDEFINE WHERE_CLAUSE
|
|
|
|
DEFINE INST_ID="&&1"
|
|
DEFINE TABLESPACE_NAME="&&2"
|
|
DEFINE TOP_ROWCOUNT="&&3"
|
|
DEFINE WHERE_CLAUSE="&&4"
|
|
|
|
|
|
COLUMN _INST_ID NEW_VALUE INST_ID NOPRINT
|
|
COLUMN _TABLESPACE_NAME NEW_VALUE TABLESPACE_NAME NOPRINT
|
|
COLUMN _TOP_ROWCOUNT NEW_VALUE TOP_ROWCOUNT NOPRINT
|
|
COLUMN _TOP_ROWCOUNT_SCHEMA NEW_VALUE TOP_ROWCOUNT_SCHEMA NOPRINT
|
|
COLUMN _WHERE_CLAUSE NEW_VALUE WHERE_CLAUSE NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&INST_ID','','%','&&INST_ID') "_INST_ID"
|
|
, DECODE('&&TABLESPACE_NAME','','%','&&TABLESPACE_NAME') "_TABLESPACE_NAME"
|
|
, TRIM(DECODE('&&TOP_ROWCOUNT','',30,'&&TOP_ROWCOUNT')) "_TOP_ROWCOUNT"
|
|
, TRIM(ROUND(TRIM(DECODE('&&TOP_ROWCOUNT','',30,'&&TOP_ROWCOUNT'))/2)) "_TOP_ROWCOUNT_SCHEMA"
|
|
, DECODE('&&WHERE_CLAUSE','','','&&WHERE_CLAUSE') "_WHERE_CLAUSE"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT ***************************************************************
|
|
PROMPT * Input Parameters
|
|
PROMPT * - INST_ID = '&&INST_ID'
|
|
PROMPT * - TABLESPACE_NAME = '&&TABLESPACE_NAME'
|
|
PROMPT * - TOP_ROWCOUNT = '&&TOP_ROWCOUNT'
|
|
PROMPT * - WHERE_CLAUSE = '&&WHERE_CLAUSE'
|
|
PROMPT ***************************************************************
|
|
|
|
|
|
PROMPT
|
|
PROMPT ***************************************************************
|
|
PROMPT * TOP &&TOP_ROWCOUNT_SCHEMA Schemas using tempspace
|
|
PROMPT ***************************************************************
|
|
|
|
COLUMN used HEADING "Size|(MB)" FORMAT 99,999,999
|
|
COLUMN username FORMAT a20
|
|
COLUMN tablespace HEADING "TablespaceName" FORMAT a20
|
|
|
|
SELECT *
|
|
FROM
|
|
(
|
|
SELECT /*+ ORDERED */
|
|
su.tablespace
|
|
, s.username
|
|
, SUM((su.BLOCKS * par.value ))/1024/1024 used
|
|
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$system_parameter par ON su.inst_id = par.inst_id AND par.NAME = 'db_block_size'
|
|
WHERE su.inst_id like DECODE('&&INST_ID','','%','&&INST_ID')
|
|
AND su.tablespace LIKE DECODE('&&TABLESPACE_NAME','','%','&&TABLESPACE_NAME')
|
|
&&WHERE_CLAUSE
|
|
GROUP BY s.username , su.tablespace
|
|
order by used desc
|
|
)
|
|
WHERE ROWNUM <= &&TOP_ROWCOUNT_SCHEMA
|
|
/
|
|
|
|
PROMPT
|
|
PROMPT
|
|
PROMPT ***************************************************************
|
|
PROMPT * TOP &&TOP_ROWCOUNT Sort Segment Usage (Ordered by Size)
|
|
PROMPT ***************************************************************
|
|
|
|
|
|
COLUMN used HEADING "Size|(MB)" FORMAT 9,999,999
|
|
COLUMN segtype HEADING "Segment|Type" FORMAT a9
|
|
COLUMN inst_id HEADING "I#" FORMAT 99
|
|
COLUMN sid FORMAT 9999
|
|
COLUMN serial# FORMAT 99999
|
|
COLUMN logon_time HEADING "LogonTime" FORMAT a15
|
|
COLUMN status FORMAT a10
|
|
COLUMN spid FORMAT a6
|
|
COLUMN machine FORMAT a20 TRUNCATE
|
|
COLUMN program FORMAT a20 TRUNCATE
|
|
COLUMN osuser FORMAT a20 TRUNCATE
|
|
COLUMN sql_child_number HEADING "SQL|Child|No" FORMAT 99
|
|
|
|
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
|
|
, s.sql_child_number
|
|
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 DECODE('&&INST_ID','','%','&&INST_ID')
|
|
AND su.tablespace LIKE DECODE('&&TABLESPACE_NAME','','%','&&TABLESPACE_NAME')
|
|
&&WHERE_CLAUSE
|
|
order by 1 desc
|
|
)
|
|
WHERE ROWNUM <= DECODE('&&TOP_ROWCOUNT','',30,'&&TOP_ROWCOUNT')
|
|
/
|
|
|
|
|
|
@@footer
|