はじめに

PL/SQL SDKを使用して、Autonomous Databaseが毎日決まった時間にSQLを自動的に実行して、その結果をメールで送信するようにしてみました。

また、こちらの内容はあくまで検証のため、エラーハンドリング等は行っておりません。
本番環境で利用される際は、エラーハンドリング、リトライ処理を実装されることをお勧めします。

1.事前準備

今回の手順ではリソース・プリンシパルを使用していますので、こちらのドキュメントを参考にして、あらかじめadminユーザーに対して、Autonomous Databaseインスタンスのリソース・プリンシパルを有効にしておく必要があります。

また、こちらのAutonomous Databaseは、adminユーザで

ALTER DATABASE SET TIME_ZONE='+09:00';

を実行して、DBタイムゾーンをJST（UTC+9:00）に設定しています。

2.PL/SQLブロックでの実行

あらかじめ、こちらの記事の1.を参考にして、通知サービスのトピックとサブスクリプションを作成しておきます。

今回は、こちらのSQLの実行結果をメールで送信してみます。

SELECT tablespace_name, TO_CHAR(SUM(bytes/1024/1024/1024),'999,990.99') size_gb 
FROM dba_data_files
WHERE tablespace_name != 'SAMPLESCHEMA'
GROUP BY tablespace_name
ORDER BY tablespace_name;

このSQLは、dba_data_filesから、表領域を構成するデータファイルのサイズを合計したものを表領域のサイズとして取得します。（SAMPLESCHEMA表領域を除く）

メールの送信には、通知サービス（DBMS_CLOUD_OCI_ONS_NOTIFICATION_DATA_PLANE.PUBLISH_MESSAGEファンクション）を使用します。

なお、こちらに記載があるように、 dbms_cloud_oci_ons_message_details_t.bodyがVARCHAR2(4000)となっているため、SQLの実行結果の内容が4000バイトを超える場合にはエラーになります。
その場合は、UTL_SMTPパッケージを使用する等の別の方法をとる必要があります。

SQL> set serveroutput on
SQL> DECLARE
  2  	ons_message_details	dbms_cloud_oci_ons_message_details_t;
  3  	response			dbms_cloud_oci_ons_notification_data_plane_publish_message_response_t;
  4  	CURSOR result_cur IS 
  5  		SELECT tablespace_name, TO_CHAR(SUM(bytes/1024/1024/1024),'999,990.99') size_gb 
  6  		FROM dba_data_files
  7  		WHERE tablespace_name != 'SAMPLESCHEMA'
  8  		GROUP BY tablespace_name
  9  		ORDER BY tablespace_name;
 10  	result_row 			result_cur%ROWTYPE;
 11  	time				VARCHAR2(100);
 12  
 13  BEGIN
 14  	ons_message_details	:= dbms_cloud_oci_ons_message_details_t();
 15  	ons_message_details.title := 'TABLESPACE SIZE as of ';
 16  	ons_message_details.body := '';
 17  
 18  	EXECUTE IMMEDIATE 'ALTER SESSION SET SYSDATE_AT_DBTIMEZONE=TRUE';
 19  	SELECT TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI') INTO time FROM dual;
 20  	ons_message_details.title := ons_message_details.title||time;
 21      DBMS_OUTPUT.PUT_LINE('Title : '||ons_message_details.title);
 22  
 23  	OPEN result_cur;
 24  	LOOP
 25  		FETCH result_cur into result_row;
 26  		EXIT WHEN result_cur%NOTFOUND;
 27  
 28  		DBMS_OUTPUT.PUT_LINE(result_row.tablespace_name||':'||result_row.size_gb||'GB');
 29  		ons_message_details.body :=  ons_message_details.body||result_row.tablespace_name||' : '||result_row.size_gb||' GB'||CHR(13) || CHR(10);
 30  
 31  	END LOOP;
 32  	CLOSE result_cur;
 33  
 34  	response := DBMS_CLOUD_OCI_ONS_NOTIFICATION_DATA_PLANE.PUBLISH_MESSAGE (
 35  		topic_id => 'ocid1.onstopic.oc1.ap-tokyo-1.xxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
 36  		message_details => ons_message_details,
 37  		region => 'ap-tokyo-1',
 38  		credential_name => 'OCI$RESOURCE_PRINCIPAL'
 39  	);
 40  END;
 41  /
Title : TABLESPACE SIZE as of 2022/02/16 12:55
DATA:	  210.10GB
DBFS_DATA:	 0.10GB
SYSAUX:       3.41GB
SYSTEM:       0.77GB
UNDOTBS1:      10.44GB
UNDO_8:       0.09GB

PL/SQLプロシージャが正常に完了しました。

SQL>

PL/SQLブロックが問題なく実行できました。

メールボックスを確認してみます。

表領域の名前と表領域のサイズ（データファイルのサイズの合計）が記載されたメールが届きました。
メールにタイトルにはデータ取得日時が含まれています。

3.ストアド・プロシージャの作成

PL/SQLブロックでの動作確認ができたので、これをもとにストアド・プロシージャ"notify_ts_size"を作成します。

SQL> CREATE OR REPLACE PROCEDURE notify_ts_size
  2  IS
  3  	ons_message_details	dbms_cloud_oci_ons_message_details_t;
  4  	response			dbms_cloud_oci_ons_notification_data_plane_publish_message_response_t;
  5  
  6  	CURSOR result_cur IS 
  7  		SELECT tablespace_name, TO_CHAR(SUM(bytes/1024/1024/1024),'999,990.99') size_gb 
  8  		FROM dba_data_files
  9  		WHERE tablespace_name != 'SAMPLESCHEMA'
 10  		GROUP BY tablespace_name
 11  		ORDER BY tablespace_name;
 12  	result_row 			result_cur%ROWTYPE;
 13  	time				VARCHAR2(100);
 14  
 15  BEGIN
 16  	ons_message_details	:= dbms_cloud_oci_ons_message_details_t();
 17  	ons_message_details.title := 'TABLESPACE SIZE as of ';
 18  	ons_message_details.body := '';
 19  
 20  	EXECUTE IMMEDIATE 'ALTER SESSION SET SYSDATE_AT_DBTIMEZONE=TRUE';
 21  	SELECT TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI') INTO time FROM dual;
 22  	ons_message_details.title := ons_message_details.title||time;
 23  	DBMS_OUTPUT.PUT_LINE('Title : '||ons_message_details.title);
 24  
 25  	OPEN result_cur;
 26  	LOOP
 27  	FETCH result_cur into result_row;
 28  	EXIT WHEN result_cur%NOTFOUND;
 29  
 30  	DBMS_OUTPUT.PUT_LINE(result_row.tablespace_name||':'||result_row.size_gb||'GB');
 31  	ons_message_details.body :=  ons_message_details.body||result_row.tablespace_name||' : '||result_row.size_gb||' GB'||CHR(13) || CHR(10);
 32  
 33  	END LOOP;
 34  	CLOSE result_cur;
 35  
 36  	response := DBMS_CLOUD_OCI_ONS_NOTIFICATION_DATA_PLANE.PUBLISH_MESSAGE (
 37  		topic_id => 'ocid1.onstopic.oc1.ap-tokyo-1.xxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
 38  		message_details => ons_message_details,
 39  		region => 'ap-tokyo-1',
 40  		credential_name => 'OCI$RESOURCE_PRINCIPAL'
 41  	);
 42  END;
 43  /

プロシージャが作成されました。

SQL>

ストアド・プロシージャ"notify_ts_size"が問題なく作成できました。

4.ストアド・プロシージャの動作確認

ストアド・プロジージャ"notify_ts_size"を実行してみます。

SQL> EXEC notify_ts_size;
Title : TABLESPACE SIZE as of 2022/02/16 13:03
DATA:	  210.10GB
DBFS_DATA:	 0.10GB
SYSAUX:       3.41GB
SYSTEM:       0.77GB
UNDOTBS1:      10.44GB
UNDO_8:       0.09GB

PL/SQLプロシージャが正常に完了しました。

SQL>

ストアド・プロシージャ"notify_ts_size"が問題なく実行できました。

メールボックスを確認してみます。

PL/SQLブロック実行時と同様に、表領域の名前と表領域のサイズ（データファイルのサイズの合計）が記載されたメールが届きました。
メールにタイトルにはデータ取得日時が含まれています。

5.ストアド・プロシージャの自動実行

こちらのストアド・プロシージャ"notify_ts_size"を毎朝8時に自動実行してメールを送信するように、DBMS_SCHEDULERのジョブ"notify_ts_size_job"を作成します。

SQL> BEGIN
  2  	DBMS_SCHEDULER.CREATE_JOB (
  3  		job_name           =>  'NOTIFY_TS_SIZE_JOB',
  4  		job_type           =>  'STORED_PROCEDURE',
  5  		job_action         =>  'ADMIN.NOTIFY_TS_SIZE', 
  6  		start_date         =>  TO_TIMESTAMP_TZ('2022-02-13 08:00:00 +9:00','YYYY-MM-DD HH24:MI:SS TZH:TZM'),
  7  		repeat_interval    =>  'FREQ=DAILY;INTERVAL=1',
  8  		auto_drop          =>  FALSE,
  9  		enabled            =>  TRUE
 10  	);
 11  END;
 12  /

PL/SQLプロシージャが正常に完了しました。

SQL>

以上で、Autonomous Databaseが毎朝8時に自動的に表領域のサイズをメールで送信するように設定できました。

参考情報

DBMS_CLOUD_OCI_ONS_NOTIFICATION_DATA_PLANE.PUBLISH_MESSAGE Function
DBMS_CLOUD_OCI_ONS_MESSAGE_DETAILS_T Type
DBMS_CLOUD_OCI_ONS_NOTIFICATION_DATA_PLANE_PUBLISH_MESSAGE_RESPONSE_T Type
[OCI]通知サービスとイベントサービスを利用してDBCSのDBノードの起動/停止をメールで通知してみた
 [OCI]Autonomous Database：SYSDATE_AT_DBTIMEZONEを使用してsysdateが日本時間を返すようにしてみた