Oracle Error Information

ORA-02063 remote db error and how to fix it

2011-02-06T19:22:00.000-05:00

ORA-02063: preceding stringstring from stringstring
Cause: an Oracle error was received from a remote database link.
Action: refer to the preceding error message(s)

ORA-02063 usually occurs when you run a transaction that tries to query or update a remote database table.
This ORA-02063 error is always proceeded by other ORA error messages , which are from the remote database and are root cause for the transaction failure.

The format of the message is as follows:
ORA-02063: preceding line from

Example:

ORA-00942: table or view does not exist
ORA-02063: preceding line from TDB21.WORLD@BILLOP

Solution:

Fixing ORA-02063 errors involves analyzing the remote database errors associated with the ORA-02063 and fixing those errors.

The following are couple of examples with fixes:

1. In teh following example error occured in Oralce 11.1.0.7
SQL> select *
2 from products_fact@exceldb1;
from products_fact@exceldb1
*
ERROR at line 2:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Microsoft][ODBC Excel Driver]Optional feature not implemented
ORA-02063: preceding 2 lines from EXCELDB1

SQL>

The above error was fixed by adding parameter HS_FDS_SUPPORT_STATISTICS=FALSE in the initilization init.ora file in 11.1.0.7.

2. In the following example, local db is Oracle 9i and remote db is Oracle 10g.

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-02063: preceding line from ABC

In this case TEMP space resource issue was in remote db and by adding more TEMP or by tuning the sql statement this error is resolved.

ORA-00257: archiver error. Connect internal only, until freed.

2008-10-22T22:31:00.004-04:00

ORA-00257: archiver error. Connect internal only, until freed.

Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Action: Check the archiver trace file for a detailed description of the problem. Also, verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.

ORA-000257 error is somewhat common error and is caused whenever archiver is unable to archive the online redo log files at the desired location. Usually this error cocurs when archive destination is full and the archiver was not able to write any more due to lack of space.

So for understanding this ORA-000257 error, it is better knowing the concepts of Oracle Archiving. SO here are the archiving details

What is archiving and Oracle archived redo logs ?

The folowing explanation excerpt from Oracle documentation
"Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log, or more simply the archive log. The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode. You can choose automatic or manual archiving.

An archived redo log file is a copy of one of the filled members of a redo log group. It includes the redo entries and the unique log sequence number of the identical member of the redo log group. For example, if you are multiplexing your redo log, and if group 1 contains identical member files a_log1 and b_log1, then the archiver process (ARCn) will archive one of these member files. Should a_log1 become corrupted, then ARCn can still archive the identical b_log1. The archived redo log contains a copy of every group created since you enabled archiving.

When the database is running in ARCHIVELOG mode, the log writer process (LGWR) cannot reuse and hence overwrite a redo log group until it has been archived. The background process ARCn automates archiving operations when automatic archiving is enabled. The database starts multiple archiver processes as needed to ensure that the archiving of filled redo logs does not fall behind."

Why archived redo logs are used?

-To Recover a database

-To Update a standby database

-To Get information about the history of a database using the LogMiner utility

Oracle database can be run in two modes (1) ARCHIVELOG mode (2) NOARCHIVELOG mode

NOARCHIVELOG Mode:

When you run your database in NOARCHIVELOG mode, you disable the archiving of the redo log.

NOARCHIVELOG mode protects a database from instance failure but not from media failure. Only the most recent changes made to the database, which are stored in the online redo log groups, are available for instance recovery. If a media failure occurs while the database is in NOARCHIVELOG mode, you can only restore the database to the point of the most recent full database backup. You cannot recover transactions subsequent to that backup.

In NOARCHIVELOG mode you cannot perform online tablespace backups, nor can you use online tablespace backups taken earlier while the database was in ARCHIVELOG mode. To restore a database operating in NOARCHIVELOG mode, you can use only whole database backups taken while the database is closed. Therefore, if you decide to operate a database in NOARCHIVELOG mode, take whole database backups at regular, frequent intervals.

ARCHIVELOG Mode:

When you run a database in ARCHIVELOG mode, you enable the archiving of the redo log. The database control file indicates that a group of filled redo log files cannot be reused by LGWR until the group is archived. A filled group becomes available for archiving immediately after a redo log switch occurs.

The archiving of filled groups has these advantages:

A database backup, together with online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure.

If you keep an archived log, you can use a backup taken while the database is open and in normal system use.

You can keep a standby database current with its original database by continuously applying the original archived redo logs to the standby.

So, If you cannot afford to lose any data in your database in the event of a disk failure, use ARCHIVELOG mode.
In reality most of the Oracle databases run in ARCHIVELOG mode.

The Oracle error ORA-000257, which we are discussing occurs in the databases that run in ARCHIVELOG mode.

Where does the Archive Logs stored/resides?

Oracle database lets you specify whether to archive, redo logs to a single destination or multiplex them. If you want to archive only to a single destination, you specify that destination in the LOG_ARCHIVE_DEST initialization parameter. If you want to multiplex the archived logs, you can choose whether to archive to up to ten locations (using the LOG_ARCHIVE_DEST_n parameters) or to archive only to a primary and secondary destination (using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST).

Two methods for specifying archive destination:

Method 1: Using the LOG_ARCHIVE_DEST_n Parameter

Use the LOG_ARCHIVE_DEST_n parameter (where n is an integer from 1 to 10) to specify from one to ten different destinations for archival. Each numerically suffixed parameter uniquely identifies an individual destination.

Steps for setting archivelog destination:

step1 )Use SQL*Plus to shut down the database.

SHUTDOWN

step2 )Set the LOG_ARCHIVE_DEST_n initialization parameter to specify from one to ten archiving locations. The LOCATION keyword specifies an operating system specific path name. For example, enter:

LOG_ARCHIVE_DEST_1 = 'LOCATION = /disk1/archive'
LOG_ARCHIVE_DEST_2 = 'LOCATION = /disk2/archive'

Step3) Optionally, set the LOG_ARCHIVE_FORMAT initialization parameter, using %t to include the thread number as part of the file name, %s to include the log sequence number, and %r to include the resetlogs ID (a timestamp value represented in ub4). Use capital letters (%T, %S, and %R) to pad the file name to the left with zeroes.
example for UNIX OS:
LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf

Method 2: Using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST

To specify a maximum of two locations, use the LOG_ARCHIVE_DEST parameter to specify a primary archive destination and the LOG_ARCHIVE_DUPLEX_DEST to specify an optional secondary archive destination. All locations must be local. Whenever the database archives a redo log, it archives it to every destination specified by either set of parameters.

Perform the following steps the use method 2:

step1) Use SQL*Plus to shut down the database.

SHUTDOWN

Step2) Specify destinations for the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameter (you can also specify LOG_ARCHIVE_DUPLEX_DEST dynamically using the ALTER SYSTEM statement). For example, enter:

LOG_ARCHIVE_DEST = '/disk1/archive'
LOG_ARCHIVE_DUPLEX_DEST = '/disk2/archive'

Step3) Set the LOG_ARCHIVE_FORMAT initialization parameter.
example for UNIX OS:
LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf

The Oracle error ORA-000257, which we are discussing occurs in the databases that run in ARCHIVELOG mode when the above mentioned archive destinations are full and when the archiver was unable to write to these archive destinations

In order to resolve this error you need to find the archive log info from the database. For that Oracle provides number of internal views and commands. The following are those details:

Oracle Archive related Views:

V$DATABASE Shows if the database is in ARCHIVELOG or NOARCHIVELOG mode and if MANUAL (archiving mode) has been specified.
V$ARCHIVED_LOG Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.
V$ARCHIVE_DEST Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.
V$ARCHIVE_PROCESSES Displays information about the state of the various archive processes for an instance.
V$BACKUP_REDOLOG Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.
V$LOG Displays all redo log groups for the database and indicates which need to be archived.
V$LOG_HISTORY Contains log history information such as which logs have been archived and the SCN range for each archived log.

To find which redo log group requires archiving run the folowing query:

SELECT GROUP#, ARCHIVED
FROM SYS.V$LOG;

GROUP# ARC
-------- ---
1 YES
2 NO

To find the current archiving mode run the following query:

SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG

The ARCHIVE LOG LIST Command
The SQL*Plus command ARCHIVE LOG LIST displays archiving information for the connected instance. For example:

SQL> ARCHIVE LOG LIST

Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\oracle\oradata\TESTDB1\archive
Oldest online log sequence 11270
Next log sequence to archive 11274
Current log sequence 11274

This display tells you all the necessary information regarding the archived redo log settings for the current instance:

The database is currently operating in ARCHIVELOG mode.

Automatic archiving is enabled.

The archived redo log destination is D:\oracle\oradata\TESTDB1\archive.

The oldest filled redo log group has a sequence number of 11270.

The next filled redo log group to archive has a sequence number of 11274.

The current redo log file has a sequence number of 11274.

RESOLUTION/Fix to ORA-00257 error:

Increase the free space in the archive log destination directories. The location where archiver archives the log is determined by parameter file pfile or spfile.
You can find the archive log destination by running the following command from SQL*Plus
SQL> show parameter log_archive_dest

You can also find archive destination information using above mentioned oracle internal views and commands mainly by using " archive log list" command

In case no more space is available at the current archive destination due to OS / Disk limitations , you can specify a new desination location by setting the parameter log_archive_dest (or log_archive_dest_1 in some cases) so that the new archives are produced at new location.
You can do this modifying init.ora file or using alter system if spfile is present

SQL> alter system set log_archive_dest_1=’LOCATION = /disk9/archive'

The other option is to take a backup of the archives from the current desination and delete those archives from that place so that new archives can generated at the current destination.
The backup can be OS level backup and OS level file deletion BUT the recommended method for ASM in place is taking RMAN backup and delete using RMAN.
Eample:
rman target sys/sys

RMAN> backup archive log all device type disk format ‘/oracle/arch_%U’;

RMAN> delete archive until time ‘trunc(sysdate)’;

This will delete all the archive logs until today and space will freed and the archiver will start archiving redo logs

CONCLUSION:

The ORA-00257 Oracle error is related to archiving of the oracele database redo logs and it requires immmediate attention of the DBA. So Oracle users when you encounter this error notify the DBA so that this error will be corrected abd instance crashes will be avoided.
This error is mainly caused because of lack of space at the archive destination. Oracle provides number of views and commands to find the archivelog, archive destination info as mentioned above in the article, by using them we can find identify which archive destination ran out of space and can take appropriate action as described in the resolution to the error.

I hope this article is informative enough to identify root cause for ORA-00257 error, to provide insights on oracle ARCHIVELOG feature and to help in resolving ORA-000257 error. More Details at http://ora-00257.ora-code.info/.

Mendus, Oracle OCP
www.ora-code.info

ORA-28009 connection to sys should be as sysdba or sysoper

2008-10-21T16:58:00.003-04:00

ORA-28009: connection to sys should be as sysdba or sysoper

Cause: Connect sys/password is no longer a valid syntax.

