同一实例下，不同用户之间的数据同步，
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>
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.