Shrink Table In Oracle

Buğra PARLAYAN

2 years ago

In today’s article, I will be explaining how to shrink the tables in the Oracle database.

One of the issues that we, as database administrators, spend the most effort on our database is the size problems that occur in tables that are constantly inserted, updated or deleted.

Due to the Oracle database architecture, the table grows continuously in the data added in the DML operations that occur in a table, but if there is deleted data, the table does not shrink.

Oracle uses the feature called High Water Mark to calculate the cost value in SQL queries in tables or to understand how much data it will read when a full table scan query comes to the existing table.

When a table is created for the first time, HWM automatically assigns an initial value and this value increases with each insert operation.

As seen in the example below, as the table continues to receive inserts, the pointer will automatically rise and changes will occur in the table accordingly.

In the delete operation, while this pointer point should be dropped as logic, it does not decrease due to the Oracle architecture.

Keeping this pointer in the same place will primarily cause unnecessary I/O on the system and will adversely affect the query performance and occupy an unnecessary space on the disk at the same time.

In the tables to be shrinked, there are some important situations that we need to know in order to bring the scattered blocks together and thus reduce the table sizes.

1. A table with the Compress property cannot be shrinked.

The table should be put into nocompress state first, and after the shrink process is done, the compress feature should be activated again.

If you shrink a table with compress property, you will get ORA-10635: Invalid segment or tablespace type error.

2. The row movement feature must be enabled in the table.

3. When a table is shrinking, it is locked, so a wait occurs when the table is related.

We can start the process for shrink.

SQL> alter table TBL_LOKUM enable row movement
Table altered

1 2	SQL> alter table TBL_LOKUM enable row movement Table altered

We have activated the row movement feature in the table.

Now let’s find and list the fragmented tables with a simple query to make our work easier.

SELECT DISTINCT owner ownr,
                ‘alter table ‘
                                || owner
                                ||’.’
                                || segment_name
                                || ‘ shrink SPACE CASCADE;’ seg_name, segment_type seg_type, tablespace_name tbs_name, file_id dbf_number FROM dba_extents WHERE (
  (
    block_id + 1
  )
  *
  (
         SELECT value
         FROM   v$parameter
         WHERE  upper (name) = ‘db_block_size’) + bytes
)
> (10000 * 1024 * 1024)
AND
segment_type = 'table'
AND
owner = 'ABUZER'
 
--Query output --
 
OWNR SEG_NAME SEG_TYPE TBS_NAME DBF_NUMBER
Alter table ABUZER.TBL_LOKUM shrink space cascade;

SELECT DISTINCT owner ownr,

‘alter table ‘

|| owner

||’.’

|| segment_name

|| ‘ shrink SPACE CASCADE;’ seg_name, segment_type seg_type, tablespace_name tbs_name, file_id dbf_number FROM dba_extents WHERE (

(

block_id + 1

)

(

SELECT value

FROM v$parameter

WHERE upper (name) = ‘db_block_size’) + bytes

)

> (10000 * 1024 * 1024)

AND

segment_type = 'table'

AND

owner = 'ABUZER'

--Query output --

OWNR SEG_NAME SEG_TYPE TBS_NAME DBF_NUMBER

Alter table ABUZER.TBL_LOKUM shrink space cascade;

The tables with fragments and which we will shrink are listed.

Now we can start the shrinking process.

SQL> alter table ABUZER.TBL_LOKUM shrink space cascade;
Table altered.

1 2	SQL> alter table ABUZER.TBL_LOKUM shrink space cascade; Table altered.

If Compress is turned on in our table, we need to do these steps.

SQL> alter table TBL_LOKUM enable row movement;
Table altered
 
SQL> alter table ABUZER.TBL_LOKUM nocompress;
Table altered.
 
SQL> alter table ABUZER.TBL_LOKUM shrink space cascade;
Table altered.
 
SQL> alter table ABUZER.TBL_LOKUM compress;
Table altered.

SQL> alter table TBL_LOKUM enable row movement;

Table altered

SQL> alter table ABUZER.TBL_LOKUM nocompress;

Table altered.

SQL> alter table ABUZER.TBL_LOKUM shrink space cascade;

Table altered.

SQL> alter table ABUZER.TBL_LOKUM compress;

Table altered.