Action: Try connect sys/password as sysdba or connect sys/password as sysoper. ORA-28009 oracle error is raised when the sys user tries to connect without providing "as sysdba" . This s controlled by the O7_DICTIONARY_ACCESSIBILITY parameter. When this parameter value is set to FALSE, sys users experience this ORA-28009 error. Access to dictionary objects is restricted to the users with the system privileges SYSDBA and SYSOPER. Connecting as SYSDBA gives a user unrestricted privileges to perform any operation on a database or the objects within a database. Data dictionary objects is under SYS schema and is protected by O7_DICTIONARY_ACCESSIBILITY to FALSE settings. Example for the ORA-28009 error:
Connecting as sys user without sysdba privilege. SQL> alter user SYS identified by SYS; SQL> connect sys/sys ERROR: ORA-28009: connection to sys should be as sysdba or sysoper Warning: You are no longer connected to ORACLE. Connecting as sys user with sysdba privilege. SQL> connect sys/sys as sysdba Connected. SQL> O7_DICTIONARY_ACCESSIBILITY parameter info: SQL> SHOW PARAMETER O7_DICTIONARY_ACCESSIBILITY NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ O7_DICTIONARY_ACCESSIBILITY boolean FALSE Modifying the O7_DICTIONARY_ACCESSIBILITY parameter value:
SQL> ALTER SYSTEM SET O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile; System altered.

In order make the chnages effective shutdown and startup the database:
SQL> show parameter O7_DICTIONARY_ACCESSIBILITY NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ O7_DICTIONARY_ACCESSIBILITY boolean TRUE Now sys user should be able connect with out "as sysdba" as verified below: SQL> conn sys/sys Connected.

CONCLUSION: Oracle Recommendation is not to set O7_DICTIONARY_ACCESSIBILITY to TRUE.
So in order to avoid this ORA-28009 error, always explicitly specify " as sysdba " when connecting as sys user.

More Details at http://ora-28009.ora-code.info/.

Mendus, Oracle OCP
http://www.ora-code.info/

ORA-00060: deadlock detected while waiting for resource

2008-10-20T23:07:00.003-04:00

ORA-00060: deadlock detected while waiting for resource
Cause: Transactions deadlocked one another while waiting for resources.
Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.

ORA-00060 error indicates that a dead lock occurred due to a resource contention with another session and Oracle rolled back your current statement to resolve the dead lock. The other session can proceed further as usual . Your current sessions rolled backed statements needs to be resubmitted for the execution after the required resources are available.

These dead locks can occur in different scenarios: They can occur while doing parallel DML operations, while updating/deleting data from same tables from different sessions , while performing transactions on bitmap index tables and so on but the mentioned scenarios are the most common ones.

At this point I think we would like to explore more about dead locks, so here are the in depth details on dead locks:

What is dead lock, when it occurs and how to fix it?

Dead lock occurs when two or more users waiting for the resources locked by each other. The users are stuck and they can not proceed as they may end up waiting indefinitely for the resources form each other. So when this condition occurs these users are stuck (deadly embraced) and cannot continue processing.

Oracle automatically detects deadlocks and resolves them by rolling back one of the transactions/statements involved in the deadlock, thus releasing one set of resources/data locked by that transaction. The session that is rolled back will observe Oracle error: ORA-00060: deadlock detected while waiting for resource. Oracle will also produce detailed information in a trace file under database's UDUMP directory.

Most commonly these deadlocks are caused by the applications that involve multi table updates in the same transaction and multiple applications/transactions are acting on the same table at the same time. These multi-table deadlocks can be avoided by locking tables in same order in all applications/transactions, thus preventing a deadlock condition.

In the following example I am demonstrating a dead lock scenario . In the first session ie. session1 lock table: CUSTOMER then CONTACT; and in session2: CONATCT then CUSTOMER.

Here is an example of how to simulate a deadlock error:

Session 1 lock table CUSTOMER:
SQL> update customer set concatenate_bills_boo = 'T' where customer_ref = '10000000000000000486';

1 row updated.

Session 2 lock table CONTACT:

SQL> update contact set language_id = 8 where customer_ref = '10000000000000000486';

1 row updated.

Session 1 now update CONTACT. The session will hang waiting for a lock (not a deadlock yet!):

SQL> update contact set language_id = 8 where customer_ref = '10000000000000000486';

Session 2 now update CUSTOMER, causing the deadlock:

SQL> update customer set concatenate_bills_boo = 'T' where customer_ref = '10000000000000000486';
update customer set concatenate_bills_boo = 'T' where customer_ref = '10000000000000000486'
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

SQL>

Resolution/Fix for deadly Embrace Dead Lock:

The session on which ORA-00060 occurred encountered the dead lock and Oracle automatically rolled back the statement that caused the dead lock. No other session got affected, so other sessions functions as usual.

In the affected session, the rolled back statement needs to be re-executed once the resources are available ie. When no other session is competing for the same resource.

Probably it will be a good idea to analyze oracle trace file and then modify the application to avoid this situation.

To avoid deadly embrace dead locks, review the application transaction logic thoroughly in the design phase it-self and ensure tables are ordered with in the transactions/applications in such a way that resource contention not occurs among transactions/applications.

In order to find the lock information from the database on which objects / tables have locked currently, which are the blocking sessions and users, ,you can find lot of useful information and scripts at ORA-00054 resource busy error article.

ITL shortage deadlocks :

ITL shortage deadlocks are those dead locks ORA-00060 errors that occur during the execution of UPDATE and DELETE statements where two processes wait for 'S' mode locks on each other's 'TX' enqueues. This cannot happen with INSERT statements, as Oracle doesn't wait on ITL (Interested Transaction List) slots for inserts, it will simply try to insert the row into the next available block.

Resolution/Fix for ITL Dead locks:

To resolve ITL dead locks , recreate the segment with higher INITTRANS and/or PCTFREE values. This will allow more space in the data blocks for Oracle to allocate more transaction entries (24 bytes at a time) when required.

Monitoring ITL waits :

Query the sys.v_$segment_statistics view for identifying and monitoring ITL waits per segments

Other causes for Dead Locks:

Bitmap indexes and dead locks:

High transaction activity on tables with bitmap indexes often causes dead locks. Bitmap indexes are only appropriate in read only/ read mostly environments.

Resolution to Bitmap Index dead locks:

This can be resolved by setting a very high INITTRANS value for the bitmap index but most cases it would be better & best to disable the index before heavy transactional activity, and to rebuild it after heavy transactional activity is done.

Additional dead lock related information :

Bitmap dead lock related info:

You can find more details on bitmap indexes and how they trigger dead locks at asktom by Thomas Kyte.

ITL Waits :

The following is very insightful article on ITL by Arup Nanda with some scripts for identifying the ITL waits.. Originally I thought to provide a link to this article but later I felt this article is very important , informative so I didn’t want the readers to miss/lose this info if that link changed by chance. So I am providing the article inline here with a sole purpose of making the freely available information on the internet in a readily usable form.

Here is the article on ITL:

“Oracle Interested Transaction List (ITL) Waits
by Arup Nanda
Author of Oracle Privacy Security Auditing

What is ITL? Ever wondered how Oracle locks rows on behalf of transactions? In some RDBMS vendor implementations, a lock manager maintains information on which row is locked by which transaction. This works great in theory, but soon the lock manager becomes a single point of contention, as each transaction must wait to get a lock from the manager and then wait again to release the lock.

This severely limits the scalability of the applications. In fact, application developers of some RDBMS products despise holding locks for a long time, and often resort to a full table lock when all that's needed is to get a few rows locked. This creates further waits, and consequently, scalability suffers.

So how is that different in Oracle? For starters, there is no lock manager. When a row is locked by a transaction, that information is placed in the block header where the row is located. When another transaction wishes to acquire the lock on the same row, it has to travel to the block containing the row anyway, and upon reaching the block, it can easily tell that the row is locked from the block header. There is no need to queue up for some single resource like a lock manager. This makes applications immensely scalable.

So, what portion of the block header contains information on locking? It is a simple data structure called "Interested Transaction List" (ITL), a linked list data structure that maintains information on transaction address and rowid. ITL contains several slots or place holders for transactions. When a row in the block is locked for the first time, the transaction places a lock in one of the slots with the rowid of the row that is locked. In other words, the transaction makes it known that it is interested in the row (hence the name "Interested Transaction List").

When the same transaction or another one locks another row, the information is stored in another slot, and so on. After a transaction ends via commit or a rollback, the locks are released and so are the slots that were used to mark the blocks, and these newly freed slots are reused for the other transactions. So there is in fact a queue, but it's at a block level, not at the entire database level or even at a segment level.

The next logical question that comes up is, how many slots are typically available? During the table creation, the INITRANS parameter defines how many slots are initially created in the ITL. When the transactions exhaust all the available slots and a new transaction comes in to lock a row, the ITL grows to create another slot. The ITL can grow up to the number defined by the MAXTRANS parameter of the table, provided there is space in the block.

Nevertheless, if there is no more room in the block, even if the MAXTRANS is high enough, the ITL cannot grow.

So, what happens when a transaction does not find a free slot to place its lock information? This can occur because either (i) the block is so packed that the ITL cannot grow to create a free slot, or (ii) the MAXTRANS has already been reached. In this case, the transaction that needs to lock a row has to wait until a slot becomes available. This wait is termed as ITL waits and can be seen from the view v$session_wait, in which the session is waiting on an event named "enqueue." Since the INITRANS is one, there is only one slot for the ITL. The rest of the block is empty.

Then another transaction, Txn2, updates the row Row2 and wants to lock the row. However, there are no more slots in the ITL available to service the transaction. The MAXTRANS entry is 11, meaning the ITL can grow up to 11 slots and the block has empty space. Therefore, ITL can grow by another slot and Slot number two is created and allocated to Txn2 (refer to figure 4).

Now the empty space in the block is severely limited, and it will not be able to fit another ITL slot. If at this time another transaction comes in to update the row three, it must have a free slot in the ITL. The MAXTRANS is 11 and currently only two slots have been created, so another one is possible; but since there is no room in the block to grow, the slot can't be created. Therefore, the Txn3 has to wait until either of the other transactions rolls back or commits and the slot held by it becomes free. At this time the session will experience an ITL waits event as seen from the view V$SESSION_WAIT.

To better illustrate the concept, let's illustrate such waits using a case. Create the following table and then populate it with several rows. Note MAXTRANS value.
CREATE TABLE TAB1
( COL1 NUMBER,
COL2 VARCHAR2(200))
INITRANS 1 MAXTRANS 1
/

DECLARE
I NUMBER;
BEGIN
FOR I IN 1..10000 LOOP
INSERT INTO TAB1 VALUES
(I,'SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS');
END LOOP;
COMMIT;
END;
/

Now update a row of the table from one session, but do not commit it.
UPDATE TAB1 SET COL2 = 'UPDATED' WHERE COL1 = 1;

From another session, update row number two and do not update it.
UPDATE TAB1 SET COL2 = 'UPDATED' WHERE COL1 = 2;

This session will wait. Why? It's updating a row for COL1 = 2, not the same row updated in the other session for COL1 = 1. So why is the session waiting? It's because the first transaction occupied the only available ITL slot. The second transaction needed another slot to place its lock information, but since the MAXTRANS I defined is one, the ITL could not grow to create another slot. Thus, the latter transaction has to wait until the former session releases the lock and makes the slot available. Now increase the MAXTRANS of the table by issuing

ALTER TABLE TAB1 MAXTRANS 11;

