[Oracle]UNDO表空间重建

$ id
uid=109(oracle) gid=106(oinstall) groups=107(dba)
$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Sat May 5 00:34:03 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/path/system01.dbf
/path/sysaux01.dbf
/path/undotbs01.dbf
/path/users01.dbf
……
15 rows selected.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
……
16 rows selected.
SQL> create undo tablespace undotbs2 datafile '/path/undotbs02.dbf' size 2048M;
Tablespace created.
SQL> host ls -l /path/undo*
-rw-r----- 1 oracle oinstall 21474844672 May 5 00:36 /path/undotbs01.dbf
-rw-r----- 1 oracle oinstall 2147491840 May 5 00:36 /path/undotbs02.dbf

SQL> alter system set undo_tablespace=undotbs2 scope=memory;
System altered.
SQL> select usn,xacts from v$rollstat;
USN XACTS
---------- ----------
0 0
20 0
21 0
22 0
23 0
24 0
25 0
26 0
27 0
28 0
29 0
11 rows selected.

SQL> drop tablespace undotbs1 including contents;
Tablespace dropped.
SQL> create undo tablespace undotbs1 datafile '/path/undotbs01.dbf' reuse;
Tablespace created.

SQL> alter system set undo_tablespace=undotbs1 scope=memory;
System altered.
SQL> select usn,xacts from v$rollstat;
USN XACTS
---------- ----------
0 0
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 rows selected.
SQL> drop tablespace undotbs2 including contents;
Tablespace dropped.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ cd /path/
$ ls -l undo*
-rw-r----- 1 oracle oinstall 21474844672 May 5 00:47 undotbs01.dbf
-rw-r----- 1 oracle oinstall 2147491840 May 5 00:48 undotbs02.dbf
$ rm undotbs02.dbf

转载于:https://www.cnblogs.com/MagicLetters/archive/2012/05/05/2484672.html