reclaim.sql

-------------------------------------------------------------------
--
-- Script:      reclaim.sql
-- Description: To show reclaimable space if 
--              object(s) re-organised.
--
-- Usage/Notes: Should be run as a DBA privileged user.
--
-- Copyright:   ABCdba.com 2014
--
-------------------------------------------------------------------
 
COL segment_owner    HEA 'Owner'           FOR a30
COL segment_name     HEA 'Name'            FOR a30
COL allocated_mb     HEA 'Allocated MB'    FOR 9,999,999
 
COL reclaimable_mb   HEA 'Reclaimable MB'  FOR 9,999,999
COL type             HEA 'Type'            FOR a6
COL percentage       HEA '%age'            FOR 999
 
SET LINESIZE  132
SET PAGESIZE  100
SET TRIMSPOOL ON
 
SET TIMING    ON
SET WRAP      ON
SET VERIFY    OFF
 
SPOOL reclaim.log
 
PROMPT
PROMPT
ACCEPT segowner PROMPT 'Enter segment owner : '
PROMPT
PROMPT
 
SELECT
   segment_owner,
   segment_name,
   segment_type type,
   CEIL( allocated_space / 1024 / 1024 ) allocated_mb,
   CEIL( reclaimable_space / 1024 / 1024 ) reclaimable_mb,
   ( reclaimable_space / allocated_space ) * 100 percentage
FROM
   table ( dbms_space.asa_recommendations( 'FALSE', 'FALSE', 'FALSE' ) )
WHERE
   segment_owner = UPPER( '&segowner' )
ORDER BY
   percentage
/
 
SPOOL OFF
 
SET TIMING OFF
SET VERIFY ON
SET WRAP OFF
© copyright 2001-2014 ABCdba.com | all rights reserved