sql server迁移到oracle

1 Software Requirement

(1)Oracle SQL Developer: You can get the zip file by accessing http://download.oracle.com/otn/java/sqldeveloper/sqldeveloper-5440.zip ; or select a desired version at the http://www.oracle.com/technology/software/products/sql/index.html website.

(2)Download JDBC Driver that will be used to connect the SQL SERVER and SYBASE database (ORACLE adopts an open source, JTDS).  You can get the jtds- 1.2.2-dist.zip from http://jtds.sourceforge.net.

2 Usage of SQL DEVELOPER

(1) Install the Oracle SQL Developer.  After downloading the ORACLE SQL DEVELOPER, unzip it, access to the SQL DEVELOPER catalogue, and then double-click on the sqldeveloper.exe to run it. Note that it does not need to be installed.  You can simply double-click the sqldeveloper.exe to run the software.

(2)Configure the SQL Server JDBC Driver under the SQL Developer.  In SQL Developer, if you have not already installed the JTDS driver using Check for Updates (on the Help menu), do the following:

a)      Click Tools, then Preferences, then Database, then Third Party JDBC Drivers.

b)      Click Add Entry.

c)      Select the jar file for the JTDS driver you downloaded from http://jtds.sourceforge.net/.

d)      Click OK.

e)      In SQL Developer, click Tools, then Preferences, then Migration: Identifier Options, and make certain that the setting is correct for the Is Quoted Identifier On option.

(3)Create a target user in the Oracle who will inherit the data from SQL Server.

--/********************************************************************************************/

--Author: Rainny Zhong

--Date  : 2006-04-17

--DB Ver: Oracle 9.2.0.1.0

--Desc  : create user

--/********************************************************************************************/

CREATE USER ZQ   PROFILE  DEFAULT

    IDENTIFIED BY ZQ   DEFAULT TABLESPACE APP02

    TEMPORARY TABLESPACE TEMP

    QUOTA UNLIMITED

    ON APP02

    QUOTA UNLIMITED

    ON APP01

    QUOTA UNLIMITED

    ON INX01 

    QUOTA UNLIMITED

    ON INX02 

    QUOTA UNLIMITED

    ON USERS 

    ACCOUNT UNLOCK;

 

GRANT CREATE PROCEDURE TO ZQ ;

GRANT CREATE SEQUENCE TO ZQ ;

GRANT CREATE SNAPSHOT TO ZQ ;

GRANT CREATE SYNONYM TO ZQ ;

GRANT CREATE TABLE TO ZQ ;

GRANT CREATE TRIGGER TO ZQ ;

GRANT CREATE TYPE TO ZQ ;

GRANT CREATE VIEW TO ZQ ;

GRANT QUERY REWRITE  TO ZQ ;

GRANT CONNECT  TO ZQ ;

GRANT DBA TO ZQ;

GRANT CREATE OPERATOR TO ZQ;

GRANT CREATE INDEXTYPE TO ZQ;

GRANT RESOURCE TO ZQ WITH ADMIN OPTION;

 

Note: Make sure the permissions highlighted in RED above are granted to the newly created users.

 

(4)Start the Quick Migration.

 

(5)Select the target database ‘Oracle’.

(6)Create a target connection (user) in Orcale. Here is ‘zq’.

 

(7)Create REPOSITORY for Migration.

(8)Select the target database ‘zq’ as the REPOSITORY.

(9)Click On the Migration -> Quick Migrate…

(10)Quick Migration Wizard pops up.  Complete the settings step by step.

 

 

(11)Overview the Summary.  Make sure the settings are correct before click on Finish to start migrating.

 

(12)Start to migrate the data.

(13)Transmit data.

(14)Accomplish the migration procedure.

 

3 import the migrated data to the new user

(1)Export the migrated data.  The following is an example showing how to do this.  

--Author:   Rainny

--Date   :  2006-12-13

--Version: Oracle 10G

--1.create backup directory

create directory backup as '/data/exp';

grant read,write on directory backup to &user_name;

--2.use expdp

--in windows command line

expdp admin/tiger2000@ORA33 DUMPFILE=xpc71pilot_anf_hk_new.dmp DIRECTORY=BACKUP SCHEMAS= xpc71pilot_anf_hk_new JOB_NAME=exp_xpc71pilot_anf_hk_new logfile=exp_xpc71pilot_anf_hk_new.log

 (2)Import the migrated data to the new user. The following is an example showing how to do this.

--Author:   Rainny

--Date   :  2006-12-13

--Version: Oracle 10G

--1.use impdp

--in windows command line

impdp admin/tiger2000@ORA33 DUMPFILE=xpc71pilot_anf_hk_new.dmp DIRECTORY=BACKUP REMAP_SCHEMA= xpc71pilot_anf_hk_new:fitchtrack SCHEMAS=xpc71pilot_anf_hk_new JOB_NAME=imp_xpc71pilot_anf_hk_new logfile=imp_ xpc71pilot_anf_hk_new.log

 (3)Change the new user’s password. The following is an example showing how to do this. 

--If you did not create the ‘text’ user before, ORACLE will create it for you.  However, you should change its password so that it can be used to log into the database.

sqlplus “system/yourpassword@ora 33”s

sql>alter user fitchtrack identified by fitchtrack;

sql>conn fitchtrack/fitchtrack@ora33;