Latest:

Making Technology Bulletproof

Oracle

Error During Maintenance Work in 10.2.0.4.0 + Rollback Segment 0 with Name “SYSTEM” too Small

Kellyn

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 11.2.0.3.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 11.2.0.3.0 and upon checking, this patch is in 11.2.0.3.0 for all OS versions I searched, so this means that this issue could arise in any 11.2.0.3.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.

 

 

 

Kellyn

http://about.me/dbakevlar

You May Also Like

SQLTXPLAIN and the AWR Warehouse- Part II

Kellyn

When RMAN Cloning Goes Bad…

Kellyn Gorman

Re-Adding a Database to the AWR Warehouse Without Losing Historical Snapshots

Kellyn

2 thoughts on “Error During Maintenance Work in 10.2.0.4.0 + Rollback Segment 0 with Name “SYSTEM” too Small”

  • One slight ‘niggle’ with the above code (and it’s happened to me as well) — the spaces in the ‘set’ statement throw errors and the missing ‘;’ on the first ‘alter system’ statement prevent the table from creating and populating:

    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

    This works flawlessly.

  • Pingback: AWR Warehouse in EM12c, Rel 4, Part II - Oracle - Oracle - Toad World

Comments are closed.