原创文章,转载请注明出处!
作者:Ligle
地址:http://liglexiner.itpub.net/post/43018/517484
###################################################
DB Version:9.2.0.6
今天上午有工作人员在执行一些update语句是报如下错误:
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
至此,调整完毕,再次执行DML语句就没有任何问题了[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25748672/viewspace-1049535/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25748672/viewspace-1049535/