1. Overview of the High Water Mark (HWM):
The HWM is a point in a data segment that indicates the boundary between used and unused data blocks. When querying table data, all data blocks below the HWM are scanned (including blocks that previously contained data but are now empty).Generally, the HWM only increases and does not decrease. To check the HWM of a table, use the following example:``` SQL> analyze table big_table estimate statisitcs; SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='BIG_TABLE';
BLOCKS EMPTY_BLOCKS NUM_ROWS
30329 391 2010778
**Note:**BLOCKS: Represents the number of data blocks that have been used, i.e., the HWM.EMPTY_BLOCKS: Represents the data blocks allocated to the table but located above the HWM, i.e., never used.### 2. Move the Table
#### 2.1 Check Current HWM
SQL> analyze table big_table estimate statistics; SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='BIG_TABLE';
BLOCKS EMPTY_BLOCKS NUM_ROWS
30329 391 2010778
#### 2.2 Check for Indexes on the Table Before Moving
SQL> select index_name,table_name,table_owner from dba_indexes where table_name='big_table';
#### 2.3 Rebuild Indexes After Moving (Optional)
SQL> alter index <index_name> rebuild;
#### 2.4 Perform the Move
SQL> alter table big_table move;
Table altered.
#### 2.5 HWM After Release
SQL> analyze table big_table estimate statistics; SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='BIG_TABLE';
BLOCKS EMPTY_BLOCKS NUM_ROWS
15001 359 1011059
### 3. Shrink the Table
#### 3.1 Check Current HWM
SQL> analyze table big_table estimate statistics; SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='BIG_TABLE';
BLOCKS EMPTY_BLOCKS NUM_ROWS
30329 391 2010778
#### 3.2 Enable Row Movement and Parallelism
Check current parallelism: SQL> select OWNER,TABLE_NAME,DEGREE from DBA_TABLES where OWNER ='TEST' and TABLE_NAME = 'BIG_TABLE';
OWNER TABLE_NAME DEGREE
TEST BIG_TABLE DEFAULT
Set parallelism: alter table test.BIG_TABLE parallel 2;
Enable row movement: alter table test.big_table enable row movement;
#### 3.3 Check if the Table Can Be Shrunk
SQL> alter table test.big_table shrink space check; alter table test.big_table shrink space check * ERROR at line 1: ORA-10655: Segment can be shrunk --- indicates the table can be shrunk
#### 3.4 Shrink the Table Data
SQL> alter table test.big_table shrink space compact;
#### 3.5 Lower the HWM
SQL> alter table test.big_table shrink space;
#### 3.6 Shrink Indexes
SQL> alter table test.big_table shrink space cascade;
#### 3.7 HWM After Release
SQL> analyze table big_table estimate statistics; SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='BIG_TABLE';
BLOCKS EMPTY_BLOCKS NUM_ROWS
14984 168 1002139
#### 3.8 Disable Row Movement and Parallelism
SQL> alter table test.big_table disable row movement; SQL> alter table test.big_table parallel 1; SQL> alter table test.big_table paralel;
### 4. Export and Import Using Data Pump to Reconstruct the Table
#### 4.1 Check Current HWM
SQL> analyze table big_table estimate statistics; SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='BIG_TABLE';
BLOCKS EMPTY_BLOCKS NUM_ROWS
30329 391 2010778
#### 4.2 Export the Table
expdp '/ as sysdba' directory=dir dumpfile=big_table.dmp logfile=table.log schemas=test tables=big_table
#### 4.3 Drop the Table
SQL> drop table big_table purge;
Table dropped.
#### 4.4 Import the Table
impdp '/ as sysdba' driectory=dir dumpfile=big_table.dmp logfile=table2.log tables=test.big_table
#### 4.5 HWM After Release
SQL> analyze table big_table estimate statistics; SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='BIG_TABLE';
BLOCKS EMPTY_BLOCKS NUM_ROWS
14988 372 1010535
### 5. Copy Table Data
This method involves copying the remaining data into a new table, dropping the original table, and renaming the new table to match the original name. #### 5.1 Check Current HWM
SQL> analyze table big_table estimate statistics; SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='BIG_TABLE';
BLOCKS EMPTY_BLOCKS NUM_ROWS
30329 391 2010778
#### 5.2 Create a New Table
It is recommended to use the dbms_metadata.get_ddl method to retrieve the full table structure https://www.cnblogs.com/dclogs/p/18189838
SQL> create table copy_big_table as select * from big_table;
Table created.
#### 5.3 Drop the Original Table
SQL> drop table big_table purge;
Table dropped.
#### 5.4 Rename the New Table
SQL> alter table copy_big_table rename to big_table;
Table altered.
#### 5.5 HWM After Release
SQL> analyze table big_table estimate statistics; SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='BIG_TABLE';
BLOCKS EMPTY_BLOCKS NUM_ROWS
14998 362 1010504
### 6. Truncate
Use this only when the table contains no data. Exercise caution. #### 6.1 Check Current HWM
SQL> analyze table big_table estimate statistics; SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='BIG_TABLE';
BLOCKS EMPTY_BLOCKS NUM_ROWS
30329 391 2010778
#### 6.2 Delete All Data from the Table
SQL> delete big_table where id>0;
2000000 rows deleted.
SQL> commit;
Commit complete.
#### 6.3 Use Truncate to Free Up the HWM When No Data Exists
SQL> truncate table big_table;
Table truncated.
#### 6.4 HWM After Release
SQL> analyze table big_table estimate statistics; SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='BIG_TABLE';
BLOCKS EMPTY_BLOCKS NUM_ROWS
0 8 0