SQL Script to Report on Space in Tablespaces in Oracle Database

The script provided below will report On Space Usage in Tablespaces. Access Privileges required:

SELECT on DBA_FREE_SPACE, DBA_DATA_FILES, DBA_SEGMENTS, DBA_EXTENTS

1. Connect to the database having above access privileges.

2. Copy the below script titled “space_in_tablespaces” and execute it from SQL*Plus.

The SQL script

SET ECHO off
REM NAME:   TFSTSNFO.SQL
REM USAGE:"@path/tfstsnfo"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on DBA_FREE_SPACE, DBA_DATA_FILES, DBA_SEGMENTS, DBA_EXTENTS
REM ------------------------------------------------------------------------
REM PURPOSE:
REM    This script displays information about space in tablespaces,
REM fragmentation and potential extent-problems. It has three sections:
REM
REM    1. Space available per tablespace.
REM       Shows total size in bytes, total free space in bytes,
REM       percentage free space to total space,
REM       the size of the largest contiguous free segment and
REM
REM    2. Lists tables and indexes with more than 20 extents.
REM
REM    3. Lists tables/indexes whose next extent will not fit
REM
REM Main text of script follows:

set pagesize 300

set linesize 120
column sumb format 9,999,999,999,999
column extents format 999999
column bytes format 9,999,999,999,999
column largest format 9,999,999,999,999
column Tot_Size format 9,999,999,999,999
column Tot_Free format 9,999,999,999,999
column Pct_Free format 9,999,999,999,999
column Chunks_Free format 9,999,999,999,999
column Max_Free format 9,999,999,999,999
set echo off
spool TFSTSNFO.SQL

PROMPT  SPACE AVAILABLE IN TABLESPACES

select a.tablespace_name,sum(a.tots) Tot_Size,
sum(a.sumb) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
sum(a.largest) Max_Free,sum(a.chunks) Chunks_Free
from
(
select tablespace_name,0 tots,sum(bytes) sumb,
max(bytes) largest,count(*) chunks
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0,0,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name;

column owner format a15
column segment_name format a30


PROMPT   SEGMENTS WITH MORE THAN 20 EXTENTS

select owner,segment_name,extents,bytes ,
max_extents,next_extent
from  dba_segments
where segment_type in ('TABLE','INDEX') and extents>20
order by owner,segment_name;



PROMPT SEGMENTS WHERE THERE'S NOT ENOUGH ROOM FOR THE NEXT EXTENT


select  a.owner, a.segment_name, b.tablespace_name,
     decode(ext.extents,1,b.next_extent,
     a.bytes*(1+b.pct_increase/100)) nextext,
     freesp.largest
from    dba_extents a,
     dba_segments b,
     (select owner, segment_name, max(extent_id) extent_id,
     count(*) extents
     from dba_extents
     group by owner, segment_name
     ) ext,
     (select tablespace_name, max(bytes) largest
     from dba_free_space
     group by tablespace_name
     ) freesp
where   a.owner=b.owner and
     a.segment_name=b.segment_name and
     a.owner=ext.owner and
     a.segment_name=ext.segment_name and
     a.extent_id=ext.extent_id and
     b.tablespace_name = freesp.tablespace_name and
     decode(ext.extents,1,b.next_extent,
     a.bytes*(1+b.pct_increase/100)) > freesp.largest
/
spool off

Sample Output

SPACE AVAILABLE IN TABLESPACES

TABLESPACE_NAME                    TOT_SIZE     TOT_FREE     PCT_FREE MAX_FREE     CHUNKS_FREE
------------------------------ ------------ ------------ ------------ ------------ ------------
DES2                               41,943,040  30,935,040          74  30,935,040            1
DES2_I                             31,457,280  23,396,352          74  23,396,352            1
RBS                                60,817,408  57,085,952          94  52,426,752           16
SYSTEM                             94,371,840   5,386,240           6   5,013,504            3
TEMP                                  563,200     561,152         100     133,120            5
TOOLS                             120,586,240   89,407,488         74  78,190,592           12
USERS                               1,048,576       26,624          3      26,624            1

SEGMENTS WITH MORE THAN 20 EXTENTS

OWNER      SEGMENT_NAME    EXTENTS BYTES     MAX_EXTENTS NEXT_EXTENT
---------- --------------- ------- --------- ----------- -----------
SYSCASE    TLN_PRIME            46   468,992         121       10240



SEGMENTS WHERE THERE'S NOT ENOUGH ROOM FOR THE NEXT EXTENT

no rows selected