一张名为T_TEST的表中有记录75个，
使用sql：
select T.segment_name,t.bytes/1024/1024total_sizefrom user_segments t where t.segment_name ='T_TEST'
SEGMENT_NAME
TOTAL_SIZE
-------------------------------------------------------------------------------- ----------
TACCTWATER
1534
查找到total_size大小为1.5G,使用truncate后占用大小不变
使用脚本查看高水位：
SQL> DECLARE
2ownerVARCHAR2(30);
3NAME VARCHAR2(30);
4seg_type VARCHAR2(30);
5tblock NUMBER;
6tbyteNUMBER;
7ublock NUMBER;
8ubyteNUMBER;
9lue_fidNUMBER;
10lue_bidNUMBER;
11lublockNUMBER;
12
13BEGIN
14
15dbms_space.unused_space(upper('mocs2'), upper('T_TEST'), 'TABLE', tblock, tbyte, ublock, ubyte, lue_fid, lue_bid, lublock);
16dbms_output.put_line('Total blocks allocated to table = '|| to_char(tblock));
17dbms_output.put_line('Total bytes allocated to table = '|| to_char(tbyte));
18dbms_output.put_line('Unused blocks(above HWM) = '|| to_char(ublock));
19dbms_output.put_line('Unused bytes(above HWM)= '|| to_char(ubyte));
20dbms_output.put_line('Last extent used file id = '|| to_char(lue_fid));
21dbms_output.put_line('Last extent used beginning block id = '|| to_char(lue_bid));
22dbms_output.put_line('Last used block in last extent = '|| to_char(lublock));
23END;
24/
Total blocks allocated to table = 128
Total bytes allocated to table = 1048576
Unused blocks(above HWM) = 126
Unused bytes(above HWM)= 1032192
Last extent used file id = 116
Last extent used beginning block id = 2569
Last used block in last extent = 2
可见高水位并不高（为1），所以使用truncate不会移回HWM，但是不明白空间为什么会占用1.5G？
查询分配的块数：
SQL> select sum(blocks) "Total Blocks",sum(bytes) "Total Size" from user_extents
2wheresegment_name='T_TEST';
Total Blocks Total Size
------------ ----------
1281048576
当move表后：
查看高水位：
SQL> DECLARE
2ownerVARCHAR2(30);
3NAME VARCHAR2(30);
4seg_type VARCHAR2(30);
5tblock NUMBER;
6tbyteNUMBER;
7ublock NUMBER;
8ubyteNUMBER;
9lue_fidNUMBER;
10lue_bidNUMBER;
11lublockNUMBER;
12
13BEGIN
14
15dbms_space.unused_space(upper('mocs2'), upper('T_TEST'), 'TABLE', tblock, tbyte, ublock, ubyte, lue_fid, lue_bid, lublock);
16dbms_output.put_line('Total blocks allocated to table = '|| to_char(tblock));
17dbms_output.put_line('Total bytes allocated to table = '|| to_char(tbyte));
18dbms_output.put_line('Unused blocks(above HWM) = '|| to_char(ublock));
19dbms_output.put_line('Unused bytes(above HWM)= '|| to_char(ubyte));
20dbms_output.put_line('Last extent used file id = '|| to_char(lue_fid));
21dbms_output.put_line('Last extent used beginning block id = '|| to_char(lue_bid));
22dbms_output.put_line('Last used block in last extent = '|| to_char(lublock));
23END;
24/
Total blocks allocated to table = 128
Total bytes allocated to table = 1048576
Unused blocks(above HWM) = 126
Unused bytes(above HWM)= 1032192
Last extent used file id = 125
Last extent used beginning block id = 3977
Last used block in last extent = 2
PL/SQL procedure successfully completed
可见高水位的位置没有变化
再查询表空间占用大小
SQL> select T.segment_name,t.bytes/1024/1024 from user_segments t where t.segment_name ='T_TEST';
SEGMENT_NAME
T.BYTES/1024/1024
-------------------------------------------------------------------------------- -----------------
T_TEST
1
表占用的物理空间已经由1.5G 降低到1M
查询分配的块数：
SQL> select sum(blocks) "Total Blocks",sum(bytes) "Total Size" from user_extents
2wheresegment_name='T_TEST';
Total Blocks Total Size
------------ ----------
1281048576
请问为什么么move后，表占用的空间已经由1.5G缩减到1M？这个问题应该是和高水位没有关系吧 ？
（另外user_extents里的bytes和user_segments里的bytes有什么区别？ ） 谢谢
|
