【データベース(oralce)】表領域の枯渇調査

ブログを作りました。

開発をやりたいですが、残念ながら保守業務に携わっています。

保守業務をやっていると、基本的に毎月同じことをするわけですが、トラブルなんかが発生すると、その時初めて試行錯誤しながら調査解決することも多いわけです。

(トラブル自体はいいことではないですが、トラブルが発生した時が一番知見が増えるので、個人的にはありがたいです)

というわけで保守をしていて、気なった内容や得た知見を載せていくブログです。

--------------------------------------------------

以前、表領域が枯渇しました。

表領域は、tablespaceとも言いますが、DB構築時にいくつかに分けて作られます。

実際に自分の検証用としてoracleDBをインストールして見たところ、

EXAMPLE、SYSAUX、SYSTEM、TEMP、UNDOTBS1、USERS

の表領域が作成されていました。

この時の表領域の枯渇原因は、業務SEが勝手に本番テーブルをバックアップしていたという、なんとも全くITリテラシーのない話でした。（そもそも本番環境に各担当が独自に操作できてしまうというのも問題なのですが、あまり制約をガチガチにすると、生産性が著しく低下するので難しいところではあるのですが。）

まず、createテーブルをする際には、当然どこかの領域にテーブルが作成されます。

じゃあどこの領域にテーブルが作成されているのかということですが、createテーブルする際に作成テーブルを指定できます。なので、環境が理解できていれば、そもそもバックアップしようとしたテーブルが元々存在する業務用の表領域にテーブルをバックアップするというのが普通なのですが、バックアップした担当者はそんなこともわかっていないため、適当にそのままcreateテーブルしてしまったため、デフォルト表領域にテーブルが作成されてしまいました。

各オラクルユーザ（スキーマ）はデフォルト表領域が設定されています。

設定値は下記で確認できました。

select username,default_tablespace from dba_users

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SCOTT USERS

このサンプルではSCOTTというスキーマのデフォルト表領域にUSERS表領域が指定されているため、指定なしにcreateテーブルするとUSERS表領域にテーブルが作成されるはずです。

試して見ましょう。

sqlplus scott/tiger as dba

create table emp

(

　　emp_id char(3) ,

　　emp_name varchar2(10),

　　primary key( emp_id )

)

select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables where table_name = 'EMP'

OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ---------------------------- ------------------------------
SCOTT EMP USERS

予想通りUSERS表領域にEMPというテーブルが作成されました。

調査の際には、OracleEnterpraseManagere(OEM)で表領域の枯渇を確認していましたので、あとは、「デフォルトの表領域に所属しているテーブルの一覧と、各テーブルが使用しているサイズ」がわかれば調査としてはOKです。

この時は、時間がなかったこともあり、適当に調べつつなんとかできたのですが、改めて調べて見るともう少しスマートな方法で調査できたようです。

まず、OEMで見ていた各表領域の使用量ですが、下記でリアルタイムに取得可能です。

select TABLESPACE_NAME,

       TABLESPACE_SIZE/1024/1024/1024 “TOTAL(GB)”,

       USED_SPACE/1024/1024/1024 “USED(GB)”,

       USED_PERCENT “USED(%)”

from DBA_TABLESPACE_USAGE_METRICS;

TABLESPACE_NAME                 TOTAL(GB)   USED(GB) USED(%)

------------------------------ ---------- ---------- -------

EXAMPLE                        .003906248 9.3877E-06     0.2

SYSAUX                         .003906248 .000059411     1.5

SYSTEM                         .003906248 .000081286     2.1

TEMP                           .003906248          0     0.0

UNDOTBS1                       .003906248 1.7136E-07     0.0

USERS                          .003906248 4.9174E-07     0.0

例えば今回Scottのデフォルト表領域となっているUSERS表領域が枯渇していたならば、USEDが100.0となっているかと思います。

DBA_TABLESPACE_USAGE_METRICSというビューに表領域関連の情報があるので、これを見たら良かったようです。

続いて、USERS表領域に存在するテーブルの一覧取得です。

select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables where tablespace_name = 'USERS';

OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ -----------------------------
SCOTT DEPT USERS

SCOTT EMP USERS

SCOTT BONUS USERS

取れました。一部省略してますが、USERS表領域を使用しているテーブルの一覧です。

（★残念ながら各テーブルサイズの取得方法がわかりませんでした。こちらは後日調べて記載したいと思います。。）

これで知りたい情報を取得できました。

あとは、それぞれのテーブルについて作成者に削除依頼をしてdropしてもらえれば完了です。

データベースについては、ほとんど触ることがないため、基本的な構文ぐらいしかわかりません。管理者としてシステムの色々な部分を知っておいたほうがいいとは思うので、データ・ディクショナリ・ビューなどの、管理部分については最低限理解しておこうと思います。