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.
(Updated September 24: No problems whatsoever after upgrading to 4.0.3, the shared SQL History folder is still there and working.)
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.

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 *before* proceding with the next step, otherwise the installation with the shortest history will wipe out the files added by the others!

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.


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.

A useful tip for Mac OS X users

Reinstalling or restoring the operating system to a previous state in Mac OS X is definitely something you don't need to perform often, however it seems that the time has finally arrived. As I have a few machines in different locations, I wondered how I could create a backup copy of the original Snow Leopard installation disk in order to keep my original disk in a safe place.
Copying DVDs can be a tricky matter, but thanks to a great tip I found on Walter's blog, I could clone my original disk without any hassle (well, almost, the original Apple's Optiarc drive refused to write to the disk, but my secondary Pioneer drive did the job smoothly).

By the way, if you are experiencing problems with the WiFi connection in your Mac and the Mac is running on top of Snow Leopard 10.6.7, chances are you hit a mysterious bug that seems to affect Macs with this patch level and certain wifi access points. Since I upgraded to 10.6.7 my wifi network has become sluggish if not completely unresponsive.
So now, in order to demonstrate the theory, I need to revert at least one machine to 10.6.6 and see if that does the trick as some users claim, hence the need of using the original installation disk.

Regardless of the final result, the dvd cloning tip is something a mac user definitely needs to know.

OT: Mac OS X dns caching problem with VMWare

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

I was working with one of my Vmware Fusion VMs, when the VM autoprotect backup started.
I don't like particularly the way this feature has been implemented in VMWare Fusion as it doesn't offer any scheduling option, it just based on some funny "elapsed time since last backup" setting that changes depending on when you turned off and on the VM in the meanwhile, which is really a crazy thing in my humble opinion.
Back to my problem, once the backup was finished, just a couple of minutes later, as soon as i tried to navigate the apex application i was working on, i got weird HTTP 404 errors. This was happening both with Firefox and Safari, which made me think that the problem was not at the browser level, but somewhere else.
Restarting the VM had no effect whatsoever.
Fortunately, before attempting some fancy and desperate options, i tried connecting using the straight IP address of the VM instead of the DNS i am using all the time and it worked.
This convinced me that something wrong had happened with the DNS server or DNS cache, however, oddly enough, executing ping servername.local from Mac's terminal application was working just fine.
Is ping skipping the DNS cache perhaps?
Don't ask me.

After a quick search I could find on the web the command to flush the DNS cache on Mac OS X 10.5:

sudo dscacheutil -flushcache

Thereafter, with some satisfaction, the browsers resumed working properly using local dns names.

Why did the VM autoprotect backup mess up the DNS cache in the first place?
I am quite sure this was not the first time the autoprotect ran, why did i need to flush the dns cache this time?

Useful answers are welcome, indeed i got some preliminary feedback from the VMWare forum.

SQLDeveloper Data Modeler is now production

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

Just a "quickie" to let you know that as of today you can download SQLDeveloper Data Modeler tool release 2.0.0.57.0.

After installing as suggested on the official page, in case you are running the tool on Mac OS X, you might get the following java exceptions:

Exception in thread "main" java.lang.UnsupportedClassVersionError: Bad version number in .class file
at java.lang.ClassLoader.defineClass1(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:675)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:124)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:260)
at java.net.URLClassLoader.access$100(URLClassLoader.java:56)
at java.net.URLClassLoader$1.run(URLClassLoader.java:195)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
at java.lang.ClassLoader.loadClass(ClassLoader.java:316)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:280)
at java.lang.ClassLoader.loadClass(ClassLoader.java:251)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:374)

This means that your current java environment does not point to a supported JDK version, as it was in my case.
The current JDK version is pointed to by a link, as detailed below:

cd System/Library/Frameworks/JavaVM.Framework/Versions
ls -l

lrwxr-xr-x  1 root  wheel    5 Jul  1 08:52 1.3 -> 1.3.1
drwxr-xr-x  3 root  wheel  102 Sep 29  2007 1.3.1
lrwxr-xr-x  1 root  wheel    5 Jul  1 08:52 1.4 -> 1.4.2
lrwxr-xr-x  1 root  wheel    3 Jul 22  2008 1.4.1 -> 1.4
drwxr-xr-x  8 root  wheel  272 Aug 28  2007 1.4.2
lrwxr-xr-x  1 root  wheel    5 Jul  1 08:52 1.5 -> 1.5.0
drwxr-xr-x  8 root  wheel  272 Aug 28  2007 1.5.0
lrwxr-xr-x  1 root  wheel    5 Jul  1 08:52 1.6 -> 1.6.0
drwxr-xr-x  8 root  wheel  272 Jul 22  2008 1.6.0
drwxr-xr-x  8 root  wheel  272 Jul  1 08:52 A
lrwxr-xr-x  1 root  wheel    1 Jul  1 08:52 Current -> A
lrwxr-xr-x  1 root  wheel    3 Jul  1 08:52 CurrentJDK -> 1.5

Note that you can change the current JDK pointer by using the Java Preferences utility, found in the Utilities folder:


i preferred to update the jdk.conf file located in my home directory with the following text:

System/Library/Frameworks/JavaVM.Framework/Versions/1.6.0/Home

instead of changing the current JDK pointer (just in case something else stops working...), but may be this is not the optimal solution or you have a newer version, so check out which version applies to your specific case, at any rate it must be 1.6 or better. You can navigate to the folder /System/Library/Frameworks/JavaVM.framework/Versions and find out which versions are currently installed on your Mac.

Thereafter i was able to run SQLDeveloper Data Modeler by executing:

