Home » SQL & PL/SQL » SQL & PL/SQL » ora-1555 error
ora-1555 error [message #202852] Mon, 13 November 2006 01:15 Go to next message
soumya_nayak
Messages: 2
Registered: November 2006
Location: India
Junior Member
Hi

I am getting ORA-1555 Error from the following query.What can be the reason and how to avoid this?

procedure abc
as
l_n pls_integer := 2 ;
begin
execute immediate 'insert /*+ append */ into my_table
select /*+ parallel */ b.my_id'||table1_fields||
' from table1 a, table2 b
where
phone_number in
( select distinct phone_number from this_table where alarm_id in
(select id from my_temp) and type = Mad )
and a.id = b.my_id' using l_n ;
end abc;

Here x is a bind variable.
-Thanks

[Updated on: Mon, 13 November 2006 01:16]

Report message to a moderator

Re: ora-1555 error [message #202862 is a reply to message #202852] Mon, 13 November 2006 01:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Tom Kyte covers this topic very well.

The solutions are basically -
1) Use less commits in your system
2) Have more and bigger rollback segments.

Don't forget - it's not just this query that has causes the Ora-1555, it's all the rest of the code running in the system.
Re: ora-1555 error [message #202873 is a reply to message #202862] Mon, 13 November 2006 02:28 Go to previous messageGo to next message
soumya_nayak
Messages: 2
Registered: November 2006
Location: India
Junior Member
Thanks for the suggestions.

But I am using very less commits in my code.

And as far as extending the rollback segment is concerned I cann't do much on that issue.Bcoz that space is restricted.

Any other suggestions in this regard pls...
Re: ora-1555 error [message #202874 is a reply to message #202873] Mon, 13 November 2006 02:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's it really. You could try rewriting your code so that it handles the insert in several pieces (eg four passes, each doing 1/4 of the rows).

Are you sure that there isn't something in the system that's running through a loop, doing a commit each time - they're quire a common find in systems that get a lot of ORA-01555 errors.
Re: ora-1555 error [message #203037 is a reply to message #202852] Mon, 13 November 2006 12:02 Go to previous messageGo to next message
artmast
Messages: 11
Registered: October 2006
Junior Member
You may be able to change a setting. Oratip http://oratip.com/ORA-01555.html says you can try the following action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.
Art.
Re: ora-1555 error [message #203077 is a reply to message #203037] Mon, 13 November 2006 16:09 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
I'm really not sure (and a bit shy amongst all those specialists already in this topic), but one suggestion:

you use the hint /*+parallel*/. As far as I know, parallel should always include a table alias and a number of parallel processes to use, so this hint won't influence the optimizer(check explain plan to make sure).

But, one specific habit of parallel is that it uses direct path reads and doesn't use the buffer cache. Instead, it "orders" to have all "related outstanding" transactions written to the database before starting getting the data (using direct path, as mentioned).

In theory what could be happening here is that (although the syntax isn't right) the direct path is still applied. If there are a lot of transactions on the tables used, which all have to be written beforehand, then that might be causing the ORA-01555.

Maybe I'm completely and utterly wrong here, but you could try removing the parallel hint and see what happens.
Previous Topic: ORA-25000 invalid use of bind variable in trigger WHEN clause
Next Topic: Query a view
Goto Forum:
  


Current Time: Mon Dec 05 04:40:20 CST 2016

Total time taken to generate the page: 0.08208 seconds