准备：

--创建表空间

SQL> create tablespace test datafile '/u01/app/oradata/nopdb/test100.dbf' size 2g;

--创建用户

SQL> create user test identified by test default tablespace test;

--赋权

SQL> grant connect,resource to test;

SQL> grant unlimited tablespace to test;

--检查创建用户

SQL> select username,account_status,DEFAULT_TABLESPACE,to_char(LAST_LOGIN,'yyyy-mm-dd hh24:mm:ss'),INHERITED from dba_users where INHERITED='NO';

USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TO_CHAR(LAST_LOGIN, INH

---------- -------------------------------- ------------------------------ ------------------- ---

HR EXPIRED & LOCKED SYSAUX NO

OJVMSYS EXPIRED & LOCKED SYSTEM NO

TEST OPEN TEST NO

1、在线move数据文件或修改数据文件名称

--查看数据文件当前名称

FILE_ID FILE_NAME G MAX_G AUTOEXTENSIBLE ONLINE_ CREATION_TIME

---------- ---------------------------------------------------------------------- ---------- ---------- --------------- ------- -------------------

5 /u01/app/oradata/nopdb/test100.dbf 2 0 NO ONLINE 2020-02-17 00:26:59

SQL> alter database move datafile 5 to '/u01/app/oradata/nopdb/test01.dbf';

Database altered.

2、MOVE 非分区表

--创建测试表

create table test.tab1 as select * from dba_objects;

create table test.tab2 as select * from dba_objects;

create table test.tab3 as select * from dba_objects;

--创建测试索引

create index idx_tab1_id on tab1(object_id);

create index idx_tab2_id on tab2(object_id);

create index idx_tab3_id on tab3(object_id);

--查看创建表、索引大小

SQL> select owner,segment_name,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 mb from dba_segments where owner='TEST';

OWNER SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME MB

---------- ------------------------------ ---------- ---------------------------------------- ----------

TEST TAB1 TABLE TEST 12

TEST TAB2 TABLE TEST 12

TEST TAB3 TABLE TEST 12

TEST IDX_TAB1_ID INDEX TEST 2

TEST IDX_TAB2_ID INDEX TEST 2

TEST IDX_TAB3_ID INDEX TEST 2

6 rows selected.

--模拟插入数据

SQL> insert into tab1 select * from tab2;

75722 rows created.

SQL> insert into tab1 select * from tab2;

75722 rows created.

SQL> commit;

Commit complete.

--再次查询

OWNER SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME MB

---------- ------------------------------ ---------- ---------------------------------------- ----------

TEST TAB1 TABLE TEST 34

--删除tab1表数据

SQL> delete tab1 ;

227165 rows deleted.

SQL> commit;

--再次插入数据

SQL> insert into tab1 select * from tab2 where object_id < 500;

SQL> commit;

--检查索引

SQL> select table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from dba_Indexes where owner='TEST';

TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS ORP

--------------- -------------------- -------- ---------- ----------- ---

TAB1 IDX_TAB1_ID VALID 1 168 NO

TAB2 IDX_TAB2_ID VALID 1 168 NO

TAB3 IDX_TAB3_ID VALID 1 168 NO

测试方法1:

--插入数据不提交后，MOVE表不加online

SQL> insert into tab1 select * from tab2 where object_id between 500 and 800;

300 rows created.

SQL> col OBJECT_NAME for a20

SQL> col MACHINE for a40

SQL> col PROGRAM for a40

SQL> col Deadlock for a60

SQL> SELECT a.inst_id, a.sid,a.username,a.machine,a.program,c.object_name,b.LOCKED_MODE,c.REQUEST FROM gv$session a inner join gv$locked_object b on a.sid =b.SESSION_ID and a.inst_id=b.inst_id inner join dba_objects c on b.object_id=c.object_id inner join gv$lock c on a.sid=c.sid;

INST_ID SID USERNAME MACHINE PROGRAM OBJECT_NAME LOCKED_MODE REQUEST

---------- ------ ---------- ---------------------------------------- ---------------------------------------- -------------------- ----------- ----------

1 62 TEST ORACLE-12C sqlplus@ORACLE-12C (TNS V1-V3) TAB1 3 0

--move表操作

SQL> alter table test.tab1 move;

alter table test.tab1 move

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

结论：有锁报资源繁忙，MOVE表不支持DML操作

测试方法2:

--数据提交后，MOVE表不加online

SQL> insert into tab1 select * from tab2 where object_id between 500 and 800;

300 rows created.

SQL> commit;

Commit complete.

--此时检查没有锁，也没有DML操作

SQL> SELECT a.inst_id, a.sid,a.username,a.machine,a.program,c.object_name,b.LOCKED_MODE,c.REQUEST FROM gv$session a inner join gv$locked_object b on a.sid =b.SESSION_ID and a.inst_id=b.inst_id inner join dba_objects c on b.object_id=c.object_id inner join gv$lock c on a.sid=c.sid;

no rows selected

--move表操作

SQL> alter table test.tab1 move;

Table altered.

--检查表大小及索引情况

SQL> select table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from dba_Indexes where owner='TEST';

TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS ORP

--------------- -------------------- -------- ---------- ----------- ---

TAB1 IDX_TAB1_ID UNUSABLE 1 168 NO

SQL> select owner,segment_name,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 mb from dba_segments where owner='TEST';

OWNER SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME MB

---------- ------------------------------ ---------- ---------------------------------------- ----------

TEST TAB1 TABLE TEST .125

TEST TAB2 TABLE TEST 12

TEST TAB3 TABLE TEST 12

TEST IDX_TAB2_ID INDEX TEST 2

TEST IDX_TAB3_ID INDEX TEST 2

--重建索引

SQL> alter index test.IDX_TAB1_ID rebuild online;

--再次检查

SQL> select table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from dba_Indexes where owner='TEST';

TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS ORP

--------------- -------------------- -------- ---------- ----------- ---

TAB1 IDX_TAB1_ID VALID 1 2 NO

TAB2 IDX_TAB2_ID VALID 1 168 NO

TAB3 IDX_TAB3_ID VALID 1 168 NO

SQL> select owner,segment_name,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 mb from dba_segments where owner='TEST';

OWNER SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME MB

---------- ------------------------------ ---------- ---------------------------------------- ----------

TEST TAB1 TABLE TEST .125

TEST TAB2 TABLE TEST 12

TEST TAB3 TABLE TEST 12

TEST IDX_TAB1_ID INDEX TEST .0625

TEST IDX_TAB2_ID INDEX TEST 2

TEST IDX_TAB3_ID INDEX TEST 2

6 rows selected.

结论：在没有DML操作时候，MOVE 表操作与11g一样正常，但索引失效，需要重建解决。

测试方法3：

--数据不提交，MOVE TAB2表加online （12c新特性）

SQL> insert into tab2 select * from tab3;

75723 rows created.

SQL> commit;

Commit complete.

SQL> delete tab2;

227168 rows deleted.

SQL> insert into tab2 select * from tab3 where object_id between 500 and 1800;

1300 rows created.

--检查TAB2

SQL> select owner,segment_name,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 mb from dba_segments where owner='TEST';

OWNER SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME MB

---------- ------------------------------ ---------- ---------------------------------------- ----------

TEST TAB2 TABLE TEST 34

TEST TAB3 TABLE TEST 12

TEST IDX_TAB2_ID INDEX TEST 6

TEST IDX_TAB3_ID INDEX TEST 2

SQL> select table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from dba_Indexes where owner='TEST';

TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS ORP

--------------- -------------------- -------- ---------- ----------- ---

TAB2 IDX_TAB2_ID VALID 1 168 NO

TAB3 IDX_TAB3_ID VALID 1 168 NO

--检查TAB2锁情况

SQL> SELECT a.inst_id, a.sid,a.username,a.machine,a.program,c.object_name,b.LOCKED_MODE,c.REQUEST FROM gv$session a inner join gv$locked_object b on a.sid =b.SESSION_ID and a.inst_id=b.inst_id inner join dba_objects c on b.object_id=c.object_id inner join gv$lock c on a.sid=c.sid;

INST_ID SID USERNAME MACHINE PROGRAM OBJECT_NAME LOCKED_MODE REQUEST

---------- ------ ---------- ---------------------------------------- ---------------------------------------- -------------------- ----------- ----------

1 62 TEST ORACLE-12C sqlplus@ORACLE-12C (TNS V1-V3) TAB2 3 0

--move表操作加online

SQL> alter table test.tab2 move online;

注意此操作一直卡住，检查等待事件是因为DML没有提交导致，提交后MOVE表完成；

--检查索引和表情况

SQL> select owner,segment_name,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 mb from dba_segments where owner='TEST';

OWNER SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME MB

---------- ------------------------------ ---------- ---------------------------------------- ----------

TEST TAB2 TABLE TEST .3125

TEST TAB3 TABLE TEST 12

TEST IDX_TAB2_ID INDEX TEST .125

TEST IDX_TAB3_ID INDEX TEST 2

SQL> select table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from dba_Indexes where owner='TEST';

TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS ORP

--------------- -------------------- -------- ---------- ----------- ---

TAB2 IDX_TAB2_ID VALID 1 5 NO

TAB3 IDX_TAB3_ID VALID 1 168 NO

结论：加上online后不影响索引有效性且不影响DML操作，但DML操作不提交会影响MOVE操作。

3、MOVE 分区表

--创建分区表

CREATE TABLE t

( tid NUMBER,

name VARCHAR2(50),

cdate DATE)

PARTITION BY RANGE (cdate)

(PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE test,

PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE test);

--插入数据，提交

SQL> INSERT INTO t SELECT level, 'name for ' || level, CASE WHEN MOD

(level, 2) = 0 THEN TO_DATE

('01/07/2014', 'DD/MM/YYYY') ELSE TO_DATE

('01/07/2015', 'DD/MM/YYYY') END FROM dual CONNECT BY level <= 1000;

1000 rows created.

SQL> commit;

Commit complete.

--收集统计信息

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'T');

