如何使用DTS将 SQL server 2000的 DB 移植到 Oracle 10g?

cheng_qinglin 2011-03-15 01:25:18
希望提供比较详细的操作步骤和注意事项:

附:Sql server 2000 创建数据库脚本

CREATE DATABASE db_advertise;
GO

USE db_advertise;
GO

CREATE TABLE t_popedom(
popedom_id SMALLINT NOT NULL,
popedom_type SMALLINT NOT NULL,
popedom_name VARCHAR(50) NOT NULL,
popedom_description VARCHAR(50),
PRIMARY KEY(popedom_id)
);
GO

INSERT INTO t_popedom(popedom_id, popedom_name, popedom_type) VALUES(1,'用户管理', 0);
INSERT INTO t_popedom(popedom_id, popedom_name, popedom_type) VALUES(2,'备份管理', 0);
INSERT INTO t_popedom(popedom_id, popedom_name, popedom_type) VALUES(3,'日志管理', 0);
INSERT INTO t_popedom(popedom_id, popedom_name, popedom_type) VALUES(4,'接口管理', 0);
INSERT INTO t_popedom(popedom_id, popedom_name, popedom_type) VALUES(5,'客户管理', 1);
INSERT INTO t_popedom(popedom_id, popedom_name, popedom_type) VALUES(6,'合同管理', 1);
INSERT INTO t_popedom(popedom_id, popedom_name, popedom_type) VALUES(7,'报表管理', 1);
INSERT INTO t_popedom(popedom_id, popedom_name, popedom_type) VALUES(8,'素材管理', 2);
INSERT INTO t_popedom(popedom_id, popedom_name, popedom_type) VALUES(9,'分组管理', 3);
INSERT INTO t_popedom(popedom_id, popedom_name, popedom_type) VALUES(10,'分组信息播发', 3);
INSERT INTO t_popedom(popedom_id, popedom_name, popedom_type) VALUES(11,'禁播管理', 3);
INSERT INTO t_popedom(popedom_id, popedom_name, popedom_type) VALUES(12,'禁播信息播发', 3);
INSERT INTO t_popedom(popedom_id, popedom_name, popedom_type) VALUES(13,'排期编辑', 4);
INSERT INTO t_popedom(popedom_id, popedom_name, popedom_type) VALUES(14,'初级审核', 4);
INSERT INTO t_popedom(popedom_id, popedom_name, popedom_type) VALUES(15,'终极审核', 4);
INSERT INTO t_popedom(popedom_id, popedom_name, popedom_type) VALUES(16,'应急播发', 4);
GO

CREATE TABLE t_role(
role_id SMALLINT NOT NULL,
role_name VARCHAR(20) NOT NULL,
role_popedomlist VARCHAR(30) NOT NULL,
role_description VARCHAR(50),
PRIMARY KEY(role_id)
);
GO

INSERT INTO t_role(role_id, role_name, role_popedomlist) VALUES(1,'系统管理员', '1,2,3,4');
INSERT INTO t_role(role_id, role_name, role_popedomlist) VALUES(2,'操作员', '5,6,7,8,9,10,11,12,13');
INSERT INTO t_role(role_id, role_name, role_popedomlist) VALUES(3,'审核员', '14,15');
INSERT INTO t_role(role_id, role_name, role_popedomlist) VALUES(4,'应急操作员', '16');
GO

CREATE TABLE t_user(
user_name VARCHAR(40) NOT NULL,
user_roleid SMALLINT NOT NULL,
user_popedomlist VARCHAR(30) NOT NULL,
user_password VARCHAR(40) NOT NULL,
user_realname VARCHAR(20),
user_email VARCHAR(30),
user_phoneno VARCHAR(30),
user_operator VARCHAR(40),
user_time DATETIME,
user_remark VARCHAR(100),
PRIMARY KEY(user_name),
FOREIGN KEY (user_roleid) REFERENCES t_role(role_id)
);
GO

INSERT INTO t_user(user_name, user_roleid, user_popedomlist, user_password) VALUES('admin', 1, '1,2,3,4', '81dc9bdb52d04dc20036dbd8313ed055');
GO

CREATE TABLE t_log(
log_id INTEGER NOT NULL IDENTITY,
log_type SMALLINT NOT NULL,
log_datetime DATETIME NOT NULL,
log_source SMALLINT NOT NULL,
log_user VARCHAR(50),
log_module SMALLINT,
log_description VARCHAR(100) NOT NULL,
log_ipaddress VARCHAR(16),
log_remark VARCHAR(100),
PRIMARY KEY(log_id)
);
GO

