Adminstration-Status

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

/

Is java installed in the database?

This will return 9000'ish if it is...

select count(*)

from all_objects

where object_type like '%JAVA%'

and owner = 'SYS'

/

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

/