Database Status

• Tablespaces

How big is the database?

set lines 132
col dbname for a9  heading "Database"
col dbsize for a15 heading "Size"
col dbused for a15 heading "Used space"
col dbfree for a15 heading "Free space"
with used_space as
(
select  bytes
from    v$datafile
union   all
select  bytes
from    v$tempfile
union   all
select  bytes
from    v$log
)
, free_space as
(
select sum(bytes) sum_bytes
from dba_free_space
)
select vd.name                                                                                            dbname
,      round(sum(used_space.bytes)/1024/1024/1024 ) || ' GB'                                              dbsize
,      round(sum(used_space.bytes)/1024/1024/1024 ) - round(free_space.sum_bytes/1024/1024/1024) || ' GB' dbused
,      round(free_space.sum_bytes/1024/1024/1024) || ' GB'                                                dbfree
from   free_space
,      used_space
,      v$database vd
group  by vd.name
,      free_space.sum_bytes
/

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)
• Mbytes: allocated space of the tablespace, sum of mbytes 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 contiguous 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
-------------------------------------------------------
-- 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;