Oracle 23ai New Feature Shrink Tablespace

In Oracle Database versions prior to 23ai the ORA-03297 error: “file contains used data beyond requested RESIZE value” would be received because there are objects residing in the tablespace like tables and indexes as well as partitions that are blocking it and preventing it from shrinking – even though the tablespace has unused space which potentially can be reclaimed.

In Oracle Database 23ai a tablespace can be reorganized and the associated data file can be resized by moving objects within the tablespace, either online or offline.

The Oracle Database 23ai DBMS_SPACE package can be used to shrink a tablespace to reclaim any unused space.

The SHRINK_TABLESPACE procedure of the package DBMS_SPACE enables us to both resize a tablespace as well as analyze a tablespace before resizing the same.

The new DBMS_SPACE.SHRINK_TABLESPACE functionality It helps to both reduce costs as well as optimize database storage by enabling the shrinking of a tablespace to reclaim unused space in the database.

We can run the DBMS_SPACE. SHRINK_TABLESPACE in the following modes:

• TS_MODE_SHRINK (default mode)
• TS_MODE_SHRINK_FORCE (move objects online by default, but if the online move fails, it will attempt to move them offline)

• TS_MODE_ANALYZE (analyze mode)

Let us look at an example of the new 23ai Shrink Tablespace feature.

Create and populate demo table with data.

SQL> CREATE TABLE "SH"."MYSALES"
   (    "ID" NUMBER,
        "FLAG" NUMBER,
        "PRODUCT" CHAR(17),
        "CHANNEL_ID" NUMBER,
        "CUST_ID" NUMBER,
        "AMOUNT_SOLD" NUMBER,
        "ORDER_DATE" DATE,
        "SHIP_DATE" DATE
   )
TABLESPACE MYDATA;
 
Table created.


SQL> insert into sh.mysales
select
rownum as id,
rownum + 1 as flag,
'Samsung Galaxy S7' as product,
mod(rownum,5) as channel_id,
mod(rownum,1000) as cust_id ,
5000 as amount_sold,
trunc(sysdate - 10000 + mod(rownum,10000))
as order_date,
trunc(sysdate - 9999 + mod(rownum,10000))
as ship_date
from dual connect by level<=1e6
;  

1000000 rows created.

SQL> commit;

Commit complete.

Note the space usage in the MYDATA tablespace.

The tablespace at the moment is around 70% used.

SQL> select sum(a.tots/1048576) Tot_Size,
     sum(a.sumb/1048576) Tot_Free,
     sum(a.sumb)*100/sum(a.tots) Pct_Free,
     sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
     from
     (
      select 0 tots,sum(bytes) sumb,
     max(bytes) largest,count(*) chunks
     from dba_free_space a
     where tablespace_name='MYDATA'
     group by tablespace_name
     union
     select sum(bytes) tots,0,0,0 from
      dba_data_files where tablespace_name='MYDATA'
     group by tablespace_name) a
     order by 1;
 
  TOT_SIZE   TOT_FREE   PCT_FREE   MAX_FREE CHUNKS_FREE
---------- ---------- ---------- ---------- -----------
       500   360.0625    72.0125     368640           2

Drop the table to simulate a case of free space becoming available in a tablespace.

Execute DBMS_SPACE.SHRINK_TABLESPACE in analyze mode initially to identify how much space can potentially be freed up – note usage of TS_MODE_ANALYZE clause.

SQL> drop table sh.mysales purge;

Table dropped.

SQL> set serverout on

SQL> execute dbms_space.SHRINK_TABLESPACE('MYDATA', SHRINK_MODE=>DBMS_SPACE.TS_MODE_ANALYZE);

-------------------ANALYZE RESULT-------------------
Total Movable Objects: 0
Total Movable Size(GB): 0
Original Datafile Size(GB): .49
Suggested Target Size(GB): .07
Process Time: +00 00:00:00.005414

PL/SQL procedure successfully completed.

Execute DBMS_SPACE.SHRINK_TABLESPACE using the clause TS_MODE_SHRINK to actually free up the unused space in the tablespace.

Note that the original datafile size was initially 500 MB (around .49 GB) – and the new size after the tablespace shrink has been performed is now 100 MB (.1 GB).

SQL>  execute dbms_space.SHRINK_TABLESPACE('MYDATA',SHRINK_MODE=>DBMS_SPACE.TS_MODE_SHRINK,TARGET_SIZE=>104857600);

-------------------SHRINK RESULT-------------------
Total Moved Objects: 0
Total Moved Size(GB): 0
Original Datafile Size(GB): .49
New Datafile Size(GB): .1
Process Time: +00 00:00:00.156137

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

Query DBA_DATA_FILES view to verify the size of the data file in the MYDATA tablespace is now resized to a new value of 100 MB (original value was 500 MB) .

SQL> select sum(bytes)/1048576 from dba_data_files where tablespace_name='MYDATA';

SUM(BYTES)/1048576
------------------
               100