LOB字段HWM问题收缩高水位线的测试

1、带lob字段的表，insert和delete的情况下，lob segment会不会产生HWM的问题？(会，在delete以后out of row的lob segment也不会自动收缩)
2、在move表的时候，对lob字段是否会有影响？例如lob索引会出现问题。（不会，无论in row还是out of row，无论只move表，还是连带lob segment一起move，move后lob index都不会失效。）
3、在高水位较高的情况下lob导出会带高水位吗？（目前只找到了通过导出方式来判断lob字段高水位的方法）
4、move lob字段释放高水位。（alter table lobtab1 move lob(text) store as (tablespace users);）
5、alter table lobtab1 move lob(text) store as (tablespace users);（会同时回收表和lob的高水位）

6、只move表，不move lob字段，lob的水位线是不是会回收？（不会，只有表的水位线会回收）

【对于in row和属性的lob，在lob小于varchar2(4000)时，与表数据一起存储在table segment中。lob长度大于4000时单独存放在lob segment中】
【对于out of row的lob，无论lob大小，都存储在lob segment中】

set lines 200 pages 200
col table_name for a15
col column_name for a20
col segment_name for a40
col index_name for a30
col index_type for a20

--创建lob字段表：
create table lobtab(id number,text clob) lob(text) store as (disable storage in row) tablespace users;

--找到lob对应的索引及状态和lob segment的名字

select table_name,column_name,segment_name,index_name,IN_ROW,partitioned from user_lobs where table_name='LOBTAB';

TABLE_NAME COLUMN_NAME SEGMENT_NAME INDEX_NAME IN_ROW PARTITION
--------------- -------------------- ---------------------------------------- ------------------------------ --------- ---------
LOBTAB TEXT SYS_LOB0000016753C00002$$ SYS_IL0000016753C00002$$ NO NO

select index_name,index_type,TABLE_NAME,UNIQUENESS,STATUS from user_indexes where table_name='LOBTAB';

INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENESS STATUS
------------------------------ -------------------- --------------- --------------------------- ------------------------
SYS_IL0000016753C00002$$ LOB LOBTAB UNIQUE VALID

--向表中插入数据。

begin
for i in 1 .. 10000 loop
insert into lobtab values(i,'abcdlkksdfjoeijflakdjlfkjdhfiwuehfjdalhfkjehwiulfhakjdhfuiewlahjkdshfkjadscnjkadsfhiewulhfkjasdbfkjbckdhfjkhaiuwlejkbkjchkljadsbf');
commit;
end loop;
end;
/

--查看初始的表HWM和lob segment的HWM
TEST @testdb> select count(*) from lobtab;
COUNT(*)
----------
10000

select segment_name,bytes/1024 KB from user_segments where segment_name in ('LOBTAB','SYS_LOB0000016753C00002$$');
SEGMENT_NAME KB
---------------------------------------- ----------
LOBTAB 384
SYS_LOB0000016753C00002$$ 81920

--删除数据9000行
delete from lobtab where id >1000;
commit;

--查看表HWM和lob segment的HWM （delete数据以后lob segment的高水位不会释放。）
select segment_name,bytes/1024 KB from user_segments where segment_name in ('LOBTAB','SYS_LOB0000016753C00002$$');
SEGMENT_NAME KB
---------------------------------------- ----------
LOBTAB 384
SYS_LOB0000016753C00002$$ 81920

--导出表(导出的评估大小是80.37MB，导出的实际大小是268K，dmp文件大小是380k，所以导出的大小预估计算高水位，实际不计算高水位。)
expdp test/test tables=lobtab dumpfile=lobtab.dmp directory=ORA_DUMP logfile=lobtab_expdp.log;

Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** tables=lobtab dumpfile=lobtab.dmp directory=ORA_DUMP logfile=lobtab_expdp.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 80.37 MB
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."LOBTAB" 268.0 KB 1000 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/oracle/ora_dump/lobtab.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Wed Dec 9 13:51:49 2020 elapsed 0 00:00:17