and redo the above test. The second session will not wait this time because the ITL had enough free slots for both transactions. How to Reduce ITL WaitsThe primary cause of ITL waits is that free slots in the ITL are not available. This can be due to low setting of the MAXTRANS, which places a hard limit on the number of transactions that can have locks on a block or, the block is so packed that there is no room for the ITL to grow OR or both.

Therefore, setting a high value of INITRANS will make sure that there are enough free slots in the ITL, and there will be minimal or no dynamic extension of the ITL. However, doing so also means that there is less space in the block for actual data, increasing wasted space.

The other option is to making sure the data is less packed so that ITL can grow enough to accommodate the surges in ITL. This can be done by increasing PCTFREE, increasing FREELISTS and FREELIST GROUPS parameters for a table.

This will make a block hold less data and more room for the ITL to grow. As a direct result of the reduction in packing, the table will experience fewer buffer busy wait events, and performance will be increased.
How to Diagnose the ITL WaitHow do you know that a segment is experiencing ITL waits? The best answer will be found in the Segment Level Statistics provided in Oracle9i Release 2. To check for ITL waits, set up the STATISTICS_LEVEL to TYPICAL in init.ora or via ALTER SYSTEM, then examine the segment statistics for the waits.

SELECT
OWNER,
OBJECT_NAME
FROM
V$SEGMENT_STATISTICS
WHERE
STATISTIC_NAME = 'ITL waits'
AND
VALUE > 0;

This unearths the objects that were subjected to ITL waits since the start up of the instance. Note that this view resets when the instance bounces. (For a more detailed explanation of this view and how to set it up, please refer to the article by this author here in DBAzine.)
In versions earlier than 9i, checking for ITL waits is tricky.

When you suspect that a database is suffering from these waits, query the view v$session_wait. If the event on which the system is waiting is "enqueue," then the session might be experiencing ITL waits. However, enqueue is a very broad event that encompasses any type of locks, so it does not accurately specify the ITL waits. When the wait event is experienced, issue the following complex query:

