Datafiles Shrink

With respect to data file on Oracle, files keeping growing in size. But over a period of time actual file size differs significantly from the amount of data within the file. Obviously and it is expected with all RDBMS operations there will always be free space in the file.

There are many possible ways to reclaim free unused space, but I like this one. It does reclaims all the free space like export/import but the main advantage is it can be done online while users are using the system. It truncates the file at the end and reclaims entire space above high water mark for auto-extensible files. It does not affect maxsize, but just reduces the size to minimum possible value. Oracle can increase the size anyways as and when required.

Here you go ..

1.       Calculate Database Size : 

SELECT ( (SELECT SUM (BYTES) FROM V$DATAFILE) + (SELECT SUM (BYTES) FROM V$TEMPFILE) + (SELECT SUM(8192*BLOCK_SIZE) FROM V$CONTROLFILE) + (SELECT SUM (MEMBERS*BYTES) FROM V$LOG))/1024/1024/1024 AS DATABASE_SIZE FROM DUAL;

2.       Get the parameter value for DB_BLOCK_SIZE : 

That is 8192 for EBS installations

3.  Calculate HWM for each file. Create temporary table for HWM : 

create table nav_hwm_tmp as (select file_id, max(BLOCK_ID + BLOCKS)* &DB_BLOCK_SIZE / 1024 / 1024 hwm from dba_extents group by file_id );

4.  Generate report for possible space reclaim: 

set verify off
set line 200
column file_name format a60 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column maxsize format 999,990 heading "Max|autoextend|size."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
select a.file_name, a.bytes/1024/1024 currsize, a.maxbytes/1024/1024 maxsize, ceil (b.HWM) smallest, (a.bytes/1024/1024 - ceil (b.HWM)) savings from dba_data_files a, nav_hwm_tmp b where a.autoextensible='YES' and a.file_id=b.file_id order by savings;

5.  Spool commands for space reclaim: 

spool space_reclaim.txt
col COMM for a110
column savings format 999,990
set line 140
select 'ALTER DATABASE DATAFILE '''||a.file_name||''' RESIZE '||ceil (b.HWM)||'M ;' COMM, (a.bytes/1024/1024 - ceil (b.HWM)) savings from dba_data_files a, nav_hwm_tmp b where a.autoextensible='YES' and a.file_id=b.file_id order by savings;

6.  Review and run commands generated in the spool file space_reclaim.txt.

7.  Calculate database size after exercise : 

SELECT ( (SELECT SUM (BYTES) FROM V$DATAFILE) + (SELECT SUM (BYTES) FROM V$TEMPFILE) + (SELECT SUM(8192*BLOCK_SIZE) FROM V$CONTROLFILE) + (SELECT SUM (MEMBERS*BYTES) FROM V$LOG))/1024/1024/1024 AS DATABASE_SIZE FROM DUAL; 

Do you want to do it for a single file?

1. Get HWM : select ceil (max(BLOCK_ID + BLOCKS)* &DB_BLOCK_SIZE / 1024 / 1024 ) hwm from dba_extents where file_id=&file_id ;

2.  select file_name, bytes/1024/1024 currsize, maxbytes/1024/1024 maxsize, HWM, (bytes/1024/1024) - HWM savings from dba_data_files where file_id=&file_id;