How To Determine the SQL Expression That May Be Causing ORA-01000


#sqlplus / as sysdba
 SQL> select
 c.sid as "OraSID",
 c.address||':'||c.hash_value as "SQL Address",
 COUNT(c.saddr) as "Cursor Copies"
 from v$open_cursor c
 group by
 c.sid,
 c.address||':'||c.hash_value
 having
 COUNT(c.saddr) > 2
 order by
 3 DESC ;
then pass the result from 'SQL Address' to the following :
SQL> select SQL_FULLTEXT from v$sql where ADDRESS ||':'||HASH_VALUE = '<SQL Address>' ;
To determine the SQL by user session :
SQL> select user_name,o.sid, osuser, machine, count(*) num_curs
 from v$open_cursor o, v$session s
 where o.sid=s.sid
 group by user_name,o.sid, osuser, machine
 order by num_curs desc;
then pass the result from o.sid to the following :
SQL> select q.sql_text
 from v$open_cursor o, v$sql q
 where q.hash_value=o.hash_value and o.sid = XXX;
 You can use event 1000 for additional diagnosis :
event="1000 trace name errorstack level 3"
 
This event may be set on SESSION or SYSTEM level
For system :
alter system set events '1000 trace name errorstack level 3';
For session :
alter session set events '1000 trace name errorstack level 3';

MOS Doc ID 1333600.1
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: