UNDO、TEMP表空间的切换

UNDO与TEMP表空间的切换是比较常见的操作，一般发生在不小心将表空间设成了Autoextend on 后，导致表空间爆涨，而不得不新建表空间，再将默认UNDO与TEMP表空间切换到新建的表空间上，最后DROP原UNDO与TEMP表空间。

1、UNDO表空间的切换

SQL> create undo tablespace undotbs2 datafile
2 'D:oracleoradataskyundotbs02.dbf' SIZE 40m autoextend off;

Tablespace created.

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1

SQL> alter system set undo_tablespace=UNDOTBS2 scope=both;

System altered.

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS2

SQL> select segment_name ,tablespace_name ,segment_id from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID
------------------------------ ------------------------------ ----------
SYSTEM SYSTEM 0
_SYSSMU1$ UNDOTBS1 1
_SYSSMU2$ UNDOTBS1 2
_SYSSMU3$ UNDOTBS1 3
_SYSSMU4$ UNDOTBS1 4
_SYSSMU5$ UNDOTBS1 5
_SYSSMU6$ UNDOTBS1 6
_SYSSMU7$ UNDOTBS1 7
_SYSSMU8$ UNDOTBS1 8
_SYSSMU9$ UNDOTBS1 9
_SYSSMU10$ UNDOTBS1 10

SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID
------------------------------ ------------------------------ ----------
_SYSSMU11$ UNDOTBS2 11
_SYSSMU12$ UNDOTBS2 12
_SYSSMU13$ UNDOTBS2 13
_SYSSMU14$ UNDOTBS2 14
_SYSSMU15$ UNDOTBS2 15
_SYSSMU16$ UNDOTBS2 16
_SYSSMU17$ UNDOTBS2 17
_SYSSMU18$ UNDOTBS2 18
_SYSSMU19$ UNDOTBS2 19
_SYSSMU20$ UNDOTBS2 20

21 rows selected.

SQL> select usn,status,xacts from v$rollstat;

USN STATUS XACTS
---------- --------------- ----------
0 ONLINE 0
10 PENDING OFFLINE 1
11 ONLINE 0
12 ONLINE 0
13 ONLINE 0
14 ONLINE 0
15 ONLINE 0
16 ONLINE 0
17 ONLINE 0
18 ONLINE 0
19 ONLINE 1

USN STATUS XACTS
---------- --------------- ----------
20 ONLINE 0

12 rows selected.

发现原UNDOTBS1的回滚段处于PENDING OFFLINE状态，并有一个事物存在，需要等到事务完成后，才能drop UNDOTBS1

SQL> select usn,status,xacts from v$rollstat;

USN STATUS XACTS
---------- --------------- ----------
0 ONLINE 0
10 PENDING OFFLINE 0 --事务结束
11 ONLINE 0
12 ONLINE 0
13 ONLINE 0
14 ONLINE 0
15 ONLINE 0
16 ONLINE 0
17 ONLINE 0
18 ONLINE 0
19 ONLINE 1

USN STATUS XACTS
---------- --------------- ----------
20 ONLINE 0

12 rows selected.

SQL> select usn,status,xacts from v$rollstat;

USN STATUS XACTS
---------- --------------- ----------
0 ONLINE 0
11 ONLINE 0
12 ONLINE 0
13 ONLINE 0
14 ONLINE 0
15 ONLINE 0
16 ONLINE 0
17 ONLINE 0
18 ONLINE 0
19 ONLINE 1
20 ONLINE 0

11 rows selected.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL>

2、TEMP表空间的切换

SQL> select username,temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS TEMP01
SYSTEM TEMP01
DBSNMP TEMP01
SQLTXPLAIN TEMP01
PERFSTAT TEMP01
OUTLN TEMP01
WMSYS TEMP01

7 rows selected.

SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
> 'D:ORACLEORADATASKYTEMP01.DBF' SIZE 41943040 autoextned off

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> select username,temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS TEMP
SYSTEM TEMP
DBSNMP TEMP
SQLTXPLAIN TEMP
PERFSTAT TEMP
OUTLN TEMP
WMSYS TEMP

7 rows selected.

SQL> drop tablespace temp01 including contents and datafiles;

Tablespace dropped.

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13171581/viewspace-1007353/，如需转载，请注明出处，否则将追究法律责任。