ユーザに対して表領域の使用権限(容量)を与えようと、alter userを発行したところ「ORA-01655」なるエラーが発生しました。
SQL> alter user scott quota unlimited on tbs_1; alter user scott quota unlimited on tbs_1 * ERROR at line 1: ORA-02002: error while writing to audit trail ORA-01655: unable to extend cluster SYS.C_FILE#_BLOCK# by 8 in tablespace SYSTEM
SYSTEM表領域が何かできなかったようです。
Oracleのサイトで調べてみると、ORA-01655は次のとおり。
ORA-01655: クラスタstring.stringを拡張できません(string分、表領域string)。 原因: 表領域のクラスタ・セグメントへのエクステントの割当てに失敗しました。 処置: ALTER TABLESPACE ADD DATAFILE文を使用して、指定された表領域に1つ以上のファイルを追加してください。
とりあえずデータファイルを追加すれば解決するらしいので、表領域情報の確認方法も合わせておさらいします。
表領域の状態を確認する
現時点での表領域の状態を確認してみます。
表領域の容量を確認するためのテーブルは2つ。
- dba_data_files:物理ファイル、使用量、最大容量などの基本情報
- dba_free_space:空き容量に関する情報
ちなみに一時表領域の基本情報はdba_temp_filesテーブルに入っています。
使用量と空き容量を同時に確認するためには2つのテーブルをjoinする必要があります。
なおdba_free_spaceは「空き容量が0」の情報は格納されません。つまり既に空き容量0となっている表領域の情報は得られません。
上記を踏まえ2つのテーブルをouter joinして結合しました。
SQL> select ut.tablespace_name name,sum(ut.bytes)/(1024*1024) "SIZE[M]",sum(ut.maxbytes)/(1024*1024) "MAX[M]",sum(ft.bytes)/(1024*1024) "FREE[M]" from dba_data_files ut left outer join dba_free_space ft on ut.file_id=ft.file_id group by ut.tablespace_name NAME SIZE[M] MAX[M] FREE[M] ----------- ---------- ---------- ---------- SYSAUX 650 0 146.625 TBS_1 200 32767.9844 199 UNDOTBS 1000 163839.922 190.375 SYSTEM 325 0
SYSTEM表領域のFREE[M]列を見てみると、空白になっています。
要するに「dba_free_space」に情報が入っていない=空き容量が0ということになります。
何故SYSTEM表領域。dba_data_filesテーブルでSYSTEM表領域のautoextensible列を確認してみると「NO」になっています。
表領域の初期値が小さく、いっぱいになっても拡張ができなかったようです。
SQL> select file_name,tablespace_name,autoextensible from dba_data_files where tablespace_name='SYSTEM'; FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME AUT ------------------ --- /opt/app/oracle/product/12.1.0/dbhome_1/oradata/mydb/system01.dbf SYSTEM NO
表領域を拡張する
単純なデータファイルの追加
Oracleのサイトにあったとおり、データファイルを追加します。
SQL> alter tablespace system add datafile '$ORACLE_HOME/oradata/mydb/system02.dbf' size 400m; Tablespace altered.
SYSTEM表領域の情報を確認してみると空き容量が追加され、データファイルが追加されていることが分かります。
SQL> select ut.tablespace_name name,sum(ut.bytes)/(1024*1024) "SIZE[M]",sum(ut.maxbytes)/(1024*1024) "MAX[M]",sum(ft.bytes)/(1024*1024) "FREE[M]" from dba_data_files ut left outer join dba_free_space ft on ut.file_id=ft.file_id group by ut.tablespace_name; NAME SIZE[M] MAX[M] FREE[M] ----------- ---------- ---------- ---------- SYSAUX 650 0 146.625 TBS_1 200 32767.9844 199 UNDOTBS 1000 163839.922 190.375 SYSTEM 725 0 399 SQL> select file_name,tablespace_name,autoextensible from dba_data_files where tablespace_name='SYSTEM'; FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME AUT ------------------ --- /opt/app/oracle/product/12.1.0/dbhome_1/oradata/mydb/system01.dbf SYSTEM NO /opt/app/oracle/product/12.1.0/dbhome_1/oradata/mydb/system02.dbf SYSTEM NO
表領域の自動拡張
単に表領域を拡張しても、また容量がFULLになってしまうかもしれません。
そこで表領域の自動拡張を設定します。
SQL> alter database datafile '$ORACLE_HOME/oradata/mydb/system01.dbf' autoextend on next 20M maxsize unlimited Database altered. SQL> select file_name,tablespace_name,autoextensible,(increment_by*8192)/(1024*1024) "NEXT[M]" from dba_data_files where tablespace_name='SYSTEM'; FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME AUT NEXT[M] ------------------------------ ------ ---------- /opt/app/oracle/product/12.1.0/dbhome_1/oradata/mydb/system01.dbf SYSTEM YES 20 /opt/app/oracle/product/12.1.0/dbhome_1/oradata/mydb/system02.dbf SYSTEM NO 0
指定した増分はincrement_by列を使って確認することができます。
increment_by列にはブロック数が格納されているので、ブロックサイズをかけてバイトを求めます。
なお、表領域のデータファイルが単一の構成(bigfile表領域)である場合は、alter tablespaceで自動拡張の設定が可能です。
alter tablespace system autoextend on next 20M maxsize unlimited;
もう一度alter user
忘れちゃいけない、このためにやっていました。
SQL> alter user scott quota unlimited on tbs_1; User altered.
まとめ
さらっとおさらいしました。
ここまでやって感じたポイントは、
CREATE DATABASEのときSYSTEM(とSYSAUX)表領域の自動拡張は忘れないように!
でしょうか。
おわり。
【オラクル認定資格試験対策書】ORACLE MASTER Silver[Silver DBA11g](試験番号:1Z0-052)完全詳解+精選問題集 (オラクルマスタースタディガイド)
- 作者: エディフィストラーニング株式会社,鈴木佐和,飯室美紀,岡野友紀,西昭彦
- 出版社/メーカー: SBクリエイティブ
- 発売日: 2011/08/27
- メディア: 単行本
- 購入: 2人 クリック: 5回
- この商品を含むブログ (5件) を見る