Select s.sid SID,
s.serial# Serial#,
l.type type,
' ' object_name,
lmode held,
request request
from v$lock l, v$session s, v$process p
where s.sid = l.sid and
s.username <> ' ' and
s.paddr = p.addr and
l.type <> 'TM' and
(l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
union
select s.sid SID,
s.serial# Serial#,
l.type type,
object_name object_name,
lmode held,
request request
from v$lock l, v$session s, v$process p, sys.dba_objects o
where s.sid = l.sid and
o.object_id = l.id1 and
l.type = 'TM' and
s.username <> ' ' and
s.paddr = p.addr
union
select s.sid SID,
s.serial# Serial#,
l.type type,
'(Rollback='||rtrim(r.name)||')' object_name,
lmode held,
request request
from v$lock l, v$session s, v$process p, v$rollname r
where s.sid = l.sid and
l.type = 'TX' and
l.lmode = 6 and
trunc(l.id1/65536) = r.usn and
s.username <> ' ' and
s.paddr = p.addr
order by 5, 6
/

The output of the query will look something like this:

SID SERIAL# TY OBJECT_NAM HELD REQUEST
----- ------- -- ---------- ---------- --------
36 8428 TX 0 4
36 8428 TM TAB1 3 0
52 29592 TM TAB1 3 0
52 29592 TX (Rollback=RBS1_6) 6 0

Note how the sessions 36 and 52 both have a TM (DML) lock on the table TAB1 of type 3 (Row Exclusive), but session 52 also holds a TX (Transaction) lock on the rollback segment of mode 6 (Exclusive) and Session 36 is waiting for a mode 4 (Share) lock. If this combination of locking occurs, you can be sure that session 36 is waiting for ITL on the table TAB1. Beware of a similar but different diagnosis when two sessions try to insert the same key value (a real locking – primary key violation). In that case, you would also see an additional TX lock on a rollback segment from the session that is waiting; for ITL waits, this TX lock on the rollback segment would not be seen.

Needless to say, this is a rather convoluted and inaccurate way to diagnose the ITL waits in pre-Oracle9i Release 2 versions.

What INITRANS Value is Optimal? Conversely, how do you know if the INITRANS setting is too high and the space is just being wasted? Ascertaining this is possible by using a few random block dumps from the segment in question. First, find out the header file# and header block# of the segment by issuing the following query:

SELECT HEADER_FILE, HEADER_BLOCK FROM DBA_SEGMENTSWHERE OWNER = '...'AND SEGMENT_NAME = '...';

Use the output of the query to do a block dump of the header block.
ALTER SYSTEM DUMP DATAFILE BLOCK MIN BLOCK MAX ;

This will produce a trace file in the USER_DUMP_DESTINATION directory. Open the trace file and find out the section on extent control via the following:
Extent Control Header

-----------------------------------------------------------------

Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 10

last map 0x00000000 #maps: 0 offset: 2080

Highwater:: 0x02011f87 ext#: 0 blk#: 0 ext size: 10

#blocks in seg. hdr's freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 0

Unlocked

Map Header:: next 0x00000000 #extents: 1 obj#: 53689 flag: 0x40000000

Extent Map

-----------------------------------------------------------------

0x02011f87 length: 10

Find out the real number of blocks for the segment from dba_segments via the following:
SELECT BLOCKS FROM DBA_SEGMENTS

WHERE OWNER = '...' AND SEGMENT_NAME = '...';

Say this returns 12, and the #blocks shows 10; this means the first two blocks are header blocks; the data starts at the third block. Take a dump of the third block, which is obtained by adding two to the header block# obtained above.
ALTER SYSTEM DUMP DATAFILE BLOCK MIN BLOCK MAX ;

This will produce another trace file in the USER_DUMP_DEST directory. If you issued it during the same session as above, then the trace will be written in the trace file opened earlier. Open the file and locate the following section.
buffer tsn: 8 rdba: 0x02011f88 (8/73608)

scn: 0x0000.389b8d81 seq: 0x01 flg: 0x04 tail: 0x8d810601

frmt: 0x02 chkval: 0x2ef5 type: 0x06=trans data

Block header dump: 0x02011f88

Object id on Block? Y

seg/obj: 0xd1ad csc: 0x00.389b8d81 itc: 4 flg: - typ: 1 - DATA

fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0003.003.000024cc 0x00804067.050a.13 C-U- 0 scn 0x0000.389b304e

0x02 0x0007.010.00002763 0x00801f49.0453.01 C--- 0 scn 0x0000.389b2628

0x03 0x0002.00a.000025d5 0x00804d42.04b2.25 C--- 0 scn 0x0000.389b2811

0x04 0x0006.006.00002515 0x00800962.03c8.18 CU 0 scn 0x0000.389b3044

This shows some very important information on the block, especially in the ITL section shown above. This table has an INITRANS entry of four, so there are four lines, one each per the ITL. The Flag column above the flag -U- indicates that the particular ITL was used. In this case, only two of the ITLs were used, and the other two were never used. However, this is the case for this block only. By selecting block dumps from other randomly selected blocks, you could have an idea how many ITLs are actually used. Then you may decide to reduce the INITRANS.

Automatic Block Management in Oracle9iIn Oracle9i, the process of space mangement inside a block is somewhat changed due to the introduction of the Automatic Block Management (ABM) feature, also known as Automatic Segment Space Management (ASSM). The option is specified at the tablespace level in the storage parameter as SEGMENT SPACE MANAGEMENT AUTO. For instance, the tablespace TS1 can be created as

CREATE TABESPACE TS1
DATAFILE '...'
EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;The last line of this code does the magic. In the ABM mode, Oracle maintains a bitmap for each segment with the information on the block. A bitmap is a data structure with a bit representing each block. When a block becomes available for INSERT, the information is made available simply by setting the corresponding bit in the bitmap rather than using freelists.

So, what does this have to do with ITL waits? The very cause of ITL waits is not freespace management, but the unavailability of a slot in ITL waits. So you still have to look for ITL waits and correct them using INITRANS and MAXTRANS. In fact, the problem may become exacerbated because the block becomes quite packed following an efficient space management system, and that may lead to lack of space for ITL growth. You can prevent this by keeping a large INITRANS for the segment.

Proper setting of INITRANS and MAXTRANS and packing of the blocks is vital to avoid ITL waits in Oracle. It's interesting to note that locking doesn't cause waits, but rather, the mechanism for locking as well as and poor planning. However, the good news is that this situation can be easily fixed by reorganizing the table and adding more slots to the Interested Transaction List.”

CONCLUSION:

Dead locks do occur in most of the applications and dead locks can be avoided by properly designing the transactions and applications by keeping other transactions and applications in mind. ITL waits and dead locks related to ITL waits can be avoided by setting of INITRANS and MAXTRANS properly. Dead locks during the transactions on bitmap indexed tables can be avoided by performing heavy transactions with no bitmap indexes and after completing the transactions rebuild the bitmap indexes.

More Details at http://ora-00060.ora-code.info/.

I hope you enjoyed the dead lock information and content of this article.

-Mendus, Oracle OCP
http://www.ora-code.info/

ORA-00055 maximum number of DML locks exceeded

2008-10-16T10:43:00.003-04:00

ORA-00055 maximum number of DML locks exceeded Cause: Ran out of DML lock state objects. Action: Increase the value of the DML_LOCKS initialization parameter and restart Oracle.
More Details at : http://ora-00055.ora-code.info/

ORA-00054: resource busy and acquire with NOWAIT specified

2008-10-15T00:17:00.008-04:00

ORA-00054: resource busy and acquire with NOWAIT specified Cause: Resource interested is busy. Action: Retry if necessary.

ORA-00054 error is the most commonly faced error by the oracle users. This error is related to table locking or object locking. ORA-00054 occur whenever concurrent DDL operations or DML Update/Delete/Insert/Select for Update operations are performed on the same table /object without proper commits.

Usually when ORA 00054 error occurs from a session, at that instant some other session might have acquired "exclusive" lock with uncommited changes on the table or object common to both the sessions.

ORA-00054 Resolution/Fix :

Identify the locking session - Find which session or user is blocking your operation.
If the lock is free ie. no locking session identified in the first step then you can simply resubmit your operation/statement.
If there is a lock then analyze the locking session and user. Based on the priority of your operation vs. locking session/user priority decide whether to wait until lock has been removed or terminate/kill the locking session and proceed with your operation. Usually we end up in waiting for the other session to complete.

So now the question is how to identify the locking sessions?

There are number of inernal tables and views Oracle provides for identifying locks,lock status and their session information.

DBA_BLOCKERS – Shows non-waiting sessions holding locks being waited-on
DBA_DDL_LOCKS – Shows all DDL locks held or being requested
DBA_DML_LOCKS - Shows all DML locks held or being requested
DBA_LOCK_INTERNAL – Displays 1 row for every lock or latch held or being requested with the username of who is holding the lock
DBA_LOCKS - Shows all locks or latches held or being requested
DBA_WAITERS - Shows all sessions waiting on, but not holding waited for locks

These tables or views may not contain all the information about locked session, usename ,process name etc. So you may have to join with v$session , v$locked_object and v$process views to obtain detailed information about the locks.

ORA-00054 and Locking Session Identification Demo:

For this demo you may need use two or three sessions. In the first session perform a DML operation (INSERT) and do not commit
Then in the second session perform another DML operation and try to acquire the lock (ie. select for update ) , we will encounter ORA-00054 error.
To find the locking session execute the provided @lockinfo.sql script
To find the locking sesssion login info execute @Locklogininfo.sql script.

Example:

1st Session:

SQL> create table mytab (n number);

Table created.

SQL> grant all on mytab to migr1dba;

Grant succeeded.

SQL> insert into mytab values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> update mytab set n=2 where n = 1;

1 row updated.

SQL>
........
No Commit yet
........

2nd Session :

SQL> declare
2 n number;
3 begin
4 select n into n from migr1.mytab for update nowait;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at line 4

SQL>

3rd Session:

SQL> @lockinfo.sql

Wed Oct 15 page 1
Lock Information

Machine name Unix RBS
Locked Object Oracle Username O/S Username of Locker Process SID/Ser# Name Logon Date/Time
-------------------- ---------------- ------------- -------------------- --------- ------------ ---- -------------------
MIGR1.MYTAB MIGR1 (INACTIVE) mig05 INFTEST 9281 151,222 _SYS 2008/10/15 05:39:39
SMU4
$

SQL>

SQL> @Locklogininfo.sql

Oracle Username O/S Username Computer/Terminal SID/Ser# STATUS ID Logon Date/Time Program
-------------------- -------------- --------------------- ------------ -------- ------------ -------------------- --------------------------------------------------
oracle INFTEST 157,1 ACTIVE 4773 2008/10/13 16:47:41 oracle@INFTEST (MMNL)
oracle INFTEST 160,7 ACTIVE 4771 2008/10/13 16:47:41 oracle@INFTEST (MMON)
MIGR1 mig05 INFTEST 151,222 INACTIVE 9281 2008/10/15 05:39:39 sqlplus@INFTEST (TNS V1-V3)
MIGR1DBA mig05 INFTEST 155,34448 INACTIVE 9613 2008/10/15 05:45:58 sqlplus@INFTEST (TNS V1-V3)
MIGR1 mig05 INFTEST 153,65380 ACTIVE 9969 2008/10/15 05:56:49 sqlplus@INFTEST (TNS V1-V3)

14 rows selected.

SQL>

Lock Info Identification Scripts:

The following scripts helps in finding the locking sessions, users , process and their status information.

lockinfo.sql script:

ttitle "Lock Information"
set linesize 150
set echo off
col oruser format a16 heading "Oracle Username"
col osuser format a13 heading "O/S Username"
col obj format a20 heading "Locked Object"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a19
col rs heading "RBS|Name" format a4
col unix heading "Unix|Process" format a9
col computer heading "Machine name|of Locker" format a20
set linesize 120
select owner||'.'||object_name obj
,oracle_username||' ('||s.status||')' oruser
,os_user_name osuser
,machine computer
,l.process unix
,s.sid||','||s.serial# ss
,r.name rs
,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
from v$locked_object l
,dba_objects o
,v$session s
,v$transaction t
,v$rollname r
where l.object_id = o.object_id
and s.sid=l.session_id
and s.taddr=t.addr
and t.xidusn=r.usn
order by osuser, ss, obj
/

Locklogininfo.sql script:

set linesize 200
set echo off
set feedback on
col oruser format a20 heading "Oracle Username"
col osuser format a14 heading "O/S Username"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a20
col computer heading "Computer/Terminal" format a21
col program format a50 heading "Program"
col process format 999999 heading "OS|Process|ID"
select username oruser
,osuser osuser
,decode(substr(machine,1,7),'TENFOLD',
substr(machine,9),machine) computer
,s.sid||','||s.serial# ss
,status
,process
,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
,program
from v$session s
order by time
/
ttitle off

Conclusion:

ORA-00054 error is the most common error caused by the table or object locks due to concurrent DDL and DML(insert/delete/update/select for update) operations from different sessions.
The resolution for this error is finding the locking sesssion & user then based on priority either wait or terminate the locking session and resubmit the job/operation for successful completion.

In this article scripts have been provided for identifying the locking sessions so that readers can get benifited with the readily available scripts for lock identifiction,

More Details at : http://ora-00054.ora-code.info/

I hope you enjoyed this article

Mendus, Oracle OCP
http://www.ora-code.info/

ORA-04091: table is mutating trigger

2008-04-30T23:02:00.005-04:00

ORA-04091: table string.string is mutating, trigger/function may not see it

Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

Action: Rewrite the trigger (or function) so it does not read that table.

ORACLE MUTATING TABLE PROBLEM

The mutating trigger error (ORA-04091) is a pretty common problem in Oracle that happens and frustrates many application developers and DBAs as the application developers bug them to find a solution. It happens when a trigger on a table tries to insert, update, or even select from the same table of whose trigger is being executed. Sometimes the inability to see the table causes standstill in the development.

The following explanation and the associated scripts attempt to present just one work-around for the problem. The work around may not be the best but rather does show that a work-around is possible and provides a solution that will be applicable in most cases. The approach is described by a case study.

SCENARIO

The mutating table problem comes when the trigger tries to select or modify any row of the same table. This situation comes when a table preserves some effective date.

To describe this method, the SALGRADE table will be used.

The table SALGRADE contains information on salary limits for each grade. The salary limits are also based on a time factor, i.e. the employees' salary is determined by checking which grade level was effective when they joined or reviewed, not necessarily the grade effective now.

So the table looks like this:

SQL> desc salgrade;

 Name                            Null?    Type

 ------------------------------- -------- ----

 GRADE                                    NUMBER

 LOSAL                                    NUMBER

 HISAL                                    NUMBER

 START_DATE                               DATE

 END_DATE                                 DATE

The table data looks like:

GRADE   LOSAL   HISAL   START_DATE       END_DATE

------- ------- ------- ----------       ---------

1       1000    2000    1-APR-94         3-AUG-95

1       1200    2200    3-AUG-95                   <---- Null

2       1500    3000    23-JUL-92        12-DEC-93

2       1600    3200    12-DEC-93        11-JAN-95

2       1800    3400    11-JAN-95                  <---- Null

This means the effective salary range of Grade 1 now is (1200-2200) not the employees who had review between 1-APR-94 to 3-AUG-95 will be in the range (1000-2000). This is a purely hypothetical scenario. The objective is to devise a trigger that does the following when a new record is inserted:

(1) Integrity checking for overlapping dates, i.e. the new record can't have a start date that is already covered.

(2) Update the record for the current grade to make the end_date equal to the start date of the new record (the new record's end_date must be null as that is the current record).

In both cases the table SALGRADE has to be selected and updated on the after insert row trigger on the same table. But the table will be mutating when the trigger fires and thus a run-time error will occur.

For the first requirement, consider the following trigger:

create or replace trigger taiudr_salgrade

after insert on salgrade

for each  row

declare

        hold_found      varchar2(1);

begin

        select 'Y' into hold_found

        from salgrade

        where grade = :new.grade

        and end_date is null

        and start_date > :new.start_date;

exception

        when NO_DATA_FOUND then

                raise_application_error(-20000,'Overlapping Dates');

end;

Although the trigger can be created with no errors, when a user tries to

insert into the table the following mutating table error is returned:

SQL> insert into salgrade values (2, 9000, 100000, '25-dec-95', null);

insert into salgrade values (2, 9000, 100000, '25-dec-93', null)

ERROR at line 1:

ORA-04091: table SCOTT.SALGRADE is mutating, trigger/function may not see it

ORA-06512: at "SCOTT.TAIUDR_SALGRADE", line 4

ORA-04088: error during execution of trigger 'SCOTT.TAIUDR_SALGRADE'

SOLUTION

The following approach is another possibility for the task:

1. Create a package "SALGRADE_PKG" that contains PL/SQL tables for holding the

SALGRADE data. Here 3 tables are created - one for holding start_dates, one

for end_dates, and one for holding the change_flag that identifies the

updated row.

2. Create a BEFORE INSERT STATEMENT trigger that populates the PL/SQL table with the start dates, end_dates and changed_grades flag ('N'). Although this still executes a SELECT agaist the SALGRADE table, the mutating table restriction, applies to all triggers that use the FOR EACH ROW clause and this new trigger will be a STATEMENT or table level trigger.

3. Create an AFTER INSERT ROW trigger that compares the newly inserted row against this PL/SQL table not the Database table. This way the integrity check can be done. The same trigger should assign the new end_date value to the PL/SQL table and update the value of the flag to indicate that this

has to be changed.

4. Create a AFTER INSERT STATEMENT trigger to update the SALGRADE table with the values in the PL/SQL table after looking at the change flag.

All these programs can be created by the sources found below.

CODE:

Code to create test table and populate it with data:

drop table salgrade;

CREATE TABLE SALGRADE

      (GRADE NUMBER,

        LOSAL NUMBER,

        HISAL NUMBER,

        START_DATE DATE,

        END_DATE DATE);

INSERT INTO SALGRADE VALUES (1,1000,2000, '1-apr-94', '3-aug-95');

INSERT INTO SALGRADE VALUES (1,1200,2200, '3-aug-95', null);

INSERT INTO SALGRADE VALUES (2,1500,3000, '23-Jul-92', '12-dec-93');

INSERT INTO SALGRADE VALUES (2,1600,3200, '12-dec-93', '11-jan-95');

INSERT INTO SALGRADE VALUES (2,1800,3400, '11-jan-95', null);

Code for package STEP 1 above:

create or replace package salgrade_pkg as

type datetabtype        is table of date index by binary_integer;

type chartabtype        is table of char(1) index by binary_integer;

type rowidtabtype   is table of rowid  index by binary_integer;

start_date_tab          datetabtype;

end_date_tab            datetabtype;

rowid_tab                       rowidtabtype;

changed_grade           chartabtype;

start_date_tab_size     binary_integer;

end;

Code for before insert statement trigger STEP 2 above:

create or replace trigger tbiuds_salgrade

before insert on salgrade

declare

 hold_start_date  date;

 hold_end_date  date;

 hold_rowid   rowid;

 hold_grade   binary_integer;

 cursor start_date_cur is

  select rowid, grade, start_date

  from salgrade

  where end_date is null

  order by grade;

begin

 open start_date_cur;

 loop

  fetch start_date_cur into

   hold_rowid, hold_grade, hold_start_date;

  exit when start_date_cur%notfound;

  salgrade_pkg.start_date_tab(hold_grade) := hold_start_date;

  salgrade_pkg.end_date_tab(hold_grade) := hold_end_date;

  salgrade_pkg.rowid_tab(hold_grade) := hold_rowid;

  salgrade_pkg.changed_grade(hold_grade) := 'N';

 end loop;

 salgrade_pkg.start_date_tab_size := hold_grade;

 close start_date_cur;

end;

Code for after insert row trigger STEP 3 above:

create or replace trigger taiudr_salgrade

after insert on salgrade

for each  row

begin

 if (:new.grade <= salgrade_pkg.start_date_tab_size) then

  if salgrade_pkg.start_date_tab(:new.grade)

    > :new.start_date then

   raise_application_error(-20001,'Overlapping Dates');

  end if;

  salgrade_pkg.end_date_tab(:new.grade) := :new.start_date;

  salgrade_pkg.changed_grade(:new.grade) := 'Y';

 else

  salgrade_pkg.start_date_tab(:new.grade) := :new.start_date;

  salgrade_pkg.end_date_tab(:new.grade) := :new.end_date;

  salgrade_pkg.changed_grade(:new.grade) := 'N';

  salgrade_pkg.start_date_tab_size :=

    salgrade_pkg.start_date_tab_size + 1;

 end if;

end;

Code for after insert statement trigger STEP 4 above:

create or replace trigger taiuds_salgrade

after insert on salgrade

begin

        for i in 1..(salgrade_pkg.start_date_tab_size) loop

                if (salgrade_pkg.changed_grade(i) = 'Y') then

                        update salgrade

                        set end_date = salgrade_pkg.end_date_tab(i)

                        where rowid = salgrade_pkg.rowid_tab(i);

                end if;

        end loop;

end;

Once all the package and triggers are created, the same insert doesn't succeed but gives the user the a more appropriate error:

SQL> insert into salgrade values (2, 9000, 100000, '25-dec-93', null);

insert into salgrade values (2, 9000, 100000, '25-dec-93', null)

ERROR at line 1:

ORA-20001: Overlapping Dates

ORA-06512: at "SCOTT.TAIUDR_SALGRADE", line 5

ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'

The above method is one possible solution to Mutating problem.

The following section provides more in depth details about this mutating problem and some other possible solutions and workarounds:

ORA-4091 Mutating Table Explanation and Workarounds

The purpose of this explanation is to illustrate to those customers who require one of the following functional capabilities whilst being able to maintain referential integrity among objects:

Cascade Update
Cascade Delete
Cascade Insert

For cascade Update and Insert functions, using stored triggers and procedures will result in an ORA-04091 - "Table is mutating" error.

ORA-04091: "table %s.%s is mutating, trigger/function may not see it"
Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
Action: Rewrite the trigger (or function) so it does not read that table.

ORA-04091 is a very common error that occurs with triggers if triggers are not managed properly. A full understanding of triggers will help you avoid that error.

A mutating table is a table that is currently being modified by an update, delete, or insert statement. You will encounter the ORA-04091 error if you have a row trigger that reads or modifies the mutating table. For example, if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error.

Another way that this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table the trigger is triggering from.

If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.

It must be stressed that this solution should ONLY be used to overcome DML restrictions imposed on triggers in order to maintain referential integrity. Whenever possible it is recommended that normal declarative integrity should be used to maintain foreign key integrity. Enforcing this integrity through stored triggers and procedures will have an effect on performance compared with declarative integrity.

For this solution to work correctly there must be no declarative integrity constraints between objects to enforce the foreign key constraint. The basic principle behind this solution is to suppress the validation checks performed as a result of inserting or updating a foreign key in the CHILD table triggered by a cascade Update or Insert. These checks would normally verify the existence of the new foreign key value in the PARENT record (by SELECTING from the parent table). The suppression of this check is only carried out as a direct result of Cascade Update or Delete, as we can be confident that this new value for the foreign key in the CHILD record does exist (i.e. a result of it being inserted or updated in the PARENT table). In all other circumstances no suppression will take place (e.g. when changing the DEPTNO of an employee or when inserting a new employee).

The following code illustrates how this is achieved for the cascade Update scenario, the code can easily be modified to add the other functionality and encapsulate it all within the same package. The EMP and DEPT table have been used, with the column EMP.DEPTNO in the EMP table referencing DEPT.DEPTNO in the DEPT table.

Conceptual Model

 -------------  1. Trigger on PARENT for cascade function

 |           |       SET global_variable = 'NO';

 |  PARENT   |           do cascade update;

 |           |       SET global_variable = 'YES';

 -------------     End;

       |                              -------------------

       |                              | GLOBAL_VARIABLE |

       |                              -------------------

/|\

 ------------  2. Trigger on CHILD to maintain referential integrity

 |          |       CHECK global_variable;

 |  CHILD   |           IF global_variable = 'YES' THEN

 |          |              check existence of (fk) in PARENT;

 ------------           ELSE

                           null;

                  End;

EXAMPLE:

Oracle does not allow you the reading of a mutating table in a row trigger as it may lead to consistent data.

Attempt to do so, results in the following error:

ORA-04091 Table %s.%s is mutating, trigger/function may not see it

However, this operation can be performed within a statement trigger.

One way to work-around the mutating error within a row level trigger is to use a package PL/SQL table to store ROWIDs of updated records in a row trigger, and reprocess the updated records in a statement trigger.

Solution

Note that there are concurrency issues with this if more than one session tries to perform operations simultaneously. This is not intended as a total solution, but as the framework to help show one option.

The following is the sample work-around that used the EMP table.

Create the package to define the objects that will house the data.

create or replace package emp_pkg as
type emp_tab_type is table of rowid index by binary_integer;
emp_tab emp_tab_type;
emp_index binary_integer;
end emp_pkg;
/

Create the table level trigger to reset the index BEFORE any changes take place.

create or replace trigger emp_bef_stm_all before insert or update or delete on emp
begin
--Remember to reset the pl/sql table before each statement
emp_pkg.emp_index := 0;
end;
/

The following Row level trigger will fire for each row being modified and copy the RowID for each row to the EMP_TAB object defined within the package.

create or replace trigger emp_aft_row_all after insert or update or delete on emp
for each row
begin
--Store the rowid of updated record into global pl/sql table
emp_pkg.emp_index := emp_pkg.emp_index + 1;
emp_pkg.emp_tab(emp_pkg.emp_index) := :new.rowid;
end;
/

The following table level trigger will fire AFTER the others and will print out each RowID affected.

create or replace trigger emp_aft_stm_all after insert or update or delete on emp
begin
for i in 1 .. emp_pkg.emp_index loop
-- Re-process the updated records.
--There is no restriction here.
dbms_output.put_line(emp_pkg.emp_tab(i));
end loop;
emp_pkg.emp_index := 0;
end;
/

In order to see how this work from SQL*Plus, the output must be turned on.

SQL> SET SERVEROUTPUT ON

This first example, displays the RowIDs for Department 20 then issues a DELETE command. The trigger should fire and display the same RowIDs to confirm it is working. (The RowIDs will vary from machine to machine.)

SQL> SELECT ROWID, DEPTNO FROM EMP WHERE DEPTNO = 20;

ROWID DEPTNO
------------------ ----------
AAAM4iAAEAAAAG+AAA 20
AAAM4iAAEAAAAG+AAD 20
AAAM4iAAEAAAAG+AAH 20
AAAM4iAAEAAAAG+AAK 20
AAAM4iAAEAAAAG+AAM 20

SQL> DELETE EMP WHERE DEPTNO = 20;
AAAM4iAAEAAAAG+AAA
AAAM4iAAEAAAAG+AAD
AAAM4iAAEAAAAG+AAH
AAAM4iAAEAAAAG+AAK
AAAM4iAAEAAAAG+AAM

This second example, displays the RowIDs for Department 10 then issues an UPDATE command. The trigger should fire and display the same RowIDs to confirm it is working. (The RowIDs will vary from machine to machine.)

SQL> SELECT ROWID, DEPTNO FROM EMP WHERE DEPTNO=10;

ROWID DEPTNO
------------------ ----------
AAAM4iAAEAAAAG+AAG 10
AAAM4iAAEAAAAG+AAI 10
AAAM4iAAEAAAAG+AAN 10

SQL> UPDATE EMP SET JOB='Test' WHERE DEPTNO=10;
AAAM4iAAEAAAAG+AAG
AAAM4iAAEAAAAG+AAI
AAAM4iAAEAAAAG+AAN

3 rows updated.

To rollback the changes, issue the Rollback command.

SQL> ROLLBACK;

Rollback complete.

CONCLUSION :

The above methods are not submitted as solutions to all cases, however, those are provided as some of the possibilities for the Mutating problem. By following the above examples and customizing them to the individual needs , these Mutating problems can be avoided or fixed.

ORA-00936:missing expression

2008-04-26T02:09:00.005-04:00

ORA-00936:missing expression

Cause:A required part of a clause or expression has been omitted. For example, a SELECT statement may have been entered without a list of columns or expressions or with an incomplete expression. This message is also issued in cases where a reserved word is misused, as in SELECT TABLE.

Action:Check the statement syntax and specify the missing component.

ORA-00936:missing expression

This Oracle error is mainly related to the SQL SELECT statements. One obvious reason is select column list is missing or expressions in the selected columns are incomplete.

Example:

One Simple example is

SELECT FROM EMPLOYEE; raise ORA-00936 error as the column list is missing..

This ORA-00936 may also caused with respective to the usage of the exponential operator “**” in the SQL statements. It may result the following errors:

ORA 936 missing expression
PLS-936
ORA 6550 line , column <>num:

ORA-00936 Exponential Operator Scenario:

Symptoms

Using the exponential operator '**' in a SQL statement results in ORA-936 or PLS-936 depending on whether the SQL statement is executed from SQLPLUS prompt or within a PL/SQL block.

SQL> select 2**2 from dual;
select 2**2 from dual
*
ERROR at line 1:
ORA-00936: missing expression

SQL> declare
2 x number := 2;
3 y number := 4;
4 z number;
5 begin
6 select x**y into z from dual;
7 end;
8 /
y number := 4;
*
ERROR at line 3:
ORA-06550: line 6, column 10:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored

Cause

'**' is a valid exponential character in PL/SQL and cannot be used in a SQL statement. The exponential operator '**' can be used in PL/SQL statements:

SQL> declare
2 x number := 2;
3 y number := 4;
4 z number;
5 begin
6 z := x**y;
7 end;
8 /

PL/SQL procedure successfully completed.

Fix

Use power() function instead of '**' in SQL statement'.

SQL> select power(2,2) from dual;

POWER(2,2)
----------
4

SQL> declare
2 x number := 2;
3 y number := 4;
4 z number;
5 begin
6 select power(x,y) into z from dual;
7 end;
8 /

PL/SQL procedure successfully completed.

Oracle Bugs related to ORA-00936 error:

There are also a few oracle bugs related to ORA-00936. Listed below are two bugs related to ORA-00936. You can find complete list of bugs at Oracle Metalink.

1) Bug#:4567818 base Bug#:4192148 (unpublished) on 9207

2) Bug#:4212516 (unpublished) on 10.1.0.4.0.

