誇平臺多個schame同時匯入匯出
SQL> col file_name for a45
SQL> set linesize 200
SQL> set pagesize 200
SQL> r
1* select file_name,tablespace_name from dba_data_files

FILE_NAME TABLESPACE_NAME
--------------------------------------------- ------------------------------
/u01/app/oracle/oradata/test/users01.dbf USERS
/u01/app/oracle/oradata/test/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/test/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/test/system01.dbf SYSTEM
/u01/app/oracle/oradata/test/example01.dbf EXAMPLE
/u01/app/oracle/oradata/soraeuc/gguser.dbf TBS_GGUSER
/u01/app/oracle/oradata/test/tbs01.dbf TBS1
7 rows selected.

SQL> create user pan1 identified by oracle default tablespace TBS1;
User created.

SQL> grant connect,resource to pan1;
Grant succeeded.

SQL> grant select on scott.emp to pan1;
Grant succeeded.

SQL> conn pan1/oracle;
Connected.
SQL> create table test1 as select * from scott.emp;
Table created.

SQL> create tablespace tbs2 datafile'/u01/app/oracle/oradata/test/tbs02.dbf' size 10m
autoextend on next 10m;
Tablespace created.

SQL> create user pan2 identified by oracle default tablespace tbs2;
User created.

SQL> grant connect,resource to pan2;
Grant succeeded.

SQL> grant select on scott.dept to pan2;
Grant succeeded.

SQL> conn pan2/oracle
Connected.
SQL> create table dept1 as select * from scott.dept;
Table created.
SQL> select * from dept1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
目標資料庫
匯入schame失敗，因為目標庫裡面沒有這裡pan1，pan2兩個使用者
[oracle@solaris102:/export/home/oracle/dump_dir]$ imp system/oracle file=pan_1_2.dmp
fromuser=pan1,pan2 log=pan_1_2_imp.log
Import: Release 11.2.0.3.0 - Production on Wed Apr 2 20:28:41 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
. importing PAN1's objects into PAN1
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
. importing PAN2's objects into PAN2
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
Import terminated successfully with warnings.
[oracle@solaris102:/export/home/oracle/dump_dir]$ imp system/oracle file=pan_1_2.dmp fromuser=
(pan1,pan2) touser=(pan1,pan2) log=pan_1_2_imp.log
Import: Release 11.2.0.3.0 - Production on Wed Apr 2 20:34:56 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing PAN1's objects into PAN1
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
. importing PAN2's objects into PAN2
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
Import terminated successfully with warnings.

在目標資料庫新建使用者pan1 pan2
idle>conn / as sysdba
Connected.
sys@TESTDB>create user pan1 identified by oracle;
User created.

sys@TESTDB>create user pan2 identified by oracle;
User created.

sys@TESTDB>grant resource,connect to pan1;
Grant succeeded.

sys@TESTDB>grant resource,connect to pan2;
Grant succeeded.

這裡的touser可以不指定，如果匯入的和匯出的使用者名稱相同且已經建立，如果匯入到另外的已經建立的使用者則必須指定
[oracle@solaris102:/export/home/oracle/dump_dir]$ imp system/oracle file=pan_1_2.dmp fromuser=
(pan1,pan2) log=pan_1_2_imp.log

Import: Release 11.2.0.3.0 - Production on Wed Apr 2 20:39:15 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
. importing PAN1's objects into PAN1
. importing PAN2's objects into PAN2
. importing PAN1's objects into PAN1
. . importing table "TEST1" 14 rows imported
. importing PAN2's objects into PAN2
. . importing table "DEPT1" 4 rows imported
Import terminated successfully without warnings.

sys@TESTDB>select * from pan1.test1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.

sys@TESTDB>select * from pan2.dept1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
注：匯入成功
1，可見匯入的時候必須在目標庫建立相應的使用者，當然在full模式下oracle會自動建立使用者。
2，在生產環境下最好建立表空間，指定使用者的預設表空間。

誇平臺多個schame資料遷移（exp,imp)

相關文章