ORA-00942: table or view does not exist

kris007 · Post by **kris007** » Thu Jun 08, 2006 10:31 am

Hi All,

I have few jobs which were running fine until yesterday. When ran yesterday the jobs aborted giving an error message

Code: Select all

ALARIS_BEST_CUSTOMERS_EXT..CUSTOMERS_LASTUPDATED: ORA-00942: table or view does not exist
ALARIS_BEST_CUSTOMERS_EXT..CUSTOMERS_LASTUPDATED.DSLink1: DSP.Open GCI $DSP.Open error -100.

And YES, I am logging in with the correct credentials. The SQL statement I have in the OCI stage works correctly when I execute it from TOAD or SQLPlus, but when I try to view the data from within the OCI stage it throws me the same error again.
All my column derivations are correct, I am passing the correct parameters for the schema name and using correct login credentials and the user has all the grants required. I can see the tables in the Database.
Now, what bothers me is, Just to be sure, I wanted to re-import the metadata for these tables and run the job. But, to my surprise, when I try to import the metadata from the Manager, I don't see the tables in the select tables window anymore when I connect to the database, but I could see the tables in the database. I am also connecting to the correct database and NO, they are not VIEWS. They are tables.

Last time I faced this error, it was either a grants issue or schema issue or something like that. I have checked for all those possibilites but its not.

Has anyone come across a similar situation? Any guidance would be of great help.
Thanks
Kris.

DSguru2B · Post by **DSguru2B** » Thu Jun 08, 2006 10:34 am

Kris, you need to get in touch with you DBA and ask him if your id was revoked permission to the system tables. As you said, you cannot see those tables even while doing a import. That clearly means those tables are transparent to DataStage. Something must have changed on the database end.

ArndW · Post by **ArndW** » Thu Jun 08, 2006 10:35 am

Have you tried to login to TOAD or your favorite tool with the same id and seeing if you get the same problem when looking at the DDL for the table or when issuing the same query?

kris007 · Post by **kris007** » Thu Jun 08, 2006 10:41 am

ArndW wrote:Have you tried to login to TOAD or your favorite tool with the same id and seeing if you get the same problem when looking at the DDL for the table or when issuing the same query?

I can execute the same query from TOAD and SQLPlus successfully. I can even see the data. But, from datastage..No.I am also the Owner of the table. Even, when I login as Owner of the table I can't view the data from the OCI stage. and Yeah..I can't see those tables from the Manager too.
So, it's kind of confusing. I can see the data in the database but not from DataStage.

DSGuru2B,
Before I talk to DBA, I just want to make sure if I am missing out something. So was hoping if anyone who faced a similar situation might show some direction?

kcbland · Post by **kcbland** » Thu Jun 08, 2006 11:18 am

Just to recap, DS doesn't honor synonyms when importing metadata. You have to choose the schema where the table lives. Since it's Oracle, you have to deal with "roles" as well, maybe something is different there?

kris007 · Post by **kris007** » Thu Jun 08, 2006 11:29 am

No Ken, Everything seems correct. Because, as I mentioned earlier in the post, this happened when I tried to 're-import' the metadata.i.e I have successfully imported the metadata once before and ran the jobs fine. But, now I dont see the table names in the Manager or the data from the OCI stage. If something changed on the database side, I believe that I should not be able to see those tables even from the database when I login.

When I attempted to connect to the database through the Datastage Manager I got the following error

Code: Select all

DATABASE Attribute not found (DSCMDGetAttrIndexByName)

It happened only once. Not sure if that leads us to anywhere?

kris007 · Post by **kris007** » Thu Jun 08, 2006 11:31 am

I am sorry I forgot to answer your question. I am loggin in as the owner while importing the metadata and also choosing the schema where the table is present.

rwierdsm · Post by **rwierdsm** » Thu Jun 08, 2006 11:39 am

It may seem obvious, however, looking through the thread, I don't see you explicitely mention that it is the same user id in TOAD as it is in DataStage. Is your DataStage job using job parameters, is it possible that the user name is set to something else?

Sorry if the question seems trivial, but it often (for me anyways

) seems in these cases that it's something 'stupid' that is causing the problem.

kcbland · Post by **kcbland** » Thu Jun 08, 2006 11:58 am

kris007 wrote:When I attempted to connect to the database through the Datastage Manager I got the following error
Code: Select all
DATABASE Attribute not found (DSCMDGetAttrIndexByName)
It happened only once. Not sure if that leads us to anywhere?

Well, this certainly is a clue that something is messed up. Either your Manager client session is whacked or something is corrupted.

Any chance the TNSNAMES.ORA on the server is different than that on your PC? Maybe the server is pointing elsewhere and you can't tell because of the connection information differs?

Could you create a new table and see if it shows up? If DS can't see any tables, that's different than a specific set of tables can't be seen.

kris007 · Post by **kris007** » Thu Jun 08, 2006 12:00 pm

rwierdsm wrote: Sorry if the question seems trivial, but it often (for me anyways ) seems in these cases that it's something 'stupid' that is causing the problem.

Yeah, I agree that these things happen neglecting simple issues as you have mentioned but in this case its not true. I have crosschecked everything and searched for possible answers on the forum and then posted my thread. I don't have any idea what's causing this problem.

kris007 · Post by **kris007** » Thu Jun 08, 2006 12:58 pm

kcbland wrote: Could you create a new table and see if it shows up? If DS can't see any tables, that's different than a specific set of tables can't be seen.

DS is not able to view only a set of tables. I observed this one other schema too.

I created a new table and I can't see it from the DataStage Manager either. I have created another table in another schema and I can't see that table too from datastage manager. One of the tables in another schema is also missing when I try to see it from Manager. It seems, that the DBA's are creating a new DEV environment and move everything from the existing environment into the new one. May be that should change some things, but for now I don't know what is happening

kcbland · Post by **kcbland** » Thu Jun 08, 2006 1:10 pm

Methinks it's time to talk to a DBA (which means Don't Bother Asking)

kris007 · Post by **kris007** » Thu Jun 08, 2006 4:42 pm

kcbland wrote:Any chance the TNSNAMES.ORA on the server is different than that on your PC? Maybe the server is pointing elsewhere and you can't tell because of the connection information differs?

That was what exactly was happening. Thanks Ken for pointing in that direction. What happened behind the doors was as I mentioned the DBA's were moving the DEV environment and also, the DS admin has updated the tnsnames.ora file and I didn't update it on my PC. I was not able to see only those tables which I created after they have moved the enviroment and updated the tnsnames.ora file. So, it all got mixed up. Anyways, now I am done with it.

Once again Thanks to Ken all others for their useful suggestions.
Kris.