Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01555 INSERT INTO SELECT (Oracle 9i, AIX)
ORA-01555 INSERT INTO SELECT [message #421759] Thu, 10 September 2009 05:10 Go to next message
psoftneto
Messages: 44
Registered: July 2008
Member
Hi all,

I am having ORA-01555 and alert.log show it is caused by SQL statement INSERT INTO SELECT.

Should I add more datafiles to rollback tablespace ?
Should I modify the select statement (drop index, make some optimisation)?

Thanks
Re: ORA-01555 INSERT INTO SELECT [message #421766 is a reply to message #421759] Thu, 10 September 2009 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-1555 is one of the most asked question, there is nothing we can add that has not been said and repeated many times.
Please search before posting.

Regards
Michel
Re: ORA-01555 INSERT INTO SELECT [message #421782 is a reply to message #421759] Thu, 10 September 2009 08:29 Go to previous messageGo to next message
psoftneto
Messages: 44
Registered: July 2008
Member
Michel,

I thought the answer could be specific since I already have the sql statement causing ORA-01555 (the search have already been done before).
Re: ORA-01555 INSERT INTO SELECT [message #421784 is a reply to message #421782] Thu, 10 September 2009 08:50 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no specific reason for your statement, the general one applies.

Regards
Michel
Re: ORA-01555 INSERT INTO SELECT [message #421786 is a reply to message #421782] Thu, 10 September 2009 09:14 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The primary cause of the ORA-01555 isn't the piece of SQL that gets the error reported.
Somewhere in your system is (probably) a process that is updating a lot of data, and committing quite frequently. This causes lots of small changes to be stored in the Undo tablespace, in segments that aren't locked because the session that made the changes has committed and moved on.

The session that got the ORA-01555 will have needed to use one of these undo segments to reconstruct the original state of the database at the time it's query started, and has found that one of them has been overwritten.

You can make the problem less likely by increasing the size of the Undo tablespace, but really you need to fix your application code.

Previous Topic: select query failing
Next Topic: ORA-01722: invalid number
Goto Forum:
  


Current Time: Sat Sep 24 16:21:01 CDT 2016

Total time taken to generate the page: 0.09410 seconds