Important Oracle Scripts for Database Administrators (Part-3)

Sum Space By Owner

select owner, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS group by owner;

Sum Space by Tablespace

select tablespace_name, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS  group by tablespace_name;

Show reads and writes by file name in oracle DB

select v$datafile.name "File Name", v$filestat.phyrds "Reads", v$filestat.phywrts "Writes" from v$filestat,v$datafile where v$filestat.file# = v$datafile.file# ;

 Show Versions Of Software

select * from V$VERSION;

 Identify segments that are getting close to their max-extent values

select owner,tablespace_name,segment_name,bytes,extents,max_extents from dba_segments where extents*2 > max_extents;

 Identifies segments that are getting close to running out of contiguous free space

select owner, s.tablespace_name, segment_name, s.bytes, next_extent, max(f.bytes) largest from dba_segments s, dba_free_space f where s.tablespace_name = f.tablespace_name(+) group by owner, s.tablespace_name, segment_name, s.bytes, next_extent having next_extent*2 >max(f.bytes);

 Displays Archived Redo Log Information

select * from v$database;

 Display Count Historical Archived Log Information From The Control File

select count(*) from v$archived_log;

select min(completion_time) from v$archived_log;

 Shows Current Archive Destinations

select * from v$archive_dest;

Backups Of Archived Logs

select count(*) from v$backup_redolog;

Display All Online Redo Log Groups For The database

select * from v$log;

Show All Datafiles For Tablespace And Oracle Stuff

select * from dba_data_files order by tablespace_name, file_name;