11gリリース1（11.1）
E05760-03
 目次 |
 索引 |
| Oracle Database 管理者ガイド 11gリリース1（11.1） E05760-03 |
|
この章の内容は次のとおりです。
CREATE SCHEMA文を使用すると、一度の操作で複数の表やビューを作成し、権限を付与できます。CREATE SCHEMA文は、複数の表とビューの作成、および権限の付与を一度の操作で確実に行う必要がある場合に便利です。個々の表やビューの作成が失敗したり、権限の付与が失敗したりすると、文全体がロールバックされます。オブジェクトは作成されず、権限も付与されません。
CREATE SCHEMA文に指定できるのは、CREATE TABLE、CREATE VIEWおよびGRANT文のみです。指定した文を発行するための権限を持っている必要があります。この文を実行しても実際にスキーマが作成されるわけではありません。スキーマが作成されるのは、CREATE USER文でユーザーを作成したときです。そのかわりに、この文はスキーマを移入します。
次の文は、2つの表とそれらのデータを結合するビューを作成します。
CREATE SCHEMA AUTHORIZATION scott CREATE TABLE dept ( deptno NUMBER(3,0) PRIMARY KEY, dname VARCHAR2(15), loc VARCHAR2(25)) CREATE TABLE emp ( empno NUMBER(5,0) PRIMARY KEY, ename VARCHAR2(15) NOT NULL, job VARCHAR2(10), mgr NUMBER(5,0), hiredate DATE DEFAULT (sysdate), sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(3,0) NOT NULL CONSTRAINT dept_fkey REFERENCES dept) CREATE VIEW sales_staff AS SELECT empno, ename, sal, comm FROM emp WHERE deptno = 30 WITH CHECK OPTION CONSTRAINT sales_staff_cnst GRANT SELECT ON sales_staff TO human_resources;
CREATE SCHEMA文は、STORAGE句など、ANSIのCREATE TABLE文とCREATE VIEW文を拡張したOracle Database独自の機能をサポートしていません。
次の目的でスキーマ・オブジェクト（表、索引またはクラスタ）を分析します。
この項の内容は、次のとおりです。
DBMS_STATSパッケージまたはANALYZE文を使用して、表、索引またはクラスタの物理記憶特性の統計を収集できます。これらの統計はデータ・ディクショナリに格納され、オプティマイザで使用して、分析対象オブジェクトにアクセスするSQL文に最も効率的な実行計画を選択できます。
オプティマイザ統計の収集には、より多様性のあるDBMS_STATSパッケージを使用することをお薦めしますが、空きブロックや平均容量など、オプティマイザに関連付けられていない統計の収集にはANALYZE文を使用する必要があります。
DBMS_STATSパッケージを使用すると、パラレル実行を利用した統計収集と統計の外部操作ができます。統計をデータ・ディクショナリ以外の表に格納し、オプティマイザに影響を与えずにその統計を操作できます。統計をデータベース間でコピーしたり、バックアップ・コピーを作成できます。
次のDBMS_STATSプロシージャにより、オプティマイザ統計を収集できます。
表、索引、クラスタまたはマテリアライズド・ビューの構造の整合性を検証するには、VALIDATE STRUCTUREオプションを指定したANALYZE文を使用します。構造が有効な場合、エラーは返されません。しかし、構造が破損していると、エラー・メッセージが出力されます。
たとえば、ハードウェアやその他のシステムに障害が発生した場合、索引が破損し、正しく機能しなくなる可能性があります。索引の妥当性をチェックすると、索引内のすべてのエントリが、対応付けられた表の正しい行を示しているかを確認できます。索引が破損した場合は、その索引を削除して再作成できます。
表、索引またはクラスタが破損している場合は、削除して再作成する必要があります。マテリアライズド・ビューが破損している場合は、完全リフレッシュを実行し、問題が修正されたことを確認します。問題が修正されない場合は、マテリアライズド・ビューを削除して再作成します。
次の文は、emp表を分析します。
ANALYZE TABLE emp VALIDATE STRUCTURE;
CASCADEオプションを含めると、オブジェクトとすべての依存オブジェクト（索引など）の妥当性をチェックできます。次の文は、emp表と、それに対応付けられているすべての索引の妥当性をチェックします。
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;
デフォルトでは、CASCADEオプションによって、完全な妥当性チェックが実行されます。この操作はリソースを消費する可能性があるため、FAST句を使用してより高速なバージョンの妥当性チェックを実行できます。このバージョンでは、最適化されたチェック・アルゴリズムを使用して破損の有無をチェックしますが、破損の詳細はレポートしません。FASTチェックで破損が検出された場合は、FAST句を指定せずにCASCADEオプションを使用すると、破損箇所を特定できます。次の文では、emp表と、それに対応付けられているすべての索引の妥当性チェックを高速に実行します。
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE FAST;
妥当性をチェックするオブジェクトに対してDMLを実行している間でも、オンラインで構造の妥当性をチェックするように指定できます。オブジェクトに影響を与えるDML文と並行して妥当性チェックを実行すると、パフォーマンスがわずかに低下しますが、これはオンラインでANALYZE文を実行できる柔軟性によって相殺されます。次の文は、emp表と、それに対応付けられているすべての索引の妥当性をオンラインでチェックします。
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE ONLINE;
表またはクラスタの連鎖行と移行行は、LIST CHAINED ROWS句を指定したANALYZE文を使用して検出できます。この文の結果は、LIST CHAINED ROWS句によって返される情報を受け入れるために明示的に作成した指定の表に格納されます。この結果は、行を更新するための領域が十分であるかどうかを判断する上で役立ちます。
ANALYZE...LIST CHAINED ROWS文によって返されるデータを格納する表を作成するには、UTLCHAIN.SQLまたはUTLCHN1.SQLスクリプトを実行します。これらのスクリプトは、データベースに付属しています。これらのスクリプトは、スクリプトを実行するユーザーのスキーマ内にCHAINED_ROWSという名前の表を作成します。
CHAINED_ROWS表を作成した後、ANALYZE文のINTO句にその表を指定します。たとえば、次の文は、CHAINED_ROWS表に、emp_deptクラスタ内の連鎖行に関する情報を含む行を挿入します。
ANALYZE CLUSTER emp_dept LIST CHAINED ROWS INTO CHAINED_ROWS;
|
関連項目:
|
CHAINED_ROWS表の情報を使用すると、既存表内にある移行行と連鎖行を低減または解消できます。これには、次の手順を使用します。
ANALYZE文を使用して、移行行と連鎖行に関する情報を収集します。
ANALYZE TABLE order_hist LIST CHAINED ROWS;
SELECT * FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST'; OWNER_NAME TABLE_NAME CLUST... HEAD_ROWID TIMESTAMP ---------- ---------- -----... ------------------ --------- SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAA 04-MAR-96 SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAB 04-MAR-96 SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAC 04-MAR-96
移行行または連鎖行がすべてリストされます。
CREATE TABLE int_order_hist AS SELECT * FROM order_hist WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST');
DELETE FROM order_hist WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST');
INSERT INTO order_hist SELECT * FROM int_order_hist;
DROP TABLE int_order_history;
DELETE FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST';
ANALYZE文を使用してから、出力表を問い合せます。
出力表に表示された行は連鎖しています。連鎖行を解消するには、データ・ブロックのサイズを大きくする以外にありません。すべての状況において連鎖を回避することはほぼ不可能です。LONG列や大きいCHAR列またはVARCHAR2列を持つ表では、ほとんどの場合、連鎖の発生は避けられません。
表（またはクラスタ）は残したままで、内容が完全に空になるように、表のすべての行またはクラスタ化表のグループ内のすべての行を削除できます。たとえば、月ごとのデータが含まれている表では、各月の終わりにそのデータをアーカイブした後で、表を空にする（すべての行を削除する）必要があります。
表からすべての行を削除するには、次の3通りの方法があります。
次の項では、これらの方法について説明します。
DELETE文を使用して表の行を削除できます。たとえば、次の文はemp表からすべての行を削除します。
DELETE FROM emp;
DELETE文を使用するときに、表またはクラスタに多数の行が存在していると、それらの行を削除する際に相当のシステム・リソースが使用されます。たとえば、CPU時間、その表と対応付けられた索引のREDOログ領域、UNDOセグメント領域などのリソースが必要です。また、各行が削除されるときに、トリガーが起動される場合があります。結果的に空になる表またはクラスタに事前に割り当てられた領域は、行を削除してもそのオブジェクトに対応付けられたままです。DELETEを使用すると削除する行を選択できますが、TRUNCATEとDROPの場合はオブジェクト全体が削除されます。
表を削除してから再作成します。たとえば、次の例では、emp表を削除してから再作成しています。
DROP TABLE emp; CREATE TABLE emp ( ... );
表やクラスタを削除してから再作成すると、対応付けられた索引、整合性制約およびトリガーもすべて削除され、削除された表またはクラスタ化表に依存するオブジェクトはすべて無効になります。また、削除された表またはクラスタ化表に対する権限付与もすべて削除されます。
TRUNCATE文を使用して表のすべての行を削除できます。たとえば、次の文はemp表を切り捨てます。
TRUNCATE TABLE emp;
TRUNCATE文は、表またはクラスタからすべての行を削除するための高速で効率的な方法を提供します。TRUNCATE文はロールバック情報を生成せず、即時にコミットします。この文はデータ定義言語（DDL）であり、ロールバックできません。TRUNCATE文を実行しても、切り捨てられる表に対応付けられている構造（制約およびトリガー）または認可は影響を受けません。また、TRUNCATE文では、表を切り捨てた後で、表に現在割り当てられている領域を、その表を含む表領域に戻すかどうかも指定できます。
自分のスキーマにある表またはクラスタは切り捨てることができます。DROP ANY TABLEシステム権限を持っているユーザーは、どのスキーマ内の表またはクラスタでも切り捨てることができます。
親キーを含む表またはクラスタ化表を切り捨てる際は、別の表で定義されているすべての参照外部キーを事前に使用禁止にする必要があります。自己参照制約を使用禁止にする必要はありません。
TRUNCATE文によって表から行を削除する場合、表に対応付けられているトリガーは起動されません。また、TRUNCATE文は、監査が使用可能の場合でも、DELETE文に対応するどのような監査情報も生成しません。そのかわりに、発行されたTRUNCATE文に対して、単一の監査レコードが生成されます。 監査の詳細は、『Oracle Databaseセキュリティ・ガイド』を参照してください。
ハッシュ・クラスタや、ハッシュ・クラスタまたは索引クラスタ内の表を個別に切り捨てることはできません。索引クラスタを切り捨てると、そのクラスタ内のすべての表からすべての行が削除されます。個々のクラスタ化表からすべての行を削除する必要がある場合は、DELETE文を使用するか、または表を削除してから再作成してください。
TRUNCATE文のREUSE STORAGEまたはDROP STORAGEオプションは、切り捨てた後に、表またはクラスタに現在割り当てられている領域を、その表を含む表領域に戻すかどうかを制御します。デフォルトのオプションDROP STORAGEは、文実行後の表に割り当てられたエクステントの数をMINEXTENTSの元の設定まで減らします。解放されたエクステントはシステムに戻され、他のオブジェクトによって使用できます。
一方、REUSE STORAGEオプションを指定すると、表またはクラスタに対して現在割り当てられているすべての領域は割り当てられたままになります。たとえば、次の文はemp_deptクラスタを切り捨てて、クラスタに対してそれまでに割り当てられているすべてのエクステントを、今後の挿入と削除のためにそのまま残します。
TRUNCATE CLUSTER emp_dept REUSE STORAGE;
REUSEまたはDROP STORAGEオプションは、対応付けられたすべての索引に適用されます。表またはクラスタを切り捨てると、対応付けられた索引もすべて切り捨てられます。切り捨てられた表、クラスタまたは対応付けられた索引の記憶域パラメータは、切捨て後も変わりません。
データベース・トリガーとは、データベースに格納されており、表に行を追加するなどの特定の条件が発生したときにアクティブ化（起動）されるプロシージャです。トリガーを使用してデータベースの標準機能を補完することにより、データベース管理システムを高度にカスタマイズできます。たとえば、表に対するDML操作を制限するトリガーを作成して、通常の営業時間中に発行された文のみ許可できます。
データベース・トリガーは、表、スキーマまたはデータベースに対応付けることができます。データベース・トリガーは、次の場合に暗黙的に起動されます。
INSERT、UPDATE、DELETE）が実行されたとき
ALTER、CREATE、DROPなど）が実行されたとき
STARTUP、SHUTDOWN、SERVERERRORなど）
このリストがすべてではありません。 トリガーを起動する文とデータベース・イベントの詳細は、『Oracle Database SQLリファレンス』を参照してください。
トリガーを作成するには、CREATE TRIGGER文を使用します。トリガーは、トリガー・イベントの前（BEFORE）、後（AFTER）またはトリガー・イベントのかわりに（INSTEAD OF）起動するように定義できます。次の文は、表scott.empに対してトリガーscott.emp_permit_changesを作成します。このトリガーは、指定されたいずれかの文が実行される前に起動します。
CREATE TRIGGER scott.emp_permit_changes BEFORE DELETE OR INSERT OR UPDATE ON scott.emp . . . pl/sql block . . .
後でDROP TRIGGER文を発行し、トリガーをデータベースから削除できます。
トリガーには、次の2つのモードがあります。
トリガーが起動される文を発行したときに、トリガー制限（存在する場合）がTRUEと評価された場合は、使用可能トリガーによってトリガー本体が実行されます。デフォルトでは、トリガーを最初に作成したときに使用可能に設定されます。
トリガーが起動される文を発行したときに、トリガー制限（存在する場合）がTRUEと評価された場合でも、使用禁止トリガーはトリガー本体を実行しません。
ALTER TABLE文を使用してトリガーを使用可能または使用禁止にするには、表を所有しているか、表に対するALTERオブジェクト権限があるか、またはALTER ANY TABLEシステム権限があることが必要です。また、ALTER TRIGGER文を使用してトリガーを個別に使用可能または使用禁止にするには、トリガーを所有しているか、またはALTER ANY TRIGGERシステム権限を持っている必要があります。
使用禁止のトリガーを使用可能にするには、ENABLEオプションを指定したALTER TRIGGER文を使用します。たとえば、inventory表に定義されているreorderという使用禁止のトリガーを使用可能にするには、次の文を入力します。
ALTER TRIGGER reorder ENABLE;
ENABLE ALL TRIGGERSオプションを指定したALTER TABLE文を使用すれば、特定の表に定義されているトリガーをすべて使用可能にできます。たとえば、inventory表に定義されているトリガーをすべて使用可能にするには、次の文を入力します。
ALTER TABLE inventory ENABLE ALL TRIGGERS;
次の条件のいずれか1つが成り立つ場合は、一時的にトリガーを使用禁止にすることを検討してください。
トリガーを使用禁止にするには、DISABLEオプションを指定したALTER TRIGGER文を使用します。たとえば、inventory表に定義されているトリガーreorderを使用禁止にするには、次の文を入力します。
ALTER TRIGGER reorder DISABLE;
DISABLE ALL TRIGGERSオプションを指定したALTER TABLE文を使用すれば、表に関連するトリガーをすべて同時に使用禁止にできます。たとえば、inventory表に定義されているトリガーをすべて使用禁止にするには、次の文を入力します。
ALTER TABLE inventory DISABLE ALL TRIGGERS;
整合性制約とは、表の1つ以上の列に格納される値を制限するルールです。CREATE TABLE文またはALTER TABLE文に制約句を指定することにより、その制約の影響を受ける列と、制約の条件を識別できます。
ここでは、制約の概念と、整合性制約の定義および管理に使用するSQL文について説明します。この項の内容は、次のとおりです。
制約は、使用可能（ENABLE）と使用禁止（DISABLE）のいずれの状態にするかを指定できます。制約が使用可能になっている場合は、データベース内でデータが入力または更新されるときにチェックが行われ、制約に従っていないデータは入力されません。制約が使用禁止になっている場合は、ルールに従っていないデータでもデータベースに入力できます。
また、表の既存データが必ず制約に従うように指定できます（VALIDATE）。逆にNOVALIDATEを指定すると、既存データが制約に従っていることは保証されません。
表に定義されている整合性制約は、次のいずれかの状態にあります。
これらの状態の意味と組合せの結果の詳細は、『Oracle Database SQLリファレンス』を参照してください。ここでは、これらの結果のいくつかについて説明します。
整合性制約によって定義したルールを施行するには、その制約を常に使用可能にしておく必要があります。しかし、次のような場合は、パフォーマンス上の理由から、表の整合性制約を一時的に使用禁止にすることを検討してください。
これら3つの場合には、整合性制約を一時的に使用禁止にすることにより、操作のパフォーマンスを改善できます。これは、特にデータ・ウェアハウス構成に当てはまります。
制約が使用禁止である間は、その制約に違反するデータを入力できます。したがって、前述の操作を終了した後に、制約を必ず使用可能にする必要があります。
制約が使用可能になっている場合、制約に違反する行は表に挿入されません。しかし、制約が使用禁止の場合は、制約に違反する行でも表に挿入できます。このような行を制約の例外と呼びます。制約が妥当性チェックなしで使用可能な状態にある場合、制約が使用禁止になっていた間に入力された違反データはそのまま残っています。制約を妥当性チェック済みの状態にするためには、制約に違反する行を更新または削除する必要があります。
制約を使用可能にするときに、特定の整合性制約に対する例外を指定できます。「制約例外のレポート」を参照してください。制約に違反している行はすべてEXCEPTIONS表に格納され、検証できます。
制約が妥当性チェックなしで使用可能な状態にある場合、それ以後の文はすべて、制約に従っているかどうかがチェックされます。ただし、表の既存データはチェックされません。妥当性チェックなしで使用可能な状態の制約を持つ表には、無効なデータが含まれる可能性がありますが、無効なデータを新たに追加することはできません。妥当性チェックなしで使用可能な制約は、有効なオンライン・トランザクション処理（OLTP）データをアップロードしているデータ・ウェアハウス構成で役立ちます。
制約を使用可能にする場合に、妥当性チェックは必ずしも必要ではありません。妥当性チェックなしで制約を使用可能にする方が、妥当性チェックありで制約を使用可能にするよりはるかに高速です。また、すでに使用可能になっている制約の妥当性をチェックする場合、妥当性チェック中のDMLロックは必要ありません（すでに使用禁止にした制約の妥当性をチェックする場合とは異なります）。これは、制約の規定により、妥当性チェック中に違反データが挿入されないことが保証されているためです。したがって、妥当性チェックなしで使用可能にすれば、制約を使用可能にすることによって一般に生じる停止時間を短縮できます。
整合性制約の状態を次の順序で使用したときに、最も大きな利点が得られます。
制約をこの順序で使用する際の利点は、次のとおりです。
CREATE TABLE文またはALTER TABLE文で整合性制約を定義するときにENABLE/DISABLE句を指定して、その制約を使用可能/使用禁止、妥当性チェックあり/妥当性チェックなしの状態にすることができます。制約の定義時にENABLE/DISABLE句を指定しなければ、自動的にその制約は妥当性チェックありで使用可能な状態になります。
次のCREATE TABLE文とALTER TABLE文は、整合性制約を定義して、使用禁止にします。
CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY DISABLE, . . . ; ALTER TABLE emp ADD PRIMARY KEY (empno) DISABLE;
整合性制約を定義して使用禁止にするALTER TABLE文は、表の行がその整合性制約に違反しているために失敗することはありません。制約のルールが施行されていないので、制約の定義が許可されます。
次のCREATE TABLE文とALTER TABLE文は、整合性制約を定義して、使用可能にします。
CREATE TABLE emp ( empno NUMBER(5) CONSTRAINT emp.pk PRIMARY KEY, . . . ; ALTER TABLE emp ADD CONSTRAINT emp.pk PRIMARY KEY (empno);
整合性制約を定義して使用可能にするALTER TABLE文は、表の行が整合性制約に違反しているために失敗する場合があります。この場合、その文はロールバックされ、制約定義は格納されず、使用可能にもなりません。
UNIQUEまたはPRIMARY KEY制約を使用可能にすると、対応する索引が作成されます。
ALTER TABLE文では、制約を使用可能または使用禁止にする他、制約を変更または削除することもできます。制約を規定するためにUNIQUEまたはPRIMARY KEY索引が使用されている場合、その索引に対応する制約を削除または使用禁止にすると、明示的に指定しないかぎり、索引は削除されます。
使用可能な外部キーがPRIMARYキーまたはUNIQUEキーを参照している場合、PRIMARYキーまたはUNIQUEキーの制約またはその索引を削除したり使用禁止にしたりすることはできません。
次の文は、使用可能状態の整合性制約を使用禁止にします。2番目の文では、対応する索引を保持するように指定しています。
ALTER TABLE dept DISABLE CONSTRAINT dname_ukey; ALTER TABLE dept DISABLE PRIMARY KEY KEEP INDEX, DISABLE UNIQUE (dname, loc) KEEP INDEX;
次の文は、使用禁止状態の整合性制約を妥当性チェックなしで使用可能な状態にします。
ALTER TABLE dept ENABLE NOVALIDATE CONSTRAINT dname_ukey; ALTER TABLE dept ENABLE NOVALIDATE PRIMARY KEY, ENABLE NOVALIDATE UNIQUE (dname, loc);
次の文は、使用禁止状態の整合性制約を使用可能にするか、または妥当性チェックありの状態にします。
ALTER TABLE dept MODIFY CONSTRAINT dname_key VALIDATE; ALTER TABLE dept MODIFY PRIMARY KEY ENABLE NOVALIDATE;
次の文は、使用禁止状態の整合性制約を使用可能にします。
ALTER TABLE dept ENABLE CONSTRAINT dname_ukey; ALTER TABLE dept ENABLE PRIMARY KEY, ENABLE UNIQUE (dname, loc);
UNIQUEキーまたはPRIMARY KEY制約、およびすべての依存するFOREIGN KEY制約を一度に使用禁止または削除するには、DISABLE句またはDROP句のCASCADEオプションを使用します。たとえば、次の文はPRIMARY KEY制約とこれに依存するFOREIGN KEY制約を使用禁止にします。
ALTER TABLE dept DISABLE PRIMARY KEY CASCADE;
ALTER TABLE...RENAME CONSTRAINT文を使用すると、表に対する既存の制約の名前を変更できます。新しい制約名には、ユーザーの既存の制約名と競合しない名前を指定する必要があります。
次の文は、表deptに対するdname_ukey制約の名前を変更します。
ALTER TABLE dept RENAME CONSTRAINT dname_ukey TO dname_unikey;
制約名を変更しても、実表に対するすべての依存性は引き続き有効です。
RENAME CONSTRAINT句を使用すると、制約のシステム生成名を変更できます。
整合性制約は、規定するルールが成立しなくなった場合、またはその制約が不要になった場合に削除できます。制約を削除するには、ALTER TABLE文で次のいずれかの句を指定します。
次の2つの文は、整合性制約を削除します。2番目の文は、PRIMARY KEY制約に対応する索引を保持します。
ALTER TABLE dept DROP UNIQUE (dname, loc); ALTER TABLE emp DROP PRIMARY KEY KEEP INDEX, DROP CONSTRAINT dept_fkey;
FOREIGN KEYがUNIQUEまたはPRIMARY KEYを参照している場合は、DROP文にCASCADE CONSTRAINTS句を指定しないかぎり、制約を削除できません。
データベースが制約をチェックしたときに制約が満たされていない場合は、エラーが通知されます。制約の妥当性チェックは、トランザクションが終わるまで遅延できます。
SET CONSTRAINTS文を発行すると、トランザクションの実行中、または別のSET CONSTRAINTS文によってモードが再設定されるまで、SET CONSTRAINTSモードが継続します。
データ操作に使用するアプリケーションでは、実際にデータの処理を始める前にすべての制約を遅延に設定する必要があります。遅延可能制約をすべて遅延に設定するには、次のDML文を使用します。
SET CONSTRAINTS ALL DEFERRED;
COMMITの発行直前にSET CONSTRAINTS ALL IMMEDIATE文を発行することにより、制約違反をチェックできます。制約になんらかの問題があると、この文は失敗し、エラーの原因となっている制約が識別されます。制約違反のままコミットすると、トランザクションはロールバックされ、エラー・メッセージが返されます。
制約の妥当性チェック時に例外が存在すると、エラーが返され、整合性制約は妥当性チェックなしの状態のままになります。整合性制約の例外が存在しているために文が正常に実行されない場合、文はロールバックされます。例外が存在している場合は、制約の例外をすべて更新または削除するまで、制約の妥当性はチェックできません。
整合性制約に違反している行を判断するには、ENABLE句にEXCEPTIONSオプションを指定してALTER TABLE文を発行します。EXCEPTIONSオプションにより、例外を含むすべての行の行ID、表所有者、表名および制約名が指定した表に格納されます。
制約を使用可能にする前に、ENABLE句のEXCEPTIONSオプションからの情報を格納する適切な例外レポート表を作成する必要があります。例外表を作成するには、UTLEXCPT.SQLスクリプトまたはUTLEXPT1.SQLスクリプトを実行します。
これらのスクリプトのどちらを使用しても、EXCEPTIONSという名前の表が作成されます。また、スクリプトを変更して再実行すると、新たに別の名前の例外表を作成できます。
次の文は、dept表のPRIMARY KEYを検証します。例外が存在すると、EXCEPTIONS表に情報が挿入されます。
ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;
dept表に重複する主キー値が存在し、deptのPRIMARY KEY制約の名前がsys_c00610である場合は、次の問合せによって例外が表示されます。
SELECT * FROM EXCEPTIONS;
次の例外が表示されます。
fROWID OWNER TABLE_NAME CONSTRAINT ------------------ --------- -------------- ----------- AAAAZ9AABAAABvqAAB SCOTT DEPT SYS_C00610 AAAAZ9AABAAABvqAAG SCOTT DEPT SYS_C00610
次の文および結果のように、例外レポート表およびマスター表の行を結合した詳細な問合せの実行により、特定の制約に違反している実際の行を表示できます。
SELECT deptno, dname, loc FROM dept, EXCEPTIONS WHERE EXCEPTIONS.constraint = 'SYS_C00610' AND dept.rowid = EXCEPTIONS.row_id; DEPTNO DNAME LOC ---------- -------------- ----------- 10 ACCOUNTING NEW YORK 10 RESEARCH DALLAS
制約に違反している行はすべて更新するか、または制約を含む表から削除する必要があります。例外を更新する場合は、制約に違反する値を、制約を満たす値またはNULLに変更します。マスター表の行を更新または削除した後、以後取得する例外レポートとの混同を避けるために、例外レポート表の例外に対応する行は削除します。マスター表と例外レポート表を更新する文は、トランザクションの一貫性を保証するために、同じトランザクション内で実行してください。
前述の例の例外を訂正するために、次のトランザクションを発行できます。
UPDATE dept SET deptno = 20 WHERE dname = 'RESEARCH'; DELETE FROM EXCEPTIONS WHERE constraint = 'SYS_C00610'; COMMIT;
例外管理の最終的な目的は、例外レポート表の例外をすべて取り除くことにあります。
表の制約定義を表示し、制約で指定されている列を識別できるように、次のビューが用意されています。
オブジェクト名を変更するには、そのオブジェクトが自分のスキーマ内に存在する必要があります。スキーマ・オブジェクトは、次のいずれかの方法で名前を変更できます。
オブジェクトを削除して再作成する場合、そのオブジェクトに付与された権限はすべて失われます。オブジェクトを再作成するときに、再度権限を付与してください。
RENAME文を使用して、表、ビュー、順序またはそれらのプライベート・シノニムの名前を変更することもできます。RENAME文を使用すると、そのオブジェクトの整合性制約、索引および権限付与は新しい名前に引き継がれます。たとえば、次の文はsales_staffビューの名前を変更します。
RENAME sales_staff TO dept_30;
スキーマ・オブジェクト名を変更する前に、次のような影響について検討する必要があります。
ここでは、オブジェクト依存性とオブジェクトの無効化に関するバックグラウンド情報を提供し、無効なオブジェクトを再検証する方法について説明します。この項の内容は、次のとおりです。
スキーマ・オブジェクトには、他のオブジェクトを参照するタイプのものがあります。たとえば、ビューには表または他のビューを参照する問合せが含まれ、PL/SQLサブプログラムは他のサブプログラムを起動し、静的SQLを使用して表やビューを参照します。他のオブジェクトを参照するオブジェクトは依存オブジェクトと呼ばれ、参照されるオブジェクトは参照オブジェクトと呼ばれます。これらの参照はコンパイル時に確立され、コンパイラが参照を解決できない場合は、コンパイル対象の依存オブジェクトに無効のマークが付けられます。
Oracle Databaseには、依存オブジェクトが参照オブジェクトに関して常に最新であることを確認する自動メカニズムが用意されています。依存オブジェクトが作成されると、データベースによって、依存オブジェクトとその参照オブジェクト間の依存性が追跡されます。参照オブジェクトが依存オブジェクトに影響を与えるような方法で変更されると、依存オブジェクトには無効のマークが付けられます。無効になった依存オブジェクトは、参照オブジェクトの新しい定義で再コンパイルして使用できるようにする必要があります。再コンパイルは、無効な依存オブジェクトが参照されると自動的に実行されます。
スキーマ・オブジェクトを無効にする可能性のある変更に注意することは重要です。無効化により、データベース上で実行されているアプリケーションが影響を受けるためです。ここでは、オブジェクトがどのように無効化されるか、および無効になったオブジェクトをどのように識別し検証するかについて説明します。
アプリケーションの通常の実行では、ビューやストアド・プロシージャが無効になることはありません。アプリケーションでは普通、実行中に表の構造を変更したり、ビューやストアド・プロシージャの定義を変更することはないためです。表やビュー、PL/SQLユニットが変更されるのは、通常、パッチ・スクリプトや非定型のDDL文を使用して、アプリケーションにパッチを適用したり、アプリケーションをアップグレードする場合です。一連の参照オブジェクトを変更するパッチを適用した後は、依存オブジェクトが無効のままになっている可能性があります。
データベース内の無効な一連のオブジェクトを表示するには、次の問合せを使用します。
SELECT object_name, object_type FROM dba_objects WHERE status = 'INVALID';
スキーマ・オブジェクトが無効になると、Enterprise Managerのデータベース・ホームページにアラートが表示されます。
オブジェクトの無効化によって、アプリケーションは次の2つの影響を受けます。第1に、無効なオブジェクトは、再検証されるまでアプリケーションで使用できません。再検証によって、アプリケーション実行の待機時間が長くなります。無効なオブジェクトが多数ある場合は、初回実行時の待機時間が長時間になる可能性があります。第2に、プロシージャ、ファンクションまたはパッケージの無効化によって、そのプロシージャ、ファンクションまたはパッケージを同時に実行している他のセッションで例外が発生する可能性があります。アプリケーションを別のセッションで使用しているときにパッチを適用すると、アプリケーションを実行しているセッションによって、使用中のオブジェクトが無効化されたことが通知され、ORA-4061、ORA-4064、ORA-4065またはORA-4068の4つの例外のうちのいずれか１つが発生します。これらの例外は、パッチ適用後にアプリケーション・セッションを再起動して修正する必要があります。
適切なSQL文にCOMPILE句を指定して、スキーマ・オブジェクトを強制的に再コンパイルできます。詳細は、「DDLを使用した手動による無効なオブジェクトの再コンパイル」を参照してください。
無効なオブジェクトが多数存在することが判明している場合は、UTL_RECOMP PL/SQLパッケージを使用して一括再コンパイルを実行します。詳細は、「PL/SQLパッケージのプロシージャを使用した手動による無効なオブジェクトの再コンパイル」を参照してください。
次に、スキーマ・オブジェクトの無効化に関する一般的な規則をいくつか示します。
したがって、開発者がスキーマ・オブジェクトの変更時に注意することにより、多くの場合、依存オブジェクトの無効化とそれによるデータベースへの不要な追加作業の発生を回避することができます。
単一のスキーマ・オブジェクトを手動で再コンパイルするには、ALTER文を使用します。たとえば、パッケージ本体のPkg1を再コンパイルするには、次のDDL文を実行します。
ALTER PACKAGE pkg1 COMPILE REUSE SETTINGS;
アプリケーションのアップグレードまたはパッチ適用後に無効なオブジェクトを再検証しておくと、オブジェクトの必要時に再検証が行われることによるアプリケーションの待機時間の発生を回避できます。Oracleには、オブジェクトの再検証を支援するUTL_RECOMPパッケージが用意されています。RECOMP_SERIALプロシージャは、特定のスキーマの無効なオブジェクトすべてを再コンパイルします。スキーマ名の引数が指定されていない場合は、データベース内の無効なオブジェクトすべてを再コンパイルします。RECOMP_PARALLELプロシージャも同様に機能しますが、複数のCPUを利用してパラレルに処理する点が異なります。
次のPL/SQLブロックを実行して、データベース内の無効なオブジェクトすべてをパラレルに、依存順序に従って再検証します。
begin utl_recomp.recomp_parallel(); end;
DBMS_UTILITYパッケージを使用して、無効なオブジェクトを個別に再検証することもできます。次のスクリプトは、HRスキーマのUPDATE_SALARYプロシージャを再検証するPL/SQLブロックです。
begin dbms_utility.validate('HR', 'UPDATE_SALARY', namespace=>1); end;
次のスクリプトは、パッケージ本体のHR.ACCT_MGMTを再検証するPL/SQLブロックです。
begin dbms_utility.validate('HR', 'ACCT_MGMT', namespace=>2); end;
SQL文で参照されるオブジェクト名は、ピリオドで区切られた複数の断片から構成できます。ここでは、データベースでオブジェクト名を解決する方法を説明します。
scott.empの最初の断片はscottです。断片が1つしか存在しない場合、その断片は最初の断片とみなされます。
手順cでスキーマが検出されない場合、そのオブジェクトは識別できず、エラーが返されます。
scott.emp.deptnoで、scottがスキーマとして識別され、empが表として識別された場合は、（empが表であるため）deptnoは列に対応する必要があります。また、empがパッケージとして識別された場合、deptnoはそのパッケージのパブリック定数、変数、プロシージャまたはファンクションに対応する必要があります。
分散データベースにおいて、グローバル・オブジェクト名が明示的またはシノニム内で間接的に使用されている場合、ローカル・データベースはローカルで参照を解決します。たとえば、シノニムをリモート表のグローバル・オブジェクト名として解決します。部分的に解決された文はリモート・データベースに転送され、前述の手順に従って、リモート・データベースでオブジェクトの解決が行われます。
データベースによる参照の解決方法の関係で、あるオブジェクトが、他のオブジェクトが存在しないことに依存している可能性があります。この状況が発生するのは、依存するオブジェクトが使用している参照の解析方法が、他のオブジェクトが存在しているときには異なる場合です。たとえば、次のような場合を考えてみます。
companyスキーマに表empが含まれています。
company.empに対してパブリック・シノニムempが作成され、company.empに対するSELECT権限がPUBLICロールに付与されます。
jwardスキーマには、表またはプライベート・シノニムempは含まれていません。
jwardが、次の文を使用して自分のスキーマにビューを作成します。
CREATE VIEW dept_salaries AS SELECT deptno, MIN(sal), AVG(sal), MAX(sal) FROM emp GROUP BY deptno ORDER BY deptno;
jwardがdept_salariesビューを作成すると、empへの参照は、jward.empを表、ビューまたはプライベート・シノニムとして検索し、いずれも見つからない場合はパブリック・シノニムempとして検索して見つけることで解決されます。その結果、jward.dept_salariesは、jward.empが存在しないことと、public.empが存在することに依存していることがわかります。
ここで、jwardが次の文を使用して自分のスキーマに新しいビューempを作成するとします。
CREATE VIEW emp AS SELECT empno, ename, mgr, deptno FROM company.emp;
jward.empの構造がcompany.empとは異なることに注意してください。
データベースは、オブジェクト定義内で参照を解決するときに、新しい依存オブジェクトの、存在しないオブジェクト（スキーマ・オブジェクト）への依存性に内部的に注目します。このスキーマ・オブジェクトが存在する場合は、オブジェクトの定義の解析が変化します。存在しないオブジェクトを後で作成する場合は、この種の依存性に注意する必要があります。存在しないオブジェクトを作成する場合は、依存オブジェクトを再コンパイルして検証できるように、すべての依存オブジェクトを無効にする必要があります。また、依存するすべてのファンクション索引を使用禁止としてマークする必要があります。
したがって、前述の例では、jward.empが作成されると、jward.dept_salariesはjward.empに依存するため無効になります。その後、jward.dept_salariesが使用されると、データベースはビューの再コンパイルを試みます。empへの参照を解決するときに、jward.empが見つかります（public.empは参照先のオブジェクトではなくなっています）。jward.empにはsal列がないため、ビューを置換するときにエラーが見つかり、ビューは無効のままになります。
要約すると、存在しないオブジェクトを後で作成する場合は、オブジェクトの解決中にチェックされる存在しないオブジェクトへの依存性を管理する必要があります。
次の文は、現行セッションのスキーマを、この文で指定するスキーマ名に設定します。
ALTER SESSION SET CURRENT_SCHEMA = <schema name>
その後のSQL文では、修飾子が省略されている場合に、Oracle Databaseによって、このスキーマ名がスキーマ修飾子として使用されます。また、データベースでは、指定したスキーマの一時表領域が、一時データベース・オブジェクトのソート、結合および格納に使用されます。セッションには元の権限が保持され、前述のALTER SESSION文によって余分な権限は取得されません。
次の例では、プロンプトが表示されたらtigerというパスワードを入力します。
CONNECT scott ALTER SESSION SET CURRENT_SCHEMA = joe; SELECT * FROM emp;
empは識別されたスキーマではないため、表名はjoeスキーマのもとで解決されます。ただし、scottがjoe.emp表の選択権限を持たない場合、scottはSELECT文を実行できません。
Oracle DatabaseにはPL/SQLパッケージが用意されており、スキーマ・オブジェクト情報の表示に使用できるオブジェクトおよびデータ・ディクショナリ・ビューを作成したDDLを判断できます。特定のタイプのスキーマ・オブジェクトに固有のビューとパッケージは、関連する章に記載されています。ここでは、汎用的な性質を持ち、複数のスキーマ・オブジェクトに適用されるビューとパッケージについて説明します。
オラクル社が提供するPL/SQLパッケージDBMS_METADATA.GET_DDLを使用すると、スキーマ・オブジェクトに関するメタデータを（オブジェクトの作成に使用するDDLの形式で）取得できます。
DBMS_METADATAパッケージは、スキーマ・オブジェクトの完全な定義を取得できる強力なツールです。このパッケージを使用すると、あるオブジェクトのすべての属性を1回のパスで取得できます。オブジェクトは、その作成（再作成）に使用できるDDLで表されます。
次の文では、GET_DDLファンクションを使用して、現行スキーマ内にあるすべての表のDDLをフェッチし、ネストした表とオーバーフロー・セグメントを除外しています。また、DDLで記憶域句が返されないようにするため、SET_TRANSFORM_PARAM（ハンドル値として「現行セッション用」を意味するDBMS_METADATA.SESSION_TRANSFORMをとる）を使用してそれを指定しています。セッション・レベルの変換パラメータは、最後にデフォルトにリセットされています。変換パラメータ値は、いったん設定すると、明示的にデフォルトにリセットされるまで有効です。
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_ALL_TABLES u WHERE u.nested='NO' AND (u.iot_type is null or u.iot_type='IOT'); EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');
DBMS_METADATA.GET_DDLの出力は、LONGデータ型です。SQL*Plusを使用している場合は、出力がデフォルトで切り捨てられる場合があります。出力が切り捨てられないようにするには、DBMS_METADATA.GET_DDL文を発行する前に、次のSQL*Plusコマンドを発行してください。
SQL> SET LONG 9999
次のビューには、スキーマ・オブジェクトに関する一般的な情報が表示されます。
次に、これらのビューの使用例を示します。
次の問合せは、問合せを発行しているユーザーが所有しているオブジェクトをすべてリストします。
SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS;
問合せの出力は次のとおりです。
OBJECT_NAME OBJECT_TYPE ------------------------- ------------------- EMP_DEPT CLUSTER EMP TABLE DEPT TABLE EMP_DEPT_INDEX INDEX PUBLIC_EMP SYNONYM EMP_MGR VIEW
ビューまたはシノニムを作成するとき、ビューやシノニムはその基礎になるベース・オブジェクトに基づきます。ビューの依存性を明確にするには、ALL_DEPENDENCIES、USER_DEPENDENCIESおよびDBA_DEPENDENCIESデータ・ディクショナリ・ビューを使用します。シノニムのベース・オブジェクトのリストを表示するには、ALL_SYNONYMS、USER_SYNONYMSおよびDBA_SYNONYMSデータ・ディクショナリ・ビューを使用します。たとえば、次の問合せは、ユーザーjwardによって作成されたシノニムのベース・オブジェクトをリストします。
SELECT TABLE_OWNER, TABLE_NAME, SYNONYM_NAME FROM DBA_SYNONYMS WHERE OWNER = 'JWARD';
問合せの出力は次のとおりです。
TABLE_OWNER TABLE_NAME SYNONYM_NAME ---------------------- ----------- ----------------- SCOTT DEPT DEPT SCOTT EMP EMP
