Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    49

    Unanswered: improving data copy

    hello everybody,
    i have to copy data from some tables to some backup tables on an Oracle 9i server. some tables have more than one million of records.
    does anybody have any hint to perform a fast and secure copy?
    bulk copy for example, how does it work?

    thank you very much,
    matte

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try create table as select... with the nologging option.

    Alan

  3. #3
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    If you want to take backup of entire table, an export/import would be a good option.
    Oracle can do wonders !

  4. #4
    Join Date
    Aug 2003
    Posts
    49
    Originally posted by AlanP
    try create table as select... with the nologging option.
    Alan

    Ok, but the problem is i have to perform some operations during the copy. one of the field has to be substituted by its transcode value (got by a join on another table).
    do you think the following can work?

    CREATE OR REPLACE PROCEDURE nrows_at_a_time (p_array_size IN PLS_INTEGER DEFAULT 100)
    IS

    TYPE ARRAY IS TABLE OF table_origin%ROWTYPE;
    l_data ARRAY;

    CURSOR c IS SELECT * FROM table_origin;

    BEGIN
    OPEN c;
    LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO table_target VALUES (l_data.field1(i), l_data.field2(i), transcodeValueOf(l_data.field3(i)) );

    EXIT WHEN c%NOTFOUND;
    END LOOP;
    CLOSE c;
    END nrows_at_a_time;
    /


    or do you think it's better to use "create table as select..." with the nologging option and then to update field3 of the created table with the new value (coming from a lookup table)?


    thankx
    matte

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Well you could put your transcode function in the select clause of create table as (CTAS). Your procedure also looks OK but I suspect the CTAS way is quicker and easier.

    As with all these things I would try it out, if only to see what difference if any there is in your environment.

    Alan

  6. #6
    Join Date
    Aug 2003
    Posts
    49
    it works fine putting transcode function in the select clause of create table as (CTAS).

    thankx a lot,
    matte

Posting Permissions

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