undo表空间和temp表空间切换维护步骤
undo表空间和temp表空间切换维护步骤
大家好！
今天把原来整理的日常维护脚本重新测试整理下，分享给大家。
正好今天有个测试需要清理，发现找资料很麻烦，以后还是发博客比较靠谱！
话不多说，下面是具体步骤，请广大朋友指正：
1 切换undo表空间
1.1查看undo表空间位置及使用大小
SQL> col FILE_NAME for a60
SQL> 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 10M
AUTOEXTEND 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 200
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
---------- ---------- --------------- --------------------- ---------------------- ----------
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 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 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.
|
评论暂时关闭