CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user854577
user854577

Reputation:

Oracle thinks table doesn't exist during alter

I'm trying to alter a column on a table with this syntax:

ALTER TABLE MY_SCHEMA.latest_workspace MODIFY (short_name VARCHAR2(10) NOT NULL);

This is the error I'm getting:

Error starting at line 1 in command: ALTER TABLE MY_SCHEMA.latest_workspace MODIFY (short_name VARCHAR2(10) NOT NULL) Error report: SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" *Cause:
*Action:

However, I can physically see the table just fine and query it with: 

SELECT short_name FROM MY_SCHEMA.latest_workspace;

Also, all the correct permissions are granted to the user that I am logged in with. Is there something else I'm forgetting that can cause this for an ALTER?

Thanks!

Upvotes: 3

Views: 10662

Answers (4)

Siddharth Sharma
Siddharth Sharma

Reputation: 1

I had this problem too. In my case it turned out to be a SYNONYM.After running post to post this is what I did to fix it.

First check if the table exists

SELECT table_name FROM user_tables WHERE table_name = 'latest_workspace';

It returned blank.

Then check if the table exists in another schema

SELECT owner, table_name FROM all_tables WHERE table_name ='latest_workspace';

It returned a long list of owner but my owner(SCHEMA) was not in the list.

Then I checked for SYNONYM

SELECT owner,table_owner, table_name FROM all_synonyms WHERE synonym_name = 'latest_workspace';

It was a hit.

Then I used the table_owner in my alter statement to execute it.

Hope this helps!

Upvotes: 0

Fernando Genta
Fernando Genta

Reputation: 1

I had this problem too, but it was because the tables were in different schemas. I dropped one table and created it in the same schema, so it worked.

Upvotes: 0

Juan Bonsiepe
Juan Bonsiepe

Reputation: 1

The only way I found to solve this problem was recreate the table. (Rename, drop constraints and indexes, recreate the original table, insert records).

Can´t find the reason, but sounds me like a bug.

Upvotes: 0

APC
APC

Reputation: 146229

It would appear MY_SCHEMA.latest_workspace is not a table. It might be a view, or perhaps it's a synonym to an object in some other schema. Try this query to find out:

select object_type
from   user_objects
where object_name = 'LATEST_WORKSPACE';

The action you need to take subsequently depends on the result.

Upvotes: 4

Related Questions