Ask A Question

4.5+ million 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!

Comments 4,249

  1. I recently wrote a view that included the Interval keyword and discovered that this makes the Query Builder fall down!

    SELECT SYSDATE + INTERVAL ‘1’ DAY AS Tomorrow FROM DUAL

    The query runs just fine and gives the result you expect, but switching to Query Builder gives a parsing error message: “Invalid SELECT statement. Unexpected token “‘1′” at line 1, pos 27. Query Builder disabled.”

    Is this just too complicated for the Query Builder to handle or is my syntax really invalid?

    1. thatjeffsmith Post
      Author
  2. Hi Jeff

    Having so far failed to convert anything, I am trying to simplify things. So I am now trying to convert a very small sql database (it has 1 table in it, with 3 columns, and 2 rows).

    Capture and convert claim to have worked, it has created several script files (although I haven’t run these), it has also created a log file that contains the following

    Error starting at line : 7 in command –
    @@C:\temp\Migration Test\books_test\generated\2014-06-17_08-16-25\master.sql
    Error report –
    Unable to open file: “C:\temp\Migration.sql”

    is this significant (usually inclined to think that all errors are)?
    Are the tables listed under ‘Converted Database Objects’ real tables or just definitions?
    If they are real, how do I get data into them (I’m trying this online, although all tutorials show it being done offline)?
    If they are not real, where does it put the real tables?

    Thanks for any help, was thinking that such a small test should have been simple.
    Paul

    1. thatjeffsmith Post
      Author
      1. That answer presuposses that I know what is the target database. The connection detailed in step 8 (Target Database) of the migration wizard as seen using SQL Developer doesn’t show the tables.

      2. thatjeffsmith Post
        Author
      3. thatjeffsmith Post
        Author
      4. I haven’t, the ones I’ve seen were for 11g, wanted you to create a user called MWREP (which I couldn’t create as it didn’t start with c##), and didn’t have the right privileges.

        I’ll try going through this one, thanks

      5. Hi Jeff

        I haven’t even got pass the first section and I have found 2 issues. The first is that it is talking about doing an offline capture (like every other conversion I have seen) but doesn’t tell how to create the files. The second is that is talking about unix and I know nothing about unix.

        It says open the terminal, is this the same as a dos window? and commands can’t start with a full stop

      6. thatjeffsmith Post
        Author

        We could keep going – but do you really want to trade blog comments for the next 2 weeks?

        Instead, let’s do this. Screenshot/detail every single thing you’ve done, put it in a word doc, and email it to me.

        So start over, try again, and document EVERYTHING.

      7. That would be a good idea, if I had your email address (can’t seem to find it on this site). You have mine so if you email me I can send you the first roadblock.

        Thanks

      8. thatjeffsmith Post
        Author
    2. thatjeffsmith Post
      Author

      Everything you need to know – logs/errors/etc should be detailed in your migration project panel.

      I would go back to the very beginning and check the prereqs – does your MSSQL user have the proper privs? What about your Oracle user? You successfully created a migration repository?

      1. The migration panel tells me nothing. It shows the captured database objects, and converted database objects, but it’s doesn’t have any logs/errors etc.
        The migration repository has the migrlog table in it, which is usually full of information but in this case just has the 2 rows
        Catalog BookShop, Schema dbo coalesced to single schema BookShop
        Convert completed in 1 seconds
        and the target status tab only lists 2 objects, both of which have a status of missing

  3. Hi Jeff

    I have an issue where importing any more tables into the data model will execute but freeze the application. This is what happens I select the objects to import, this launches the progress bar, the progress bar continues till completion, at this point the cpu utilization goes up and the app does not respond. I do not see any imported tables and have to manually kill the app. I am using it with Oracle 11.2.0.3.0 and windows 7 Pro. The memory utilization remains steady throughout. Please help

    Thanks
    Isingh

    1. thatjeffsmith Post
      Author
      1. Yes, I just tried to import a very small table from the db. It did make it to the merge window and barely did a merge over two minutes. The application is really slow and any clicks will shoot the cpu to max out. I only loaded the relational model for the db and the physical model was not loaded before the operation. This is the only model currently open in the SDDM. I have more than 1500 tables in the db and am currently stuck at around 1100 imported into the model.

      2. thatjeffsmith Post
        Author

        Ok, that’s a ‘big’ model and you’re exhausting the amount of memory available in the JVM. You need to find the product.conf file and edit it to give SQL Dev say 2GB of RAM.

      3. thatjeffsmith Post
        Author
  4. I have tried that link Jeff, but that links helps to add the columns to all the entities(tables) which is present.. But I want to update certain tables only using different columns.. The table name and column name differs from one another..

    There is no common columns nor tables..

    Thanks in Advance,
    Niharika

    1. thatjeffsmith Post
      Author
  5. Hi Jeff,

    As per your suggestion I have did export excel option where I’m able to rename and modify globally.

    But How to remove and add few attributes from few entities globally?

    1. thatjeffsmith Post
      Author

      you can use table templates and transformation scripts to add/remove columns to multiple tables. @krisrice talks about it here a little bit

      Or look at the Table Template – uses column name custom transformation script

    2. thatjeffsmith Post
      Author
  6. One of my comment was eaten … sql was actually following, since I used angle brackets i guess

    {yyyymmddhh24miss}.{dbconnection}.{dbname}.{schema}.{object_type}.{“object_name”}.sql

    note that object_name can be case sensitive inside the file

    with the { } – they are just for emphasis

    thanks!
    /S/

  7. Hi Jeff,

    few suggestions, if they are not already covered eariler –

    (1) commit or rollback via checkmark,anticlock-arrow

    Can this be logged in the status bar with exact time and if in “Script output” tab with similar message?

    And of course it will be nice to gray out the icon’s untill some change happens again

    Scope – if I have multiple tabs open, the commit/rollback scope is the window that is selected. So do you track the icon’s in context of active window? I am sure you do, just checking..

    (2) DDL generation:

    If selected seperate file option – it will be very helpful if file name has following convention to get rock-solid identification

    ……sql

    This will allow one to no scratch head to understand where this came from … I have

    (3) Can commit & rollback be ketp far apart! I mean first they are tiny
    and second a slight shake of hand and boom you are toast – plus no real purpose served in keeping them next to each other – we know commit or rollback – just buttons are too close

    Thanks for keeping product super current!

    /S/

  8. Hi Jeff
    I have now got SQL Developer trying to convert our MSSQL Database and have 2 questions.
    Firstly it doesn’t seem to attempt to convert the CLR Stored Procedures (we’ve got 12 of them), is there a migration path for these, or will I have to rewrite them?
    Secondly when trying to move the data I get many (770) ‘Failed to disable contraints: Data Move’ errors followed by some (78) ‘maximum open cursors exceeded’ errors. Do I have to convert the tables one at a time (is this possible), or have I missed a setting?
    Thanks
    Paul

    1. thatjeffsmith Post
      Author

      We don’t do CLRs, just straight up T-SQL.

      Something is wrong with the data move, obviously πŸ™‚

      Convert the tables – convert and migrate are two separate steps. Have the new tables been built in Oracle yet?

      If you can post your question to our Migrations forum, I can get one of our engineers to take a look at your logs.

      1. thatjeffsmith Post
        Author

        yeah, don’t use SYSTEM. I can ask a developer to take a look at the post, but they will probably have more questions as I don’t see many details in your post.

  9. Question:
    Is there a method to find specific code text within a schema via a search?
    For example, search for all ‘Update tblPerson where person_id = ‘ I would like to know all package bodies, functions, and procedures where that code text is ound

  10. I want to be able to export multiple Query Result Tabs into one Excel spreadsheet with corresponding tabs. I don’t know if this is possible in SQL Developer. I am on version 4.0.0.13

    1. thatjeffsmith Post
      Author
  11. Hi Jeff,

    I have just started using version 4.0.2.15, Build 15.21.

    When I RMC on a table (for example) I can select Edit but then I get a popup saying I have insufficient privileges. I don’t and can do a direct edit via a worksheet.

    Also in Scheduler->Job, when I RMC on a job the context menu was disabled. Now it is back. I may have closed and reopened but not sure.

    Christine

    1. thatjeffsmith Post
      Author
      1. It is happening to me for a table.

        It is also happening to me for an MV.

        Both of which I can edit in a worksheet/SQLPlus.

        C

      2. thatjeffsmith Post
        Author
      3. thatjeffsmith Post
        Author

        That changes things a bit, the proxy user detail that is. I’ll need to know which privs are granted, explicitly or via roles for that setup still.

        You might want to open an SR with MOS as this is starting to go beyond a normal Q&A on a blog site though πŸ˜‰

  12. Hi Jeff,
    Am trying to find shortform words in sql developer where its available in TOAD.
    eg.
    In TOAD, when we type ss it ll produce SELECT
    2. ff it ll produce FROM.

    Do we ve this type of settings in SQL DEVOLPER ?

    1. thatjeffsmith Post
      Author
  13. Hi, Jeff!

    I fear I’ve goofed up with versioning. I ended up with having specified
    an invalid GIT repository. How can I reset the “Team” menu to it’s original
    contents so that I can start again? Is there some file I need to edit?
    What is the recommended procedure here?

    SqlDeveloper version is 4.0.2.15.21

    All the best
    Michael

  14. Hi Jeff,

    I have just started using SQL Developer 4.0.2.15. How do I open a package body.

    When i use ctrl+click the package spec opens and there is a “Open Body” button, but it is greyed out. Can you please help me in making the settings so that the button is accessible.

    Regards
    Anoop Dangui

    1. thatjeffsmith Post
      Author

      2 things:

      1. Are you sure it HAS a package body – not all specs have bodies
      2. If you open the package in the tree, is the body available for you there?

      Otherwise this works for me. As a test, you could try this for sys.dbms_output – does the toggle button work there?

  15. Hi, I’ve been using SQLDeveloper for a few months now (after using TOAD for years πŸ˜‰ ) and I have this annoying thing happen randomly. I launch SQLDeveloper and it hangs while launching.
    I’ve done a lot of research on this, and I haven’t found any great solutions. Initially, I would download and re-install SQLDeveloper, but that got old.
    I poked around the internet and found that I should check to make sure it’s trying to connect to the correct jdk. I had to clean up my classpath, I made sure the jdk was enabled from control panel, and I also make sure that the product.conf file has the right jdk
    The last two times, my solution has been to make a copy of the “SQL Developer” folder, delete the original “SQL Developer” folder from the %appdata% area and relaunch SQLDeveloper. I then go back and copy in the connections.xml from the copy into the new so I at least my connections defined. But this is also just basically re-installing SQLDeveloper, and any other settings I had aren’t carried over. Any ideas as to what else I should be checking/doing? Thanks!

    1. thatjeffsmith Post
      Author

      For starters – so sorry you’ve been struggling with this for months. Not good.

      I wouldn’t re-install the app when it hangs though – we don’t know why it’s hanging, so we don’t know if/how that would fix the issue.

      The next time it hangs on startup, run the jstack.exe out of your jdk/bin directory and attach to the sqldeveloper.exe PID. Then send that stack dump to me at [email protected] – hopefully that will tell us what’s borking up your SQLDev ‘install.’

      1. Ok, will do! I just “fixed” this today, so not sure when it will happen again. Hope it happens soon πŸ˜‰

  16. Hello Jeff,

    I read about your article on DBDOC. I want to use the utility for my SQL project. Can you please share the utility?

    Thanks.

    1. thatjeffsmith Post
      Author
      1. DBDOC and JAVADOC aren’t they different utilities?

        I use Doxygen for Java APIs and I want to use DBDOC for SQL APIs. DBDOC looks to be pretty cool and easy to use.

        Thanks,
        Gouri

      2. thatjeffsmith Post
        Author
  17. Hi Jeff,

    Where can I find class/object documentation that I can be used in transformation script..

    For eg, for classs model.. what are method and properties available.

    Cheers

  18. Jeff,

    I have a question regarding Table abbreviation.

    I created relational model using reverse engineering and now would like to find out missing foreign key using Discover Foreign Key feature.
    Updated naming standard template to have my foreign key constraint name as {Table Abbr}_{Reference Column}_FK.
    Next step would be to set table abbre for all tables..
    My relational model has 500+ tables and want to know, is there an easy way like template where I can update Table Abbr for all the tables instead of going to each table properties and updating the abbr.

    Cheers

    1. thatjeffsmith Post
      Author
  19. i have this content of file.bat

    cd /
    sqlplus scott/tiger
    spool C:\Users\imrane\Desktop\RΓ©sultat01.txt
    select iamj.numA||’;’||iamj.numB||’;’||iamj.dateA||’;’||iamj.heureA
    from iamj left join janmt on iamj.numB=janmt.numB where janmt.dateA is null;
    spool out

    and after it’s stoped after connexion to sqlplus without complete sentences

    1. thatjeffsmith Post
      Author

      your question is about SQL*Plus, not SQL Developer…but i would put your sql*plus script in a file and then change your bat file to be something like sqlplus scott/tiger and then next line @your_script.sql which does the spool and your queries

  20. I want to integrate SQLPLUS into the latest version of SQLDeveloper. Is there a way to invoke SQLPlus similar to how I can open a new SQL Worksheet for a specific schema? What I really want to see, is sqlplus running in its own tab.

    Thank you, Ken

    1. thatjeffsmith Post
      Author
      1. I changed the parameter to “/ nolog” without quotes. I get a Sqlplus tab in the Messages – Log window. I just can type anything.

        C:\users\kenlee>
        C:\oracle\app\product\11.2.0\client_1\BIN\sqlplus.exe /nolog

        SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 5 10:09:03 2014

        Copyright (c) 1982, 2010, Oracle. All rights reserved.

        idle>

  21. good moorning,
    I have this file.bat

    cd /
    sqlplus scott/tiger
    spool C:\Users\imrane\Desktop\RΓ©sultat01.txt
    select iamj.numA||’;’||iamj.numB||’;’||iamj.dateA||’;’||iamj.heureA
    from iamj left join janmt on iamj.numB=janmt.numB where janmt.dateA is null;
    spool out

    and when I call this file.bat he stoped after connexion without taking the spool content in consideration
    help mee please

  22. Fairly novice user of SQL Developer. Connecting to an instance of Oracle working mostly with EBS data. I’m not getting any tables in the tree when I connect. Already enabled “Include Synonyms” in the filters per your posting that I found, still nothing. Using SQL Developer 4.0.0.13 and connecting to an 11g database. I don’t know what information would help so just let me know what you need and I will get it.

    Thank you,
    Kris

    1. thatjeffsmith Post
      Author

      I’m guessing you’re logging in as someone who doesn’t own anything. Select * from user_tables – does that bring back anything? If not, you’ll need to go into the Other Users bit of the tree and drill down into the schema where your application objects actually are.

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

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

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

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

    1. thatjeffsmith Post
      Author

      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.

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

    1. thatjeffsmith Post
      Author
      1. yes.. These are oracle schemas.

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

      2. thatjeffsmith Post
        Author

        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.

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

      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.

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

      2. thatjeffsmith Post
        Author

        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.

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

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

    1. thatjeffsmith Post
      Author

      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.

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

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

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

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

      πŸ˜‰

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

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

    1. thatjeffsmith Post
      Author

      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.

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

      2. thatjeffsmith Post
        Author

        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.

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

    1. thatjeffsmith Post
      Author

      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.

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

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

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

    1. thatjeffsmith Post
      Author

      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…

  37. Hi Jeff,

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

    Thanks
    -Rohit

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

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

    1. thatjeffsmith Post
      Author

      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.

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

    1. thatjeffsmith Post
      Author
      1. thatjeffsmith Post
        Author

        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.

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

      3. thatjeffsmith Post
        Author

        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…

    1. thatjeffsmith Post
      Author

      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

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

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

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

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

    1. thatjeffsmith Post
      Author

      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.

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

    1. thatjeffsmith Post
      Author

      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.

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

    1. thatjeffsmith Post
      Author

Leave a Reply

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