事前準備
version情報
17:35:29 SQL> select version from v$instance;
VERSION
-----------------
11.2.0.1.0
テーブルとインデックスの作成
- テーブル作成
1 create table t_redefine (
2 tid number(10)
3 , cdate date
4 , udate date
5 , status number(2)
6 , delflf char(1)
7* )
11:35:17 SQL> /
Table created.
Elapsed: 00:00:00.11
11:35:18 SQL>
- シーケンス作成
1 create sequence seq_redefine
2 increment by 1
3 start with 1
4 maxvalue 9999999999
5 minvalue 1
6 cycle
7 cache 100
8* order
11:35:45 SQL> /
Sequence created.
Elapsed: 00:00:00.04
11:35:47 SQL>
- プライマリーキー作成
1* create unique index t_redefie_pk on t_redefine( tid )
11:36:07 SQL> /
Index created.
Elapsed: 00:00:00.10
11:36:09 SQL>
1* alter table t_redefine add constraints t_redefine_pk primary key( tid )
11:36:23 SQL> /
Table altered.
Elapsed: 00:00:00.10
11:36:26 SQL>
- テーブルに１万レコード登録
1 declare
2 cnt number(10);
3 begin
4 cnt := 0;
5 for i in 1..10000 loop
6 insert into t_redefine values( seq_redefine.nextval
7 , sysdate
8 , sysdate
9 , mod( i, 10 )
10 , mod( trunc( dbms_random.value*10 ), 2 )
11 );
12 cnt := cnt + 1;
13 if mod(cnt, 100) = 0 then
14 commit;
15 end if;
16 end loop;
17 commit;
18* end;
11:37:43 SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.59
11:37:48 SQL>
- インデックス作成
1* create index t_redefine_ix01 on t_redefine( status )
11:38:03 SQL> /
Index created.
Elapsed: 00:00:00.13
11:38:04 SQL>
- 統計情報取得
1 begin
2 dbms_stats.gather_table_stats (
3 ownname => 'TEST01'
4 , tabname => 'T_REDEFINE'
5 , cascade => true
6 );
7* end;
11:38:29 SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.46
11:38:33 SQL>
MVIEW作成
- MLOG作成
1 create materialized view log on t_redefine
2 with primary key
3* including new values
11:39:01 SQL> /
Materialized view log created.
Elapsed: 00:00:01.15
11:39:04 SQL>
- MVIEW作成
1 create materialized view t_redefine_mv (
2 tid
3 , cdate
4 , udate
5 , status
6 , delflg
7 , cno
8 )
9 using no index
10 refresh fast
11 with primary key
12 as select tid
13 , cdate
14 , udate
15 , status
16 , delflg
17 , mod( tid, 100 )
18* from t_redefine
11:45:41 SQL> /
Materialized view created.
Elapsed: 00:00:02.07
11:45:45 SQL>
DBMS_REDEFINITIONの実行
ここでは実行する各PROCEDUREの説明はしません。
CONS_REDEF_TABLE
- CONS_REDEF_TABLEの実行
1 begin
2 dbms_redefinition.can_redef_table( 'TEST01', 'T_REDEFINE', DBMS_REDEFINITION.CONS_USE_PK, null );
3* end;
11:48:04 SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.34
11:48:05 SQL>
START_REDEF_TABLE
- 再定義用NEWテーブル作成
1 create table t_redefine_new
2 partition by list ( status )
3 (
4 partition p000 values( 0 )
5 , partition p001 values( 1 )
6 , partition pdef values( default )
7 )
8 enable row movement
9* as select * from t_redefine where 1 = 2
11:49:48 SQL> /
Table created.
Elapsed: 00:00:00.33
11:49:53 SQL>
- 再定義用NEWプライマリーキー作成
本当はここで作成しなくてもコピーされるので不要。
1* create unique index t_redefine_new_pk on t_redefine_new( tid )
11:50:36 SQL> /
Index created.
Elapsed: 00:00:00.14
11:50:40 SQL>
1* alter table t_redefine_new add constraints t_redefine_new_pk primary key( tid )
11:50:57 SQL> /
Table altered.
Elapsed: 00:00:00.17
11:50:58 SQL>
- 再定義用NEWインデックス作成
1* create index t_redefine_new_ix1 on t_redefine_new( status ) local
11:51:26 SQL> /
Index created.
Elapsed: 00:00:00.47
11:51:28 SQL>
- START_REDEF_TABLEの実行
1 begin
2 dbms_redefinition.start_redef_table (
3 uname => 'TEST01'
4 , orig_table => 'T_REDEFINE'
5 , int_table => 'T_REDEFINE_NEW'
6 , col_mapping => NULL
7 , options_flag => DBMS_REDEFINITION.CONS_USE_PK
8 , part_name => NULL
9 );
10* end;
11:53:06 SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.92
11:53:20 SQL>
COPY_TABLE_DEPENDENTS
- COPY_TABLE_DEPENDENTSの実行
1 declare
2 num_errors PLS_INTEGER;
3 begin
4 dbms_redefinition.copy_table_dependents (
5 uname => 'TEST01'
6 , orig_table => 'T_REDEFINE'
7 , int_table => 'T_REDEFINE_NEW'
8 , copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS
9 , copy_triggers => TRUE
10 , copy_constraints => TRUE
11 , copy_privileges => TRUE
12 , ignore_errors => TRUE
13 , num_errors => num_errors
14 , copy_statistics => TRUE
15 , copy_mvlog => TRUE
16 );
17* end;
11:57:42 SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:48.51
11:58:31 SQL>
- DBA_REDEFINITION_ERRORSでエラー確認
手動で先に用意したインデックスがコピー出来ずにエラーだと言っている。
1 select object_type
2 , object_owner
3 , object_name
4 , base_table_owner
5 , base_table_name
6 , ddl_txt
7* from dba_redefinition_errors
12:02:22 SQL> set long 1000
12:02:27 SQL> /
OBJECT_TYPE OBJECT_OWNER OBJECT_NAME BASE_TABLE_OWNER BASE_TABLE_NAME DDL_TXT
------------ -------------------- ------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------
INDEX TEST01 T_REDEFIE_PK TEST01 T_REDEFINE CREATE UNIQUE INDEX "TEST01"."TMP$$_T_REDEFIE_PK0" ON "TEST01"."T_REDEFINE_NEW"
("TID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
INDEX TEST01 T_REDEFINE_IX01 TEST01 T_REDEFINE CREATE INDEX "TEST01"."TMP$$_T_REDEFINE_IX010" ON "TEST01"."T_REDEFINE_NEW" ("ST
ATUS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
CONSTRAINT TEST01 T_REDEFINE_PK TEST01 T_REDEFINE ALTER TABLE "TEST01"."T_REDEFINE_NEW" ADD CONSTRAINT "TMP$$_T_REDEFINE_PK0" PRIM
ARY KEY ("TID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE NOVALIDATE
3 rows selected.
Elapsed: 00:00:00.24
12:02:28 SQL>
SYNC_INTERIM_TABLE
- SYNC_INTERIM_TABLEの実行
1 begin
2 dbms_redefinition.sync_interim_table (
3 uname => 'TEST01'
4 , orig_table => 'T_REDEFINE'
5 , int_table => 'T_REDEFINE_NEW'
6 );
7* end;
12:03:50 SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.12
12:03:52 SQL>
FINISH_REDEF_TABLE
- FINISH_REDEF_TABLEの実行
1 begin
2 dbms_redefinition.finish_redef_table (
3 uname => 'TEST01'
4 , orig_table => 'T_REDEFINE'
5 , int_table => 'T_REDEFINE_NEW'
6 );
7* end;
12:04:54 SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.88
12:04:55 SQL>
事後作業
結果確認
- オブジェクト確認
1 select object_type
2 , object_id
3 , object_name
4 , last_ddl_time
5 , status
6 from user_objects
7* order by 1, 3
12:07:50 SQL> /
OBJECT_TYPE OBJECT_ID OBJECT_NAME LAST_DDL_TIME STATUS
------------------- ---------- ------------------------------ ------------------- -------
INDEX 74605 T_REDEFIE_PK 2017/08/05 11:35:59 VALID
INDEX 74606 T_REDEFINE_IX01 2017/08/05 11:37:55 VALID
INDEX 74620 T_REDEFINE_NEW_IX1 2017/08/05 11:53:11 VALID
INDEX 74615 T_REDEFINE_NEW_PK 2017/08/05 11:50:31 VALID
INDEX PARTITION 74623 T_REDEFINE_NEW_IX1 2017/08/05 11:53:11 VALID
INDEX PARTITION 74622 T_REDEFINE_NEW_IX1 2017/08/05 11:53:11 VALID
INDEX PARTITION 74621 T_REDEFINE_NEW_IX1 2017/08/05 11:53:11 VALID
LOB 74627 SYS_LOB0000074626C00026$$ 2017/08/05 11:58:17 VALID
MATERIALIZED VIEW 74610 T_REDEFINE_MV 2017/08/05 11:45:36 INVALID
SEQUENCE 74604 SEQ_REDEFINE 2017/08/05 11:35:38 VALID
TABLE 74624 MLOG$_T_REDEFINE 2017/08/05 11:38:54 VALID
TABLE 74607 MLOG$_T_REDEFINE_NEW 2017/08/05 12:04:46 VALID
TABLE 74608 RUPD$_T_REDEFINE 2017/08/05 11:38:55 VALID
TABLE 74625 RUPD$_T_REDEFINE_NEW 2017/08/05 11:58:16 VALID
TABLE 74611 T_REDEFINE 2017/08/05 12:04:46 VALID
TABLE 74609 T_REDEFINE_MV 2017/08/05 11:45:35 VALID
TABLE 74603 T_REDEFINE_NEW 2017/08/05 12:04:46 VALID
TABLE PARTITION 74612 T_REDEFINE 2017/08/05 11:49:44 VALID
TABLE PARTITION 74613 T_REDEFINE 2017/08/05 11:49:44 VALID
TABLE PARTITION 74614 T_REDEFINE 2017/08/05 11:49:44 VALID
20 rows selected.
Elapsed: 00:00:00.15
12:07:51 SQL>
MVIEWがINVALIDになっている。
- MVIEW確認
1 select owner
2 , mview_name
3 , last_refresh_type
4 , last_refresh_date
5 , compile_state
6* from user_mviews
12:13:45 SQL> /
OWNER MVIEW_NAME LAST_REF LAST_REFRESH_DATE COMPILE_STATE
-------------------- ------------------------------ -------- ------------------- -------------------
TEST01 T_REDEFINE_MV FAST 2017/08/05 11:46:38 NEEDS_COMPILE
1 row selected.
Elapsed: 00:00:01.20
12:13:47 SQL>
COMPILE_STATEがNEEDS_COMPILEになっている。
- MVIEWのCOMPILEを実施
言われた通りに。
1* alter materialized view t_redefine_mv compile
12:14:51 SQL> /
Materialized view altered.
Elapsed: 00:00:01.60
12:14:55 SQL>
1 select owner
2 , mview_name
3 , last_refresh_type
4 , last_refresh_date
5 , compile_state
6* from user_mviews
12:15:11 SQL> /
OWNER MVIEW_NAME LAST_REF LAST_REFRESH_DATE COMPILE_STATE
-------------------- ------------------------------ -------- ------------------- -------------------
TEST01 T_REDEFINE_MV FAST 2017/08/05 11:46:38 VALID
1 row selected.
Elapsed: 00:00:00.26
12:15:12 SQL>
VALIDになった。
- MVIEWの差分リフレッシュ実施
12:18:04 SQL> execute dbms_mview.refresh( 't_redefine_mv', 'f' );
BEGIN dbms_mview.refresh( 't_redefine_mv', 'f' ); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "TEST01"."T_REDEFINE" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 1
Elapsed: 00:00:01.33
12:18:29 SQL>
差分リフレッシュ出来ない。
- 完全リフレッシュの実施
17:07:35 SQL> execute dbms_mview.refresh( 't_redefine_mv', 'c' );
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.62
17:07:49 SQL>
1 select owner
2 , mview_name
3 , last_refresh_type
4 , last_refresh_date
5 , compile_state
6* from user_mviews
17:09:52 SQL> /
OWNER MVIEW_NAME LAST_REF LAST_REFRESH_DATE COMPILE_STATE
-------------------- ------------------------------ -------- ------------------- -------------------
TEST01 T_REDEFINE_MV COMPLETE 2017/08/05 17:07:41 VALID
1 row selected.
Elapsed: 00:00:00.15
17:09:55 SQL>
- 再度、差分リフレッシュの実施
17:10:40 SQL> execute dbms_mview.refresh( 't_redefine_mv', 'f' );
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.22
17:10:44 SQL>
1 select owner
2 , mview_name
3 , last_refresh_type
4 , last_refresh_date
5 , compile_state
6* from user_mviews
18:26:07 7 /
OWNER MVIEW_NAME LAST_REF LAST_REFRESH_DATE COMPILE_STATE
-------------------- ------------------------------ -------- ------------------- -------------------
TEST01 T_REDEFINE_MV FAST 2017/08/05 17:10:35 VALID
1 row selected.
Elapsed: 00:00:00.12
18:26:08 SQL>
これで差分リフレッシュ出来るようになったが、完全リフレッシュを実施せずに復旧出来ないものか。
- 再定義されたテーブルの確認
1 select table_name
2 , partition_name
3 , num_rows
4 , last_analyzed
5* from user_tab_partitions
18:35:23 SQL> /
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
T_REDEFINE P000 1000 2017/08/05 18:34:36
T_REDEFINE P001 1000 2017/08/05 18:34:36
T_REDEFINE PDEF 8000 2017/08/05 18:34:36
3 rows selected.
Elapsed: 00:00:00.28
18:35:24 SQL>
- 再定義されたインデックスの確認
1 select index_name
2 , partition_name
3 , status
4 , num_rows
5 , last_analyzed
6* from user_ind_partitions
18:38:54 SQL> /
INDEX_NAME PARTITION_NAME STATUS NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ -------- ---------- -------------------
T_REDEFINE_NEW_IX1 P000 USABLE 1000 2017/08/05 18:34:36
T_REDEFINE_NEW_IX1 P001 USABLE 1000 2017/08/05 18:34:36
T_REDEFINE_NEW_IX1 PDEF USABLE 8000 2017/08/05 18:34:36
3 rows selected.
Elapsed: 00:00:00.22
18:38:55 SQL>