With these bugs, ORA-00936 error is thrown when the SELECT ON view fails.

Basically, ORA-00936 is thrown when a SQL view is created from "create or replace view MY_VIEW as select t.*,other_tab_col from tab t, other_tab". This creates a view definition that is incorrect in the DBA_VIEWS, thus throwing ORA-00936 and possible core dumps.

In order to fix the bugs and resolve ORA-00936, MetaLink offers these solutions for the appropriate version:

Fix for 9.2.0.7 :

Patch 4192148 is available for Solaris (64bit) and AIX5L Based Systems (64-bit).

Fix for 10.1.0.4 :

Patch 4212516 is available for most of the platforms.

ORA-00936 Example from Oracle Forum:

Question:

When trying o use the code below, I keep getting ORA-00936. What is the problem?

Dim sSQL As String = _
"UPDATE TBLICPMETHODS SET Frequency = @Frequency, " & _
"Calibration = @Calibration, QCValue = @QCVAlue" & _
" WHERE Frequency = '" & Session("EditFrequency") & "' AND MethodNumber = " & ddlMethods.SelectedValue

Dim connConnection As New OleDbConnection(Application("ERSconnectionStr"))

connConnection.Open()

Dim cmdCommand As New OleDbCommand(sSQL, connConnection)

Dim prmFrequency As OleDbParameter = _
New OleDbParameter("@Frequency", OleDbType.VarChar, 75)
prmFrequency.Value = sFrequency
cmdCommand.Parameters.Add(prmFrequency)

