Administration - Status

Startup time

select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time"
from v$instance
/

How large is the database

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" , round(sum(used.bytes) / 1024 / 1024 / 1024 ) - round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" , round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used , (select sum(bytes) as p from dba_free_space) free group by free.p
/

Distribution of objects and data

Which schemas are taking up all of the space

set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from  (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
 from dba_segments group by owner) seg
where  obj.owner  = seg.owner(+)
order by 3 desc ,2 desc, 1
/ 

Show the ten largest objects in the database

col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner , segment_name , segment_type , mb from ( select owner , segment_name , segment_type , bytes / 1024 / 1024 "MB" from dba_segments order by bytes desc ) where rownum < 11
/

Display character set information

select * from nls_database_parameters
/ 

Show all used features

select name , detected_usages from dba_feature_usage_statistics where detected_usages > 0 /