73 lines
1.9 KiB
SQL
73 lines
1.9 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display schema sizes breakdown
|
|
* Compatibility :
|
|
* Parameters : 1 - owner (% - wildchar, \ - escape char) , this could also be passed as OWNER.OBJECT_NAME
|
|
* 2 - Object name (% - wildchar, \ - escape char)
|
|
* 3 - Object Type (% - 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 owner
|
|
UNDEFINE WHERECLAUSE
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE WHERECLAUSE="&&2"
|
|
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
|
|
set term off
|
|
|
|
SELECT UPPER(DECODE('&&owner','','%','&&owner')) "_owner"
|
|
FROM DUAL;
|
|
|
|
set term on
|
|
|
|
|
|
PROMPT **************************************************
|
|
PROMPT * Schema Sizes Breakdown
|
|
PROMPT *
|
|
PROMPT * Input Parameter
|
|
PROMPT * - Owner = '&&owner'
|
|
PROMPT * - Where Clause = '&&WHERECLAUSE'
|
|
PROMPT **************************************************
|
|
PROMPT
|
|
|
|
COLUMN owner HEADING "Schema Owner" FORMAT a30
|
|
COLUMN segment_type HEADING "Schema Type" FORMAT a20
|
|
COLUMN "Size(GB)" HEADING "Size (GB)" FORMAT 999,999,999.99 JUSTIFY RIGHT
|
|
|
|
BREAK ON OWNER SKIP 1
|
|
|
|
select owner
|
|
, segment_type
|
|
, ROUND(SUM(bytes)/1024/1024/1024,2) "Size(GB)"
|
|
from dba_segments s
|
|
where s.owner LIKE '&&OWNER'
|
|
&&WHERECLAUSE
|
|
GROUP BY owner
|
|
, segment_type
|
|
order by s.owner, ROUND(SUM(bytes)/1024/1024/1024,2) desc
|
|
;
|
|
|
|
|
|
|
|
@@footer
|