@@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