Overview of an Oracle DB

Quick overview of an Oracle database (these are from a few years back)

-- Oracle files details

select * from dba_data_files

select * from v$logfile

select * from v$log

select * from v$tempfile

select * from v$loghist

-- check the amt of redologging

-- Oracle database & instance details & parameter settings

select * from v$database

select * from v$instance

select * from v$parameter

-- #objects x owner & object_type

select owner, object_type, count(*)

from dba_objects

group by owner, object_type

-- #segments (count) & size (GB) x schema x segment type

select owner, segment_type, count(*), sum(bytes)/1024/1024/1024

from dba_segments

group by owner, segment_type

order by owner, segment_type

-- #segments (count) & size (GB) x schema x segment type per table

select owner, segment_type, count(*), sum(bytes)/1024/1024/1024

from dba_segments

where segment_name like '%%'

group by owner, segment_type

order by 4 desc

-- Listing of schema, segment type & bytes (per table commented out)

select owner, segment_type, segment_name, bytes

from dba_segments

--where segment_name like '%%'

order by 4 desc

-- other useful views

dba_jobs -- scheduled jobs

dba_mviews -- see all 9i mview dictionary views here