News, views, tips and tricks on Oracle and other fun stuff

Oracle Pre-Upgrade Utility

This is a guest post by David Clement, a veteran database engineer and a friend of mine. You can find him online at

A handy feature that I’ve just found out about is that there is a link from Metalink document 884522.1 to the Oracle Pre-Upgrade Utility. You can get the utility without downloading the entire installation set and unpacking it.

For me, the easiest way to use the utility is to invoke the non-XML version from SQL Plus. For example:

SQL> conn sys/<password>@<tns_entry> as sysdba
SQL> spool utlu112_prereqs.out
SQL> @@utlu112i
SQL> spool off

The utility checks some interesting things:

  • your core tablespaces, including System, Undo, Sysaux, and Temp;
  • your init.ora parameters, to detect obsolete and deprecated parameters and hidden parameters;
  • your timezone file;
  • your dictionary statistics.

If you did unpack the entire installation set, the utility script is in rdbms/admin.

2 Comments | Filed in Oracle | Tags: ,

Oracle DB 11g Password Expiry Gotcha

Jeff Hunter:

Something I discovered recently is that the DEFAULT profile for Oracle 11g sets the PASSWORD_LIFE_TIME to 180 instead of UNLIMTED by default.  Applications will encounter an “ORA-28002: the password will expire within X days” error message if you keep the default value.

To change the PASSWORD_LIFE_TIME, you:
ALTER PROFILE default LIMIT password_life_time UNLIMITED;

Read more about 11g new security related features here.

1 Comment | Filed in Links, Oracle, Tips | Tags: , , ,

Did You Know About File Watchers?

Starting with Oracle Database 11g Release 2, an event-based Scheduler job can be started based on the arrival of a file in a directory on the server. The file arrival event is raised by a new Scheduler object called a file watcher.

As per the docs: A file watcher defines the location, name, and other properties of a file whose arrival on a system causes the Scheduler to start a job. You create a file watcher and then create any number of event-based jobs or event schedules that reference the file watcher. When the file watcher detects the arrival of the designated file, it raises a file arrival event. The job started by the file arrival event can retrieve the event message to learn about the newly arrived file. The message contains the information required to find the file, open it, and process it.

Ittichai has an excellent example of using the file watcher. The following is similar example, but uses BLOBs instead of CLOBs. In this example, we want a Scheduler job to be triggered as soon as an image file is deposited into a directory on the database server. We want the job to be triggered once for each file. The job reads the image file and saves it into a BLOB column in a table. We will then use Oracle SQL Developer to retrieve the image from the table and view it in the built-in viewer.

Continue reading…

9 Comments | Filed in Oracle | Tags: ,

Neat Oracle Database 11g Release 2 Feature: Preprocessing External Tables

Arup Nanda:

External tables enable users to access data in text files, immediately eliminating the need to load input text files to intermediate tables for processing—saving both time and storage space. Now, with Oracle Database 11g Release 2, intermediate processing of any kind—such as decompression of compressed input files—is eliminated, further saving time and storage, not to mention a change to the existing code.

But the power of inline preprocessing is not limited to decompression alone. It can be applied to any type of prior processing required, as long as it produces an output that can be parsed by the external table. The directory listing is just one small demonstration of this rich capability of preprocessing in external tables. You can also use it to massage datafiles to fit a specific format, append or augment data from multiple sources dynamically—without creating any intermediate storage—or even send an e-mail when a specific text file is accessed by an external table. What you can do with inline preprocessing is limited only by your imagination.

Greg Rahn:

