Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    22

    Question Unanswered: commiting with database links

    I am using database links to copy a whole schemas data in another database.

    e.g.

    inset into db1usr.table1 select * from db2usr.table1@the_link

    this works fine but as I have to copy 150 tables, some with 100,000+ rows in them, I think the roll back segments will fill up.

    how can I commit after each statement without doing a 'conn db1usr/password@db1', committing and then 'conn db2usr/password@db2'

    Idealy I would be using the COPY command but it has issues with the two different database verions (a 'fetch' error) and Oracle has told me to upgrade so they are the same version. Until I can do this I have to use the db link method.

    any ideas gratefully received

    Andy

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: commiting with database links

    Originally posted by ecynaxt
    I am using database links to copy a whole schemas data in another database.

    e.g.

    inset into db1usr.table1 select * from db2usr.table1@the_link

    this works fine but as I have to copy 150 tables, some with 100,000+ rows in them, I think the roll back segments will fill up.

    how can I commit after each statement without doing a 'conn db1usr/password@db1', committing and then 'conn db2usr/password@db2'

    Idealy I would be using the COPY command but it has issues with the two different database verions (a 'fetch' error) and Oracle has told me to upgrade so they are the same version. Until I can do this I have to use the db link method.

    any ideas gratefully received

    Andy
    When you run:

    inse[r]t into db1usr.table1 select * from db2usr.table1@the_link

    you will be logged into db1, where the insert is being done, so just COMMIT.

    In any case, COMMIT works accross database links.

  3. #3
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    22
    I think I wrote that the wrong way round. I am inserting into the remote linked db from a select on the local db. I will try commiting and seeing if it works accross the links.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •