Database Status

• Tablespaces

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

Database Free space report

From asktom

• Tablespace Name: name of tablespace, leading '*' indicates a good locally managed tablespace, leading blank means it is a bad dictionary managed tablespace. Second character of A implies ASSM managed storage, second character of M implies manually managed (pctused, freelists, etc are used to control space utilization)
• Kbytes: allocated space of the tablespace, sum of kbytes consumed by all datafiles associated with tablespace.
• Used: space in the tablespace that is used by some segment.
• Free: space in the tablespace not allocated to any segment.
• %Used: ratio of free to allocated space
• largest: mostly useful with dictionary managed tablespaces, the size of the largest contigously set of blocks available. If this number in a dictionary managed tablespace is smaller than the next extent for some object, that object could fail with "out of space" even if the FREE column says there is lots of free space.
• MaxPoss Kbytes: the autoextend max size (note CAN be smaller than the allocated size!!!! you can set the maxsize to be less than the current size of a file)
• %Max Used: how much of the maximum autoextend size has been used so far

set linesize 132
-------------------------------------------------------
-- free.sql  
--
-- This SQL Plus script lists freespace by tablespace
--------------------------------------------------------

column dummy noprint
column  pct_used format 999.9       heading "%|Used" 
column  name    format a19      heading "Tablespace Name" 
column  Mbytes   format 999,999,999    heading "MBytes" 
column  used    format 999,999,999   heading "Used" 
column  free    format 999,999,999  heading "Free" 
column  largest    format 999,999,999  heading "Largest" 
column  max_size format 9,999,999,999,999 heading "MaxPoss|Mbytes"
column  pct_max_used format 999.9       heading "%|Max|Used" 
break   on report 
compute sum of mbytes on report 
compute sum of free on report 
compute sum of used on report 

select (select decode(extent_management,'LOCAL','*',' ') ||
               decode(segment_space_management,'AUTO','a ','m ')
              from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,
                         nvl(a.tablespace_name,'UNKOWN')) name,
           mbytes_alloc mbytes,
           mbytes_alloc-nvl(mbytes_free,0) used,
           nvl(mbytes_free,0) free,
           ((mbytes_alloc-nvl(mbytes_free,0))/
                                                  mbytes_alloc)*100 pct_used,
           nvl(largest,0) largest,
           nvl(mbytes_max,mbytes_alloc) Max_Size,
           decode( mbytes_max, 0, 0, (mbytes_alloc/mbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024/1024 mbytes_free,
                          max(bytes)/1024/1024 largest,
                          tablespace_name
           from  sys.dba_free_space
           group by tablespace_name ) a,
     ( select sum(bytes)/1024/1024 mbytes_alloc,
                          sum(maxbytes)/1024/1024 mbytes_max,
                          tablespace_name
           from sys.dba_data_files
           group by tablespace_name
           union all
      select sum(bytes)/1024/1024 mbytes_alloc,
                          sum(maxbytes)/1024/1024 mbytes_max,
                          tablespace_name
           from sys.dba_temp_files
           group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by 8
/

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;