68 lines
1.7 KiB
SQL
68 lines
1.7 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display schema sizes
|
|
* 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
|
|
PROMPT *
|
|
PROMPT * Input Parameter
|
|
PROMPT * - Owner = '&&owner'
|
|
PROMPT * - Where Clause = '&&WHERECLAUSE'
|
|
PROMPT **************************************************
|
|
PROMPT
|
|
|
|
COLUMN owner HEADING "SchemaOwner" FORMAT a20
|
|
COLUMN total_size HEADING "Size (MB)" FORMAT 999,999,999 JUSTIFY RIGHT
|
|
|
|
select owner
|
|
,ROUND(SUM(bytes)/power(1024,2)) total_size
|
|
from dba_segments s
|
|
where s.owner LIKE '&&OWNER'
|
|
&&WHERECLAUSE
|
|
GROUP BY owner
|
|
order by total_size desc
|
|
;
|
|
|
|
|
|
|
|
@@footer
|