Subprogram inlining in 11g

| Nov 28, 2007
Oracle extends the scope of its PL/SQL compiler optimisation. November 2007

Pls-00436 removed in oracle 11g

| Nov 28, 2007
Oracle relaxes the long-standing FORALL implementation restriction in 11g. November 2007

How to Import the PUTTY Settings from One Machine to Another Machine

| Nov 25, 2007
Most of the DBA's work 24 x 7 and finds difficiult and time consuming to configure the Whole List of Server settings in PUTTY. I had gone through Google hits and found one workaround to import your putty settings .

Work Around

1. Run the command --> regedit /e "%userprofile%\desktop\putty.reg" HKEY_CURRENT_USER\Software\SimonTatham at the command prompt.

2. Copy the Putty.exe and putty.reg onto Target Machine.

3. Right Click the putty.reg and click the option "Merge", this will import the settings to the target registry, and after that you can see all the server details which were defined (more...)

Copy and Compress the Datafiles using multiple Processes.

| Nov 22, 2007
Following are the scripts used for copying and compressing the Datafiles within the Same Server.


1. ------> This File consists of all the functions which are used for copy and compress.

2. ------> This File consists of commands used for copying , compressing , uncompressing . This file is being called by the

3. worker_no ------> Define the No. of Workers (Process) for the whole process. This value can be dynamically changed by using the command , echo 4 > worker_no.

#! /usr/bin/ksh
# bkp_dir_path is the source (TO (more...)

Upgrading the Recovery Catalog Database from 9i to 10g

| Nov 19, 2007
The Rman Catalog Upgarde is same as normal Database upgrade and can be accomplished in two ways.

a) Updrade the Database from 9i to 10g
- Connect to rman catalog datase.
- Issue the rman command "upgrade catalog" as this upgrades the catalog database from 09.02.00 to

b) Fresh Install of 10g Database / Use the existing 10g Database.
- Create the rman user and grant create session, recovery_catalog_owner, create type to rman user.
- Export import of Rman Schema
- Issue the rman Command :upgrade catalog"

Note:- You will encounter the below warning (more...)

Joe’s Blog: Happy First Anniversary SQL Snippets!

| Nov 18, 2007

Well, it's been exactly one year since SQL Snippets first appeared on the web as a prototype site containing 33 pages viewed by a handful of visitors in its first month (a few close friends and the occasional searchbot). It now has over 250 pages, RSS feeds, site search, and HaloScan commenting. It gets thousands of visitors each month from around the globe (including the loyal searchbots, we've become steady friends this past year) and has been blogged about, StumbleUpon'd, and'd. (*ouch*) I'd like to say a big THANK-YOU to all of you who helped spread the (more...)

Columns to Rows: UNPIVOT (11g) (New SQL Snippets Tutorial)

| Nov 17, 2007
SQL Snippets "Columns to Rows" section has been expanded to include a topic on using Oracle 11g's new UNPIVOT clause, which makes all prior techniques for transforming columns into rows now obsolete.

Sizing the UNDO TABLESPACE for Automatic Undo management

| Nov 15, 2007
Sizing an UNDO tablespace requires three pieces of data.

- (UR) UNDO_RETENTION in seconds
- (UPS) Number of undo data blocks generated per second
- (DBS) Overhead varies based on extent and file size (db_block_size)

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

Two can be obtained from the initialization file: UNDO_RETENTION and DB_BLOCK_SIZE.
The third piece of the formula requires a query against the database. The number of undo blocks generated per second can be acquired from V$UNDOSTAT.

The following formula calculates the total number of blocks generated and divides it by the amount of time (more...)

Some issues with Oracle views as ActiveRecord source

I am using Ruby on Rails to publish data from existing “legacy” application on Oracle database which already have existing complex data model. I am defining additional database views on existing legacy data to which I grant select rights to Rails schema. And I am using Rails conventions when defining these views – view names as pluralized / underscored version of Rails model name, ID column as primary key and %_ID columns as foreign keys.

Typically this works quite well and I can use Rails find methods to automatically generate SQL SELECTs from these views. But for some legacy data (more...)

Enabling , Disabling , Change the password protection on the RDBMS and Tools (8.0.6) listeners in an Oracle Applications 11.5.x

| Nov 15, 2007
The steps in this guide should be performed after applying the following AutoConfig patches:-

3453499 (11i.ADX.F)
5225940 (Post ADX.F Fixes)

How to enable/disable/change the password

To enable/disable or change the password script must be used.

DB Tier: $ORACLE_HOME/appsutil/bin/
Apps Tier: $AD_TOP/bin/

This has the following syntax:-

Valid arguments for
help : get usage information
contextfile : provide Applications or DB Tier context file name

Set the Applications (APPSORA.env) or RBDMS ($CONTEXT_NAME.env)
Run one of the following (more...)

Oracle VM

Oracle VM is now available for download.

You can read the news here.

Oracle VM Homepage.

EclipseLink 1.0 Milestone 1: Now Available

We just made our first milestone build available for download. See the EclipseLink team blog for more information.

This is an important step for our project. Our plan is to produce monthly milestone builds as we work toward our 1.0 release. It does seem very odd to be starting (more...)

OCR Mirroring

In my last blog I talked about adding mirrors to your voting disk. Here I will document how to add mirrors to your OCR on a clustered file system like vxfs.

In the existing configuration there is only one ocr. The location of your ocr is specified in /var/opt/oracle/ocr.loc (on Solaris). It would be different depending on your port.

You need to add an OCR mirror when CRS is up else you get an error message like the one below

root@bart # /u01/app/oracle/product/10.1.0/crs/bin/ocrconfig -replace ocrmirror /u02/oracrs/ocr_file_02
PROT-1: Failed to initialize ocrconfig

For more details on why you (more...)

Changing DB 32-Bit to 64-Bit

| Nov 6, 2007

1. Ensure that there is ample free size for the 64bit release installation.
Recommended free space should be 3G.

2. Start the Installer GUI.

3. On the File Locations Screen, create a new name and path for the 64bit
oracle installation under the Destination.

A typical entry would be

Name: orahome920_64b
Path: /u01/app/oracle/product/9.2.0-64b

4. Proceed with the installation. Stop at the configuration assistant
configuration screen.

5. Install the latest 64bit patch set under the new oracle installation.


The instructions in this section guide you through changing the (more...)

Sql plan enhancements in 10g

| Nov 5, 2007
New plan features in 10g make SQL performance investigations much more simple. December 2004 (updated November 2007)

Voting disk mirroring

A while ago I had blogged on how to add a voting disk to an existing RAC setup here.

To be safe it is best to do this with the cluster down.
I did the same recently on a 2 node RAC on running Veritas SFRAC 4.1.

In the setup I am working on there is only one voting disk the location of which is
root@bart # /u01/app/oracle/product/10.1.0/crs/bin/crsctl query css votedisk
0. 0 /u02/oravoting/voting

With CRS down on all nodes you have to use the force option else you receive an error

root@bart (more...)

Blocked Patch files cause OPatch to fail with error code = 170

I had to apply a CPU patch to Oracle Home on Windows 2003 Server box.

Opatch utility was failing with the following error:

The patch directory area must be a number.

ERROR: OPatch failed because of problems in patch area.
OPatch returns with error code = (more...)