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;



Comments

Popular posts from this blog

How to create Oracle stored Procedures using TOAD for Oracle

Create Tables in Oracle Database using TOAD

How to Create Packages in Oracle Database using TOAD for Oracle