ORA-01552 non-system tablespace cannot be processed using system rollback segments

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.