sqlser附件如何迁移到oracle,sql server迁移到oracle

1Software Requirement

(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.

2Usage 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

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 ZQPROFILEDEFAULT

IDENTIFIED BY ZQDEFAULT 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 REWRITETO ZQ ;

GRANT CONNECTTO 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.

3import 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;