oracle資料庫高水位線問題處理

一、什么是高水線(High Water Mark)?

Oracle 資料庫在創建一張表時，會為這張表分配一個段空間（segment）,為了方便理解，把段空間容納資料的上限，稱之為高水位線（HIGH WATER MARK） HWM ，HWM是一個標記，用來說明表示有多少未使用的塊分配給這個段，
兩個結論：
1.水位線以上表示已經分配但還未使用塊（block),水位先以下愛表示已經分配且已經使用過的塊（包含了正在使用的塊和使用過的且被洗掉了資料的空塊）
2.理論上來說，一張表的水位線只會增大不會減小（除非通過特殊的方法重置），即使將表中的資料全部洗掉，HWM還是為原值，

二、HWM資料庫的操作有如下影響：

a) 全表掃描通常要讀出直到HWM標記的所有的屬于該表資料庫塊，即使該表中沒有任何資料，

b) 即使HWM以下有空閑的資料庫塊，鍵入在插入資料時使用了append關鍵字，HWM也會不斷增大，占用系統資源，表所占的實際空間會不斷增大，導致系統出現問題

三、高水位線原因以及解決方法：

產生原因：
1.操作表時使用洗掉了大量資料，
2.在插入時使用了/append nologging/陳述句，append關鍵字會從為表分配段中的隨機位置插入，水位線會不斷增高，
3.Sql load 時默認使用truncate 自帶了reuse storage引數，導致truncate以后水位線不會降低，

解決方法：
1.直接truncate table drop storage
2.建立一張維護表定期move并重建索引或者shrink space，
3.表資料落表時按照日期建立了備份表，保留一定天數資料
4.Rename表名，重建表，重建索引，將資料匯入重建表，drop原表，然后rename重建表為原表
5.使用alter table 表名 shrink space（oracle10新增功能）
6.在線表重定義（功能強大，操作復雜，一般不使用，可以改變表的結構）
表重建的兩個方法move與shrink的對比：
move是oracle8出現的命令，使用時會創建一塊和原來表空間相同大小的另一塊表空間，然后進行資料的復制，完成后使用后表替換原表，解決hwm的問題，
缺點：操作時鎖表，索引會失效，
shrink是oracle10新增功能，使用時不會開辟新的表空間，操作分為兩步，第一步整理資料，第二步降低水位線，進行第一步時，可以在線進行操作，可以再業務不繁忙的時候進行第二步操作，
缺點：相比move速度比較緩慢，
shrink的詳細操作步驟：
詳細收縮步驟

1. 全表收縮
   不管磁區表還是非磁區表，收縮都可以表級別進行，具體陳述句如下：
   ALTER TABLE owner.table_name ENABLE ROW MOVEMENT;（開啟行移動會使游標失效，需謹慎）
   ALTER TABLE owner.table_name SHRINK SPACE COMPACT CASCADE（第一步整理資料，并不會降低高水位線，可以在線進行操作）;
   ALTER TABLE owner.table_name SHRINK SPACE CASCADE（第二步分析重置高水位線，會短暫鎖表需要在業務量小的時候進行操作）;

2. 單個磁區收縮
   磁區表的收碩訓可以磁區級別進行，具體陳述句如下：
   ALTER TABLE owner.table_name ENABLE ROW MOVEMENT;
   ALTER TABLE owner.table_name MODIFY PARTITION partition_name SHRINK SPACE COMPACT CASCADE;
   ALTER TABLE owner.table_name MODIFY PARTITION partition_name SHRINK SPACE CASCADE;
   ALTER INDEX owner.index_name MODIFY PARTITION partition_name SHRINK SPACE COMPACT CASCADE;
   ALTER INDEX owner.index_name MODIFY PARTITION partition_name SHRINK SPACE CASCADE;

四、在線表重定義步驟：

1、確認表是否可以做在線重定義：
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname => 'OWNER',
tname => 'ORIGTABLENAME',
options_flag => dbms_redefinition.cons_use_pk);
END;
/
--如果有主鍵則是options_flag => DBMS_REDEFINITION.cons_use_pk，如果沒有DBMS_REDEFINITION.cons_use_rowid

2、創建新的中間表TABLENAME_TMP準備重定義 (可以新增洗掉欄位、可以修改表存盤引數、可以修改為磁區表等需要的操作)
注意因為在線重定義程序中要求列的屬性要相同，因此不可使用dbms_redefinition完成列型別的調整

--普通表
CREATE TABLE OWNER.TABLENAME_TMP ( ) TABLESPACE XXX;
--磁區表
CREATE TABLE OWNER.TABLENAME_TMP ( )
PARTITION BY RANGE (PARTITIONNAME)
(
PARTITION P1 VALUES LESS THAN ('xxx'),
...
PARTITION PMAX VALUES LESS THAN (MAXVALUE)
)
TABLESPACE XXX;

3、開啟并行提高在線重定義速度：
ALTER SESSION FORCE PARALLEL DML PARALLEL 2;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;

4、開始在線重定義：
BEGIN
dbms_redefinition.start_redef_table(
uname => 'OWNER',
orig_table => 'ORIGTABLENAME',
int_table => 'TABLENAME_TMP',
options_flag => dbms_redefinition.cons_use_pk);
END;
/
--如果有主鍵則是options_flag => DBMS_REDEFINITION.cons_use_pk，如果沒有DBMS_REDEFINITION.cons_use_rowid

5、使用COPY_TABLE_DEPENDENTS把原始表的權限、約束、索引等在中間表上創建一份
DECLARE
num_errors PLS_INTEGER;
BEGIN
dbms_redefinition.copy_table_dependents(
uname => 'OWNER',
orig_table => 'ORIGTABLENAME',
int_table => 'TABLENAME_TMP',
copy_indexes => dbms_redefinition.cons_orig_params,
copy_triggers => true,
copy_constraints => true,
copy_privileges => true,
ignore_errors => false,
num_errors => num_errors,
copy_statistics => true);
END;
/

6、假如在線重定義要很久，這期間應用往源表插入資料，中間表并不會有這條資料，使用sync_interim_table包同步在線重定義期間源表所有的DML的資料：
BEGIN
dbms_redefinition.sync_interim_table(
uname => 'OWNER',
orig_table => 'ORIGTABLENAME',
int_table => 'TABLENAME_TMP');
END;
/

7、完成在線重定義：
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'OWNER',
orig_table => 'ORIGTABLENAME',
int_table => 'TABLENAME_TMP');
END;
/

9、中間重定義報錯時需要執行以下命令終止重定義：
*BEGIN
*DBMS_REDEFINITION.ABORT_REDEF_TABLE(
*uname => 'OWNER',
*orig_table => 'ORIGTABLENAME',
*int_table => 'TABLENAME_TMP');
*END;
*/

10、確定資料索引等同步成功后，洗掉中間表：
drop table OWNER.TABLENAME_TMP;

標籤：Oracle

上一篇：VMware安裝后網路配接器中沒有VMnet1和VMnet8

下一篇：qiankun微前端學習