Important Queries

--Query Product Information

select * from product_component_version;

--Query Row Count for all Tables using stats

select owner table_name, num_rows from dba_tables where num_rows > 0;

--Query Row Count For All Tables without stats

select 'Select count(*) from ' ||owner|| '.' ||table_name|| ';' from dba_all_tables order by owner, table_name;

--Query all Active Users details

select sid, serial#,user#, Username, machine, program, server, status, command, type from v$session order by username;

--Query  all Index details

select owner, index_name, table_type, tablespace_name from dba_indexes where  owner <>'SYSTEM' and owner <> 'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <> 'SYS' and owner <> 'SYSTEM' order by owner, index_name, tablespace_name

--Query  All Tables

select owner, table_name, table_type, tablespace_name from dba_all_tables where owner <>'SYSTEM' and owner <> 'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <> 'SYS' and owner <> 'SYSTEM' order by owner, table_name, tablespace_name

--Query  Space Used

select Tablespace_Name, /*Tablespace name*/ Owner, /*Owner of the segment*/ Segment_Name, /*Name of the segment*/ Segment_Type, /*Type of segment (ex. TABLE, INDEX)*/ Extents, /*Number of extents in the segment*/ Blocks, /*Number of db blocks in the segment*/ Bytes /*Number of bytes in the segment*/ from DBA_SEGMENTS where owner <>'SYSTEM' and owner <> 'DBSNMP' and owner <> 'ORDSYS' and owner <> 'OUTLN' and owner <> 'SYS' and owner <> 'SYSTEM'

--Query Sum Space By Owner

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

--Query Sum Space by Tablespace

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

--Query  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#

--Query  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)

--Query s Archived Redo Log Information

select * from v$database  

--Query  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

--Query  All Online Redo Log Groups For The database

select * from v$log 

--Query  All Datafiles For Tablespace And Oracle Stuff

select * from dba_data_files order by tablespace_name, file_name