ORA_DUMP
/oracle/ora_dump

--move表的时候，对lob字段是否会有影响
alter table lobtab move;
SEGMENT_NAME KB
---------------------------------------- ----------
SYS_LOB0000016753C00002$$ 81920
LOBTAB 64
表的水位线已经收缩。

TEST @testdb> select * from lobtab where id=456;
ID TEXT
---------- --------------------------------------------------------------------------------
456 abcdlkksdfjoeijflakdjlfkjdhfiwuehfjdalhfkjehwiulfhakjdhfuiew
lahjkdshfkjadscnjkadsfhiewulhfkjasdbfkjb
ckdhfjkhaiuwlejkbkjchkljadsbf
查询可用。

--move lob segment收缩高水位。
alter table lobtab move lob(text) store as (tablespace users);

SEGMENT_NAME KB
---------------------------------------- ----------
LOBTAB 64
SYS_LOB0000016753C00002$$ 9216

ID TEXT
---------- --------------------------------------------------------------------------------
456 abcdlkksdfjoeijflakdjlfkjdhfiwuehfjdalhfkjehwiulfhakjdhfuiew
lahjkdshfkjadscnjkadsfhiewulhfkjasdbfkjb
ckdhfjkhaiuwlejkbkjchkljadsbf

附录：
如果是in row的lob表，move索引是否会失效？

create table lobtab1(id number,text clob) lob(text) store as (enable storage in row) tablespace users;

select table_name,column_name,segment_name,index_name,IN_ROW,partitioned from user_lobs where table_name='LOBTAB1';
TABLE_NAME COLUMN_NAME SEGMENT_NAME INDEX_NAME IN_ROW PARTITION
--------------- -------------------- ---------------------------------------- ------------------------------ --------- ---------
LOBTAB1 TEXT SYS_LOB0000016780C00002$$ SYS_IL0000016780C00002$$ YES NO

select index_name,index_type,TABLE_NAME,UNIQUENESS,STATUS from user_indexes where table_name='LOBTAB1';

begin
for i in 1 .. 10000 loop
insert into lobtab1 values(i,'abcdlkksdfjoeijflakdjlfkjdhfiwuehfjdalhfkjehwiulfhakjdhfuiewlahjkdshfkjadscnjkadsfhiewulhfkjasdbfkjbckdhfjkhaiuwlejkbkjchkljadsbf');
commit;
end loop;
end;
/

select segment_name,bytes/1024 KB from user_segments where segment_name in ('LOBTAB1','SYS_IL0000016780C00002$$');
SEGMENT_NAME KB
---------------------------------------- ----------
LOBTAB1 4096
SYS_IL0000016780C00002$$ 64

delete from lobtab1 where id >1000;
commit;

alter table lobtab1 move;
INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENESS STATUS
------------------------------ -------------------- --------------- --------------------------- ------------------------
SYS_IL0000016780C00002$$ LOB LOBTAB1 UNIQUE VALID

SEGMENT_NAME KB
---------------------------------------- ----------
SYS_IL0000016780C00002$$ 64
LOBTAB1 448

begin
for i in 1001 .. 10000 loop
insert into lobtab1 values(i,'abcdlkksdfjoeijflakdjlfkjdhfiwuehfjdalhfkjehwiulfhakjdhfuiewlahjkdshfkjadscnjkadsfhiewulhfkjasdbfkjbckdhfjkhaiuwlejkbkjchkljadsbf');
commit;
end loop;
end;
/

