Fragmentation in table

Deleted records not freeing up space

 

We have deleted records from big tables in database. But each table still holds the same blocks and extents. It means they have not released the space logically. How to use this space.

Let us see the steps.

Example: I have created a table abc from emp and inserted many records to occupy many database blocks and extents.

1- Create a table ABC

SQL> create table abc as select * from emp;
Table created.

Inserted many records into table ABC

SQL> insert into abc select * from abc;
14 rows created.
SQL>/
28 rows created.
SQL> /
.....
SQL> /
1835008 rows deleted.
SQL> commit;
commit complete.

1-  Check how many Blocks are being used by table ABC

SQL> select blocks from user_segments where segment_name='ABC';
BLOCKS
----------
11264

 

2-  Check how many extents are being used by table ABC

SQL> SELECT count(*) from user_extents where segment_name='ABC';
COUNT(*)
----------
82

 

3-  Deleted records from table ABC.

SQL> delete from abc;
1835008 rows deleted.
commited
SQL> commit;
Commit complete.

4-  Verify how many blocks occupied by ABC table, result is same.

SQL> select blocks from user_segments where segment_name='ABC';
BLOCKS
----------
11264

5-  Verify how many extents occupied by ABC table, result is same.

 

SQL> SELECT count(*) from user_extents where segment_name='ABC';
COUNT(*)
----------
82

6-  To make a space from the deleted records extents and blocks steps are

 

7-  Enable the table row movement

SQL> alter table abc enable row movement;
Table altered.

8-  Shrink space for table abc

SQL> alter table abc shrink space;
Table altered.

9-  Verify again how many extents are occupied by ABC table, result are changed to correct and achievable.

 

SQL> SELECT count(*) from user_extents where segment_name='ABC';
COUNT(*)
----------
1

13 – And verify how many blocks are still occupied by table ABC, result are correct.

 

SQL> select blocks from user_segments where segment_name='ABC';
BLOCKS
----------
8
SQL>

 

It means we have freed up space (extents and blocks) of the deleted records.

 

Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Performance and Tuning, Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s