[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;刪除再匯入,總之這樣操作有點煩!!
--//或者採用線上重定義的方式,只要新表是具有這樣屬性的表就可以現實.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2666411/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql的ALTER TABLE命令MySql
- alter table set unused column
- create table，show tables，describe table，DROP TABLE，ALTER TABLE ，怎麼使用？
- alter table move與shrink space
- alter table nologging /*+APPEND PARALLEL(n)*/APPParallel
- alter table drop unused columns checkpoint
- ALTER TABLE修改列的不同方法
- MySQL-ALTER TABLE命令學習[20180503]MySql
- MySQL alter table時執行innobackupex全備再看Seconds_Behind_MasterMySqlAST
- 透過alter table 來實現重建表，同事大呼開眼界了
- MySQL中的alter table命令的基本使用方法及提速最佳化MySql
- Oracle 12.2之後ALTER TABLE .. MODIFY轉換非分割槽表為分割槽表Oracle
- [ABC330F] Minimize Bounding Square
- 題解：CF1918B Minimize Inversions
- [20191129]關於hugepages的問題.txt
- SQL__ALTERSQL
- MySQL ALTER命令MySql
- [20191129]oracle Audit檔案管理3.txtOracle
- 線上改表工具oak-online-alter-table和pt-online-schema-change的使用限制總結
- mysql加快alter操作MySql
- №20191129簡易操作1~7(今期：上=下=-3)
- alter database disable thread 2Databasethread
- [20191129]OER 7451 in Load Indicator Error Code = OSD-04500.txtIndicatorError
- CodeForces - 960B：Minimize the error（優先佇列+貪心）Error佇列
- ALTER SYSTEM FLUSH BUFFER_POOL
- [重慶思莊每日技術分享]-在為表新增了列後執行ALTER TABLE SHRINK SPACE 提示ORA-8102
- 如何獲取 alter 裡面的字串？字串
- 開心檔之MySQL ALTER命令MySql
- 【SQL】16 SQL CREATE INDEX 語句、 撤銷索引、撤銷表以及撤銷資料庫、ALTER TABLE 語句、AUTO INCREMENT 欄位SQLIndex索引資料庫REM
- mysql alter modify 和 change的區別MySql
- alter tablespace ts_name autoextend_clause
- MySQL 資料庫 ALTER命令講解MySql資料庫
- MySQL alter 新增列對dml影響MySql
- table
- mysql表操作(alter)/mysql欄位型別MySql型別
- ORA-279 signalled during: alter database recover logfileDatabase
- 2.7.6.2.1 ALTER SYSTEM SET語句中的SCOPE子句
- MySQL:Analyze table導致'waiting for table flush'MySqlAI