Tip#22 Fix for ORA-01882

ORA-01882: timezone region %s not found

Recently we got this error while running via TOAD

select * from dba_scheduler_jobs;

The error message itself turns out not very informative.

01882, 00000, "timezone region %s not found"
// *Cause: The specified region name was not found.
// *Action: Please contact Oracle Customer Support.

In short, the error is because there are 7 timezone region IDs changed from version 3 and above. If you have old Timezone data from Version 2 that using one of these IDs the error raises.

In our case, server had time zone files were already upgraded so running same query on the DB server was working fine. So to fix it we had fix it on the client side,

1) Download patch 5731535 for a 10.2.0.X client ( 10.2.0.1 to 10.2.0.3)
2) Copy the 2 .dat files located at Client_patch\5731535\files\oracore\zoneinfo and the readme.txt file into the %ORACLE_HOME%\oracore\zoneinfo directory on your oracle client.
3) restart the TOAD

Unlocking the locked table

Some times we get an error while running a DDL statement on a table. something like belowSQL> drop table aa;
drop table aa
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

This happens because some other session is using this table or having a lock on this table.
Following is the simple procedure to kill the session holding the lock on this table and drop the table. Note that this should be done only if you are sure that this table is no more required and all sessions holding lock on this table can be deleted

1. Get the object ID of the table to be droppedSQL> select object_id from dba_objects where object_name = 'AA';
OBJECT_ID
----------
3735492

2. Get the session ID which is holding lock on this object from v$locked_object view
SQL> select OBJECT_ID, SESSION_ID, ORACLE_USERNAME, PROCESS from v$locked_object where OBJECT_ID = 3735492;
OBJECT_ID SESSION_ID ORACLE_USERNAME PROCESS
---------- ---------- ------------------------------ ------------------------
3735492 1124 MSC 4092@AKPRADH-LAP

3. Get the serial# of the SID using v$session

SQL> select sid, serial# from v$session where sid = 1124;
SID SERIAL#
---------- ----------
1124 51189

4. Kill the session by connecting as sysdba and try dropping the table
SQL> alter system kill session '1124, 51189';
System altered.

SQL> drop table aa;
Table dropped.