1.创建测试表
SQL> create table t1 (id int);

Table created.


2.查看表占用的块数量
SQL> SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------

3.对T1表做统计信息收集
SQL> ANALYZE TABLE T1 COMPUTE STATISTICS;

Table analyzed.


4.再次查看表占用的块数量
SQL> SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
     0          7      0

5.向表中插入1000W条数据
declare
i number;
begin
for i in 1..10000000 loop
 insert into t1 values(i);
end loop;
commit;
end;
/


6.查看表占用的块数量
SQL> SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
     0          7      0

7.统计信息收集
SQL> ANALYZE TABLE T1 COMPUTE STATISTICS;

Table analyzed.

8.再次查看表占用的块数量
SQL> SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
     15155        204   10000000

9.删除数据
SQL> delete from t1;

10000000 rows deleted.

SQL> commit;

Commit complete.


10.查看表占用的块数量
SQL> SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
     15155        204   10000000

11.统计信息收集
SQL> ANALYZE TABLE T1 COMPUTE STATISTICS;

Table analyzed.


12.再次查看表占用的块数量，可以看到
SQL> SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
     15155        204      0

13.降低高水位操作
SQL> alter table T1 move;

Table altered.


14.再次查看表占用的块数量
SQL> SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
     15155        204      0


15.再次收集统计信息
SQL> ANALYZE TABLE T1 COMPUTE STATISTICS;

Table analyzed.

16.查看表占用的块数量，可以看到执行alter table T1 move命令后，高水位已经下降了。
SQL> SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
     0          7      0

4种降低高水位的方法：

ANALYZE TABLE T1 COMPUTE STATISTICS;      分析表
SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1';   查询高水位线方法
 1：alter table T1 enable row movement;    开启行迁移(收缩表操做第一步)
 alter table T1 shrink space ;     收缩表空间
 alter table T1 disable row movement;   关闭行迁移方法 
 2：alter table T1 move;    移动表需要新建索引
 create Index new_index On T1(deptno);   单一索引
 create Index new_index  on T1(deptno,job);   复合索引方法 
 3：truncate table T1;  清空表方法 
 4：create table T1_NEW as select * from T1 where 1=2;
 创建新表alter table T1_NEW rename to T1;    重命名新表

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle,linux