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, I would like to ask, on our Oracle database, we have a sysadm account (QA) and we have created a proxy for a user (TEST)
alter user QA grant connect through TEST;
when we are logging in SQL Developer using TEST[QA]
we are not able to view the objects owned by QA.
I hope you could help us out on this 🙂
Hi Jeff,
thanks for this post. Some colleagues of mine (commongly using TOAD) told me why they cannot view (in the Table node of Developer) tables that are accessed through a database link (plus a synonym corresponding e.g. to tab_name@db_link_X). They say that this is possible in TOAD… Obviously your answer clarifies this (the SELECT * FROM user_tables does not include tables accessed through database links), but do you think this feature will be available in future releases of Developer?
Thamks
probably not – querying across db_links is expensive
it also starts to stretch the concept of tables for a schema…you could possible create at report and use that as a navigator for those objects, or if it were me, i’d just login to the remote database and work on them directly
Thanks Jeff for your instant reply!
I suggested them to create one or more views as select * from remote_tab@mylink. In this way they can browse the data (and the structure) almost the same as they would with a table.
I was just wondering if there were plans to enhance this feature in a controlled manner, e.g. a further checkbox in the filter dialog window asking “Include synonyms through DB links” that also performs something like select * from user_tables@mylink. I agreee that it should be used with caution, because querying across db links is expensive…
select *from user_objects where object_name like ‘%LOOKUP%’
select * FROM CORE_LOOKUP_VALUES_Vl
The first query does not return the object in the second query.
I spent a lot of time searching for the table/view in my schema and other users, but no luck.
Is there any query I can use to find under which user the table/view is?
Yeah, on a newer version of SQL Developer, put your cursor on the ‘CORE_LOOKUP_VALUES_VI’ and hit ALT+G -> That will feed that string to our find db object search utility.
Or, SELECT * FROM ALL_OBJECTS WHERE OBJECT NAME = ‘CORE_LOOKUP_VALUES_VI’
Or, on your table filter, set it to include synonyms, as I’m betting a $1 someone created a synonym for you so you wouldn’t have to include the schema name in your queries.
HI Jeff,
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 cant 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.
Thanks,
Angel
This was a huge help, thanks!..
Thanks for the synonyms info! I was tearing my hair out on this one
Oh, glad to see you finally got it worked out. I’m very sensitive to hair loss!
Hello, it’s the first time that I use SQL developer. I followed the steps showed here and checked “include synonyms”, but I still cannot see any real tables under Tables (there is no + sign next to Tables). I struggled for two days. I will be very appreciate for any help!
did you try the query i showed in the post? select * from user_tables; — if nothing comes back, you in fact, have no tables.
very helpful i thought i was doing it all wrong !!!! thanks
You’re welcome. And you weren’t doing it wrong, you’re just doing what most folks do when new to Oracle. When you get stuck, just come back and let me know what we can do to help!
Hello!
I have done this and as result I don`t see any tables. But I have permission to read and no more. Can I know names of those tables?
You’ve done what exactly?
My SQL Developer (4.0) is missing the entire “tables” node! I must have changed the preference for what kind of nodes are displayed in the navigation pane. Do you know where that is done, so I can get my “tables” node back?
this is not the issue I have – I can see all tables that I have access to when sqldeveloper does it’s predictive guessing …like when you enter
DESC table_name …when you start typing table… it builds a list of tables starting with the characters you type – in this drop down list I can see it all … however on the connection browser I cannot …wish I could show you what I mean.
So if you run select * from user_tables in a worksheet – you see your objects in the results, but you don’t see the same tables in the tree?
Show me – just take a screenshot and send me a link to it.
hi. i did try your tip to include sysnonyms but that did not help. thank you, will play around with it for little more.
thanks you for the reply. so is that mean im not going to able to list the tables in sql developer? im wondering why i could see the table list in PL/SQL developer. Im logging in with same ID and Pass to both PL/SQL developer and SQL developer, so i should have the same previlage. Woudl you please advise. Thank you in advance.
gopherhahaha
My guess – you don’t really have any tables in your schema.
Select * from all_objects where object_name = ‘THE_TABLE_YOU_SEE’;
Pay special attention to object_type and owner in the results for any hits.
I don’t think it’s a permissions issue – a user ALWAYS has the privs needed to see their own objects.
You are logging in as the user that owns the objects, yes?
hi again, thank you for your advise.
i did run Select * from all_objects where object_name = ‘THE_TABLE_YOU_SEE’; and found that the logged in userid that i use is not the owner of the table objects. however, going back to the initial question, why im able to see the table list in PL/SQL developer?? which im using the same userid…
thanks
gopherhahaha
I can only tell you how SQL Developer works.
It shows you the tables that the login user owns. You can optionally have synonyms which resolve to tables also show in the tables node – which I discuss in this post. Did you try that?
Otherwise, your other tool could be doing any number of things. For example, it might be issuing an alter session set current schema command.
You can see what you want – just go down to the ‘Other Users’ tab, and open the tables node under the appropriate schema.
hi. i did try your tip to include sysnonyms but that did not help. thank you, will play around with it for little more.
i have the same issue as Alejandro. im using both pl/sql developer and oracle sql developer. i can see tables in pl/sql developer but not in sql developer. i did your tip to include synonyms but no luck. Would you please advise
to add, when i do select * from USERS_OBJECTS, i only get DATABASE_LINK for the query result
So the database doesn’t think you own anything except for DB_Links…
Thank for your help. Regrettably, there are two issues here:
1. I applied your suggestion, and still no tables / synomyns / views, nothing to view, much less manage.
2. I don’t have this issue when working under allrounds’ PL/SQL Developer, Toad for Oracle and even Microsoft Visual Studio 2012 Data Tools can view all the tables right out the box, no filter manual tweaking whatsoever.
Something else must be missing.
So you query user_tables and something comes back?
I digged some more your explanation that the tables from my schema are not necessarily mine, so I found the tables and views were at the bottom of the list of the objects explorer. Rather counterintuitive.
But that’s Oracle’s fault, not yours. My most sincere thanks for your usable advice.
How would we know what tables to show you if they do not belong to the user you used to logon to the database with?
I’m glad you found them though.
Hi,
I am unable to create local users with oracle12c, but the common users are made easily(by the name c##) Kindly help me.
What exactly have you tried?
I created table named P_dump on toad, but when i tried to access the same table using oracle sql developer, it is not there. I have also included synonyms, but still it is not visible there. Please suggest a solution.
So run something like
[sql]
select table_name from user_tables;
[/sql]
If you don’t see ‘P_DUMP’ – then you didn’t create the table where you thought you did.
Try using the View > Find DB Object and interrogate all of the database schemas for your table. It’s in there, somewhere.
Thanks , Solved my problem.
Brilliant Smith. It works for me. Many thanks for this. Until I see your post I use to go to Other Users where I have to select 1 out of 80 odd users… Thanks again…Cheers Thayu
Just the way I click on tables , and it expands and shows all the tables under schema, similarly when I click on packages it does not list all the packages under the schema. Same applies for functions and synonms. But when I login with another user who has read and write access , it lists all packages under the schema.
You see some packages, but not all packages? Or you see no packages?
Either way, I’d run SQL Developer in debug mode to see what is happening. You’ll see the queries run, and the tree nodes populated in the log panel…
Hi Jeff,
I really like your site and I have learnt a lot of things . I had question for you. Recently I have started studying a database of another project.The DBA has given me only “Read_Only” acess. Now I am not able to see any tables listed under the schema. I did query user_objects and I did not find any record under the table. So is that the reason? If yes what should I communicate to the DBA to get the thing done (I.e display the tables under the schema )
The tables belong to someone else. The ‘other users’ node in your connection tree will help you find them. Or look under your views/synonyms.
You are awesome!!. The ‘Other User’ node works for me perfectly.
Great news 🙂 Enjoy the ride!
Jeff, I am able to see table, views, Indexes but in ‘other users’ node I am not able to see packages, functions,synonms etc. What could be the reason and what is possible soultions?
You can’t see the names or you can’t see the source? Or both?
And what should I do, when there is not Tables in my connection?
I created my tables, but the Tables above the views is gone.
https://ws.cubby.com/p/_dd74496df8a14d958389818696987f41/asdfasddf.jpg/840631261
You can try a restart – sometimes that fixes it. But it might be better to upgrade to version 4.0.1 – I’ve not see this happen in that version.
Ok, thank you.
Yes but I stii can’t see objects granted by another user.
As you have written “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”. The owner of objects granted only select on them for another simple user but did’nt create synonyms. Is there any way in sql developer for such simple user to see such objets ?
Probably not. If there’s no visibility in your ALL_OBJECTS types data dictionary views, then we can’t see. That doesn’t stop you from querying them, but you have to know about them.
It could be argued that this was done intentionally – you might want to confirm this with your DBA. If it’s an oversight, asking for a synonym wouldn’t be asking for too much. Be prepared to say WHY you need it though.
Smith,
It’s pretty good, was very helpful to me. Keep on post.
Cheers !!!
Thanks Nathan!
Very helpful; thanks.
No worries Mike!
Is there a configuration setting to make this the default for new connections? How about retro actively applying it to dozens of current connections, on both the tables and views nodes?
No way to make it default for connections. Not for multiple existing connections either. Sounds like an idea for the Exchange though – default filters for connections?