Oracle Data Migration Reference

I. Oracle database data migration:
A. Data Migration Method
1、import and export exp.exe/imp.exe;
Advantage: strong compatibility. You can export custom objects;
2、data pump Import and Export (oracle10g.pdf to upload expdp.exe/impdp.exe;
Advantage: Faster than exp.exe/imp.exe;
Disadvantage: You need to specify Directory for import and export (you need to create a new Directory, which may have high permission requirements );
3. SQLLODER import and export;
Advantage: It can be imported from text files;
4. Use ODBC to import and export data;
5. Use the insert into method through DBLINK;
Ii. data migration between heterogeneous databases:
A. oracle transparent gateway technology:
1. Install the 10201_gateways_win32 transparent network management component;
2. Add SID_LIST_LISTENER to the listener. ora file:
(SID_DESC =
(PROGRAM = tg4sybs)
(SID_NAME = tg4sybs)
(ORACLE_HOME = E: \ oracle \ product \ 10.2.0 \ db_1)
)

Tg4sybs is the sybase component, while tg4msql is the SQL server component;
3. Add the following content to the tnsnames. ora file:
SYBS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = renx) (PORT = 1521 ))
)
(CONNECT_DATA =
(SID = tg4sybs)
)
(HS = OK) (notify oracle to use heterogeneous services here)
)

4. Restart the listener:
Lsnrctl stop;
Lsnrctl start;
B. Data migration through ODBC:

Iii. Migration functions of some database software:
A. PL/SQL Developer:
1. Tools-> Export Tables <> apply to the exp.exe/export SQL statement (insert );
2. Tools-> Import Tables <> export imp.exe/import from SQL statement (insert );
3. Tools-> ODBC Importer <> corresponding ODBC import;
4. Tools-> Text Importer <> corresponding SQLLODER data import;
5. Tools-> Export User Object <> Export DDL scripts;
6. Tools-> Data Generator <> Generate Test Data;

B. Toad? For Oracle:
1. Database-> EXPORT-> Data Pump Export Wizard <> corresponding Data Pump EXPORT expdp.exe;
2. Database-> EXPORT-> Data Subset Wizard <> mode replication (assign the corresponding values of one user to another );
3. Database-> EXPORT-> Export DDL <> EXPORT selected object DDL statements (commonly used for data cutover );
4. Database-> EXPORT-> Export Utility Wizard <> EXPORT exp.exe;
5. Database-> EXPORT-> Export File Browser <> browse exported DMP files;
6. Database-> EXPORT-> Generate Database Script <> EXPORT Database Script (used to create a similar Database)
7. Database-> EXPORT-> Generate Schema Script <> EXPORT the DDL Script of the entire user mode;
8. Database-> EXPORT-> Export Table as Flat File <> EXPORT data in SQLLODER format;
9. Database-> IMPORT-> Data Pump Import Wizard <> IMPORT to impdp.exe
10. Database-> IMPORT-> Import Source Files <> IMPORT the script file (*. SQL .., *. pkb ............)
11. Database-> IMPORT-> Import Table Data <> IMPORT Data from the txt \ xls \ dbf \ mdb file to the specified TABLE;
12. Database-> IMPORT-> Import Utility Wizard <> IMPORT imp.exe;
13. Database-> IMPORT-> SQL * Loader Wizard <> corresponding SQLLODER IMPORT;

C. Embarcadero DBArtisan (ORACLE \ SYBASE \ SQL SERVER)
1. Utilities-> Data Unload Wizard <> export Data to a text file;
2. Utilities-> Data Load Wizard <> import a database from a text file (SQLLODER );
3. Utilities-> Data Export Wizard for Oracle <> Export exp.exe; (ORACLE)
4. Utilities-> Data Import Wizard for Oracle <> Import imp.exe; (ORACLE)
5. Utilities-> schema extraction wizard <> export the DDL script of the entire user mode;
6. Utilities-> schema migration wizard <> transfers the entire user mode to another database user;