ORA-01552

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.

Leave a Reply

Your email address will not be published. Required fields are marked *