Before External Tables existed in the Oracle database, loading from flat files was done via SQL*Loader. One option that some used was to have a compressed text file and load it with SQL*Loader via a named pipe. This allowed one not to have to extract the file, which could be several times the size of the compressed file. As of, a similar feature is now available for External Tables (and will be in This enhancement is a result of Bug 6522622 which is mentioned in the Bugs fixed in the Patch Set note.

1 Comment | Filed in Links, Oracle | Tags: ,

Conventional (NOAPPEND) parallel inserts available in Oracle DB 11g

Randolf Geist:

Oracle 11g obviously has added the capability to perform a conventional, non-direct-path insert in parallel.

Greg Rahn:

Parallel conventional (NOAPPEND) insert was an 11g new feature, though it seems to have escaped the new features list in the docs. It was added to support cases where parallel insert as select was desired, but the restrictions associated with direct path inserts were not desired.
Comments Off on Conventional (NOAPPEND) parallel inserts available in Oracle DB 11g | Filed in Links, Oracle | Tags: ,

How To Manually Install XML DB On Oracle 11g


Due to Oracle Bug 9818995, if catqm.sql is used then not all installation steps are performed by this script… This document explains the full set of steps that need to be taken in order to perform a complete and working Oracle XML DB installation.
Comments Off on How To Manually Install XML DB On Oracle 11g | Filed in Links, Oracle, Tips | Tags: ,

Oracle Database 11g Interactive Quick Reference

Via Harald van Breederode:

Just a quick note about the Oracle Database 11g Interactive Quick Reference That was released today to the public. This Interactive Quick Reference, created by Oracle University and the Server Technology Curriculum group, is your Essential Guide to Oracle Database 11g Release 2. This interactive quick reference includes an architecture diagram, a comprehensive list of background processes and an extensive list of DBA views organized by product feature areas.

Nicely done! Wish the columns in the DBA views had comments though.

Comments Off on Oracle Database 11g Interactive Quick Reference | Filed in Oracle | Tags: ,

Virtual Machines Are Your Friends

VirtualBox, Virtual PC, VMware Workstation and a few other software packages help you create and run multiple virtual machines on your desktop or laptop enabling you to run multiple operating systems simultaneously on a single computer.

My personal favorite is VirtualBox (recently acquired by Sun Microsystems), not only because it is freely available as Open Source Software and runs on Windows, Linux and Macintosh but also because it has great features and supports a large number of guest operating systems.

Last Saturday I used VirtualBox to create a new virtual machine (VM) running Windows XP Pro and Oracle Database 11gR1. First, I created a “base” VM with only Windows XP pro SP3 installed. I then detached the virtual disk file (VDI) from the VM. I ended up with a VDI file that I can clone as many times as I want eliminating the need to install a new operating system every time I create a new VM.

Cloning a virtual disk in VirtualBox is done using the command line. For example, to clone WindowsXP.vdi as a new virtual disk called WindowsXPProOraDB11gR1.vdi, you would issue this command:

VBoxManage clonevdi WindowsXP.vdi WindowsXPProOraDB11gR1.vdi

You would then assign WindowsXPProOraDB11gR1.vdi as the virtual hard drive of a new virtual machine.

The installation of Oracle Database 11gR1 on a new Windows XP VirtualBox VM on my laptop went very smoothly, but I had to troubleshoot a couple of things on the VM: A missing loopback adapter and a 100% CPU usage.

My laptop and the newly created VM do not have a static IP address. Dynamic Host Configuration Protocol (DHCP) is used to assign dynamic IP addresses on the network. According to the documentation, before installing Oracle Database onto a computer that uses the DHCP protocol, you need to install a loopback adapter to assign a local static IP address to that computer. I followed the instructions found in the Oracle Database Pre-installation Requirements document to install the loopback adapter. After that, the database installed without any issues.

Now that my WinXP VM and 11g Database are up and running, I noticed that the oracle.exe process was consuming 100% of the CPU. I waited a few minutes, but the CPU usage did not go down, it stayed at 100%. After a quick search, I found these two forum posts: post 1 and post 2. Since this is a test database, I did not hesitate to follow the instructions in post 2 and was able to bring the CPU usage down to a normal level.

Virtual machines are a great way to learn and try new software and applications without the need to buy new hardware or messing up your existing computer. You may even have fun embarking on some virtual adventures.

2 Comments | Filed in Oracle, Technology, Tips | Tags: , , ,

PL/Scope in Oracle Database 11g – Revisited

Oracle Database 11g introduced a new feature called PL/Scope. A while back, I wrote about Dan Morgan’s experience when he compiled the package STANDARD for PL/Scope. I also wrote about Oracle’s answer, in which they said “…the reason to compile STANDARD would be to make its identifiers available in the new DBA_Identifiers view family (see PL/Scope). Our script to do this sadly missed the release. We’re about to post it on OTN.”

In fact, the PL/Scope documentation mentions a utlirplscope.sql script:

A database that has been upgraded from a release of Oracle Database that did not yet support PL/Scope will have no PL/Scope metadata—nor will a new Oracle Database 11g environment that has been ordinarily created. The DBA can rectify this by running the utlirplscope.sql script.

In an email, Bryn Llewellyn, Oracle’s PL/SQL Product Manager, was kind enough to point me to this page on OTN:

On the Subject of the utlirplscope.sql script

It turns out that a script is not needed after all:

Instead of simply providing a script to recompile STANDARD and DBMS_STANDARD for PL/Scope, we would first like to clear up some misconceptions about the usefulness of such a script and make sure you really do need to perform such an operation.

I suggest you read this OTN piece if you want to know the answers to the following questions:

  • What was utlirplscope.sql?
  • What is STANDARD and DBMS_STANDARD identifier data?
  • Do I need STANDARD and DBMS_STANDARD identifier data?
  • Do I already have STANDARD and DBMS_STANDARD identifier data in my database?
  • Ok, I have determined that I do not already have STANDARD and DBMS_STANDARD PL/Scope identifier data and need it. What now?

Important notes to take from this:

  • Not everyone needs STANDARD and DBMS_STANDARD identifier data.
  • When needed, it’s going to be mostly in development environments.
  • Compiling STANDARD and DBMS_STANDARD using utlirp.sql should be done while the database is in UPGRADE mode.

In conclusion, I agree with Bryn that PL/Scope is a fine feature. Just get your DBA to read the OTN reference above if your use case would benefit from having STANDARD and DBMS_STANDARD identifier data.

Comments Off on PL/Scope in Oracle Database 11g – Revisited | Filed in Oracle | Tags: , ,

Oracle Database 11g for Windows Available for Download Now

Just out, Oracle Database 11g Release 1 for Microsoft Windows (32-bit). Get it while it’s hot.

2 Comments | Filed in Oracle | Tags: ,