首页 » ORACLE 9i-23ai » oracle同一实例不同用户之间所有对象的数据导入

oracle同一实例不同用户之间所有对象的数据导入

同一实例下，不同用户之间的数据同步，
1，exp from a user,imp to b user,
2,impdp network_link,不生成dump文件直接导入
…
1,不多说，下面实验2 环境oracle 10g

sys@ORCL> conn anbob/anbob
Connected.
anbob@ORCL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
TESTSTOR                       TABLE
OBJ                            TABLE
TESTDEFTBS                     TABLE
TESTHWM                        TABLE
V_DL_TEST                      VIEW
TESTCUR                        TABLE
OPERATIONS                     TABLE
REPORT_PERIOD                  TABLE
REPORT_RECORD                  TABLE
SECURITY_ACCOUNT               TABLE
SECURITY_APPLICATION           TABLE
SECURITY_RESOURCE              TABLE
SECURITY_ROLE                  TABLE
TESTFBK2                       TABLE
TESTFBK1                       TABLE
BIN$p2UI3ewP2H7gQAB/AQANTw==$0 TABLE
SEQ                            SEQUENCE
TESTLOCK                       TABLE
TESTINT                        TABLE
TESTINTE                       TABLE
TESTASC                        TABLE
BASE_EQUIPMENT_SEQ             SEQUENCE
BASE_MEDICAMENT_CLASS_SEQ      SEQUENCE
BASE_MEDICAMENT_SEQ            SEQUENCE
BASE_MEDICAMENT_USED_SEQ       SEQUENCE
DATA_DIC_SEQ                   SEQUENCE
DATA_SOURCE_ITEM_SEQ           SEQUENCE
DATA_SOURCE_SEQ                SEQUENCE
DESEASE_SEQ                    SEQUENCE
DISEASE_DIAGNOSE_SEQ           SEQUENCE
DISEASE_SEQ                    SEQUENCE
EMPLOYEE_SEQ                   SEQUENCE
EMP_EDUCATION_SEQ              SEQUENCE
EMP_EXTEND_ATTR_SEQ            SEQUENCE
EMP_TRAIN_SEQ                  SEQUENCE
EQUIPMENT_SEQ                  SEQUENCE
FOMRS_SUBMIT_SEQ               SEQUENCE
FORMS_SEQ                      SEQUENCE
FORMS_SUBMIT_SEQ               SEQUENCE
FORM_DATA_SEQ                  SEQUENCE
FORM_TO_DIC_SEQ                SEQUENCE
OPERATIONS_DIAGNOSE_SEQ        SEQUENCE
OPERATIONS_SEQ                 SEQUENCE
ORGANIZATION_SEQ               SEQUENCE
REPORT_FREQUENCY_SEQ           SEQUENCE
REPORT_PERIOD_SEQ              SEQUENCE
REPORT_RECORD_SEQ              SEQUENCE
RESOURCE_SEQ                   SEQUENCE
SECURITY_RESOURCE_SEQ          SEQUENCE
SECURITY_ROLE_SEQ              SEQUENCE
TRAINS_SEQ                     SEQUENCE
UNDERGO_SEQ                    SEQUENCE
SECURITY_ACCOUNT_SEQ           SEQUENCE
NOT_MEDICAMENT_USED_SEQ        SEQUENCE
EMPLOYEE_UPDATE_LOG_SEQ        SEQUENCE
DATA_SOURCE                    TABLE
DISEASE                        TABLE
EMPLOYEE                       TABLE

58 rows selected.

Elapsed: 00:00:00.03
anbob@ORCL> conn system/oracle
Connected.
system@ORCL> create user weejar identified by weejar;

User created.

Elapsed: 00:00:00.10
system@ORCL> set timing off
system@ORCL> grant resource,connect to weejar;

Grant succeeded.

system@ORCL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


[oracle@orazhang admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orazhang)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)


[oracle@orazhang admin]$ hostname
orazhang
[oracle@orazhang admin]$ tnsping orcl

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-7月 -2011 13:21:40

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orazhang)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@orazhang admin]$ ora

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 7月 19 13:21:47 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@ORCL> create database link  dl_myself connect to system identified by oracle using 'orcl';

Database link created.

sys@ORCL> select sysdate from dual@dl_myself;

SYSDATE
-------------------
2011-07-19 13:22:50

sys@ORCL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@orazhang admin]$ impdp system/oracle network_link=lk_myself schema=anbob remap_schema=anbob:weejar
LRM-00101: unknown parameter name 'schema'

[oracle@orazhang admin]$ impdp system/oracle network_link=dl_myself schemas=anbob remap_schema=anbob:weejar

Import: Release 10.2.0.1.0 - Production on 星期二, 19 7月, 2011 13:25:18

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39200: Link name "dl_myself" is invalid.
ORA-02019: connection description for remote database not found


[oracle@orazhang admin]$ ora

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 7月 19 13:25:30 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@ORCL> drop database link dl_myself;

Database link dropped.

sys@ORCL> create public database link  dl_myself connect to system identified by oracle using 'orcl';

Database link created.

sys@ORCL> select sysdate from dual@dl_myself;

SYSDATE
-------------------
2011-07-19 13:26:21

sys@ORCL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@orazhang admin]$ impdp system/oracle network_link=dl_myself schemas=anbob remap_schema=anbob:weejar

