HWM

HighWater Mark

简介：Interfacetables traditionally have rows inserted and then deleted after they areprocessed.  Deleting rows from a table does not free the space in whichthe rows resided.  As more and more rows are added and deleted, the highwater mark is raised and new rows cannot occupy the previously usedspace.  The only way to make the space reusable again is to TRUNCATE thetable, thus lowering the high water mark。

 

Step 1： identified  table with HWM

The following script was used to determine thehigh water mark:

SELECT TABLE_NAME ,  (BLOCKS *8192 /1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024) 
"Data lower than HWM in MB"   FROM DBA_TABLES WHERE  UPPER(owner) =UPPER('&OWNER') order by 2 desc;----checkfragment for every table.
select segment_name, segment_type, bytes fromdba_segments
where segment_name = '#####' ;------------youcan change this as your request .

Solution1：When the table is created with CTASfrom another table, the high water mark of the latter table is not reflected inthe new table.

To implement the solution, please execute thefollowing steps:
1. Ensure that you have taken a backup of yoursystem before applying the recommended solution.
   create table TAB_BKP as select*  from TAB;
2. Run the following scripts in a TESTenvironment first:
   TRUNCATE table TAB;
3. Reload the interface table.
    insert into TAB
    select * from TAB_BKUP;
4. Confirm that the table was reloaded.
    select count(*) from TAB ; 
    Were the number of rowsloaded the same as were backed up?
5. Check table space
   select segment_name, segment_type,bytes, initial_extent, next_extent
   from dba_segments
   where segment_name = 'TAB' ;
   Note: initial_extent and next_extentshould have the same value.
Commit;
6. Gather stats on MTL_TRANSACTIONS_INTERFACE
    execfnd_stats.gather_table_stats('SYS','TAB);
7. Drop MTI backup
    drop table TAB_BKUP ;
8. Confirm that the data is corrected whenviewed in the Oracle Applications.
9. If you are satisfied that the issue isresolved, migrate the solution as appropriate to other environments.

10.check the index status

Solution2：shrink

适用于10g以后的版本，前提是这个表，索引，物化视图或物化视图log所在表空间segment的管理是auto的，并且表已enable row movement，如：

SQL> selectowner,segment_name,bytes,blocks,extents from dba_segments

where segment_name = 'EMP';

SQL> alter table emp enable row movement;

SQL> alter table emp shrink space;

SQL> selectowner,segment_name,bytes,blocks,extents from dba_segments

where segment_name = 'EMP';

Solution3:online  redefinition-----nevertested

1.调用dbms_redefinition.can_redef_tableprocedure确认是否可以online redefinition

SQL> EXECUTEdbms_redefinition.can_redef_table ('FINANCE', 'ACCTS_PAYABLE');

2.在redefinition schema下创建interimtable

3.如果redefinition一个大表，可以用下面的Statement来改善性能:

SQL> alter session force parallel dmlparallel degree-of-parallelism;

SQL> alter session force parallel queryparallel degree-of-parallelism;

4.调用dbms_redefinition.start_redef_tableprocedure开如redefinition

SQL>dbms_redefinition.start_redef_table('FINANCE','ACCTS_PAYABLE','ACCTS_PAYABLE_STAGE');

5.创建dependent objectson interim table

有两种方法：

调用dbms_redefinition.copy_table_dependents自动创建dependentobjects;

使用create statement手动创建dependent objects，手动创建的dependents需要调用register_dependent_object注册

6.更新在redefinition过程中的更新到interim表

7.调用dbms_redefinition.finish_redef_table完成redefinition

8.删除收回空间如interim table使用的表

注：使用online redefinition需要额外的空间

Solution 4:expdp/impdp like the solution 1.

We can expdp the table that need to reducehigh water mark ,truncate table to lower HWM , then we can impdp the table ,andcheck the real rowid and index .

Solution 5: move tablespace

SQL> select table_name,tablespace_name,num_rows,last_analyzed from user_tables
  2  where table_name='T';
SQL> select count(*) from t;
  COUNT(*)
----------
      1000
SQL> analyze table t compute statistics;
SQL> select table_name,tablespace_name,num_rows,last_analyzed from user_tables
  2  where table_name='T';
user_table中的num_rows是最后一次analyze取得的数据。
SQL> alter table t move tablespace test;
表已更改。
SQL> select table_name,tablespace_name,num_rows,last_analyzed from user_tables
  2  where table_name='T';
SQL> select index_name,status from user_indexes where table_name='T';
INDEX_NAME                     STATUS
------------------------------ --------
T_INDEX3                       UNUSABLE
SQL> alter index t_index3 rebuild;
SQL> select index_name,status from user_indexes where table_name='T';
INDEX_NAME                     STATUS
------------------------------ --------
T_INDEX3                       VALID
SQL>  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'t');
PL/SQL 过程已成功完成。
结论：
对表执行alter table xxx move tablespace yyy后，必须对索引进行重建。
此外，把索引移到另外一个表空间；索引中存在较多deleted entry时（sliding index）；都需要进行索引重建，但需要注意的是：重建索引时，会lock table。最好执行alter index t_index3 rebuild online较为合适，尤其是在OLTP系统中。