Oracle shrinking / reclaiming free tablespace space

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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;

Answer 4

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;

Answer 5

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/

Answer 6

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