SQL DDL:プロファイル

プロファイルとは?

データベース・リソースへの制限
ユーザにプロファイルを割り当てる事で、ユーザの動作を制限できる
・ リソース制限
・ パスワード制限
・ ログイン制限
等がある

リソース制限

ユーザーが使用できるシステム・リソースを制限
使用可能なCPU等の上限値を設定
ユーザーが誤った無限ループ処理を行い、CPUが無制限に浪費される等の事態を防止
設定したリソース制限を超えると現行の処理はロールバックされる

ログイン制限

・ パスワードの定期変更
・ パスワードの有効期限
・ 誤ったパスワードの連続入力制限等
アカウントのロック
設定回数以上ログインを失敗した場合に自動的にそのアカウントをロック

定義

CREATE PROFILE (プロファイル名)
LIMIT
FAILED_LOGIN_ATTEMPTS 5
指定回数ログイン失敗でアカウントをロック

PASSWORD_LIFE_TIME 60
パスワード設定時からの有効日数

PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX 5
(1)同一パスワードが使用可能になるまでの日数
(2)同一パスワードが使用可能になるまでの変更回数
(1)(2)はどちらか一方のみ設定可

PASSWORD_VERIFY_FUNCTION (ファンクション名)
指定のパスワードチェック関数を実行して使用可能なパスワード文字をルール化

PASSWORD_LOCK_TIME 1/24
指定回数ログイン失敗時のアカウントロック日数
規定:1日

PASSWORD_GRACE_TIME 10
ログイン猶予日数の警告表示を行う日数
規定:7日


SESSIONS_PER_USER UNLIMITED
同時セッション数

CPU_PER_SESSION UNLIMITED
1セッション当たりのCPU制限時間(1/100秒単位)

CPU_PER_CALL 3000
1命令当たりのCPU制限時間(1/100秒単位)
CONNECT_TIME 45
1セッション当たりの接続制限時間(分単位)

LOGICAL_READS_PER_SESSION DEFAULT
1セッション当たりの読み込みデータブロック数上限

LOGICAL_READS_PER_CALL 1000
1命令当たりの読み込みデータブロック数上限

PRIVATE_SGA 15K
1セッション当たりのSGA内共有プールに割り当て可能な容量上限
COMPOSITE_LIMIT 5000000;
1セッション当たりのリソース使用容量(サービス単位)

SQL DDL:表領域

表領域とは?

=テーブルスペース
スキーマオブジェクトやディスクソートに使用するための一時的な領域を格納するためのストレージ領域
基本は表を表領域に所属させると、データファイルが特定の表領域に保存される。
(場合によっては論理的に複数の表領域に所属させたりできる)
・表領域単位でのアクセス制限
・表領域単位でのバックアップ/リカバリ
・表領域単位で保存ディスクを分ける事でのI/O速度向上
CREATE TABLESPACE ~ で作成し、容量を指定するので、
データベースのデータ蓄積に伴いHDがパンクする事は無い。

SYSTEM表領域

・ディクショナリ
・ストアドプロシージャ
・SYSTEMロールバックセグメント
データベース作成時に自動的に作成される
この領域にユーザオブジェクトを作成しない

ユーザ表領域

テーブル等のスキーマオブジェクトを作成
表、索引、順序、ビュー毎に表領域を作成する事をOracleは推奨している

ロールバック表領域

トランザクションデータを一時的に保持する領域

一時表領域

DISTINCT句、GROUP BY句、ORDER BY句を含むSQL実行時に利用される領域

定義方法

作成

CREATE TABLESPACE (表領域名)
 DATAFILE (データファイル名.dbf) SIZE 100M;
表領域を作成(オプション)
CREATE TABLESPACE (表領域名)
 DATAFILE (データファイル名.dbf) SIZE 100M;
 AUTOEXTEND ON NEXT 500K MAXSIZE 1024M;
 自動拡張500Kで最大1024M
 AUTOEXTEND ON NEXT 500K MAXSIZE UNLIMITED
 自動拡張500Kで最大サイズ無制限
 AUTOEXTEND OFF NEXT 500K MAXSIZE 1024M;
 自動拡張無しで最大1024M
 BLOCKSIZE ブロックサイズ
複数のデータファイルにまたがった表領域を作成
CREATE TABLESPACE (表領域名)
 DATAFILE (データファイル名1.dbf) SIZE 100M;
 DATAFILE (データファイル名2.dbf) SIZE 100M;
 AUTOEXTEND ON NEXT 500K MAXSIZE 1024M;

拡張

ALTER TABLESPACE (表領域名)
ADD DATAFILE '~/(データファイル名).dbf' SIZE 100M;
ALTER DATABASE DATAFILE '~/(データファイル名).dbf ';
RESIZE 150M
AUTOEXTEND ON ※自動拡張
NEXT 10M
MAXSIZE UNLIMITED;

