Oracle 12cR2新特性: move table online
有时因为表空间转移或整理碎片的目的需要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. 简化了过去版本中的在线重定义功能。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。