回收lob占用的空间

感谢：

http://halisway.blogspot.com/2007/06/reclaiming-lob-space-in-oracle.html

http://stackoverflow.com/questions/18367444/how-to-drop-oracle-lob

 

LOB字段是单独存储的，而且用delete是无法回收lob占用的空间。

SQL>  create table lob_test (id number, data blob);

 

Table created.

 

-- 向lob_test中导入数据

SQL> @lobload

PL/SQL procedure successfully completed.

 

SQL> select segment_name, segment_type, bytes from user_segments;

 

SEGMENT_NAME                   SEGMENT_TYPE            BYTES

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

LOB_TEST                       TABLE                                       131072

SYS_IL0000316067C00002$$       LOBINDEX                   131072

SYS_LOB0000316067C00002$$      LOBSEGMENT          134217728

 

-- 现在可以看到LOB是单独存储的

 

SQL> delete from lob_test;

 

500 rows deleted.

 

SQL> commit;

 

Commit complete.

 

SQL> select segment_name, segment_type, bytes from user_segments;

 

SEGMENT_NAME                   SEGMENT_TYPE            BYTES

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

LOB_TEST                       TABLE                                       131072

SYS_IL0000316067C00002$$       LOBINDEX                    327680

SYS_LOB0000316067C00002$$      LOBSEGMENT          134217728

-- delete后，lob占用的空间没有释放

-- 再次导入

SQL> @lobload

SQL> select segment_name, segment_type, bytes from user_segments;

 

SEGMENT_NAME                   SEGMENT_TYPE            BYTES

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

LOB_TEST                       TABLE                                       131072

SYS_IL0000316067C00002$$       LOBINDEX                    393216

SYS_LOB0000316067C00002$$      LOBSEGMENT          260046848

-- lob对应的segment 继续增长

-- 再次删除

 

SQL> delete from lob_test;

500 rows deleted.

 

SQL> commit;

Commit complete.

 

SQL> select segment_name, segment_type,   bytes from user_segments;

 

SEGMENT_NAME                   SEGMENT_TYPE            BYTES

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

LOB_TEST                       TABLE                                       131072

SYS_IL0000316067C00002$$       LOBINDEX                    655360

SYS_LOB0000316067C00002$$      LOBSEGMENT          260046848

 

 

SQL> alter table lob_test modify lob(data) (shrink space);

 

Table altered.

 

SQL> select segment_name, segment_type, bytes from user_segments;

 

SEGMENT_NAME                   SEGMENT_TYPE            BYTES

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

LOB_TEST                       TABLE                  131072

SYS_IL0000316067C00002$$       LOBINDEX               655360

SYS_LOB0000316067C00002$$      LOBSEGMENT              65536

 

SQL> alter table lob_test modify lob(data) (shrink space);

 

Table altered.

 

SQL> select segment_name, segment_type, bytes from user_segments;

 

SEGMENT_NAME                   SEGMENT_TYPE            BYTES

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

LOB_TEST                       TABLE                                           131072

SYS_IL0000316067C00002$$       LOBINDEX                       655360

SYS_LOB0000316067C00002$$      LOBSEGMENT              65536

 

--使用alter后 lob占用的空间被回收。

 

注意一

：在drop表的时候，如果不加purge ，lob segment一样不会被删除

 

删除lob_test前：

SQL> select segment_name, segment_type, bytes from user_segments;

SEGMENT_NAME                   SEGMENT_TYPE            BYTES

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

LOB_TEST                       TABLE                                       131072

SYS_IL0000316067C00002$$       LOBINDEX                   655360

SYS_LOB0000316067C00002$$      LOBSEGMENT          134217728

 

SQL> drop table lob_test;

Table dropped.

 

SQL> select segment_name, segment_type, bytes from user_segments;

 

SEGMENT_NAME                   SEGMENT_TYPE            BYTES

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

BIN$+oFtu0VUcIzgQH+HqwZhEw==$0 TABLE                    131072

SYS_IL0000316067C00002$$       LOBINDEX                    655360

SYS_LOB0000316067C00002$$      LOBSEGMENT          134217728

 

依然可以看到lob字段，证明不加purge是不法释放lob segment的

 

在回收站中，清理lob_test表

SQL> purge table lob_test;

Table purged.

 

SQL> select segment_name, segment_type, bytes from user_segments;

no rows selected

 

证明使用purge可以释放lob segment。

 

注意二：

一开始的建表语句是：

 create table lob_test (id number, data blob);

如果，建表时指定lob的segment，在drop不加purge时，lob的segment可以直接被删除，放入回收站中。

create table lob_test22(id number, data blob)

lob (data) store as data_blob_segment; 

 

 

SQL> select segment_name, segment_type, bytes from user_segments;

 

SEGMENT_NAME                   SEGMENT_TYPE            BYTES

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

LOB_TEST22                     TABLE                                131072

SYS_IL0000316073C00002$$       LOBINDEX               131072

DATA_BLOB_SEGMENT              LOBSEGMENT          134217728

 

SQL> drop table lob_test22;

 

Table dropped.

 

SQL> select segment_name, segment_type, bytes from user_segments;

 

SEGMENT_NAME                   SEGMENT_TYPE            BYTES

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

BIN$+oFtu0VWcIzgQH+HqwZhEw==$0 TABLE                     131072

SYS_IL0000316073C00002$$       LOBINDEX                      131072

BIN$+oFtu0VVcIzgQH+HqwZhEw==$0 LOBSEGMENT          134217728

 

SQL> select object_name, ORIGINAL_NAME from user_recyclebin;

 

OBJECT_NAME                    ORIGINAL_NAME

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

SYS_IL0000316073C00002$$            SYS_IL0000316073C00002$$

BIN$+oFtu0VVcIzgQH+HqwZhEw==$0 DATA_BLOB_SEGMENT

BIN$+oFtu0VWcIzgQH+HqwZhEw==$0 LOB_TEST22

 

附：

-bash-3.2$ cat lobload.sql 

DECLARE

  src_file      BFILE := bfilename('MY_DIR', 'data.dat');

  dst_file      BLOB;

  lgh_file      BINARY_INTEGER;

  cur_id        NUMBER(10);

BEGIN

  FOR i IN 1..500

  LOOP

  if (mod (i,10) = 0) then

   dbms_output.put_line(i);

  end if;

    INSERT INTO lob_test22(id,data) VALUES(lob_test_seq.nextval,empty_blob()) 

          RETURNING id into cur_id;

    commit;

    -- lock record

    SELECT data INTO dst_file FROM lob_test22 WHERE id=cur_id FOR UPDATE;

 

    dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

    lgh_file := dbms_lob.getlength(src_file);

    dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

    dbms_lob.fileclose(src_file);

  END LOOP;

END;

/

 

--这里的MY_DIR 是指数据库中的directory。如果使用create directory my_dir as '/tmp'; 那么这里就要求必须使用大写的MY_DIR。

 

移动LOB字段的哦其他表空间：

ALTER TABLE CENTER_ADMIN.NWS_NEWS 
MOVE LOB(ABSTRACT) 
STORE AS (TABLESPACE lob_test);