1、在线重命名和重新定位活跃数据文件
在Oracle数据库12c R1版本中对数据文件的迁移或重命名不再需要太多繁琐的步骤，可以使用ALTER DATABASE MOVE DATAFILE这样的SQL语句对数据文件进行在线重命名和移动。
而当此数据文件正在传输时，终端用户可以执行查询，DML以及DDL方面的任务。另外，数据文件可以在存储设备间迁移，如从非ASM迁移至ASM，反之亦然。

--示例：
SQL> select sys_context ('USERENV', 'CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4157746405 PDB$SEED READ ONLY
3 1096553944 LLFPDB READ WRITE

SQL> alter session set container=LLFPDB;

Session altered.

SQL> select sys_context ('USERENV', 'CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
LLFPDB

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/llfdb/llfpdb/system01.dbf
/u01/app/oracle/oradata/llfdb/llfpdb/sysaux01.dbf
/u01/app/oracle/oradata/llfdb/llfpdb/llfpdb_users01.dbf

SQL> create tablespace rosan datafile '/u01/app/oracle/oradata/llfdb/llfpdb/rosan01.dbf' size 200m;

Tablespace created.

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

User created.

SQL> grant dba to rosan;

Grant succeeded.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/llfdb/llfpdb/system01.dbf
/u01/app/oracle/oradata/llfdb/llfpdb/sysaux01.dbf
/u01/app/oracle/oradata/llfdb/llfpdb/llfpdb_users01.dbf
/u01/app/oracle/oradata/llfdb/llfpdb/rosan01.dbf

--在线重命名数据文件
SQL> alter database move datafile '/u01/app/oracle/oradata/llfdb/llfpdb/rosan01.dbf' to '/u01/app/oracle/oradata/llfdb/llfpdb/rosan_01.dbf';

Database altered.

--在线移动数据文件到其他目录
[oracle@lfdz1 oradata]$ mkdir new_data
[oracle@lfdz1 oradata]$ ls
llfdb new_data

SQL> alter database move datafile '/u01/app/oracle/oradata/llfdb/llfpdb/rosan_01.dbf' to '/u01/app/oracle/oradata/new_data/rosan_01.dbf' reuse;

Database altered.

SQL> select file_name from dba_data_Files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/llfdb/llfpdb/system01.dbf
/u01/app/oracle/oradata/llfdb/llfpdb/sysaux01.dbf
/u01/app/oracle/oradata/llfdb/llfpdb/llfpdb_users01.dbf
/u01/app/oracle/oradata/new_data/rosan_01.dbf

[oracle@lfdz1 oradata]$ cd new_data/
[oracle@lfdz1 new_data]$ ls
rosan_01.dbf
[oracle@lfdz1 new_data]$ ls -ltr
total 205012
-rw-r----- 1 oracle oinstall 209723392 Jun 21 13:48 rosan_01.dbf

--在线移动数据文件到其他目录，并且在原路径保留其拷贝文件
SQL> alter database move datafile '/u01/app/oracle/oradata/new_data/rosan_01.dbf' to '/u01/app/oracle/oradata/llfdb/llfpdb/rosan_01.dbf' keep;

Database altered.

SQL> select file_name from dba_data_Files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/llfdb/llfpdb/system01.dbf
/u01/app/oracle/oradata/llfdb/llfpdb/sysaux01.dbf
/u01/app/oracle/oradata/llfdb/llfpdb/llfpdb_users01.dbf
/u01/app/oracle/oradata/llfdb/llfpdb/rosan_01.dbf

[oracle@lfdz1 oradata]$ cd new_data/
[oracle@lfdz1 new_data]$ ls
rosan_01.dbf
[oracle@lfdz1 new_data]$ ls -ltr
total 205012
-rw-r----- 1 oracle oinstall 209723392 Jun 21 13:48 rosan_01.dbf

[oracle@lfdz1 llfdb]$ cd llfpdb/
[oracle@lfdz1 llfpdb]$ ls
llfpdb_users01.dbf rosan_01.dbf sysaux01.dbf system01.dbf temp01.dbf
[oracle@lfdz1 llfpdb]$ ls -ltr rosan_01.dbf
-rw-r----- 1 oracle oinstall 209723392 Jun 21 13:48 rosan_01.dbf

ITPub博客

12c Oracle新特性1

最新文章