Basic Scripts

Check Database Status

set lines 200
col startup_time for A25
col host_name for A25
alter session set nls_date_format='DD-MON-YYYY hh24:mi:ss';
select sysdate,instance_name,startup_time,open_mode,log_mode,host_name from v$instance, v$database;







Total database size,free space,used space on db level

select
sysdate "DATE",
i.instance "INSTANCE",
a.TOTAL_GB,
u.USED_GB,
f.FREE_GB
from (select instance_name instance from v$instance) i,
(select round(sum(bytes/1024/1024/1024),2) TOTAL_GB from dba_data_files) a,
(select round(sum(bytes/1024/1024/1024),2) USED_GB from sm$ts_used) u,
(select round(sum(bytes/1024/1024/1024),2) FREE_GB from sm$ts_free) f;







 Total database size,free space,used space on TBS level

select
sysdate "DATE",
i.instance "INSTANCE",
f.tablespace_name,
a.total_mb,
u.used_mb,
f.free_mb,
f.maxfree_mb
from (select instance_name instance from v$instance) i,
(select tablespace_name, sum(bytes/(1024*1024)) total_mb from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) used_mb from dba_extents group by tablespace_name) u,
(select tablespace_name, round(sum(bytes/(1024*1024))) free_mb,round(max(bytes)/1024/1024) maxfree_mb from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
and a.tablespace_name = u.tablespace_name
order by f.free_mb;

 





 Free space of temp tablespace.

select tablespace_name,bytes_used/1024/1024 "USED_MB",bytes_free/1024/1024 "Free Mbs",(bytes_used+bytes_free)/1024/1024 "TOTAL MB"  from v$TEMP_SPACE_HEADER;

 

 

 

Find top 5 segments size in DB

select * from (select owner,segment_name,segment_type,bytes/1024/1024"Mb" from dba_segments order by bytes/1024/1024 desc) where rownum<5;