12C 在线MOVE

来源：这里教程网时间：2026-03-03 15:01:12 作者：

准备： --创建表空间 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 1 62 TEST ORACLE-12C sqlplus@ORACLE-12C (TNS V1-V3) TAB1 3 0 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 1 62 TEST ORACLE-12C sqlplus@ORACLE-12C (TNS V1-V3) TAB2 3 0 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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推