CASS

--- users.sql list of users from dba_users
set pages 1000 lines 160 

col OWNER format a20
col SEGMENT_TYPE format a20
col TABLESPACE_NAME format a30
col TYPE format a8
col NAME format a34
col TABLESPACE format a20
col BLOCKS format 999999999
col MBS format 99999 

set termout off serveroutput off heading off timing off
col title_name new_value title_name
select '['||to_char(sysdate,'HH24:MI DD-MON-RR')||' '||INSTANCE_NAME||'@'||HOST_NAME||' '||VERSION||'] ' title_name from  v$instance;
set termout on serveroutput on heading on 

TTitle Left '###  Schema Summary  -  ' title_name skip 2 

select OWNER, SEGMENT_TYPE, TABLESPACE_NAME , round(sum(BYTES)/(1024*1024),2) USED_MB, count(*) NUM_OBJECTS
from dba_segments
group by OWNER, SEGMENT_TYPE, TABLESPACE_NAME
order by OWNER, SEGMENT_TYPE, TABLESPACE_NAME; 

TTitle Left '###  Schema Detail for &&SCHEMA  -  ' title_name skip 2 

select decode(TYPE, 'TABLE','TABLE', 'INDEX','- INDEX',TYPE )TYPE,
decode(TYPE, 'TABLE', TABOWNER, 'INDEX', '- '||INDOWNER,INDOWNER) OWNER,
decode(TYPE, 'TABLE', TABNAME, 'INDEX', '- '||INDNAME,INDNAME) NAME,
TABLESPACE_NAME,NUM_ROWS, DISTINCT_KEYS DISKEYS,BLOCKS , MBS,LAST_ANALYZED
from
(select 'A' DUMMY, s.SEGMENT_TYPE TYPE, t.OWNER TABOWNER,t.TABLE_NAME TABNAME,t.TABLESPACE_NAME , null INDOWNER, null INDNAME,
t.NUM_ROWS,null DISTINCT_KEYS ,t.BLOCKS , round(s.BYTES/(1024*1024)) MBS,t.LAST_ANALYZED
from dba_tables t, dba_segments s
where t.OWNER='&&SCHEMA'
and t.OWNER = s.OWNER
and t.TABLE_NAME = s.SEGMENT_NAME
and s.SEGMENT_TYPE like '%TABLE%'
union
select 'B' DUMMY,s.SEGMENT_TYPE TYPE, TABLE_OWNER as TABOWNER, TABLE_NAME TABNAME, i.TABLESPACE_NAME, i.OWNER INDOWNER, i.INDEX_NAME INDNAME,
i.NUM_ROWS,i.DISTINCT_KEYS,s.BLOCKS, round(s.BYTES/(1024*1024)) MBS,i.LAST_ANALYZED
from dba_indexes i, dba_segments s
where i.OWNER='&&SCHEMA'
and i.OWNER = s.OWNER
and i.INDEX_NAME = s.SEGMENT_NAME
and s.SEGMENT_TYPE like '%INDEX%')
order by  TABOWNER,TABNAME, DUMMY,INDNAME; 

TTitle off