Oracle Truncate Table - Storage Clause Significance

This week I performed a truncate on a table in Oracle database to recover some space in my tablespace. Even after truncate the space was not released back to the tablespace. There I realized the storage clause of the table as well as the truncate command is important.

The storage clause in the truncate command is required to be specified while performing a truncate command. The storage clause determine as to what will happened to the space released by the rows that got removed. When a truncate is issued on a table, Oracle Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter (be aware that the de-allocation depends on the min extents clause and be wary when the table is large and is imported)

The options are

Drop Storage – This means that all space used by the object would be released except for the space allocated by the minextent parameter. This space can be used by any other object or by the same object from which the data is truncated. However, if the minextent (along with the initial value) is large enough, this space is NOT released even after the truncate. For e.g. See the storage parameter for a table that got imported into my database

STORAGE (
INITIAL 2432M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)

If the table with the above definition is truncated, 2432M is not released back to the tablespace for other objects to use. However, if you perform alter table move tablespace X or Drop table, the space would be released back to the tablespace.

Reuse Storage – This means that the space is not released back to the pool instead the space is reserved for the same table. Hence if the table is occupying 50M, a truncate would not release the 50M back to the tablespace instead the space would be reserved for the same table. This option needs to be used in case you are sure that you are going to reload the similar data again into the same table.

Oracle ROWID - its Meaning

ROWID pseudo-column provides the address of a row of a table. Hence it’s the fastest way a row can be accessed in Oracle. When you look at the plan of execution for queries that uses indexes, it shows “Table access by ROWID”. This means that the scan of the indexes has decided to get the row from a set of ROWID’s and the access of the row data will be through the ROWID value for the row.

The ROWID value contains the following information

The data object number of the object –For e.g. if the row belongs to table “Employee”, the data object number can be obtained from the query “select object_id from dba_objects where object_name=’EMPLOYEE’”

The data block number in the data file that holds the row – The block number is relative to the tablespace which holds the block.

Position of the row in the data block – This is the position of the row inside the block. If the row is the first one to get inserted in the block, the position would be zero.

The data file in the database that holds the row – This would point to the data file that holds the block. The number is directly mapped to the File# in the V$DATAFILE table.

The ROWID value, when selected directly using the queries will not display in an understandable format. We need to use dbms_rowid package to retrieve the values.

select rowid,dbms_rowid.ROWID_ROW_NUMBER(rowid) ROWPOSITION,

dbms_rowid.ROWID_OBJECT(rowid) OBJECTNUMBER,

dbms_rowid.ROWID_RELATIVE_FNO(rowid) DATAFILENUM,

dbms_rowid.ROWID_BLOCK_NUMBER(rowid) BLOCKNUMBER,

name from EMPLOYEE

ROWID	ROWPOSITION	OBJECTNUMBER	DATAFILENUM	BLOCKNUMBER	NAME
AAAcv2AAOAAANHIAAA	0	117750	14	53704	Myname

The above result with respect to the row that has the name as “Myname” is

Row is placed in the data file identified with File# as 14

Row is part of the object with the id as 117750

Row holds the first position in the block

Row is placed in the block 53704 in the tablespace.