Oracle 12c对Move的增强

12c对Move做了一些增强,在12c以前我们要把数据文件移动到其他位置,我们需要先offline数据文件,然后再数据库中rename数据文件的路径,然后再online,而12c直接就可以move,不再需要那些繁琐的操作。如下所示:

SQL> select name from v$datafile;
NAME
---------------------------------------------------------------------------------------------------------------------------------------------
/oracle/app/oracle/oradata/DB12C/datafile/o1_mf_undotbs1_8hhbbbho_.dbf
/oracle/app/oracle/oradata/DB12C/D38D1D177D1575B6E0430100007F6C44/datafile/o1_mf_system_8hl2tn8l_.dbf
/oracle/app/oracle/oradata/DB12C/D38D1D177D1575B6E0430100007F6C44/datafile/o1_mf_sysaux_8hl2tndl_.dbf
/oracle/app/oracle/oradata/DB12C/D38D1D177D1575B6E0430100007F6C44/datafile/o1_mf_users_8hl2v8n4_.dbf
/oracle/app/oracle/oradata/DB12C/D38D1D177D1575B6E0430100007F6C44/datafile/test.dbf

SQL> alter database move datafile '/oracle/app/oracle/oradata/DB12C/D38D1D177D1575B6E0430100007F6C44/datafile/test.dbf' to '/oracle/app/oracle/oradata/DB12C/test.dbf';
Database altered.

SQL> select name from v$datafile;
NAME
---------------------------------------------------------------------------------------------------------------------------------------------
/oracle/app/oracle/oradata/DB12C/datafile/o1_mf_undotbs1_8hhbbbho_.dbf
/oracle/app/oracle/oradata/DB12C/D38D1D177D1575B6E0430100007F6C44/datafile/o1_mf_system_8hl2tn8l_.dbf
/oracle/app/oracle/oradata/DB12C/D38D1D177D1575B6E0430100007F6C44/datafile/o1_mf_sysaux_8hl2tndl_.dbf
/oracle/app/oracle/oradata/DB12C/D38D1D177D1575B6E0430100007F6C44/datafile/o1_mf_users_8hl2v8n4_.dbf
/oracle/app/oracle/oradata/DB12C/test.dbf

并且在做这个操作的同时,我们开启另外一个session,对test表空间的数据进行编辑,会发现移动数据文件并不会阻塞这个session。数据操作仍然正常执行下去了。
12c的在线重定义也进行了增强,我们可以直接对表和索引一起进行move操作。让索引有效。

SQL> create table t as select * from dba_objects;
Table created.

SQL> alter table t modify object_id not null;
Table altered.

SQL> create index idx_objid on t(object_id);
Index created.

SQL> select segment_name,TABLESPACE_NAME from dba_segments where segment_name in ('T','IDX_OBJID');
SEGMENT_NAME                             TABLESPACE_NAME
---------------------------------------- ------------------------------
T                                         TEST
IDX_OBJID                                 TEST

SQL> exec dbms_redefinition.REDEF_TABLE(UNAME=>'test',TNAME=>'t',TABLE_PART_TABLESPACE=>'USERS',INDEX_TABLESPACE=>'USERS');
PL/SQL procedure successfully completed.

SQL> select segment_name,TABLESPACE_NAME from dba_segments where segment_name in ('T','IDX_OBJID');
SEGMENT_NAME                             TABLESPACE_NAME
---------------------------------------- ------------------------------
T                                        USERS
IDX_OBJID                                USERS

SQL> select index_name,status from dba_indexes where index_name='IDX_OBJID';
INDEX_NAME                        STATUS
--------------------------------- --------
IDX_OBJID                         VALID
分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *