"Snapshot too Old" exception when writing to a Global Temp Table.
Jimbo1
I'm currently experiencing a perplexing problem with the "ORA-01555:
Snapshot too Old" Exception when writing to a Global Temporary Table
on an Oracle 9i Release 2 database.
The Temp Table concerned has been created with the ON COMMIT PRESERVE
ROWS specification.
I have a PL/SQL Procedure that is performing data validation on the
rows in this Temp Table. The rows that pass validation are stored in
memory in an associative array.
When validation is complete and I have my array of validated temp
table rows/records, I perofrm the following statement:
DELETE FROM global_temp_table
WHERE client = :p_current_client;
COMMIT;
That works fine. On the next step, I BULK-Bind INSERT the associative
array of validated temp table records:
FORALL idx IN t_validated_recs_array.FIRST ..
t_validated_recs_array.LAST
INSERT INTO global_temp_table
VALUES t_validated_recs_array(idx);
COMMIT;
At this point, I get the "ORA-01555: Snapshot too old" exception.
I cannot understand why I'm getting this exception, as the only cursor
I opened has been closed and I know for a fact that nobody else is
connected to the database I'm using at the moment.
Is this a result of delayed block cleanout?
I've taken a look at the following link about this particular error,
but nothing there seems to apply to my situation:
http://www.fors.com/orasupp//rdbms/dba/40689_1.HTM
Unfortunately, I don't have the necessary privs to be able to view the
rollback segment concerned. All I know is that we just have a single
rollback segment (the first time I've ever encountered this; all other
sites I've worked at have had multiple rollback segments), and I've
been told that it's "big". Personally, I'm wondering if it's "big
enough", or if we should create at least one additional rollback
segment.
Does anybody here have any opinions or help/advice to offer?
Cheers.
James
Ana C. Dent
493f-901b-5...@f24g2000prh.googlegroups.com:
http://asktom.oracle.com & do keyword search on ORA-01555
>I know for a fact that nobody else is connected to the database I'm
using at the moment.
Then as Pogo once said, "Wehave met the enemy, & they is us."
Dion Cho
Dion Cho
Jimbo1
> Just to clarify your situation, is your column is LOB type?
>
> Dion Cho
Ana, thanks for your response. I appreciate the irony, re. my
'fact'! ;o)
Dion, One of the columns in the temp table is a CLOB.
Dion Cho
You need to check your whole error message.
If your error message contains actual rollback segment name, you have
rollback failure on normal data.
But if your error message contains no valid rollback segment name(like
blank), the failure is on the lob segment.
LOB has its own mechanism for rollback and reason for snapshot too old
error.
For e.g., low pctversion or lob corruption.
Search metalink for that.
Dion Cho
Jimbo1
Hi Dion,
Thanks for your illuminating post. You're just identified my problem.
The error message I'm getting is indeed:
"ORA-01555: snapshot too old: rollback segment number with name ""
too small"
So from what you're saying, it looks like the failure I'm experiencing
is definitely on the LOB segment.
I was wondering why no rollback segment name was being quoted, but as
I don't have the privs to see the rollback segment(s) (worse luck), I
was assuming this was just a default rollback segment that hadn't been
explicitly named.
I haven't done this much work with LOBs before, so am pretty
inexperienced in this area. You've just cleared the fog of perplexity.
Thanks once again for your help.
James
abhihot11
Hi everyone,
Can someone please send me the most recent oracle 9i dba dumps of
funda I,or the entire set of qns for DBA for 9i....I am giving the
exam coming week...
Any help would be much appreciated.
Eric
So this guy gets some stuff from someone on the internet, spends a week
cramming his brains with things he doesn't understand, and passes an
exam. Now we're supposed to believe that this makes him an expert.
E.
Steve Howard
LOL! This was actually funny.
Thanks!
Steve
sybr...@hccnet.nl
wrote:
This doesn't matter. Being an expert is not important. Being certified
is. This is why so many people can't resolve ORA-12514 or PLS-201.
They are certified.
Remember bean counters do not value expertise, they value credentials,
that favourite word of Burleson.
--
Sybrand Bakker
Senior Oracle DBA
Shakespeare
"Eric" <er...@deptj.demon.co.uk> schreef in bericht
news:slrng42an...@tasso.deptj.demon.co.uk...
I understood he is GIVING an exam...
Shakespeare
abhihot11
> On Sat, 31 May 2008 11:39:59 +0100, Eric <e...@deptj.demon.co.uk>
> wrote:
>
> >On 2008-05-31, abhihot11 <abhiho...@gmail.com> wrote:
>
> >> Hi everyone,
>
> >> Can someone please send me the most recent oracle 9i dba dumps of
> >> funda I,or the entire set of qns for DBA for 9i....I am giving the
> >> exam coming week...
> >> Any help would be much appreciated.
>
> >So this guy gets some stuff from someone on the internet, spends a week
> >cramming his brains with things he doesn't understand, and passes an
> >exam. Now we're supposed to believe that this makes him an expert.
>
> >E.
>
> This doesn't matter. Being an expert is not important. Being certified
> is. This is why so many people can't resolve ORA-12514 or PLS-201.
> They are certified.
> Remember bean counters do not value expertise, they value credentials,
> that favourite word of Burleson.
>
> --
> Sybrand Bakker
> Senior Oracle DBA
Thanks Sybrand.....I am new to this group and also new in this
domain.....I had heard that before ones certification only the book
knowledge is not enough so I thought of checking my skills with the
dumps as I have read the funda I.
Some people are born DBA's when they mock around others. They forget
that they were also naive at one point of time before they have became
experienced today. Thanks to all those who cared to answer my simple
question.......
Well, Sybrand if u can help me in this matter I would really
appreciate.
Thanks again for ur support.
Regards,
Abhi