Oracle中的ORA-01548: active rollback segment '_SYSSMU1$' found

Oracle中的ORA-01548: active rollback segment '_SYSSMU1$' found 

接上文《Oracle Undo tablespace恢復（無備份）》

http://tiany.blog.51cto.com/513694/1431287

1、在創(chuàng)建新的undo tablesapce “undotbs2”后，刪除舊的undo tablespace

15:12:49 SYS@ prod>ALTER tablespace undotbs1 offline immediate;

Tablespace altered.

Elapsed: 00:00:00.15

報以下錯誤：

15:12:59 SYS@ prod>drop tablespace undotbs1 including contents and datafiles;

drop tablespace undotbs1 including contents and datafiles

*

ERROR at line 1:

ORA-01548: active rollback segment '_SYSSMU1_3780397527$' found, terminate dropping tablespace

Elapsed: 00:00:00.05

2、通過spfile生成pfile

15:13:08 SYS@ prod>create pfile from spfile;

File created.

Elapsed: 00:00:00.11

15:14:12 SYS@ prod>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

在initprod.ora 文件加入以下隱含參數(shù)：

_offline_rollback_segments=(_SYSSMU10_3550978943$,_SYSSMU9_1424341975$,_SYSSMU8_2012382730$,_SYSSMU7_3286610060$,_SYSSMU6_2443381498$,_SYSSMU5_1527469038$,_SYSSMU4_1152005954$,_SYSSMU3_2097677531$,_SYSSMU2_2232571081$,_SYSSMU1_3780397527$)

3、重新啟動database，并刪除舊的undo tablespace

[oracle@rh7 dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 26 16:32:49 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

16:32:49 SYS@ prod>create spfile from pfile;

File created.

Elapsed: 00:00:00.04

16:32:52 SYS@ prod>startup

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                  2217952 bytes

Variable Size             775948320 bytes

Database Buffers           54525952 bytes

Redo Buffers                2412544 bytes

Database mounted.

Database opened.

16:33:06 SYS@ prod>drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

Elapsed: 00:00:00.59

16:33:17 SYS@ prod>shutdown immediate；

在pfile 刪除_offline_rollback_segments參數(shù)；

16:33:56 SYS@ prod>create spfile from pfile;

File created.

Elapsed: 00:00:00.05

16:33:59 SYS@ prod>startup

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                  2217952 bytes

Variable Size             775948320 bytes

Database Buffers           54525952 bytes

Redo Buffers                2412544 bytes

Database mounted.

Database opened.

16:34:17 SYS@ prod>select count(*) from scott.emp;

  COUNT(*)

----------

        14

Elapsed: 00:00:00.05

16:34:20 SYS@ prod>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

SYSAUX                         ONLINE

TEMP                           ONLINE

USERS                          ONLINE

UNDOTBS2                       ONLINE

EXAMPLE                        ONLINE

TBS1                           ONLINE

7 rows selected.

Elapsed: 00:00:00.06

16:34:28 SYS@ prod>

@至此，undo tablespace 被正常刪除！