Oracle News

Having now had this problem installing the Oracle Warehouse Builder with version 11g and 10g I though it deserved a mention here in my blog. If the Repository Assistant is just vanishing around 87 / 88% percent and you can see a final entry of “I am in processLoadJavaToken” or under unix you may get a message from the assistent 'reposinst.sh' saying "Failures occurred during processing" this might be helpful to you.

What is going on is, that part of your xml code is corrupt in the database, but that is not apparent! All you need to do is go to your installation directory “ORACLE_HOME” and switch to rdbms/admin then de-install and re-install the xml code like this:

1. log in as sysdba ("export {SID}; sqlplus / as sysdba")
2. enter "@$ORACLE_HOME/rdbms/admin/rmxml" and press enter. Wait for it to finish.
3. enter "@$ORACLE_HOME/rdbms/admin/initxml" and press enter. Wait for it to finish.
4. exit

After that remove any repositories before re-running the assistant. Also remove any roles installed by OWB.

The Oracle Optimizer is continuously undergoing scrutiny and being tuned. In Oracle 10gR2 and version 11 some work has been done to improve the elimination of unneeded tables in joins. Although that might sound stupid to reference tables which are not required that is not as uncommon as you might think. Particularly in generated statements and views which select several tables, of which the user may only require data from one or two tables, this feature can have a great benefit.

The following article goes into more detail of how this feature can benefit your environment and what limitations exist.

Well it has taken me sometime to get around to it, but finally I've added the Google WEB 2.0 Search API to this site. So now you can directly continue your search for anything Oracle specific or otherwise from here. As a default you get anything current in the way of Oracle news from other sites and blogs.

If you are interested in this facility supplied by Google then check out the documentation on their website. There is nothing to install but you do have to register with Google by opening an account to get a site specific key but that is pretty painless.

If you are looking for a comfortable way of efficiently logging information within your Oracle database or you want a seamless logging from within your Java application which already uses log4j and uses an Oracle database - look no further. It is part of the Apache project and can be freely used within production solutions. So take a look here for details on log4plsql.

If you encounter an error "ORA-39700: database must be opened with UPGRADE option" you probably have a database instance which hasn't yet been converted. That is to say, you have more than one instance running on the same database but the instance you want to start hasn't yet been converted to the correct version. This can happen when you restore an old backup to your database server. The steps needed to complete this are very simple:

1. make sure your ORACLE_SID points to the instance you want to convert

2. start sqlplus / nolog

3. connect / as sysdba

3. use "startup upgrade" to boot the instance in resticted upgrade mode

4. run "catupgrd.sql" stored in "$ORACLE_HOME/rdbms/admin"

5. shutdown the instance "shutdown immediate"

6. restart "startup"

7. finally run "utlrp.sql" to compile any invalid objects

... and that should be it!

When tuning a database you have to contend with a vast number of possibilities as to how to go about it. Whilst anything Oracle supplies in the way of tools and techniques can be regarded as a valid means of correcting some unwanted behavior there are some tools which should be avoided in production.

The use of "Hints" in particular should regarded as an exploratory method for diagnosing what could be done to improve things or what might happen when a database gets loaded with large amounts of data and performance starts to degrade. Using hints in a production environment should only be done as a last resort and as a temporary work-around until some more robust correction can be installed. This article 'Rules for Hinting' by Jonathan Lewis demonstrates why that is.

So be warned - there are almost always better solutions, which could be used instead of hints.

In Oracle 10g R2 their would appear to be a bug when enabling a job via a user owned procedure like this:
BEGIN
MYUSER.PKG_JOBS.ENABLE_JOBS;
END;
which my activate a whole bunch of jobs in a schedule.

Say the MYUSER has been granted MYROLE which is defined as:
grant create job,
execute any program,
execute any class,
create external job,
manage scheduler TO MYROLE;

In Order for the procedure call above to execute a:
DBMS_SCHEDULER.ENABLE ( 'MYJOB' );
which uses an EXECUTABLE (external) shell or program to run you must grant the 'create external job' directly to MYUSER.

Interestingly if MYUSER enables the job by calling DBMS_SCHEDULER.ENABLE directly it works fine but not if the enable is stored in a package which belongs to MYUSER.

This useful "GUIDE TO RUNNING EXTERNAL JOBS ON 10g WITH DBMS_SCHEDULER" may help in understanding what issues are involved with calling external jobs under Oracle.

Using the Oracle package DBMS_SCHEDULE for running internal and external jobs is a great way of controlling what goes on with your database system. However, when running external jobs such as shell scripts or java it is important to understand a couple of things.

1. Oracle Execution Environment
All external jobs are executed by default with a low privileged user called nobody. Oracle uses the definitions found in "$ORACLE_HOME/rdbms/admin/externaljob.ora". By default this user doesn't even have a PATH variable set.

2. Unix Return Value
The exit code should be zero if all is okay. Anything other than zero causes the Job to be displayed as failed. Oracle captures the error output (STDERR) from the job and saves this along with the execution code from each job. This can be a multiline output, so you can do something like this:

echo "Starting $1 at " `date` >&2
...
echo "Finished execution at " `date` >&2
exit 0

3. Parsing Parameters
You can parse parameters (or arguments) to an external program by specifying the "NUMBER_OF_ARGUMENTS" when the job is created. You must also create the job disabled and then add the arguments and set the job to enabled:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (

JOB_NAME => 'MY_TEST',
JOB_TYPE => 'EXECUTABLE',
JOB_ACTION => '/bin/ple_test.sh',
START_DATE => SYSTIMESTAMP,
REPEAT_INTERVAL => 'freq=minutely; byminute=2,12,22,32,42,52;',
END_DATE => NULL,
ENABLED => FALSE,
NUMBER_OF_ARGUMENTS => 1 );

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('MY_TEST',1,'Hello World');
DBMS_SCHEDULER.ENABLE('MY_TEST');
END;