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
在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博客 ” ,链接:http://blog.itpub.net/30192548/viewspace-2121495/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30192548/viewspace-2121495/