OraFAQ Forum: Server Administration » ORA-01552 and ORA-01092 (merged 4 threads)

Home » RDBMS Server » Server Administration » ORA-01552 and ORA-01092 (merged 4 threads)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01552 and ORA-01092 (merged 4 threads) [message #137080]

Tue, 13 September 2005 02:24

aania_neo
Messages: 6
Registered: September 2005
Location: Delhi

Junior Member

I am creating a new DB schema. I created new tablespaces and used them for creation of DB. The sequences are created fine..but it gives me an error in Table creation :
ora-01552 : cannot use system rollback segment for non-system tablespace.

Report message to a moderator

ORA-01552 [message #137084 is a reply to message #137080]

Tue, 13 September 2005 03:04

aania_neo
Messages: 6
Registered: September 2005
Location: Delhi

Junior Member

Hi,
I am creating a new DB schema.
I created new tablespaces and used them for the creation.
The sequences were created fine..however, during the creation of tables, it gives me the error :

ORA-01552 : cannot use system rollback segment for non-system tablespace.

I even tried creating rollback segments using the following script:

SQL> create public rollback segment rb01 storage (initial 10 M minextents 10 next 10 M) tablespace rbs ;

It still gave the same error :

ORA-01552 : cannot use system rollback segment for non-system tablespace RBS.

Plz help.

Swati

[Updated on: Tue, 13 September 2005 04:14]

Report message to a moderator

ORA-01092: ORACLE instance terminated. Disconnection forced [message #137099 is a reply to message #137080]

Tue, 13 September 2005 04:35

aania_neo
Messages: 6
Registered: September 2005
Location: Delhi

Junior Member

I am trying to start my Database ,the following has occured:

SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup;
ORACLE instance started.

Total System Global Area 638653016 bytes
Fixed Size 451160 bytes
Variable Size 318767104 bytes
Database Buffers 318767104 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

I have already rebooted my system and still this error is coming.
Kindly advise on what I should do next.

Swati

Report message to a moderator

ORA-01092: ORACLE instance terminated. Disconnection forced [message #137100 is a reply to message #137080]

Tue, 13 September 2005 04:37

aania_neo
Messages: 6
Registered: September 2005
Location: Delhi

Junior Member

Hi,

I am trying to start my Database ,the following has occured:

SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup;
ORACLE instance started.

Total System Global Area 638653016 bytes
Fixed Size 451160 bytes
Variable Size 318767104 bytes
Database Buffers 318767104 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

I have already rebooted my system and still this error is coming.
Kindly advise on what I should do next.

Swati

Report message to a moderator

Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #137105 is a reply to message #137100]

Tue, 13 September 2005 05:15

deepa_balu
Messages: 74
Registered: March 2005

Member

Hi,

Please check the alert log for the additional Errors reported.
It will give you what exactly caused the instance to terminate

Report message to a moderator

Re: ORA-01552 [message #137109 is a reply to message #137084]

Tue, 13 September 2005 05:22

deepa_balu
Messages: 74
Registered: March 2005

Member

Check the output of the query

select segment_name, tablespace_name, initial_extent,status from dba_rollback_segs;

If any of the tablespace is offline make it online.

Suppose if the database is set in Automatic Undo Management (AUM) mode - it must have at least one UNDO tablespace.

To resolve the error create an UNDO tablespace to automatically create UNDO
segments set online immediately.

SQL> create undo tablespace UNDO1
2 datafile '/ora/ora901/oradata/V901/undo01.dbf' size 10M;
Tablespace created.

SQL> create table test (c number) tablespace users;
Table created.

SQL> select segment_name, tablespace_name, initial_extent,status
2 from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAM INITIAL_EXTENT STATUS
-------------- -------------- -------------- ----------------
SYSTEM SYSTEM 57344 ONLINE
_SYSSMU1$ UNDO1 131072 ONLINE
_SYSSMU2$ UNDO1 131072 ONLINE
_SYSSMU3$ UNDO1 131072 ONLINE
_SYSSMU4$ UNDO1 131072 ONLINE
_SYSSMU5$ UNDO1 131072 ONLINE
_SYSSMU6$ UNDO1 131072 ONLINE
_SYSSMU7$ UNDO1 131072 ONLINE
_SYSSMU8$ UNDO1 131072 ONLINE
RB1 SYSTEM 16384 OFFLINE

Report message to a moderator