[20191129]ALTER TABLE MINIMIZE RECORDS_PER_BLOCK.txt

lfree發表於2019-12-02

[20191129]ALTER TABLE MINIMIZE RECORDS_PER_BLOCK.txt

--//前幾天別人問的問題,以前為了避免行遷移使用ALTER TABLE <table_name> MINIMIZE RECORDS_PER_BLOCK設定每塊的最大行號,
--//使用impdp/expdp遷移時如何保留這些特性.實際上我記憶裡是不行的,也就是匯入的表必須在原系統上按照原來的方式重建.
--//也就是先插入要求的記錄到資料塊中,然後執行ALTER TABLE <table_name> MINIMIZE RECORDS_PER_BLOCK.

--//查詢oraus.msg檔案,發現如下:
$ cat ooerr
#! /bin/bash
/bin/grep "^[0-9][0-9][0-9][0-9][0-9]"  $ORACLE_HOME/rdbms/mesg/oraus.msg | grep -i $1

$ ooerr "hakan factor"
14529, 00000, "copy hakan factor during ctas at facilitate exchange partition"
14643, 00000, "Hakan factor mismatch for tables in ALTER TABLE EXCHANGE PARTITION"
44406, 0000, "preserve HAKAN factor for ALTER TABLE ADD CONSTRAINT"

$ oerr ora 14529
14529, 00000, "copy hakan factor during ctas at facilitate exchange partition"
// *Document: NO
// *Cause: Oracle internal testing only
// *Action: Never set this event unless running into bug 3747472

$ oerr ora 44406
44406, 0000, "preserve HAKAN factor for ALTER TABLE ADD CONSTRAINT"
// *Document: NO
// *Cause:    An internal event was triggered during the TTS import as part
//            of the fix for bug 7251049. This event use to preserve HAKAN
//            factor for ALTER TABLE ADD CONSTRAINT.
// *Action:   N/A

--//僅僅設定14529事件可以實現ctas儲存這個屬性,也不能滿足對方要求,不過還是測試看看:

1.環境:
SYS@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試:
SCOTT@book> create table t as select rownum id  from dual connect by level<=4;
Table created.

SCOTT@book> alter table t minimize records_per_block;
Table altered.
--//這樣修改表屬性

