Ask A Question

Nearly 7,000,000 Oracle professionals use SQL Developer on a regular basis. Have a question about Oracle SQL Developer? Searched this blog and couldn’t find the answer? Ask away!

If your question is about Oracle Database, SQL, PL/SQL, etc – go Ask Tom!

Feel free to ask anything you want, but I’ll feel free to send you to Support or our Forums if it goes sideways.

Note: This page has turned out way more successful(?) than I would have ever imagined. Please keep these things in mind when asking questions.

  1. I am NOT support. Don’t expect me to log bugs for you, or give you official timelines on bug fixes, enhancements, or product releases.
  2. I AM NOT SUPPORT. Don’t open an SR with My Oracle Support AND leave a question here. Pick one and go with it, and when in doubt, go to My Oracle Support.
  3. I try to answer questions as quickly as possible. If you don’t get an answer, ask me for an update. I may have just forgotten or overlooked your request.

Go!

7,513 Comments

  1. Paul Darling Reply

    Hi Jeff
    I think this is a Java problem but I’m getting it through SQL Developer.
    I have set up the connection to a MS SQL Server and when I connect I can see a list of the databases available. When I access these databases through SQL Server Management Studio I have full access, and with both applications I am using windows authentication, however when I try to access any of the databases using SQL Developer (which is using JDBC, and I believe only allows read access) I get a 916 error, ‘not able to access the database under the current security context’.
    From this I guess it’s the security context that is at fault, but don’t know where it is defined.
    Can you shed any light on this?
    Thanks

    • Paul Darling

      One additional point (don’t know how relevant it is) is that I did get the following error
      Status : Failure -I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

      So I copied ntlmauth.dll into the sqldeveloper\jdk\jre\bin folder. That resolved that error and allowed the database list to be loaded, but that’s when I encountered the security context error

  2. Paul Darling Reply

    Hi Jeff
    I’m sure I’m doing something wrong as somebody must have done this by now, however I can’t find any tutorials or guides to help (there are plenty for older versions).
    I am trying to convert a MS SQL 2008 database into Oracle 12c on a 64-bit windows 7 professional box. I have SQL Developer 4.0.2.15 installed and can connect to the Oracle database, but when I try to create a connection to the MS SQL database I don’t have the tab available (only Oracle and Access). I have tried to install the latest updates but the CVS pre-requisites fail to connect (all others installed). I have downloaded jtds-1.3.1 and have pointed the Database: Third Party JDBC Drivers to the directory inwhich the sqljdbc.jar and sqljdbc4.jar files are.
    All to no avail, what am I doing wrong?

  3. Hi Jeff, I’m using Oracle SQL Developer tool Version 3.2.10.09., The problem I’m facing is after typing the code I can’t delete or backspace anything ., I dunno wat hapnd., Could U help me with that.,

    • Try Tools > Preferences > Shortcut Keys > Click on ‘More Actions’ in the toolbar in the upper right hand corner, then say ‘Load Keyboard Scheme’ and ‘Default.’

      That should reset your keyboard shortcuts.

  4. Jeff,

    I recreated the model and now table name and column are editable…

    I import 5 schema , 5 schema got created but one a single sub-view got created.

    is there any setting that need to be set before reverse engineering or any properties need to be set during RE

    Can you please share some screen snapshot so that I can follow it.

    • yes.. These are oracle schemas.

      3 schema has tables, views, MV , objects
      other 2 schema has packages/ procedure and synonym

    • I don’t know – there’s nothing ‘special’ to do or to enable to get the automatic schema filtering. You could try posting your question to the forums.

  5. Hi Jeff,
    Data Model : I created a relational data model using reverse engineer.
    Our database had multiples schema, so i reversed engineer one schema at a time . In the end multiples schema objects rolled into one relational model.
    My idea to create model by ‘model using schema name’.
    Issue I am facing.
    1. I am unable to edit relational model like table name or column name etc after reverse engineering.
    2. When I try to update logical model from relational model, I get a lists of table / attributes that will be added to logical model .. after clicking ‘Engineer’ button, no tables are getting added to logical model.

    What could be the reason?

    Lastly, you have mentioned in one your blogs a sub-view will be created for schema by default when reverse engineer option is used.. I didn’t see had any subview created.

    I am working on latest SQL developer version 4.0.2.15

    Thanks

    • 1. Why not? What happens when you edit the properties dialog and try to change a column definition?
      2. No idea.

      I just imported 3 schemas from Oracle to a new relational model. I got 3 subviews, one for each schema.

      Give me more details, and maybe I can help.

    • Jeff,

      1. all fields are grey out.

      Below is the error from log file. This might help

      2014-06-02 08:53:37,589 [main] INFO DataModelerAddin – Oracle SQL Developer Data Modeler 4.0.1.836
      2014-06-02 08:56:03,144 [Thread-31] ERROR XMLTransformationManager – Unable to load object from XML: C:\Suraj\DM\Fuse\rel\93AA6B4B-8D1F77DD8A7A\phys\D9582E4E-2ED963CB9D32\Trigger\seg_0\97B6371C-2256-964B-88CC-18D411678F00.xml
      java.io.FileNotFoundException: C:\Suraj\DM\Fuse\rel\93AA6B4B-8D1F77DD8A7A\phys\D9582E4E-2ED963CB9D32\Trigger\seg_0\97B6371C-2256-964B-88CC-18D411678F00.xml (The system cannot find the path specified)
      at java.io.FileInputStream.open(Native Method)
      at java.io.FileInputStream.(FileInputStream.java:146)
      at oracle.dbtools.crest.model.metadata.XMLToObjectTransformer.transformToObject(XMLToObjectTransformer.java:115)
      at oracle.dbtools.crest.model.metadata.XMLToObjectTransformer.transformToObject(XMLToObjectTransformer.java:111)
      at oracle.dbtools.crest.model.metadata.XMLTransformationManager.transformFromXMLToObject(XMLTransformationManager.java:1658)
      at oracle.dbtools.crest.model.metadata.XMLTransformationManager.openPhysicalModel(XMLTransformationManager.java:3172)
      at oracle.dbtools.crest.model.metadata.XMLTransformationManager.openPhysicalModel(XMLTransformationManager.java:3107)
      at oracle.dbtools.crest.model.design.Design.openDesign(Design.java:1437)
      at oracle.dbtools.crest.swingui.ControllerApplication$1.run(ControllerApplication.java:2407)

    • Yeah, sounds like you have a corrupt design file. Can you create a new design and try to import to your relational model?

      Or if you need this one fixed, I would open an SR with MOS. If you don’t have a support contract, you could try posting this to the OTN Forums.

    • Let me try to create a new design and import it.

      one more info..
      When I try to make a new connection and select connection type = TNS, I don’t get TNS ora entry in network alias dropdown option.

      I used to get it before I upgraded SQL developer to 4.0.2

      I had downloaded sql developer with JDK version.

      Thanks
      Suraj

  6. Hi Jeff,

    Is it possible to refresh tables created from .csv files (directly from SQL Developer, without going down the EXTERNAL TABLE route)?

    Nice postings!

    Thanks,
    Stu

    • Even if it were – and it’s not today – I would still push you to an EXTERNAL TABLE solution. It’s sooooooo easy. Files go up, CTAS, or INSERT AS SELECT gets it where you want the data to go.

      For what it’s worth, we’re going to look at being able to replay insert batches from Excel/CSV/Delimited in SQLDev and possibly add to the CLI. But again, in terms of performance and overhead, makes way more sense to get a DBA involved to give you a DIRECTORY to have the External Tables going.

    • Thanks Jeff,

      I can certainly see the use of EXTERNAL files in a production or test environment, but not as much for development. In my particular case, I am using Data Miner and constantly changing my data sources, what data I pull from those sources, data models, etc. And this is just for one subject area. Many of the data sources are not from Oracle databases (or even from databases), and I am finding the process of development within SQL Developer to be a bit … clunky. I am happy with the workflow tool and the breadth of data mining models, but the process of importing data is tedious at best. (And I am/will be iterating scores/hundreds of times to refine the models. =8^O )

      Any support given to ease this process would be very welcome!

      – Stu

  7. I am using the latest version of SQL Developer and have a number of User Defined Reports. How do I backup all the scripts that I have save there?

    Thanks.

    • By scripts, do you mean the xml reports themselves?

      I would add this file to your backup scripts:
      C:\Users\You\AppData\Roaming\SQL Developer\UserReports.xml

  8. Jeff,

    I work in an environment with Windows roaming profiles and do a considerable amount of work on application servers that have profile restrictions – basically every time I run Oracle SQL Developer I have to recreate my connections. Is there a way I can save or create a “profile” file that I can point to when I launch SQL Developer. Creating multiple environment connections each time I logon to an app server or remote site workstaiton is a pain.

    Thanks!

    Mike

    • I just found the import / export connections feature – guess I answered my own question. Thanks for the web site… got the old brain working.

      πŸ˜‰

    • you can actually set a preference to point sqldev WHERE to write it’s application settings, so they’re shared across all of your installs, have you see this?

  9. Jeff, since we upgraded to 3.2, I no longer get the SQL in a separate excel spreadsheet. Do you know if I can turn that on in 3.2?

    • I just tried it in 4.0 with a xlsx file, and it seems to work there. I don’t suppose y’all have 4.0 on your upgrade path yet?

  10. Jeff,
    When jdeveloper executes a query, behind the scenes how it does fetches data in batches of 200. If I scroll to the end of the table, i see a status that it is fetching another batch of 200. I am curious.

    • There’s a preference that determines the fetch array size – it’s in Preferences, Database, Advanced, Sql Array Fetch Size. 200 is the max allowed today.

      You run a query, we grab the first x rows. As you scroll through your grid, we’ll grab another x rows. If you hit the grid such, e.g. ctrl+PgDn, then we’ll do a full fetch – grab all the rows.

    • Jeff, Thanks for the reply.
      When sqldeveloper brings batches of 200 rows, Does it uses rownum internally to bring data in batches?
      like for eg
      select * from ( select * from
      ( query in sqlldeveloper) where rownum min;
      Or it is that sqlldeveloper have a counter and loop through the resultset to pull data in batches?

      The reason I am more curious is that when looking at the OEM, I see that the time graph of how the DB resources is being used.

    • No, the entire mechanism is handled via the database and the JDBC layer. Here’s some Docs info on the subject.

      The rows aren’t retrieved/read UNTIL the fetch is required. So you’ll see the IO info get hot as that starts.

  11. Jeff,

    Question regarding Domain.
    In ERwin, I create a domain and then configure the name of the attribute that should be created when the domain in used in an Entity. ERWin uses the macro and assign name to the attribute.
    Also I can specify whether the domain is mandatory or non mandatory so that I don’t have to specify in each of the entity.

    Is there a similar feature in SQL Developer DM?

    Cheers
    Suraj

    • We have domains, but there’s no enforcement of a domain for an attribute/column with a specific name. And mandatory, no.

      But, our transformation scripts could be used to scan your designs and switch in the domains based on attribute/column name.

  12. Jeff,

    How to set PCTFREE parameter for a table in physical design? I can see that in Table property window, but I don’t see any possibility of entering values there… can you help ?

  13. Dave Christensen Reply

    I have installed the 64-bit SQL Developer on my workstation. When I run the sqldeveloper.exe executable from the installation, it shows up as a process named sqldeveloper *32. What do I have to do to get the 64-bit executable to run?

    Thanks!

  14. Hi Jeff,

    Thx for this site, very efficient.

    I’m looking for some trick allowing to do a table rebuild (change column’s order mainly) since table is already created and filled.

    Coming from Toad, I’m used to do this easily with the “rebuild” tool.
    But, except error, that’s not yet implemented into SQLDeveloper (I found some up/down arrows into the modify table screen, but that seems not working).

    Do you know some workaround ?

    Thx,
    Hector

    • if you want an on-line rebuild, you could script something using DBMS_REDEFINITION. Or you could use a CTAS to build the table anew using your desired new structure, then drop and rename…

  15. Hi Jeff,

    Is there a shortcut in sql developer to close/minimize the ‘Query Result’ pane (on Mac) ?

    Thanks
    -Rohit

    • Brian Minaji

      In a similar vein, it would also be great to have a keyboard shortcut to hide (close/minimize) the Script Output pane. On Windows as well as Mac.

      Thanks.

      Brian

  16. Jeff, the SQL developer DDL generation produced following –
    In fact there is no table in user schema and it was NOT run as SYS

    One of them –

    ——————————————————–
    — DDL for Synonymn DUAL
    ——————————————————–

    CREATE OR REPLACE PUBLIC SYNONYM “DUAL” FOR “DUAL”;

    Other –

    ——————————————————–
    — DDL for Synonymn DUAL
    ——————————————————–

    CREATE OR REPLACE PUBLIC SYNONYM “DUAL” FOR “SYS”.”DUAL”;

    —-

    Now in general one would look at that and ignore it. However, what if I tell you it can bring down your production AND all dataguards and their mothers and fathers πŸ™‚ Well, as I experienced it – it may just have (I say may because it depends upon if you hit the bug)

    In short once you get ORA-01775 on DUAL — Lotsathingsgotohel*

    So my question is do you think this thing of SYNONYM generation (and it does that for DBMS_SQL and DBMS_OUTPUT as add on bonus πŸ™‚ ) done in Sqldeveloper for some reason? I am just curious to understand…

    I only have SYS.DUAL and one public synonym ….

    Also, there is CONTEXT objects that are not generated in DDL generation?

    Once again, product is great and gets better and better. I will give some of my suggestions as well on DDL generation …

    Appreciate your help!
    Sudhir

    • public synonyms don’t belong to sys – they belong to everyone

      So if you ask for synonyms, you’ll get these

      >>Now in general one would look at that and ignore it.
      Yeah, don’t do that. Review and test everything we generate for you in a TEST environment first.

      Or rather, trust AND verify.

  17. Nicolas Caire Reply

    Hello Jeff,

    I have an issue with Oracle Data Modeler: I imported my tables from SQL Server 2005, it automatically created a relational model. Then I regenerated my tables to a logical model.
    Now I want to display all my foreign keys directly into the tables (in a subview) like any other columns but they are ‘hidden’. I have the relations with the primary keys but the name of the foreign keys columns is not displayed….

    Best regards,

    Nicolas.

    • When you brought it into the the tool initally, in your 2005 SQL Relational model – were the relations (FKs) there then?

    • Jess?

      So yeah I can see the FK columns there, but I don’t see the relationships there in the relational diagram itself. That’s kinda weird.

    • Nicolas Caire

      My bad JeFF ^^

      Anyway I didn’t found any explenation and I’m still stuck… I’m only working on logical model, and no way to make these foreign keys visible…

      Any idea ?

    • So when I import a SQL Server 2008 data dictionary, I get the relationships in my Relational Model.

      sql server RE for oracle sql developer data modeler

      When I RE this to a Logical Model – making sure to include the tables AND the relationships, I see them in the Logical Diagram too…

  18. Hi Jeff,

    Is there any information of anyone using sdcli from Jenkins?

    Thanks.

    • cat the sdcli file – you’ll see that it calls sqldeveloper. Do a ‘which’ on that. Is it calling the right one? You want to make sure it’s calling the sqldeveloper binary from that same bin directory

  19. Hi,

    How is the sdcli run from linux? Only the sqldeveloper file in the bin directory is executable?

    When I run bash ./sdcli it starts and then just hangs indefinately and not returning to the prompt.

    I don’t get this problem in Windows.

    • SDCLI from Oracle Linux Shell

      If it fails to do anything, it probably can’t find the JDK. Are you able to run the SQL Developer GUI on that machine?

    • Hi,

      Firstly the ‘sdcli’ file in the bin directory for some reason had read only permissions, only the ‘sqldeveloper’ file had execute permissions (why is this?) Also, it has been installed as the root user for now (is this correct?). We had to modify the permissions of the ‘sdcli’ file in order to run it. Now when I run ./sdcli it is opening up the SQL Developer GUI and giving me a LOAD_TIME: …. .

      We have installed JDK.1.7.0_51. We are able to run the GUI.

      Please advise how we can ensure we are setup to run sdcli correctly?

      Thanks.

  20. Smith,

    Is there any simple way to drop a atbel only if that table exits. I drop and create table multiple times as a part of my job, when i run them on whole i get error for few new tables that table does not exist.

    Any chance to drop table only if exist in sql developer4.0(Oracle 11g)

  21. Thanks Jeff, you are right. Bit I don’t mind ‘Sessions’ – from the predefined reports under the Reports tab, but selecting ‘Monitor SQL’ – Tools->Monitor SQL. Non-privileged users see one grey pane after selecting SQL Monitor, there is no error, just nothing. If the user has select rights on some views, then this pane is not empty, there are sessions info there. But if this user wants to see sql details (right mouse button), then execution plan is not displayed. If i am sys, everything is ok.
    I could make some screenshots if you want.

    Regards,

    Georgi

    • Argggh, Georgi – sorry about that. Answered too fast.

      That’s part of the Tuning Pack – Real Time SQL Monitoring to be specific. I would start here. It’s going to be more than just data dictionary view access. You’ll need privs to execute a few packages.

      Seeing the explain plan is prob also b/c of the package calls.

  22. Hi Jeff,

    I have a question about “Monitor SQL” tool from SQL Developer.
    Which roles/privs/grants does non-privileged user(no SYS, SYSTEM) need in order to be able to see details for one of the sql statements in the generated report as well to save this report as a nice html page?
    I make it more clear. If a user has select privilege on some views(v$sql, etc), it is possible to invoke “Monitor SQL” and to get a list with the db sessions. But if the user right-click on one line from the list and select to get details for this sql statement, execution plan is not shown. If I do this with SYS user, I get a nice coloured report and if I save it as a html I see execution plan step bz step with colours and table names, etc. But this is when I use SYS.
    I tried to give different roles/privs to my non-privileged user(select_catalog_role, advisor, select any table, even dba) – without success. The user see no execution plan.
    Can you advise me which privileges are missing?

    Thanks in advance, there is a great stuff in this blog! Keep going!

    gme

    • If you copy the sessions report down to the User Defined Reports, you can see the queries being executed for each bit of the page. Then you can grant privs as necessary.

  23. I’m trying to use Oracle SQL Modeler 4.0.1.14. I have reverse database from SQL Server 2008 and it worked fine. I use the TDS JDBC driver, because the MS native doesnt show up on connection dialog.
    My problem is when I try to synchronize any changed entity back to the database, it compares but always show as the table doesn’t exists on it and mark to create. But dont’t even enable Synchronize button nor the DLL preview.
    Does the SQL Modeler work with it ?

    • We work with it, yes. But we don’t support ALTER scripting for anything other than Oracle Database.

Write A Comment