SET SERVEROUTPUT ON
SET ECHO OFF
SET FEEDBACK OFF
SET PAGES 80
SET LINES 132

REM
REM
REM || Title :tablespace_free.sql
REM ||
REM || Purpose : Script show free space in tablespaces, also object that cannot extend.
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : system
REM ||
REM

COLUMN sun_bytes FORMAT 999,999,999,999
COLUMN extents FORMAT 9999
COLUMN bytes FORMAT 999,999,999,999
COLUMN largest FORMAT 999,999,999,999
COLUMN Tot_Size FORMAT 999,999,999,999
COLUMN Tot_Free FORMAT 999,999,999,999
COLUMN Pct_Free FORMAT 999,999,999,999
COLUMN Chunks_Free FORMAT 999,999,999,999
COLUMN Max_Free FORMAT 999,999,999,999
COLUMN owner FORMAT a15
COLUMN segment_name FORMAT a30

SPOOL Tablespace_util.lis

PROMPT SPACE AVAILABLE IN TABLESPACES
PROMPT
PROMPT

SELECT a.tablespace_name, SUM (a.tots) tot_size, SUM (a.sun_bytes) tot_free,
SUM (a.sun_bytes) * 100 / SUM (a.tots) pct_free,
SUM (a.largest) max_free, SUM (a.chunks) chunks_free
FROM (SELECT tablespace_name, 0 tots, SUM (bytes) sun_bytes,
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;

PROMPT
PROMPT
PROMPT
PROMPT SEGMENTS WITH MORE THAN 15 EXTENTS
PROMPT
PROMPT

SELECT owner, segment_name, extents, bytes, max_extents, next_extent
FROM dba_segments
WHERE segment_type IN ('TABLE', 'INDEX')
AND extents > 15
ORDER BY owner, segment_name;

PROMPT
PROMPT
PROMPT SEGMENTS THAT CANNOT EXTEND
PROMPT
PROMPT

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
/

PROMPT
PROMPT
PROMPT

SPOOL OFF

CLEAR BREAKS
CLEAR COL