Import: Release 10.2.0.1.0 - Production on 星期二, 19 7月, 2011 13:26:50

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_03":  system/******** network_link=dl_myself schemas=anbob remap_schema=anbob:weejar
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.25 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"WEEJAR" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "WEEJAR"."EMPLOYEE"                           3929 rows
. . imported "WEEJAR"."OBJ"                                   3 rows
. . imported "WEEJAR"."TESTLOCK"                          30019 rows
. . imported "WEEJAR"."TESTHWM"                           10000 rows
. . imported "WEEJAR"."REPORT_RECORD"                       897 rows
. . imported "WEEJAR"."SECURITY_ACCOUNT"                   1916 rows
. . imported "WEEJAR"."DATA_SOURCE"                           1 rows
. . imported "WEEJAR"."DISEASE"                             692 rows
. . imported "WEEJAR"."OPERATIONS"                          206 rows
. . imported "WEEJAR"."REPORT_PERIOD"                        33 rows
. . imported "WEEJAR"."SECURITY_APPLICATION"                  2 rows
. . imported "WEEJAR"."SECURITY_RESOURCE"                    20 rows
. . imported "WEEJAR"."SECURITY_ROLE"                         5 rows
. . imported "WEEJAR"."TESTASC"                               3 rows
. . imported "WEEJAR"."TESTDEFTBS"                            1 rows
. . imported "WEEJAR"."TESTFBK2"                              1 rows
. . imported "WEEJAR"."TESTCUR"                               0 rows
. . imported "WEEJAR"."TESTFBK1"                              0 rows
. . imported "WEEJAR"."TESTINT"                               0 rows
. . imported "WEEJAR"."TESTINTE"                              0 rows
. . imported "WEEJAR"."TESTSTOR"                              0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
ORA-39082: Object type ALTER_FUNCTION:"WEEJAR"."F_GETNAME" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"WEEJAR"."ISFOUND" created with compilation warnings
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:"WEEJAR"."COLLECT" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"WEEJAR"."COLLECT_PERSONNEL" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"WEEJAR"."P_TESTWRAP" created with compilation warnings
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-39082: Object type VIEW:"WEEJAR"."V_DL_TEST" created with compilation warnings
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
ORA-39082: Object type PACKAGE_BODY:"WEEJAR"."ANBOB" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_03" completed with 8 error(s) at 13:27:38



sys@ORCL> conn weejar/weejar
Connected.
weejar@ORCL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
BASE_EQUIPMENT_SEQ             SEQUENCE
BASE_MEDICAMENT_CLASS_SEQ      SEQUENCE
BASE_MEDICAMENT_SEQ            SEQUENCE
BASE_MEDICAMENT_USED_SEQ       SEQUENCE
DATA_DIC_SEQ                   SEQUENCE
DATA_SOURCE_ITEM_SEQ           SEQUENCE
DATA_SOURCE_SEQ                SEQUENCE
DESEASE_SEQ                    SEQUENCE
DISEASE_DIAGNOSE_SEQ           SEQUENCE
DISEASE_SEQ                    SEQUENCE
EMPLOYEE_SEQ                   SEQUENCE
EMPLOYEE_UPDATE_LOG_SEQ        SEQUENCE
EMP_EDUCATION_SEQ              SEQUENCE
EMP_EXTEND_ATTR_SEQ            SEQUENCE
EMP_TRAIN_SEQ                  SEQUENCE
EQUIPMENT_SEQ                  SEQUENCE
FOMRS_SUBMIT_SEQ               SEQUENCE
FORMS_SEQ                      SEQUENCE
FORMS_SUBMIT_SEQ               SEQUENCE
FORM_DATA_SEQ                  SEQUENCE
FORM_TO_DIC_SEQ                SEQUENCE
NOT_MEDICAMENT_USED_SEQ        SEQUENCE
OPERATIONS_DIAGNOSE_SEQ        SEQUENCE
OPERATIONS_SEQ                 SEQUENCE
ORGANIZATION_SEQ               SEQUENCE
REPORT_FREQUENCY_SEQ           SEQUENCE
REPORT_PERIOD_SEQ              SEQUENCE
REPORT_RECORD_SEQ              SEQUENCE
RESOURCE_SEQ                   SEQUENCE
SECURITY_ACCOUNT_SEQ           SEQUENCE
SECURITY_RESOURCE_SEQ          SEQUENCE
SECURITY_ROLE_SEQ              SEQUENCE
SEQ                            SEQUENCE
TRAINS_SEQ                     SEQUENCE
UNDERGO_SEQ                    SEQUENCE
DATA_SOURCE                    TABLE
DISEASE                        TABLE
V_DL_TEST                      VIEW
EMPLOYEE                       TABLE
OPERATIONS                     TABLE
REPORT_PERIOD                  TABLE
REPORT_RECORD                  TABLE
SECURITY_ACCOUNT               TABLE
SECURITY_APPLICATION           TABLE
SECURITY_RESOURCE              TABLE
SECURITY_ROLE                  TABLE
TESTCUR                        TABLE
TESTFBK1                       TABLE
TESTFBK2                       TABLE
TESTLOCK                       TABLE
TESTINT                        TABLE
TESTINTE                       TABLE
TESTASC                        TABLE
OBJ                            TABLE
TESTDEFTBS                     TABLE
TESTHWM                        TABLE
TESTSTOR                       TABLE

57 rows selected.

weejar@ORCL>
打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Rufus Stoeltzing | #1
    2011-12-21 at 07:26

    I became just browsing here and there and got to learn this post. I have to admit that we’re within the hand of luck today in any other case getting this kind of excellent post to learn to read wouldn’t happen to be achievable for me personally, at the very least. Really appreciate your content.

上一篇：

下一篇：