oracle shrink space compact,oracle 学习笔记 Shrink 参数 compact

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操作被阻塞。