大量の表領域をリサイズする方法

涼しくなり始めたある日、構築済みDBの表領域をリサイズして欲しいとの連絡を受けました。

リリース準備中の新本番環境で、構成は以下の通りです。

Oracle Restart(SIHA) 12.1.0.2
AIX Version 7.1

対象DBの数は約80で、対象表領域の数はトータルで1800くらいです。

Pro Oracle SQL DevelopmentBest Practices for Writing Advanced Queries【電子書籍】[ Jon Heller ]

翌日に作業を開始するスケジュールなので、当日中にSQLスクリプトを作成しなければなりません。

資料として連携されてきたエクセルファイルにはDB名、表領域名、現行サイズ、更新後のサイズが記載されています。

しかしながら表領域のリサイズは実際にはデータフィルのリサイズなので、対象表領域に含まれるデータファイルの名前もしくはFILE_IDが必要になります。

とは言え、対象DBの数は約80で対象表領域の数は約1800、しかも翌日までにスクリプトを用意しなければならないので、あらかじめFILE_IDを取得しておいてそれを元にスクリプト作成する時間はありません。

時間があっても面倒です。

なので以下のようなスクリプトを作成しました。

col FILE_ID new_value FILE_ID
col max_file_id new_value max_file_id
col min_file_id new_value min_file_id

データファイルが一つのみの表領域のパターン

select min(FILE_ID) FILE_ID from dba_data_files where TABLESPACE_NAME='TBS_A';
alter database datafile &FILE_ID resize 11000 M;

データファイルが二つの表領域のパターン

select max(FILE_ID) max_file_id,min(FILE_ID) min_file_id from dba_data_files where TABLESPACE_NAME='TBS_B';
alter database datafile &min_file_id,&max_file_id resize 7850 M;

この書き方であればその場でFILE_IDを取得するので、あらかじめFILE_IDを取得しておいてそれを元にスクリプト作成する必要がありません。

データファイルが三つ以上の表領域には対処できませんが、対象表領域約1800の内、三つ以上のデータファイルから構成される表領域は二つしかないので、その二つについては手作業で対処する事にしました。

データファイルが一つのみの表領域のパターンでselect min(FILE_ID)・・・としているのは、select FILE_ID・・・だと結果が取得できなくてもそのすぐ前のFILE_IDが変数にセットされたままなので、エラーが発生せずに別の表領域用のサイズでresizeされてしまう為です。

select min(FILE_ID)としておけば、仮にエクセルファイルで連携されてきた表領域名が間違っていた場合、変数にヌルがセットされるのでエラーになります。

この変数設定はスプールファイル名を動的に指定するのにも使えます。

col host_name new_value host_name
col instance_name new_value instance_name
col log_name new_value log_name
select instance_name,host_name from v$instance;
select 'resize_ts_'||'&host_name'||'_'||'&instance_name'||'.log' log_name from dual;
spool &log_name

上記の通り指定すると、resize_ts_myhost_orcl1.logのようなログファイル名になるので、いちいちリテラルでログファイル名を指定せずに済みますし、接続先DBが正しい事の確認にもなります。

今回は対象DBが12cでしたが、かなり古いバージョンでも使えるはずです。