Database Administration (MOSC)

MOSC Banner

Reclaim space with XMLTYPE

edited Nov 3, 2017 4:54PM in Database Administration (MOSC) 2 commentsAnswered

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

   

OWNERSEGMENT_NAME
Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center