sh datamodeler.sh from the datamodeler folder.
I haven't investigated yet if i can start the modeler from withing SQLDeveloper as it was suggested some time ago.

Hope it helps

Updated July 2: According to Roel, SQLDeveloper Data Modeler requires a license for professional use.
Indeed if you carefully read the license agreement from start to end, you'll see significant differences compared to SQL Developer, one of the most significant being that one cannot use the product for internal development unless a valid license is purchased first.

See other articles about SQLDeveloper.

Oracle 10gR2 is finally available for Mac OS X on Intel

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

If you didn't hear the latest news, Oracle 10gR2 for Mac OS X is officially available for download.
Thanks to Scott Spendolini for reporting this exciting news.

Awaiting to finish the download and to begin the installation on Mac OS X 10.5.6.

Updated April 14. If you are primarily interested in getting Oracle Application Express to work on Mac OS X natively, then you'll have to wait until Oracle releases the missing server components as Apex is not yet supported. See Scott's posting.

SQL Developer, Mac OS X and the weird bug of key presets

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

A quick note for those who came across a weird bug in Oracle SQL Developer that makes its editor virtually useless under Mac OS X.
If you are wondering why suddenly the backspace key doesn't work or the arrow keys simply do nothing, it seems that, for some reason currently being investigated, the keyboard preset for Mac OS X is not correctly applied or it's not properly loaded.
If you go to the menu TOOLS/PREFERENCES/ACCELERATORS, then you click on the button Load Preset, and finally you choose "default Mac OS X", you should be able to get the keys to work again.
And thanks to -K- for the useful workaround!




See the OTN thread where this problem is discussed.

Apparently this elusive bug affects SQL Developer both 1.5.1, 1.5.3 and possibly 1.5.4 according to Sue's reply.

Finding your way out of SQLDeveloper's DBMS_OUTPUT pane

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

SQL Developer 1.2.1 build 32-13:
have you ever used the DBMS_OUTPUT pane inside Oracle SQL Developer?

Unlike my friend John Scott, i like to open several SQL Worksheet against the same database connection.
This must be the reason as to why i noticed one curious fact about the DBMS_OUTPUT pane that he wasn't apparently aware of, according to a recent message interchange.

In brief, if you open multiple worksheets against the same connection, you'll notice that each one comes with an associated series of tabs, among which there is the DBMS_OUTPUT pane (see the center of the picture in the screenshots below).
According to my personal experience, the pane associated with the first worksheet where you enabled the DBMS_OUTPUT will drive all the DBMS_OUTPUT of the session, as illustrated by the following series of pictures.

The first worksheet belongs to the first session. After enabling server output and after executing the procedure, it will display the content of the buffer:

The second connection has got its own DBMS_OUTPUT pane also displaying some output:

The third worksheet however is created against the first connection and it won't display anything.


However, if we switch back to the first worksheet, we find the output of the third worksheet...


This can be explained by looking at the active oracle sessions opened by SQL Developer, where it becomes clear that multiple SQLDeveloper worksheets against the same database connection will be mapped to the same oracle session:

select sid, serial#, username, module, action
from v$session
where program = 'SQL Developer';

SID     SERIAL#   USERNAME  MODULE                      ACTION
------- --------- --------- --------------------------- -------------
116     38309     SYS       SQL Developer            
117     8352      SYS       Oracle SQL Developer        Code Insight
121     13186     YOCOYA    Oracle SQL Developer        Code Insight
125     26877     YOCOYA    SQL Developer            

4 rows selected

SQL Developer uses two distinct sessions for each connection, one is marked as "Code Insight" and is for handling the object tree, the other one is for executing the SQL scripts. I opened two db connections and this accounts for the 4 rows that you see in the report above.

Although this makes perfectly sense from a technical point of view, it can result in puzzling results, as far as DBMS_OUTPUT is concerned.

Note also that if you set server output off and on again from in the third pane, then you will redirect the output to the active tab and the other one will stop being updated.

Not a big deal, but something to be aware of.

Creating an ISO image from a zip file

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

Quick link to the original posting, my thanks to Gary for sharing this useful tip for MAC OS X.

Some days ago i was working via SSH/VNC on my Mac OS X server where i needed to install Oracle 11G for linux 64-bit.

One of the great features of VMWare Fusion (present also in other VMWare products) is the possibility of attaching an ISO disk image as if it were a physical CD or DVD, avoiding the physical burning of CD or DVD media.
Unfortunately i hadn't such ISO image available, i had instead the source files in a zip file downloaded on the Mac.

I could have copied the files from the host to the guest operating system or executed from a shared folder, but i preferred to see if i could find a way of stuffing the content of a directory into an ISO image file using plain OS X commands.

Fortunately, Google came to the rescue and i stumbled upon Gary's blog, where i found such great tip, that i easily adapted for my case:

First of all unzip the archive:

unzip linux.x64_11gR1_database.zip

This will create a subdirectory called "database" into the current one.
Finally, create the ISO disk image:

hdiutil makehybrid -o oracle11g.iso -iso -joliet -hfs -udf database

Now you can mount the resulting ISO file through VMWare Fusion.
If for some reason you cannot run the installer, e.g. you get a "Permission denied" error, it can be that the "cdrom" is not mounted properly. By mounted properly i mean that a line like the following must be present in the /etc/fstab file:

/dev/cdrom   /mnt/cdrom  auto  exec,noauto,owner,ro 0 0

For some reason this line was present in a Xubuntu Linux VM i've created, but was not present in Oracle Linux and i don't really know if it is a "quirk" of the auto-mount feature or a normal behavior.