导出oracle个别表空间,oracle可传输表空间:导入导出

实验环境： 源数据库服务器名：beijing    数据库全局名和SID：orcl

辅助数据库:suzhou 只安装数据库软件，没有新建数据库

在辅助服务器suzhou上复制一个例程名和数据库名都是bj的数据库.

Source database                                       Duplicate database

SYSTEM: windows server 2008 R2                        SYSTEM: windows server

2008 R2

IP ADDRESS:192.168.2.188                              IP ADDRESS:192.168.2.199

HOST NAME:beijing                                     HOST NAME:suzhou

ORACLE SID: orcl                                      ORACLE SID: orcl

TNSNAMES:bj                                           TNSNAMES:sz

安装盘符:F盘                  安装盘符:E盘

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

0.源数据库服务器上新建备份目录

mkdir f:\backup

mkdir f:\dump

1.源数据库和目标数据库检测

set oracle_sid=orcl

col name heading '实例名' for a10 col version heading '数据库版本' for a15 col

platform_name heading '操作系统平台' for a30 col endian_format heading

'字节顺序' for a15

SELECT d.NAME, i.VERSION, d.PLATFORM_NAME, ENDIAN_FORMAT FROM

V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d, V$INSTANCE i WHERE tp.PLATFORM_NAME =

d.PLATFORM_NAME and d.DB_UNIQUE_NAME = i.INSTANCE_NAME;

2.源数据库新建测试表空间和用户

create tablespace ocp datafile 'F:\app\Administrator\oradata\orcl\ocp01.dbf'

size 100m autoextend on next 10m maxsize unlimited extent management local

autoallocate segment space management auto;

create user test identified by password default tablespace ocp temporary

tablespace temp;

grant dba to test;

create table t1 ( sid int not null primary key, sname varchar2(10) );

insert into t1 values(101,'wind'); insert into t1 values(102,'snow'); insert

into t1 values(103,'apple');

commit;

select table_name from dba_tables where tablespace_name='OCP';

3.源数据库检测是否自包含

conn sys/password@orcl as sysdba  --需要sys账户

exec dbms_tts.transport_set_check('OCP', TRUE , TRUE);

SELECT * FROM TRANSPORT_SET_VIOLATIONS;

4.生成可传输表空间集

4.1将表空间置为READ-ONLY

alter tablespace ocp read only;

4.2 使用Data Dump导出表空间集元数据

/** DIRECTORY 指定转储文件和日志文件所在的目录 DIRECTORY=directory_object

Directory_object用于指定目录对象名称.需要注意, 目录对象是使用CREATE

DIRECTORY语句建立的对象,而不是OS目录 Expdp scott/tiger DIRECTORY=dump

DUMPFILE=a.dump */

connect / as sysdba

create directory dump as 'f:\dump'; --必须创建此目录 f:\dump

drop directory dump;--删除

exit;

--在 cmd 中直接 执行此命令

expdp system/password@orcl directory=dump  dumpfile=ocp.dmp

transport_tablespaces=ocp

4.3 将生成的文件和表空间的数据文件拷贝到指定目录

copy f:\dump\* f:\backup\

copy F:\app\Administrator\oradata\orcl\ocp01.dbf  f:\backup\

4.4  设置表空间为read write

alter tablespace ocp read write;

5.目标数据库

5.1 新建目录

sqlplus / as sysdba

create directory dump as 'e:\dump'  --必须创建此目录 e:\dump

exit;

5.2 从源数据库拷贝数据

copy e:\dump\ocp01.dbf   E:\app\Administrator\oradata\orcl\ocp01.dbf

5.3 检查字符集

select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

5.4 导入元数据:由于目标数据库上没有用户test 因此

remap_schema=test:system迁入了system方案