Dim prmCalibration As OleDbParameter = _
New OleDbParameter("@Calibration", OleDbType.Double, 75)
prmCalibration.Value = CDbl(sCalibration)
cmdCommand.Parameters.Add(prmCalibration)

Dim prmQCValue As OleDbParameter = _
New OleDbParameter("@QCValue", OleDbType.Double, 75)
prmQCValue.Value = CDbl(sQCValue)

cmdCommand.Parameters.Add(prmQCValue)
' MessageBox.show(cmdCommand.CommandText + ":Frequency=" + e.Item.Cells(2).Text + ":Calibration=" + e.Item.Cells(3).Text + ":QCValue=" + e.Item.Cells(4).Text)
Try
cmdCommand.ExecuteNonQuery()
Catch ex As OleDbException
MessageBox.show(ex.Message)
End Try

Answer:

What you are forgetting is that in OLEDB you cannot use named parameters. This being, your use of @parameter is throwing ORA-00936. Trying taking out the @ and using a question mark instead to clear up ORA-00936. Here is an example, instead of:

"UPDATE TBLICPMETHODS SET Frequency = @Frequency, " & _
"Calibration = @Calibration, QCValue = @QCVAlue"

use:

"UPDATE TBLICPMETHODS SET Frequency = ?, " & _
"Calibration = ?, QCValue = ?"

- By Mendus

ORA-00604: error occurred at recursive SQL level string

2008-04-25T01:31:00.004-04:00

ORA-00604: error occurred at recursive SQL level string

Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).

Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support.

ORA-00604 error can occur in so many different contexts. The following is one such scenario

Connecting as DB User gives ORA-00604, ORA-01986: OPTIMIZER_GOAL is Obsolete and ORA-6512 Errors

This error applies to Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.3
This problem can occur on any platform

When you try to log-in to the database as a particular user, you get disconnected with the error : ORA-01986

SQL> conn dvamin/dvamin

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-01986: OPTIMIZER_GOAL is obsolete

ORA-06512: at line 2

Warning: You are no longer connected to ORACLE.

You check the init.ora / spfile and verify that you haven't set any parameter : OPTIMIZER_GOAL
still the database login errors out indicating that OPTIMIZER_GOAL is obsolete.
In the alert.log, you find :

Errors in file $ORACLE_BASE/admin/hrupg/udump/_ora_.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-01986: OPTIMIZER_GOAL is obsolete

ORA-06512: at line 2

Why this error is occurring ? Troubleshoot

Please check the tracefile : $ORACLE_BASE/admin/hrupg/udump/_ora_.trc
This shows :
-----------------------------------------------------------------------------------------------------------
*** SERVICE NAME:(SYS$USERS) 2006-11-21 08:20:41.909
*** SESSION ID:(104.17) 2006-11-21 08:20:41.909
Error in executing triggers on connect internal
*** 2006-11-21 08:20:41.911
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-01986: OPTIMIZER_GOAL is obsolete
ORA-06512: at line 2
-----------------------------------------------------------------------------------------------------------

You have configured some Database Login Triggers.
These triggers are trying to set a parameter that is obsolete in 10.2
Hence errors.

You might have configured a login trigger of the type :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE OR REPLACE TRIGGER LoginTrigger AFTER LOGON ON xxxxxx.SCHEMA
BEGIN
execute immediate
'alter session set OPTIMIZER_GOAL=FIRST_ROWS';
...
...
END;
/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SIMPLE TEST-CASE :
+++++++++++++++++++++++++++++++++++++++++++++++++++++
#############################################################
SQL> create user dvamin identified by dvamin quota 5M on users;
User created.

SQL> grant connect to dvamin;
Grant succeeded.

SQL> CREATE OR REPLACE TRIGGER LoginTrigger AFTER LOGON ON dvamin.schema
2 BEGIN
3 execute immediate
4 'alter session set OPTIMIZER_GOAL=FIRST_ROWS';
5 END;
6 /
Trigger created.

SQL> conn dvamin/dvamin
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01986: OPTIMIZER_GOAL is obsolete
ORA-06512: at line 2
Warning: You are no longer connected to ORACLE.
#############################################################

What is the Solution?

(i) Log-in as a user with ADMINISTER DATABASE TRIGGER privilege
(ii) Drop this trigger.
(iii) Log in as the required user successfully.

SQL> conn / as sysdba

Connected.

SQL> drop trigger LoginTrigger;

Trigger dropped.

SQL> conn dvamin/dvamin

Connected.

Rewrite the trigger as necessary if required. Please refer to the Oracle Administrator's Guide 10g as available in OTN if needed.

- Mendus

ORA-00600: internal error code arguments:

2008-04-23T00:28:00.005-04:00

ORA-00600: internal error code, arguments: [string], [string], [string], [string], [string], [string], [string], [string]

Cause: This is the generic internal error number for Oracle program exceptions. This indicates that a process has encountered an exceptional condition.

Action: Report as a bug - the first argument is the internal error number

Most of the Oracle database administrators face the ORA-00600 Internal Error one or other time and it is very intricate resolving this error. So what is this ORA-600 error anyway?

ORA-600 errors are raised from the kernel code of the Oracle RDBMS software when an internal inconsistency is detected or an unexpected condition is met. This situation is not necessarily a bug as it might be caused by problems with the Operating System, lack of resources, hardware failures, etc.

With the ORA-600 error comes a list of arguments in square brackets. The first of these arguments tells us from where in the code the error was caught and thus is the key information in identifying the problem. This argument is either a number or a character string. The remaining arguments are used to supply further information eg. values of internal variables etc.

Whenever an ORA-600 error is raised a trace file is generated in either USER_DUMP_DEST or BACKGROUND_DUMP_DEST depending on whether the error was caught in a user or a background process. The error is also written in the alert log and so is the name of the trace file. The trace file contains vital information about what led to the error condition.

The following are some in depth details about the ORA-00600 Oracle error

Error:  ORA 600

Text:   internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s]

-------------------------------------------------------------------------------

Cause:  This is the generic internal error number for Oracle program

        exceptions.    This indicates that a process has encountered an

        exceptional condition.

Action: Report as a bug - the first argument is the internal error number

*** Important: The notes below are for experienced users - See Note 22080.1

Explanation:

        Any ORA-600 error indicates Oracle has detected an internal

        inconsistency or a problem which it doesnt know how best to

        address. These are *NOT* necessarily bugs and can occur for

        reasons such as Operating System IO problems, running out of some

        resource etc..

Diagnosis:

        Oracle support need more information to determine what caused the

        error. The most useful items are:

        a) The alert log for the instance (and any other instances in

           a Parallel Server environment)

           These should be in BACKGROUND_DUMP_DEST.

        b) The trace file for the failing session and any other

           relevant trace files.

        c) Information on what action was being performed.

        d) Whether the problem is reproducible or was a one off

           occurance.

        e) Anything that has recently been changed in the system

           environment. Eg: Has the OS been upgraded, Have any

           database files just been restored, etc...

Articles:

        What information to collect for ORA 600

        ORA-600/ORA-7445 Troubleshooter   Note 153788.1

Where can you find more information about the ORA-600 error?

You can find further information on ORA-600 errors using MetaLink Search or by accessing the Oracle Internal Errors Technical Library (select "Top Tech Docs" in the main menu, locate the "Data Server" section. Here you will find the library dealing with Internal Errors).

Now you know this ORA-00600 error is more complex than any other Oracle error, so how to deal with these ORA-600 Internal Errors?

Unless you are able to identify the cause and possible fix for an ORA-600 error using the references mentioned below, it should be considered as a potentially serious issue and reported to Oracle Support for identification.

As mentioned in Note 146580.1 every ORA-600 error will generate a trace file. The name and location of the trace file is also written to the alert.log. To help Oracle Support determine what caused the ORA-600 you should open a TAR and supply the following information:

The database alert log located in BACKGROUND_DUMP_DEST (for Oracle Real Application Clusters supply the alert log for all instances in the environment).
The trace file mentioned in the alert log.

In case of multiple occurrences of the same ORA-600 error (ie. identical first arguments) you should only supply the first 2 or 3 trace files but do not delete any of the remaining traces as they might be required at a later stage.
In case of multiple different ORA-600 errors you should only supply the trace file from the first error. Oracle Support will request any other file that may be needed.

Recent changes to the system environment, eg. hardware modifications, operating system upgrades, restore of datafiles, power failures, etc.
Use RDA (Remote Diagnostic Agent) to collect important information:
Note 314422.1 "Remote Diagnostic Agent (RDA) 4.0 - Overview"

The following is partial list of the ORA-00600 known bugs and issues. You can find complete list and details about these bugs at Oracle Metalink.

Partial list of ORA-00600 known bugs and issues:

An Oracle ORA-00600 [729] : indicates a memory leak in the UGA. Setting diagnostic events 10262 ignores this error, that is, no ORA-00600 is produced even if a memory leak is detected.
ORA-00600 [16365] : indicates a Half Duplex Violation. Shared servers and dispatchers cannot both be writing to the same virtual curcuit at the same time because they communicate with a half-duplex protocol (TTC protocol).
ORA-00600 [kghpih:ds] : and ORA-00600 [17175] may occur in oracle 9.2.0.6 when two processes work on the same library cache object. fixed in oracle 9.2.0.7.. Note:300604.1
An Oracle ORA-00600 [3020] : Bug.2322620 - RECOVERY HALTED ON PHYSICAL STANDBY WITH ORA-600 AND ORA-10564 Details: ORA-600 [3020] can occur on recovery of LOB data.Fixed in 8.1.7.5 PAtch available. This error occurred on our database recovery on the primary server.
ora-00600 [12333] : Most probably a network error that may have caused bad data to be received or the client application may have sent the wrong data in the network buffer.
Oracle ORA-00600 [12700] : Indicates corruption in index, table or the mapping between them. Upon this discovery, oracle marks the whole block as corrupt and users will not have normal access to the data from the corrupted block. This can also break referntial integrity and should be referred to oracle.
ORA-00600 [lxhcsn] : Shutdown may dump ORA-7445. Known Bug 2311529

Conclusion:

In many cases ORA-00600 error indicates an existing corruption in the oracle database OR it can be a indication of events leading to data or database corruption.

Oracle database administrators should take an ORA-00600 error very seriously, and check any known fixes available in the Oracle MetlaLink and raise tars if no known fixes and work with oracle support to resolve the ORA-600 issue.

ORA-12154: TNS:could not resolve the connect identifier specified - Oracle Error

2008-04-22T00:53:00.009-04:00

ORA-12154: TNS:could not resolve the connect identifier specified - ORA-12154 Error/ Oracle Error

Cause: A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.

Action:

- If you are using local naming (TNSNAMES.ORA file):

o Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)

o Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.

o Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.

o Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.

- If you are using directory naming:

o Verify that "LDAP" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

o Verify that the LDAP directory server is up and that it is accessible.

o Verify that the net service name or database name used as the connect identifier is configured in the directory.

o Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier

- If you are using easy connect naming:

o Verify that "EZCONNECT" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

o Make sure the host, port and service name specified are correct.

o Try enclosing the connect identifier in quote marks. See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming.

This Oracle error ORA-12154 is thrown during the logon process to a database. This ORA-12154 error indicates that the communication software in Oracle ( SQL *Net ) did not recognize the host/service name specified in the connection parameters.

For a database name to be recognized, it must be resolved by Oracle Client. To resolve a name, Oracle client uses either a TNSNames.ora, A Directory Server or an Oracle Names Server.

ORA-12154: TNS:could not resolve service name - ORA-12154 Error / Oracle Error

If the cause for this Oracle error is Oracle Net could not locate the net service name specified in the tnsnames.ora configuration file the perform the following steps to resolve this ora error / tns error:

· Verify that a tnsnames.ora file exists.

· Verify that there are not multiple copies of the tnsnames.ora file.

· In the tnsnames.ora file, verify that the net service name specified in your connect string is mapped to a connect descriptor.

· Verify that there are no duplicate copies of the sqlnet.ora file.

· If you are using domain names, verify that your sqlnet.ora file contains a NAMES.DEFAULT_DOMAIN parameter. If this parameter does not exist, you must specify the domain name in your connect string.

· If you are not using domain names, and this parameter exists, delete it or disable it by commenting it out.

· If you are connecting from a login dialog box, verify that you are not placing an "@" symbol before your connect net service name.

· Activate client tracing and repeat the operation.

If the cause for the ORA-12514 Oracle error is " Oracle Net could not locate the database service name or net service name specified in the directory server" then perform the following steps for resolving this ora error or tns error:

· Verify that the database service or net service name entry exists in the directory that this computer was configured to use.

· Verify that the sqlnet.ora file includes the following entry:

· NAMES.DIRECTORY_PATH=(ldap, other_naming_methods)

ORA-28009: connection to sys should be as sysdba or sysoper

2008-04-20T02:21:00.000-04:00

ORA-28009: connection to sys should be as sysdba or sysoper

Cause: connect sys/ is no longer a valid syntax

Action: Try connect sys/ as sysdba or connect sys/ as sysoper

ORA-28009: "connection to sys should be as sysdba or sysoper":

This occurs from Oracle 9i onwards if you try to connect as user SYS just by specifying username/password.

Instead you must connect 'as sysdba':

sqlplus "sys/sys@tnsname.world as sysdba"

Note the quotes if it's on a Unix command line, which are necessary to ensure that the connection string is all one argument.

ORA-21000:error number argument to raise_application_error is out of range

2008-04-20T02:18:00.001-04:00

ORA-21000:error number argument to raise_application_error of stringstring is out of range

Cause:An attempt was made to specify a number not in the allowed range.

Action:Use an error number in the range of -20000 to -20999, inclusive.

ORA-21000: "error number argument to raise_application_error of 0 is out of range":

The PL/SQL RAISE_APPLICATION_ERROR procedure has been called with an error code of 0. The only valid error codes are 20000 to 20099.

The usualy cause for a value of 0 being used is that a program has failed to set amn error variable correctly, e.g. it has called an exception but not set the variable.

PLS-00222: no function with name exists

2008-04-20T02:14:00.000-04:00

PLS-00222: no function with name 'string' exists in this scope

Cause: An identifier being referenced as a function was not declared or actually represents another object (for example, it might have been declared as a procedure).

Action: Check the spelling and declaration of the identifier. Also confirm that the declaration is placed correctly in the block structure.

PLS-00222: no function with name 'name' exists in this scope:

It cannot find the 'function' specified. This may be a packaged or standalone function.

If the routine is spelt correctly then the clue to the problem may be the word 'function' in the error message. It is looking for a function as opposed to a procedure. Perhaps it is really a procedure but has been mis-called, i.e. with a return variable.

ORA-04030: out of process memory when trying to allocate string bytes

2008-04-20T02:11:00.001-04:00

ORA-04030: out of process memory when trying to allocate string bytes (string,string)

Cause: Operating system process private memory has been exhausted

Action: none

ORA-04030: "out of process memory when trying to allocate %s bytes (%s,%s)":

The operating system's process private memory has been exhausted and a "malloc" function has failed. The database administrator or operating system administrator can often resolve this by increasing the process memory quota. (O/S specific parameters: maxdsiz, maxtsiz, maxssiz, maxdsiz64, maxtsiz64, maxssiz64 and "max_per_proc_data_size", "max_per_proc_address_space", "max_per_proc_stack_size" )

The user resource limit (ulimit) may be the problem. You could try setting it to unlimited for memory.

There may be a bug in the application that causes excessive allocations of process memory space, typically because it is not freeing up the space, such as opening cursors but not closing them.

It is important that the full error message is available. The part in the parenthisis will tell more about the memory structure that was having problems.

As a workaround you can try calling dbms_session.free_unused_user_memory at key points in code. This releases the unused session memory.

You can set event="4030 trace name errorstack level 10" in the init.ora to generate a trace file when you reproduce this problem.

ORA-04031: unable to allocate string bytes of shared memory

2008-04-20T02:05:00.003-04:00

ORA-04031: unable to allocate string bytes of shared memory ("string","string","string","string")

Cause: More shared memory is needed than was allocated in the shared pool.

Action: If the shared pool is out of memory, either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size". If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".

ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","user","PL/SQL MPCODE","BAMIMA: Bam Buffer"):

This is usually followed by the "ORA-06508: PL/SQL: could not find program unit being called" error.

This is likely to be caused by fragmentation within the shared pool, such that there is insufficient contiguous free memory to load entire PL/SQL packages.

The solution is to increase the "shared_pool_reserved_size" parameter in the "init.ora" parameter file. This defaults to 5% of the shared pool, which may be fine if a large shared pool, but not if a small one (where majoriry is used by large PL/SQL objects).

ORA-03237: Initial Extent of specified size cannot be allocated in tablespace (string)

2008-04-20T02:01:00.001-04:00

ORA-03237: Initial Extent of specified size cannot be allocated in tablespace (string)

Cause: Too large a size for an initial extent due to freelist group specification

Action: Reduce number of freelist groups specified for segment

ORA-03237: "Initial Extent of specified size cannot be allocated":

Unable to create queues, or tables containing CLOBs

If you see the following error:

ORA-03237: Initial Extent of specified size cannot be allocated

while creating queue tables (which contain a BLOB column) or tables that contain CLOBs, it may be because of a database tablespace configuration problem. This only applies to the 9i databases which have been configured to use locally managed tablespaces.

This is a documented restriction in Oracle that LOBs cannot be created in 'locally managed uniform extent tablespaces with extent size less than 3 blocks'.

ORA-01480: trailing null missing from STR bind value

2008-04-20T01:57:00.000-04:00

ORA-01480: trailing null missing from STR bind value

Cause: A bind variable of type 5 (null-terminated string) does not contain the terminating null in its buffer.

Action: Terminate the string with a null character

ORA-01480: trailing null missing from STR bind value:

The column that is being read has a value which is too large to fit into the variable in the C program if a SELECT, or, the string contains a longer value than the column can support if it is an INSERT or UPDATE statement.

ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces

2008-04-20T01:46:00.001-04:00

ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces

Cause: No overflow segment defined.

Action: Add overflow segment

ORA-01429: "Index-Organized Table: no data segment to store overflow row-pieces":

The maximum sizes of the columns comprising an index cannot be more than half of the block size.

When an Index-Organised Table is being created, many or all of the columns are held within the B*-Tree index. The error:

ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces

will occur if the maximum size of the columns held in the index part of the IOT are more than half the block size for the tablespace (or database, prior to 9i).

This problem can be overcome by specifying an overflow segment in which either some columns on long rows or all columns after a named column are held separate for the rest of the row, i.e not in the B*-Tree structure. Obviously the primary key columns must be in the index.

The downside of using an overflow segment is that performance is reduced whenever the columns in the overflow segment need to be accessed.

Alternatively, the table can be configured to reject long rows. This is done by specifying a PCTTHRESHOLD value (0-50) without an OVERFLOW clause.

ORA-01422: exact fetch returns more than requested number of rows

2008-04-20T01:33:00.005-04:00

ORA-01422: exact fetch returns more than requested number of rows

Cause: The number specified in exact fetch is less than the rows returned.

Action: Rewrite the query or change number of rows requested

ORA-01422: "exact fetch returns more than requested number of rows":

This can be for one of the following three reasons:

There's a bug in the code and the developer did not realise that you could get more than one row returned;
The data has been hacked rather than using the API so that validation has been broken;
The software is OK, what the user did was OK, but two parallel updates occurred at the same time and neither could see the uncommitted change that the other did - hence not validated correctly.

ORA-01000:maximum open cursors exceeded

2008-04-20T01:11:00.002-04:00

ORA-01000:maximum open cursors exceeded

Cause:A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.

Action:Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle.

ORA-01000: "maximum open cursors exceeded":

This error occurs when the number of cursors currently open by a single database connection have exceeded the value of the "open_cursors" paramerer in the "init.ora" file.

You can find the current maxiumum value allowed using "show parameter open_cursors" from SQL*Plus or Server Manager.

This is a per user limit, and is the number of cursors which have been opened but not closed since the database connection was made. A large limit is typically required when performing a large number of queries since the last commit, such as validating a catalogue. The cause is usually "recursive SQL", e.g. having to extend a table or firing of triggers.

There may be a bug somewhere in the application so that a series of cursors are being opened and not closed.

As a workaround, the user could log out and log back in again, going straight to the function which caused the error. If this resolves the problem, it is likely that cursors were remaining open from a previous function.

ORA-00997: illegal use of LONG datatype

2008-04-19T23:58:00.001-04:00

ORA-00997: illegal use of LONG datatype

Cause: A value of datatype LONG was used in a function or in a DISTINCT, WHERE, CONNECT BY, GROUP BY, or ORDER BY clause. A LONG value can only be used in a SELECT clause.

Action: Remove the LONG value from the function or clause

ORA-00997: "illegal use of LONG datatype":

When trying to reorganize a database table that includes a LONG column, there is likely to be an Oracle error message such as:

ORA-00997: illegal use of LONG datatype

This is because it is not possible to use a LONG column in any SQL statement. It can only be used where the application is retrieving part of the column - by specifying the start byte and the number of bytes.

The following will cause the above error:

ALTER TABLE tablename MOVE TABLESPACE tablespacename;

CREATE TABLE newtablename AS SELECT * FROM oldtablename;

ORA-00055: maximum number of DML locks exceeded

2008-04-19T23:38:00.005-04:00

ORA-00055: maximum number of DML locks exceeded

Cause: Ran out of DML lock state objects.

Action: Increase the value of the DML_LOCKS initialization parameter and warm start.

ORA-00055 maximum number of DML locks exceeded:

This tends to be an intermittent problem, and occurs at moments of peak usage in an Oracle database.

It can be resolved by changing a parameter in the "initSID.ora" file:

dml_locks = 200

Basically, if the DML Locks limit has been set at 200, then

. 200 people could each be updating one table at a time
· or 20 people could all be updating 10 tables at a time,
· or 1 user could be doing an account number rename and using 100 tables while 10 other people could be updating 10 tables.

These updates do not need to be the same table.

The number of locks required increased from Oracle8 onwards since partitioned tables would use a lock for each partition.

ORA-00030: User session ID does not exist.

2008-04-18T00:39:00.000-04:00

ORA-00030: User session ID does not exist.

Cause: The user session ID no longer exists, probably because the session was logged out.

Action: Use a valid session ID.

ORA-00029: session is not a user session

2008-04-18T00:37:00.002-04:00

ORA-00029: session is not a user session

Cause: The session ID specified in an ALTER SYSTEM KILL SESSION command was not a user session (for example, recursive, etc.).

Action: Retry with a user session ID.

Example:

The following is an example scenario for ORA-00029 :

I have a big problem here on my ORACLE 10.2.0.2 P14 on Windows 2003
32-bit.

Something happened at the weekend (and I have no idea what because
nobody was working) that lead to the following:

Maximum number of processes (200) exceeded within a couple of minutes
after DB start. No further
connections possible (ORA-00020).

Now, what I researched so far is that there is apparantly a record in
V$LOCK with lmode=6 without username, always with
the same SID and serial# but different thread numbers after each DB
startup. The thread numbers
do not exist in Windows Manager and there is no locked record, latch, no
deadlock or whatever out there.

I tried to use "ALTER SYSTEM KILL SESSION 'xxxx,x';" but I receive the
ORA-00029 "session is not a user session".
So I tried to use "orakill ". I receive the expected
correct (!) message but all what happens is
that the database is shutdown. After new startup the record still stays
active and blocks everything.

Not only can no further connection be established (e.g. SAP, BRGUI, OEM,
SQLPLUS) but also my filesystem is filling up
with trace files about ORA-00020 and of course the alert log with
messages about process M000 died and new creation failed
(4 lines each second!)...

Can anybody help me with that? I have spent now 3 days on this and don't
know wherelese to research further options...

And of course I tried to increase the number of processes and sessions,
up to 600 proc. with 1200 sessions...no change
in behavor...just took longer to reach the limit...

My SGA total size is 1.3GB while windows is booted with 3GB option.
PGA-target is 300M.

------------
Verify PMON is running in the background...
select *
from v$session
where type ='BACKGROUND'
AND program like '%PMON%';

-----------------
Resolution :
I just fixed it! Since I could not get rid of the locked record I tried
this:
First I did a reset on all system user passwords. Then deleted
the remote-login-password file and recreated it. Processes are now
perfectly in the normal range!

ORA-00028: your session has been killed

2008-04-18T00:36:00.000-04:00

ORA-00028: your session has been killed

Cause: A privileged user has killed your session and you are no longer logged on to the database.

Action: Login again if you wish to continue working.

ORA-00027: cannot kill current session

2008-04-18T00:29:00.001-04:00

ORA-00027: cannot kill current session

Cause: Attempted to use ALTER SYSTEM KILL SESSION to kill the current session.

Action: None.

ORA-00026: missing or invalid session ID

2008-04-18T00:26:00.001-04:00

ORA-00026: missing or invalid session ID

Cause: Missing or invalid session ID string for ALTER SYSTEM KILL SESSION.

Action: Retry with a valid session ID.

ORA-00025: failed to allocate string

2008-04-18T00:20:00.002-04:00

ORA-00025: failed to allocate string

Cause: Out of memory.

Action: Restart with larger sga heap.

ORA-00024: logins from more than one process not allowed in single-process mode

2008-04-18T00:08:00.002-04:00

ORA-00024: logins from more than one process not allowed in single-process mode

Cause: Trying to login more than once from different processes for ORACLE started in single-process mode.

Action: Logoff from the other process.

ORA-00023: session references process private memory; cannot detach session

2008-04-18T00:06:00.000-04:00

ORA-00023: session references process private memory; cannot detach session

Cause: An attempt was made to detach the current session when it contains references to process private memory.

Action: A session may contain references to process memory (PGA) if it has an open network connection, a very large context area, or operating system privileges. To allow the detach, it may be necessary to close the session's database links and/or cursors. Detaching a session with operating system privileges is always disallowed.

ORA-00022: invalid session ID; access denied

2008-04-17T22:33:00.002-04:00

ORA-00022: invalid session ID; access denied

Cause: Either the session specified does not exist or the caller does not have the privilege to access it.

Action: Specify a valid session ID that you have privilege to access, that is either you own it or you have the CHANGE_USER privilege.

ORA-00021: session attached to some other process; cannot switch session

2008-04-12T23:29:00.001-04:00

ORA-00021: session attached to some other process; cannot switch session

Cause: The user session is currently used by others.

Action: Do not switch to a session attached to some other process.

ORA-00020: maximum number of processes exceeded.

2008-04-10T01:44:00.002-04:00

ORA-00020: maximum number of processes (string) exceeded.

Cause: All process state objects are in use.

Action: Increase the value of the PROCESSES initialization parameter.

Example:

The following is an example scenario for ORA-00020 and this scenario was obtained from web and provide here for informational purpose:

I have a big problem here on my ORACLE 10.2.0.2 P14 on Windows 2003
32-bit.

Something happened at the weekend (and I have no idea what because
nobody was working) that lead to the following:

Maximum number of processes (200) exceeded within a couple of minutes
after DB start. No further
connections possible (ORA-00020).

Now, what I researched so far is that there is apparantly a record in
V$LOCK with lmode=6 without username, always with
the same SID and serial# but different thread numbers after each DB
startup. The thread numbers
do not exist in Windows Manager and there is no locked record, latch, no
deadlock or whatever out there.

I tried to use "ALTER SYSTEM KILL SESSION 'xxxx,x';" but I receive the
ORA-00029 "session is not a user session".
So I tried to use "orakill ". I receive the expected
correct (!) message but all what happens is
that the database is shutdown. After new startup the record still stays
active and blocks everything.

Not only can no further connection be established (e.g. SAP, BRGUI, OEM,
SQLPLUS) but also my filesystem is filling up
with trace files about ORA-00020 and of course the alert log with
messages about process M000 died and new creation failed
(4 lines each second!)...

Can anybody help me with that? I have spent now 3 days on this and don't
know wherelese to research further options...

And of course I tried to increase the number of processes and sessions,
up to 600 proc. with 1200 sessions...no change
in behavor...just took longer to reach the limit...

My SGA total size is 1.3GB while windows is booted with 3GB option.
PGA-target is 300M.

------------
Verify PMON is running in the background...
select *
from v$session
where type ='BACKGROUND'
AND program like '%PMON%';

-----------------
Resolution :
I just fixed it! Since I could not get rid of the locked record I tried
this:
First I did a reset on all system user passwords. Then deleted
the remote-login-password file and recreated it. Processes are now
perfectly in the normal range!

ORA-00019: maximum number of session licenses exceeded

2008-04-10T01:42:00.003-04:00

ORA-00019: maximum number of session licenses exceeded

Cause: All licenses are in use.

Action: Increase the value of the LICENSE MAX SESSIONS initialization parameter.

ORA-00018: maximum number of sessions exceeded

2008-04-10T01:34:00.001-04:00

ORA-00018: maximum number of sessions exceeded

Cause: All session state objects are in use

Action: Increase the value of the SESSIONS initialization parameter.

ORA-00017: session requested to set trace event

2008-04-10T01:31:00.001-04:00

ORA-00017: session requested to set trace event

Cause: The current session was requested to set a trace event by another session.

Action: This is used internally; no action is required.

ORA-00001: unique constraint

2008-04-10T01:27:00.002-04:00

ORA-00001: unique constraint (string.string) violated

Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level.

Action: Either remove the unique restriction or do not insert the key.

This error is related to the duplicate data. Whenever you try to insert same unique/primary key you will encounter this error.

Example:

SQL> CREATE TABLE Employee (
2 EMP_ID VARCHAR(10) PRIMARY KEY,
3 name VARCHAR(20),
4 address VARCHAR(20)
5 );

Table created.

SQL>
SQL> INSERT INTO Employee VALUES ('0602751122','Sree Mendu','Cincinnati OH');

1 row created.

SQL> INSERT INTO Employee VALUES ('0602751122','Sree Ram','Cincinnati OH');
INSERT INTO Students VALUES ('0602751122','Sree Ram','Cincinnati OH')
*
ERROR at line 1:
ORA-00001: unique constraint (AGHT.SYS_C005092) violated

ORA-01555: snapshot too old

2008-04-09T00:46:00.005-04:00

ORA-01555: snapshot too old: rollback segment number string with name "string" too small

Cause: rollback records needed by a reader for consistent read are overwritten by other writers

Action: If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments

Details:

There are various reasons why users get the ORA-01555 error. The most common reason is that the rollback segments are too small.

Oracle uses rollback segments to reconstruct the read-consistent snapshot of the data. Whenever a transaction makes any changes, a snapshot of the record before the changes were made is copied to a rollback segment. Once a transaction is complete, its data is not deleted from the rollback segment. It remains there to service the queries and transactions that began executing before it was committed. This may cause problems with long queries because these blocks may be overwritten by other transactions, even though the separate long-running query against those blocks has not completed.

So users may potentially experience this error whenever a long-running query is executed at the same time as data manipulation(DML) transactions.

How to avoid/fix this error :

Increase the size of all the roll back segments.
Add more rollback segments.
Increase the ‘OPTIMAL’ size of the roll back segments.
Tune the application to “commit” more frequently such that smaller rollback space is used and this condition can be avoided.
Oracle recommended ‘proper’ solution is to schedule long-running queries at times when online transaction processing is at a minimum.

Example:

ORA-01555 Error Scenario:

Sample application logic causing ORA-01555 error:

Prepare a select
fetchrowArray
while the row is not null
update the row
if nrows mod commitsize then commit
fetch the next row
end while

final commit

This application is developed in Perl, DBD, DBI and oracle. It raised the error as shown below:

DBD::Oracle::st fetchrow_array failed: ORA-01555: snapshot too old: rollback segment number 15 with name "_SYSSMU15$" too small (DBD ERROR: OCIStmtFetch) [for Statement "

Analysis and Resolution:

As you can see from the application logic, the rows selected by the outer select query are being modified by the update statement. ie both are acting on the same table . It can be resolved by setting UNDO_RETENTION appropriately large enough BUT it may not be the best solution.
The better solution may be separating out select and updates in to different transactions.

After rewriting the application logic by separating data selection and data update activities, this “ORA-01555: snapshot too old” no longer occurred.

Oracle Error Information

2008-04-08T00:29:00.006-04:00

Welcome to Oracle Error Information Blog. Oracle is one of the leading relational databases in the industry and it is widely used as back end database or even we can say as a backbone for large scale software applications and data-ware houses. Oracle operates by predefined set of rules.

During the course of application development, unit testing , performance testing or even in Production, Oracle may generate an error if any of these predefined rules are violated. If any error, Oracle fails the operation with an ORA- error code. Oracle provides list of the error codes with little bit of explanation in their documentation but it didn't provide much details or examples of the errors. In the internet also, information about these Oracle errors such as example scenarios for these errors and info about fixing these errors is not easily available/accessible as it is usually buried in various forums.

The intention of this blog is to provide easily searchable Oracle Error Information with examples such that developers, testers, DBAs, production support people and other Oracle users benefit by finding all needed information about an Oracle error by one click.

If you have questions not covered in Oracle Error Information Blog please feel free to email your question to smendu@gmail.com

- Mendus,
Oracle OCP DBA