Friday, August 22, 2014

Sharing SQLDeveloper SQL history across several machines with Dropbox

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

A quick tip for keeping a single shared SQL history repository among different installations of SQLDeveloper, for the same user, on distinct computers synchronized with Dropbox.
This seems to be working just fine on Mac OS X and SQLDeveloper 4.0.2 however it's a totally unsupported hack, which means ** use at your own risk **.

I ignore what will happen when I'll upgrade to the next release of SQLDeveloper but I'll find out sooner or later.
From what I've seen, SQL Developer makes a copy of the history folder when it upgrades an existing installation, may be it crashes or may be I'll have to repeat the whole procedure, I don't know, for the moment I really appreciate the fact that I can pick up a work on a different location exactly from where I left off without having to worry about making copies of certain queries manually.

From the shell prompt, assuming you are in your home folder and SQLDeveloper is NOT running:
cd .sqldeveloper
cp -R SqlHistory ~/Dropbox/.SqlHistory
mv SqlHistory SqlHistory.old
ln -sf ~/Dropbox/.SqlHistory/ SqlHistory

SqlHistory.old is your safety net, in case something goes wrong.

Then, on the second machine you may either decide to get rid of the current history and use the history shared from the first machine or you may try to merge them.

In the former case, just execute:

cd .sqldeveloper
mv SqlHistory SqlHistory.old
ln -sf ~/Dropbox/.SqlHistory/ SqlHistory

In the latter case, in addition to the three steps above, execute also:

cp -R SqlHistory.old SqlHistory.tmp 
mv -n SqlHistory.tmp/* ~/Dropbox/.SqlHistory

Any duplicate filename (albeit with different SQL content) will remain in the SqlHistory.tmp folder, so you have a chance to decide what to do with it. If the SqlHistory.tmp is empty at the end of the operation, then get rid of it.

SQLDeveloper keeps the log as a bunch of XML files whose names are generated by prepending a pseudo random number (I ignore the details) to the fixed string "history.xml".
As I don't know if this number can be unique across different computers in virtue of some clever algorithm, I guess that occasionally there could be a conflict with an existing one, but when I merged the histories of my SQL Developer installations, there were no conflicts at all (may be the magic number is based on the timestamp, which explains why there were no conflicts as I haven't been given the gift of ubiquity...)

If there is a third machine, repeat the same steps as for the second one and so on.

As your SQL history is going to be bigger than before, it makes sense to increase the maximum number of statements that you want to keep, to do so go to Preferences/Database/Utilities/Worksheet pane and increase the SQL history limit and remember to do so on all the machines, setting the same value.

Probably it could make sense to do the same with the library of connections, but this is an exercise I'll keep for another occasion.

I guess the same procedure should work also on most Linux distros as I don't see any difference under the hood.

Tuesday, August 12, 2014

ORA-27476: "SYS.MY_FANCY_JOB_CLASS" does not exist

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Ever exported a whole schema containing DBMS_SCHEDULER jobs with non-default job classes?
Chances are that you hit the following error message when you attempt to import the dump later on.
ORA-39083: Object type PROCOBJ failed to create with error:
ORA-27476: "SYS.MY_FANCY_JOB_CLASS" does not exist

Job classes defined with DBMS_SCHEDULER.CREATE_JOB_CLASS are not owned by the schema user but by user SYS (as inferred by looking at the error message itself).

As a result, any jobs whose job class has not been re-created before importing the dump, won't be (re)created.

If, for some reason, the script for creating the job is not readily available but you have access to the database where the dump was taken, you can quickly generate a script for the job via the "Create like" feature of SQLDeveloper.
Note however that the missing job class must be (re)created manually, SQLDeveloper doesn't help with that.


See message translations for ORA-27476 and search additional resources.

Glorious mysteries of Oracle's web site

God or Larry knows why Oracle feels the compelling need of changing some of its page links over time.

Oracle Application Express' main page URL is now:


It was:


May be I am a bit slow in picking up these changes, I don't really know if this happend one day or 6 months ago, at any rate this is it.

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles