[20130814] 12C Online rename and relocation of an active data file.txt
12c下更改数据文件可以在线修改,不像以前那样需要offline,改名后再online。
自己做一个简单的测试:
1.建立测试环境:
CREATE TABLESPACE lfree DATAFILE
'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' SIZE 100M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
create table t tablespace lfree as select rownum id,'test' name from dual connect by level<=100;
Table created.
2.建立一个存储过程:
--以sys用户执行
SQL> grant execute on dbms_lock to scott;
Grant succeeded.
--再建立存储过程:
CREATE OR REPLACE PROCEDURE test_proc AS
BEGIN
FOR J IN 1..26 LOOP
FOR i IN 1..100 LOOP
update t set name=chr(64+j)||i where id=i;
COMMIT;
dbms_lock.sleep(0.1);
END LOOP;
END LOOP;
END;
/
Procedure created.
--会话1执行如下:
exec test_proc
--会话2执行如下:
SQL> ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf';
Database altered.
--会话2已经执行完成,而会话1依旧在执行。会话1大约在100*0.1*26=260秒上下完成。
--加入keep参数,保持文件存在。
SQL> ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' keep ;
Database altered.
--会话1结束后:
D:\app\oracle\oradata\test\test01p>ls -l lfree*
ls -l lfree*
-rw-rw-rw- 1 user group 104865792 Aug 14 20:46 LFREE01.DBF
-rw-rw-rw- 1 user group 104865792 Aug 14 20:44 LFREE01X.DBF
参看alert.log文件,也存在如下记录:
Wed Aug 14 20:41:48 2013
ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf'
Wed Aug 14 20:41:48 2013
Moving datafile D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01.DBF (11) to D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01X.DBF
Move operation committed for file D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01X.DBF
Completed: ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf'
ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' keep
Wed Aug 14 20:44:40 2013
Moving datafile D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01X.DBF (11) to D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01.DBF
Move operation committed for file D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01.DBF
Completed: ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' keep
12c下更改数据文件可以在线修改,不像以前那样需要offline,改名后再online。
自己做一个简单的测试:
SQL> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
1.建立测试环境:
CREATE TABLESPACE lfree DATAFILE
'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' SIZE 100M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
create table t tablespace lfree as select rownum id,'test' name from dual connect by level<=100;
Table created.
2.建立一个存储过程:
--以sys用户执行
SQL> grant execute on dbms_lock to scott;
Grant succeeded.
--再建立存储过程:
CREATE OR REPLACE PROCEDURE test_proc AS
BEGIN
FOR J IN 1..26 LOOP
FOR i IN 1..100 LOOP
update t set name=chr(64+j)||i where id=i;
COMMIT;
dbms_lock.sleep(0.1);
END LOOP;
END LOOP;
END;
/
Procedure created.
--会话1执行如下:
exec test_proc
--会话2执行如下:
SQL> ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf';
Database altered.
--会话2已经执行完成,而会话1依旧在执行。会话1大约在100*0.1*26=260秒上下完成。
--加入keep参数,保持文件存在。
SQL> ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' keep ;
Database altered.
--会话1结束后:
SQL> select * from t where rownum<=2;
ID NAME
---------- --------------------
1 Z1
2 Z2
D:\app\oracle\oradata\test\test01p>ls -l lfree*
ls -l lfree*
-rw-rw-rw- 1 user group 104865792 Aug 14 20:46 LFREE01.DBF
-rw-rw-rw- 1 user group 104865792 Aug 14 20:44 LFREE01X.DBF
参看alert.log文件,也存在如下记录:
Wed Aug 14 20:41:48 2013
ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf'
Wed Aug 14 20:41:48 2013
Moving datafile D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01.DBF (11) to D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01X.DBF
Move operation committed for file D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01X.DBF
Completed: ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf'
ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' keep
Wed Aug 14 20:44:40 2013
Moving datafile D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01X.DBF (11) to D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01.DBF
Move operation committed for file D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE01.DBF
Completed: ALTER DATABASE MOVE DATAFILE 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01x.dbf' TO 'D:\APP\ORACLE\ORADATA\TEST\TEST01P\lfree01.dbf' keep
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-768635/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-768635/