INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Move records from one database to the other

Move records from one database to the other

(OP)
I want to move records from one database to another for archiving purposes.

So I created a db link and a synonym on archiving server pointing to the production server like this:

CREATE Public Database Link Prod_Link Connect to myuser IDENTIFIED BY "mypass" Using 'MyTnsName';

CREATE Public SYNONYM DocsProd FOR myuser.Docs@Prod_Link;

I want to run the following sql statements on archiving server to move the records:

INSERT INTO myuser.Docs
SELECT * FROM DocsProd WHERE ID<1000000;

DELETE FROM DocsProd WHERE ID<1000000;
COMMIT;


I have two questions:
1. Would it be faster to do it the other way around? What I mean is create db link on Production Server to point to the archive and run the sql on production server.
2. How can I be sure that the transaction is rollbacked if an error occurs during the insert or delete statement?

RE: Move records from one database to the other

1. I'm not sure it will make much difference; you will probably just need to try it both ways to see.
2. If you set this up with both statements in the same transaction and do a rollback if there is an error in either statement, it will roll back.

I would caution against basing your query on a record count - there is no guarantee you will delete the same records you copied off (unless, of course, you have less than 1000000 rows in the table!). A better gauge would be a date or primary key value.

Finally, if you are going to be taking out up to 1000000 rows at a time, you might also want to consider rebuilding your indexes when you are done.

RE: Move records from one database to the other

It will make a big difference. If you are running from the archive machine, use the following queries. if you are running from the production site you do not need the hint.


INSERT INTO myuser.Docs
SELECT * /*+ DRIVING_SITE(DocsProd) */ FROM DocsProd WHERE ID<1000000;

DELETE /*+ DRIVING_SITE(DocsProd) */ FROM DocsProd WHERE ID<1000000;
COMMIT;

Bill
Lead Application Developer
New York State, USA

RE: Move records from one database to the other

(OP)
I also added the following line in top of the sql script and run it using sql plus (to make sure that the whole process is a transaction):

whenever sqlerror exit failure rollback

RE: Move records from one database to the other

Bill -
If the transactions do not involve joins or sorting, how will the performance differ? Please understand that I am not questioning your answer, but trying to learn something here.

As I understand it, if you are pulling the data, the local database spawns off a session on the remote database. The remote session then transmits the data back to the local session, which inserts it into a table. When pushing, the local database spawns a session on the remote database and sends it data, which the remote database session inserts into a table. So in either case, we have a session on each database, one of which is running a query and sending data to the other, which is inserting into tables. The machines are the same in either case and we are using the same network in either case. So where does the performance difference come from?

I could understand differences if there were joins or sorts involved, but for a simple query on one table I do not see where the variable is. But my knowledge in this area is rudimentary and I'm always anxious to learn from somebody with more experience!

Thanks.

- Dave Bridges
Black Forest, CO

RE: Move records from one database to the other

your right, it matters if you have joins not just a simple where clause. However with joins it makes a tremendous difference. I had a select going to a remote database with joins on the remote tables that was taking 2 hours to run. I added the driving site hint and it went down to 5 seconds.

Bill
Lead Application Developer
New York State, USA

RE: Move records from one database to the other

Bill -
Thanks - that's a good trick to know. We've had similar issues (several years ago) and found it was easier to set up landing zones on the local database consisting of temporary tables that only contained the columns we needed, dragging great bloody hunks of the remote tables over to the local side and then doing the joins locally. But the hint seems like a much cleaner method!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close