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.
