数据仓库之抽取数据：通过链接服务器获取Oracle数据

最新推荐文章于 2022-01-26 06:48:59 发布

LongRui888

最新推荐文章于 2022-01-26 06:48:59 发布

阅读量1.5k

点赞数

分类专栏：【SQL 编程&商务智能BI】

本文链接：https://blog.csdn.net/sqlserverdiscovery/article/details/86701909

版权

【SQL 编程&商务智能BI】专栏收录该内容

82 篇文章 5 订阅

订阅专栏

在做数据仓库时，最重要的就是ETL的开发，而在ETL开发中的第一步，就是要从原OLTP系统中抽取数据到过渡区中，再对这个过渡区中的数据进行转换，最后把经过处理的干净的数据加载到数据仓库中。

目标数据库是sql server，现在要从异构的Oracle数据库中抽取数据。

步骤如下：

1、开启即席查询

--修改高级参数
sp_configure 'show advanced options',1
go
--允许即席分布式查询
sp_configure 'Ad Hoc Distributed Queries',1
go
--如果配置的值不在合理范围（在最小值最大值范围内），那么可以强制覆盖
reconfigure with override  
go

2、建立链接服务器，使用oracle的oledb库MSDAORA

EXEC sp_addlinkedserver 
	@server = 'OracleLinkedServer',    --要创建的链接服务器名称                
	@srvproduct = 'Oracle',            --产品名称
	@provider = 'MSDAORA',             --OLE DB 字符  
	@datasrc = 'orcl'                  --数据源
 
 
--创建连接服务器登陆
EXEC sp_addlinkedsrvlogin 
	@rmtsrvname = 'OracleLinkedServer',  --链接服务器名称
	@useself = 'false', 
	@locallogin = NULL,
	@rmtuser = 'scott',                  --Oracle服务器的登陆用户名 
	@rmtpassword = 'tiger'               --Oracle服务器的登陆密码

3、设置链接服务器属性

exec sp_serveroption @server='OracleLinkedServer',
                     @optname = 'rpc',      --从给定的服务器启动rpc
                     @optvalue ='true'      --这样可以调用远程的存储过程

exec sp_serveroption @server='OracleLinkedServer',
                     @optname = 'rpc out',  --对给定的服务器启动rpc 
                     @optvalue ='true'      --在远程执行DDL语句

4、在远程服务器上执行DDL语句新建表，用oracle的方式书写语句

exec('create table SCOTT.EMPXX(iddd int)') at OracleLinkedServer

5、用openrowset函数，把oracle的数据直接新建表的同时插入数据

select * 
INTO EMP_X 
from openrowset('MSDAORA','orcl';'scott';'tiger',SCOTT.EMP)

select * 
INTO EMP_X 
from openrowset('MSDAORA','orcl';'scott';'tiger','select * from SCOTT.EMP')

6、 opendatasource函数，查询oracle中的数据

select * 
from 
opendatasource('MSDAORA','Data Source=orcl;
                          User ID=scott;
                          password=tiger')..SCOTT.EMP