dblink error ORA-00942 & ORA-02063

[thomgreen]

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?

[PAVB]

Can you please post your Create DBLINK statement as well as the Select statement you are using to test them?

[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.

[sambavan]

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.

[thomgreen]

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.

[marist89]

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.

[dbasan]

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?

[isaias]

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..

[isaias]

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.