space reclaim with xmltype
I am having trouble in getting back the space for the table PAYLOAD
CREATE TABLE XXTIBCOFRAMEWORK.PAYLOAD
(
PAYLOAD_ID NUMBER NOT NULL,
LOG_ID NUMBER NULL,
DATA XMLTYPE NULL,
CONSTRAINT PK_PAYLOAD
PRIMARY KEY (PAYLOAD_ID)
USING INDEX TABLESPACE XXTIBCOFRAMEWORK_TS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
ENABLE
VALIDATE
)
The above table is heavily used around 3 million record is being inserted every day .
We have a purge script which deletes 21 days old data but not seeing the space back .
select owner, segment_name, segment_type tablespace_name, bytes/1024/1024/1024 gb from dba_segments
order by gb desc
OWNER | SEGMENT_NAME |