--#################################################################################################
--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