ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 重建undo tablespace

重建undo tablespace

原创 Linux操作系统 作者:jst143 时间:2011-04-07 09:38:59 0 删除 编辑

重建undo tablespace:

一。开启数据库至MOUNT状态
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup mount


二。查看undo信息
SQL> show parameter undo
undo_management                      string      AUTO
undo_retention                       integer     3600
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1

三。改为不使用undo,可以启动DB,但没法新建对象
SQL> alter system set undo_management=manual scope=spfile;
SQL> alter database open;

SQL> shutdown immediate
SQL> startup;

SQL> conn wacos/oss  
Connected.
SQL> select count(*) from tab;
      2034
SQL> create table t_test (c1 date);
create table t_test (c1 date)
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'WACOS'

四。再新建一个undo表空间,并弃用原先的:
SQL> create undo tablespace UNDOTBS2 datafile '/opt/oracle/db02/app/oracle/oradata/ORCL/undotbs02.dbf' size 3000m;

SQL> show parameter undo
undo_management                      string      MANUAL
undo_retention                       integer     3600
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1

五。切换undo表空间
SQL> alter system set undo_management=auto scope=spfile;
SQL> alter system set undo_tablespace=UNDOTBS2 scope=spfile;

六。重启数据库
SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> show user
USER is "WACOS"
SQL> conn /as sysdba
Connected.

SQL> shutdown immediate
SQL> startup

SQL> conn wacos/oss
Connected.
SQL> create table t_test (c1 date);
SQL>
SQL> drop tablespace undotbs1;
SQL> create table t_test2 (c1 date);
SQL> select * from t_test;
SQL> insert into t_test values (sysdate);
SQL> commit;
SQL> select * from t_test;
04-MAY-08

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23577591/viewspace-691827/,如需转载,请注明出处,否则将追究法律责任。

上一篇: undo表空间的切换
请登录后发表评论 登录
全部评论

注册时间:2010-05-06

  • 博文量
    109
  • 访问量
    138788