ORA-01655が出たついでに表領域の拡張方法をおさらい(Oracle)

ユーザに対して表領域の使用権限(容量)を与えようと、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つ以上のファイルを追加してください。

ORA-01500〜ORA-02098

とりあえずデータファイルを追加すれば解決するらしいので、表領域情報の確認方法も合わせておさらいします。

表領域の状態を確認する

現時点での表領域の状態を確認してみます。

表領域の容量を確認するためのテーブルは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の現場を効率化する100の技

Oracleの現場を効率化する100の技

【オラクル認定資格試験対策書】ORACLE MASTER Silver[Silver DBA11g](試験番号:1Z0-052)完全詳解+精選問題集 (オラクルマスタースタディガイド)

【オラクル認定資格試験対策書】ORACLE MASTER Silver[Silver DBA11g](試験番号:1Z0-052)完全詳解+精選問題集 (オラクルマスタースタディガイド)