上一篇文章中已经介绍了通过CLONE_TABLESPACES过程表空间,这一篇继续描述如何通过ATTACH_TABLESPACES过程,将表空间加载到目标数据库中。[@more@]
首先需要将源库的datafile和元文件通过scp都copy过来。
[oracle@rhel131 transfer]$ pwd
/u01/transfer
[oracle@rhel131 transfer]$ ls
NADSPACE001.dbf NADSPACE003.dbf NADSPACE_091027.dp
NADSPACE002.dbf NADSPACE004.dbf NADSPACE_091027.log
建立这个目录的directory
SQL> create directory D_TRANS_3 as '/u01/transfer';
Directory created.
SQL> grant read,write on directory D_TRANS_3 to system;
Grant succeeded.
下面可以执行ATTACH_TABLESPACES过程了:
SQL> conn system/sys
Connected.
SQL> SET SERVEROUT ON SIZE 1000000
SQL> DECLARE
V_JOB_NAME VARCHAR2(30) := 'MY_ATTACH_TABLESPACES';
V_TABLESPACE_NAME DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
V_DUMPFILE DBMS_STREAMS_TABLESPACE_ADM.FILE;
V_LOGFILE DBMS_STREAMS_TABLESPACE_ADM.FILE;
V_DATAFILES DBMS_STREAMS_TABLESPACE_ADM.FILE_SET;
BEGIN
V_DUMPFILE.DIRECTORY_OBJECT := 'D_TRANS_3';
V_DUMPFILE.FILE_NAME := 'NADSPACE_091027.dp';
V_LOGFILE.DIRECTORY_OBJECT := 'D_TRANS_3';
V_LOGFILE.FILE_NAME := 'NADSPACE_091027_imp.log';
V_DATAFILES(1).DIRECTORY_OBJECT := 'D_TRANS_3';
V_DATAFILES(2).DIRECTORY_OBJECT := 'D_TRANS_3';
V_DATAFILES(3).DIRECTORY_OBJECT := 'D_TRANS_3';
V_DATAFILES(4).DIRECTORY_OBJECT := 'D_TRANS_3';
V_DATAFILES(1).FILE_NAME := 'NADSPACE001.dbf';
V_DATAFILES(2).FILE_NAME := 'NADSPACE002.dbf';
V_DATAFILES(3).FILE_NAME := 'NADSPACE003.dbf';
V_DATAFILES(4).FILE_NAME := 'NADSPACE004.dbf';
DBMS_STREAMS_TABLESPACE_ADM.ATTACH_TABLESPACES(V_JOB_NAME,
V_DUMPFILE,
V_DATAFILES,
V_DATAFILES,
NULL,
V_LOGFILE,
V_TABLESPACE_NAME);
FOR I IN 1..V_TABLESPACE_NAME.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(V_TABLESPACE_NAME(I));
END LOOP;
END;
/
NADSPACE
表空间已导入,如果需要修改表空间,那么将表空间至于READ WRITE状态:
SQL> alter tablespace nadspace read write;
Tablespace altered.
首先需要将源库的datafile和元文件通过scp都copy过来。
[oracle@rhel131 transfer]$ pwd
/u01/transfer
[oracle@rhel131 transfer]$ ls
NADSPACE001.dbf NADSPACE003.dbf NADSPACE_091027.dp
NADSPACE002.dbf NADSPACE004.dbf NADSPACE_091027.log
建立这个目录的directory
SQL> create directory D_TRANS_3 as '/u01/transfer';
Directory created.
SQL> grant read,write on directory D_TRANS_3 to system;
Grant succeeded.
下面可以执行ATTACH_TABLESPACES过程了:
SQL> conn system/sys
Connected.
SQL> SET SERVEROUT ON SIZE 1000000
SQL> DECLARE
V_JOB_NAME VARCHAR2(30) := 'MY_ATTACH_TABLESPACES';
V_TABLESPACE_NAME DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
V_DUMPFILE DBMS_STREAMS_TABLESPACE_ADM.FILE;
V_LOGFILE DBMS_STREAMS_TABLESPACE_ADM.FILE;
V_DATAFILES DBMS_STREAMS_TABLESPACE_ADM.FILE_SET;
BEGIN
V_DUMPFILE.DIRECTORY_OBJECT := 'D_TRANS_3';
V_DUMPFILE.FILE_NAME := 'NADSPACE_091027.dp';
V_LOGFILE.DIRECTORY_OBJECT := 'D_TRANS_3';
V_LOGFILE.FILE_NAME := 'NADSPACE_091027_imp.log';
V_DATAFILES(1).DIRECTORY_OBJECT := 'D_TRANS_3';
V_DATAFILES(2).DIRECTORY_OBJECT := 'D_TRANS_3';
V_DATAFILES(3).DIRECTORY_OBJECT := 'D_TRANS_3';
V_DATAFILES(4).DIRECTORY_OBJECT := 'D_TRANS_3';
V_DATAFILES(1).FILE_NAME := 'NADSPACE001.dbf';
V_DATAFILES(2).FILE_NAME := 'NADSPACE002.dbf';
V_DATAFILES(3).FILE_NAME := 'NADSPACE003.dbf';
V_DATAFILES(4).FILE_NAME := 'NADSPACE004.dbf';
DBMS_STREAMS_TABLESPACE_ADM.ATTACH_TABLESPACES(V_JOB_NAME,
V_DUMPFILE,
V_DATAFILES,
V_DATAFILES,
NULL,
V_LOGFILE,
V_TABLESPACE_NAME);
FOR I IN 1..V_TABLESPACE_NAME.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(V_TABLESPACE_NAME(I));
END LOOP;
END;
/
NADSPACE
表空间已导入,如果需要修改表空间,那么将表空间至于READ WRITE状态:
SQL> alter tablespace nadspace read write;
Tablespace altered.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/271283/viewspace-1028183/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/271283/viewspace-1028183/