OraFAQ Forum: Server Administration » Delete LOB data from LOB segment

Home » RDBMS Server » Server Administration » Delete LOB data from LOB segment (Oracle 11.2.0.3, HP-UX 11.31)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Delete LOB data from LOB segment [message #655901]

Thu, 15 September 2016 14:53

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

One of the LOB segments is occupying more space in the database. The application team has mentioned that they have deleted some of the data through the application. However after the table re-org and then the table shrink cascade, the size of the segment still remains the same.

Through the estimation of expdp we found that the table size remains the same. How can we delete the data of LOB segments from the DB?

The application team has requested to delete the data from the database.

Report message to a moderator

Re: Delete LOB data from LOB segment [message #655904 is a reply to message #655901]

Thu, 15 September 2016 20:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

>However after the table re-org and then the table shrink cascade, the size of the segment still remains the same.

We don't know what you have.
We don't know what you do.
We don't know what you see.

Application team is responsible for table & it content.
DELETE marks space in table as available for reuse.

>How can we delete the data of LOB segments from the DB?
Post proof that the deleted rows still exist?
Was COMMIT issued after DELETE?
Has RECYCLE BIN been emptied?

Report message to a moderator

Re: Delete LOB data from LOB segment [message #655999 is a reply to message #655901]

Mon, 19 September 2016 12:47

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

The LOB segment size is 55 GB.

Query used to find the segment size

The LOB data size is 2 MB.

Query used to find the LOB data size

Code: [Select all] [Show/ hide]

select sum(dbms_lob.getlength (<lob column name>)) from <table_name>;

We used

Code: [Select all] [Show/ hide]

DBMS_REDEFINITION

to do the online re-org.

We did a shrink after the re-org using the below statement.

Code: [Select all] [Show/ hide]

alter table <table name> modify lob(<lob column name>) (shrink space [cascade]);

There is nothing seen in DBA_RECYCLEBIN. As well, since the deletion was performed through application functionality, it issues an AUTO COMMIT;

Report message to a moderator

Re: Delete LOB data from LOB segment [message #656000 is a reply to message #655904]

Mon, 19 September 2016 12:49

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Query used to find the segment size

Code: [Select all] [Show/ hide]

select bytes from dba_segments where segment_name ='<lob segment name>' and owner ='<table owner>';

Report message to a moderator

Previous Topic:	How to apply patch in Oracle VISION Instance
Next Topic:	Cloning in oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 05 10:34:12 CDT 2025