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

TOGAF 9.2 - STUDY [ The Open Group Architecture Framework ] - Chap 01 - Introduction

100 Feet View of TOGAF  What is Enterprise? Collection of Organization that has common set of Goals. Enterprise has People - organized by co...