oracle清空blob,为何truncate能清除blob段，而update null不行

最新推荐文章于 2021-04-10 20:02:11 发布

AIS_Canada

最新推荐文章于 2021-04-10 20:02:11 发布

阅读量325

点赞数

文章标签： oracle清空blob

SQL> conn demo/demo

Connected.

SQL> col segment_name format a30

SQL> select segment_name,bytes from user_segments;

SEGMENT_NAME BYTES

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

T_EXCHANGE 65536

T_EXCHANGE_TEMP 65536

T_PART 65536

T_PART_EXCHANGE 65536

IDX_T_EXCHANGE 65536

IND_EXCHANGE_TEMP_TYPEID 65536

IDX_T_PART 65536

IND_PART_EXCHANGE_TYPEID 65536

14 rows selected.

SQL> create table t_lob_test (id number(8), note clob);

Table created.

SQL> select segment_name,bytes from user_segments;

SEGMENT_NAME BYTES

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

T_EXCHANGE 65536

T_EXCHANGE_TEMP 65536

T_PART 65536

T_PART_EXCHANGE 65536

IDX_T_EXCHANGE 65536

IND_EXCHANGE_TEMP_TYPEID 65536

IDX_T_PART 65536

IND_PART_EXCHANGE_TYPEID 65536

14 rows selected.

SQL> insert into t_lob_test values (1,null);

1 row created.

SQL> commit;

Commit complete.

SQL> select segment_name,bytes from user_segments;

SEGMENT_NAME BYTES

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

T_EXCHANGE 65536

T_EXCHANGE_TEMP 65536

T_LOB_TEST 65536

T_PART 65536

T_PART_EXCHANGE 65536

IDX_T_EXCHANGE 65536

IND_EXCHANGE_TEMP_TYPEID 65536

SYS_IL0000075810C00002$$ 65536

IDX_T_PART 65536

IND_PART_EXCHANGE_TYPEID 65536

SYS_LOB0000075810C00002$$ 65536

17 rows selected.

SQL> truncate table t_lob_test;

Table truncated.

SQL> select segment_name,bytes from user_segments;

SEGMENT_NAME BYTES

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

T_EXCHANGE 65536

T_EXCHANGE_TEMP 65536

T_LOB_TEST 65536

T_PART 65536

T_PART_EXCHANGE 65536

IDX_T_EXCHANGE 65536

IND_EXCHANGE_TEMP_TYPEID 65536

SYS_IL0000075810C00002$$ 65536

SEGMENT_NAME BYTES

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

IDX_T_PART 65536

IND_PART_EXCHANGE_TYPEID 65536

SYS_LOB0000075810C00002$$ 65536

17 rows selected.

SQL> insert into t_lob_test values (1,'asdfghjkl');

1 row created.

SQL> insert into t_lob_test select * from t_lob_test;

1 row created.

SQL> /

2 rows created.

SQL> /

4 rows created.

SQL> /

8 rows created.

SQL> /

16 rows created.

SQL> /

32 rows created.

SQL> /

64 rows created.

SQL> /

128 rows created.

SQL> /

256 rows created.

SQL> /

512 rows created.

SQL> /

1024 rows created.

SQL> /

2048 rows created.

SQL> /

4096 rows created.

SQL> /

8192 rows created.

SQL> /

16384 rows created.

SQL> select segment_name,bytes from user_segments;

SEGMENT_NAME BYTES

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

T_EXCHANGE 65536

T_EXCHANGE_TEMP 65536

T_LOB_TEST 3145728

T_PART 65536

T_PART_EXCHANGE 65536

IDX_T_EXCHANGE 65536

IND_EXCHANGE_TEMP_TYPEID 65536

SYS_IL0000075810C00002$$ 65536

IDX_T_PART 65536

IND_PART_EXCHANGE_TYPEID 65536

SYS_LOB0000075810C00002$$ 65536

17 rows selected.

SQL> alter table t_lob_test modify note not null;

Table altered.

SQL> select segment_name,bytes from user_segments;

SEGMENT_NAME BYTES

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

T_EXCHANGE 65536

T_EXCHANGE_TEMP 65536

T_LOB_TEST 3145728

T_PART 65536

T_PART_EXCHANGE 65536

IDX_T_EXCHANGE 65536

IND_EXCHANGE_TEMP_TYPEID 65536

SYS_IL0000075810C00002$$ 65536

IDX_T_PART 65536

IND_PART_EXCHANGE_TYPEID 65536

SYS_LOB0000075810C00002$$ 65536

17 rows selected.

SQL> truncate table t_lob_test;

Table truncated.

SQL> select segment_name,bytes from user_segments;

SEGMENT_NAME BYTES

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

T_EXCHANGE 65536

T_EXCHANGE_TEMP 65536

T_LOB_TEST 65536

T_PART 65536

T_PART_EXCHANGE 65536

IDX_T_EXCHANGE 65536

IND_EXCHANGE_TEMP_TYPEID 65536

SYS_IL0000075810C00002$$ 65536

IDX_T_PART 65536

IND_PART_EXCHANGE_TYPEID 65536

SYS_LOB0000075810C00002$$ 65536

17 rows selected.

AIS_Canada

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle清空blob,为何truncate能清除blob段，而update null不行

SQL> conn demo/demoConnected.SQL> col segment_name format a30SQL> select segment_name,bytes from user_segments;SEGMENT_NAME BYTES------------------------------ --------...
复制链接

扫一扫