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表空间也会释放空间。