Alter table EMP shrink space

Alter table EMP shrink space

Thanks to Shilpa for asking and making me remind this too. I am trying to write a note on high water mark and shrink space command.

Whenever we create a table, at least one extent will be surely allocated to it. Even without a single row we can check minimum size of the table from dba_segments view. And immediately after creation of the table a high water mark is set to the first block of the first extent and which is gradually shift as we insert row in the table. So high water mark (HWM) is the maximum mark of extent table has ever reached.  “This extent allocation could be different if we set MINEXTENT with create table statement.”

Now let’s understand why it is (HWM) needed to be change. So assume we have a table which has 1000000 rows, so it must have allocated large number of extent and ultimately block with it. And assume we need to delete 50000 rows. But deletion rows from the table will not change the HWM from the table and there will be blank or better say empty formatted block with the table. And later again when there is insertion in the table, these empty block will be used.

But there are few insert statement that won’t use this empty block, these are serial or parallel insert statement, using direct=y with SQL LOADER and many more. I’m not sure for the exact reason behind this but may be because these statements won’t use SGA but rather will use PGA. And also temporary extent will be allocated in the default tablespace of session holding schema instead of temporary tablespace. This is also a reason for the error “ ORA-1652: unable to extend temp segment” even when you have large free space in temporary tablespace.

So this way gradually over a time we can have large number of empty blocks in the table and also high water mark at high level.

Thus to use this empty block and to get HWM to lower value we can use SHRINK SPACE command. But this will be only used when the tablespace holding the object will use Automatic Segment Space Management (ASSM) which is default from Oracle 10gR2 version.

With earlier versions we have “alter table table_name move” command which can be used for same purpose but that cause exclusive lock on the table. Although SHRINK SPACE also cause lock but at lower level, we will see this gradually.

So to use this command initially we need to allow ORACLE to change the ROWID (physical address of the rows of the table) by using command:

“alter table emp enable row movement”

Once done from this we can use SHRINK STATEMENT:

“alter table emp shrink space” .

Oracle SRHINK the space in two steps actually.

In the first steps, it moves the rows to the free blocks from the beginning.

And in second step it changes the ROWID. For changing the ROWID, ORACLE again needs EXCLUSIVE LOCK as in earlier versions but for very short period of time.

We have 3 version of SHRINK SPACE command available:

Alter table emp shrink space (this will complete both the steps on EMP table)

Alter table emp shrink space cascade (this will complete both the steps on all dependent objects like INDEXES)

Alter table emp shrink space compact ( now this command will only shift the rows to the empty block but it won’t change the HWM of the table).

Now to check when we need to perform this, we can use DBMS_SPACE package provided from ORACLE.

Please let me know if I have missed anything to this relevant topic.

Thanks.