--创建分区索引

SQL> create index idx_t_tid on T(tid) local;

Index created.

--检查分区大小和索引

SQL> select owner,segment_name,SEGMENT_TYPE,PARTITION_NAME,TABLESPACE_NAME,BYTES/1024/1024 mb from dba_segments where owner='TEST' and SEGMENT_NAME='T';

OWNER SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME MB

---------- ---------------------------------------- ---------------------------------------- ------------------------------ ---------------------------------------- ----------

TEST T TABLE PARTITION PART_2014 TEST 8

TEST T TABLE PARTITION PART_2015 TEST 8

SQL> col INDEX_OWNER for a20

SQL> col PARTITION_NAME for a30

SQL> select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,STATUS,BLEVEL,LEAF_BLOCKS from DBA_IND_PARTITIONS where INDEX_OWNER='TEST';

INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS BLEVEL LEAF_BLOCKS

-------------------- -------------------- ------------------------------ -------- ---------- -----------

TEST IDX_T_TID PART_2014 USABLE 1 2

TEST IDX_T_TID PART_2015 USABLE 1 2

--再次插入不提交

SQL> INSERT INTO t SELECT level, 'name for ' || level, CASE WHEN MOD

2 (level, 2) = 0 THEN TO_DATE

3 ('01/07/2014', 'DD/MM/YYYY') ELSE TO_DATE

