如何知道一个表的HWM?
1、首先对表进行分析:
analyze table <tablename> compute/estimate statistics;
compute:是全表分析。
estimate :是抽样分析。
2、查看相关信息:
select segment_name,segment_type,bytes,blocks,extents from user_segments;
bytes:对象所占空间大小(字节)。
blocks:对象所占的数据块。bytes=blocks*8
extents:对象所占的区。
select table_name,blocks,empty_blocks,num_rows from user_tables;
blocks:该表中曾经使用过得数据库块的数目,即水线。
empty_blocks:代表分配给该表,但是在水线以上的数据库块,即从来没有使用的数据块。
num_rows:该表中记录数。
当用户发出一个全表扫描时,ORACLE 始终必须从段一直扫描到 HWM,即使它什么也没有发现。该任务延长了全表扫描的时间。
truncate和delete的区别:
SQL> insert into t1 select * from t2;
已创建402344行。
SQL> commit;
提交完成。
SQL> analyze table t1 compute statistics;
表已分析。
SQL> select segment_name,segment_type,bytes,blocks,extents from user_segments;
SEGMENT_NA SEGMENT_TY BYTES BLOCKS EXTENTS
---------- ---------- ---------- ---------- ----------
T2 TABLE 46137344 5632 59
T1 TABLE 46137344 5632 59
T1_IND INDEX 10485760 1280 25
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables;
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T2 5536 96 402344
T1 5536 96 402344
SQL> truncate table t1;
表被截断。
SQL> analyze table t1 compute statistics;
表已分析。
SQL> select segment_name,segment_type,bytes,blocks,extents from user_segments;
SEGMENT_NA SEGMENT_TY BYTES BLOCKS EXTENTS
---------- ---------- ---------- ---------- ----------
T2 TABLE 46137344 5632 59
T1 TABLE 65536 8 1
T1_IND INDEX 65536 8 1
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables;
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T2 5536 96 402344
T1 0 8 0
truncate会回收高水位和释放表所占的空间大小。
SQL> insert into t1 select * from t2;
已创建402344行。
SQL> commit;
提交完成。
SQL> analyze table t1 compute statistics;
表已分析。
SQL> select segment_name,segment_type,bytes,blocks,extents from user_segments;
SEGMENT_NA SEGMENT_TY BYTES BLOCKS EXTENTS
---------- ---------- ---------- ---------- ----------
T2 TABLE 46137344 5632 59
T1 TABLE 46137344 5632 59
T1_IND INDEX 10485760 1280 25
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables;
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T2 5536 96 402344
T1 5536 96 402344
SQL> delete from t1;
已删除402344行。
SQL> commit;
提交完成。
SQL> analyze table t1 compute statistics;
表已分析。
SQL> select segment_name,segment_type,bytes,blocks,extents from user_segments;
SEGMENT_NA SEGMENT_TY BYTES BLOCKS EXTENTS
---------- ---------- ---------- ---------- ----------
T2 TABLE 46137344 5632 59
T1 TABLE 46137344 5632 59
T1_IND INDEX 10485760 1280 25
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables;
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T2 5536 96 402344
T1 5536 96 0
delete之后,什么也没有改变。
SQL> alter table t1 enable row movement;
表已更改。
SQL> alter table t1 shrink space;
表已更改。
SQL> analyze table t1 compute statistics;
表已分析。
SQL> select segment_name,segment_type,bytes,blocks,extents from user_segments;
SEGMENT_NA SEGMENT_TY BYTES BLOCKS EXTENTS
---------- ---------- ---------- ---------- ----------
T2 TABLE 46137344 5632 59
T1 TABLE 65536 8 1
T1_IND INDEX 10485760 1280 25
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables;
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T2 5536 96 402344
T1 1 7 0
shrink space之后,和truncate效果一样了。
SQL> select count(*) from t1;
COUNT(*)
----------
0
SQL> select segment_name,segment_type,bytes,blocks,extents from user_segments;
SEGMENT_NA SEGMENT_TY BYTES BLOCKS EXTENTS
---------- ---------- ---------- ---------- ----------
T2 TABLE 46137344 5632 59
T1 TABLE 65536 8 1
T1_IND INDEX 10485760 1280 25
SQL> select index_name,status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
T1_IND VALID
SQL> alter index t1_ind rebuild;
索引已更改。
SQL> select segment_name,segment_type,bytes,blocks,extents from user_segments;
SEGMENT_NA SEGMENT_TY BYTES BLOCKS EXTENTS
---------- ---------- ---------- ---------- ----------
T2 TABLE 46137344 5632 59
T1 TABLE 65536 8 1
T1_IND INDEX 65536 8 1
可以看见表中记录都没有,索引还是占了这么大的空间,rebuild一下就正常了。
SQL> insert into t1 select * from t2;
已创建402344行。
SQL> commit;
提交完成。
SQL> analyze table t1 compute statistics;
表已分析。
SQL> select segment_name,segment_type,bytes,blocks,extents from user_segments;
SEGMENT_NA SEGMENT_TY BYTES BLOCKS EXTENTS
---------- ---------- ---------- ---------- ----------
T2 TABLE 46137344 5632 59
T1 TABLE 46137344 5632 59
T1_IND INDEX 10485760 1280 25
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables;
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T2 5536 96 402344
T1 5536 96 402344
SQL> delete from t1;
已删除402344行。
SQL> commit;
提交完成。
SQL> select segment_name,segment_type,bytes,blocks,extents from user_segments;
SEGMENT_NA SEGMENT_TY BYTES BLOCKS EXTENTS
---------- ---------- ---------- ---------- ----------
T2 TABLE 46137344 5632 59
T1 TABLE 46137344 5632 59
T1_IND INDEX 10485760 1280 25
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables;
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T2 5536 96 402344
T1 5536 96 402344
SQL> alter table t1 move;
表已更改。
SQL> analyze table t1 compute statistics;
表已分析。
SQL> select segment_name,segment_type,bytes,blocks,extents from user_segments;
SEGMENT_NA SEGMENT_TY BYTES BLOCKS EXTENTS
---------- ---------- ---------- ---------- ----------
T2 TABLE 46137344 5632 59
T1 TABLE 65536 8 1
T1_IND INDEX 10485760 1280 25
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables;
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T2 5536 96 402344
T1 0 8 0
移动表之后就对头了,不过索引还是要rebuild。
下面的方法都可以降低高水位线标记。
(1). 执行表重建指令 alter table table_name move;
在线转移表空间ALTER TABLE ... MOVE TABLESPACE ..
当你创建了一个对象如表以后,不管你有没有插入数据,它都会占用一些块,ORACLE也会给它分配必要的空间.同样,用ALTER TABLE MOVE释放自由空间后,还是保留了一些空间给这个表.
ALTER TABLE ... MOVE 后面不跟参数也行,不跟参数表还是在原来的表空间,Move后记住重建索引。如果以后还要继续向这个表增加数据,没有必要move,只是释放出来的空间,只能这个表用,其他的表或者segment无法使用该空间。
(2). 执行alter table table_name shrink space;
注意,此命令为Oracle 10g新增功能,再执行该指令之前必须允许行移动 alter table table_name enable row movement;
如果要同时压缩表的索引:ALTER TABLE TEST_TAB SHRINK SPACE CASCADE
alter table table_name shrink space compact;如果使用了compact,oracle会整理表的碎片但不会调整高水位线,需要使用alter table table_name shrink space;语句来重新设置高水位线。
如果你很在意整理碎片和调整高水位线所花费的时间。通过两个短短的步骤,无需花费很长时间就可以对表进行收缩。
(3). 复制要保留的数据到临时表t,drop原表,然后rename临时表t为原表
(4). 用逻辑导入导出: Emp/Imp
(5). Alter table table_name deallocate unused
注:这证明,DEALLOCATE UNUSED为释放HWM上面的未使用空间,但是并不会释放HWM下面的自由空间,也不会移动HWM的位置.
(6). 尽量使用truncate