38 0 ONLINE .000114441 .000114441 0
39 0 ONLINE .000114441 .000114441 0
40 0 ONLINE .000114441 .000114441 0
41 0 ONLINE .000114441 .000114441 0
42 0 ONLINE .000114441 .000114441 0
43 0 ONLINE .000114441 .000114441 0
0 0 ONLINE .000358582 .000358582 0
18 rows selected.
1.6删除原临时表空间同时删除数据文件
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
1.7重新创建原undo表空间
SQL> CREATE SMALLFILE UNDO TABLESPACE "undotbs1" DATAFILE
'/u01/app/oracle/oradata/lottery/undotbs01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 100M MAXSIZE 16G;
Tablespace created.
1.8查看当前undo表空间使用情况
SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2 from v$rollstat order by rssize;
USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------------- ---------------------- ----------
28 0 .000114441 .000114441 0
44 0 .000114441 .000114441 0
29 0 .000114441 .000114441 0
30 0 .000114441 .000114441 0
31 0 .000114441 .000114441 0
32 0 .000114441 .000114441 0
33 0 .000114441 .000114441 0
34 0 .000114441 .000114441 0
35 0 .000114441 .000114441 0
36 0 .000114441 .000114441 0
37 0 .000114441 .000114441 0
USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------------- ---------------------- ----------
38 0 .000114441 .000114441 0
39 0 .000114441 .000114441 0
40 0 .000114441 .000114441 0
41 0 .000114441 .000114441 0
42 0 .000114441 .000114441 0
43 0 .000114441 .000114441 0
0 0 .000358582 .000358582 0
18 rows selected.
1.9切换当前undo表空间为原undo表空间
SQL> alter system set undo_tablespace=undotbs1 scope=both;
System altered.
1.10查看当前undo表空间使用状态
SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2 from v$rollstat order by rssize;
USN XACTS STATUS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
1 0 ONLINE .000114441 .000114441 0
17 0 ONLINE .000114441 .000114441 0
2 0 ONLINE .000114441 .000114441 0
3 0 ONLINE .000114441 .000114441 0
4 0 ONLINE .000114441 .000114441 0
5 0 ONLINE .000114441 .000114441 0
6 0 ONLINE .000114441 .000114441 0
7 0 ONLINE .000114441 .000114441 0
8 0 ONLINE .000114441 .000114441 0
9 0 ONLINE .000114441 .000114441 0
10 0 ONLINE .000114441 .000114441 0
USN XACTS STATUS RSSIZE/1024/1024/1024 H