1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Copy command in SQL Plus

Discussion in 'SQL PL/SQL' started by viachan, Jun 22, 2011.

  1. viachan

    viachan Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Hong Kong
    Hi,

    I want to ask a question about COPY command in SQLplus. (Oracle 9i)

    My script file: C:\script.txt
    -- --------------------------------------------------------------------------------------------
    -- Backup table of People
    COPY FROM docsadm/password@service1 Create People_Backup using select * from PEOPLE;
    commit;

    -- Backup table of Profile
    COPY FROM docsadm/password@service2 Create Profile_Backup using select * from PROFILE;
    commit;
    /
    -- ---------------------------------------------------------------------------------------------

    SQL:> @C:\script.txt;
    People_Backup : created successfully. (shown on screen)
    But Profile cannot be created. The prompt hangs on. No any response.
    I haven't any syntax error. I don't know why it will hang on. I have been waiting about 45 minutes.

    The TABLE of PROFILE was using by about 3000 peoples currently. When users insert a file, a record will be written to the table of PROFILE.
    I give up to wait anymore. By pressing CTRL+C twice to stop the compiler to execute.
    Finally, two tables cannot be created. Maybe the slash has not been executed.

    THen I used another method to backup PROFILE table , Create TABLE tableName As Select * from Profile
    It works well . It only takes about 2 seconds to backup 80,000 records.
    Anyone could help me ?
    Although I have a workaround to solve my problem, I really want to know why it will hang on.
    Any thoughts/ideas/suggestions/links will be highly appreciated.

    Thanks and best regards,
    Via
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Copy command

    This statement

    COPY FROM docsadm/password@service2 Create Profile_Backup using select * from PROFILE;

    is not the same as this statement

    Create TABLE tableName As Select * from Profile

    as the second is executed locally and the first is executed across a database link. I also notice that you're using two different database links to create these tables (service1, service2) -- is this intentional? Have you checked how long it takes to connect to service2 versus how long it takes to connect to service1? With the lack of information you've provided (meaning you didn't provide much which would be useful) it's imossible to tell you why one takes seemingly forever and the other executes in a reasonable amount of time.

    You need to post items like the tnsping output for service1 and for service2, wait times for the commands (available in v$sesstat) and possibly network transport information from your PC or server to the source db servers (your network admin can help with that data).
     
  3. viachan

    viachan Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Hong Kong
    Re: Copy command

    Hi David,
    Sorry , service2 should be service 1. They are the same.
    It connects to service1 about 1-2 seconds.

    COPY FROM docsadm/password@service1 Create People_Backup using select * from PEOPLE;
    It takes about 30 seconds to copy 3000 records.

    Sorry, I am just a newie in Oracle sql. I don't know what is tnsping output for service1 .
    I want to ask how to know the timeout of a session ?
    I cannot find the network transport information from my PC or server to the source db servers.
    The information is on client side, I am hard to execute and find such things. Their information is confidential.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Copy command

    How long does this take to execute:

    select * from profile@service1;
     
  5. viachan

    viachan Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Hong Kong
    Re: Copy command

    I have testing the time taken at testing environment.
    select * from profile; ---> About 1 minute

    WHy I wait 45 minutes for
    COPY FROM docsadm/password@service2 Create Profile_Backup using select * from PROFILE;

    It was because
    COPY FROM docsadm/password@service1 Create People_Backup using select * from PEOPLE;
    It takes about 1 minute to complete.
    There are 2597 records in PEOPLE.

    And there is 122781 records in PROFILE.

    122781/2597 = 47 minutes

    So I was waiting over 45 minutes in order to let it have time to complete execution.

    However, I cannot wait anymore as I don't know when will it be completed. I haven't any time in the client side. There were 4 scripts I needed to continue to execute in order complete my task. My time is limited. My client did not have patience to wait anymore.

    I give up. And use another syntax to complete the backup.
    Create TABLE tableName As Select * from Profile
    It takes me 2 seconds. OH~ I release at that moment.

    Why there is so big differences between COPY command and Create Table command ?
    What is the mechanism inside them ?

    I am very frustrated at that moment. Fortunetly I found another workaround.
    I really want to know what's wrong with my coding.
    The copy command works well at the testing environment, but hangs on at the production environment.

    Thanks a lot !
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Copy command

    "Why there is so big differences between COPY command and Create Table command ?
    What is the mechanism inside them ?"

    Your create table command is 'local' to the database; yout copy table is located in the same schema, in the same database, as the source. COPY, the way you're using it, accesses data across a database link which takes time to complete (I've said this before; did you miss this information in a prior response?).

    "I am very frustrated at that moment. Fortunetly I found another workaround.
    I really want to know what's wrong with my coding.
    The copy command works well at the testing environment, but hangs on at the production environment."

    It doesn't hang, it takes time to process the volume of data present in production. Again, it's using a database link, which adds more time to the process. There is nothing wrong with your 'coding'; your concept of how fast COPY should work needs adjustment, however, as it's accessing a remote database (hence the database link). It won't necessarily work as fast as 'create table ... as select ...' since the select in that statement is using a local table.
     
    viachan likes this.
  7. viachan

    viachan Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Hong Kong
    Re: Copy command

    Thank you very much.
    Now I know there is a big difference between remote and local database.
    Thanks for your professional explanation.