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.
- 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.
- 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.
- 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,828 Comments
Hi Jeff,
I have two SQL-Developer environments here:
One Linux-based (Java 1.7.0, SQL-Dev 4.0.2) and the other Windows-7-based (Java 1.8.0, SQL-Dev 4.0.3). Both 64bit.
While the Linux-based works fine, the Windows-7 environment fails when trying to import data (right click on a table and selecting “Import data …”) due to not being able to select the character encoding of the file (at the “import assistant step 1 of 5” screen):
By default it displays “Cp1252”. When trying to open the drop-down menu, it just switches to “” and shows the error message “The selected encoding is not supported”. This way, there is no chance to import the data file.
Is this error related to the Java 1.8.0, I have installed? SQL-Dev warned me on its first startup that only Java < 1.8.0 is "supported".
(The messages are translated to english. Actually, I have a german environment here.)
Thanks in advance.
p.s.: “” should actually mean “[Load]”. (The brackets have been filtered out by your CMS.)
Not sure, but we can cheat. That default character encoding for the file is set in the preferences. Go into Tools > Preferences > Database > Utilities > Import, select the File Encoding, and set it to something more appropriate for your machine.
Hmmm … strange … even there I only have the option to select “[Load]”. Hoped, that I can avoid having to install multiple JRE’s/JDK’s but it seems that there is no way around it …
Which 1.8JDK do you have installed? If it’s an early one, try upgrading to a more recent version.
Ok, I had installed JDK 8u5, updated to JDK 8u25 (both had the “[Load]” problem). The only way actually seems to be downgrading to JDK 7 (here JDK 7u71). Not what I wanted, but fixed the problem … :-/
Ran this query and got data back:
SELECT * FROM all_objects where owner like ‘CE%’;
CESX XXX_GET_XXX 88733 PROCEDURE 15-JUL-14 12:40:41 04-SEP-14 13:31:26 2013-09-27:09:04:56 VALID N N N 1 NONE Y N
just to make sure both of us are on same page, I did this
Owner account – create SP – grant execute to user account
User Account – open sql developer – can execute sp but it is not listed in the tree under procedures.
Owner account – create synonym for sp
User account – synonym shows up in the tree.
Can’t see the objects in the tree. Based on info from one of your other posts, I did right-click on procedures/packages – apply filter – override schema filter – still no objects show up under either Packages or procedures.
Added a synonym to my procedure and that did show up under the synonym – from there I was able to look at the code and execute the proc too. Still would like to see the proc/package under the right header (proc/package).
Thanks for getting back to me. appreciate it.
Lack of data dictionary privs it sounds like. I’m a power user so I’ve got access to SYS.DBA_OBJECTS – which is what we query when we have access to the DBA views, otherwise we query ALL_OBJECTS.
If you query all_objects where owner = ‘that owner you are looking it’ and type = ‘FUNCTION’ — or package or procedure…do you get results?
Hi Jeff,
I have user account that can execute couple of packages and stored procedures – tested works. However, when I register that account and try to list them in sql developer – packages and procedures are empty!
If I use the account that created them, then the objects show up in my tree.
what am I missing?
You can’t see the objects in the tree, or you open the objects and you can’t see the code in the editor?
Hey Jeff!
I just recently changed the syntax colors of the code editor to be a bit darker, but there is now a stark contrast between the code editor and the rest of the GUI. Is there a way to set the rest of the GUI – outside of the code editing space – to be a darker as well?
Thanks a bunch,
Adam
I don’t know.
But.
You can play with the IDE settings. I talk about that a bit here
http://www.thatjeffsmith.com/archive/2013/12/how-to-change-the-ui-font-size-for-oracle-sql-developer/
Hi Jeff,
I’ve been using SQL Developer 4.03 for Windows x64 to copy database tables from one Oracle database to another. For the most part, it’s worked very well, but I have a table with about 5 million records that includes BLOB field. I’ve successfully copied the table, but I had to do it a piece at a time because the copy (of the data for the one table only) would crash. Based on what I’ve seen in the Windows Task Manager, it looks like memory is being allocated but not being freed/GC’d.
What’s the best way to report this problem, and what information would be helpful to provide?
Thanks,
Tom
Yeah, I wouldn’t use a client tool to do a copy like that, esp when LOBs are involved. I would use DataPump. Don’t put the network and client in the equation – it will always present a bottleneck of sorts.
I did it this way because I have DBA access to the database, but not to the filesystem on the server, and because I’m not an Oracle expert with knowledge of DataPump or Import/Export.
This was simple and allowed me to spend my time on my regular job, rather than researching Import/Export or DataPump. It did cause SQL Developer to allocate and not free about half a gig of memory, very different behavior than I saw when copying tables that don’t have LOBs. If you’re saying “If it hurts when you do that, don’t do that”, I understand. If SQL Developer *should* do this without crashing, I’d like to provide a bug report to the appropriate people. This is reproducible basically on demand…
When the tool runs out of memory, there’s not much it can do to recover – if anything.
I think what you’re asking the client tool to do, is not advisable based on your machine specs. If you had solid state disk and unlimited memory, maybe it would work.
But it would never work as well as using Data Pump. It’s worth the time and effort to get access and learn the technology. In the meantime, this SQL Developer feature is just fine for most ad hoc copy operations.
SQL Developer may be hitting a memory limit, but it isn’t my machine. This is an i7 – 2600 with 16GB of memory running Windows 7 Pro x64. When I launch SQL Developer, it grabs between 500 and 525 megs of RAM, according to the Windows Task Manager. When copying normal tables, it goes up to 550 or so, while using between 1% and 3% of CPU. When copying a table with BLOBs, it increases to between 700 and 950 megs, still using < 3% of CPU before the copy operation aborts. When the abort occurs, about 3 GB of physical memory remain free in the machine. SQL Developer doesn't actually crash, but it does start using about 50% of CPU. Other operations don't seem to work properly afterwards, unless I restart.
There is a copy log for each operation, but the aborted ones contain 0 bytes.
The JVM can only use a limited amount of memory. This is configured in the product.conf file. i think the default is 500 or 750 MB of RAM…you could bump it up to 4 GB or so and see if that works.
Please explain me the relations in relational diagram with example as well as in logical diagram.
Please explain me identifying and transferrable concept with example.
Is that a question?
Hi there,
please could you tell me if there is a way to launch SQLDeveloper and automatically open a connection to a given database ? e.g. pass in the db name, username and password in some kind of command line call.
If there’s not a way, please could you consider adding this feature in a future release ?
kind regards
Neil
How do I grant audit roles in Oracle?
Sometimes when I run a query it will take a long time. When I hover my mouse over the ‘x’ on the tab that is open the tooltip shows ‘Cancel Task’, I wasn’t able to find that command in the list to configure keyboard shortcuts. Do you know of one that will close that query without having to click on it with the mouse?
I don’t know of a KB shortcut for cancelling queries…seems like there should be one though. I’ll log it as an ER for you.
I am new to data modeling my manager asked me to create a physical logical and relational diag… I imported the data dictionary and created a relational diagram.. and I have done it part by part by creationg new relational diagrams relational diag 1 2 3…. now when I go to that part of relational diag and click on re engineer to logical diagram it doesn’t work… its blank… y isn’t it creating a logical diagram any idea?
When you go to engineer it to a logical model, do you get the popup dialog where you have to select the objects you want to merge into the logical design? Make sure everything’s checked before you hit the button to complete the process.
HI Jeff, I downloaded SQL Developer 4 and while trying to connect to the database I get the error ocijdbc11.dll: Can’t load IA 32-bit.dll on a AMD 64-bit platform. I have downloaded the 1.7 version JDK and it didn’t seem to fix the issue. Please assist.
are you using an Oracle Client to connect? Make sure it’s the same bit level as your JDK
I often use Popup Describe (Shift F4) to see the columns in a table. Usually, the columns appear in sequential order by column ID. But sometimes, a table will appear with the columns in a different order. It appears to be in order of Primary Key, then Column Id. Is there a way to set the default so that whenever I Popup Describe, the columns will ALWAYS appear in Column ID order? Thanks!
Hello Jeff,
On more Question related to my Topic.
Im Internet there are some query scripts to find some relations and references between tables.
Like here:
http://stackoverflow.com/questions/17501840/how-can-i-find-out-what-foreign-key-constraint-references-a-table-in-sql-server
Is it also an uncritical possibility to find some references between Tables on my Database?.
Are there any good Documents that they describe the way to find out references between Tables ?
best regards
john s.
The relationships in the database are ONLY defined as foreign key constraints on the table columns. Those are shown in the constraints page. You can see this visually if you import your data dictionary to a new SQL Developer Data Modeler design…
Hello.
I have a problem using Query Builder in Data Modeler.
I have imported the data model from a database.
When I open a view definition in Query Builder, the SQL text is both formatted AND altered to a more “standard” SQL.
I have unticked Preferences->Data Modeler->SQL Formatter->Autoformat…
I need Query Builder to not do any automatic changes to my SQL.
How do I accomplish this?
This happen in both version 4.0.3.853 and 4.0.2.840.
Thanks,
Lars Johan
Jeff,
your blog is outstanding. I look forward to every post. Your recent post about MAC I found very helpful. Having insider knowledge can we expect Oracle database version for Mac platform or we need to use VM.
Thanks,
Chris
We like Apple. Many of us use Macs. Only a small percentage of the general IT population download Mac versions of any software though. For the year, about 10% of our SQLDev downloads were Macs.
If I were you, I would do as we do, run a VM on your Mac. Mac + Virtual Box + Linux + Oracle DB works great.
Thank you. I will setup in the VM then. Best.
Hello Jeff,
The Tables have no constraints at all.
So this dosent take me further.
The feature to “Discover Foreign Keys…..Is that possible in Tables which are exist already ?
Or do i mixing things up a the moment.
best regards
john s.
Yup, just import your data dictionary (schema) into a new data modeler design, and use the feature to match up tables based on primary/unique columns which have the same column names. It might help you, a little.
Hello Jeff,
1. checked and always on.
2. How i can find out this .. and if there is no foreign key constraints aviable what other methods i can use to find out the right joins ?
We use the infor erp com System with an orcale databse
http://www.infor.de/solutions/erp/
best regards
john s.
Open your table, go to the constraints tab. Do you see any of these?
If you don’t have any, you’re expected to know the relationships and write the queries yourself. Hopefully you have a data model that explains these tables and how they’re related. If you don’t, then you can try to make some educated guesses.
Hello Jeff,
I’m completley new with sql developer
May you can help me
It relates to the drag and drop automatic join feature
I have the Problem, that if i take some tables with the drag and drop select join option, theres no row in the result for an join in the worksheet at the end just only the select commands for the tables which i’m using …
What I’m doing wrong ?
Ps:
I’ve posted in an another section too, but then i saw that the last post was 7 months ago. So you can delete the previous one in the section “SQLDev Tip and Trick: Drag and Drop to Worksheet”.
best regards john s.
Two things:
Hi, I’m basically NEW to SQL Developer. I need to change a birth date from showing up as 2/12/1975 to just 2/12. I’ve been spoiled in that I’ve just been using the Query Builder to add all my criteria for my reports. SO I’ve tried different things like TO_DATE or TO_CHAR, and basically I reall have no idea what I’m doing. Is there a code I can just plug in the criteria section of the query builder, or do I need to insert some code into the worksheet directly? Thanks for your help!
try with something like
select to_char(sysdate, ‘DD/MM’) from dual;
The date formats and to_char and to_date functions are in our Code Snippets you can refer to for ‘help.’
Get out of the query builder, start writing your own queries in the main worksheet.
Question on O-R logical modeling in SQL Developer 4.0.1.836: How to model nested tables. I have scoured the docs and read your post about reverse engineering them, but in the tool itself the Collection types dialog seems only to allow single-column collection types. This is a very limited application of a nested table. If you want an example of one for constructing an answer, let’s use the basic customer-with-multiple-address/email combinations, where the address/email combination is the nested table collection type. How would one model that from scratch in either a logical or relational model?
Thank you!
Jeff
i am trying to assist a staff member with SQL Developer who has allowed the password to expire. i have read the comments from this site and per the staff member the Oracle client has alredy been added to a directory and user path and there are getting this message:
error report
An attempt was made to change the current unsername or password without the appropriate privilege. this error also occurs if attempting to install a database without the necessary operating privileges.
when trusted oracle is configure in DBMS MAC, this error may occur if the user was granted the necessary privilege at a higher label than the current login
Yeah, it sounds like you don’t have the right privs. Have you consulted the docs?
no sir. i will review and thanks.
Jeff,
I recently had to uninstall/reinstall my 64-bit Oracle 8 Windows client in order to get Microsoft SSAS to work properly on my 64-bit development system. I ended up using the Upgrade option after multiple attempts to uninstall the prior client failed. After the upgrade my issue connecting to Oracle via OLE DB from SSAS was fixed, but now my SQL Developer is giving me connection errors when I’m configured (via Database/Advanced preferences) to use the Oracle Client and the OCI/Thick driver. Removing the OCI/Thick driver still generates the error. If I uncheck both the “Use Oracle Client” and “Use OCI/Thick driver” options I’m able to connect.
Also note that I have both the 32-bit and 64-bit Oracle clients installed on my PC since this is required to perform Microsoft development. I also removed the path to the 32-bit BIN folder from my PATH in order to fix the proper selection of the OLE DB driver. (see http://blogs.msdn.com/b/dbrowne/archive/2010/03/23/oracle-database-connectivity-for-the-microsoft-bi-stack-part-1-oracle-client.aspx)
Here’s the error I’m getting. Hopefully you can tell me what needs to be fixed to get SQL Developer working again.
java.lang.NoSuchMethodError: oracle.jdbc.driver.PhysicalConnection$1.([Ljava/lang/String;Ljava/lang/String;Ljava/lang/String;)V
at oracle.jdbc.driver.PhysicalConnection.getSystemProperty(PhysicalConnection.java:3616)
at oracle.jdbc.driver.PhysicalConnection.getSqlTranslationProfile(PhysicalConnection.java:3588)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:547)
at oracle.jdeveloper.db.adapter.AbstractConnectionCreator.getConnection(AbstractConnectionCreator.java:185)
at oracle.dbtools.raptor.standalone.connection.RaptorConnectionCreator.getConnection(RaptorConnectionCreator.java:195)
at oracle.dbtools.raptor.dialogs.conn.ConnectionPrompt.promptForPassword(ConnectionPrompt.java:67)
at oracle.jdevimpl.db.DBConnAddin$1.promptForPassword(DBConnAddin.java:78)
at oracle.jdeveloper.db.adapter.DatabaseProvider.getConnection(DatabaseProvider.java:366)
at oracle.jdeveloper.db.adapter.DatabaseProvider.getConnection(DatabaseProvider.java:299)
at oracle.jdevimpl.db.adapter.CAConnectionCreator.createConnectionImpl(CAConnectionCreator.java:66)
at oracle.javatools.db.DatabaseFactory.createConnection(DatabaseFactory.java:838)
at oracle.javatools.db.DatabaseFactory.createDatabase(DatabaseFactory.java:282)
at oracle.jdeveloper.db.DatabaseConnections.getDatabase(DatabaseConnections.java:833)
at oracle.dbtools.raptor.utils.Connections$ConnectionInfo.getDatabase(Connections.java:175)
at oracle.dbtools.raptor.utils.Connections.getConnection(Connections.java:1165)
at oracle.dbtools.raptor.utils.Connections.getConnection(Connections.java:1139)
at oracle.dbtools.raptor.navigator.db.DatabaseConnection.openConnectionImpl(DatabaseConnection.java:146)
at oracle.dbtools.raptor.navigator.db.AbstractConnectionNode.getConnection(AbstractConnectionNode.java:30)
at oracle.dbtools.raptor.navigator.db.impl.DatabaseTreeNode.getObjectFactory(DatabaseTreeNode.java:83)
at oracle.dbtools.raptor.navigator.db.impl.DatabaseTreeNode$LoadTask.doWork(DatabaseTreeNode.java:138)
at oracle.dbtools.raptor.navigator.db.impl.DatabaseTreeNode$LoadTask.doWork(DatabaseTreeNode.java:112)
at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:193)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:554)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
What do you see when you use the ‘Test’ button on the Thick Driver setup dialog? Also, you don’t say what version of the client you’re using or what version of SQL Developer and/or version/bit level of the JDK.
I need to know all that to see what’s going on.
SQL Developer 4.0.2.15.21 (64-bit)
Java 1.7.0_55
Oracle Client version 12.1.0 (Both 32 & 64 bit)
Clicking the Test when setting up the Thick client yields a success.
Testing the Oracle Home located at C:\app\client\jim.mccusker\product\12.1.0\client_64
Testing client directory … OK
Testing loading Oracle JDBC driver … OK
Testing checking Oracle JDBC driver version … OK
Driver version: 12.1.0.2.0
Testing testing native OCI library load … OK
Success!
Jim, if that’s working, then I have no idea what the problem could be on connect…do you absolutely need the THICK connections?
I would open a SR with MOS and see what’s up.
Thanks Jeff, I was afraid that was going to be the answer. I suspect that my inability to uninstall the prior 64-bit Oracle 12 client may have played a role. I was able to upgrade with the latest 64-bit client though. The frustration is that everything else except SQL Developer is working fine.
I like using the Thick client because it generally lets me cancel an active query, so I’d prefer to keep using it.
At this point I think I may need to manually uninstall all of the Oracle clients and clear out the registry and finally reinstall everything…then cross my fingers that everything will work.
Do you have any suggestions on how to manually uninstall the Oracle 12 clients cleanly? I’ve already tried multiple times to uninstall the 64 bit client using the command-line uninstaller but it keeps failing.
–Jim
No, but it requires lots of mucking around with the registry. Which is why I personally like the instant clients – it’s just a directory you remove and a PATH OS env variable you update.
Hi, Jeff!
I’m working on a method to debug the DB-Calls from our 4GL-Client software. Initially it works nice by having the clients call DBMS_DEBUG_JDWP.CONNECT_TCP and have correspondig SQLDevloper Remote Debug Listeners on.
After some steps however, the Client Sessions get stuck on “TCP Socket (KGAS)”.
The Remote_Session_Debug…logs are empty (connect/disconnected messages)
If set the retry connection rate higher and the JPDA listener timeout to 1000. To no avail.
How to proceed?
Sorry, no idea. It sounds like a network/timeout issue – and I think it’s happening outside of SQL Developer. I would open an SR with MOS.
Jeff,
Love the site!
I’m noticing that with SQL Developer Data Modeler 4.0.3 that the Re-engineering Your Database Using SQL Developer Data Modeler 4.0 OLL tutorial doesn’t work. (Note that I’m using SQL Developer Data Modeler from within SQL Developer and not the stand alone version.)
When you get to the step Synchronizing the Logical Model with the Relational Model the names for the FK columns which we changed in the Logical Model (PROJECT_DEPT, ASSIGNED_TO and PROJECT_ID) are NOT reflected in the Relational Model. Instead the old names remain.
I’ve tried this on two different systems and got the same result both times.
Thanks,
Rich