Square DBA – Oracle DBA Talk

To shrink a tablespace in Oracle you actually need to shrink the datafiles that make up the tablespace. You can only shrink a datafile down to the high water mark (HWM). There are several methods to reduce the HWM, but I am not going to address those in this post. If you want to return some space back to the OS or ASM disk you may be able to run a few commands and not need to worry about trying to adjust the HWM.

Running the following script will output the necessary SQL to reduce the size of your datafiles. Again this will only give you the syntax to reduce the datafiles to the HWM.

SET VERIFY OFF
COLUMN SMALLEST FORMAT 999,990
COLUMN CURRSIZE FORMAT 999,990
COLUMN SAVINGS FORMAT 999,990
COLUMN FILE_NAME FORMAT A50 WORD_WRAPPED
SET PAGES 60

BREAK ON REPORT
COMPUTE SUM OF SAVINGS ON REPORT 

COLUMN VALUE NEW_VAL BLKSIZE 

SELECT FILE_NAME,
CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) -
CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
FROM DBA_DATA_FILES DBADF,
( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM
FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+);