SCOTT@book> select * from sys.tab$ where (obj#, dataobj#) in (select object_id, data_object_id from dba_objects where owner = 'SCOTT' and object_name = 'T');
  2  @ prxx
==============================
OBJ#                          : 1133544860
DATAOBJ#                      : 1133544860
TS#                           : 4
FILE#                         : 4
BLOCK#                        : 1050
BOBJ#                         :
TAB#                          :
COLS                          : 1
CLUCOLS                       :
PCTFREE$                      : 10
PCTUSED$                      : 40
INITRANS                      : 1
MAXTRANS                      : 255
FLAGS                         : 1073741825
AUDIT$                        : --------------------------------------
ROWCNT                        :
BLKCNT                        :
EMPCNT                        :
AVGSPC                        :
CHNCNT                        :
AVGRLN                        :
AVGSPC_FLB                    :
FLBCNT                        :
ANALYZETIME                   :
SAMPLESIZE                    :
DEGREE                        :
INSTANCES                     :
INTCOLS                       : 1
KERNELCOLS                    : 1
PROPERTY                      : 536870912
TRIGFLAG                      : 0
SPARE1                        : 32771
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SPARE2                        :
SPARE3                        :
SPARE4                        :
SPARE5                        :
SPARE6                        : 2019-11-29 08:22:50
PL/SQL procedure successfully completed.

--//記錄在spare1中SPARE1-32768 = 32771-32768 = 3. 最大行號是3(從0開始),也就是每塊容納4條記錄.

SCOTT@book> alter session set events '14529 level 1';
Session altered.

SCOTT@book> create table t1 as select * from t where 1=0;
Table created.

SCOTT@book> alter session set events '14529 off';
Session altered.

SCOTT@book> select * from sys.tab$ where (obj#, dataobj#) in (select object_id, data_object_id from dba_objects where owner = 'SCOTT' and object_name = 'T1')
  2  @ prxx
==============================
OBJ#                          : 1133544862
DATAOBJ#                      : 1133544862
TS#                           : 4
FILE#                         : 0
BLOCK#                        : 0
BOBJ#                         :
TAB#                          :
COLS                          : 1
CLUCOLS                       :
PCTFREE$                      : 10
PCTUSED$                      : 40
INITRANS                      : 1
MAXTRANS                      : 255
FLAGS                         : 1073741825
AUDIT$                        : --------------------------------------
ROWCNT                        :
BLKCNT                        :
EMPCNT                        :
AVGSPC                        :
CHNCNT                        :
AVGRLN                        :
AVGSPC_FLB                    :
FLBCNT                        :
ANALYZETIME                   :
SAMPLESIZE                    :
DEGREE                        :
INSTANCES                     :
INTCOLS                       : 1
KERNELCOLS                    : 1
PROPERTY                      : 17716740096
TRIGFLAG                      : 0
SPARE1                        : 3
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SPARE2                        :
SPARE3                        :
SPARE4                        :
SPARE5                        :
SPARE6                        : 2019-11-29 08:31:10
PL/SQL procedure successfully completed.
--//昏,spare1=3.看看是否可以.

SCOTT@book> insert into t1  select rownum  from dual connect by level<=10;
10 rows created.

SCOTT@book> commit ;
Commit complete.

3.檢查是否有效:
SCOTT@book> select rowid,id from t1 ;
ROWID                      ID
------------------ ----------
BDkIWeAAEAAAAQkAAA          1
BDkIWeAAEAAAAQkAAB          2
BDkIWeAAEAAAAQkAAC          3
BDkIWeAAEAAAAQkAAD          4
BDkIWeAAEAAAAQkAAE          5
BDkIWeAAEAAAAQkAAF          6
BDkIWeAAEAAAAQkAAG          7
BDkIWeAAEAAAAQkAAH          8
BDkIWeAAEAAAAQkAAI          9
BDkIWeAAEAAAAQkAAJ         10
10 rows selected.
--//不行.

SCOTT@book> delete from t1 ;
10 rows deleted.

SCOTT@book> commit ;
Commit complete.

--//人為設定看看.
SYS@book> update sys.tab$  set spare1 = 32768+4-1 where (obj#, dataobj#) in (select object_id, data_object_id from dba_objects where owner = 'SCOTT' and object_name = 'T1');
1 row updated.

SYS@book> commit;
Commit complete.

SCOTT@book> alter system flush shared_pool ;
System altered.

SCOTT@book> alter system flush shared_pool ;
System altered.
--//注這樣操作一定不要忘記重新整理共享池.

SCOTT@book> select spare1 from sys.tab$ where (obj#, dataobj#) in (select object_id, data_object_id from dba_objects where owner = 'SCOTT' and object_name = 'T1');
    SPARE1
----------
     32771

SCOTT@book> insert into t1  select rownum  from dual connect by level<=10;
10 rows created.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> select rowid,id from t1 ;
ROWID                      ID
------------------ ----------
BDkIWeAAEAAAAQkAAA          1
BDkIWeAAEAAAAQkAAB          2
BDkIWeAAEAAAAQkAAC          3
BDkIWeAAEAAAAQkAAD          4
BDkIWeAAEAAAAQlAAA          5
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
BDkIWeAAEAAAAQlAAB          6
BDkIWeAAEAAAAQlAAC          7
BDkIWeAAEAAAAQlAAD          8
BDkIWeAAEAAAAQmAAA          9
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
BDkIWeAAEAAAAQmAAB         10
10 rows selected.

--//很明顯這樣可以實現每塊4條記錄,不過這種修改tab$的方式存在一定的風險屬於oracle不推薦的方式.千萬不要在生產系統使用!!
--//我這裡記錄了我以前的一次危險操作.連結:http://blog.itpub.net/267265/viewspace-763315/
--//總之沒有什麼好方法.保險起見還是先匯入需要的資料記錄,alter table t minimize records_per_block;刪除再匯入,總之這樣操作有點煩!!
--//或者採用線上重定義的方式,只要新表是具有這樣屬性的表就可以現實.


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2666411/,如需轉載,請註明出處,否則將追究法律責任。

相關文章