準備：

--建立表空間

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操作與非分割槽表一樣，只是語法不通而已。

12C 線上MOVE

相關文章