UNDO与TEMP表空间的切换是比较常见的操作,一般发生在不小心将表空间设成了Autoextend on 后,导致表空间爆涨,而不得不新建表空间,再将默认UNDO与TEMP表空间切换到新建的表空间上,最后DROP原UNDO与TEMP表空间。
[@more@]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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13171581/viewspace-1007353/