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

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>
复制