Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01552 non-system tablespace
ORA-01552 non-system tablespace [message #641] Mon, 25 February 2002 10:19 Go to next message
Clayton Stordahl
Messages: 3
Registered: February 2002
Junior Member
I had this running in Oracle 8i Enterprise, but when I move up to Oracle 9i Enterprise, I get an ORA-01552: cannot use system rollback segment for non-system tablespace 'BIGROLL'

Here is the sql part and the Table BIGROLL exist's:

CREATE PUBLIC ROLLBACK SEGMENT BIGROLL
TABLESPACE BIGROLL
STORAGE(INITIAL 10M
NEXT 10M
MINEXTENTS 10
MAXEXTENTS 50
OPTIMAL NULL)
/
ALTER ROLLBACK SEGMENT BIGROLL ONLINE
/

I am logged in a sysdba also.
Could it be a permissions problem?

Please help, and thanks in advance.

-Clayton
Re: ORA-01552 non-system tablespace [message #657 is a reply to message #641] Tue, 26 February 2002 02:00 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
when u get this error ? how many rollback seg u have in system and non-system

have u changed the rollback_segment parameter in init.ora

ORA-01552 cannot use system rollback segment for non-system tablespace "
name"

Cause: An attempt was made to use the SYSTEM rollback segment for operations involving a non-system tablespace.
Action: Create one or more rollback segments and then use ALTER ROLLBACK SEGMENT 'name' online. It may be necessary to modify the initialization parameter ROLLBACK_SEGMENTS to acquire one of the new private rollback segments. Before creating a rollback segment outside the SYSTEM tablespace, it is necessary to first create and activate a non-system rollback segment in the SYSTEM tablespace.
Re: ORA-01552 non-system tablespace [message #672 is a reply to message #657] Tue, 26 February 2002 05:05 Go to previous messageGo to next message
Clayton Stordahl
Messages: 3
Registered: February 2002
Junior Member
I only have one rollback segment and that is for the system tablespace.

I did change the init.ora because when I first ran the script it gave me the error:

ORA-30019: Illegal rollback Segment operation in Automatic Undo mode

-Clayton
Re: ORA-01552 non-system tablespace [message #674 is a reply to message #641] Tue, 26 February 2002 05:34 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
Before creating a rollback segment outside the SYSTEM tablespace, it is necessary to first create and activate a non-system rollback segment in the SYSTEM tablespace.

so connect as system and create anoter rollback segment in system tablespace and make it online, then create other rollback segment
Re: ORA-01552 non-system tablespace [message #2220 is a reply to message #641] Fri, 28 June 2002 12:58 Go to previous messageGo to next message
kwalther
Messages: 9
Registered: April 2002
Junior Member
Don't forget that when you create your own rollback
segments, to have to bring them online after creating
them. Issue this command for a rollback segment named
rbs01..

alter rollback segment rbs01 online;

Pretty stupid, but thats what you have to do.

Re: ORA-01552 non-system tablespace [message #2256 is a reply to message #641] Tue, 02 July 2002 06:57 Go to previous messageGo to next message
Clayton Stordahl
Messages: 3
Registered: February 2002
Junior Member
Thanks,

Clayton
Re: ORA-01552 non-system tablespace [message #2331 is a reply to message #672] Mon, 08 July 2002 04:09 Go to previous message
Manish Bharucha
Messages: 2
Registered: July 2002
Junior Member
Oracle9i databases are capable of managing their own undo (rollback) segments. No longer will administrators need to carefully plan and tune the number and sizes of rollback segments or decide how to strategically assign transactions to a particular rollback segment. Oracle9i also allows administrators to allocate their undo space in a single undo tablespace with the database taking care of issues such as undo block contention, consistent read retention, and space utilization.

*************check this with your DB expert ----->

UNDO_SUPPRESS_ERRORS
Parameter type Boolean

Default value false

Parameter class Dynamic: ALTER SESSION, ALTER SYSTEM

Range of values true | false

UNDO_SUPPRESS_ERRORS enables users to suppress errors while executing manual undo management mode operations (for example, ALTER ROLLBACK SEGMENT ONLINE) in automatic undo management mode. Setting this parameter enables users to use the undo tablespace feature before all application programs and scripts are converted to automatic undo management mode. For example, if you have a tool that uses SET TRANSACTION USE ROLLBACK SEGMENT statement, you can add the statement "ALTER SESSION SET UNDO_SUPPRESS_ERRORS = true" to the tool to suppress the ORA-30019 error.

If you want to run in automatic undo management mode, ensure that your tools or applications are updated to run in automatic undo management mode.

We trust this resolution fixes

Previous Topic: loops
Next Topic: Simple Trigger functionality
Goto Forum:
  


Current Time: Fri Mar 29 01:52:06 CDT 2024