167 lines
6.5 KiB
SQL
167 lines
6.5 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display schema Object 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)
|
|
* 4 - Minimum SizeMB (Default 0 MB)
|
|
* 5 - Tablespace ((% - wildchar, default value = %)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------------------
|
|
* 25-Jul-16 Vishal Gupta Added tablespace as the input parameter
|
|
* 02-SEP-14 Vishal Gupta Added object_name and object_type as input parameters
|
|
* 23-May-13 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE owner
|
|
UNDEFINE object_name
|
|
UNDEFINE object_type
|
|
UNDEFINE min_size_mb
|
|
UNDEFINE tbs
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE object_name="&&2"
|
|
DEFINE object_type="&&3"
|
|
DEFINE min_size_mb="&&4"
|
|
DEFINE tbs="&&5"
|
|
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _object_name NEW_VALUE object_name NOPRINT
|
|
COLUMN _object_type NEW_VALUE object_type NOPRINT
|
|
COLUMN _min_size_mb NEW_VALUE min_size_mb NOPRINT
|
|
COLUMN _tbs NEW_VALUE tbs NOPRINT
|
|
|
|
|
|
set term off
|
|
|
|
SELECT UPPER(DECODE('&&owner','','%','&&owner')) "_owner"
|
|
, UPPER(DECODE('&&object_name','','%','&&object_name')) "_object_name"
|
|
, UPPER(DECODE('&&object_type','','%','&&object_type')) "_object_type"
|
|
, DECODE('&&min_size_mb','','100','&&min_size_mb') "_min_size_mb"
|
|
, DECODE('&&tbs','','%','&&tbs') "_tbs"
|
|
FROM DUAL;
|
|
|
|
SELECT CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),1,INSTR('&&owner','.')-1)
|
|
ELSE DECODE(UPPER('&&owner'),'','%',UPPER('&&owner'))
|
|
END "_owner"
|
|
, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1)
|
|
ELSE DECODE(UPPER('&&object_name'),'','%',UPPER('&&object_name'))
|
|
END "_object_name"
|
|
/*, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN DECODE(UPPER('&&object_name'),'','%',UPPER('&&object_name'))
|
|
ELSE DECODE(UPPER('&&object_type'),'','%',UPPER('&&object_type'))
|
|
END "_object_type"
|
|
*/
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT **************************************************
|
|
PROMPT * Input Parameter
|
|
PROMPT * - Owner = '&&owner'
|
|
PROMPT * - Object Name = '&&object_name'
|
|
PROMPT * - Object Type = '&&object_type'
|
|
PROMPT * - Minimum SizeMB = '&&min_size_mb'
|
|
PROMPT * - Tablesapce = '&&tbs'
|
|
PROMPT **************************************************
|
|
PROMPT
|
|
PROMPT
|
|
|
|
PROMPT **************************************************
|
|
PROMPT * Schema Sizes
|
|
PROMPT **************************************************
|
|
|
|
COLUMN owner HEADING "SchemaOwner" FORMAT a20
|
|
COLUMN "Size(GB)" HEADING "Size(GB)" FORMAT 999,999 JUSTIFY RIGHT
|
|
|
|
select owner
|
|
, TO_CHAR(ROUND(SUM(bytes)/1024/1024/1024,2),'999,999,999.99') "Size(GB)"
|
|
from dba_segments s
|
|
where s.owner LIKE '&&OWNER'
|
|
AND s.segment_name LIKE '&&OBJECT_NAME'
|
|
AND s.segment_type LIKE '&&OBJECT_TYPE'
|
|
AND s.tablespace_name LIKE '&&tbs'
|
|
GROUP BY owner
|
|
order by ROUND(SUM(bytes)/1024/1024/1024,2) desc
|
|
;
|
|
|
|
|
|
PROMPT
|
|
PROMPT
|
|
PROMPT **************************************************
|
|
PROMPT * Schema Objects
|
|
PROMPT **************************************************
|
|
|
|
COLUMN owner HEADING "SchemaOwner" FORMAT a20
|
|
COLUMN segment_name HEADING "SegmentName" FORMAT a30
|
|
COLUMN segment_type HEADING "SegmentType" FORMAT a15
|
|
COLUMN segment_size HEADING "Total Size (MB)" FORMAT 999,999,999,999 JUSTIFY RIGHT
|
|
COLUMN lob_size HEADING "Lob Size (MB)" FORMAT 999,999,999,999 JUSTIFY RIGHT
|
|
COLUMN tablespace_name HEADING "TablespaceName" FORMAT a30
|
|
|
|
|
|
SELECT ROUND(SUM(s.bytes)/power(1024,2)) segment_size
|
|
, s.owner
|
|
, DECODE(s.segment_type
|
|
, 'LOBSEGMENT',l.table_name
|
|
, 'LOBINDEX' ,l.index_name
|
|
, s.segment_name
|
|
) segment_name
|
|
, DECODE( SUBSTR(s.segment_type,1,DECODE(INSTR(s.segment_type,' '),0,LENGTH(s.segment_type),INSTR(s.segment_type,' ')-1))
|
|
, 'LOBSEGMENT','TABLE'
|
|
, 'LOBINDEX' ,'INDEX'
|
|
, SUBSTR(s.segment_type,1,DECODE(INSTR(s.segment_type,' '),0,LENGTH(s.segment_type),INSTR(s.segment_type,' ')-1))
|
|
) segment_type
|
|
, s.tablespace_name
|
|
, ROUND(SUM(DECODE(s.segment_type
|
|
, 'LOBSEGMENT',s.bytes
|
|
, 'LOBINDEX' ,s.bytes
|
|
, 0 ))/power(1024,2)) lob_size
|
|
FROM dba_segments s
|
|
LEFT OUTER JOIN dba_lobs l ON l.owner = s.owner AND l.segment_name = s.segment_name AND (s.segment_type = 'LOBSEGMENT' or s.segment_type = 'LOBINDEX')
|
|
WHERE s.owner LIKE '&&OWNER'
|
|
AND DECODE(s.segment_type
|
|
, 'LOBSEGMENT',l.table_name
|
|
, 'LOBINDEX' ,l.index_name
|
|
, s.segment_name
|
|
) LIKE '&&OBJECT_NAME'
|
|
AND DECODE( SUBSTR(s.segment_type,1,DECODE(INSTR(s.segment_type,' '),0,LENGTH(s.segment_type),INSTR(s.segment_type,' ')-1))
|
|
, 'LOBSEGMENT','TABLE'
|
|
, 'LOBINDEX' ,'INDEX'
|
|
, SUBSTR(s.segment_type,1,DECODE(INSTR(s.segment_type,' '),0,LENGTH(s.segment_type),INSTR(s.segment_type,' ')-1))
|
|
) LIKE '&&OBJECT_TYPE'
|
|
AND s.tablespace_name LIKE '&&tbs'
|
|
GROUP BY s.owner
|
|
, DECODE(s.segment_type
|
|
, 'LOBSEGMENT',l.table_name
|
|
, 'LOBINDEX' ,l.index_name
|
|
, s.segment_name
|
|
)
|
|
, DECODE( SUBSTR(s.segment_type,1,DECODE(INSTR(s.segment_type,' '),0,LENGTH(s.segment_type),INSTR(s.segment_type,' ')-1))
|
|
, 'LOBSEGMENT','TABLE'
|
|
, 'LOBINDEX' ,'INDEX'
|
|
, SUBSTR(s.segment_type,1,DECODE(INSTR(s.segment_type,' '),0,LENGTH(s.segment_type),INSTR(s.segment_type,' ')-1))
|
|
)
|
|
, s.tablespace_name
|
|
HAVING ROUND(SUM(s.bytes)/power(1024,2)) > &&min_size_mb
|
|
ORDER BY segment_size desc
|
|
;
|
|
|
|
@@footer
|