Access similar database on different servers

Hello,
I want to access a similar or same database from one server to another without using a database link.

The user and password are the same just the server names are different.

My dba says this can be done without a database link, as long as the user name and password are the same.

Example.
Test1 server:
username - as_build   password - scuttle

Prod1 server:
username - as_build   password - scuttle

sql example:
Insert into test1.as_build.part_master
select * from prod1.as_build.part_master;


Is this possible?

Thanks,
MG
MachinegunnerAsked:
Who is Participating?
 
Walter RitzelSenior Software EngineerCommented:
Please take a look on this, maybe is what you need:
http://docs.oracle.com/cd/B10501_01/server.920/a90842/apb.htm
0
 
Walter RitzelSenior Software EngineerCommented:
Yes, it is possible, which does not mean that this is exactly what you want. Can you share what exactly is your requirement, in detail?
0
 
Walter RitzelSenior Software EngineerCommented:
And, by the way, I dont know if the server name could be part of the query.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MachinegunnerAuthor Commented:
I am particularly the owner of both schema's right now and I need to move some particular parts data from production into the test environment to test code on.
I do not have sys dba rights on either database.  I asked for a public or other database link but the dba says, "since you have the same username and password, you should be able to retrieve the data from Prod1".

Thanks for the reply
0
 
MachinegunnerAuthor Commented:
Yes, I was just providing an example of what server that the data is being retrieved and inserted into, not
the syntax that is to be used.

Thanks
0
 
Walter RitzelSenior Software EngineerCommented:
Ok, so clarifying:
1) You have 2 different servers: Test1 and Prod1.
2) On both servers, you have a schema called as_build, which you have the password (and it is the same on both servers)

Ok, so you dont need a db link: you can connect on Prod1 server, execute the query you need, export the data in a convenient format and then connect on Test1 server, and import the information. Off course, this is kind of manual, but you could use a batch script of some sort to automate that.
0
 
MachinegunnerAuthor Commented:
Yes, this will be a manual process where I can execute the insert script when ever it is necessary.
Also, the table - part_master have the exact column names and data types, so no conversions necessary.

If I only had around a hundred rows, I would do a simple export with the DDL insert statements but there are
a couple of hundred thousand rows.

scenario:
truncate the as_build.part_master table on server test1

insert the prod1 as_build.part_master data into the test1 side of things.

Thanks
0
 
Walter RitzelSenior Software EngineerCommented:
In this case, the way I know would be through a db link or using an external ETL tool like Pentaho, Power Center, etc...
0
 
MachinegunnerAuthor Commented:
Something like this then?

copy from as_build/scuttle@prod1 to as_build/scuttle@test1 -
replace part_master -
using select * from part_master

Thanks
0
 
Walter RitzelSenior Software EngineerCommented:
Yes, it is something like this.
0
 
MachinegunnerAuthor Commented:
I'll try it tomorrow, thanks for the information and replies!  Much appreciated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.