Home > Oracle Errors > ORA-01552

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:\CRM\DATA\UNDOTBS.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.

Advertisements
Categories: Oracle Errors Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: