ブログ - UNDO領域のメンテナンス ORA-03297もでたし
Oracle 9i以降では，トランザクションログがUNDO表領域という所に保存されるのだけれど，Oracle 11g XEだとUNDO領域は自動拡張autoextentするので，ほっといたら容量が大変大きくなっていたりした．
今回は，それを小さくする手順を確認する．
現在の状態を確認
表領域用のファイルとファイルサイズを確認．
200MBの領域が確保されている．そのうちの，実際のデータ使用量を確認．
表領域のファイルがファイルが200MBで利用量が39MBとなっていることが確認できた．
UNDO表領域を小さくする
200MB中の40MB弱程度しか使ってないので，UNDO表領域を50MBに変更する．
エラーORA-03297がでた．これは「ファイルに、要求されたRESIZE値を超える使用済みデータが含まれています」というエラー．今回の場合、200MBの表領域の中で、データが散らばって保管されているので、100MBにできない模様．
表領域を切り替える
断片化したUNDO表領域を小さくすることができなかったので、UNDO表領域を新たに作成して、切り替える．
UNDO領域を確認する．
現在のUNDO表領域はUNDOTBS1という名前だとわかる．新しい表領域を作成する．
UNDOTBS2という名前で新しく作成した．
新しいUNDO表領域に切り替える．
切り替わったか確認する．
切り替わりました．
不要となったUNDO表領域を削除する
表領域のファイルを確認．
UNDO表領域を削除する．
cascadeすることでファイルも消える．
表領域のファイルを確認．
切り替え後の状態を確認．
これで、古い表領域を消して、新しい小さな表領域に切り替わった．
自動拡張を停止する
長期間のUNDO領域を必要としてない場合、拡張されない様にしておきたい．
現在の設定を確認する．
autoextensibleがNOになっているので、自動拡張されない．
自動拡張を有効にする場合は、次のようにAUTOEXTENDをONにする．
UNDO表領域の解放
今回は，それを小さくする手順を確認する．
現在の状態を確認
表領域用のファイルとファイルサイズを確認．
SQL> col name for a50🆑
SQL> select name,bytes / 1024 / 1024 as SIZE_MB from v$datafile order by name;🆑
NAME SIZE_MB
-------------------------------------------------- ----------
/u01/app/oracle/oradata/XE/app_idx_tbs.dbf 10
/u01/app/oracle/oradata/XE/app_tbs.dbf 8204.375
/u01/app/oracle/oradata/XE/sysaux.dbf 770
/u01/app/oracle/oradata/XE/system.dbf 370
/u01/app/oracle/oradata/XE/undotbs1.dbf 200🈁
/u01/app/oracle/oradata/XE/users.dbf 100
7 rows selected.
SQL>
SQL> col tablespace_name for a15🆑
SQL> col MB format 9999.99🆑
SQL> select tablespace_name,sum(bytes)/1024/1024 as MB from dba_segments group by tablespace_name🆑
2 order by tablespace_name;🆑
TABLESPACE_NAME MB
--------------- --------
app_TBS 7811.88
SYSAUX 721.13
SYSTEM 366.94
UNDOTBS1 39.56🈁
USERS 1.56
6 rows selected.
SQL>
UNDO表領域を小さくする
200MB中の40MB弱程度しか使ってないので，UNDO表領域を50MBに変更する．
SQL> alter database datafile '/u01/app/oracle/oradata/XE/undotbs1.dbf' resize 50m;🆑
alter database datafile '/u01/app/oracle/oradata/XE/undotbs1.dbf' resize 50m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value🈁
SQL>
表領域を切り替える
断片化したUNDO表領域を小さくすることができなかったので、UNDO表領域を新たに作成して、切り替える．
UNDO領域を確認する．
SQL> show parameter undo_🆑
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1🈁
SQL>
SQL> create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/XE/undotbs2.dbf' size 50M;🆑
Tablespace created.
SQL>
新しいUNDO表領域に切り替える．
SQL> alter system set undo_tablespace = 'UNDOTBS2';🆑
System altered.
SQL>
SQL> show parameter undo;🆑
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2🈁
SQL>
不要となったUNDO表領域を削除する
表領域のファイルを確認．
SQL> col name for a50🆑
SQL> select name,bytes / 1024 / 1024 as SIZE_MB from v$datafile order by name;🆑
NAME SIZE_MB
-------------------------------------------------- ----------
/u01/app/oracle/oradata/XE/app_idx_tbs.dbf 10
/u01/app/oracle/oradata/XE/app_tbs.dbf 8204.375
/u01/app/oracle/oradata/XE/sysaux.dbf 770
/u01/app/oracle/oradata/XE/system.dbf 370
/u01/app/oracle/oradata/XE/undotbs1.dbf 200🈁
/u01/app/oracle/oradata/XE/undotbs2.dbf 50
/u01/app/oracle/oradata/XE/users.dbf 100
8 rows selected.
SQL>
SQL> drop tablespace undotbs1 including contents and datafiles cascade constraints;🆑
Tablespace dropped.
SQL>
表領域のファイルを確認．
SQL> select name,bytes / 1024 / 1024 as SIZE_MB from v$datafile order by name;🆑
NAME SIZE_MB
-------------------------------------------------- ----------
/u01/app/oracle/oradata/XE/app_idx_tbs.dbf 10
/u01/app/oracle/oradata/XE/app_tbs.dbf 8204.375
/u01/app/oracle/oradata/XE/sysaux.dbf 770
/u01/app/oracle/oradata/XE/system.dbf 370
/u01/app/oracle/oradata/XE/undotbs2.dbf 50🈁
/u01/app/oracle/oradata/XE/users.dbf 100
6 rows selected.
SQL>
SQL> select tablespace_name,sum(bytes)/1024/1024 as MB from dba_segments group by tablespace_name🆑
2 order by tablespace_name;🆑
TABLESPACE_NAME MB
--------------- --------
app_TBS 7811.88
SYSAUX 721.19
SYSTEM 366.94
UNDOTBS2 1.25🈁
USERS 1.56
SQL>
自動拡張を停止する
長期間のUNDO領域を必要としてない場合、拡張されない様にしておきたい．
現在の設定を確認する．
SQL> col file_name for a50🆑
SQL> col autoextensible for a3🆑
SQL> select file_name,autoextensible from dba_data_files;🆑
FILE_NAME AUT
-------------------------------------------------- ---
/u01/app/oracle/oradata/XE/users.dbf YES
/u01/app/oracle/oradata/XE/sysaux.dbf YES
/u01/app/oracle/oradata/XE/system.dbf YES
/u01/app/oracle/oradata/XE/app_tbs.dbf YES
/u01/app/oracle/oradata/XE/app_idx_tbs.dbf YES
/u01/app/oracle/oradata/XE/undotbs2.dbf NO🈁
6 rows selected.
SQL>
自動拡張を有効にする場合は、次のようにAUTOEXTENDをONにする．
SQL> alter database datafile '/u01/app/oracle/oradata/XE/undotbs3.db' autoextend on;🆑
Database altered.
SQL>
UNDO表領域の解放
alter rollback segment undotbs1 shrink;🆑
