Recently in Oracle 10g Category

When using Oracle 10g features around the flashback technology it's important to know the differences between the individual options and their respective restrictions. The Flashback Table, Flashback Query, Flashback Transaction Query and Flashback Version Query are all based on undo (or rollback) data option, which primarily provides read consistency and rolling back transactions in Oracle 10g. However, Flashback Database is based on before images in the flashback logs and Flashback Drop is based on another 10g feature called the Recycle Bin, which is there to manage dropped database objects until their space is required to store new data.

Oracle Metalink has some very useful documents relating to these features:

• Note 249319.1 - Configure flashback database
• Note 270060.1 - Use Flashback Table Feature and Resolve errors
• Note 270535.1 - Restrictions on Flashback Table Feature
• Note 317499.1 - 10G Oracle Flashback Transaction Query - Introduction and usage
• Note 369755.1 - Flashback Logs-Space management
• Note:435998.1 - What Do All 10g Flashback Features Rely on and what are their Limitations
  

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.

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.

Support for conversion of intervals and timestamps to other formats is still not properly supported in Oracle 10g. The problems with the "interval" and "timestamp" data types have been around since version 9i and they haven't been fixed in 10g R2. You just have to ask why? There doesn't even appear to be a work-around by using the cast statement!
Take a look at this little scenario from Andrew Piskorski it shows some examples of how bad the problem is. The advice has to be stick clear of these data types.

In Oracle 10g the indentation used to visualize the level of individual statements within the overall execution plan is sometimes reported incorrectly. This report by Jonathan Lewis gives an example of how the visualization under 10g compares with that of 9i.

The bug is apparently partly fixed in 11g but it would appear you shouldn't take what you see for granted and best display the "level" as well.

Are you one of those people who still miss the Oracle SQL Language Quick Reference which was last distributed will version 7. Well search no more. The following links go a long way to filling that missing gap:

• Complete SQL Syntax
• Oracle Functions
• PL/SQL Syntax
• SQL Syntax
• Oracle Tables

If you pass this document to something like iSilox you get a tiny offline version for your PDA – just give it a try.

If you have a system with around 12 GB of free disk space and 1+ GB of memory as well as a reasonable CPU (2 Ghz), why not try the Oracle 10g Release 2 Desktop Data Center. I installed it on my Centrino Notebook which has 1.5 GB memory.

It includes a VM (virtual Machine) and ReadHat Linux as well as a RAC Installation of Oracle 10g R2. It takes around an hour to install (you only have to switch DVD's once). Then you have full access to a trial version of a pre-configured 10g instance. I found it particularly interesting to take a look at some of the Warehouse Builder functionality. Also being able to access the example data and front-end for OLAP was useful.

If you have time take a look at it. When you're finished, simply drop the VM, and it's all gone!

You can order a copy at this address here or by free phone 1.800.633.0821.

If you want to use the Oracle BI Suite or your own components without putting the Oracle Application Server (AS) into operation - well you can! Simply install the BI-Subset of the AS (called OC4J) which can run on you Database Server that will provide the middle tier for Oracle BI components. The OC4J is supplied within the Business Intelligence package of version 10g R2. Take a look at it this. It's currently available for Windows, Linux and Sun Solaris.


This is a list of the contents:

If you are looking for the release 2 of the Oracle AWM (Analitical Workspace Manager), you'll have to extract it from the Oracle Database Package of version 10g release 2. This link should help you. Currently available are the Windows, Linux, Sun Solaris versions, AIX5L and HP-UX PA-RISC.

In Oracle 10g we find some minor additions to the way the database handles hierarchical queries. This article explains how the use of CONNECT_BY_ROOT, CONNECT_BY_ISLEAF, and CONNECT_BY_ISCYCLE can have some major impact in the way you select data in hierarchical tables.