>
> Hello
>
> I'm getting these errors when I execute the script below:
>
> Errors:
>
> 01555: snapshot is too old; rollback segment number 4 with name "R03"
>
> Script:
>
> SELECT se_desc, na_desc, DECODE (fw_sex, 'L', 'Lelaki', 'P',
> 'Perempuan') fw_sex, COUNT (fw_reg_icno) fw_cnt
> FROM mh_renewal, dc_fworkers d, mh_sector, mh_nation
> WHERE fw_nation = na_code
> AND fw_sector = se_code
> AND rl_doc_no = fw_id
> AND fw_worker_type IN ('F', 'M')
> AND rl_app_date
> BETWEEN TO_DATE (:p_from_date, 'DD/MM/YYYY')
> AND TO_DATE (:p_to_date, 'DD/MM/YYYY')
> GROUP BY se_desc, na_desc, fw_sex
>
> Any ideas what causes this and workarounds? I attached a file regarding
> the rollback segments size :
>
Whatever your rollback segment sizes are, they are not enough for your
transaction. The ORA-1555 indicates this. You have two choices:
1) Increase the sizes of all rollback segments. How large is impossible
for me to say without knowing what indexes exist, the size of the four
tables, etc. You will have to try some larger value, and keep increasing it
if you keep getting the ORA-1555 error.
2) Have a large rollback segment, and before you execute a transaction, enter:
"ALTER SESSION SET TRANSACTION rollback_name;"
to use a large rollback segment.
_______________________________________________________________________________________________
John Bush (john_bush@hotmail.com) writes:
I love your web site. It's loaded with great information. I was just
clicking through some of the tips and found one I thought I might be able to
add something to. This is the infamous "Snapshot too old" error.
We have some processes that run quite a while, and occassionally would get
this error. Our rollback segments were plenty large, so I couldn't
understand what was going on. Finally I found the root cause.
We were running a read only report that had a main loop getting data from a
cursor and then did a lot of work along the way. While this report was
running, some activity would occur that would change the table this query
was reading from. After the changes were committed, Oracle house cleaning
would kick in and shrink the rollback segment that held the original values.
When the report went back to fetch a row that had been modified, Oracle finds
the row based on the SCN at the time the cursor was opened. This row
has been changed in the database and committed, and the row based on the SCN
is not available on a rollback segment. So Oracle is unable to return
consistent data and generates this error.
The good news is, this is preventable. There is more information available
at http://www.ixora.com.au/tips/admin/ora-1555.htm . This site also includes
some scripts that will prevent the error.
I was unable to use the scripts as given on this site, but I created my own
version.
I first created the driver script:
pre1555
sqlplus $1 prevent_1555.sql $1 $$ $2
chmod u+x prevent_1555.sh
prevent_1555.sh
Next I created this SQL file:
prevent_1555.sql
spool prevent_1555.sh
prompt rm -f prevent_1555.sh
select 'sqlplus :1 -s @protect_rbs ' || segment_name || ' :2 :3 >
/dev/null & '
from sys.dba_rollback_segs
where status = 'ONLINE'
and segment_name != 'SYSTEM'
/
spool off
exit
Finally, I created this SQL file:
protect_rbs.sql
set transaction use rollback segment &1
/
insert into xxxx values ('&2','&1')
/
delete from xxxx where col1 = '&2' and col2 = '&1'
/
execute sys.dbms_lock.sleep(&3)
/
rollback
/
exit
Then to execute:
prevent_1555 dbname 3600
This would prevent 1555 errors for 1 hour.
What this does is fairly straight forward. First, we call the script with 2
parameters--the database name and the number of seconds we want to prevent
errors. We run sqlplus on the prevent_1555.sql file, again passing in these
2 parameters, plus the process ID, so I'll have a unique value if this is
run multiple times.
In the SQL we find all of the active, non-system rollback segements and
created a new script that will include one command for each active rollback
segment. Finally, script prevent_1555.sh is executed. This script kicks
executes SQL protect_rbs.sql for each of these rollback segments.
Protect_rbs.sql does all the real work. First we set the rollback segment
being used, and then execute a command that will cause a change to be placed
on that rollback segment. Finally, we sleep for the amount of time
specified. At the end, we rollback the changes and the rollback segment
will be freed. The scripts at the link above are more robust, but have
found this to be extremely helpful for me.
Thanks for your contribution to the Oracle community.
John
_______________________________________________________________________________________________
Best of luck,
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 160+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page