Database Administration (MOSC)

MOSC Banner

How to delete/release space allocated to CLOB segments for a table

edited Apr 28, 2016 5:04AM in Database Administration (MOSC) 3 commentsAnswered

Hi All,

We have table with CLOB column and related 2 LOB segments were occupying 19 GB of space out of 23 GB tablespace size. Could you help me to either delete or nullify those 2 column data.

Database Version = 11g

LOB Segments in Tablespace “TST_SOAINFRA” were occupied more than 19GB in size.

Owner                            Segment Type                  Size (in Bytes)                        Segment Count

--------------------              ------------------                    ------------------                               --------------

TST_SOAINFRA               INDEX                           3,155,230,720                               540

                                          LOBINDEX                    49,807,360                                      82

                                          LOBSEGMENT             19,014,942,720                               82

                                          TABLE                            3,515,285,504                              255

********************                                                     ------------------                           --------------

                                                                                25,735,266,304                              959

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024/1024 from dba_segments where TABLESPACE_NAME='TST_SOAINFRA' and SEGMENT_TYPE like'%LOB%';

OWNER                               SEGMENT_NAME                           SEGMENT_TYPE           TABLESPACE_NAME                BYTES/1024/1024/1024

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