11gにおける一時表領域がSYSTEM表領域なユーザに関する考察#2

(今回はいつにも増して下らないネタ…)

11.2.0.3.0時代、あるところに、ユーザのデフォルト表領域がSYSTEM表領域で、
一時表領域もSYSTEM表領域のユーザがおったそうな。。

前回は既存のDB環境で色々やってみましたが、
一時表領域がSYSTEM表領域なユーザは作れませんでした。
どうやらデータベースのデフォルトの一時表領域が関係してそうなのは
分かりましたが、その設定をSYSTEM表領域に変えられません。

うまくいかないので、DBそのものから作り直してみます。


■環境
Windows7 64Bit
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64Bit

データベースの作成⇒カスタムデータベースの作成⇒表領域作成画面
TEMP表領域をGUIから削除します。


むかっ。


じゃあいい、DB作成スクリプト生成して、
スクリプト編集してTEMP作成する部分を削除して実行してやるっ！

CREATEDB.SQL抜粋
=========================================================================
・・・
MAXDATAFILES 100
 DATAFILE 'C:\app\oracle\oradata\orcl2\system01.dbf' SIZE 700M REUSE
 AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED　EXTENT MANAGEMENT LOCAL
  SYSAUX DATAFILE 'C:\app\oracle\oradata\orcl2\sysaux01.dbf' SIZE 600M REUSE
  AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
   SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE 
   'C:\app\oracle\oradata\orcl2\undotbs01.dbf' SIZE 200M REUSE
    AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED

CHARACTER SET JA16SJISTILDE

・・・
=========================================================================
行1でエラーが発生しました。:
ORA-12900: ?????????????????????????????????????? 


=========================================================================
ORA-12900: ローカル管理データベース用のデフォルトの
　　　　　 一時表領域を指定する必要があります。
原因: ローカル管理データベースには、SYSTEM表領域以外に一時表領域が必要です
処置: ローカル管理データベースの作成時に、
      デフォルトの一時表領域を指定してください。
=========================================================================


むかむかっ。

SQLリファレンスのCREATE DB文を確認します。
=========================================================================
extent_management_clause

この句を使用すると、ローカル管理SYSTEM表領域を作成できます。
この句を指定しない場合、SYSTEM表領域はディクショナリ管理となります。

注意:
ローカル管理SYSTEM表領域を作成すると、
この表領域をディクショナリ管理に変更することはできません。
このデータベース内に別のディクショナリ管理表領域を作成することも
できません。
この句を指定した場合、ローカル管理のSYSTEM表領域には
一時セグメントを格納できないため、データベースにデフォルトの
一時表領域が必要になります。

EXTENT MANAGEMENT LOCALを指定して、DATAFILE句を指定しない場合は、
default_temp_tablespace句を省略できます。
Oracle Databaseは、データファイル・サイズが10MBで、
自動拡張を使用禁止にした状態で、TEMPという一時表領域を作成します。

EXTENT MANAGEMENT LOCALおよびDATAFILEの両方の句を指定する場合は、
default_temp_tablespaceを指定し、その表領域のデータファイルを
明示的に指定する必要があります。

default_temp_tablespace
この句を指定すると、データベースのデフォルトの一時表領域を作成できます。
Oracle Databaseは、この一時表領域に対して、別の一時表領域を指定していない
ユーザーを割り当てます。
この句を指定しないと、
データベースがローカル管理のSYSTEM表領域の作成時に、
デフォルトの一時表領域を自動的に作成しない場合、
SYSTEM表領域がデフォルトの一時表領域になります。
==========================================================================

なんか最後の三行は矛盾している気もするけど、
とにかくSYSTEM表領域がローカルだと、ローカル管理データベースとなり、
SYSTEM表領域以外に一時表領域が必要というなら、
ディクショナリ管理でSYSTEM表領域作ってやろうじゃない。


CREATEDB.SQL抜粋
・・・
MAXDATAFILES 100
 DATAFILE 'C:\app\oracle\oradata\orcl2\system01.dbf' SIZE 700M REUSE
 AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED　
 EXTENT MANAGEMENT LOCAL　←削除
  SYSAUX DATAFILE 'C:\app\oracle\oradata\orcl2\sysaux01.dbf' 
  SIZE 600M REUSE　AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
   SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE 
   'C:\app\oracle\oradata\orcl2\undotbs01.dbf' SIZE 200M REUSE
    AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED

CHARACTER SET JA16SJISTILDE

・・・

DBが作成されました。

データベースのデフォルトの表領域を確認します。

SQL> select property_value,property_name from database_properties
   2 where property_name like '%TABLESPACE%';

PROPERTY_VALUE  PROPERTY_NAME
--------------- -----------------------------
SYSTEM          DEFAULT_TEMP_TABLESPACE
SYSTEM          DEFAULT_PERMANENT_TABLESPACE

いい感じ。

SQL> create user hoge2 identified by hoge2;

ユーザーが作成されました。

SQL> select username,default_tablespace,temporary_tablespace from dba_users
  2  where username='HOGE2';
  
  
USERNAME   DEFAULT_TABLESPACE        TEMPORARY_TABLESPACE
---------- ------------------------- -------------------------
HOGE2      SYSTEM                    SYSTEM

1行が選択されました。



そう。これ。これ待ってた。


冷静になって、DBCAの表領域作成箇所で、
SYSTEM表領域のエクステント管理をディクショナリ管理に変更して、
TEMP表領域の削除を実施したら。エラーなく削除できました。
(わざとDB作成スクリプトの例を出しているのですが。念のため)


次回、検証を通して一時表領域がSYSTEMなユーザがなぜできたかを考えます。