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

大家好！今天把原来整理的日常维护脚本重新测试整理下，分享给大家。正好今天有个测试需要清理，发现找资料很麻烦，以后还是发博客比较靠谱！话不多说，下面是具体步骤，请广大朋友指正：1 切换undo表空间1.1查看undo表空间位置及使用大小SQL> col FILE_NAME for a60SQL> select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like "UNDOTBS1"; FILE_NAME BYTES/1024/1024------------------------------------------------------------ ---------------/u01/app/Oracle/oradata/lottery/undotbs01.dbf 2048
注：1.2查询回滚段使用状态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---------- ---------- --------------------- ---------------------- ---------- 0 0 .000358582 .000358582 0 25 0 .049797058 .049797058 15 22 0 .050773621 .050773621 0 19 0 .051750183 .051750183 61 23 0 .060539246 .060539246 0 13 0 .061515808 .061515808 0 15 0 .078117371 .078117371 0 12 0 .078117371 .078117371 0 10 0 .078422546 .078422546 0 4 0 .080070496 .080070496 0 1 0 .086112976 .086112976 71 USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS---------- ---------- --------------------- ---------------------- ---------- 7 0 .086845398 .086845398 0 11 0 .093742371 .093742371 0 5 0 .101554871 .101554871 0 3 0 .101554871 .101554871 0 9 0 .101554871 .101554871 0 8 0 .101676941 .101676941 0 6 0 .101676941 .101676941 0 2 0 .101860046 .101860046 0 19 rows selected. 1.3创建新的undo表空间SQL> CREATE SMALLFILE UNDO TABLESPACE "UNDOTBS02" DATAFILE"/u01/app/oracle/oradata/lottery/undotbs21.dbf" SIZE 10MAUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED; Tablespace created. 1.4使用新的undo表空间SQL> alter system set undo_tablespace=UNDOTBS02 scope=both; System altered. 1.5查看原表空间使用情况等带SHRINKS全部为0时删除原undo表空间SQL> set line 200SQL> 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---------- ---------- --------------- --------------------- ---------------------- ---------- 28 0 ONLINE .000114441 .000114441 0 44 0 ONLINE .000114441 .000114441 0 29 0 ONLINE .000114441 .000114441 0 30 0 ONLINE .000114441 .000114441 0 31 0 ONLINE .000114441 .000114441 0 32 0 ONLINE .000114441 .000114441 0 33 0 ONLINE .000114441 .000114441 0 34 0 ONLINE .000114441 .000114441 0 35 0 ONLINE .000114441 .000114441 0 36 0 ONLINE .000114441 .000114441 0 37 0 ONLINE .000114441 .000114441 0 USN XACTS STATUS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS---------- ---------- --------------- --------------------- ---------------------- ---------- 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 10MAUTOEXTEND 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 HWMSIZE/1024/1024/1024 SHRINKS---------- ---------- --------------- --------------------- ---------------------- ---------- 11 0 ONLINE .000114441 .000114441 0 12 0 ONLINE .000114441 .000114441 0 13 0 ONLINE .000114441 .000114441 0 14 0 ONLINE .000114441 .000114441 0 15 0 ONLINE .000114441 .000114441 0 16 0 ONLINE .000114441 .000114441 0 0 0 ONLINE .000358582 .000358582 0 18 rows selected. 1.11删除undo2表空间SQL> drop tablespace UNDOTBS02 including contents and datafiles; Tablespace dropped.

慎用alter table move在Debian 上安装 Oracle 11gR2相关资讯 undo undo表空间 temp表空间

Oracle 11g undo_retention 以及（05月28日）
如何删除回滚段状态为NEEDS （09/02/2015 19:28:49）
undo表空间修复小结（07/08/2015 08:43:13）

Oracle中利用undo进行数据的恢复操（11/27/2015 09:31:30）
undo表空间使用率（07/23/2015 16:29:56）
undo表空间概述（02/24/2015 20:32:43）

本文评论查看全部评论（0）