insert into lobtab1 values(88888,'abcdlfdafadsklfjadskfjdkaslfjkadsfhkladsjfhakdhklashfkjadshfkjadhskjfhadskfhakdsjfhadkfhakfhaksjfhakdhkasjfhakdsjfhkdhkadsfhkadsjjjjjjjjjjjjjjjjjjfaffffffffffffffffffffffffffffffffffffffffffffffffffladssssssssssssssssssssssssssfdssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssfhjkladsfdsahlfkjdslfhldsjkfdshfkjadshlkfjashkfasjhflkadsjfhklsjhfkdskksdfjoeijflakdjlfkjdhfiwuehfjdalhfkjehwiulfhakjdhfuiewlahjkdshfkjadscnjkadsfhiewulhfkjasdbfkjbckdhfjkhaiuwlejkbkjchkljadsbf');
insert into lobtab1 values(88888,'abcdlfdafadsklfjadskfjdkaslfjkadsfhkladsjfhakdhklashfkjadshfkjadhskjfhadskfhakdsjfhadkfhakfhaksjfhakdhkasjfhakdsjfhkdhkadsfhkadsjjjjjjjjjjjjjjjjjjfaffffffffffffffffffffffffffffffffffffffffffffffffffladssssssssssssssssssssssssssfdssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssfhjkladsfdsahlfkjdslfhldsjkfdshfkjadshlkfjashkfasjhflkadsjfhklsjhfkdskksdfjoeijflakdjlfkjdhfiwuehfjdalhfkjehwiulfhakjdhfuiewlahjkdshfkjadscnjkadsfhiewulhfkjasdbfkjbckdhfjkhaiuwlejkbkjchkljadsbf');
insert into lobtab1 values(88888,'abcdlfdafadsklfjadskfjdkaslfjkadsfhkladsjfhakdhklashfkjadshfkjadhskjfhadskfhakdsjfhadkfhakfhaksjfhakdhkasjfhakdsjfhkdhkadsfhkadsjjjjjjjjjjjjjjjjjjfaffffffffffffffffffffffffffffffffffffffffffffffffffladssssssssssssssssssssssssssfdssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssfhjkladsfdsahlfkjdslfhldsjkfdshfkjadshlkfjashkfasjhflkadsjfhklsjhfkdskksdfjoeijflakdjlfkjdhfiwuehfjdalhfkjehwiulfhakjdhfuiewlahjkdshfkjadscnjkadsfhiewulhfkjasdbfkjbckdhfjkhaiuwlejkbkjchkljadsbf');
insert into lobtab1 values(88888,'abcdlfdafadsklfjadskfjdkaslfjkadsfhkladsjfhakdhklashfkjadshfkjadhskjfhadskfhakdsjfhadkfhakfhaksjfhakdhkasjfhakdsjfhkdhkadsfhkadsjjjjjjjjjjjjjjjjjjfaffffffffffffffffffffffffffffffffffffffffffffffffffladssssssssssssssssssssssssssfdssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssfhjkladsfdsahlfkjdslfhldsjkfdshfkjadshlkfjashkfasjhflkadsjfhklsjhfkdskksdfjoeijflakdjlfkjdhfiwuehfjdalhfkjehwiulfhakjdhfuiewlahjkdshfkjadscnjkadsfhiewulhfkjasdbfkjbckdhfjkhaiuwlejkbkjchkljadsbf');

SEGMENT_NAME KB
---------------------------------------- ----------
SYS_IL0000016780C00002$$ 64
LOBTAB1 4096

delete from lobtab1 where id >1000;
commit;

SEGMENT_NAME KB
---------------------------------------- ----------
SYS_IL0000016780C00002$$ 64
LOBTAB1 4096

alter table lobtab1 move lob(text) store as (tablespace users);

SEGMENT_NAME KB
---------------------------------------- ----------
LOBTAB1 448
SYS_IL0000016780C00002$$ 64

TEST @testdb> select index_name,index_type,TABLE_NAME,UNIQUENESS,STATUS from user_indexes where table_name='LOBTAB1';

posted on 2020-12-10 09:01 Nathan-wang 阅读(630) 评论(0) 编辑收藏举报

刷新页面返回顶部

导航

LOB字段HWM问题 收缩高水位线的测试

LOB字段HWM问题收缩高水位线的测试