Oracle DB: Create new undo tablespace

If you have the following message:

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

Then the undo tablespace is possibly corrupted.

Try the following:

create undo tablespace UNDOTBS2 datafile 'c:\app\vag\oradata\ora11g\UNDOTBS02.DBF' size 100000;

alter system set UNDO_TABLESPACE=UNDOTBS2;

drop tablespace UNDOTBS1 including contents and datafiles;

alter database datafile 'c:\app\vag\oradata\ora11g\UNDOTBS02.DBF' autoextend on maxsize unlimited;

Useful queries:

select * from sys.dba_tablespaces

select * from sys.dba_data_files

Leave a Reply