Oracle中的ORA-01548:activerollbacksegment'_SYSSMU1$'found

Oracle中的ORA-01548: active rollback segment

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

接上文《Oracle Undo tablespace恢复（无备份）》

RMAN备份与恢复之undo表空间丢失

关于Oracle 释放过度使用的undo表空间

Oracle undo的一些理解

Oracle undo 镜像数据探究

Oracle 回滚(ROLLBACK)和撤销(undo)

1、在创建新的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 文件加入以下隐含参数：

_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@rh6 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参数；

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 被正常删除！

本文永久更新链接地址：