undo表空间和temp表空间切换维护步骤(二)

2014-11-24 18:13:34 · 作者: · 浏览: 1
----- ----------


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