ORA-01552: cannot use system rollback segment for... error resolved
Fault symptom:
Today, the database is not completely restored, but several tables need to be created as the base table for recovery when preparing the environment. When creating a table
The following error is reported:
13:56:03 SQL> create table wwl001 (id number, name varchar (12 ));
Create table wwl001 (id number, name varchar (12 ))
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'wwl ';
!!!! System rollback segments cannot be used for non-system tablespace 'wwl'
Cause:
I performed a database disaster recovery yesterday due to disk damage. It may be because the parameter file was modified during the recovery, which leads to rollback segments.
To change to the manual management mode, take a look at the following steps:
Details:
1. Create a table. Non-system tablespace cannot use rollback segments.
13:56:03 SQL> create table wwl001 (id number, name varchar (12 ));
Create table wwl001 (id number, name varchar (12 ))
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'wwl ';
2. view the rollback segment management method, which is manual management.
13:57:24 SQL> show parameter undo
NAME TYPE VALUE
-----------------------------------------------------------------------------
Undo_management string MANUAL
Undo_retention integer 900
Undo_tablespace string UNDOTBS1
3. view the rollback segment status
13:57:23 SQL> select segment_name, tablespace_name, status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
----------------------------------------------------------------------------
SYSTEM ONLINE
_ SYSSMU1 $ UNDOTBS1 OFFLINE
_ SYSSMU2 $ UNDOTBS1 OFFLINE
_ SYSSMU3 $ UNDOTBS1 OFFLINE
_ SYSSMU4 $ UNDOTBS1 OFFLINE
_ SYSSMU5 $ UNDOTBS1 OFFLINE
_ SYSSMU6 $ UNDOTBS1 OFFLINE
_ SYSSMU7 $ UNDOTBS1 OFFLINE
_ SYSSMU8 $ UNDOTBS1 OFFLINE
_ SYSSMU9 $ UNDOTBS1 OFFLINE
_ SYSSMU10 $ UNDOTBS1 OFFLINE
11 rows selected.
4. Modify the undo_management parameter to auto.
14:08:06 SQL> alter system set undo_management = auto scope = spfile;
System altered.
14:17:51 SQL> shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.
14:18:22 SQL> startup
ORACLE instance started.
Total System Global Area 100663296 bytes
Fixed Size 1217884 bytes
Variable Size 88083108 bytes
Database Buffers 8388608 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
5. view the rollback segment status
14:18:55 SQL> select segment_name, tablespace_name, status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
----------------------------------------------------------------------------
SYSTEM ONLINE
_ SYSSMU1 $ UNDOTBS1 ONLINE
_ SYSSMU2 $ UNDOTBS1 ONLINE
_ SYSSMU3 $ UNDOTBS1 ONLINE
_ SYSSMU4 $ UNDOTBS1 ONLINE
_ SYSSMU5 $ UNDOTBS1 ONLINE
_ SYSSMU6 $ UNDOTBS1 ONLINE
_ SYSSMU7 $ UNDOTBS1 ONLINE
_ SYSSMU8 $ UNDOTBS1 ONLINE
_ SYSSMU9 $ UNDOTBS1 ONLINE
_ SYSSMU10 $ UNDOTBS1 ONLINE
11 rows selected.
6. Execute the table creation statement just now. You can create a table.
14:19:51 SQL> create table wwl001 (id number, name varchar (12 ));
Table created.
For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12