Hi
We have a table in our 11g R2 database that as per the DBA_SEGMENTS is showing the following
select owner,segment_name,sum(bytes)/1024/1024/1024, sum(blocks),sum(extents) from dba_segments where segment_name='SEG' group by owner,segment_name;
OWNER SEGMENT_NAME SUM(BYTES)/1024/1024/1024 SUM(BLOCKS) SUM(EXTENTS)
-------------------------------------------------------------------------------------------------------------------------------------------------------------
SEGOWNER SEG 78.00 10224680 1766
We know there is a purge happening on this table so we are trying to determine how many blocks or how much space is FREE within the SEG table
And once we determine that, what we should do to address the coalesced space within the segment?
Should we:
1. Export / Import
2. Alter table shrink
What are oracle guidelines for tables that may have lots of free blocks / space inside it as a result of DML
Is there any criteria like based on free space % or number of free blocks in the segment we should either re-org the table OR shrink the table?
Thanks in advance for your kind help and assistance