4

I have an oracle query SELECT * FROM My_Table which returns rows as expected.

I am trying to get information on some of the columns in the table however when I query All_Tab_Columns there are no columns where TABLE_NAME = 'My_Table'

I then queried ALL_TABLES and ALL_VIEWS but I couldn't find a table/view where TABLE_NAME = 'My_Table' or where VIEW_NAME = 'My_Table'.

What am i missing here? What else could My_Table be and how can I find information on the columns that are being returned when I query it?

Improve this question
3
  • 1
    table names are in uppcase. Commented Aug 19, 2015 at 18:08
  • 4
    Use all_objects. The object in question could, for example, be a synonym. By default, Oracle identifiers are case-insensitive but are stored in upper case in the dictionary. If you are searching for a mixed case table name, that would only work if you were using case-sensitive (double-quoted) identifiers for your object names. Commented Aug 19, 2015 at 18:09
  • @JustinCave - Thanks that did it. It is indeed a synonym. Commented Aug 19, 2015 at 18:19

2 Answers 2

Reset to default
4

In Oracle syntax is not case sensitive, but data is case sensitive. so you can query your table as following:

select * from all_tables where table_name like upper('%My_Table%')

if you did not find any data about table this could be synonym so you can query about it from all_synonyms view.

select * from all_synonyms where synonym_name like upper('%My_Table%')
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks. Like JustinCave's comment on the original post suggested i needed to query all_objects table. Since his was a comment and yours is an answer i'll mark yours as the correct answer
0

In Oracle, tables names (and all object names for that matter) are actually upper-cased internally by default. If you query the data dictionary using this WHERE clause, you should get results:

where TABLE_NAME = 'MY_TABLE'
Improve this answer

2 Comments

I tried that already and i still didn't get anything. I even queried all of ALL_TABLES and ALL_VIEWS and ordered them by TABLE_NAME and VIEW_NAME and couldn't find 'MY_TABLE'
Looks at Justin Cave's comment. It may not be a table or view then. Try all_objects as he suggests.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.