Create an Oracle Linked Server

Using GUI, choose Oracle Provider for OLE DB, product name as OraOLEDB.Oracle(same as oracle provider for OLE DB), data source is the tnsname.

Going to Security and provide user and password in remote login section

In Server options, enable RPC and RPC out. Or for the provider itself, enable the Inprocess.

--this is an automatically generated script by SSMS
USE [master]
GO

/****** Object:  LinkedServer [ORADB1]    Script Date: 13/11/2013 2:47:47 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ORADB1', @srvproduct=N'OraOLEDB.Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'ORADB1'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORADB1',@useself=N'False',@locallogin=NULL,@rmtuser=N'scott',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO