Post updated November 12, 2018.
The answer is simple – you can’t see any tables, because you don’t OWN any tables.
I hate to be the bearer of bad news, but you don’t have any tables. What you mostly likely DO have are SYNONYMS that point to tables in an application schema. When you log on to Oracle, you are seeing this:
But wait you say – I queried something called ‘EMPLOYEES’, I should be able to see something?
I’m guessing if you expanded your SYNONYMS list, you would see something…
A SYNONYM is like a shortcut or pointer or link..it allows you to reference an object in the database by a different name. In this case EMPLOYEES in your SCHEMA – the collection of objects own by your Oracle user account – points to a TABLE in HR called EMPLOYEES.
In the screenshot above, when you open a SYNONYM you can get all the details. And if you click on the blue-underlined text, it will go open that table for you so you can work with it directly.
Just Show Me Stuff I can Query!
Mouse-right click on your Tables tree node, and select ‘Apply Filter…’
With this checked, bam!
We’re now not just querying TABLEs from ALL_OBJECTS, we’re now ALSO including SYNONYMS for any TABLEs.
Note: someone will have had to create a SYNONYM in your schema – this won’t pull in TABLES for PUBLIC SYNONYMS. Because…there are thousands of those, and no one wants to see all of that.Note: This is also available for views.
Still Don’t Believe Me?
Trust, but verify. Great words to live by. And if you’re reading a blog post on a technical subject, I would advise you doubt and double-check.
Let’s query the USER_OBJECTS view!
SELECT OBJECT_NAME, OBJECT_TYPE, CREATED FROM USER_OBJECTS;
We’ll Let you Treat These Objects as Tables
The cool part is that SQL Developer is going to treat those synonym objects as tables now that they’re in the Tables node. So all of the Table goodies are available to you, including context menus and drag-and-drop mouse operations.
Note: these aren’t YOUR tables, you can just see them AS tables. You won’t be able to query these tables or make any data or DDL changes to these tables if the SELECT, INSERT, ALTER, … or whatever privileges haven’t been granted to your account.
Why Can’t I Just Login as the Application Owner?
It’s a pretty common security scheme to create a user with very low privilege levels and then only grant SELECT on objects they should see or to create local synonyms to objects they need to access.
This protects the data and application itself, AND it protects you from accidentally screwing things up in the database.
The ‘trick’ is just to remember to ask SQL Developer to show you SYNONYMS under your TABLEs & VIEWs nodes.
But Wait, Why Can’t I See Tables in Other Schemas Either?
Because you don’t have at least READ or SELECT privs on those tables.
For ‘normal’ (non-DBA) users, when we expand the ‘Other Users’ node, and go into another schema, and list tables from there – we are querying the ALL_OBJECTS views. These views list things in the database that you have the security rights to see. For tables, this is controlled by the SELECT priv.
So if you have the SELECT priv on a table, that table will show up in the ALL_TABLES view.
Here’s a scenario.
- create role – SEE_HR
- grant select hr.employees, hr.locations, hr.departments to SEE_HR
- create user DOES_NOT_OWN_ANYTHING
- grant SEE_HR to DOES_NOT_OWN_ANYTHING
- connect as DOES_NOT_OWN_ANYTHING
- query ALL_OBJECTS and browse the HR schema
Earlier I said if you were a ‘normal user’ … Well, if you’re a DBA level account, you can query the DBA_OBJECTS views. These VIEWS are much quicker to select from, because they assume you can see EVERYTHING because you’re a dba. There’s no security checks to see if my account can ‘see’ an object, therefore populate that into the DBA_OBJECTS views (Docs).
So when you connect in SQL Developer, the first thing we do is figure out if you can use the DBA_ views. If you can, we will, and you’ll browse the entire database very efficiently.