ANBOB™

有时因为表空间转移或整理碎片的目的需要MOVE TABLE， 但是该操作在12.2之前如果有业务在运行中常常因为排它锁的原因而失败无法有时间点切入。或move 成功后会导致索引无效而又影响了业务，无法像索引那个可以rebuild online, 在12C版本引入了几个重要的online操作，其实move table online就是12C R2版本引入的一个重要特性。

# 11g

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> create table t10 as select object_id,object_name from all_objects where rownum<=100; Table created. SQL> create index idx_t10 on t10(object_id);
Index created.
 
SQL> @ind t10
Display indexes where table or index name matches %t10%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
WEEJAR               T10                            IDX_T10                           1 OBJECT_ID


INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS   
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- 
WEEJAR               T10                            IDX_T10                        NORMAL     NO   VALID    

SQL> alter table t10 move online;
alter table t move online
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

SQL> alter table t10 move;
Table altered.

SQL> @ind t10
Display indexes where table or index name matches %t10%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
WEEJAR               T10                            IDX_T10                           1 OBJECT_ID


INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS   PART
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ----
WEEJAR               T10                            IDX_T10                        NORMAL     NO   UNUSABLE NO  

Note:
在11G及以前版本中move table不存在online选项， 如果move 会同样会引rowid改变使对应的索引失效。在12c r1版本中只引入了对IOT TABLE的online操作。

# 12.2

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production                 0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                                0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0


# session 1
SQL> create table t11(id int, name varchar2(500));
Table created.

SQL> insert into t11 select rownum id, 'anbob'||rownum from dual connect by rownum<=1e4; 
10000 rows created. 

SQL> commit;
Commit complete.

SQL> @gts t11
Gather Table Statistics for table t11...
PL/SQL procedure successfully completed.

SQL> @tab t11
Show tables matching condition "%t11%" (if schema is not specified then current user's tables only are shown)...
OWNER                TABLE_NAME                     TYPE     NUM_ROWS        BLOCKS     EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED   DEGREE                                   COMPRESS
-------------------- ------------------------------ ---- ------------ ------------- --------- ------ ------ ------------------- ---------------------------------------- --------
ANBOB                T11                            TAB         10000            28         0      0     14 2017-03-17 15:23:12          1                               DISABLED

SQL> alter table t11 add primary key (id);
Table altered.

SQL> create index idx_t11_name on t11(name);
Index created.

SQL> @ind t11
Display indexes where table or index name matches %t11%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
ANBOB                T11                            IDX_T11_NAME                      1 NAME
                                                    SYS_C007403                       1 ID

INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS   
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- 
ANBOB                T11                            IDX_T11_NAME                   NORMAL     NO   VALID    
                     T11                            SYS_C007403                    NORMAL     YES  VALID    

# session 2

SQL> insert into t11 select 1e4+to_number(COLUMN_VALUE),'weejar'||rownum from xmltable('100 to 200');
101 rows created.

-- no commit;

# session 1
SQL> alter table t11 move;
alter table t11 move
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL> alter table t11 move online;
...
-- wait session 2 commit; event:(enq: TX - row lock contention)

# session 3
SQL> delete t11 where id=1000;
1 row deleted.

SQL> commit;
Commit complete.

# session 1
--当session 2 commit后，alter table move online的返回成功。
SQL> alter table t11 move online;
Table altered.

SQL> @ind t11
Display indexes where table or index name matches %t11%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
ANBOB                T11                            IDX_T11_NAME                      1 NAME
                                                    SYS_C007403                       1 ID

INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS  
-------------------- ------------------------------ ------------------------------ ---------- ---- --------
ANBOB                T11                            IDX_T11_NAME                   NORMAL     NO   VALID   
                     T11                            SYS_C007403                    NORMAL     YES  VALID   

NOTE：
12.2 的alter table move line不会对新事务阻塞同时会自动的维护索引的有效性，实现了真正的online.  简化了过去版本中的在线重定义功能。

打赏

微信扫一扫，打赏作者吧～