Oracle Error :: ORA-01552: cannot use system rollback segment for non-system tablespace ‘CRM’
Possible Reason and Action:
Undo tablespace is missing – Recreate the undo tablespace
Undo tablespace size is full – Create new undo tablespace and make it default
Undo tablespace datafile offline – Make it online
SQL> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;
TABLESPACE_NAME STATUS
———————————- ————-
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS ONLINE
TEMP ONLINE
CRM ONLINE
Check V$DATAFILE:
SQL> SELECT FILE#, STATUS FROM V$DATAFILE;
FILE# STATUS
——— ————
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
If any datafile is OFFLINE, bring it ONLINE. Eg:
SQL> ALTER DATABASE DATAFILE ‘D:CRMDATAUNDOTBS.DBF’ ONLINE;
Database altered.
SQL> commit;
Commit complete.
SQL> SHOW PARAMETER UNDO
NAME TYPE VALUE
——————————– ———– ——————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS
If undo_management parameter isn’t auto, set it to auto in parameter file and restart database.