ive always used the oracle driver from the client install, but the previous poster is right...you might need to install the driver first:

SET @provider = N'ORAOLEDB.Oracle' --optionally 'MSDAORA' to use the MS driver

here's my code for my linked oracle server:

--#################################################################################################

--Linked server Syntax for Oracle 10G

--#################################################################################################

DECLARE @server sysname,

@srvproduct nvarchar(256),

@provider nvarchar(256),

@datasrc nvarchar(100),

@location nvarchar(100),

@provstr nvarchar(100),

@catalog sysname,

@sql varchar(1000)

--add an access Database as a linked server

SET @server = N'MyOracle' --this is your ALias

SET @srvproduct = N'Oracle'

SET @provider = N'ORAOLEDB.Oracle' --optionally 'MSDAORA' to use the MS driver

SET @datasrc = N'SFMN10G' --this is the SID

set @provstr = ''

EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr

-- exec sp_dropserver AccessDb

exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle',

@useself = N'FALSE',

@locallogin = 'sa',

@rmtuser = N'CHANGE_ME',--oracle username

@rmtpassword = 'NotARealPassword' --oracle password

--list all the tables and their names

EXEC sp_tables_ex 'MyOracle'

GO

--EXEC dbo.sp_DropServer 'MyOracle', 'DropLogins'

--list all the tables and their names

EXEC sp_tables_ex MyOracle

GO

select * from MyOracle..CHANGE_ME.SOMETABLE

select * from MyOracle..MFHDS.SOMETABLE