Oracle Database

Recently I had to export some PL SQL packages from an Oracle databse into flat files.

Before I was always exporting them using the "Export..." function available from the context menu of a package.

These exports were either incomplete because I did not tick the option "Export dependencies" in the export dialog or if I did tick the option, the tool exported also scripts to create objects like referenced tables and sequences.

Both options were not satisfying and led to me having to carefully manually edit the exported script files. This would mean to execute something on the target system without having tested it. In summary I did not feel comfortable and save with this export dialog.

Therefore I searched for another option and it is pretty obvious. Right click on a package and select "Package-Spezifikation und Body speichern...". This option will be named different depending on your SQL Developers language settings.

My SQL skills are good and I rarely faced major performance issues yet when using a real database. MS Access: Yeah of course I had performance issues, but I could most times solve them by aggregating data in temp tables as soon as possible. On a real database server even running only locally on a laptop I never had issues. Maybe because I always try to follow some basics, like using indices or appropriate joins and of course testing properly. Another reason might be that we have a pretty sick database server, which others would envy us for :0).

Today a new functionality I implemented should have gone live, but it did not pass User Acceptance Testing as the performance was very bad. What should have ran only a few minutes, did not finish within three hours and we had to kill the session.

Check all used views

Recently I had to rebuild some indexes, since they met two major conditions under which an index rebuild most likely will improve performance.

According to Burleson, an index rebuild can help under the following conditions.

  • High index fragmentation:  The SQL workload has lots of table DML causing lots of deleted leaf blocks.
  • High index scan access plans:  The SQL workload is rich with index scans (index fast-full scans and index range scans)

The second condition definetely holds valid for my clients database. The application, RSA IGL is used for over 10 years without any archiving or regular house keeping and up to 100 Million entries in several important tables.


This category will hold examples for Oracle SQL.

This category will hold Oracle SQL Developer specific articles.

A category holding articles for Oracles programming language PL SQL.