Tips and Tricks To Make Your Oracle Systems Your Most Valuable Asset

John Seaman, Editor - send us your questions/feedback here. Subscribe to our ezine for more tips and tricks just go to www.asktheoracle.net/oracle-tips-signup.html

Answers To Your Oracle Questions

Learn how to develop your Oracle systems better, faster and smarter with high quality Oracle training from our top consultants . See the Oracle training page for more info or click here for details of our scheduled Oracle courses .

1. Introduction

More questions from you and, more importantly, more answers from us. In this issue:

How to run multiple instances on one machine
How to run ".exe" files from a trigger
Is Oracle certification worth while ?
How can using stored procedures degrade performance ?

The resources section has recommendations for further reading to enhance your knowledge of Oracle and contains links to information on OTN and other places.

2. Oracle Questions

From: Sandeep Singh

I am new on Oracle 9i. I want to run two instance of one oracle database server on single machine. I already install oracle 9I on windows 2000 professional. I create one database named sandb and instance for that. I want one more instance for the same database on same machine. Please give the process for doing this.

Sandeep,

You can have more than 1 instance on a single machine but unless it's a multi-processor system and you're using Oracle Parallel Server or Real Application Cluster, they won't share the same database - i.e. a new instance means a new database. To create another database and instance, use the universal installer (Oracle 8i and above), but don't re-install the software, just create a new database.

---------

From: Sean Talbot

Hi, I need to run an executable from an Oracle Trigger, but can find no docs on on how to do this - please help! Thanks in advance

I don't know of anyway to run a ".exe" file directly from a trigger. However, you can call "external procedures" from pl/sql and therefore from a trigger. An external procedure in this context is a shared library (e.g. ".DLL" on MS Windows) as opposed to an executable.

There are a number of steps that have to be taken to be able to call an external procedure from your PL/SQL as follows:

Set up the listener for external procedures - this must be called EXTERNAL_PROCEDURE_LISTENER and is separate from the other listeners so will need to be added to the database start-up and shut-down scripts if you intened to use this feature on a regular basis.
Build the shared library file (may contain more than one external procedure) for your operating system
Ensure Oracle knows where to find the library by issuing the CREATE LIBRARY command in SQL*Plus and specifying the full path to the library file (e.g. CREATE LIBRARY ext_lib AS 'C:\WINDOWS\system32\extprocs.dll'). You need to do this once for each library you intend to use, as opposed to once for each external procedure you wish to call.
Write your pl/sql wrapper to call the external procedure. This comprises the pl/sql function or procedure header for the body with the rest of the body replaced by the external procedure interface definition. For example: CREATE OR REPLACE PROCEDURE extproc1 (p1 PLS_INTEGER) IS EXTERNAL LIBRARY ext_lib NAME "an_ext_proc" LANGUAGE C PARAMETERS (p1 int)
Call your pl/sql wrapper procedure from your trigger or other pl/sql routines in the same way you would call any other pl/sql procedure or function (e.g. extproc1(400) ).

Fpr more information about external procedures see Ch. 10 of the Application Developer's Guide - Fundamentals in your Oracle documentation. Also Steven Feuerstein and Bill Pribyl have a whole chapter on external procedures in their excellent book Oracle PL/SQL Programming which gets 4 stars on Amazon as the average review. Click here for Oracle PL/SQL Programming at Amazon UK

---------

From: K I Vijoy,

I am a software professional working in java for developing web based applications for a US Based company here in india. I would like to know whether taking a Oracle DBA 9i certification would brighten my career prospects or not. Kindly throw some light on this matter so that i can pursue the certification course as soon as possible. Thanks, Vijoy

Oracle Certifications are one of the most sought after marques of credibility for expertise in the Information Technology marketplace, according to Oracle. The OCP DBA certification was the only DBA credential ranked in the Top Ten most Valuable credentials by CRN.com 2002 (although apparently not for 2003). For more information about the retrun on investment in certifcation programs see http://www.crn.com/sections/Special/certification/certification.asp?ArticleID=44021 . Details of Oracle certification requirements can be found on our web site at
http://www.smart-soft.co.uk/Oracle/oracle-certification-demystified.htm . Oracle certification study guides can be obtained from certyourself.com . Also uCertify offer free practice questions for Oracle certification as part of their Oracle certification prepkit.

---------

From: Chakersh Katare ,

Hi, I am facing problem related to performance. I moved my all Sql statements from Client layer to Database layer in the form of Stored Procedures.I was thinking that performance of my Application will be improved but reverse is happening, performance is going down by nearly 30% . Why is it happening can u suggest some valueable ideas sothat i can improve performance of my database.

This is very strange, I can only guess that there are other factors that could be the cause of this loss of performance such as more users, adding or dropping indexes. My suggestion swould be make sure you analyse tables and indexes to give the optimiser accurate statistics. Pin the most frequently used packages into memory. Query the database to find out which queries are taking the most time or use the most i/o and then set about optimising them. Also make sure the Oracle server has enough real (physcial) memory on the machine where it is running. If PL/SQL code has been taken from the client and put on the server the overall memory requirements may well have increased. You will need to look at the performance statistics at the operating system level to determine this. Look for lost of processes being swapped out of memory and/or very high memory utilisation. Disk access is the slowest operation and swapping processes in and out of memory will cause very poor performance. The following book should throw some light on the problem if you're running Oracle 9i on UNIX

Or this one if you're using Windows

---------------------------------------

Looking for more Oracle tips and tricks ?

If you're looking for no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our ezine. Published monthly, it's jam-packed full of tips and tricks to help you make more of your Oracle systems and save you hours of blood, sweat and tears searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox.

Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320

Contact Us

Click here to view our privacy policy .

This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.