Home » RDBMS Server » Server Administration » Delete LOB data from LOB segment (Oracle 11.2.0.3, HP-UX 11.31)
Delete LOB data from LOB segment [message #655901] Thu, 15 September 2016 14:53 Go to next message
jesuisantony
Messages: 155
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.
Re: Delete LOB data from LOB segment [message #655904 is a reply to message #655901] Thu, 15 September 2016 20:30 Go to previous messageGo to next message
BlackSwan
Messages: 25797
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?
Re: Delete LOB data from LOB segment [message #655999 is a reply to message #655901] Mon, 19 September 2016 12:47 Go to previous messageGo to next message
jesuisantony
Messages: 155
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

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

We used
DBMS_REDEFINITION
to do the online re-org.

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

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;
Re: Delete LOB data from LOB segment [message #656000 is a reply to message #655904] Mon, 19 September 2016 12:49 Go to previous message
jesuisantony
Messages: 155
Registered: July 2006
Location: Chennai
Senior Member
Query used to find the segment size

select bytes from dba_segments where segment_name ='<lob segment name>' and owner ='<table owner>';
Previous Topic: How to apply patch in Oracle VISION Instance
Next Topic: Cloning in oracle
Goto Forum:
  


Current Time: Sat Jan 20 06:00:36 CST 2018

Total time taken to generate the page: 0.00839 seconds