While cleaning up a development environment the other day, attempted to drop a tablespace and received the error ORA-01555, Rollback Segment 0 with Name “system” too Small…
A quick search on google pointed me to the MOS doc 862469.1 which results in both ORA-604 and ORA-1555 on the SYSTEM RBS and assures the reader that this
is not a code bug in Oracle, but rather is due to the system rollback segment being too small.
Due to a fix for bug 7313166, it is now possible for data dictionary transaction binding to generate much larger amounts of undo than before. As there is not undo retention policy on the System undo RBS and rarely is very large to begin with, the DBA can encounter an ORA-1555, specifically for any large dictionary queries.
What I found most interesting, is that this can apply to version 10.2.0.4 and above, yet I was on 220.127.116.11.0 and it still hasn’t been resolved by default to deter it from occurring.
The change stated in the doc was that the patch for bug 7313166 was applied, yet this is a clean install of 18.104.22.168.0 and upon checking, this patch is in 22.214.171.124.0 for all OS versions I searched, so this means that this issue could arise in any 126.96.36.199.0 database.
The fix is simple, but does require an outage, (yes, we all love explaining that to our boss/users/clients… :))
As I can’t see anyone still using dictionary managed tablespaces, I’ll just post the fix for the locally managed System tablespace, but thought it was interesting to simply “force” it to extend and not have this in place before the release went out:
connect / as sysdba alter system set undo_management = MANUAL scope=spfile shutdown immediate startup create table test (t1 number, t2 varchar2(10)); begin for i in 1.. 300000 loop insert into test values (i,'AAAAAAAAAA'); end loop; end; / delete test; commit; alter system set undo_management = AUTO scope=spfile; shutdown immediate startup
Post this, the issue is resolved and large queries against the data dictionary can be performed successfully.