ORACLE 12.2新特性之在线MOVE且维护索引

本实验是通过ORACLE LIVE SQL进行测试的。

https://livesql.oracle.com/apex/livesql/file/index.html

实验：MOVE时不影响DML的操作并且维护索引

select * from v$version;
Oracle Database 12c Enterprise Edition 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

5 rows selected.

create table test(id number primary key, name varchar2(10), xid number not null);

Table created.

select * from test;

no data found

insert into test values(1,'MZF',1);

1 row(s) inserted.

insert into test values(2,'MZF1',2);

1 row(s) inserted.

commit;

Statement processed.

create index idx_t_xid on test(xid);

Index created.

select index_name, index_type, status from user_indexes where table_name='TEST';

INDEX_NAME   INDEX_TYPE   STATUS
SYS_C001240082  NORMAL    VALID
IDX_T_XID  NORMAL    VALID

2 rows selected.

select tablespace_name from user_tablespaces;

TABLESPACE_NAME
LIVESQL_USERS

alter table test move tablespace LIVESQL_USERS;

Table altered.

select index_name, index_type, status from user_indexes where table_name='TEST';

INDEX_NAME   INDEX_TYPE   STATUS
SYS_C001240082  NORMAL    UNUSABLE
IDX_T_XID  NORMAL    UNUSABLE

2 rows selected.

alter index idx_t_xid rebuild;

Statement processed.

alter index SYS_C001240082 rebuild;

Statement processed.

select index_name, index_type, status from user_indexes where table_name='TEST';

INDEX_NAME   INDEX_TYPE   STATUS
SYS_C001240082  NORMAL    VALID
IDX_T_XID  NORMAL    VALID

2 rows selected.

alter table test move tablespace LIVESQL_USERS online;

Table altered.

select index_name, index_type, status from user_indexes where table_name='TEST';

INDEX_NAME   INDEX_TYPE   STATUS
SYS_C001240082  NORMAL    VALID
IDX_T_XID  NORMAL    VALID

2 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30192548/viewspace-2131779/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30192548/viewspace-2131779/