CREATE TABLE t_tsid(
tsid_id INTEGER NOT NULL,
tsid_frequency INTEGER NOT NULL,
tsid_pid INTEGER NOT NULL,
tsid_bandulimit INTEGER NOT NULL,
tsid_delayulimit INTEGER NOT NULL,
tsid_bandwidth INTEGER NOT NULL,
tsid_ismultiplex INTEGER NOT NULL,
PRIMARY KEY(tsid_id)
);
GO

INSERT INTO t_tsid (tsid_id, tsid_frequency, tsid_pid, tsid_bandulimit, tsid_delayulimit, tsid_bandwidth, tsid_ismultiplex) VALUES(65536, 0, 0, 0, 0, 0, 0);
INSERT INTO t_tsid (tsid_id, tsid_frequency, tsid_pid, tsid_bandulimit, tsid_delayulimit, tsid_bandwidth, tsid_ismultiplex) VALUES(65535, 0, 0, 0, 0, 0, 0);
INSERT INTO t_tsid (tsid_id, tsid_frequency, tsid_pid, tsid_bandulimit, tsid_delayulimit, tsid_bandwidth, tsid_ismultiplex) VALUES(65534, 0, 0, 0, 0, 0, 0);
INSERT INTO t_tsid (tsid_id, tsid_frequency, tsid_pid, tsid_bandulimit, tsid_delayulimit, tsid_bandwidth, tsid_ismultiplex) VALUES(65533, 0, 0, 0, 0, 0, 0);

CREATE TABLE t_serviceid(
sid_id INTEGER NOT NULL,
sid_tsid INTEGER NOT NULL,
sid_networkid INTEGER NOT NULL,
sid_ishdonly INTEGER NOT NULL,
sid_channel VARCHAR(30) NOT NULL,
sid_isforbid SMALLINT NOT NULL,
sid_forbidtype VARCHAR(50),
sid_forbidstarttime DATETIME,
sid_forbidendtime DATETIME,
PRIMARY KEY(sid_id, sid_tsid, sid_networkid),
UNIQUE(sid_channel),
FOREIGN KEY(sid_tsid) REFERENCES t_tsid(tsid_id)
);
GO

CREATE TABLE t_client(
client_id INTEGER NOT NULL IDENTITY,
client_name VARCHAR(80) NOT NULL,
client_trade VARCHAR(30),
client_type SMALLINT NOT NULL,
client_phone VARCHAR(50),
client_address VARCHAR(100),
client_contact VARCHAR(50),
client_contactphone VARCHAR(50),
client_info VARCHAR(200),
client_operator VARCHAR(40),
client_time DATETIME,
client_remark VARCHAR(200),
PRIMARY KEY(client_id),
UNIQUE(client_name)
);
GO

CREATE TABLE t_advertisetype(
advertisetype_id INTEGER NOT NULL,
advertisetype_ishighdefinition INTEGER NOT NULL,
advertisetype_class INTEGER NOT NULL,
advertisetype_maintypeid INTEGER NOT NULL,
advertisetype_programrelate INTEGER NOT NULL,
advertisetype_name varchar(50) NOT NULL,
advertisetype_desription VARCHAR(100),
PRIMARY KEY(advertisetype_id),
);
GO

.....太多了。。。。。。。。。。。
...全文
93 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
王向飞 2011-03-21
DTS也很方便
以后直接改数据源的链接字符串就可以。
适合大数据量转移

回复
cheng_qinglin 2011-03-21
我还是选择了手动建库、建表,更改了脚本。

以后就可以直接用脚本来自动创建了。
回复
cheng_qinglin 2011-03-16
需要移植后,现有的系统 程序代码几乎不用做任何改动,DTS自动转换后不能满足这个要求.

(表名称、表结构、默认表数据)

最后的选择就是手动建库、建表。

希望有更高效的办法。
回复
csouth 2011-03-15
DTS到oracle一个是数据类型可能出错,还有生成的表名都是带了双引号的。可能会遇到不少问题,小心点为好。
回复
王向飞 2011-03-15
有DTS还不会用吗?

拖俩数据源过来,一个设成MSSQL 一个选择ORACLE

再拖若干个数据流连接他俩

每个数据流选一张表,配置转换。

。。。。就差不多了
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2011-03-15 01:25
社区公告
暂无公告