Hello,
We use Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production. There is a BLOB database whose size is 109GB which contained data from Blob columns in 2 different tables. I made these 2 tables empty. But even after that the below query shows the same column names from the tables which are now empty. I made sure that the tables are empty by a separate query using SQL*Plus. I expected that the below query should give no results. Now my question is why are the segments till present in BLOB tablespace even if they are deleted? How can i reclaim the space from this BLOB database?
select
l.owner,
l.table_name,
l.column_name,
l.segment_name,
l.index_name
from
dba_lobs l,
dba_segments s
where
l.owner in ('Schema1', 'Schema2')
and s.tablespace_name = 'TS_TBZ_P_BLOB'
and l.segment_name = s.segment_name
order by l.owner;
Could anyone help me in this?
Thanks in advance.
AGana