本文共 1568 字,大约阅读时间需要 5 分钟。
alter table t shrink space 与 alter table t shrink space compact 区别在于compact 只对空间进行紧缩,高水位不下降。
SQL> SELECT table_name,num_rows,blocks FROM dba_tables where table_name='T' and owner='SYS';
TABLE NUM_ROWS BLOCKS
----- ---------- ----------
T 171653 791
SQL> delete from t where rownum<=30000;
30000 rows deleted.
SQL> commit;
Commit complete.
SQL> execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'T' ,estimate_percent => 100 ,cascade => true);
PL/SQL procedure successfully completed.
SQL> SELECT table_name,num_rows,blocks FROM dba_tables where table_name='T' and owner='SYS';
TABLE NUM_ROWS BLOCKS
----- ---------- ----------
T 141653 791
SQL> alter table t shrink space compact;
Table altered.
SQL> execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'T' ,estimate_percent => 100 ,cascade => true);
PL/SQL procedure successfully completed.
SQL> execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'T' ,estimate_percent => 100 ,cascade => true);
PL/SQL procedure successfully completed.
SQL> SELECT table_name,num_rows,blocks FROM dba_tables where table_name='T' and owner='SYS';
TABLE NUM_ROWS BLOCKS
----- ---------- ----------
T 141653 791
SQL> alter table t shrink space;
Table altered.
SQL> execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'T' ,estimate_percent => 100 ,cascade => true);
PL/SQL procedure successfully completed.
SQL> SELECT table_name,num_rows,blocks FROM dba_tables where table_name='T' and owner='SYS';
TABLE NUM_ROWS BLOCKS
----- ---------- ----------
T 141653 644
调整HWM会导致DML操作被阻塞。
转载地址:https://blog.csdn.net/weixin_33682804/article/details/116373252 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!