削除

DROP TABLESPACE 表領域名;
(表領域にオブジェクトがある場合はエラー)
DROP TABLESPACE 表領域名 INCLUDING CONTENTS AND DATAFILES;
(表領域にオブジェクトがある場合も可。データファイルも同時削除)
※EMにおける表領域削除時の挙動と同じ

移動

ALTER TABLESPACE 旧表領域名 RENAME TO 新表領域名;

表作成時の表領域指定

「SQL DDL:テーブル定義」参照

SQL DDL:トリガー定義

CREATE TRIGGER

基本構文

CREATE (OR REPLACE) TRIGGER トリガー名
(BEFOR/AFTER/INSTEAD OF)
(INSERT/UPDATE [OF 列名]/DELETE) (OR INSERT/OR UPDATE [OF 列名]/OR DELETE) ON 表名
(FOR EACH ROW)
DECLARE
BEGIN
 ~;
EXCEPTION
 ~;
END;

例

CREATE OR REPLACE TRIGGER TEST
AFTER INSERT OR UPDATE OF 列名 ON 表名 FOR EACH ROW
BEGIN
 IF INSERT THEN
  UPDATE 表名 SET 列名 = ~;
 ELSIF
  UPDATE 表名 SET 列名 = ~;
 END IF;
 
 UPDATE 表名 SET 列名 = ~;
END;

トリガーの実行タイミング

BEFOR
SQL実行前
AFTER
SQL実行後
INSTEAD OF
SQLを実行せずにトリガーのみ実行

実行回数

FOR EACH ROW
SQL文によって複数行のデータが操作された場合、
行数文トリガーを実行する
「FOR EACH ROW」指定が無い場合は、1回のSQL文で1回のみトリガー実行実行

ALTER TRIGGER

トリガーの有効/無効 切り替え
ALTER TRIGGER TEST ENABLE;
ALTER TRIGGER TEST DISABLE;

DROP TRIGGER

トリガーの削除
DROP TRIGGER TEST;

SQL DDL:ドメイン定義

ドメインとは

定義域
domain
ユーザーが定義するデータ型
組み込みデータ型のエイリアス

定義

CREATE DOMAIN
ALTER DOMAIN
DROP DOMAIN
CREATE DOMAIN MyDomain INTEGER
 CHECK (VALUE > 10);

SQL DDL:ストアド定義

ストアドプロシージャ定義

CREATE OR REPLACE PROCEDURE MyProcedure
(
 prm IN NUMBER,
 ret OUT NUMBER
) AS

BEGIN

 ret:=prm*100;

EXCEPTION
 WHEN OTHERS THEN
  ret:=0;
END;

CREATE PROCEDURE ストアド名 引数 型情報 AS
  SELECT 列名 FROM 表名 WHERE 列名 = 引数

ストアドファンクション定義

CREATE OR REPLACE FUNCTION MyFunction
(
 PRM IN VARCHAR1
)
RETURN VARCHAR2 AS

 RET VARCHAR2(4);

BEGIN
 
 RET := PRM;
 
 RETURN RET;
 
EXCEPTION
 WHEN OTHERS THEN
  RETURN NULL;

END MyFunction;

パッケージ

・ストアドプロシージャ
・ストアドファンクション
を分類する為の名称
Javaのパッケージそのもの
.NETで言う名前空間

【定義】

仕様部
パッケージに含まれるファンクション(プロシージャ)の名前と引数、戻り値のみを定義

CREATE [OR REPLACE] PACKAGE (パッケージ名) AS

 FUNCTION ファンクション名(引数1 IN 型1, 引数2 IN 型2) RETURN 戻り値型;

 PROCEDURE プロシージャ名(引数1 IN 型1, 引数2 IN 型2);

END;

BODY部
実ファンクション(プロシージャ)、グローバル変数

CREATE [OR REPLACE] PACKAGE BODY パッケージ名 AS

 グローバル変数
 変数名 型;

 FUNCTION ファンクション名(引数1 IN 型1, 引数2 IN 型2) RETURN 戻り値型
  IS
   ~
  END ファンクション名;

 PROCEDURE プロシージャ名(引数1 IN 型1, 引数2 IN 型2)
  IS
   ~
  END プロシージャ名;

END パッケージ名;

【削除】
DROP PACKAGE <パッケージ名>;
DROP PACKAGE BODY <パッケージ名>;

ストアドプログラムの呼び出し

SQL> execute ストアド名('~', '~');
SQL> exe ストアド名('~', '~');
SQL> execute パッケージ.ストアド名('~', '~');

EXECUTE ストアド名
  @Prm1 = @SyoriYMD1,
  @Prm2 = 1000,
  @Prm3 = 'TEST'