Geek Answers Handbook

My Oracle view uses a table that does not exist, but I can still query it

I have an Oracle view that uses a table that I cannot find anywhere. However, I can still request a presentation that I thought would be impossible.

Is the view content cached somewhere since the table still exists, or am I just not looking enough for the table?

Just to be clear: I looked at ALL_TABLES, and ALL_OBJECTS and the table (or something else) are not displayed in.

+4
source share
5 answers

It is very possible. Providing a choice in a view does not give a choice in the underlying tables. This allows me to create a view that provides a couple of columns from a table that I don't want you to see everything. You must have access to the table so that it appears in the ALL_TABLES view. If this is really a table, you can find it in the DBA_TABLES view (assuming you have access to the DBA_TABLES view), which has everything, not just the tables on which you have privileges.

In fact, the ALL_TABLES view is a great example of this situation. I bet you also can't find the tables used in this view, since you probably don't have rights to the SYS tables on which it is based (e.g. SYS.user $, SYS.obj $, etc.) .

+6
source

Also check to see if the “missing” table is synonymous with:

SELECT table_owner, table_name FROM all_synonyms WHERE table_name = 'MISSING_TABLE';

If this is not a synonym, try looking in the view for the word all_tables for your table:

 SELECT owner, table_name FROM all_tables WHERE table_name = 'MISSING_TABLE';
+5
source

Check the table schema for table references in a view that you cannot find - this is more likely not the current schema, but the current schema has SELECT privilege (at least) in the specific table.

Once you recognize the schema, this should help determine if the table is really a view in the current schema. Or it can be a synonym that exists in the current scheme - with an open synonym the same in all schemes, so you will need to check the synonyms to see what it points to.

+1
source

Perhaps this is a materialized look? This is a copy of the data so that it continues to exist even if the underlying table has been deleted.

+1
source

I would make sure you didn't do this by saying

 Create Table "TableName" ("ColumnName" Number(10,0))....

If you then try to reference them with the following script:

 Select ColumnName from TableName....

it will not work. This is because Oracle accepts names without quotes, such as TableName, and turns them into a TABLENAME that does not exist when you declared it with the "TableName" on which you acted: "Only respond to queries with such an exact capitalization which are also in quotation marks "

Thus, the following would be done:

 Select "ColumnName" from "TableName"....

But basically, you should never use case-sensitive definitions because they require every request to be quoted.

Instead of this

 Create Table TableName (ColumnName Number(10,0))....

and he will respond to any capitalization that you want to use when requesting

0
source

More articles:


All Articles