http://yangtingkun.itpub.net/post/468/484859
在9i的时候,表空间的迁移使用EXP/IMP来实现。在10g中,除了使用EXP/IMP之外,还可以使用数据泵EXPDP/IMPDP,以及RMAN的命令TRANSPORT TABLESPACE命令,除此之外,还可以通过PL/SQL包DBMS_STREAMS_TABLESPACE_ADM来实现。
这一篇介绍PULL_SIMPLE_TABLESPACE过程。
Oracle的DBMS_STREAMS_TABLESPACE_ADM包提供了8个过程:ATTACH_TABLESPACES、ATTACH_SIMPLE_TABLESPACE、CLONE_TABLESPACES、CLONE_SIMPLE_TABLESPACE、DETACH_TABLESPACES、DETACH_SIMPLE_TABLESPACE、PULL_TABLESPACES、PULL_SIMPLE_TABLESPACE。
其中包含了4个SIMPLE过程,SIMPLE过程可以用来处理一个表空间,且这个表空间中仅包含一个数据文件的情况。
ATTACH过程用于将表空间加载到目标数据库中,这个过程的功能类似IMP导入。
CLONE过程用于将表空间置于只读,并导入源数据,并将表空间对应的数据文件拷贝到指定目录,这个操作包含了EXP的功能。
DETACH过程和CLONE过程功能类型,区别在于DETACH过程在拷贝完成后,会从源数据库中删除表空间。
PULL过程实际上包含了CLONE以及ATTACH两个过程,其中还包括了DBMS_FILE_TRANSFER包的调用,使得在目标数据库执行一个命令就完成表空间迁移的所有的操作。
使用Oracle提供PL/SQL包,可以与TRANSPORT TABLESPACE命令,以及EXP/IMP、EXPDP/IMPDP工具配合。比如通过TRANSPORT TABLESPACE命令,或EXP/EXPDP生成的导出源数据以及表空间的数据文件,可以利用ATTACH过程进行导入。利用CLONE过程或DETACH过程生成的源数据以及数据文件,也可以通过IMP/IMPDP方式导入的目标数据库中。
这里介绍一个最简单的例子,由于当前要迁移一个表空间,且表空间仅包含了一个数据文件,因此这里选择了最简单的PULL_SIMPLE_TABLESPACE过程。
[@more@]源数据库的情况:SQL> select * From v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> select property_name,property_value
2 from database_properties
3 where property_name like '%CHARACTERSET%';
PROPERTY_NAME
------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
WE8ISO8859P1
NLS_NCHAR_CHARACTERSET
AL16UTF16
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME ='NADSPACE';
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/NADSPACE001.dbf
SQL> SHOW PARAMETER DB_BLOCK_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK('NADSPACE',TRUE);
PL/SQL procedure successfully completed.
SQL> select * From transport_set_violations;
no rows selected
检查目标数据库:
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE '%CHARACTERSET%';
PROPERTY_NAME
------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16
NLS_CHARACTERSET
WE8ISO8859P1
SQL> SHOW PARAMETER DB_BLOCK_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME ='NADSPACE';
no rows selected
这里目标数据库的版本高于源数据库,其它的字符集以及DB_BLOCK_SIZE都与源数据库一致
在源数据库建立目录,使得源数据库可以读取数据文件:
SQL> create directory d_datafile as '/u01/app/oracle/oradata/orcl';
Directory created.
SQL> grant read,write on directory d_datafile to system;
Grant succeeded.
在目标数据库建立目标用户:
SQL> create user nad identified by nad ;
User created.
SQL> grant connect,resource to nad;
Grant succeeded.
建立到源数据库的dblink:
SQL> create public database link nad
2 connect to system
3 identified by sys
4 using 'orcl130';
Database link created.
测试一下dblink
SQL> select count(*) from nad.AAA@nad;
COUNT(*)
----------
49835
建立目标数据库的directory
SQL> create directory d_trans as '/u01/app/oracle/oradata/orcl';
Directory created.
SQL> grant read,write on directory d_trans to system;
Grant succeeded.
SQL> exec dbms_streams_tablespace_adm.pull_simple_tablespace('NADSPACE','NAD','D_TRANS');
PL/SQL procedure successfully completed.
[oracle@rhel131 orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@rhel131 orcl]$ cat NADSPACE001.plg
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 03:02:26
需要特别注意的地方是建立的源数据库以及目标数据库的directory必须是要传输的datafile的目录,否则会如下的错误:
SQL> exec dbms_streams_tablespace_adm.pull_simple_tablespace('NADSPACE','NAD','D_TRANS');
BEGIN dbms_streams_tablespace_adm.pull_simple_tablespace('NADSPACE','NAD','D_TRANS'); END;
*
ERROR at line 1:
ORA-23609: unable to find directory object for directory
/u01/app/oracle/oradata/orcl/
ORA-06512: at "SYS.DBMS_STREAMS_TBS_INT_INVOK", line 535
ORA-06512: at "SYS.DBMS_STREAMS_TBS_INT_INVOK", line 370
ORA-06512: at "SYS.DBMS_STREAMS_RPC", line 142
ORA-06512: at "SYS.DBMS_STREAMS_TABLESPACE_ADM", line 1949
ORA-06512: at line 1
文档上是这个描述的:
Usage Notes
To run this procedure, a user must meet the following requirements on the remote database:
* Have the EXP_FULL_DATABASE role
* Have execute privilege on the DBMS_STREAMS_TABLESPACE_ADM package
* Have access to at least one data dictionary view that contains information about the tablespaces. These views
include DBA_TABLESPACES and USER_TABLESPACES.
* Have MANAGE TABLESPACE or ALTER TABLESPACE privilege on a tablespace if the tablespace must be made read-only
* Have READ privilege on the directory object for the directory that contains the datafile for the tablespace. The
name of this tablespace is specified by the tablespace_name parameter. If a directory object does not exist for this
directory, then create the directory object and grant the necessary privileges before you run this procedure.
To run this procedure, a user must meet the following requirements on the local database:
* Have the roles IMP_FULL_DATABASE and EXECUTE_CATALOG_ROLE
* Have WRITE privilege on the directory object that will contain the Data Pump export the log file, specified by
the log_file parameter if non-NULL
* Have WRITE privilege on the directory object that will hold the datafile for the tablespace, specified by the
directory_object parameter
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/271283/viewspace-1028140/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/271283/viewspace-1028140/