ITPub博客

首页 > 数据库 > Oracle > 夸平台多个schame数据迁移(exp,imp)

夸平台多个schame数据迁移(exp,imp)

原创 Oracle 作者:fei890910 时间:2014-04-02 20:49:22 0 删除 编辑

夸平台多个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,在生产环境下最好建立表空间,指定用户的默认表空间。
                                                                                                 




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29108064/viewspace-1134951/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-08-15

  • 博文量
    120
  • 访问量
    754502