切換UNDO(zt)

2. 查詢資料庫的UNDO表空間名

[oracle@dbdev dbs]$ strings spfileora9.ora | grep undo
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'

解決步驟：
1. 啟動SQLPLUS，並用sys登陸到資料庫。

[oracle@dbdev dbs]$ sqlplus "/ as sysdba"

3. 確認UNDO表空間；

SQL> col name format a40
SQL> select name from v$tablespace;

NAME
----------------------------------------
SYSTEM
UNDOTBS1
TEMP

HYC
HYCOMMONDATA
HYSADATA
HYFFDATA

33 rows selected.

4. 檢查資料庫UNDO表空間佔用空間情況以及資料檔案存放位置；

SQL> col FILE_NAME format a40
SQL> select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS1';

FILE_NAME BYTES/1024/1024
---------------------------------------- ---------------
/trsgfifs/oradata/UNDOTBS1.dbf 10000

5. 檢視回滾段的使用情況，哪個使用者正在使用回滾段的資源，如果有使用者最好更換時間（特別是生產環境）。

SQL> select s.username, u.name from v$transaction t,v$rollstat r,
2 v$rollname u,v$session s where s.taddr=t.addr and
3 t.xidusn=r.usn and r.usn=u.usn order by s.username;

no rows selected

6. 檢查UNDO Segment狀態；

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
1 0 .006950378 .026481628 78
3 0 .006950378 .051872253 48
5 0 .006950378 .078239441 58
6 0 .006950378 .030448914 52
9 0 .006950378 .070426941 59
10 0 .006950378 .022575378 63
8 0 .006950378 .059684753 51
2 0 .014762878 .021598816 57
4 0 .014762878 .022575378 67
7 0 .022575378 .022575378 59

11 rows selected.

SQL> desc v$tablespace
Name Null? Type
----------------------------------------- -------- ----------------------------
TS# NUMBER
NAME VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)

SQL> desc v$datafile

SQL> select file#,ts#,name from v$datafile;

FILE# TS# NAME
---------- ---------- ----------------------------------------
1 0 /rdb/oradata/ora9/system01.dbf
2 1 /rdb/oradata/ora9/undotbs01.dbf
3 4 /trsg/oradata/tr_data01.dbf
4 5 /trsg/oradata/tr_index01.dbf
5 17 /trsg/oradata/hycdhindex_data01.dbf
6 7 /trsg/oradata/tr_roll.dbf
7 8 /trsg/oradata/hyc_data01.dbf
36 rows selected.

7. 建立新的UNDO表空間，並設定自動擴充套件引數；

SQL> create undo tablespace undotbs2 datafile '/trsgfifs/oradata/UNDOTBS2.dbf' size 1000m reuse autoextend on next 100m maxsize unlimited;

Tablespace created.

8. 動態更改spfile配置檔案；

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

System altered.

9. 等待原UNDO表空間所有UNDO SEGMENT OFFLINE；

SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;

11 rows selected.

10. 再執行看UNDO表空間所有UNDO SEGMENT ONLINE；

SQL> /

11 rows selected.

SQL> shutdown immediate;

SQL> startup

11. 刪除原有的UNDO表空間；

SQL> drop tablespace undotbs1 including contents;

Tablespace dropped.

12. 確認刪除是否成功；

SQL> select name from v$tablespace;

NAME
----------------------------------------
JIRA_DATA
PERFSTAT
COGNOS_DATA
COGNOS_INDEX
TP_ITTEST_DATA
TP_ITTEST_INDEX
CUSTDEV_DATA
CUSTDEV_INDEX
UNDOTBS2

33 rows selected.

SQL> exit

13. 在做此步驟前，請到$ORACLE_HOME/dbs/init$ORACLE_SID.ora如下內容是否發生變更：

[oracle@dbdev dbs]$ strings spfileora9.ora | grep undo
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS2'

14. 冊除原UNDO表空間的資料檔案，其檔名為步驟中執行的結果。

[oracle@dbdev dbs]$ rm /rdb/oradata/ora9/undotbs01.dbf
[oracle@dbdev dbs]$