ORA-01552: 非系统表空间'EOMSV2'无法使用系统回退段。

怎么会使用系统回滚段呢？难道没有undo表空间吗？

SQL> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string MANUAL

undo_retention integer 900

undo_suppress_errors boolean FALSE

undo_tablespace string

可见，undo_tablespace没有指定任何可用的撤销表空间，因此决定创建undo表空间：

SQL> CREATE UNDO TABLESPACE undotbs1

2 DATAFILE '/opt/oracle/oradata/openview/undo01.dbf' SIZE 200M AUTOEXTEND OFF;

Tablespace created.

由于当前该库使用的是pfile，所以也打算改成spfile，这样管理起来也更方便些…

SQL> create spfile from pfile;

File created.

关闭数据库，然后使用spfile启动：

SQL>!lsnrctl stop
SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 79910172 bytes

Fixed Size 455964 bytes

Variable Size 62914560 bytes

Database Buffers 16384000 bytes

Redo Buffers 155648 bytes

Database mounted.

Database opened.

SQL> show parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string ?/dbs/spfile@.ora

SQL> alter system set undo_tablespace = UNDOTBS1 scope=spfile;

System altered.

SQL> alter system set undo_management= AUTO scope=spfile;

System altered.

再次重启DB以使修改的参数生效：

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !lsnrctl start

SQL> startup;

SQL> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_suppress_errors boolean FALSE

undo_tablespace string UNDOTBS1