Why Can’t I See My Tables in Oracle SQL Developer?

thatjeffsmith SQL Developer 79 Comments

Tell Others About This Story:

Because you don’t have any tables.

I hate to be the bearer of bad news, but you don’t actually ‘OWN’ any tables. What you mostly likely DO have are SYNONYMS that point to tables in a application schema. When you log on to Oracle, you are seeing this:

Your Table List is Empty!

Your Table List is Empty!

The solution is simple!

Enable Synonym Support in the Tables Filter Dialog

Mouse-right click on your tables tree node, and select ‘Apply Filter…’

Treat Local Synonyms for Tables as Tables

Treat Local Synonyms for Tables as Tables

With this checked, bam!

Oh my sweet, sweet darlings, how I missed you.

Oh my sweet, sweet darlings, how I missed you.

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!

I told you so!

I told you so!

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.

Drag and drop your synonyms to get INSERT statements, or access the table context menus

Drag and drop your synonyms to get INSERT statements, or access the table context menus

But Why Were We Lied To?

I think lying is a bit of a stretch. Perhaps you were using other tools that treat synonyms as tables and you just assumed the tables you saw were yours?

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.

The ‘trick’ is just to remember to ask SQL Developer to show you SYNONYMS under your TABLES node. And I should clarify, it will only show synonyms that resolve to tables, not all types of objects.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 79

  1. 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.

    1. thatjeffsmith Post
      Author

      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.

    1. thatjeffsmith Post
      Author
  2. 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

    1. thatjeffsmith Post
      Author
  3. 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.

  4. 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”

    1. thatjeffsmith Post
      Author
  5. 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.

    1. thatjeffsmith Post
      Author
  6. 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?

    1. thatjeffsmith Post
      Author

      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

      1. I right clicked, applied filter, and checked “Include Synonyms” but none of the tables shows up.

        Thanks,
        Apple

  7. 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

    1. thatjeffsmith Post
      Author

      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.

  8. 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 🙂

  9. 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

    1. thatjeffsmith Post
      Author

      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

      1. 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…

  10. 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?

    1. thatjeffsmith Post
      Author

      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.

  11. 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

    1. thatjeffsmith Post
      Author
      1. 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!

        1. thatjeffsmith Post
          Author
    1. thatjeffsmith Post
      Author

      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!

  12. 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?

    1. thatjeffsmith Post
      Author
  13. 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?

  14. 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.

    1. thatjeffsmith Post
      Author

      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.

  15. 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

    1. thatjeffsmith Post
      Author

      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?

      1. 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

        1. thatjeffsmith Post
          Author

          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.

          1. hi. i did try your tip to include sysnonyms but that did not help. thank you, will play around with it for little more.

  16. 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

      1. thatjeffsmith Post
        Author
  17. 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.

    1. thatjeffsmith Post
      Author
      1. 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.

        1. thatjeffsmith Post
          Author
    1. thatjeffsmith Post
      Author
  18. 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.

    1. thatjeffsmith Post
      Author

      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.

  19. 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

  20. 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.

    1. thatjeffsmith Post
      Author

      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…

  21. 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 )

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
          1. 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?

          2. thatjeffsmith Post
            Author
    1. thatjeffsmith Post
      Author
  22. 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 ?

    1. thatjeffsmith Post
      Author

      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.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  23. 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?

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *