copying rows from DBlink into local table!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: copying rows from DBlink into local table!

  1. #1
    Join Date
    Jan 2002
    Posts
    96

    Unhappy

    Hi there guys, i am using the command, insert into table_name select * from table_name@DBLINK.

    when i run this script it shows for example 30 rows created. I am still busy running the script, cause i need to copy, +/- 100 tables.

    My problem is when i open another session of sql and do a select * statement on one of the tables that i know rows were created in it comes up with no rows? why? do i have to wait for all the rows to be created or the script to run befor i can do a select * from table?
    Share on Google+

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    The rows won't show up until the INSERT has been committed.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."
    Share on Google+

  3. #3
    Join Date
    Jan 2002
    Posts
    96
    I did add a commit to each one of the statements here is one of them

    insert into BILL_DATA_ERROR_LOG select * from BILL_DATA_ERROR_LOG@webdevcimdb commit;

    And another thing is it just freezes up, and says not responding in task manager. I know some of them are like really big, but will sqlplus only appear once it is finish copying the data?

    And i tried to add rollback segments, doesn't want to add, cause at the moment i only have the system rbs! Any views on this?
    Share on Google+

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by charlton
    I did add a commit to each one of the statements here is one of them

    insert into BILL_DATA_ERROR_LOG select * from BILL_DATA_ERROR_LOG@webdevcimdb commit;

    You want to make two different statements, like:
    insert into BILL_DATA_ERROR_LOG select * from BILL_DATA_ERROR_LOG@webdevcimdb;
    commit;


    And another thing is it just freezes up, and says not responding in task manager. I know some of them are like really big, but will sqlplus only appear once it is finish copying the data?

    And i tried to add rollback segments, doesn't want to add, cause at the moment i only have the system rbs! Any views on this?
    http://technet.oracle.com/docs/produ...1_res.htm#7132
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."
    Share on Google+

  5. #5
    Join Date
    Mar 2001
    Posts
    314
    Originally posted by charlton

    And another thing is it just freezes up, and says not responding in task manager. I know some of them are like really big, but will sqlplus only appear once it is finish copying the data?
    Set up a JOB.

    -amar
    Share on Google+

  6. #6
    Join Date
    Jan 2002
    Posts
    96
    I am still having problems with my roll Back segments. I still can't create them in oracle 9i.

    And when copying the data, even if i do a commit after all of the rows for the tables is created, when i do a select statement, it still come up with no rows selected.

    My dead line was friday, it is sunday, i have to finish it today... it is URGENT!
    Share on Google+

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    Hey Charlton;
    Have you tried the SQL* Copy command. Using that you can set up an arraysize and a copycommit. This way you will commit before the entire table is loaded, which is what you are trying to do now. This can be a problem with large tables since Oracle will attempt to load the whole table before it commits.
    My guess is that you are blowing out the rollback segs before you get a chance to commit, then Oracle rolls back your transaction.

    I do not have the syntax here but I know I posted it a while ago, I have a doc at work detailing it, unfortunately I am home now.

    Good luck

    MH
    I remember when this place was cool.
    Share on Google+

  8. #8
    Join Date
    Jan 2001
    Posts
    3,131

    Talking

    FOUND IT!!

    SQL* Plus
    Copy Command


    You must be connected to one of the databases involved. It doesn’t matter which, but you must be connected to either the database containing the data or the database to which you want to copy the data.


    SET ARRAY[SIZE] May be abbreviated to array.
    This parameter sets the number of rows fetched at one time. The default is 15. The allowed range is from 1 to 5000.

    SET COPYC[OMMIT] May be abbreviated to copyc.
    The copycommit setting works in conjunction with the arraysize setting. The arraysize setting controls the number of rows in a batch. The copycommit setting controls how many batches are copied before committing. The number or rows copied before each commit is equal to ARRAYSIZE * COPYCOMMIT.

    The default is 0 this means that by default COPY will only commit after the copy command is finished. This is a potential rollback disaster, please adjust this accordingly. You would not want to forget this on a very large table.

    Example
    SET ARRAYSIZE 15
    SET COPYCOMMIT 10
    COPY TO my_id@D135.oracledb.blah.com -
    INSERT my_id.table_name -
    USING SELECT * from table_name;

    Since the arraysize setting is 15 and the copycommit setting is 10, the COPY statement shown here will commit changes after every 150 rows (15 * 10).

    COPY
    The COPY command allows you to use SQL* Plus as a conduit for transferring data between two Oracle databases:

    COPY {FROM connection | TO connection}
    {APPEND | CREATE | INSERT | REPLACE}
    destination_table [(column_list)]
    USING select_statement

    Note; When using the COPY command In SQL*Plus you must code a hyphen (-) after each line to continue to the next line. The last line will end with a semi-colon (
    COPY is the command.

    FROM/TO
    If you are connected to the source database , use the TO option to specify the destination database. If you are connected to the target database, use the FROM option to specify the source of the data.

    Connection
    Is the login information to use when connection to the other database.
    Your_id@D135.oracledb.blah.com

    APP[END]
    Causes SQL*Plus to insert the copied rows into the destination table, creating it first if necessary.

    CRE[ATE]
    Causes SQL*Plus to copy the data only if the destination table is a new table.
    If the destination table already exists, the COPY command will abort.

    INSERT
    Causes SQL*Plus to insert the copied rows into the destination table only if it already exists. If the destination table is a new table, the COPY command will abort.

    REP[LACE]
    Causes SQL*Plus to drop the destination table if it currently exists. A new table is then created, and the data is copied.

    Destination_table
    Is the name of the table to which you want to copy.

    Column_list
    Specifies column names to use when the COPY command creates a new destination table. This is a comma delimited list, and the number of column names must match the number of columns in the SELECT statement.

    Select_statement
    Is a SELECT statement that returns the data you want to COPY.


    * Excerpts from Oracle SQL*Plus the definitive guide by Jonathen Gennick,
    O’Reilly Press.


    __________________
    I remember when this place was cool.
    Share on Google+

  9. #9
    Join Date
    Jan 2001
    Posts
    3,131
    One more thang.
    I'm not sure how many tables you need to copy, at least you could use this for some of the bigger ones and use the SELECT * FROM for the smaller ones.

    If you can not create more rollback segs just try to make the ones you have bigger.

    MH

    Hope you make it!!
    I remember when this place was cool.
    Share on Google+

Posting Permissions

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



Click Here to Expand Forum to Full Width