LOBSEGMENT defragmentation

Method 1: Shrink space, which is slow
Method 2: "Move" LobSegment

[oracle@vmxdb01 ~]$ sqlplus donghua/ora123

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 8 15:02:14 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set echo on
SQL> @lob_test.sql
SQL> col segment_name for a30
SQL> col index_name for a30
SQL> set lin 90
SQL> col mbytes for 99999999999
SQL> set timing on
SQL>
SQL> drop table tbl_lob_test purge;

Table dropped.

Elapsed: 00:00:01.70
SQL>
SQL> CREATE TABLE tbl_lob_test
  2  (
  3     col1    CHAR (2000),
  4     col2    CHAR (2000),
  5     col3    CHAR (2000),
  6     message   CLOB
  7  )
  8  LOB (message) STORE AS (TABLESPACE users DISABLE STORAGE IN ROW);

Table created.

Elapsed: 00:00:00.21
SQL>
SQL> insert into tbl_lob_test
  2  select owner, object_name,object_name, object_name from dba_objects;

72458 rows created.

Elapsed: 00:00:56.44
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL> select SEGMENT_NAME,INDEX_NAME     from dba_lobs
  2  where table_name='TBL_LOB_TEST' and owner='DONGHUA';

SEGMENT_NAME                   INDEX_NAME
------------------------------ ------------------------------
SYS_LOB0000074592C00004$$      SYS_IL0000074592C00004$$

Elapsed: 00:00:00.14
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$            4194304
TBL_LOB_TEST                      603979776
SYS_LOB0000074592C00004$$         603979776

Elapsed: 00:00:02.15
SQL>
SQL> delete from tbl_lob_test;

72458 rows deleted.

Elapsed: 00:01:13.64
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL> select segment_name,sum(bytes)     mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$           11534336
TBL_LOB_TEST                      603979776
SYS_LOB0000074592C00004$$         603979776

Elapsed: 00:00:02.62
SQL>
SQL> insert into tbl_lob_test
  2  select owner, object_name,object_name, object_name from dba_objects;

72458 rows created.

Elapsed: 00:01:26.81
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL> select segment_name,sum(bytes)     mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$           14680064
TBL_LOB_TEST                      603979776
SYS_LOB0000074592C00004$$        1207959552

Elapsed: 00:00:01.68
SQL>
SQL> alter table tbl_lob_test modify lob (message) (shrink space);

Table altered.

Elapsed: 00:02:29.18
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$           14680064
TBL_LOB_TEST                      603979776
SYS_LOB0000074592C00004$$         603979776

Elapsed: 00:00:01.09
SQL>
SQL> alter table tbl_lob_test
  2  move lob (message)  STORE AS tbl_lob_test_lob_msg
  3          (TABLESPACE users
  4          index tbl_lob_test_lob_msg_idx (tablespace users) );

Table altered.

Elapsed: 00:01:06.87
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$            4194304
TBL_LOB_TEST                      603979776
TBL_LOB_TEST_LOB_MSG              603979776

Elapsed: 00:00:00.05
SQL>
SQL> truncate table tbl_lob_test;

Table truncated.

Elapsed: 00:00:02.10
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$              65536
TBL_LOB_TEST                          65536
TBL_LOB_TEST_LOB_MSG                  65536

Elapsed: 00:00:00.06
SQL>
SQL> drop table tbl_lob_test purge;

Table dropped.

Elapsed: 00:00:00.49
SQL>
SQL> CREATE TABLE tbl_lob_test
  2  (
  3     col1      CHAR (2000),
  4     col2      CHAR (2000),
  5     col3      CHAR (2000),
  6     MESSAGE   CLOB
  7  )
  8  LOB (
  9     MESSAGE)
 10     STORE AS
 11        tbl_lob_test_lob_msg (
 12     TABLESPACE users
 13     DISABLE STORAGE IN ROW
 14     INDEX tbl_lob_test_lob_msg_idx ( TABLESPACE users ));

Table created.

Elapsed: 00:00:00.18
SQL>
SQL> insert into tbl_lob_test values (1,2,3,4);

1 row created.

Elapsed: 00:00:00.06
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
TBL_LOB_TEST_LOB_MSG_IDX              65536
TBL_LOB_TEST                          65536
TBL_LOB_TEST_LOB_MSG                  65536

Elapsed: 00:00:00.05
SQL>
SQL> select SEGMENT_NAME,INDEX_NAME     from dba_lobs
  2  where table_name='TBL_LOB_TEST' and owner='DONGHUA';

SEGMENT_NAME                   INDEX_NAME
------------------------------ ------------------------------
TBL_LOB_TEST_LOB_MSG           TBL_LOB_TEST_LOB_MSG_IDX

Elapsed: 00:00:00.04