OracleとMySQLのalter table column動作の違い（データがある場合）
OracleとMySQLを使用しているとカラム変更の動作がいろいろと違ってくるので、
どっちがどっちかわからなくなることか多いのでまとめ。
OracleDatabaseSE(11.2.0.4)とMySQL5.6.29（INNODB）で調査。
1. 実行可能なものは（可）
2. 実行が即時で終了するものは（即時）
3. 実行不可のものは（不可）
両DB共に以下のようなテーブルを用意した。
>desc test 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(38) ID2 NUMBER(38) TX VARCHAR2(50) > select count(*) from test; +----------+ | count(*) | +----------+ | 1600000 | +----------+ 1 row in set (0.63 sec)
ADD COLUMNS
制約なしのカラム追加
Oracle:即時 MySQL：可
# Oracle ALTER TABLE test ADD id3 int; 経過: 00:00:00.00 # MySQL ALTER TABLE test ADD id3 int; Query OK, 0 rows affected (12.50 sec)
デフォルト句のカラム追加
Oracle:可 MySQL：可
# Oracle ALTER TABLE test ADD id3 int default '3'; 経過: 00:00:29.65 # MySQL ALTER TABLE test ADD id3 int default '3'; Query OK, 0 rows affected (19.67 sec)
デフォルト句+NOTNULL制約のカラム追加
Oracle:即時 MySQL：可
# Oracle ALTER TABLE test ADD id3 int not null default '3'; 経過: 00:00:00.04 # MySQL ALTER TABLE test ADD id3 int not null default '3'; Query OK, 0 rows affected (16.56 sec)
MODIFY COLUMNS
カラムのデータ型変更
Oracle:不可 MySQL：可
# Oracle ALTER TABLE test MODIFY id2 varchar2(100); 行1でエラーが発生しました。: ORA-01439: データ型を変更するには、修正する列を空にする必要があります # MySQL ALTER TABLE test MODIFY id2 varchar(100); Query OK, 1600000 rows affected (17.75 sec)
カラムデータサイズ拡大
Oracle:即時 MySQL：可
# Oracle ALTER TABLE test MODIFY id2 varchar2(1000); 経過: 00:00:00.03 # MySQL ALTER TABLE test MODIFY id2 varchar(1000); Query OK, 1600000 rows affected (20.48 sec)
カラムデータサイズ縮小
Oracle:即時また不可 MySQL：可
Oracleの場合は数値の縮小は対象の列を空にしなくてはいけない。
# Oracle ALTER TABLE test MODIFY id2 varchar2(50); 経過: 00:00:00.23 ALTER TABLE test MODIFY numbercol number(7); ORA-01440: 精度またはスケールを下げるには、修正する列を空にする必要があります # MySQL ALTER TABLE test MODIFY id2 varchar(50); Query OK, 1600000 rows affected (22.79 sec)
カラムデータサイズ縮小:サイズオーバーする値がある場合
Oracle:不可 MySQL：可
MySQLの場合、varchar(1)に指定すると先頭の1文字以降切り捨てる。
# Oracle ALTER TABLE test MODIFY id2 varchar2(1); ORA-01441: 大きすぎる値があるため、列の長さを減らせません。 経過: 00:00:00.17 # MySQL ALTER TABLE test MODIFY id2 varchar(1); Query OK, 1600000 rows affected, 65535 warnings (19.60 sec) Warning (Code 1265): Data truncated for column 'id2' at row 1 Warning (Code 1265): Data truncated for column 'id2' at row 2
RENAME COLUMNS
カラム名の変更
Oracle:即時 MySQL：即時
OracleもMySQLも即時で完了する。 ただし、MySQLは指定するカラムのデータ型とデータサイズを同じにした場合。
# Oracle ALTER TABLE test RENAME COLUMN id2 TO xid2; 経過: 00:00:00.02 # MySQL ALTER TABLE test CHANGE COLUMN id2 xid2 varchar(1); Query OK, 0 rows affected (0.06 sec)
DROP COLUMNS
データありのカラム削除
Oracle:可 MySQL：可
# Oracle ALTER TABLE test DROP COLUMN xid2; 経過: 00:00:16.90 # MySQL ALTER TABLE test DROP COLUMN xid2; Query OK, 0 rows affected (19.96 sec)
データなしのカラム削除
Oracle:可（早い） MySQL：可
# Oracle ALTER TABLE test DROP COLUMN nullcol; 経過: 00:00:03.29 # MySQL ALTER TABLE test DROP COLUMN nullcol; Query OK, 0 rows affected (19.51 sec)
Oracle-semi-onlineDDL作成した
結構前にJPOUGでLTしたOracleDatabaseセミオンラインDDLを作成しました。
ベンチマークツールのswingbenchで試したりしたのですが、たぶん動くはず。。。
OracleDatabase11gR2に対応。
仕組みは以下を参照
kenken0807.hatenablog.com
できることとできないこと
できること
カラムの追加(add)、変更(modify)や削除(drop)ができる
- 例えばnumber型からvarchar2型へ変更する際はテーブルが空でないといけないがデータがあってもできる
インデックスの追加
インデックスの削除
できないこと
カラム名の変更(RENAME)
対象のテーブルのPKが外部参照されている場合、実行不可
- 外部参照しているテーブルのForeign Keyを削除またはdisableにしてあげる必要がある。
注意点
対象のテーブルが外部参照している場合、
--disable-foreignkeyで新規作成されるテーブルの外部参照はdisableになるが 既存のテーブルは外部参照したままなのでテーブルをrenameする際に待たされる場合がある。セミオンラインDDLを実行する前に外部参照をdisableしてあげたほうが良い。PK必須
データ容量が対象のテーブルの約３倍使用する
オプション
$ perl oracle-semi-onlineDDL.pl
16:27:14 :[ERROR] Check Options
Usage: perl oracle-semi-onlineDDL.pl [options]
--dryrun Create and alter the new table, but do not copy table[default none(MUST choose --dryrun or --execute)]
--execute Create and alter the new table and DO copy table[default none(MUST choose --dryrun or --execute)]
Connect OPTIONS:
--db SID[dafault none]
--user username[default none]
--password user's password[default none]
--host hostname or IP[default localhost]
--port listener port[default 1521]
--table tablename [dafault none]
CREATE NEW INDEX:
--idx [create index statement] the tablename should be "--TABLENAME--"
ex. --idx "create index TEXT_IDX on --TABLENAME-- (col1,col2) tablespace test_ts"
ex. --idx "create index TEXT_IDX2 on --TABLENAME-- (col2 desc) tablespace test_ts"
DROP INDEX:
--dropidx [INDEX NAME] Drop Index
ex. --dropidx "IDX_TEST"
REBUILD INDEX:
--rebuild REcreate Table and Indexes
ALTER TABLE:
--alter [alter statement after ALTER TABLE XXXX]
ex. --alter "drop (text_col)"
ex. --alter "modify id varchar2(10) not null"
OTHERS:
--auto Rename Table Auto[default none]
--disable-foreignkey The Option is to disable the foreign key that has the target table.After finish to alter table,the foreign key is still disable.
実行
swingbenchで実行中に試してみた。
テーブル情報
約3Gのテーブル
>desc ORDERS
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ORDER_ID NOT NULL NUMBER(12)
ORDER_DATE NOT NULL TIMESTAMP(6) WITH LOCAL TIMEZONE
ORDER_MODE VARCHAR2(8)
CUSTOMER_ID NOT NULL NUMBER(12)
ORDER_STATUS NUMBER(2)
ORDER_TOTAL NUMBER(8,2)
SALES_REP_ID NUMBER(6)
PROMOTION_ID NUMBER(6)
WAREHOUSE_ID NUMBER(6)
DELIVERY_TYPE VARCHAR2(15)
COST_OF_DELIVERY NUMBER(6)
WAIT_TILL_ALL_AVAILABLE VARCHAR2(15)
DELIVERY_ADDRESS_ID NUMBER(12)
CUSTOMER_CLASS VARCHAR2(30)
CARD_ID NUMBER(12)
INVOICE_ADDRESS_ID NUMBER(12)
>select count(*) from ORDERS;
COUNT(*)
----------
14297900
Alter Tableしたい内容
PROMOTION_IDNUMBER(6)→PROMOTION_IDVARCHAR(6)へ変更DELIVERY_ADDRESS_IDにインデックスを追加する
セミオンラインDDLをまずドライランで実行
$ perl oracle-semi-onlineDDL.pl
--db orcl \
--user soe \
--password soe \
--table ORDERS \
--idx "create index IDX_ORDERS on --TABLENAME-- (DELIVERY_ADDRESS_ID)" \
--alter "modify PROMOTION_ID VARCHAR(6)" \
--dryrun
17:18:52 :[WARNING]Stop alter table because ORDERS has Foreign Key(ORDERS_CUSTOMER_ID_FK).
17:18:52 :You need --disable-foreignkey option.
17:18:52 :Recommend to disable or drop Foreign key(ORDERS_CUSTOMER_ID_FK) first if alter table
Foreign Keyが設定されいるため、実行できない。
Foreign Keyをdisableにして、--disable-foreignkeyを指定して実行
>alter table ORDERS modify constraint ORDERS_CUSTOMER_ID_FK disable;
表が変更されました。
$ perl oracle-semi-onlineDDL.pl
--db orcl \
--user soe \
--password soe \
--table ORDERS \
--idx "create index IDX_ORDERS on --TABLENAME-- (DELIVERY_ADDRESS_ID)" \
--alter "modify PROMOTION_ID VARCHAR(6)" \
--dryrun \
--disable-foreignkey
ドライランで実行されるSQLなどが表示される。
17:27:33 :Tablename(ORDERS) NewTablename(NEW__ORDERS) OldTablename(OLD__ORDERS) Mvname(MV__ORDERS) Triggername(TRG__ORDERS)
17:27:33 :PK (ORDER_PK) is not used by Foreign Key
----------------------------------------------------------
[DEBUG]
CREATE INDEX "SOE"."NEW__ORD_SALES_REP_IX" ON "SOE"."NEW__ORDERS" ("SALES_REP_ID") REVERSE
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
TABLESPACE "SOE"
PARALLEL 48
----------------------------------------------------------
17:27:47 :idx(ORD_SALES_REP_IX) newidx(NEW__ORD_SALES_REP_IX) oldidx(OLD__ORD_SALES_REP_IX)
----------------------------------------------------------
[DEBUG]
CREATE INDEX "SOE"."NEW__ORD_ORDER_DATE_IX" ON "SOE"."NEW__ORDERS" ("ORDER_DATE") REVERSE
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
TABLESPACE "SOE"
PARALLEL 48
----------------------------------------------------------
17:27:47 :idx(ORD_ORDER_DATE_IX) newidx(NEW__ORD_ORDER_DATE_IX) oldidx(OLD__ORD_ORDER_DATE_IX)
----------------------------------------------------------
[DEBUG]
CREATE INDEX "SOE"."NEW__ORD_WAREHOUSE_IX" ON "SOE"."NEW__ORDERS" ("WAREHOUSE_ID", "ORDER_STATUS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
TABLESPACE "SOE"
PARALLEL 48
----------------------------------------------------------
17:27:47 :idx(ORD_WAREHOUSE_IX) newidx(NEW__ORD_WAREHOUSE_IX) oldidx(OLD__ORD_WAREHOUSE_IX)
----------------------------------------------------------
[DEBUG]
CREATE INDEX "SOE"."NEW__ORD_CUSTOMER_IX" ON "SOE"."NEW__ORDERS" ("CUSTOMER_ID") REVERSE
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
TABLESPACE "SOE"
PARALLEL 48
----------------------------------------------------------
17:27:47 :idx(ORD_CUSTOMER_IX) newidx(NEW__ORD_CUSTOMER_IX) oldidx(OLD__ORD_CUSTOMER_IX)
17:27:50 :DIABLE FOREIGN KEY(NEW__ORDERS_CUSTOMER_ID_FK)
----------------------------------------------------------
[DEBUG]
CREATE TABLE "SOE"."NEW__ORDERS"
( "ORDER_ID" NUMBER(12,0) CONSTRAINT "NEW__ORDER_ORDER_ID_NN" NOT NULL ENABLE,
"ORDER_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE CONSTRAINT "NEW__ORDER_DATE_NN" NOT NULL ENABLE,
"ORDER_MODE" VARCHAR2(8),
"CUSTOMER_ID" NUMBER(12,0) CONSTRAINT "NEW__ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE,
"ORDER_STATUS" NUMBER(2,0),
"ORDER_TOTAL" NUMBER(8,2),
"SALES_REP_ID" NUMBER(6,0),
"PROMOTION_ID" NUMBER(6,0),
"WAREHOUSE_ID" NUMBER(6,0),
"DELIVERY_TYPE" VARCHAR2(15),
"COST_OF_DELIVERY" NUMBER(6,0),
"WAIT_TILL_ALL_AVAILABLE" VARCHAR2(15),
"DELIVERY_ADDRESS_ID" NUMBER(12,0),
"CUSTOMER_CLASS" VARCHAR2(30),
"CARD_ID" NUMBER(12,0),
"INVOICE_ADDRESS_ID" NUMBER(12,0),
CONSTRAINT "NEW__ORDER_PK" PRIMARY KEY ("ORDER_ID")
USING INDEX REVERSE PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
TABLESPACE "SOE" ENABLE NOVALIDATE,
CONSTRAINT "NEW__ORDER_MODE_LOV" CHECK (order_mode in ('direct','online')) DEFERRABLE ENABLE NOVALIDATE,
CONSTRAINT "NEW__ORDER_TOTAL_MIN" CHECK (order_total >= 0) DEFERRABLE ENABLE NOVALIDATE,
CONSTRAINT "NEW__ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
REFERENCES "SOE"."CUSTOMERS" ("CUSTOMER_ID") ON DELETE SET NULL DISABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 16 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "SOE"
----------------------------------------------------------
17:27:50 :Create Table (NEW__ORDERS)
----------------------------------------------------------
[DEBUG]
ALTER TABLE NEW__ORDERS MODIFY PROMOTION_ID VARCHAR(6)
----------------------------------------------------------
17:27:51 :ALTER (NEW__ORDERS) (ALTER TABLE NEW__ORDERS MODIFY PROMOTION_ID VARCHAR(6))
17:27:51 :Create Index (NEW__ORD_WAREHOUSE_IX)
17:27:51 :Create Index (NEW__ORD_CUSTOMER_IX)
17:27:51 :Create Index (NEW__ORD_SALES_REP_IX)
17:27:51 :Create Index (NEW__ORD_ORDER_DATE_IX)
17:27:51 :>>>>>CREATE NEW INDEX(create index IDX_ORDERS on NEW__ORDERS (DELIVERY_ADDRESS_ID))
17:27:51 :Create MVLOG (ORDERS)
----------------------------------------------------------
[DEBUG]
create materialized view log on ORDERS NOLOGGING
----------------------------------------------------------
17:27:53 :[DRY-RUN] Drop MVLOG(ORDERS)
17:27:54 :Create Mview (MV__ORDERS)
----------------------------------------------------------
[DEBUG]
create materialized view MV__ORDERS NOLOGGING as select ORDER_ID,ORDER_DATE,ORDER_MODE,CUSTOMER_ID,ORDER_STATUS,ORDER_TOTAL,SALES_REP_ID,PROMOTION_ID,WAREHOUSE_ID,DELIVERY_TYPE,COST_OF_DELIVERY,WAIT_TILL_ALL_AVAILABLE,DELIVERY_ADDRESS_ID,CUSTOMER_CLASS,CARD_ID,INVOICE_ADDRESS_ID from ORDERS where rownum < 2
----------------------------------------------------------
----------------------------------------------------------
[DEBUG]
insert /*+ APPEND */ into NEW__ORDERS(ORDER_ID,ORDER_DATE,ORDER_MODE,CUSTOMER_ID,ORDER_STATUS,ORDER_TOTAL,SALES_REP_ID,PROMOTION_ID,WAREHOUSE_ID,DELIVERY_TYPE,COST_OF_DELIVERY,WAIT_TILL_ALL_AVAILABLE,DELIVERY_ADDRESS_ID,CUSTOMER_CLASS,CARD_ID,INVOICE_ADDRESS_ID) select ORDER_ID,ORDER_DATE,ORDER_MODE,CUSTOMER_ID,ORDER_STATUS,ORDER_TOTAL,SALES_REP_ID,PROMOTION_ID,WAREHOUSE_ID,DELIVERY_TYPE,COST_OF_DELIVERY,WAIT_TILL_ALL_AVAILABLE,DELIVERY_ADDRESS_ID,CUSTOMER_CLASS,CARD_ID,INVOICE_ADDRESS_ID from MV__ORDERS where rownum < 2
----------------------------------------------------------
17:27:56 :[DRY-RUN]COPY TABLE (MV__ORDERS to NEW__ORDERS) 1 row
----------------------------------------------------------
[DEBUG]
create or replace trigger TRG__ORDERS
after insert or delete or update on MV__ORDERS for each row
begin
IF (INSERTING) THEN
INSERT INTO NEW__ORDERS (ORDER_ID,ORDER_DATE,ORDER_MODE,CUSTOMER_ID,ORDER_STATUS,ORDER_TOTAL,SALES_REP_ID,PROMOTION_ID,WAREHOUSE_ID,DELIVERY_TYPE,COST_OF_DELIVERY,WAIT_TILL_ALL_AVAILABLE,DELIVERY_ADDRESS_ID,CUSTOMER_CLASS,CARD_ID,INVOICE_ADDRESS_ID) VALUES ( :NEW.ORDER_ID , :NEW.ORDER_DATE , :NEW.ORDER_MODE , :NEW.CUSTOMER_ID , :NEW.ORDER_STATUS , :NEW.ORDER_TOTAL , :NEW.SALES_REP_ID , :NEW.PROMOTION_ID , :NEW.WAREHOUSE_ID , :NEW.DELIVERY_TYPE , :NEW.COST_OF_DELIVERY , :NEW.WAIT_TILL_ALL_AVAILABLE , :NEW.DELIVERY_ADDRESS_ID , :NEW.CUSTOMER_CLASS , :NEW.CARD_ID , :NEW.INVOICE_ADDRESS_ID );
END IF;
IF (UPDATING) THEN
UPDATE NEW__ORDERS SET CARD_ID = :NEW.CARD_ID , ORDER_STATUS = :NEW.ORDER_STATUS , WAIT_TILL_ALL_AVAILABLE = :NEW.WAIT_TILL_ALL_AVAILABLE , ORDER_TOTAL = :NEW.ORDER_TOTAL , ORDER_MODE = :NEW.ORDER_MODE , ORDER_DATE = :NEW.ORDER_DATE , SALES_REP_ID = :NEW.SALES_REP_ID , PROMOTION_ID = :NEW.PROMOTION_ID , CUSTOMER_CLASS = :NEW.CUSTOMER_CLASS , ORDER_ID = :NEW.ORDER_ID , DELIVERY_ADDRESS_ID = :NEW.DELIVERY_ADDRESS_ID , CUSTOMER_ID = :NEW.CUSTOMER_ID , WAREHOUSE_ID = :NEW.WAREHOUSE_ID , INVOICE_ADDRESS_ID = :NEW.INVOICE_ADDRESS_ID , DELIVERY_TYPE = :NEW.DELIVERY_TYPE , COST_OF_DELIVERY = :NEW.COST_OF_DELIVERY WHERE ORDER_ID = :OLD.ORDER_ID ;
END IF;
IF (DELETING) THEN
DELETE FROM NEW__ORDERS WHERE ORDER_ID = :OLD.ORDER_ID ;
END IF;
end;
----------------------------------------------------------
17:27:56 :Create Trigger (TRG__ORDERS)
17:27:57 :Drop Trigger (TRG__ORDERS)
17:27:57 :Drop Mview (MV__ORDERS)
17:27:58 :Drop Table NEW__ORDERS
17:27:58 :[DRY-RUN] complete, Does not alter
セミオンラインDDL実行
ドライランで問題なければ、executeする。
今回はIO性能が低いもので行ったので時間がかかった
$ perl oracle-semi-onlineDDL.pl
--db orcl \
--user soe \
--password soe \
--table ORDERS \
--idx "create index IDX_ORDERS on --TABLENAME-- (DELIVERY_ADDRESS_ID)" \
--alter "modify PROMOTION_ID VARCHAR(6)" \
--execute \
--disable-foreignkey
17:32:27 :Tablename(ORDERS) NewTablename(NEW__ORDERS) OldTablename(OLD__ORDERS) Mvname(MV__ORDERS) Triggername(TRG__ORDERS)
17:32:27 :PK (ORDER_PK) is not used by Foreign Key
17:32:32 :idx(ORD_SALES_REP_IX) newidx(NEW__ORD_SALES_REP_IX) oldidx(OLD__ORD_SALES_REP_IX)
17:32:32 :idx(ORD_ORDER_DATE_IX) newidx(NEW__ORD_ORDER_DATE_IX) oldidx(OLD__ORD_ORDER_DATE_IX)
17:32:32 :idx(ORD_WAREHOUSE_IX) newidx(NEW__ORD_WAREHOUSE_IX) oldidx(OLD__ORD_WAREHOUSE_IX)
17:32:32 :idx(ORD_CUSTOMER_IX) newidx(NEW__ORD_CUSTOMER_IX) oldidx(OLD__ORD_CUSTOMER_IX)
17:32:32 :DIABLE FOREIGN KEY(NEW__ORDERS_CUSTOMER_ID_FK)
17:32:32 :Create Table (NEW__ORDERS)
17:32:32 :ALTER (NEW__ORDERS) (ALTER TABLE NEW__ORDERS MODIFY PROMOTION_ID VARCHAR(6)) ←カラム変更
17:32:33 :Create Index (NEW__ORD_WAREHOUSE_IX) ←既存インデックス
17:32:33 :Create Index (NEW__ORD_CUSTOMER_IX) ←既存インデックス
17:32:33 :Create Index (NEW__ORD_SALES_REP_IX) ←既存インデックス
17:32:33 :Create Index (NEW__ORD_ORDER_DATE_IX) ←既存インデックス
17:32:33 :>>>>>CREATE NEW INDEX(create index IDX_ORDERS on NEW__ORDERS (DELIVERY_ADDRESS_ID)) ←新規インデックス追加
17:32:33 :Create MVLOG (ORDERS) ←MVIEWログ作成
17:32:34 :Create Mview (MV__ORDERS) ←MVIEW作成
17:35:20 :Copy Table (MV__ORDERS to NEW__ORDERS) ←テーブルデータのコピーがすごく重い
18:20:00 :Create Trigger (TRG__ORDERS) ←トリガー作成
18:20:10 :Fast Refresh Start (MV__ORDERS)
18:20:31 : End
=============================================
Choose Number and Enter key
1 or Only EnterKey: Refresh Mview(MV__ORDERS)
2: Rename Table(ORDERS)
9: Cancel
=============================================
18:21:31 :Mview log cnt: 547 ←差分の更新数
18:21:31 :Fast Refresh Start (MV__ORDERS)
18:21:33 : End
=============================================
Choose Number and Enter key
1 or Only EnterKey: Refresh Mview(MV__ORDERS)
2: Rename Table(ORDERS)
9: Cancel
=============================================
1 ← 1 で再度差分の更新
18:21:35 :Mview log cnt: 54
18:21:35 :Fast Refresh Start (MV__ORDERS)
18:21:35 : End
=============================================
Choose Number and Enter key
1 or Only EnterKey: Refresh Mview(MV__ORDERS)
2: Rename Table(ORDERS)
9: Cancel
=============================================
2 ← 2 でテーブルの入れ替えを実施
18:21:36 :Fast Refresh Start (MV__ORDERS)
18:21:36 : End
18:21:37 :>>>Read Only Table (ORDERS) ←ここから
18:21:38 :Fast Refresh Start (MV__ORDERS) ｜
18:21:38 : End ｜
18:21:38 :Drop Mview log(ORDERS) ｜約４秒ほどアクセスできない時間が発生。
18:21:41 :Rename Table (ORDERS to OLD__ORDERS) ｜差分の更新が多いとMview logの削除に時間がかかる模様
18:21:41 :Rename Table (NEW__ORDERS to ORDERS) ｜
18:21:41 :>>>Finish ReadOnly ←ここまで
18:21:42 :Drop Trigger (TRG__ORDERS)
18:21:42 :Drop Mview (MV__ORDERS)
18:21:43 :Drop Table (OLD__ORDERS)
18:21:43 :Finish Completely
18:21:43 :You need to rename CONSTRAINT_NAME and INDEX_NAME
というように最小限の停止時間でDDLが可能となる。
Mviewの作成とテーブルデータコピーが非常に重いため、IO負荷が高いので注意。
Oracle 別スキーマ間でFAST REFRESH ON COMMITモードのマテリアライズドビューを作成する
スキーマMVIEW_MOTOのテーブルTESTに対して
スキーマMVIEW_SAKIにFAST REFRESH ON COMMITモードのマテリアライズドビューを作成する
MVIEW_MOTOにテーブルとマテリアライズドビューログの作成
CREATE TABLE test (id NUMBER,id2 NUMBER,del_flg NUMBER,CONSTRAINT test_pk PRIMARY KEY(id)); CREATE MATERIALIZED VIEW LOG ON test;
権限の付与
MVIEW_SAKIにMVIEW_MOTOのtestテーブルとマテビューログに対してのSELECT権限を付与
GRANT SELECT ON mview_moto.test TO mview_saki; GRANT SELECT ON mview_moto.MLOG$_TEST to mview_saki;
ちなみにマテビューログの名前はMLOG$_[tablename]となっている。
Oracleの場合オブジェクトの名称は３０バイトまでなので３０バイトのテーブル名称は以下のようになるみたい。
→２０バイトで切られる、または２０バイト切った際に同様のMLOG名称がある場合は末尾に数値がつく
create table abcdefghijklmnopqrstuvwxyzabcD(id int primary key); create materialized view log on abcdefghijklmnopqrstuvwxyzabcD; → MLOG$_ABCDEFGHIJKLMNOPQRST create table abcdefghijklmnopqrstuvwxyzabcE(id int primary key); create materialized view log on abcdefghijklmnopqrstuvwxyzabcE; → MLOG$_ABCDEFGHIJKLMNOPQRST1
MVIEW_SAKIにMATERIALIZED VIEW関連の権限を付与
GRANT CREATE TABLE TO mview_saki; GRANT CREATE MATERIALIZED VIEW TO mview_saki; GRANT ON COMMIT REFRESH TO mview_saki;
MVIEW_SAKIにマテリアライズドビューの作成
del_flgが0のデータのみ取得するように作成
CREATE MATERIALIZED VIEW test REFRESH FAST ON COMMIT AS SELECT id,id2 FROM mview_moto.test WHERE del_flg=0;
確認
- FAST REFRESH ON COMMITモードのマテリアライズドビューの作成が完了。
SQL>insert into mview_moto.test values(1,1,0);
1行が作成されました。
SQL>insert into mview_moto.test values(2,1,1);
1行が作成されました。
SQL>commit;
コミットが完了しました。
SQL>select * from mview_moto.test;
ID ID2 DEL_FLG
---------- ---------- ----------
1 1 0
2 1 1
SQL>select * from mview_saki.test;
ID ID2
---------- ----------
1 1
Oracle オンラインREDOログとアーカイブログの解析ツール
前回記述したブログを元に解析ツールを作成した。
Oracle-ArchiveLog-Analyzer
MySQLのmysqlbinlogのような感じでLogMinerを使用してトランザクションごとにコミット順にテキストフォーマットで表示する。
インストール
perlスクリプトなのでDBD::Oracleをいれる cpanm DBD::Oracle git clone https://github.com/kenken0807/Oracle-ArchiveLog-Analyzer.git
使用方法
- オプション情報
$ perl Oracle-ArchiveLog-Analyzer.pl Usage: perl Oracle-ArchiveLog-Analyzer.pl [options] log-files Options: --start-position [SCN] Start reading REDO log at SCN. (START_SCN >= #) --stop-position [SCN] Stop reading REDO log at SCN. (COMMIT_SCN <= #) --start-datetime ['YYYY/MM/DD HH24:MI:SS'] Start reading REDO log at first event having a datetime equal. (START_TIMESTAMP >= 'YYYY/MM/DD HH24:MI:SS') --stop-datetime ['YYYY/MM/DD HH24:MI:SS'] Stop reading REDO log at first event having a datetime equal. (COMMIT_TIMESTAMP <= 'YYYY/MM/DD HH24:MI:SS') --select Show EXPLAIN SELECT STATEMENTS --table [TABLENAME] To extract transactions the table has been used (ex."'USER_MASTER','CODE_MASTER'") Connect Options: --host hostname [default localhost] --port port [default 1521] --sid sid [default orcl] --user user must be 'system' [default system] --pass password --checkuser FORMAT is "'username','username'...." (ex. --checkuser "'ORCLUSER','ORA'") Detail: Set log-files(archivelog or online redolog) at Current Dir or Fullpath
--start-position・・トランザクションが開始したSCNを起点
--stop-position・・トランザクションがコミットしたSCNを終点
--start-datetime・・トランザクションが開始した時間を起点
--stop-datetime・・トランザクションがコミットした時間を終点
--checkuser・・表示させたいトランザクションを実行したユーザ名を指定する
--table・・指定するとそのテーブルに対して実行された更新のみ取得
--user・・接続するユーザ名を選択（デフォルトsystem）
別ユーザで実行する場合は以下権限を付与すれば可能
GRANT SELECT ANY TRANSACTION to orauser; GRANT EXECUTE_CATALOG_ROLE to orauser; GRANT SELECT ON _v$LOGMNR_CONTENTS to orauser;
- 実行する
- 2016/02/18 14:41:27～2016/02/18 15:05:00間でORAUSERスキーマが実行した更新処理を表示する
$ perl Oracle-ArchiveLog-Analyzer.pl --sid orcl --pass xxxx \
--checkuser "'ORAUSER'" \
--start-datetime '2016/02/18 14:41:27' \
--stop-datetime '2016/02/18 15:05:00' \
/opt/app/oracle/online-redo/redo02.log
-- START_TIME: 2016/02/18 14:41:27 COMMIT_TIME: 2016/02/18 14:41:42
-- START_SCN: 47902287496 COMMIT_SCN: 47902287503
-- TRANSACTION ID: 09000700172A0600
set transaction read write;
update "ORAUSER"."TT" set "ID2" = '999' where "ID2" = '666';
commit;
-- START_TIME: 2016/02/18 15:04:43 COMMIT_TIME: 2016/02/18 15:04:43
-- START_SCN: 47902288848 COMMIT_SCN: 47902288878
-- TRANSACTION ID: 0A00120003D60B00
create table TEST (id int primary key);
-- START_TIME: 2016/02/18 15:04:53 COMMIT_TIME: 2016/02/18 15:04:53
-- START_SCN: 47902288882 COMMIT_SCN: 47902288883
-- TRANSACTION ID: 0A0006000FD70B00
set transaction read write;
insert into "ORAUSER"."TEST"("ID") values ('999');
commit;
-- START_TIME: 2016/02/18 15:05:00 COMMIT_TIME: 2016/02/18 15:05:00
-- START_SCN: 47902288895 COMMIT_SCN: 47902288924
-- TRANSACTION ID: 0700070050CD0700
truncate table TEST;
- --select をつけると実行されたDML文をSELECT文に書き換えて表示
$ perl Oracle-ArchiveLog-Analyzer.pl --sid orcl --pass xxxx \
--checkuser "'ORAUSER'" \
--start-datetime '2016/02/18 14:41:27' \
--stop-datetime '2016/02/18 15:04:53' \
--select /opt/app/oracle/online-redo/redo02.log
-- START_TIME: 2016/02/18 14:41:27 COMMIT_TIME: 2016/02/18 14:41:42
-- START_SCN: 47902287496 COMMIT_SCN: 47902287503
-- TRANSACTION ID: 09000700172A0600
set transaction read write;
update "ORAUSER"."TT" set "ID2" = '999' where "ID2" = '666';
commit;
-- EXPLAIN SELECT:
-- select count(*) from "ORAUSER"."TT" where "ID2" = '666';
-- START_TIME: 2016/02/18 15:04:43 COMMIT_TIME: 2016/02/18 15:04:43
-- START_SCN: 47902288848 COMMIT_SCN: 47902288878
-- TRANSACTION ID: 0A00120003D60B00
create table TEST (id int primary key);
-- START_TIME: 2016/02/18 15:04:53 COMMIT_TIME: 2016/02/18 15:04:53
-- START_SCN: 47902288882 COMMIT_SCN: 47902288883
-- TRANSACTION ID: 0A0006000FD70B00
set transaction read write;
insert into "ORAUSER"."TEST"("ID") values ('999');
commit;
-- EXPLAIN SELECT:
-- select count(*) from "ORAUSER"."TEST" where "ID" = '999' ;
SQL>delete from tt; 10行が削除されました。 SQL>commit;
・ 表示されるもの
-- START_TIME: 2016/02/23 17:25:57 COMMIT_TIME: 2016/02/23 17:25:57 -- START_SCN: 47902717945 COMMIT_SCN: 47902717954 -- TRANSACTION ID: 0A001E002FDC0B00 set transaction read write; delete from "ORAUSER"."TT" where "ID" = '1' and "ID2" = '1'; delete from "ORAUSER"."TT" where "ID" = '2' and "ID2" = '2'; delete from "ORAUSER"."TT" where "ID" = '3' and "ID2" = '3'; delete from "ORAUSER"."TT" where "ID" = '4' and "ID2" = '4'; delete from "ORAUSER"."TT" where "ID" = '5' and "ID2" = '5'; delete from "ORAUSER"."TT" where "ID" = '6' and "ID2" = '6'; delete from "ORAUSER"."TT" where "ID" = '7' and "ID2" = '7'; delete from "ORAUSER"."TT" where "ID" = '8' and "ID2" = '8'; delete from "ORAUSER"."TT" where "ID" = '9' and "ID2" = '9'; delete from "ORAUSER"."TT" where "ID" = '10' and "ID2" = '10'; commit;
これで調査するのには捗りそう。
これで独自のレプリケーション機能でも作ろうかな。と思ったけどOracleにはMviewやらいろんな機能が多くてそこは加味してないので。。
Oracle LogMinerを使用してトランザクションごとコミット順に並び替えて表示する
アーカイブログやオンラインREDOログからLogMinerを使用して、実行されたSQLを調査する際に
不要な内部SQLがあったりして確認が手間だったりする。
不要な内部SQLを取得せずにトランザクションごとにまとめて、コミットした順番で表示させる。
前提
- サプリメンタルロギングの状態
- 最小サプリメンタルロギングは有効化
SQL>SELECT SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUPPLEME SUP SUP
-------- --- ---
YES NO NO
- ログを読み込ませる
exec SYS.DBMS_LOGMNR.ADD_LOGFILE(
LOGFILENAME => '/orabackup/online-redo/redo01.log',
OPTIONS => SYS.DBMS_LOGMNR.NEW);
- LogMinerを起動する
- DICT_FROM_ONLINE_CATALOG・・データディクショナリをデータベースのものを使用する
- COMMITTED_DATA_ONLY・・コミットされたトランザクションのみ
- NO_ROWID_IN_STMT ・・・ROWIDを追記しない
exec SYS.DBMS_LOGMNR.START_LOGMNR(
OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
+ SYS.DBMS_LOGMNR.COMMITTED_DATA_ONLY
+ SYS.DBMS_LOGMNR.NO_ROWID_IN_STMT);
トランザクションごとコミット順で取得
- トランザクションを２つ実行する
| 時系列 | Tx1 | Tx2 |
|---|---|---|
| 1 | set transaction read write; | |
| 2 | insert into TT values (999,999); | |
| 3 | set transaction read write; | |
| 4 | update TT set ID2=999 where id=1; | |
| 5 | update TT set ID2=666 where id=1; | |
| 6 | commit; | |
| 7 | commit; |
Tx2は後からトランザクションを開始しているがTx1より先にコミットしているため
Tx2、Tx1の順で取得するようにする。
set line 200
set pagesize 3000
col SQL_REDO format a40
col SCN format 99999999999999
col START_SCN format 99999999999999
col COMMIT_SCN format 99999999999999
SELECT
*
FROM
(
SELECT
SCN,
MIN(START_SCN) OVER (PARTITION BY RAWTOHEX(xid)) as START_SCN,
MAX(COMMIT_SCN) OVER (PARTITION BY RAWTOHEX(xid)) as COMMIT_SCN,
RAWTOHEX(xid) as xid,
SEQUENCE#,
MIN(START_TIMESTAMP) OVER (PARTITION BY RAWTOHEX(xid)) as START_TIMESTAMP,
MAX(COMMIT_TIMESTAMP) OVER (PARTITION BY RAWTOHEX(xid)) as COMMIT_TIMESTAMP,
substr(SQL_REDO,0,40) SQL_REDO,
OPERATION_CODE,
MIN(OPERATION_CODE) OVER (PARTITION BY RAWTOHEX(xid) ) as OPE_CODE_MIN
FROM V$LOGMNR_CONTENTS
WHERE
USERNAME in ('ORAUSER') --取得したいユーザ名を指定
AND (SEG_OWNER <> 'SYS' or SEG_OWNER is null) --内部SQLを取得しない
AND (INFO not like '%INTERNAL%' or INFO is null) --内部SQLを取得しない
AND OPERATION_CODE in (1,2,3,5,6,7,36) --DDL、DML、START、COMMITとROLLBACKのみ取得
)
WHERE
OPE_CODE_MIN <> 6 --DML,DDLがなかったトランザクションのSTARTとCOMMITを表示しない
AND (OPE_CODE_MIN <> 5 or OPERATION_CODE = 5) --内部SQLでないDDLを取得
ORDER BY COMMIT_SCN,xid,SEQUENCE#;
SCN STAR COMM XID S START_TIMESTAMP COMMIT_TIMESTAMP SQL_REDO O O
---- ---- ---- ---------------- - ------------------- ------------------- ---------------------------------------- - -
7496 7496 7503 09000700172A0600 1 2016/02/18 14:41:27 2016/02/18 14:41:42 set transaction read write; 6 3
7496 7496 7503 09000700172A0600 2 2016/02/18 14:41:27 2016/02/18 14:41:42 update "ORAUSER"."TT" set "ID2" = '999' 3 3
7503 7496 7503 09000700172A0600 3 2016/02/18 14:41:27 2016/02/18 14:41:42 commit; 7 3
7493 7493 7505 0A0014002FCD0B00 1 2016/02/18 14:41:18 2016/02/18 14:41:46 set transaction read write; 6 1
7493 7493 7505 0A0014002FCD0B00 2 2016/02/18 14:41:18 2016/02/18 14:41:46 insert into "ORAUSER"."TT"("ID","ID2") v 1 1
7504 7493 7505 0A0014002FCD0B00 3 2016/02/18 14:41:18 2016/02/18 14:41:46 update "ORAUSER"."TT" set "ID2" = '666' 3 1
7505 7493 7505 0A0014002FCD0B00 4 2016/02/18 14:41:18 2016/02/18 14:41:46 commit; 7 1
XIDがトランザクションIDで09000700172A0600がTx2、0A0014002FCD0B00がTx1。
DDLの内容取得
| 時系列 | Tx1 |
|---|---|
| 1 | create table TEST (id int primary key);｜ |
| 2 | insert into TEST values (999); |
| 3 | commit; |
| 4 | truncate table TEST; |
SCN STAR COMM XID SE START_TIMESTAMP COMMIT_TIMESTAMP SQL_REDO O O
---- ---- ---- ---------------- -- ------------------- ------------------- ---------------------------------------- - -
8873 8848 8878 0A00120003D60B00 14 2016/02/18 15:04:43 2016/02/18 15:04:43 create table TEST (id int primary key); 5 5
8882 8882 8883 0A0006000FD70B00 1 2016/02/18 15:04:53 2016/02/18 15:04:53 set transaction read write; 6 1
8882 8882 8883 0A0006000FD70B00 2 2016/02/18 15:04:53 2016/02/18 15:04:53 insert into "ORAUSER"."TEST"("ID") value 1 1
8883 8882 8883 0A0006000FD70B00 3 2016/02/18 15:04:53 2016/02/18 15:04:53 commit; 7 1
8916 8895 8924 0700070050CD0700 2 2016/02/18 15:05:00 2016/02/18 15:05:00 truncate table TEST; 5 5
LOBは内容は出力されないが、登録時に行われる初期化EMPTY_CLOB()は拾うことはできるのでXIDから検索すれば中身の確認もできる。
insert into "ORAUSER"."LOBLOB"("ID","LL") values ('2',EMPTY_CLOB());
