Cheeky Cockatoo

Life Advice and Stories, Travel, Oracle Tech, Good Food and More

space_report.sql

Download Script

rem  name:      space_report.sql
rem
rem  purpose:   Three queries to monitor overall space use in a database.
rem
rem  usage:     
rem
rem  parameters:
rem     &1 = database name,   used for the spool file name.
rem	    &2 = spool file name
rem ..........................................................................

rem .........................................................................
rem setup environment
rem .........................................................................
set feedback off
set verify off
set echo on 
set linesize 80
set pagesize 24
define db = &&1
define outfile = &&2
column segment_name	format a24 trunc head "Segment|Name"
column segment_type	format a10 trunc head "Segment|Type"
drop table space_report
/
drop table max_next
/
column db new_value new_db
column time new_value new_time
select upper('&&db') db, to_char(sysdate,'DD-MON-YYYY HH24:MI') time from dual
/
spool &&outfile
prompt &&new_db Database Space Report
prompt &&new_time
create table space_report 
(tablespace_name char(30),
 used_bytes      number,
 free_bytes      number,
 largest         number,
 free_extents    number)
/
insert into space_report (tablespace_name, used_bytes)
select tablespace_name, sum(dba_extents.bytes)
from dba_extents
group by tablespace_name
/
insert into space_report (tablespace_name, used_bytes)
 (select TABLESPACE_NAME, 0 from dba_tablespaces
  where TABLESPACE_NAME not in 
  (select distinct TABLESPACE_NAME from dba_extents)
 )
/
update space_report sr set (free_bytes, largest, free_extents) =
(select sum(fs.bytes), max(bytes), count(*) from dba_free_space fs
 where rtrim(sr.tablespace_name) = fs.tablespace_name
 group by fs.tablespace_name)
/
prompt
prompt
prompt System, data, and index tablespaces should not have less than 10% free space.
column tablespace_name	format a15 trunc	heading "TABLESPACE"
column used_bytes	format 999,999,999,999	heading "USED BYTES"
column free_bytes	format 999,999,999,999	heading "FREE BYTES"
column pctfree		format 999.9		heading "PCTFREE"
column largest		format 9,999,999,999	heading "LARGEST EXT"
column free_extents	format 9999		heading "FREE|FRGS"
select tablespace_name,
       used_bytes,
       free_bytes,
       100*free_bytes/(used_bytes+free_bytes) "pctfree",
       largest,
       free_extents
from space_report
/
prompt
prompt
prompt Objects which next extents WILL NOT FIT in the tablespace:
create table max_next
(tablespace_name char(30),
 object_name      char(30),
 next             number,
 type             char(8))
/
insert into max_next 
 select dba_tables.TABLESPACE_NAME,
        dba_tables.table_name,
        dba_tables.NEXT_EXTENT,
        'TABLE'
 from dba_tables, space_report
 where dba_tables.TABLESPACE_NAME = space_report.TABLESPACE_NAME
 and   space_report.largest < dba_tables.NEXT_EXTENT
/
insert into max_next
 select dba_indexes.TABLESPACE_NAME,
        dba_indexes.index_name,
        dba_indexes.NEXT_EXTENT,
        'INDEX'
 from dba_indexes, space_report
 where dba_indexes.TABLESPACE_NAME = space_report.TABLESPACE_NAME
 and   space_report.largest < dba_indexes.NEXT_EXTENT
/
insert into max_next
 select dba_rollback_segs.tablespace_name,
        dba_rollback_segs.segment_name,
        dba_rollback_segs.next_extent,
        'ROLLBACK'
 from dba_rollback_segs, space_report
 where dba_rollback_segs.status = 'IN USE'
 and   dba_rollback_segs.tablespace_name = space_report.tablespace_name
 and   space_report.largest < dba_rollback_segs.next_extent
/
break on tablespace_name
column tablespace_name	format a25
column object_name	format a25
column next		format 999,999,999
select * from max_next order by tablespace_name
/
prompt
prompt
prompt Objects extended to more than 60% but less than 100% of max_extents,
prompt excluding cache segments:
column owner		format a15 trunc
column MAX_EXTS		format a7 heading "Max|Ext"
select segment_name, 
       segment_type,
       owner,
       extents,
       decode ( max_extents, 2147483645, 'NoLimit', max_extents ) MAX_EXTS
from dba_segments
where extents/max_extents > .6
  and extents <> max_extents
  and segment_type <> 'CACHE'
order by segment_name
/
prompt
prompt
spool off
drop table max_next
/
drop table space_report
/
exit