Metalink, SCM, the error and the good stuff

Yesterday I got an e-mail from Oracle Support letting me know about the new Metalink interface which is supposed to go live this Fall:


Dear MetaLink Customer,

Oracle is committed to consistently improving your customer support experience. In the fall of 2008, MetaLink will have a new user interface. To (more...)

Database upgrade apex.oracle.com

Read my blog post Database upgrade apex.oracle.com.

Using EJBContext from an EJB 3 Interceptor

I got a question from a customer that he wants to use EJBContext from an EJB 3 Interceptor.

Yes, it’s very simple. Just inject the EJBContext into the interceptor using @Resource injection.

See the example code that uses methods of SessionContext as follows:


package actionbazaar.buslogic;

import javax.annotation.Resource;
(more...)

11g SQLNet Client trace files

I spent half an hour wondering why I was not able to generate a SQL* Net client trace file from a 11g client to a 10.2 database.
Here are the contents of my client sqlnet.ora

bart:ELEV:/opt/oracle/product/11.1.0/db_1/network/admin> cat sqlnet.ora
#SQLNET.OUTBOUND_CONNECT_TIMEOUT = 3
TRACE_LEVEL_CLIENT= 16
TRACE_DIRECTORY_CLIENT = /tmp
TRACE_FILE_CLIENT= cli.trc
TRACE_TIMESTAMP_CLIENT = ON
TRACE_UNIQUE_CLIENT = ON

It took me a while to realize that thanks to ADR the client trace (more...)

My Sessions at Oracle Open World 2008

I’ve two sessions at Oracle Open World 2008, San Francisco. Here are my session details:

S298520 : Best Practices for Managing Your Oracle WebLogic Environment with Oracle Enterprise Manager
Track: Application Server and Transaction Processing
Room: Rm 2006
Date: 2008-09-22
Start Time: 11:30

S298522 : Top Five Things DBAs Must (more...)

One off patches, conflicts and merges

So you are applying one of the zillion patches on top of 10.2.0.3 and you hit the following error

************************************************************************

ApplySession applying interim patch '6338357' to OH
'/opt/oracle/product/10.2.0/db_1'
Interim patch 6338357 has File Conflict with patch(es) [ 5399670 ]
in OH /opt/oracle/product/10.2.0/db_1

************************************************************************

Wouldn't it be nice if you could check for conflicts before you apply a patch
Here is one way to do the same

1. You can check which source code file is being modified by doing the following

cd 6338357/etc/config
cat actions | grep .o
oneoff_actions
oracle.rdbms version="10.2. (more...)

1Z0-043 … Passed

I passed the 1Z0-043 exam a week ago. While waiting on Oracle to process my hands-on course requirement I am thinking what should be the next step. Certainly OCM is on my list, but in the mean time I'd like to go for SQL Expert Certificate.

I have been studying (more...)

10g Migration Ramification Part 1: ORA-1426, Numeric Overflow

Oracle 11g was released over a year ago, but lets face it, not everyone is running Oracle 10g, let alone 11g. July 31st marks the end of the 'free' Premier Support Oracle offered up to all Oracle 9.2.0.8 customers last summer. With that spirit in mind, I thought I would share some of my Oracle 10g Migration Ramifications. ('Upgrade' is a boring word and doesn't have a lot of words that rhyme with it. It is also a great homage to Adam Sandler's 'Cajun Man').

In part 1, I will discuss an ORA-1426 you may (more...)

Do we need stats on all columns?

Recently I had visited one of the customers for performance review. As per the current stats gathering policy, they use following method to gather stats on the tables.

dbms_stats.gather_table_stats (user, tabname, cascade=>true, method_opt=>'FOR ALL INDEXED COLUMNS 75');

As can be seen from the command, the input from method_opt is to gather stats on all the indexed columns with bucket size as 75. This means the stats on the other non-index columns will not be collected. This is a wrong practice and stats on the columns of the table are crucial, particularly if these are use in queries.

When I (more...)

Latest additions to Oracle enhanced adapter

Short information about latest enhancements in ActiveRecord Oracle enhanced adapter:

  • Oracle enhanced adapter is now compatible with composite_primary_keys gem which is quite useful if you are working with legacy databases.
  • Adapter now is also working correctly with Rails 2.1 partial_updates enabled. Previously I mentioned that you needed to disable partial_updates when using CLOB/BLOB columns. Now the issue is found and fixed and partial_updates are working with CLOB/BLOB columns.
  • Support for other date and time formats when assigning string to :date or :datetime column. For example, if you would like to assign strings with format dd. (more...)

Microsoft acquires DATAllegro

Lots of interesting posts on this news, from:

