77 lines
2.1 KiB
SQL
77 lines
2.1 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display tablespace usage by schema
|
|
* Compatibility :
|
|
* Parameters : 1 - tablespace_name (% - wildchar, \ - escape char)
|
|
* 2 - owner (% - wildchar, \ - escape char)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------------------
|
|
* 24-Jul-15 Vishal Gupta Split original schema_size.sql
|
|
* into schema_size.sql and schema_size_details.sql
|
|
* 23-May-13 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE tablespace_name
|
|
UNDEFINE owner
|
|
UNDEFINE WHERECLAUSE
|
|
|
|
DEFINE tablespace_name="&&1"
|
|
DEFINE owner="&&2"
|
|
DEFINE WHERECLAUSE="&&3"
|
|
|
|
|
|
COLUMN _tablespace_name NEW_VALUE tablespace_name NOPRINT
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
|
|
set term off
|
|
|
|
SELECT UPPER(DECODE('&&tablespace_name','','%','&&tablespace_name')) "_tablespace_name"
|
|
, UPPER(DECODE('&&owner','','%','&&owner')) "_owner"
|
|
FROM DUAL;
|
|
|
|
set term on
|
|
|
|
|
|
PROMPT **************************************************
|
|
PROMPT * Schema Sizes
|
|
PROMPT *
|
|
PROMPT * Input Parameter
|
|
PROMPT * - Tablespace = '&&tablespace_name'
|
|
PROMPT * - Owner = '&&owner'
|
|
PROMPT * - Where Clause = '&&WHERECLAUSE'
|
|
PROMPT **************************************************
|
|
PROMPT
|
|
|
|
COLUMN tablespace_name HEADING "Tablespace" FORMAT a30
|
|
COLUMN owner HEADING "SchemaOwner" FORMAT a30
|
|
COLUMN total_size HEADING "Size (MB)" FORMAT 999,999,999 JUSTIFY RIGHT
|
|
|
|
select tablespace_name
|
|
, owner
|
|
, ROUND(SUM(bytes)/power(1024,2)) total_size
|
|
from dba_segments s
|
|
where 1=1
|
|
AND s.tablespace_name LIKE '&&tablespace_name'
|
|
AND s.owner LIKE '&&OWNER'
|
|
&&WHERECLAUSE
|
|
GROUP BY tablespace_name
|
|
, owner
|
|
order by total_size desc
|
|
;
|
|
|
|
|
|
|
|
@@footer
|