Amazon

Tuesday, April 4, 2017

Oracle : Release space occupied by Blob/Clob objects

Objective: Release the space occupied by table having columns capable to store BLOB/CLOB data. The space is not released even after deleting the whole row from the table.

There are defined steps to execute after doing delete of Table rows. Steps are below :

Assumptions:
Table Name : BLOB_OBJECT_TAB
Column Name : BLOB_OBJECT_TAB_COL

set serverout on
spool shrink_table.log

ALTER TABLE BLOB_OBJECT_TAB enable row movement;

ALTER TABLE BLOB_OBJECT_TAB SHRINK SPACE compact;

ALTER TABLE BLOB_OBJECT_TAB SHRINK SPACE;

ALTER TABLE BLOB_OBJECT_TAB MODIFY LOB(BLOB_OBJECT_TAB_COL) (SHRINK SPACE CASCADE);

alter table BLOB_OBJECT_TAB disable row movement;

ALTER INDEX PK_ID REBUILD;


ALTER TABLE BLOB_OBJECT_TAB disable row movement;



spool off;

No comments:

Post a Comment

Amazon Best Sellors

Weblogic - Server Creation - Issues

Datasource creation using python script.  Python Script was failing after creating Datasource. From UI Admin Console, if I try to activat...