[20191129]ALTER TABLE MINIMIZE RECORDS_PER_BLOCK.txt

[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;删除再导入,总之这样操作有点烦!! --//或者采用在线重定义的方式,只要新表是具有这样属性的表就可以现实.