Saturday , March 2 2024

Shrink Table In Oracle

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.

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.

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

Now we can start the shrinking process.

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

Loading

About Buğra PARLAYAN

Burgra Parlayan is an experienced Database and Weblogic Administrator. After completing his technical / relevant training he has got involved with a serious amount of projects. He successfully managed database upgrade, database migration, database performance tuning projects for various public institutions.Currently he has been employed by one of the leading financial institutions called Turkiye Hayat & Emeklilik as responsible administrator for Oracle Database and Oracle Middleware. He has been sharing his experience and knowledge by face to face training, personal blog and various social networking accounts to support the Oracle ecosystem continuously since 2010.

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories