The SQL / PLSQL developper mantra...

I read an article written by the fabulous Tom Kyte that says something that I think for many years :

I have a pretty simple mantra when it comes to developing database software, and I have written this many times over the years:

• You should do it in a single SQL statement if at all possible.
• If you cannot do it in a single SQL statement, do it in PL/SQL.
• If you cannot do it in PL/SQL, try a Java stored procedure.
• If you cannot do it in Java, do it in a C external procedure.
• If you cannot do it in a C external procedure, you might want to seriously think about why it is you need to do it.

If you can do it in a single SQL statement, by all means do it in a single SQL statement. Do not waste time, energy, and CPU cycles writing procedural code that will run slower than regular SQL.

Oracle 10g R2 RAC & iScsi on Windows Server 2003

You can read there a POC of Oracle 10g R2 RAC on a Windows Server 2003 using an iScsi device from Wasabi...

Oracle Agent 10g and multiple Oracle Inventories

Find Oracle documentation (b16242) at there
exactly at here


There, we find "14.2 Enabling Multi-Inventory Support for Configuration Management"
Every time you install an Oracle software product on a host computer, Oracle Universal Installer saves information about the software installation on your hard disk. The directories and files that contain this software configuration information are referred to as the Oracle Universal Installer inventory.

When you use Enterprise Manager to monitor your Oracle software installations, Enterprise Manager takes advantage of information saved in the Universal Installer inventory.
As it gathers information about the configuration of your host computer, by default, Enterprise Manager assumes that you have one Oracle Universal Installer inventory on the host. Specifically, Enterprise Manager recognizes the inventory that Oracle Universal Installer uses when you run the Universal Installer on the host.
However, in some cases, you may have more than one inventory. For example, you may have worked with Oracle Support to clone your Oracle software installations. For those cases, you can use the following procedure to be sure that Enterprise Manager can track and manage the software information in multiple inventories on the same host.

Caution:
Enabling support for multiple inventories is optional and available only for advanced users who are familiar with the Oracle Universal Installer inventory architecture and who have previously worked with multiple inventories on a managed host. This procedure is not required for hosts where normal installations have been performed.

To set up Enterprise Manager so it can read multiple inventories on a host:

1. Locate the OUIinventories.add file in the following directory $ORACLE_HOME/_/sysman/config
   The Management Agent state listed in this example represents an installation for Database Control. For more information about the Management Agent state to use for other installations, see Section 14.2.1, "AGENT_HOME Versus AGENT_STATE Directories" .
2. Open OUIinventories.add using a text editor.
   Instructions within the file describe the format to use when identifying multiple inventories, as well other techniques for mapping Oracle Homes.
3. Carefully review the instructions within the file.
4. Add entries to the file for each additional inventory on the managed host.
5. Save your changes and close the file.

During its next collection of host configuration information, Enterprise Manager will start gathering software configuration information from the inventories that you identified in the OUIinventories.add file, in addition to the default inventory that Enterprise Manager normally collects.
Alternatively, to see the data gathered from the additional inventories before the next regularly-scheduled collection, navigate to the Host home page in the Grid Control Console, click the Configuration tab, and click the Refresh Data icon next to the page timestamp.

Note:
If there any irrecoverable problems during the collection of the default inventory (for example, if the inventory file or directory protections prevent Enterprise Manager from reading the inventory), inventories listed in OUIinventories.add file are also not collected.

If the Enterprise Manager is able to read the default inventory, but there is a problem reading an additional inventory listed in the OUIinventories.add file, Enterprise Manager issues a collection warning for those inventories. However, Enterprise Manager does collect the configuration information for the other inventories.


Before the script :

1. note on the "(for example, if the inventory file or directory protections prevent Enterprise Manager from reading the inventory)"
   Be carrefull of this... The IAS guys that have installed some of our ias instances, have installed them with specific users/groups... and my user (part of the dba group) hasn't the rigths to read the ias files... and then my agent discovers nothing... :-(
2. $HOME/OUIinventories.add.orig is the original copy of $ORACLE_HOME/sysman/config/OUIinventories.add
   Just to begin with a "clean" file each time we regenerate.... Even if original the file contains only comments... ;-)
3. iagt10g@uxhades2da > cat $HOME/oraInventories.txt
   /opt/app/oracle/app/oradev8i/oraInventory
   /opt/app/oracle/app/oradev9i/oraInventory
   /opt/app/ias/oraInventory
   /opt/app/ias/infra/oraInventory
   /opt/app/ias/Inventory/oraInventoryForms
   /opt/app/ias/Inventory/oraInventoryA916dev1012
   /opt/app/ias/Inventory/oraInventoryA927dev1012
   /opt/app/ias/Inventory/oraInventoryAsInfradev1012
   /opt/app/ias/Inventory/oraInventoryas1012infra
   /opt/app/ias/Inventory/oraInventoryas1012mid
   /opt/app/ias/Inventory/oraInventoryrepca_101203
   /opt/app/ias/Inventory/oraInventorya918dev101202
   /opt/app/ias/Inventory/oraInventorya917dev101202
   /opt/app/ias/Inventory/oraInventorya916dev101202
   /opt/app/ias/Inventory/oraInventoryaCOMAD00D
   /opt/app/ias/Inventory/oraInventorydevappn1013
   /opt/app/ias/Inventory/oraInventoryA949dev101202
   
   iagt10g@uxhades1da > cat $HOME/oraInventories.txt
   /opt/app/ias/iagt10g/oraInventory
   /opt/oracle/app/iagt10g/oraInventory
   /opt/oracle/app/iagtacc/oraInventory
   /opt/oracle/app/iagtadm/oraInventory
   /opt/oracle/app/iagtdev/oraInventory
   /opt/oracle/app/oraacc10g/oraInventory
   /opt/oracle/app/oraacc8i/oraInventory
   /opt/oracle/app/oraacc9i/oraInventory
   /opt/oracle/app/oraadm10g/oraInventory
   /opt/oracle/app/oraadm8i/oraInventory
   /opt/oracle/app/oraadm9i/oraInventory
   /opt/oracle/app/oradev10g/oraInventory
   /opt/oracle/app/oradev8i/oraInventory
   /opt/oracle/app/oradev9i/oraInventory
   /opt/oracle/app/oraedu8i/oraInventory
   /opt/oracle/app/oraedu9i/oraInventory
   /opt/oracle/app/oratst8i/oraInventory

###############################################################
# Christian Clemmen - 2005/07 - v1.0 - creation
# info@infor-conseils.be
###############################################################
#!/bin/ksh

cp $HOME/OUIinventories.add.orig $ORACLE_HOME/sysman/config/OUIinventories.add

for inv in `cat $HOME/oraInventories.txt`
do
loc=$HOME/oraInst.`echo $inv | cut -d/ -f6`.loc
echo "inventory_loc=$inv" > $loc
echo "inst_group=dba" >> $loc
echo "inventory: $loc" >> $ORACLE_HOME/sysman/config/OUIinventories.add
done

In the $HOME, we have now:
-rw-r--r-- 1 iagt10g dba 68 May 23 08:16 oraInst.oraacc10g.loc
-rw-r--r-- 1 iagt10g dba 66 May 23 08:16 oraInst.iagtdev.loc
-rw-r--r-- 1 iagt10g dba 66 May 23 08:16 oraInst.iagtadm.loc
-rw-r--r-- 1 iagt10g dba 66 May 23 08:16 oraInst.iagtacc.loc
-rw-r--r-- 1 iagt10g dba 66 May 23 08:16 oraInst.iagt10g.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oratst8i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraedu9i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraedu8i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oradev9i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oradev8i.loc
-rw-r--r-- 1 iagt10g dba 68 May 23 08:16 oraInst.oradev10g.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraadm9i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraadm8i.loc
-rw-r--r-- 1 iagt10g dba 68 May 23 08:16 oraInst.oraadm10g.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraacc9i.loc
-rw-r--r-- 1 iagt10g dba 67 May 23 08:16 oraInst.oraacc8i.loc

