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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.