PURPOSE
-------
This document explains if the High Water Mark is reset during the following
procedures :
- Moving a table to another tablespace
- Export / Delete / Import
- Export / Truncate / Import
SCOPE & APPLICATION
-------------------
For all DBAs who need to reclaim disk space.
*** *************************************************************
High Water Mark Before/After Moving a Table to Another Tablespace
*** *************************************************************
a. Create a table on tablespace USERS
----------------------------------
SQL> create table t (a number, b number, c number, d number, e number,
f number)
tablespace USERS;
Table created.
b. Load table with 200000 rows
---------------------------
SQL> create or replace procedure populate (numrows in number) is
fa number; fb number; fc number; fd number; fe number; ff number;
begin
dbms_random.Initialize(1234567);
for i in 1..numrows loop
fa:=mod(abs(dbms_random.random),10)+1991;
fb:=mod(abs(dbms_random.random),2);
fc:=mod(abs(dbms_random.random),20);
fd:=mod(abs(dbms_random.random),30);
fe:=mod(abs(dbms_random.random),40);
ff:=mod(abs(dbms_random.random),10);
insert into t values(fa,fb,fc,fd,fe,ff);
if mod(i,100)=0 then commit; end if;
end loop;
dbms_random.Terminate;
end;
/
Procedure created.
SQL> set serveroutput on
SQL> execute populate(200000);
PL/SQL procedure successfully completed.
c. Delete rows
-----------
SQL> delete from t where a=1991 and b=0;
9991 rows deleted.
SQL> commit;
Commit complete.
...
SQL> delete from t where f=9;
13911 rows deleted.
d. Analyze the table to get the HWM value:
--------------------------------------
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select num_rows, blocks, empty_blocks from dba_tables
where table_name='T';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
126176 2654 550
e. Move the table to another tablespace TEST:
-----------------------------------------
SQL> alter table t move tablespace test;
Table altered.
SQL> select num_rows, blocks, empty_blocks
from dba_tables where table_name='T';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
The statistics are reset to NULL.
f. Reanalyze the table to get the new HWM value:
--------------------------------------------
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select num_rows, blocks, empty_blocks
from dba_tables where table_name='T';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
126176 1675 264
============> YES, the HWM is reset.
To compare this method with other methods such as Export/Truncate/Import, the
ALTER TABLE MOVE is more appropriate to optimize the reclaim of disk space,
since you can specify new storage clause values for the new segment created :
SQL> alter table t move tablespace TEST2 storage (initial 30k next 50k);
Table altered.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select num_rows, blocks, empty_blocks from dba_tables
where table_name='T';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
126176 1675 274
The ALTER TABLE MOVE can even be a good method to optimize the HWM even if the
move occurs within the same tablespace
*** *************************************************************
High Water Mark After Exporting/Deleting/Importing a Table
*** *************************************************************
Perform. the same steps a through d.
e. Export the table : 126176 rows exported
----------------
f. Delete all rows and reanalyze the table:
---------------------------------------
SQL> select num_rows, blocks, empty_blocks from dba_tables
where table_name='T';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
126176 2654 550
g. Import the table : 126176 rows imported
----------------
h. Reanalyze the table to table to get the new HWM value:
-----------------------------------------------------
SQL> select num_rows, blocks, empty_blocks from dba_tables
where table_name='T';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
126176 2654 550
============> NO, the HWM is not reset.
*** *************************************************************
High Water Mark After Exporting/Truncating/Importing a Table
*** *************************************************************
Perform. the same steps a through d.
e. Export the table : 126176 rows exported
----------------
f. Truncate the table keeping only the INITIAL extent:
--------------------------------------------------
SQL> truncate table t drop storage;
Table truncated.
SQL> select num_rows, blocks, empty_blocks from dba_tables
where table_name='T';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
0 0 4
g. Import the table : 126176 rows imported
----------------
h. Reanalyze the table to table to get the new HWM value:
-----------------------------------------------------
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select num_rows, blocks, empty_blocks from dba_tables
where table_name='T';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
126176 1669 470
============> YES, the HWM is reset.
You get the same behavior. with a TRUNCATE TABLE REUSE STORAGE, except that there
are more empty_blocks, unused (1535 empty blocks instead of 470 in our case).
RELATED DOCUMENTS
-----------------
Note:77635.1 How to Determine Real Space used by a Table (Below the High
Water Mark)
Note:1019716.6 Script. to Report Table Fragmentation
