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

Thread: dblink error ORA-00942 & ORA-02063

  1. #1
    Join Date
    Jan 2001
    Posts
    66

    dblink error ORA-00942 & ORA-02063

    I have 3 databases, I will call them: db1,db2,db3

    o I created dblinks using the same ddl on db2 and db3 that allow me to access db1.
    o On db2 and db3 I am using a user with the same name and same privileges and roles - I will call the user bdops
    o On am trying to access a table (named delete_stuff) in db1 owned by the user bdopstage.
    o From db2 user bdops; I am able to access the 'delete_stuff' table on db1 with no problem.
    o From db3 user bdops; I am not able to access the 'delete_stuff' table on db1 with no problem.
    I get the following errors:
    ORA-00942: table or view does not exist
    ORA-02063: preceding line from DB1.WORLD@BDOPSTAGE
    :: I get this error when trying to access this new table created (delete_stuff). But I can access almost all of the other tables in the bdopstage schema using this dblink. It would seem as though it is a permission issue but I am having no problem accessing any tables using the dblink on db2.

    What would differentiate the 2 db's, db2 and db3, to make one dblink work for all tables and the other dblink to not work with all tables?

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Can you please post your Create DBLINK statement as well as the Select statement you are using to test them?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jan 2001
    Posts
    66
    CREATE PUBLIC DATABASE LINK BDWHDEV.WORLD@BDOPSTAGE
    CONNECT TO bdopstage IDENTIFIED BY
    USING 'bdwhdev'
    /

    select * from delete_stuff@BDWHDEV.WORLD@BDOPSTAGE;

    :: This is the same 2 pieces of code used on db2 that work just fine.

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    What is the global_names parameter set between the two dbs. Also just to make sure that your db link is working, can you do

    select sysdate from dual@bdwhdev.world

    Secondly

    try
    select count(*) from bdopstage.delet_stuff@bdwhdev.world.
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Jan 2001
    Posts
    66
    The global_names parameter is set to 'false' on all db's.

    Check out my original posting real quick. I can select from dual and almost all other tables... i can't select on the 'delete_stuff' table (through the dblink) nor can I select from other new tables that I create directly on that db1.

    I thought that it might be a permissions issue but I can access all tables through the dblink from db2. Is there any specifics about that particular table that would not allow me to select from db3?

    I figured that maybe the roles where not usable through a dblink so I made sure that the 'BDOPSTAGE' user that I was linking to had all of the necessary privileges directly granted to itself.

    I am truly confused by this.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by thomgreen
    I thought that it might be a permissions issue but I can access all tables through the dblink from db2. Is there any specifics about that particular table that would not allow me to select from db3?
    Hmm, that was my first thought as well. But, since you're connecting directly to the user on db1 (IMHO, a big no-no, but that's besides the point) I don't think that's it. To verify, login directly to db1 and see if you can get to the table.

    My second thought would be to verify that there are not more than one dblink with the same name; one public and one private.

    My third thought would be to verify that the tnsnames.ora point to the same place.

    My fourth thought would be to check synonyms and the like, but only as a last resort.
    Jeff Hunter

  7. #7
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650

    Post

    Quote Originally Posted by thomgreen
    CREATE PUBLIC DATABASE LINK BDWHDEV.WORLD@BDOPSTAGE
    CONNECT TO bdopstage IDENTIFIED BY
    USING 'bdwhdev'
    /

    select * from delete_stuff@BDWHDEV.WORLD@BDOPSTAGE;

    :: This is the same 2 pieces of code used on db2 that work just fine.
    can you post the same 2 piece of code used on db3, which does not work?

  8. #8
    Join Date
    Apr 2008
    Posts
    3
    I have this problem to.
    it is one mistery.. because the dblink was correctly created.

    See my case:

    I have one dblink correctly working because it is used for another procedures.

    I try to create one package body with procedures. This PL/SQL codes acess the Dblink, to insert data into tables of other database.

    In database 2, i grant the table for the other user.. this user its a user of dblink.

    well.. others procedures created.. with others tables workings.. but only two tables dont working. the message its.. Table or view does not exists....

    if i try execute the select of example:

    DELETE FROM MY_TABLE@MY_DBLINK WHERE
    id = MYROWTYPE.ID its work...

    but.. i use a rowtype property to insert this data ....

    INSERT INTO MY_TABLE VALUES ( myRowType.id, myRowType.propertyCLob1, myRowType.propertyCLob2, myRowType.propertyCLob3, myRowType.propertyCLob4, myRowType.otherId);

    its dont work.. but.. if a use fixed values.. its work.. example


    INSERT INTO MY_TABLE VALUES ( 1, 'text1', 'text2', 'text3', 'text4', 1);

    Where are the problem?

    In the rowtype... ( this rowtype are of one cursor.. this cursor execute one select into the accessed database.. with no dblink )

    In the dblink?

    In the grant of the objects???

    Thanks for help.

    Bad english.. my language its portuguese..

  9. #9
    Join Date
    Apr 2008
    Posts
    3
    If you are using a CLOB to resolve this problem, execute one insert with the clob field null.. and update with only clob fields defined.

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