Squeeze the size of files in a tablespace

This tip decreases the size of files of a tablespace to the bare minimum. It's useful when you have many files in a tablespace and you've moved some tables or indexes into another tablespace, and then you want to decrease the size of the first tablespace.

One tablespace in my database had 32 files and each of them was 2 GB. It occupied 64 GB of disk space and the tablespace didn't have a place for any other tables. Then I moved some tables to another tablespace because the tablespace was fragmented and the pct increase was not equal to zero. After moving the tables I wanted to squeeze the size of the tablespace but I didn't want to do it with each file manually. This script helped me. Now my tablespace occupies 56 GB of disk space. I've tested this script on 8.1.7 but I'm sure it will work on other versions because it's very simple. Use Oracle's EXECUTE IMMEDIATE command to execute the dynamic SQL.

 set serveroutput on SIZE 1000000
   declare
   CURSOR CF IS 
   select file_name, tablespace_name, BYTES/1024/1024 SMB from dba_data_files 
       where tablespace_name IN
('COLVIR_LARGE_ROLLBACK','COLVIR_ROLLBACK1','COLVIR_ROLLBACK2','COLVIR_ROLLBACK3');
   N_SIZE_FILE number(30)    := 0;
   S_TMP       varchar2(512) := '';
   begin
     <<C_T>>
     for C_T in CF loop
       N_SIZE_FILE:=C_T.SMB;
       dbms_output.put_line(C_T.file_name||' '||N_SIZE_FILE);
       <<I_S>>
       FOR I_S IN REVERSE 1..N_SIZE_FILE LOOP
          S_TMP:='ALTER DATABASE DATAFILE '''||C_T.file_name||''' RESIZE'||I_S||'M';
          begin
                EXECUTE IMMEDIATE S_TMP;
                EXCEPTION WHEN OTHERS THEN
                dbms_output.put_line(I_S);
  GOTO out_from_loop;
          end;
       END LOOP I_S;
       <<out_from_loop>>
       null;
     end loop C_T;
   end;

Reader Feedback

James N. writes: In his procedure, Mr. Solodilov starts with the current size of the datafile and in a loop keeps resizing it, decreasing by 1MB until an error condition is raised. By changing the cursor to join dba_data_files with dba_extents, one can find the current highwater mark of extents actually allocated in the datafile, determine the next 1MB boundary, and issue a single resize command to that size.

Here is a sample of the simplified code with the cursor modified to determine the highwater mark. Note that the 8192 value used is the blocksize for the database and would need to be modified for databases using a different blocksize.

SQL> column tablespace_name format a10
SQL> column file_name format a30
SQL> column mb  format 9999
SQL> create tablespace jim_1 datafile '/u07/oradata/tdwh01/jim1.dbf' size 100m reuse;

Tablespace created.

SQL> create tablespace jim_2 datafile '/u07/oradata/tdwh01/jim2.dbf' size 100m reuse;

Tablespace created.

SQL> create table jim1 (col1 number) tablespace jim_1
  2     storage (initial 10m next 1m);

Table created.

SQL> create table jim2 (col1 number) tablespace jim_2
  2     storage (initial 20m next 1m);

Table created.

SQL> select tablespace_name, file_name, bytes/(1024*1024) mb
  2    from dba_data_files where tablespace_name like 'JIM%';

TABLESPACE FILE_NAME                         MB
---------- ------------------------------ -----
JIM_1      /u07/oradata/tdwh01/jim1.dbf     100
JIM_2      /u07/oradata/tdwh01/jim2.dbf     100

SQL> set serveroutput on SIZE 1000000
SQL>    declare
  2     CURSOR CF IS
  3        SELECT ddf.tablespace_name, ddf.file_name,
  4               ceil(8192*MAX(de.block_id+de.blocks)/(1024*1024)) SMB /* <-- change '8192' to your actual blocksize */
  5          FROM dba_data_files ddf, dba_extents de
  6         WHERE ddf.file_id = de.file_id
  7           AND ddf.tablespace_name
  8               IN ('JIM_1', 'JIM_2')
  9  -- IN ('COLVIR_LARGE_ROLLBACK','COLVIR_ROLLBACK1','COLVIR_ROLLBACK2','COLVIR_ROLLBACK3')
 10         GROUP BY ddf.tablespace_name, ddf.file_name;
 11
 12     N_SIZE_FILE number(30)    := 0;
 13     S_TMP       varchar2(512) := '';
 14
 15     begin
 16       <>
 17       for C_T in CF loop
 18          N_SIZE_FILE:=C_T.SMB;
 19          dbms_output.put_line(C_T.file_name||' '||N_SIZE_FILE);
 20          S_TMP:='ALTER DATABASE DATAFILE '''||C_T.file_name||''' RESIZE '||N_SIZE_FILE||'M';
 21          EXECUTE IMMEDIATE S_TMP;
 22       end loop C_T;
 23     end;
 24  /
/u07/oradata/tdwh01/jim1.dbf 11
/u07/oradata/tdwh01/jim2.dbf 21

PL/SQL procedure successfully completed.

SQL> select tablespace_name, file_name, bytes/(1024*1024) mb
  2    from dba_data_files where tablespace_name like 'JIM%';

TABLESPACE FILE_NAME                         MB
---------- ------------------------------ -----
JIM_1      /u07/oradata/tdwh01/jim1.dbf      11
JIM_2      /u07/oradata/tdwh01/jim2.dbf      21

SQL> spool off

Also note that although the tables created were only 10MB and 20MB, you can only resize down to 11MB and 21MB respectively because of the header and trailer blocks that Oracle maintains in each datafile.

Ofer H. writes: Both scripts will work only if the tablespace is coalesced completely. Actually there is no support from Oracle to defragment the tablespace, other than to recreate the tablespace with all its contents. Usually, after using the database for a long time, the free space of a tablespace is spread all over it, so resizing the tablespace to its used size is not possible.

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.