Tablespace Usage

set pagesize 200;
set feedback off;
set heading off;

column TABLESPACE_NAME format a19
column free_kb format 999,999,999
column total_kb format 999,999,999
column percent_used format 999.99 heading "%_USED"
column countf format 999 heading "FILES"

spool tbsp_size_${ORACLE_SID}.txt
select INSTANCE_NAME from V_$INSTANCE;
set heading on;
select  a.TABLESPACE_NAME,
        (b.BYTES/1024) free_kb,
        (a.BYTES/1024) total_kb,
        round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used,
        a.COUNTF
from
        (
                select  TABLESPACE_NAME,
                        sum(BYTES) BYTES,
                        count(FILE_ID) COUNTF
                from    dba_data_files
                group   by TABLESPACE_NAME
        )
        a,
        (
                select  TABLESPACE_NAME,
                        sum(BYTES) BYTES
                from    dba_free_space
                group   by TABLESPACE_NAME
        )
        b
where   a.TABLESPACE_NAME=b.TABLESPACE_NAME
order   by ((a.BYTES-b.BYTES)/a.BYTES) desc ;

-- This part will show all INVALID objectts
select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status from
dba_objects where status <> 'VALID';

-- This part will show all UNUSABLE indexes
select i.owner,i.table_name,i.index_name,partition_name,p.status
from dba_ind_partitions p,
dba_indexes i
where p.index_name = i.index_name
and p.status = 'UNUSABLE'
order by partition_name

-- This part will show RMAN backup statuss
set heading off;
select INPUT_TYPE || ' Bakup ' || initcap(STATUS) || ' ' ||
  to_char(START_TIME,'mm/dd/yyyy') || ' in ' || TIME_TAKEN_DISPLAY
from v$rman_backup_job_details
where START_TIME > sysdate - 1
order by session_key desc;
select 'Db size' || INPUT_BYTES_DISPLAY || ', Backup size ' || OUTPUT_BYTES_DISPLAY || 
  ', Compression ' || round(COMPRESSION_RATIO,1) "Compression"
from v$rman_backup_job_details
where START_TIME > sysdate - 1
order by session_key desc;

spool off;
1