Wednesday, February 29, 2012

Tracking Down Oracle Deadlocks

The Dreaded ORA-00060 error. DEADLOCK DETECTED Fortunately this is not an Oracle problem, but is an application problem. Now the fun part of tracking it down.

1. Locate the Trace file so you can gather some details.

2. Find the resource names in the Trace file…

Resource Name        

Hopefully this will give you some relavant object information. This will allow you to query the all_objects table looking for the objects.

3. Query the all_objects table

select object_name, object_type from all_objects
where object_id in ( to_number( '14ef4', 'xxxxx' ), to_number( '14ef4', 'xxxxx' ) );

From here you get to do some more digging. The trace file will contain a lot of information, mostly cryptic but hopefully it can help point you in the right direction.

Monday, February 27, 2012


So that took longer than it should. There is a command called whatis that gives you a single line description of a command:

whatis grep

grep (1) - print lines matching a pattern
grep (1p) - search a file for a pattern
grep (rpm) - The GNU versions of grep pattern matching utilities

A nice little tool to use. I just installed CentOS 5 in a VM and noticed that all my whatis commands were returning nothing appopriate…I quickly found the makewhatis command on some articles but could not locate it for CentOS 5 at first. Finally found the location:


This command builds the database and now my whatis commands are working.

Un-expire Oracle User account

There is no real un-expire option for an Oracle user account. Say you have an account that goes into expire status…you have to alter the user using the identified by clause to set the password. Problem is if you do not know the original password you have to change it…this could cause big problems.

However here is a cool workaround to remedy the problem:

First find the user int he sys.user$ table using this query:

select password, spare4 from sys.user$ where name='USERNAME';

You should see some results similar to below:


Now take the results and run the ALTER statement on the user: 

alter user USERNAME identified by values 

You concatenate the password field and the spare4 field with a semicolon in between.

Thursday, February 23, 2012