Each of them contains something like :
iagt10g@uxhades1da > cat oraInst.oraacc8i.loc
inventory_loc=/opt/oracle/app/oraacc8i/oraInventory
inst_group=dba

Content of $ORACLE_HOME/sysman/config/OUIinventories.add after script:
inventory: /home/iagt10g/oraInst.iagt10g.loc
inventory: /home/iagt10g/oraInst.iagtacc.loc
inventory: /home/iagt10g/oraInst.iagtadm.loc
inventory: /home/iagt10g/oraInst.iagtdev.loc
inventory: /home/iagt10g/oraInst.oraacc10g.loc
inventory: /home/iagt10g/oraInst.oraacc8i.loc
inventory: /home/iagt10g/oraInst.oraacc9i.loc
inventory: /home/iagt10g/oraInst.oraadm10g.loc
inventory: /home/iagt10g/oraInst.oraadm8i.loc
inventory: /home/iagt10g/oraInst.oraadm9i.loc
inventory: /home/iagt10g/oraInst.oradev10g.loc
inventory: /home/iagt10g/oraInst.oradev8i.loc
inventory: /home/iagt10g/oraInst.oradev9i.loc
inventory: /home/iagt10g/oraInst.oraedu8i.loc
inventory: /home/iagt10g/oraInst.oraedu9i.loc
inventory: /home/iagt10g/oraInst.oratst8i.loc

Mount ISO - Virtual cdrom

There are excellent software packages that allow users to mount ISO files as virtual CD-ROMs.

Microsoft has a free, 60kb program that does it! Of course, it is not supported... and it's not as friendly as the software listed above; however, it works and it's free.

You can download it here


Here's the readme:

Readme for Virtual CD-ROM Control Panel v2.0.1.1

THIS TOOL IS UNSUPPORTED BY MICROSOFT PRODUCT SUPPORT SERVICES


System Requirements
===================
- Windows XP Home or Windows XP Professional

Installation instructions
=========================
1. Copy VCdRom.sys to your %systemroot%\system32\drivers folder.
2. Execute VCdControlTool.exe
3. Click "Driver control"
4. If the "Install Driver" button is available, click it. Navigate to the %systemroot%\system32\drivers folder, select VCdRom.sys, and click Open.
5. Click "Start"
6. Click OK
7. Click "Add Drive" to add a drive to the drive list. Ensure that the drive added is not a local drive. If it is, continue to click "Add Drive" until an unused drive letter is available.
8. Select an unused drive letter from the drive list and click "Mount".
9. Navigate to the image file, select it, and click "OK". UNC naming conventions should not be used, however mapped network drives should be OK.

You may now use the drive letter as if it were a local CD-ROM device. When you are finished you may unmount, stop, and remove the driver from memory using the driver control.

Bug 5458753 SQL may execute in wrong schema

I found this issue this afternoon....
Waaw... completely crazy!!!!


This issue is very very rare in 10.1 but can occur
much more easily in 10.2.0.2 .

If different schemas have tables / views etc.. with identical names
and sessions from different schemas execute IDENTICAL SQL
statements then it is possible for such SQL sentences to get
executed against the wrong schema objects. This can result
in:

• Wrong results from SELECT SQL
• Logical data corruption for DML SQL
• Unexpected errors such as ORA-2291 / ORA-1 from DML
• Parse errors if the underlying table definition differs.

(eg: ORA-904 etc..)

eg:
Consider an application with seperate sessions executing
"select mycol from mytable where key=:B1" as user A and user B
where both users have a table called "MYTABLE".
In this case it is possible for user A to end up executing the
SQL against B.MYTABLE instead of against A.MYTABLE.

Workaround:
There is no simple workaround which can avoid this fully.
The issue can be avoided by prefixing object names with the schema name.
eg: In the above example change the SQL to use
"select mycol from A.mytable" for user A and
"select mycol from B.mytable" for user B.

If SQL cannot be changed then it can help to reduce shared pool
load (as the problem occurs when cursors are reloaded having
been aged out / invalidated). Using DBMS_SHARED_POOL.KEEP for
affected cursors can also help.



More info in Metalink note: 392673.1