-- Resetting a table or Index high water mark --

We recently got confronted in our Data Warehouse database with an issue where one of the database files was a "bigfile" tablespace and it was located on a drive that can't be expanded. Yes, I know that is the rule No.1 about using "bigfile" tablespaces, if you are going to have storage limitations do not use tablespaces. In my defense I inherited the environment, someone else designed it :) . 

Our application administrator told us that he deleted a humongous amount of space from the biggest table, however you will not see that space back in the tablespace as it is still being help as empty blocks on the table. Out grid control critical threshold of 97% was triggered.   

Although the application administrator told us not to worry about it, because the table will reuse the empty space and won't try to consume more space in the tablespace, I didn't feel well just knowing that the next time that we get an issue I won't get an alert, I will get a call from the application administrator telling me that the system in halt because of the space. Since this is a 1.7TB database, it will take long time for me to move that tablespace to somewhere else with more space.

So what I decided to do was to reset the high water mark of those tables and indexes so it returns the space to the tablespace and I get my Grid control alert clear again.

This is the way you accomplish that.

SQL> alter table enable row movement;
SQL > alter table shrink space;

If you want to reset the high water mark for all dependent objects yo do this:

SQL > alter table shrink space cascade;

If you just want to compact the table without resetting the high ware mark you do

SQL > alter table shrink space compact;

Please note that the operations above can be done online with minimum impact (row level locking for a brief moment). 

For indexed you do


SQL > alter index shrink space;

Enjoy.