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;