I have a Oracle 12c tablespace with the size of 90GB. Almost 40GB are free.

TB1 54 %    90880   41383   49497   46  98304   50 %    ONLINE  PERMANENT

The tablespace has 3 datafiles.

E:\ORACLE\ORADATA\xx\TB1.DBF    33554416    True    100 
F:\ORACLE\ORADATA\xx\TB1_2.DBF  33554416    True    100 
G:\ORACLE\ORADATA\xx\TB1_3.DBF  25344000    True    100

How can I shrink the tablespace to reclaim the free 40GB (datafiles on disk)? I can not have a downtime.

  • 1
    What's about alter database datafile resize? – JSapkota Dec 7 '17 at 14:12
  • 1
    ORA-03297: file contains used data beyond requested RESIZE value - I can tried making them smaller. – r0tt Dec 7 '17 at 14:21
  • Thetablespace has 600 tables + Indexes. I guess I have to shrink/move all of them first. – r0tt Dec 7 '17 at 14:50
  • 2
    You can't shrink a file belows the high water mark, which will require moving/reorging the tables. And short of moving them all to a new TS, it would likely be a long, looping process of reorging the same tables multiple times. And for what, a lousy 40gb? Are you really that space critical, or are you suffering from Compulsive Tuning Disorder? Uwe Hesse has a good article on this at uhesse.com/2010/08/23/… – EdStevens Dec 8 '17 at 1:39

I agree with EdStevens' comment, but here are some other ideas:

The AskTom website has a script that will list exactly how much you can shrink the existing data files.

If that doesn't yield much savings, you can find the biggest indexes on that tablespace and rebuild them; that is a temporary solution and the indexes would eventually get big again, but if budget doesn't allow more disk space now, try this query:

select segment_name, sum(bytes)/1024/1024 as mb 
from dba_segments 
where tablespace_name='TB1' and segment_type like 'INDEX%'
group by segment_name
order by mb;

Then if you find an index named ind_big_stuff is huge, then

alter index ind_big_stuff rebuild online;

Also, if your application is a commercial application, there may be several tables with zero rows. Or if you have partitioned tables, you may have some partitions with zero rows. You can drop space allocated to them with

exec dbms_space_admin.drop_empty_segments(schema_name=>'HR');

You can use the segment creation deferred clause on an alter table statement for things like partitioned tables, or tables you know will not have any rows.

  • The comment by @r0tt below about the dbms_redefinition package can be a big help; expecially for Oracle 12.2 or later, because it has a simplified procedure redef_table that automates the whole task; it is useful for applying compression to tables and indexes if you have the compression option licensed. Another plus: no downtime. – Mark Stewart Feb 15 '19 at 15:36

I think there are five methods to deal with this oracle maintenance issue:

  • Online reorg - Oracle online table reorganization package, (dbms_redefinition) is used to reorganize tables while they are accepting updates. It preserves the index and constraint definitions. (Only Enterprise Editon). No downtime.
  • Alter table shrink space - Using the alter table shrink space compact command will re-pack the rows, move down the HWM, and releases unused extents. The shrink only compacts the data. It doesn't guarantee the blocks will be placed at the front of the datafile, so it's possible to have blocks at the end of the datafile, which means the datafile can't be resized smaller. It preserves the index and constraint definitions. No downtime.
  • Data pump (expdp, impdp) – Offline method to transfer data and reorganize tables. It preserves the index and constraint definitions. Downtime required.
  • Alter table move - The alter table move command moves rows down into un-used space and adjusts the HWM but does not adjust the segments extents, and the table size remains the same. The alter table move syntax also preserves the index and constraint definitions. Datafile resize required. Downtime required.
  • Create table As Select (CTAS) - This copies the table rows into a clean area, lowering the high-water-mark, packing the rows densely (as dictated by PCTFREE) and releasing free space. Datafile resize required. Downtime required.
  • And the CTAS would require extra space temporarily until after the command completes, and you drop the old table and rename the new table. Plus that would not coy all the constraints, indexes, etc. – Mark Stewart Dec 11 '17 at 19:08

I searched for an answer to this and finally found it:

  • (optionally) remove unused (dropped) tables from tablespace: PURGE TABLESPACE table_space_name;
  • clean the tablespace: ALTER TABLESPACE table_space_name COALESCE;
  • finally, resize the datafiles: ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/file1.dbf' RESIZE 100M;

PS: Not sure about downtime, someone please check that.

PPS: For TEMP tablespace(s) use:

  • ALTER TABLESPACE TEMP SHRINK SPACE ; (optionally add KEEP 512M or similar)
  • resize the data/tempfiles: ALTER DATABASE TEMPFILE '/u02/oracle/rbdb1/temp01.dbf' RESIZE 512M;
  • 2
    No downtime required for those commands; you can also purge dba_recyclebin; to get rid of tables that have been dropped and and are available for flashback. – Mark Stewart Jun 24 '18 at 3:47
  • 1
    Ask Tom on COALESCE: asktom.oracle.com/pls/apex/… – Vadzim Feb 15 '19 at 14:39

Run the below scrip, It will release unused space from the datafiles of the respective tablespace.

column value new_val blksize
select value from v$parameter where name = 'db_block_size'

set pages 0
set lines 300
column cmd format a300 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a, 
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+) 
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

Check details at https://orahow.com/reclaiming-unused-space-in-datafiles/

  • Could you please write more, how does this script work? – peterh Aug 18 '19 at 16:48

Very effective is also moving everything to another new tablespace online:

## Lobs
select 'ALTER TABLE '||s.owner||'.'||l.table_name||' MOVE LOB('||l.column_name||') STORE AS (TABLESPACE USERNAME) online;' 
  from dba_segments s, dba_lobs l
 where s.segment_name = l.segment_name
   and s.tablespace_name = 'USERNAME'
   and segment_type='LOBSEGMENT'
   and partition_name is null;

## Tables move
select 'ALTER TABLE '||owner||'.'||table_name||' move tablespace '||'USERNAME online;' from dba_tables where tablespace_name='USERS'and Owner='USERNAME';

## Move / rebuild indexes 
select 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE '||'USERNAME_INDEX parallel 8 online;' 
from dba_indexes where tablespace_name='USERS';

## Lob Index move 
select 'alter table '||owner||'.'||table_name||' move lob ('||column_name||') store as '||SEGMENT_NAME||' (tablespace USERNAME_INDEX);'from dba_lobs where OWNER='USERNAME' and tablespace_name='USERS';

Check Lob indexes
select index_name,index_type, table_name,table_type, tablespace_name from dba_indexes where tablespace_name='USERNAME' order by 3;
select index_name,index_type, table_name,table_type, tablespace_name from dba_indexes where owner='USERNAME' and index_type='LOB' and tablespace_name='USERS' order by 3;

https://orahow.com/reclaim-unused-space-in-oracle/ Has 2 approaches for identifying and reclaiming space.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.