4 ('01/07/2015', 'DD/MM/YYYY') END FROM dual CONNECT BY level <= 90000;

90000 rows created.

--在线move 表，卡住

SQL> ALTER TABLE TEST.T MOVE PARTITION PART_2014 ONLINE;

--检查锁等待

BLOCK_INST USER_NAME LO OBJECT LOCK_MODE OWNER SID SQL_ID LOGON_TIME EVENT SECONDS_IN_WAIT BLOCKING_SESSION

---------- --------------- -- --------------- -------------------- ---------- ------ --------------- ------------------- ------------------------------ --------------- ----------------

1 SYS TM SYS_JOURNAL_763 Exclusive TEST 1 11kvvhbbsgc03 2020-02-17 00:10:43 enq: TX - row lock contention 46 62

97

1 SYS TM SYS_RMTAB$$_H76 Exclusive TEST 1 11kvvhbbsgc03 2020-02-17 00:10:43 enq: TX - row lock contention 46 62

397

1 SYS TM T Row Exclusive TEST 1 11kvvhbbsgc03 2020-02-17 00:10:43 enq: TX - row lock contention 46 62

1 SYS OD T Row Exclusive TEST 1 11kvvhbbsgc03 2020-02-17 00:10:43 enq: TX - row lock contention 46 62

1 SYS OD T Exclusive TEST 1 11kvvhbbsgc03 2020-02-17 00:10:43 enq: TX - row lock contention 46 62

1 SYS TM T Row Exclusive TEST 1 11kvvhbbsgc03 2020-02-17 00:10:43 enq: TX - row lock contention 46 62

select inst_id,SID,SERIAL#,USER#,USERNAME,PROGRAM,event,SQL_ID,blocking_session from gv$session where sid=62

INST_ID SID SERIAL# USER# USERNAME PROGRAM EVENT SQL_ID BLOCKING_SESSION

---------- ------ ---------- ---------- ---------- ---------------------------------------- ---------------------------------------- --------------- ----------------

1 62 16515 107 TEST sqlplus@ORACLE-12C (TNS V1-V3) SQL*Net message from client 8ma202nswg34m

--会话62提交后，MOVE表完成

SQL> commit;

Commit complete.

SQL> ALTER TABLE TEST.T MOVE PARTITION PART_2014 ONLINE;

Table altered.

--检查表以及索引情况：均正常

SQL> select owner,segment_name,SEGMENT_TYPE,PARTITION_NAME,TABLESPACE_NAME,BYTES/1024/1024 mb from dba_segments where owner='TEST';

OWNER SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME MB

---------- ---------------------------------------- ---------------------------------------- ------------------------------ ---------------------------------------- ----------

TEST TAB2 TABLE TEST .3125

TEST TAB3 TABLE TEST 12

TEST T TABLE PARTITION PART_2015 TEST 8

TEST T TABLE PARTITION PART_2014 TEST 8

TEST IDX_TAB2_ID INDEX TEST .125

TEST IDX_TAB3_ID INDEX TEST 2

TEST IDX_T_TID INDEX PARTITION PART_2015 TEST 8.0625

TEST IDX_T_TID INDEX PARTITION PART_2014 TEST 8

SQL> select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,STATUS,BLEVEL,LEAF_BLOCKS from DBA_IND_PARTITIONS where INDEX_OWNER='TEST';

INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS BLEVEL LEAF_BLOCKS

-------------------- -------------------- ------------------------------ -------- ---------- -----------

TEST IDX_T_TID PART_2014 USABLE 1 181

TEST IDX_T_TID PART_2015 USABLE 1 2

结论：分区表MOVE操作与非分区表一样，只是语法不通而已。

ITPub博客

12C 在线MOVE

最新文章