事前準備

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>