Right, here is my question.
I was playing with some ETL tool which created some tables in my Oracle XE. Then I notice I could neither access those tables nor drop them.
As you can see in the screenshot(well I couldn't post the image so I had to copy / paste the text). Querying "dba_tables" shows that table, but when dropping it, Oracle says "does not exist".
SQL> select table_name from dba_tables where owner='HR';
TABLE_NAME
------------------------------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
DetailedOrders
COUNTRIES
8 rows selected.
SQL> drop table HR.DetailedOrders cascade constraints;
drop table HR.DetailedOrders cascade constraints
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
I also tried: Create an empty table with the same name -- success! Then drop it -- success! Query dba_tables or user_tables, it is still there!
Is it a know issue? How can I fix it?
Thanks,
Jeremy
What user are you logged in as when you execute these commands?
Assuming that you are logged in as a user other than HR (which it sounds like you probably are), it makes sense that you can find out that a table exists by querying DBA_TABLES but that you don't have any privileges to do anything with the table. That would imply that your account has been granted something like the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role but hasn't explicitly been given privileges on hr.DetailedOrders. The error that you get when you attempt to do something with a table that you have no privileges on is an ORA-00942 and the error message is designed so that it doesn't inadvertently give an attacker information about tables that may exist in the database by telling you whether the problem is that the table really doesn't exist or that it does exist and you don't have privileges. That can make the error a bit confusing particularly if you happen to be using an account that has been granted access to DBA_TABLES.
What do you mean by "creating an empty table with the same name"? Are you talking about creating a table DetailedOrders in the current schema? If so, that would also be expected to work (as would dropping the table). Each schema is a separate namespace. So you can have one DetailedOrders object in every schema in the database if you would like. Some of them could be tables, some could be views, some could even be stored procedures-- of course, this wouldn't make a whole lot of sense, but it can certainly be done.
Additionally, it appears that the DetailedOrders table was created as a case-sensitive identifier. This is a terrible idea. Oracle defaults to using case-insensitive identifiers but can be force to use case-sensitive identifiers if you surround an identifier with double-quotes. Here is a StackOverflow thread that discusses case-sensitive vs. case-insensitive identifiers in more detail. The short answer, though, is that if you do in fact have the ability to drop the table, you would need to do something like
DROP TABLE HR."DetailedOrders"
where the name of the table is enclosed in double quotes and the casing matches the casing you see in the data dictionary. You'd also have to do this every time you queried the table. Lots of tools don't handle case-sensitive identifiers well and future developers will curse your name if you create case-sensitive identifiers in their databases. But you can do it.