使用exp做同平台同环境表空间传输。

说明：以下环境经测试操作可适用这样的方法：

          1.同环境9i到10g传输

          2.10g同字节跨平台传输   (linux下到windows ，linux到solaris<x86>) 

 

此次表空间传输的源库和目标库均为linux平台32位，即环境一样。

SQL> col platform_name for a30

SQL> select d.platform_name,endian_format from V$transportable_platform tp,V$database d

  2  where tp.platform_name=d.platform_name;

 

PLATFORM_NAME                  ENDIAN_FORMAT

------------------------------ --------------

Linux IA (32-bit)              Little

1.一些简单的准备

SQL> select name from V$tablespace;

NAME

------------------------------

SYSTEM

UNDOTBS1

SYSAUX

USERS

TEMP

MGMT_TABLESPACE

MGMT_ECM_DEPOT_TS

SQL> select file_name from dba_data_files;

 

FILE_NAME

--------------------------------------------------------------------------------

/apps/oracle/product/10.2.0/db_1/oradata/dboms/users01.dbf

/apps/oracle/product/10.2.0/db_1/oradata/dboms/sysaux01.dbf

/apps/oracle/product/10.2.0/db_1/oradata/dboms/undotbs01.dbf

/apps/oracle/product/10.2.0/db_1/oradata/dboms/system01.dbf

/apps/oracle/product/10.2.0/db_1/oradata/dboms/mgmt.dbf

/apps/oracle/product/10.2.0/db_1/oradata/dboms/mgmt_ecm_depot1.dbf

SQL> create tablespace tan

  2  datafile '/apps/oracle/product/10.2.0/db_1/oradata/dboms/tan01.dbf' size 10m

  3  segment space management auto

  4  extent management local;

 

SQL> create user tan identified by tan;

SQL> grant connect,resource to tan;

SQL> alter user tan default tablespace tan;

SQL> grant dba to tan;

SQL> conn tan/tan;

Connected.

 

2.自包含验证：

SQL> create table tan as select rownum id,username from dba_users;

SQL> create index ind_id on tan(id) tablespace users;

SQL> conn /as sysdba

执行非严格验证

SQL> exec dbms_tts.transport_set_check('tan',true);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

--说明验证通过。

执行严格验证

SQL> exec dbms_tts.transport_set_check('tan',true,true);

SQL> select * from transport_set_violations;

VIOLATIONS

--------------------------------------------------------------------------------

Index TAN.IND_ID in tablespace USERS points to table TAN.TAN in tablespace TAN

有上面的问题，没通过。

SQL>

将两个表空间放一起执行严格验证

QL> exec dbms_tts.transport_set_check('tan,users',true,true);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

--通过。

3.表空间传输

必须设置为read only

SQL> alter tablespace users read only;

TSQL> alter tablespace tan read only;

在源库导出元数据

 [oracle@dbserver1 ~]$ exp \'sys/system as sysdba\' tablespaces=users,tan

transport_tablespace=y file=exp_userstan.dmp;

Export terminated successfully with warnings.

在目标库创建用户

SQL> create user scott identified by scott;

SQL> create user tan identified by tan;

拷贝数据文件和dmp文件

导入元数据

[oracle@dbserver1 ~]$ imp \'sys/system as sysdba\' transport_tablespace=y

file=exp_userstan.dmp log=imp.log tablespaces=users,tan

 datafiles='/data1/tan01/users01.dbf, /data1/tan01/tan01.dbf'

Import terminated successfully without warnings.

[oracle@dbserver1 ~]$

 

在这个过程中很容易遇到字符问题和版本问题，需要修改一致。