CASS

Archive for June, 2010

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

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 off

set pages 1000 lines 160
col TABLESPACE_NAME format a20
col TOT_FLS       format 9,999 head "Total|Files"
col STAT_FLS      format 9,999 head "Static|Files"
col NO_FRAGS      format 9,999 head "Free|Frags"
col AUTOX_FLS     format 9,999 head "Auto X|Files"
col CUR_TOT_MB    format 999,999 head "Total Mbs|Current"
col CUR_USED      format 999,999 head "Used Mbs|Current"
col AVAIL_GROW_MB format 999,999 head "Grow|Mbs"
col MAX_MB        format 999,999 head "Max Total|Mbs"
col FREE_MB       format 999,999 head "Free Mbs|Current"
col LRGST_FREE    format 999,999 head "Lrgst|Free"
col PCT_USED_CUR  format 999 head "% Used|Current"
col PCT_FREE_CUR  format 999 head "% Free|Current"
col PCT_FREE_POSS format 999 head "% Free|Possible" 

set heading off timing off
select ''  from dual;
select '['||sysdate||']['||VERSION||'] '||INSTANCE_NAME||' - '||HOST_NAME||'
---------------------------------------------------------------------------- ' from  v$instance;
set heading on 

select a.TABLESPACE_NAME,  a.CUR_TOT_MB, a.CUR_TOT_MB-b.FREE_MB CUR_USED, b.FREE_MB,
round((((a.CUR_TOT_MB-b.FREE_MB)*100)/CUR_TOT_MB)) PCT_USED_CUR, b.NO_FRAGS, b.LRGST_FREE,
a.TOT_FLS, a.STAT_FLS, a.AUTOX_FLS,a.AVAIL_GROW_MB, a.MAX_MB,
round(((FREE_MB*100)/CUR_TOT_MB)) PCT_FREE_CUR ,round((((FREE_MB+AVAIL_GROW_MB)*100)/MAX_MB)) PCT_FREE_POSS
from
     (select TABLESPACE_NAME,
             TOT_FLS,
             TOT_FLS - AUTOX_FLS as STAT_FLS,
             AUTOX_FLS,
             round(TOT_BYTES/(1024*1024)) CUR_TOT_MB,
             round((MAX_BYTES-TOT_BYTES)/(1024*1024)) AVAIL_GROW_MB,
             round(MAX_BYTES/(1024*1024)) MAX_MB
      from ( select TABLESPACE_NAME, sum(BYTES) as TOT_BYTES,
                    sum(decode(AUTOEXTENSIBLE,'YES',1,0)) as AUTOX_FLS,
                    count(*) as TOT_FLS,
                    sum(decode(AUTOEXTENSIBLE,'YES',MAXBYTES,BYTES)) MAX_BYTES
             from dba_data_files
             group by TABLESPACE_NAME)) a,
     (select TABLESPACE_NAME,
             round(sum(BYTES)/(1024*1024),2) FREE_MB ,
             round(max(BYTES)/(1024*1024),2) LRGST_FREE,
             count(*) NO_FRAGS
      from dba_free_space
      group by TABLESPACE_NAME) b
where b.TABLESPACE_NAME (+) =  a.TABLESPACE_NAME
order by PCT_USED_CUR;