oracle SYSTEM01.DBF が大きい

質問

現在、oracleのSYSTEM01.DBFが大きいくなっていています。
１３Ｇです。
SYSTEM01.DBFの縮小する方法がわかりません。
教えて下さい。
Ｗｅｂで調べましたがよくわからなくて・・・。
宜しくお願いします。
わがままとなりますが、なるべく、わかりやすく。

回答No.4

muyoshid

こんにちわ。
1点訂正です。

LONG 型データを含まないテーブルの表領域移動をするコマンドに
間違えがありました。
以下のように訂正します。

誤:SQL> Alter table テーブル名 move <新表領域名>;
正:SQL> Alter table テーブル名 move tablespace <新表領域名>;

お礼

ご回答、ありがとうございます。

回答No.3

muyoshid

こんにちわ。
> Oracleは9iです。
> テーブルをdropしてインポートすると言う事を頻繁に・・・
Oracle9i でしたか。
11g であれば、標準監査のログ (SYS.AUD$) が肥大化している可能性
もあったのですが、9i だと違いそうですね。

そうすると、テストで使ってるテーブルがSYSTEM 表領域を使っている
可能性が高いと思います。
SYSTEM 表領域の中を確認して、テスト用のテーブル/索引があった場合は
別の表領域に移動させてはどうでしょう？

因みに、テーブル/索引は以下の方法で表領域の移動が可能です。
テーブルの移動:
SQL> Alter table テーブル名 move <新表領域名>;

索引の移動:
SQL> Alter index 索引名 rebuild tablespace <新表領域名>;

但し、テーブルの表領域移動を行う際には3点程注意点があります。
1) SYSTEM 系のテーブル (SYS.AUD$ 等) は表領域の移動が
　 サポートされていない可能性があります。
2) Alter table コマンドで表領域の移動ができるのは
　 LONG 列を含まないテーブルに限られます。
　 LONG 列を含むテーブルは、exp, 別表領域にテーブル作成,
　 ignore=y を指定してimp の手順で行って下さい。
3) テーブルを移動すると、移動したテーブルに定義されている
　 索引の状態がUNUSABLE になります。
　 索引の状態を元に戻すには、索引のRebuild が必要となります。
　 ですので、表領域の移動はテーブルを先に行って下さい。

お礼

ご回答、ありがとうございます。

回答No.2

nora1962

> Oracleは９ｉです。
> ちなみに、テスト環境なのでテーブルをdropしてインポートすると言う事を頻繁に行っています。

これはテストテーブルをSYSTEM表領域に作成しているということでしょうか？
だとするとデータファイルが断片化してしまい縮小は難しくなると思います。

http://d.hatena.ne.jp/yohei-a/20100417/1271482051
を参考にしてData Pump で dumpデータを　exportし、DBの再作成。USERS表領域等を作成。
表領域を指定してインポート。

後は定期的にデータポンプでもexp,impでもいいのでデータの再構成を行なってdatafileのresizeをすればいいかと思います。

お礼

ご回答、ありがとうございます。

回答No.1

muyoshid

こんにちわ。

> SYSTEM01.DBFの縮小する方法がわかりません。
例えば、'/opt/oracle/oradata/orcl/system01.dbf' を5GB まで
縮小させるには、以下のコマンドを実行します。

SQL> alter database datafile
　　　'/opt/oracle/oradata/orcl/system01.dbf' resize 5120m;

但し、データファイルの5GB より後ろの領域が使われていると
縮小には失敗します。
データファイルが拡張していると言う事は、何かのセグメントが
大量に領域を使用しているんだと思います。
先ずは、何が領域を大量に使用しているか確認した方が良いですね。

手順は、こんな感じです。
1) SQL> select file_id, file_name from dba_datafiles;
　 上のSQL でSYSTEM01.DBF のfile_id を調べる。
2) SQL> select tablespace_name, block_size from dba_tablespaces;
　 上のSQL でSYSTEM 表領域 (SYSTEM01.DBF を使用している表領域) の
　 ブロックサイズを調べる。
3) SQL> select segment_type, segment_name, max(block_id)
　　　　 where file_id = <1) で確認したfile_id> and
　　　　　　block_id > 655360 order by 3 desc;
　 上のSQL で5GB より後ろの領域を使っているセグメントを調べる。
　 ※ block_size が8KB の場合を想定しています。
　　　5120 × 1024 ÷ 8 = 655360

それと、使用しているOracle のバージョンを教えて下さい。

お礼

ご回答、ありがとうございます。
No.３を確認します。

補足

Oracleは９ｉです。
ちなみに、テスト環境なのでテーブルをdropしてインポートすると言う事を頻繁に行っています。