DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: copy

  1. #1
    Join Date
    May 2002
    Posts
    232
    Hiiii, I get a problem when i try to copy the file from remote database to mydatabase.
    I was connected to remote DB.
    then i try to copy I get a error

    SQL> COPY FROM SGHDEV/SGHDEV@SGH TO WEBDBDEV/WEBDBDEV@SGH.DIG
    > CREATE RANG (FROM_MOBTEL,TO_MOBTEL) -
    > USING SELECT * FROM PREPAID_RANGE;

    Array fetch/bind size is 15. (arraysize is 15)
    Will commit when done. (copycommit is 0)
    Maximum long size is 80. (long is 80)

    ERROR:
    ORA-12154: TNS:could not resolve service name

    any one can help me--
    this one urgent
    thanks
    kavitha
    kavitha

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    As the error message says, the service name could not be resolved. This means that the tnsnames.ora entry for one or both of the instances is not present or is incorrect. Correct your tnsnames.ora file and try again. To determin which one it is try to connect to both from SQL*Plus. Whichever you can't connect to is the problem.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  3. #3
    Join Date
    May 2002
    Posts
    37
    I should add. that in this case oracle uses tnsnames.ora on your DB server, not the one that you have on your workstation.
    So, make sure you have correct entries in tnsnames.ora on your db server.

    Regards.

  4. #4
    Join Date
    Jan 2001
    Posts
    3,134
    When you use SQL* COPY you must be connected to one of the databases involved, it does not matter which one but you MUST be connected to the source or target.

    Check you db_links too.

    MH
    I remember when this place was cool.

  5. #5
    Join Date
    Jan 2001
    Posts
    3,134
    Sorry kavithared;
    I was looking at your syntax and it appeared that you were not connected to one of the DB's. I am pretty sure your syntax is incorrect, unfortunately I am not in the office and do not have access to my files.

    I know that there is a copy of a COPY.DOC out there somewhere, I posted it several times on this website.

    MH
    I remember when this place was cool.

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by Mr.Hanky
    When you use SQL* COPY you must be connected to one of the databases involved, it does not matter which one but you MUST be connected to the source or target.

    Check you db_links too.

    MH
    not true, you can connect from any database to perform COPY, however if you dont wanna generate twice network traffic you should do it from one of the two database you are moving the data

    For example I have 3 instances running, dev817, rep817 and dev920

    Code:
    I am connected to dev920 and goingto move data between dev817 and rep817 (tried from 9.2.0 to 8.1.7 but got ORA-00904: invalid column name, guess bug?)
    
    SQL> select instance_name from v$instance;
    
    INSTANCE_NAME
    ----------------
    dev920
    
    set copycommit 2
    set arraysize 1000
    
    SQL> copy from lsc/lsc@dev817 to lsc/lsc@rep817 create dept using select * from dept
    
    Array fetch/bind size is 1000. (arraysize is 1000)
    Will commit after every 2 array binds. (copycommit is 2)
    Maximum long size is 80. (long is 80)
    SQLRCN in cpytbl failed: -1075
    Table DEPT created.
    
       5 rows selected from lsc@dev817.
       5 rows inserted into DEPT.
       5 rows committed into DEPT at lsc@rep817.
    the data first travel from dev817 to dev920, i.e my session then from my session I send the data to rep817

  7. #7
    Join Date
    Jan 2001
    Posts
    3,134
    Then my O'Reilly book is incorrect, but rather than point that out, why not focus on the problem at hand?

    MH
    I remember when this place was cool.

  8. #8
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Use "tnsping" utility and test your service names @SGH, SGH.DIG first to see you get response or not and go from there...
    Reddy,Sam

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well his problem is he has not configured tnsnames.ora nothing more

    SGH node tnsnames.ora must have entries of SGH

    if you look my previous post:

    the data first travel from dev817 to dev920, i.e my session then from my session I send the data to rep817

    that means dev920 must be able to connect to dev817 and rep817

    if he logon to a rep817 session then rep817 must be able to see dev817 and so on

    For better performance you should issue copy from one of two nodes locally not from any remote sqlplus session or the network traffic will be generate twice

  10. #10
    Join Date
    May 2002
    Posts
    232
    Hiii,in tnsnames.ora,i was added remote host and sid.
    i was add a one listener for sgh.
    thanks
    kavitha
    kavitha

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