ASP State and SQL Server: The Mirror Has Three Faces

In the spirit of Jonathan Lewis' recent post, Sometimes, Everything I Write Sucks!, comes today's installment of the Prestidigitation of Oracle. Today's prestidigitation is that I'm not going to write about Oracle (or why I need to blog more), but I'm going to focus on a topic I've been dealing with lately which is implementing the ASPState database with SQL Mirroring.

This is part one in the series and will go over the set up of a custom ASP session state database to be used with ASP.NET applications which is connected to a SQL Server database that is (more...)

Screencast of Oracle E-Business Suite setup management tool

URRA.pngWarning: this post will be unrelatead to Ruby :)

For many years our company is using tool for Oracle E-Business Suite setup management that we initially built for our own purposes but now also we are offering to other Oracle partners and customers.

As I was bored to do demonstrations of it over and over again I finally decided to create a screencast of this tool (it’s 22 MB large). And I think that my first experiment in screencasting is not too bad :)

Previously screencasting was quite hard stuff as there was not so many good tools for that. (more...)

Reset the APEX internal password

I noticed in one of the comments of Dimitri Gielis articles that Jornica pointed out a script called apxXEpwd.sql

I ran this as the SYS user and sure enough, it allows you to reset the ADMIN password for the internal workspace.

This script can be found in the root (more...)

IOUG RAC Attack! … I surrender!!!

The Oracle RAC SIG and IOUG are putting on an event in a few weeks in Chicago, August 4th and 5th. It is called the IOUG RAC Attack! (awesome name, I wonder who came up with it). Since my current job entails supporting an Oracle RAC environment (2 5-node clusters) and we have some newer members who are a bit green in the RAC arena, I thought it would make sense to check into the event and agenda to see if it is worthwhile to send some people to this.

From looking at the agenda and the quality speakers they (more...)

Oracle Locator Express

If you do much work with the Oracle database on Windows, and you have 1+N Oracle homes installed, you've probably lamented the fact that the Oracle Home Switcher is no longer included with Oracle.

I can't recall exactly what the tool was called or which version Oracle was at when it ceased to be a part of the installation. I do know that it doesn't work with 10g+.

A little tool called Oracle Locater Express fills this niche nicely, and it does work with 10g. Sorry, have not yet tried it with 11g.

"Oracle Locator Express"


I've used it for (more...)

9 Reasons I Need to Blog More

Almost on a daily basis, I sit and stair at the Prestidigitation of Oracle and wonder what I should blog about today. I know I have a lot of ideas floating around in my head and all it takes is that mental kick in the arse to get it out. And yet, here I am sitting on July 22nd, 2008 with only 3 blog posts since starting in February (That is a) not a good average and b) 2 posts, as the first one was an inaugural post). This is my attempt to kick my brain in the arse and (more...)

CURSOR_SPACE_FOR_TIME to be deprecated in future releases

According to a recently published metalink note (565424.1) the parameter "CURSOR_SPACE_FOR_TIME" is going to be deprecated starting release 11.1.0.7 onwards. In fact Oracle's next patch set release for 10g - 10.2.0.5 will also de-support this parameter. We will have to remove this parameter whenever these 2 patch sets - 10.2.0.5 for 10g and 11.1.0.7 for 11g are released and we decided to migrate. CURSOR_SPACE_FOR_TIME was introduced to reduce latch contention; This parameter may no longer be required with introduction of cursor mutexes hence Oracle has decided to deprecate this in future releases.

Oracle 11g New Features – PL/SQL enhancements Part-II

This is the concluding part of the 2 part series on PL/SQL enhancements in 11g. Here are few more useful enhancements in PL/SQL :-

1. Usage of Named and mixed notation with PL/SQL subprograms in a SQL statement

Prior to 11g, to use a sub-program (e.g. function) in a SELECT statement you have to provide the real parameters in positional notation. Let’s look at an example using both 10g and 11g to understand it better.

Here is a small piece of code which calculates the years of employment of an employee :-
CREATE OR REPLACE FUNCTION cal_employment_duration (
empid (more...)

TechNight All India Oracle User Group (AIOUG)


AIOUG started his first ever Technical session in India at Hyderabad… On … Friday, July 18th 2008, between 5:00 PM to 8:00 PM. Session started with quick introduction about AIOUG, there mission goal by Murali Vallath. Latter Phani Arega took technical session on "Efficient SQL Programming - Some Tricks and Tips" and Vivek Sharma on " Real Time Performance Tuning".

Both the technical session was quite good and they shared lots of real life examples in there presentation. Here is some photos from AIOUG TechNight

Murali Vallath

Phani Arega