So you just got your brand new machine at work. Party time, excellent*!
And, your friendly neighborhood IT guy even moved over all of your softwares, including SQL Developer.
But when you go to use it, none of your connections are there.
Or maybe the connections are there, but the connection passwords ARE not.
And really, who has the discipline to have 300 different passwords for all of the databases memorized and ready to recall at a moment’s notice? I thought so.
So, without further ado, here’s how to move those connections WITH their passwords intact, from your old machine to your new one.
Export Your Existing Connections
Import Them Back
Get that XML file you just generated to your new machine. I just emailed it to myself, probably not the most secure thing in the world, but I’m just playing around here.
Note that I’m telling it to RENAME connections coming in if there’s a conflict – I don’t want to lose anything I already have. I’m not that creative with my connection names, so I’m taking the safe route here.
Happy Jeff! And happy DBAs that don’t have to reset my 300+ passwords.
A Few More Connection Things You Should Know
- Connections can be organized with folders
- Connections can be distinguished in the UI with a color scheme
- Connections are now listed in the ‘pickers’ by active connections first, then alpha sort
- You can connect to other kinds of databases – helpful for migrating them to Oracle!
78 Comments
Hi Jeff,
I am using SQL Developer v4.1.1.19.59. Connection picker is not listing active connections first and even alpha sort for rest is not working. Have you come across such an issue? Any suggestions will be helpful.
Thank you,
KD
Thankyou, it was very helpful.
Thanks you very much.
I dont’ know password. So when I installed new sql developper, I had a little problem … 😉
you’ll have to ask the person who owns the database to reset your password and tell you what the new one is
Hi Jeff,
Is it possible to also export all the filters applied on tree items in SQL Developer?
For instance, every time a new computer or when we upgrade SQL Developer from one version to another, we have to apply manually all the filters on every team member’s user schema. In particular point, we apply filters on the [Other Users] tree branch item.
Would be nice to export and import these for every user and for every upgrade.
Thanks!
not via the UI, but all that stuff is stored in XML files so it’d be a matter of having the same connection names and files copied around
Sadly, it is not 🙁
sadly, what’s not?
Filters are not saved as part of the Connection XML export.
that’s not what i meant, there’s a separate xml file in the users’ setting directory that has the connection filters, but they’re tied to that users’ connections – so as long as they’re the same connection file, it might be portable/scriptable
What is the full path to the file and what is the name of the XML file?
this was very helpful
great!
I have exported DB connections from SQL developer.
Is it possible to import that DB Connections xml into TOAD ? Is this Supported.?
I will not be able to re-create all the DB connections in Toad, since passwords have been forgotten.
Thanks,
Shan
you’d have to ask the Toad folks, but I’ll just go ahead and say ‘no’
if you forgot your password(s), just ask the DBA to reset them for you
I got new laptop @ my office but iam not able to import a connection file in SQL Developer it says :
oracle.jdeveloper.db.ConnectionException: Could not retrieve connection details for database CASPARO. The error encountered was:
Missing class: oracle.jdevimpl.db.adapter.DatabaseProviderFactory1212
Dependent class: oracle.adf.share.common.ClassUtils
Loader: main:11.0
Code-Source: /C:/oracle/product/11.2.0/client_1/sqldeveloper/BC4J/lib/adf-share-base.jar
Configuration: system property C:\oracle\product\11.2.0\client_1\sqldeveloper\BC4J\lib\adf-share-base.jar
This load was initiated at main:11.0 using the Class.forName() method.
The missing class is not available from any code-source or loader in the system.
at oracle.jdeveloper.db.DatabaseConnections.getReferenceable(DatabaseConnections.java:713)
at oracle.jdeveloper.db.DatabaseConnections.getProperties(DatabaseConnections.java:733)
at oracle.dbtools.raptor.connections.ConnectionGrinder.doImportExport(ConnectionGrinder.java:222)
at oracle.dbtools.raptor.connections.ConnectionGrinder.invoke(ConnectionGrinder.java:117)
at oracle.dbtools.raptor.navigator.DatabaseNavigatorController.handleEvent(DatabaseNavigatorController.java:199)
what version of sqldev did you export the connections FROM and what version of sqldev are you trying to import them TO?
Hi Dan,
somehow this does not seem to work for us, as it appears impossible to re-import connections exported on a Mac (SQLDeveloper version 4.0.2.15) on a Linux (version 4.1.3.20). It just won’t accept the password (there seem to be a few more problematic constellations, but I’m only aware of this particular one).
does it work if you import it to another mac, or to another copy of version 4.0.2.15?
An error was encountered performing the requested operation:
Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
Vendor code 12505
What does that have to do with migrating connections with passwords?
THAT WORKED THANK YOU SO MUCH!! 😀
I downloaded 4.0.3 Oracle SQL Developer and when I try to run it now, it gets stuck on “Migrating User Settings”. It just sits there with the progress bar half full. I tried to remove the program and reinstall but the same thing happened. I am NOT tech savvy whatsoever. Please help. Thank you.
You want to find the 4.0.3 directory on Windows that SQL Developer is using to store your settings – and delete it.
great. very helpful.
hi,
Is there a similar way to export preferences ?
Thanks,
N.
Not via the UI, but if you just zip up the system4.1 directory, and put it down somewhere else, you’ll have all of your preferences and more from that install.
Very handy, just what I needed. It works from 4.1 to 4.0.3 🙂
Is there a silky smooth way to generate all connections from a tnsnames.ora file?
No – but you’re not the first to have asked for this.
Is it possible to migrate connections between different versions of SQL Developer. Particularly I’m interested in migration from version 4 to version 3. Usually I receive an error:
oracle.jdeveloper.db.ConnectionException: Could not retrieve connection details for database AML_CMS_U5_jd_nie_dziala. The error encountered was:
Missing class: oracle.jdevimpl.db.adapter.DatabaseProviderFactory1212
Dependent class: oracle.adf.share.common.ClassUtils
Loader: ide-global:11.1.1.0.0
Code-Source: /C:/Program Files (x86)/SQLDeveloper/modules/oracle.adf.share.ca_11.1.1/adf-share-base.jar
no, you can’t back port, sorry
Actually you can, just without the saved passwords.
In the SQLDeveloper you are importing, create any connection and perform an export. The XML file will contain a Factory tag like this:
Copy the Factory tag.
Now open the XML you are going to import, and replace all Factory tags with yours.
Import should be possible then, just without saved password. All you need to do is re-enter password on first connect.
Formatting ate my XML… here’s what the factory tag should look like.
<Factory className=”…”/>
Hi,
Developer save the connection information under C:\Users\UEBI\AppData\Roaming\SQL Developer\systemx.x.x.x.xx. Is it possible to tell Developer to find those informations in another place on network?
Thanks for the post. very clear instructions.
You’re very welcome!
The export/import for connections does not seem to work if you want to migrate connection from v3.x to v4.x. Is there a way to migrate connection to a newer version?
Between different computers?
Hi,
Thanks for the prompt reply.
Re #2 – it’s not documented anywhere – my guess was to just delete/truncate all the tables in the schema, but sometimes it’s not enough (sequences etc. although i saw none in the repository). If not from CLI – I would expect it to be documented somewhere, as automation tools can’t use GUI and everything relevant the gui can do should be possible to be done using CLI or documented scripts/guidelines (in example a sample purge script in the sqldeveloper folder).
The same btw for the repository creation. I was surprised it’s hardcoded in the app and not calling a script like dbca does.
I hope you get what i mean.
Thanks,
Mor
Hi,
We started checking the unit testing module of SQL Developer and i’m trying to think how to integrate it with our build server to be performed automatically (through Jenkins).
As I can see – to be able to integrate it we need the following 2 missing features:
1. related to this post – to be able to import/export connections through CLI. our builder duplicates schemas on a build DB and then upgrades them. the name of the schemas are not constant (but has a certain convention). or better yet – allow the CLI to use TNS connection instead of an SQL Developer connection name for the DB part (the repository is constant and single – i don’t care to define it once).
2. Purge the Unit Test Repository through the CLI – to begin all tests from scratch (mostly to delete obsolete ones and the test results, which import cannot do). currently it’s only possible from the GUI, which is a shame.
Would appreciate your feedback on the above.
Thanks,
Mor
For #2 – why not just use a SQL*Plus script to delete the rows? But I’ll add this request to our list…same for #1.
Correction: I didn’t WANT TO start a flamewar. I guess I have too much or too little coffee in my system 🙂
If you don’t want to start a flame war, don’t through around insults, even if buried within /Sarcasm/ tags.
But, I’m not upset.
I still maintain our passwords are as secure as your machine is.
If your machine isn’t secure, encrypt it.
And actually, that code sample from the blog post won’t work for v4.0…
Btw. storing passwords is not secure 🙁
http://blog.jooq.org/2014/06/05/sql-developers-securely-encrypted-passwords/
Sure it is. If you lose your computer, you’ve got bigger problems. Hey, can I have your password file?
*SARCASM* Well, I thought that SQL Developer was a professional tool, not just a toy :-p */SARCASM*
But really, I never thought about this and I wrongly assumed that it is stored securely. If I think about it, it must be insecure because the algorithm to decode the password must be on the client computer, because when you connect to the database using JDBC, you need password in plain text.
But I would be glad if SQL Developer told me when I want to store the password that this is not 100% secure and I should think twice. Just my 2 cents.
Btw. I love SQL Developer, it rocks and your blog is awesome as well. Keep up the good work.
100% secure?
We’ll have agree to disagree I suppose. You probably shouldn’t save your passwords ANYWHERE if you want 100% security, yes?
Yes. You are 100% correct. But when your DB password is something like “Ad45Soi5jwErn1kbY” because of some stupid company policy, you cannot remember it, so you either save it in SQL Developer, or write it down somewhere else, in your computer or on a sticky note (not that I would condone it). And I would like to store it the most secure way.
Anyway I didn’t start a flamewar 😉
If you work in a large company then they should be using a password manager or a password vault at least.
Yep used it about a month ago, works very well.
I used it a few months back when I had to change machines. Worked great!
Cool. Just wish you’d posted that a couple months ago.
Dan
Sorry Dan. I just happened to do this today, and remembered I hadn’t ever talked about it before.