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
Just found your website. I am just learning how to use SQL Developer and am glad to have found such a great tutorial. However, I have a question that I hope isn’t intuitive. When I use the worksheet to test code, I don’t want it to affect the database. Is there an editor I can use that will show me the output, errors without altering the data in the database? Thank you
I think you need to get yourself a test database. Our Developer Day VirtualBox VM sounds like it’s exactly what you want. Then you can ‘break’ stuff if you want. Just create a restore point for your image that you can ‘rollback’ to if you break-break something.
Otherwise, when you run transactions, they’re only visible to your session until you COMMIT. But you can get into trouble here as any DDL you run implicitly does a COMMIT on your session. So if you’re worried about breaking stuff, don’t touch anything.
Our group here is building a new service where you can do exactly what you’re wanting online – so you get an editor to play around with stuff. Stay tuned for news on that.
Jeff, I really am learning a lot from your posts! thank you so much for doing this.
Two things I haven’t seen and can’t figure out
– how do I extract the DDL for a schema without signing on to that schema. I need to produce the DDL for several schemas into different files for each schema. It would be SO much easier if I could just I prefer to sign on once and “check” the all the schemas I want and then go.
– on a related note, I’m sure… when I want to compare two schemas, is there a way to do that without signing on to both of them. Again, I want to sign on as myself and compare two other schemas in the same database.
Thanks again for the great information!
Kate
1. Sign in as a ‘power’ user. You’ll need pretty high priv levels to generate the DDL – being able to query said objects isn’t enough. Then in the Database Export utility, go to Step 3 of the wizard. Click on the More button. Then pull the objects/schemas you want included in the export.
2. It depends. If the objects belong to the same schemas in both databases, then I think you’re golden. Otherwise, you’ll probably need to get the person or persons who do have those accounts to run the DIFFs for you.
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.
Log windows cannot be found.
view _ log does nothing. Started after a re-boot
Hi Jeff,
talking about reports… Is there a way to I derive(ou create) a variable, after de main select that should be passed to the child or drill down report?
mmmm, probably not, but if you tell me what you’re looking to achieve, mayhaps we can find a way 🙂
Hi Jeff,
I need to figure out relations between some schemata. Could SQLDeveloper help me to detect / cover all relations? Do you have other tools in mind which might assist me?
Two possibilities which come to my mind are a) to check database links and b) to check permissions / grants.
Thanks in advance!
Regards, mark
When you say ‘relations’ – I think of foreign keys. DB_Links and Grants – not so much.
So what are your requirements?
Have you seen me talk about the data modeler? I have a resource page link up at the top of this page – click on it, and check out the reverse engineer modeling posts.
Hi Jeff,
yes, I thought mainly of foreign keys, DB-links and grants.
I will check out the reverse engineer modeling posts.
Thanks, Mark
Hi Jeff,
We have added new columns to a number of object_types. These are used as inputs to procedures. When we synchronize these unit tests to pick up these changes in the respository, the current input data is lost, ie. NULLed.
We have a lot of tests for which we will have to retype the test data in again for the affected tests. As it’s quite tedious and very time consuming.
I’ve been looking at exporting each test and manually changing the XML file by adding the new columns and then re-importing the test back into the repository. Although, I don’t know if this will work yet.
What is the best practice for this scenario, other than manually retyping in the lost data again?
I’d probably try to do what you’re doing, have fun with the XML.
Hi Jeff,
When I added 2 columns to the record object type within the XML and re-imported the XML it NULLed the record type parameters for that object in the repository!
However, if I made no changes to the XML and re-imported this did not NULL the record type parameters.
Is there something else that needs changing in the XML for this to work?
Hello Jeff
For the life of me I can’t get Drill Down working between two custom reports in SQL Developer 4.0. The first report is a chart – and the source query returns (amongst other things) a field called report_date.
The report I want to drill down to accepts REPORT_DATE as a bind variable and the query refers to :REPORT_DATE (always uppercase).
I’ve added the second report to the drill down list of the first report – but when I select a “column” on the chart of the first report and then choose to drill-down to the second, the bind variable isn’t carried across and the second report runs using the last parameters it was explicitly run with.
If I use a table instead of a chart as the first report then the drill down works as expected.
Is this a known bug or is it just possible I haven’t built the chart correctly?
Thanks,
– Matt
Actually it WAS a bug – and it ALSO drove me nuts. I’m pretty sure it’s fixed in v4.0.3.
Just upgraded to 4.0.3 and can confirm the linked reports now working beautifully. Thanks Jeff.
Suh-weet!
Hi Jeff,
I have one database in which the completion of table and columns names does not work. Also, the dependencies are not shown when I view a table’s properties.
(Other databases which I access from the same instance of SQLDeveloper are fine.)
Maybe these two problems have different causes, but I suspect there are some privileges missing.
Is there a list of privileges/roles/obejcts that a database user needs for different SQLDeveloper features?
Thanks!
Hi Jeff,
Thanks for your great work with SQL Developer.
Why is it when I open my data model in either SQL Developer or the Data Modeler, my foreign key constraints gets a v1 appended to the name? e.g. if my Fk is named BEER_FK, when I re-open the diagram they are renamed to BEER_FKv1
Hi Jeff!
This is regarding Xmetal. when I am converting word documents into XML by using unix commands(Dos to Unix)..in which word documents contains all the image path but The conversion Save as XML, strips all the pictures out and the AVO tagging will be created when converting. when I am opening Xmetal i am NOT finding the path in any AVO element. can you please help me in this to get all the path.
Hi Jeff!
I’m trying to figure out how to add parameters to a spatial index (i.e. “sdo_dml_batch_size=10000”), but I can’t figure out for the life of me where to go to set those parameters in SQL Data Modeler.
Anyway, thanks for the great site – been very useful as I transitioned from Toad back to SQL Developer/Data Modeler.
Thanks!
Rich
Hi Jeff,
I’m trying to include scheduler objects in my install scripts. I’m using the create like option in developer, then I copy / paste the sql text in my script. I was wondering what happens on the background? How can I export the DDL automaticly, like you do? The dbms_metadata doesn’t work because I’m missing arguments…
Thanx in advance.
Hi Jeff,
is there any chance of connecting to an old Oracle Database 8.0.5.0.0 with a recent 4.0.3 SQL Developer?
Thanks in advance for any hints.
No.
The jdbc driver doesn’t support 8.0.5 databases, so it’s basically impossible.
I’m sure you’ve heard this a 100x before, but 8.0.5 is beyond old. Any chance you can just move that data into a newer database?
Yeah, well, I’m actually in the process of migrating, yes 😉
But it is unfortunately no option to just “shutdown” the 8.0.5 for the time of the migration to 11g or 12c: daily work has to be accomplished on the 8.0.5.
Are there any – maybe unsupported options – you’re aware of?
What I want to avoid is to use the corresponding old 8.0.5 client applications as working with SQL Developer is way faster. 🙂
You can try getting a copy of SQL Developer 1.0 or 1.5 and see if that ancient jdbc driver will let you into an 805 instance – but that’s so old I doubt you find it much better than what you have now.
I’m guessing the data in your 805 instance is pretty much dormant? Just getting the data out to somewhere else for easier access wouldn’t take more than a few minutes/hours depending on the volume. So what i’m talking about isn’t that you should migrate the entire stack/application – just copy the data out to query from somewhere else.
Sorry for the late answer:
No, unfortunately, the 805 instance isn’t dormant at all … the reason we want to migrate … 😉 So, just exporting relevant data won’t help much as it will be outdated just the second it has been exported.
Guess, I just have to cope with the fact that I have to use the old 805 sqlplus to connect to the instance as long as we have not yet migrated to a recent version.
But thanks nevertheless for your help!
So consider this another reason to upgrade sooner vs later.
trying to install sqldeveloper on my mac osX. i get the error “SQL Developer” can’t be opened because the identity of the developer cannot be confirmed.
i can’t find reference to this error on the forums…
any help is appreciated.
You need to go into the Mac security settings and say it’s OK to open programs downloaded from the Web. We had a signed certificate in there for 4.0.2 but something happened with the 4.0.3 distribution. We’ll get it back going again for 4.1.
I want to join 2 tables now in same database, (which I could do using database link and a create table query). The join create a Cartesian product and unfortunately I need all of the rows generated by the joining of two tables.
Is there a way to efficiently have this operation done without me hogging the resources?
A cartesian product of millions of rows over a db_link? I don’t think I can help you.
Hi Jeff,
Since yesterday I am trying to create a new table under my user by connecting 2 tables in different databases. I was successful creating a DB link but I do not have access to create a VIEW or a Synonym.
I want to create a new DB1.Table3 from DB1.Table1 and DB2.Table2 by joining them using a common ID. We are talking about BIG data here as there are practically multi million records that I am trying to bring under one table.
Any guidance would be helpful!
Thanks again for your awesome posts and insightful blog as always!
Have you tried querying across the db_link? Is it performant? It won’t get any faster once you bring in a join.
Recommend you consolidate both tables into a single database.
Yes, I have tested the DB Link and it is working fine. I would not be able to consolidate the tables myself or even via a DBA. Though I could copy DB2.Table2 in DB1 using a simple select statement via DB Link.
Using the current imported table, now I am trying to join and create a new table. Not sure how much time it would take.
Is there any other suggestion, now that I was able to copy the table in other DB2 to DB1?
I’m confused, do you want to copy the data over, or just create a view that joins the data together so you can query it?
Hi Jeff,
I just set up the VM from Kris Rice on my machine. Thanks for the article!
However, in SQL Developer 4.0.3 on the VM, it does not recognize PDB1 as a service whenever I attempt to add a connection per your screenshot.
I was able to add a CDB instance connect to HR using the ORCL service and 127.0.0.1 as the host.
Do you have any idea what I may have done wrong? Also, is there any way to modify the TNS or listner files on the VM? And finally, at the expense of sounding daft, what is the PDB?
Thanks!
In the latest VM, Kris updated the name of the databases.
ORCL is the pluggable and CDB1 is the container – that you can connect as HR tells you that you are in the PDB. HR isn’t a common user and as such you can’t login to the container as HR.
You can see what’s what via the listener. Go to a terminal/shell prompt, and run
> lsnrctl status
that will bring back the databases being serviced.
You can also open SQL Developer inside the VM and see the default connection defined there, which is I think to the pluggable. There should also be a message on the desktop in the VM that explains the basics.
BTW, I’m only about 20 minutes from the main Wake Tech campus. I can swing by if you need help 🙂
Okay, got it! Thanks for the fast response and the offer to help!
Hi,
I’m using sql developer 3.0.03 on Win7 x64.
I’m having a problem when saving query results.
When manually trying to alter file saving location it doesn’t work.
The browse button is also not working.
Can this be caused by win/ UAC?
Regards
I doubt it, but you never know…run as an ADMIN to test.
Otherwise, update SQL Developer to v4.0.3 and get the latest JDK7 or JDK8 – you will be MUCH happier.
Thank you Jeff! Confusing, but knowing this helps!
Hi,
I have a doubt and it might come across as a very silly question. But I am gonna ask anyway. I have recently Downloaded Oracle SQL Developer 4.0.3 Version on Windows 7 64-bit.
But What is 11g? Is it another name for 4.0 Version or some instance or something I am unaware of?
It would be great if somebodycould guide me on this.
Cheers,
Database versions go something like this…
SQL Developer started to be included with the Database around version 11. But it has an independent numbering system. The current version, 4.0.3, came out this Summer.
You also have OEM or Enterprise Manager or EM12c which is also numbered separately and also many Oracle Applications which are ALSO numbered separately. This can get very confusing.
Welcome to Oracle 🙂
I am new at SQL Dev CTRL+Spacebar will kind of keep what you want saved?
Hello Jeff, I have SQL DEV version 4.0.0.13 and the Autocomplete for data tab filter does not work, how do i fix that
You can’t fix it – we took out the ‘Automatic’ bit on purpose. See here for details.
Ctrl+Spaces invokes the completion insight helper. History you can see that on the dropdown, and it should be saved for that session of SQL Developer.
Invoking the completion will also show the history items.
Hi jeff
I recently installed oracle on a virtual server and i aslo wanted to use sql developer bt it brought this error:
the application or DLL C:\program files\sql developer\jdk\bin\msvcr100.dll is not a valid windows image. please check this against your installation diskette.
Thanx.
I’ve not seen that exact error message before, but try finding that dll file and copying it down to your sqldeveloper/bin directory
Hi
I am using SQL developer version and the previous software that i was using was TOAD.
One of the things i really thought was nice about TOAD was when i wrote a query it would auto correct the “form” to “from”
Eg: SELECT * form dual would be corrected on the fly as SELECT * from dual.
Is there an equivalent of setting up auto correct feature in SQL developer
Thanks
George
That sounds familiar 😉