Difference between revisions of "Database Status"

Revision as of 15:52, 10 November 2012

How big 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

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

colowner format a15
colsegment_name format a30
colsegment_type format a15
colmb 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  1=1
and    rownum < 11;

Is java installed in the database?

This will return 9000'ish if it is

select count(*)
from   all_objects
where  1=1
and    object_type like '%JAVA%'
and    owner = 'SYS';

Show character set information

select * from nls_database_parameters;

Show all used features

select name
,      detected_usages
from   dba_feature_usage_statistics
where  1=1
and    detected_usages > 0;