回收oracle Lob对象空间利用

SQL> create table t(time date,text clob)
  2  /

表已创建。

SQL> begin
  2    for i in 1 .. 10000 loop
  3      insert into t values(sysdate,rpad('a', 4000, 'a'));
  4      commit;
  5    end loop;
  6  end;
  7  /

PL/SQL 过程已成功完成。

SQL> select count(*) from t;

  COUNT(*)
----------
     10000
     
SQL> select table_name, column_name, segment_name, a.bytes
  2    from dba_segments a
  3    join dba_lobs b
  4   using (owner, segment_name)
  5   where b.table_name = 'T';

TABLE_NAME                     COLUMN_NAME          SEGMENT_NAME                        BYTES
------------------------------ -------------------- ------------------------------ ----------
T                              TEXT                 SYS_LOB0000102100C00002$$        83886080

SQL> select sum(dbms_lob.getlength(text)) as bytes from t;

     BYTES
----------
  40000000

SQL> delete from t;

已删除10000行。

SQL> commit;

提交完成。

SQL> select table_name, column_name, segment_name, a.bytes
  2    from dba_segments a
  3    join dba_lobs b
  4   using (owner, segment_name)
  5   where b.table_name = 'T';

TABLE_NAME                     COLUMN_NAME          SEGMENT_NAME                        BYTES
------------------------------ -------------------- ------------------------------ ----------
T                              TEXT                 SYS_LOB0000102100C00002$$        83886080

SQL> select sum(dbms_lob.getlength(text)) as bytes from t;

     BYTES
----------

可以看见删掉以后，空间没有释放，再次想表中插入10000跳记录。

SQL> begin
  2    for i in 1 .. 10000 loop
  3      insert into t values (sysdate, rpad('a', 4000, 'a'));
  4      commit;
  5    end loop;
  6  end;
  7  /

PL/SQL 过程已成功完成。

SQL> select table_name, column_name, segment_name, a.bytes
  2    from dba_segments a
  3    join dba_lobs b
  4   using (owner, segment_name)
  5   where b.table_name = 'T';

TABLE_NAME                     COLUMN_NAME          SEGMENT_NAME                        BYTES
------------------------------ -------------------- ------------------------------ ----------
T                              TEXT                 SYS_LOB0000102100C00002$$       167772160

SQL> select sum(dbms_lob.getlength(text)) as bytes from t;

     BYTES
----------
  40000000

SQL> delete from t;

已删除10000行。

SQL> commit;

提交完成。

SQL> select table_name, column_name, segment_name, a.bytes
  2    from dba_segments a
  3    join dba_lobs b
  4   using (owner, segment_name)
  5   where b.table_name = 'T';

TABLE_NAME                     COLUMN_NAME          SEGMENT_NAME                        BYTES
------------------------------ -------------------- ------------------------------ ----------
T                              TEXT                 SYS_LOB0000102100C00002$$       167772160

SQL> select sum(dbms_lob.getlength(text)) as bytes from t;

     BYTES
----------

空间依然没有释放，下面释放空间。

SQL> alter table t modify lob (text) (shrink space);

表已更改。

SQL> select table_name, column_name, segment_name, a.bytes
  2    from dba_segments a
  3    join dba_lobs b
  4   using (owner, segment_name)
  5   where b.table_name = 'T';

TABLE_NAME                     COLUMN_NAME          SEGMENT_NAME                        BYTES
------------------------------ -------------------- ------------------------------ ----------
T                              TEXT                 SYS_LOB0000102100C00002$$           65536

SQL> select sum(dbms_lob.getlength(text)) as bytes from t;

     BYTES
----------

 

SQL> begin
  2    for i in 1 .. 10000 loop
  3      insert into t values (sysdate, rpad('a', 4000, 'a'));
  4      commit;
  5    end loop;
  6  end;
  7  /

PL/SQL 过程已成功完成。

SQL> select table_name, column_name, segment_name, a.bytes
  2    from dba_segments a
  3    join dba_lobs b
  4   using (owner, segment_name)
  5   where b.table_name = 'T';

TABLE_NAME                     COLUMN_NAME          SEGMENT_NAME                        BYTES
------------------------------ -------------------- ------------------------------ ----------
T                              TEXT                 SYS_LOB0000102100C00002$$        83886080

SQL> truncate table t;

表被截断。

SQL> select table_name, column_name, segment_name, a.bytes
  2    from dba_segments a
  3    join dba_lobs b
  4   using (owner, segment_name)
  5   where b.table_name = 'T';

TABLE_NAME                     COLUMN_NAME          SEGMENT_NAME                        BYTES
------------------------------ -------------------- ------------------------------ ----------
T                              TEXT                 SYS_LOB0000102100C00002$$           65536

可以看见truncate会释放空间。

SQL> begin
  2    for i in 1 .. 10000 loop
  3      insert into t values (sysdate, rpad('a', 4000, 'a'));
  4      commit;
  5    end loop;
  6  end;
  7  /

PL/SQL 过程已成功完成。

SQL> select table_name, column_name, segment_name, a.bytes
  2    from dba_segments a
  3    join dba_lobs b
  4   using (owner, segment_name)
  5   where b.table_name = 'T';

TABLE_NAME                     COLUMN_NAME          SEGMENT_NAME                        BYTES
------------------------------ -------------------- ------------------------------ ----------
T                              TEXT                 SYS_LOB0000102100C00002$$        83886080

SQL> delete from t;

已删除10000行。

SQL> commit;

提交完成。

SQL> select table_name, column_name, segment_name, a.bytes
  2    from dba_segments a
  3    join dba_lobs b
  4   using (owner, segment_name)
  5   where b.table_name = 'T';

TABLE_NAME                     COLUMN_NAME          SEGMENT_NAME                        BYTES
------------------------------ -------------------- ------------------------------ ----------
T                              TEXT                 SYS_LOB0000102100C00002$$        83886080

SQL> alter table t move lob(text) store as(tablespace tab1);

表已更改。

SQL> select table_name, column_name, segment_name, a.bytes
  2    from dba_segments a
  3    join dba_lobs b
  4   using (owner, segment_name)
  5   where b.table_name = 'T';

TABLE_NAME                     COLUMN_NAME          SEGMENT_NAME                        BYTES
------------------------------ -------------------- ------------------------------ ----------
T                              TEXT                 SYS_LOB0000102100C00002$$           65536

move表空间也会释放空间。