Re: ORA-01552 System Rollback Question

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/08/01
Message-ID: <33E187CB.43D9@postoffice.worldnet.att.net>#1/1

This is an interesting problem. It happened to me when I increased the db_block_size from 2K to 8K for my database and had to rebuild it. I changed the db_block_size value in the initorcl.ora file and used the build.sql (or buildall.sql) script that the installer uses to create the starter database. Everything seemed to work fine, but when I tried to use the demo and SCOTT/TIGER tables they were not there (or were empty, I don't recall). I checked the build*.log and noticed that the creation of the additional rollback segment in the SYSTEM tablespace failed. When the build.sql script got to the part where it runs the demobld.sql and scott.sql scripts it failed with ORA-01552 on CREATE TABLE or INSERT VALUES statements. What it boils down to, is that you have only the system rollback segment available, hence the error message.

I figured out that the creation of the second rollback segment in the SYSTEM tablespace failed because its INITIAL extent had to be at least 5 ORACLE blocks. In the build*.sql script, there are no STORAGE parameters specified for this rollback segment, so it takes the DEFAULT STORAGE values from the SYSTEM tablespace. This happens to be 16K for INITIAL, I believe, which is 8 blocks when block size is 2K, but only 2 blocks when block size is 8K.

Interesting to note that, contrary to the ORACLE documentation which states that you need another rollback segment in addition to the system rollback segment in order to be able to create any non-SYSTEM tablespace, build*.sql script was able to create the other tablespaces; it failed, however, when it tried to do any operation in those non-SYSTEM tablespaces.

Please confirm that this is true in your case by checking what rollback segments you have and if they are online, and sorry for the long-winded explanation. Also check the build*.log file, if you still have it available.

Hope this helps.

Michael Serbanescu

---

Jonathan Atkinson wrote:
>
> I'm moving from Oracle 7.1.6 on AIX to 7.3 on windows NT. I have run
> identical scripts on both versions to create the database and schema and
> these all run OK. However, when I attempt a user txn on the 7.3 database I
> get an "ORA-01552 cannot use system rollback segment for non-system
> tablespace".
>
> Any ideas?
>
> Jonathan A.
> London, UK
Received on Fri Aug 01 1997 - 00:00:00 CDT