ORA-01552: cant use system rollback segment for non-system tablespace "Users"
sys@oradb> select name,status from v$datafile;
NAME STATUS
-------------------------------------------------------------------------------- -------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORADB\SYSTEM01.DBF SYSTEM
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORADB\UNDOTBS01.DBF OFFLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORADB\SYSAUX01.DBF ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORADB\USERS01.DBF ONLINE
sys@oradb> select segment_name, status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1$ NEEDS RECOVERY
_SYSSMU2$ NEEDS RECOVERY
_SYSSMU3$ NEEDS RECOVERY
_SYSSMU4$ NEEDS RECOVERY
_SYSSMU5$ NEEDS RECOVERY
_SYSSMU6$ NEEDS RECOVERY
_SYSSMU7$ NEEDS RECOVERY
_SYSSMU8$ NEEDS RECOVERY
_SYSSMU9$ NEEDS RECOVERY
_SYSSMU10$ NEEDS RECOVERY
11 rows selected.
sys@oradb> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------------------------------------
1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORADB\SYSTEM01.DBF
2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORADB\UNDOTBS01.DBF
3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORADB\SYSAUX01.DBF
4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORADB\USERS01.DBF
sys@oradb> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
sys@oradb> alter tablespace UNDOTBS1 online;
Tablespace altered.
sys@oradb> select name,status from v$datafile;
NAME STATUS
-------------------------------------------------------------------------------- ------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORADB\SYSTEM01.DBF SYSTEM
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORADB\UNDOTBS01.DBF ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORADB\SYSAUX01.DBF ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORADB\USERS01.DBF ONLINE
sys@oradb> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
sys@oradb> alter system set undo_management=auto scope=spfile;
sys@oradb> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@oradb> startup;
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1250380 bytes
Variable Size 230689716 bytes
Database Buffers 364904448 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
sys@oradb> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
