Hello All,
Our environment is Oracle 11.2.0.3.0.
In our production environment, we are getting this error frequently but not in any other environment (dev/qa/stage), And strangely, when we are trying to simulate the error in non-production environments, we are not getting the error.
The line at which this error is occurring in production environment is like below:
...
...
FOR rec IN (SELECT DISTINCT TRUNC(syslog_time)
FROM table_name
WHERE col1='C'
AND col2='N') LOOP
...
...
END LOOP
...
Obviously we checked for the existence of the table and it was positive. For your information, the loop shown above is running for approximately 30 minutes/loop iteration and usually there are 2-3 rows returned by the cursor. In between the cursor for loop, we are also dropping and adding partitions(another session) from which we selected the cursor data. So we tried to simulate it via a different table by the the following code.
create table test_8103(user_name varchar2(30)) partition by list(user_name) (partition p_david values('David'), partition p_sinken values('Sinken'), partition p_default values(DEFAULT));
insert into test_8103 select 'David' user_name from dual;
insert into test_8103 select 'David' user_name from dual;
insert into test_8103 select 'Sinken' user_name from dual;
insert into test_8103 select 'Sinken' user_name from dual;
insert into test_8103 select 'Ish' user_name from dual;
insert into test_8103 select 'Ish' user_name from dual;
commit;
SESSION-1
set serveroutput on;
set timi on;
begin
dbms_output.put_line(to_char(sysdate,'dd-MON-yyyy hh24:mi:ss'));
for rec in (select user_name from test_8103 where user_name='David') loop
dbms_output.put_line(rec.user_name);
dbms_lock.sleep(10);
end loop;
dbms_output.put_line(to_char(sysdate,'dd-MON-yyyy hh24:mi:ss'));
end;
/
Output:
02-NOV-2013 03:56:27
David
David
02-NOV-2013 03:57:08
PL/SQL procedure successfully completed.
Elapsed: 00:00:40.59
SESSION-2 (Executed 10 Seconds after SESSION-1 started)
ALTER TABLE test_8103 DROP PARTITION p_david;
Output:
Table altered.
We are trying to figure out the root cause but no luck till yet . Any pointers on similar kind of problem faced by anybody will be helpful.
I had gone through the below URL but it all bugs around this error had been fixed in 10.2
ORA-08103: object no longer exists tips
Please clarify my doubts on below points:
1. From the above example it is clear that, after opening the cursor the data resides in memory. Otherwise it could have given me the desired error (ORA-08103). Please correct me.
2. Is there any chance that, the the cursor data residing in cursor area (memory) can be flushed/aged out? Only in that case is it giving ORA-08103 error?
Thanks in advance.