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.
94 Comments
Hi Jeff,
Checking the ‘Include Synonyms’ works a treat. IS there anything similar for packages? At the moment I have to go down to ‘Other Users’ and click on the schema owner.
Cheers
Danny
Just to follow any updates.
Issue with apex.oracle.com is that its a multi-tenant installation. To avoid people getting acces to customer / user data from customer A to customer B all regular grants and queries has been disabled.
You can use the Object Browser or SQL Commands in the SQL Workshop menu system.
This “knows” the multi-tenant nature of Apex and applies more strict access controls than regular SQL Developer can do.
Not sure about any issues…there’s no database access for apex.oracle.com in SQL Developer to begin with
Works like a charm, thank you
Hi, I have a similar trouble using apex cloud option on apex.oracle.com. I’m a green beginner. Unfortunately I can’t seem to find any tables too. No dept,or emp, no nothing. It’s just empty. Do you know how I could go about this?
There is no database connectivity available for the database behind apex.oracle.com – you can use their sql worksheet and tools build into apex to browse and work with your ‘schema’ objects though.
I have an odd situation, I got connected to the database, I’m connecting to a third party vendor.
I run the query
SELECT *
FROM MY_TABLE
WHERE ROWNUM <=5
I get 5 results,
When I run SELECT * FROM …
tabs (0 results)
dba_tables (invalid)
user_tables (0 results)
all_tables
I get 32 rows all owned by SYS, SYSTEM, or XDB
The odd part is the table I just got data from above (MY_TABLE ) is not there.
I know im missing something simple but just plain stuck, any suggestions are appreciated (Yes I looked in Views)
What is tabs?
dba_tables fails because you don’t have the required privileges
User_tables is 0 because your login user has no tables
All_tables 32 means there are 32 tables you have access to
Thank you so much for your response Jeff.
The confusion I am having is I see the data when I run Select * from My_Table however I dont see My_Table when I run SELECT * FROM ALL _TABLES. I just looked back and noticed I not querying “My_Table” Im querying “EMS.MyTable” which I think is causing the issue.
FYI, Tabs was something I found on the internet (Im trying anything :[)), Ive been out of development for a long time so trying to catch back up.
Thanks. The solution was helpful
I have been working with PLSQL Developer tool before, in which while writing queries, when you type apps. it shows all the tables and views, from which I can select. I am looking for that kind of drop down in SQL developer when I am writing queries, I can’t remember all the table names. like I type hr. it should show all the tables in it. Do we have any such thing in SQL developer?
yeah, of course we do
Thanks, I found the answer here:
https://stackoverflow.com/questions/12578591/oracle-sqldeveloper-table-name-autocompletion-setting
Cool, so you had turned if off and forgot about it, or you have more than 10 tables in HR?
Is there a way to use the same Tables filter across multiple connections? I have a somewhat complicated filter that I would rather not have to duplicate for DEV, QA, and production connections. I tried looking in the AppData folder and the registry (Windows 10 OS) for where the filter might be stored, but no luck.
No, not that I know of. Also, we don’t use the Windows Registry for anything.
My guess is that the xml file that stores the connection filters should be in the systemX.Y.Z…\o.sqldeveloper.12.2… folder, probably.
I can’t see the connection sidebar, what should I do sir?
View > Connections
If that doesn’t work, Windows > Reset Windows to Factory
Thank you sir, it works 🙂
Hi Jeff,
Can i execute (select * from table@mydblink) query on sql developer directly? If yes, then how? Since i could not find right article to execute this step. I need to test my queries on sql developer.
Thanks
you can run any valid query in SQL Developer – there’s nothing special about running queries that hit db_links in SQL Developer
A very usefull step, this take me almost 4 hours to discover this answer.
Thank you so much for the help.
This solution worked perfectly for me
Is there a way to log in as abc but under tables see the tables for def without having to go to the Other Users tree and scrolling down? I tried alter session set current_schema = def; but that didn’t work.
Thanks a lot. I had a problem to check if a table exists before droping it when using “user_tables” with a user, but it works fine using “all_objects”
You just saved my entire day with this – I was positive it was a firewall or a versioning issue.
Oh no, I hope you didn’t lose an entire day! I’m glad you got it sorted it out though. Please let me know if you need any help going forward.
How to get dummy tables with data ie tables such as departments,employees,countries,locations etc in sql developer as I only have Bonus,emp,salgrade table i need other tables too.
You’ll need to create the HR schrma. You should have some example scripts in your Oracle Home directory…check the Docs.
Hi Jeff,
So I did what you suggested by end up seeing the “(filtered)” next to the tables. Does it mean I have restricted access?
what did you do? i suggest a lot of things 🙂
the (filtered) just means that you have a filter applied to your list of objects – you may or may not be seeing everything
I right clicked, applied filter, and checked “Include Synonyms” but none of the tables shows up.
Thanks,
Apple
Thanks Jeff. It worked like a charm.
Hi Jeff,
We’re getting this issue, but the solution definitely isn’t what you’ve suggested. I could use a hand as I’m close to pulling my hair out over this.
Here are the parameters of the issue:
This has been tested on multiple machines: servers, desktops and laptops. It has also been tested with SQL Developer on the local server itself. It has been tested and found with SQL Developer 3.1 and the most recent version of SQL Developer downloaded from Oracle’s website with JDK included.
The user in question has tables that are visible to that user in user_tables. They are definitely tables, and definitely owned and created by that user. Just to be sure I included synonyms in the SQL Developer table filter which did not resolve the issue.
The user’s tables can be viewed in SQL PLUS by that user and by sys. They can be selected, DML’d, interacted with by the application and basically everything you’d expect because they are actually there and not phantom tables.
The user’s tables can be viewed by SYS in SQL Developer through the Other Users dropdown. There are over 4k of them but they are accessible instantly.
The user’s tables CANNOT be viewed by the user in SQL developer, and attempting to view them causes SQL Developer to wait indefinitely on network response. Any other action causes a Try Again or Abort dialogue to pop up.
Resource Monitor’s Analyze Wait Chain reports that one or more of the SQLDeveloper.exe threads are awaiting Network I/O.
Any ideas, suggestions or hints would be welcome. Heck, I’d even be happy to introduce you to the system itself via screen share. If you can give me an answer promptly I’ll even send you a thing. Not sure what thing, but a good one!
Best (and thank you very much for any help),
ee
The user’s tables can be viewed by SYS in SQL Developer through the Other Users dropdown. There are over 4k of them but they are accessible instantly.
The user’s tables CANNOT be viewed by the user in SQL developer, and attempting to view them causes SQL Developer to wait indefinitely on network response. Any other action causes a Try Again or Abort dialogue to pop up.
Start up SQL Developer in debug mode.
Run it again.
When it hangs, run jstack -l on the process ID, and send all of that information, include the contents of the log panel, as a service request to My Oracle Support.
Sounds like a plan, thanks for the quick update!