多用户数据迁移exp imp

目的：

oracle从A机器数据迁移到B机器上

环境：

A机器：

Windows 2003 + Oracle Database 10g 10.2.0.1.0

B机器：

            Windows 2003 + Oracle Database 10g 10.2.0.1.0

 

以sys用户登录，先查询出数据文件位置：

select * from dba_data_files;

 

一：建立表空间

 

CREATE SMALLFILE TABLESPACE "TS1"

DATAFILE 'E:/ORACLE/PRODUCT/10.2.0/ORADATA/M1BI/TS101.DBF'

SIZE 500M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

CREATE SMALLFILE TABLESPACE "TS2"

DATAFILE 'E:/ORACLE/PRODUCT/10.2.0/ORADATA/M1BI/TS201.DBF'

SIZE 500M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

 

 

CREATE SMALLFILE TABLESPACE "TS3"

DATAFILE 'E:/ORACLE/PRODUCT/10.2.0/ORADATA/M1BI/TS3.DBF'

SIZE 500M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

 

二、创建用户

 

create user USER1

  identified by "user1"

  default tablespace TS1

  temporary tablespace TEMP

  profile DEFAULT;

-- Grant/Revoke role privileges

grant connect to user1;

grant resource to user1;

-- Grant/Revoke system privileges

grant create view to user1;

grant unlimited tablespace to user1;

 

 

-- Create the user

create user user2

  identified by "user2"

  default tablespace TS2

  temporary tablespace TEMP

  profile DEFAULT;

-- Grant/Revoke role privileges

grant connect to user2;

grant resource to user2;

-- Grant/Revoke system privileges

grant create view to user2;

grant unlimited tablespace to user2;

 

 

-- Create the user

create user user3

  identified by "user3"

  default tablespace TS3

  temporary tablespace TEMP

  profile DEFAULT;

-- Grant/Revoke role privileges

grant connect to user3;

grant resource to user3;

-- Grant/Revoke system privileges

grant unlimited tablespace to user3;

 

A机器：

三：exp三个用户的数据

1.   如果一个用户一个用户导入导出(前提要知道各个用户的密码)，请注意导入导出权限设置 

Grant exp_full_database to ***;

Grant imp_full_database to ***;

 

exp user1/user1@test owner=user1 FILE="D:/backup/exp_20100310_user1.dmp" LOG="d:/backup/exp_20100310_user1.log"

 

exp user2/user2@test owner=user2 FILE="D:/backup/exp_20100310_user2.dmp" LOG="d:/backup/exp_20100310_user2.log"

 

exp user3/user3@test owner=user3 FILE="D:/backup/exp_20100310_user3.dmp" LOG="d:/backup/exp_20100310_user3.log"

 

 

2.   用system用户导出三个用户

 

exp system/oracle@test direct=y buffer=3000000 owner=user1 FEEDBACK=1000 FILE='D:/backup/exp_20100310_user1.dmp' LOG='d:/backup/exp_20100310_user1.log'

 

exp system/oracle@test direct=y buffer=3000000 owner=user2 FEEDBACK=1000 FILE='D:/backup/exp_20100310_user2.dmp' LOG='d:/backup/exp_20100310_user2.log'

 

exp system/oracle@test direct=y buffer=3000000 owner=user3 FEEDBACK=1000 FILE='D:/backup/exp_20100310_user3.dmp' LOG='d:/backup/exp_20100310_user3.log'

 

四、imp三个用户

1. 用system导入三个用户

 

imp system/oracle@test buffer=3000000 fromuser=user1 touser=user1 ignore=y FEEDBACK=1000 FILE='D:/backup/exp_20100310_user1.dmp' LOG='d:/backup/imp_20100310_biuser.log'

 

imp system/oracle@test buffer=3000000 fromuser=user2 touser=user2 ignore=y FEEDBACK=1000 FILE='D:/backup/exp_20100310_user2.dmp' LOG='d:/backup/imp_20100310_borep.log'

 

imp system/oracle@test buffer=3000000 fromuser=user3 touser=user3 ignore=y FEEDBACK=1000 FILE='D:/backup/exp_20100310_user3.dmp' LOG='d:/backup/imp_20100310_user3.log'

 

 

 

附录：

全库导入

imp sys/mima

FILE="D:/2word.DMP"

LOG="D:/exp_2word.log"

FULL=Y

FEEDBACK=1000

 

全库导出：

exp sys/mima

FILE="D:/备份/hy13.36 0303.DMP"

LOG="D:/备份/imp_hy13.36 0303.log"

FULL=Y

IGNORE=Y

 

先在B上建立表空间，然后再在B上建立用户，然后从A库上exp数据，再导入到建立好的B库上。。

 

新库建好，先建表空间，再建用户，之后按照用户导入

EM默认地址： http://*********:1158/em