翻訳は機械翻訳により提供されています。提供された翻訳内容と英語版の間で齟齬、不一致または矛盾がある場合、英語版が優先します。

# Oracle から PostgreSQL への部分的なデータベース移行に関するオブジェクトの依存関係を分析する
<a name="multilevel-object-analysis-for-database-migration-from-oracle-to-postgresql"></a>

*Amazon Web Services、Anuradha Chintha*

## 概要
<a name="multilevel-object-analysis-for-database-migration-from-oracle-to-postgresql-summary"></a>

このパターンでは、部分的な Oracle データベースを Amazon Relational Database Service (Amazon RDS) または Amazon Aurora PostgreSQL に移行するときに、システムの依存関係を体系的に識別および管理することの重要性について説明します。部分的な移行では、元のデータベースからのデータベースオブジェクトとデータのサブセットのみが移行されますが、ソースデータベースでは移行されていないコンポーネントに依存するアプリケーションを引き続き運用および提供します。

アップストリームとダウンストリームの依存関係と緊密に結合したアプリケーションを持つ大規模なデータベースを処理する場合は、移行の範囲を特定して分析する必要があります。部分的な移行を開始するには、テーブル、トリガー、ビュー、ストアドプロシージャ、関数、パッケージなどのスコープオブジェクトを特定します。スコープ識別プロセスは、以下の包括的なアプローチに従って進めます。
+ 第 1 レベルのスコープオブジェクトは、アプリケーションコードと重要なモジュール固有のジョブの直接参照によって識別されます。
+ 第 2 レベルのオブジェクトは、包括的な依存関係分析によって導出されます。

システムのさまざまな部分がどのように相互作用するかを理解すると、データベースコンポーネントを移動するための正しい順序をより適切に計画し、移行失敗のリスクを減らすことができます。次の表に、さまざまなタイプの依存関係分析を示します。


| 
| 
| 分析タイプ | 焦点 | 目的 | 
| --- |--- |--- |
| オブジェクトの依存関係 | テーブルビューストアドプロシージャ関数トリガ | データベースオブジェクトとその階層構造間の関係を識別する | 
| セグメントの依存関係 | 外部キー関係プライマリキーチェーンクロススキーマリファレンス | データ関係をマッピングし、参照整合性を維持する | 
| セキュリティ依存関係 | ユーザーアクセス許可ロール階層オブジェクト権限 | 適切なアクセスコントロールの移行とセキュリティメンテナンスを行う | 
| アクセスパターン | 読み込みオペレーション書き込みオペレーション | データベースインタラクションパターンを決定する | 

ソースシステムとターゲットシステム間の一貫性を維持するには、移行期間中にデータ同期メカニズムを確立します。また、ソース Oracle データベースとターゲット PostgreSQL データベースの両方でデータ分散を処理するように、アプリケーションコードと関数を変更する必要があります。

## 前提条件と制限
<a name="multilevel-object-analysis-for-database-migration-from-oracle-to-postgresql-prereqs"></a>

**前提条件**
+ アクティブな AWS アカウント
+ Oracle データベース (ソース)
+ Amazon RDS または Amazon Aurora PostgreSQL (ターゲット)

**製品バージョン**
+ Oracle 19c 以降
+ PostgreSQL 16 以降

## アーキテクチャ
<a name="multilevel-object-analysis-for-database-migration-from-oracle-to-postgresql-architecture"></a>

**ソーステクノロジースタック**
+ Oracle 19c 以降

**ターゲットテクノロジースタック**
+ Amazon RDS または Amazon Aurora PostgreSQL

**ターゲットアーキテクチャ**

次の図は、オンプレミスの Oracle データベースから Amazon RDS for Oracle への移行プロセスを示しています。これには以下が含まれます。
+ 依存関係の特定
+  AWS Schema Conversion Tool (AWS SCT) を使用したデータベースコードとオブジェクトの移行
+  AWS Database Migration Service (AWS DMS) を使用したデータの移行
+ を使用した変更データキャプチャ (CDC) による継続的な変更のレプリケート AWS DMS

詳細については、 AWS ドキュメントの[「 AWS Database Migration Service との統合 AWS Schema Conversion Tool](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_DMSIntegration.html)」を参照してください。

![](http://docs.aws.amazon.com/ja_jp/prescriptive-guidance/latest/patterns/images/pattern-img/90160825-3199-4382-95a8-ad63139c5c89/images/b09c36a4-27fa-412e-877e-57a31bcce0dc.png)


## ツール
<a name="multilevel-object-analysis-for-database-migration-from-oracle-to-postgresql-tools"></a>

**AWS のサービス**
+ Oracle の [Amazon Relational Database Service (Amazon RDS)](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Oracle.html) を活用することで、 AWS クラウド上で Oracle リレーショナルデータベースのセットアップ、運用、スケーリングができます。
+ 「Amazon Aurora」はクラウド用に構築されたフルマネージド型のリレーショナルデータベースエンジンで、MySQL および PostgreSQL と互換性があります。
+ AWS Schema Conversion Tool (AWS SCT) は、ソースデータベーススキーマとカスタムコードの大部分をターゲットデータベースと互換性のある形式に自動的に変換することで、異種データベースの移行をサポートします。
+ [AWS Database Migration Service (AWS DMS)](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html) を使用すると、データストアを に移行する AWS クラウド か、クラウドとオンプレミスのセットアップの組み合わせ間で移行できます。

**その他のサービス**
+ [Oracle SQL Developer](https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html) は、従来のデプロイとクラウドベースのデプロイの両方で Oracle データベースの開発と管理を簡素化する統合開発環境です。このパターンでは、[SQL\*Plus](https://docs.oracle.com/cd/B19306_01/server.102/b14357/qstart.htm) を使用できます。

## ベストプラクティス
<a name="multilevel-object-analysis-for-database-migration-from-oracle-to-postgresql-best-practices"></a>

Oracle データベースのプロビジョニングと移行に関するベストプラクティスについては、「[Best practices for migrating to Amazon RDS for Oracle](https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-oracle-database/best-practices.html)」をご確認ください。

## エピック
<a name="multilevel-object-analysis-for-database-migration-from-oracle-to-postgresql-epics"></a>

### オブジェクト依存関係の識別
<a name="identify-object-dependencies"></a>


| タスク | 説明 | 必要なスキル | 
| --- | --- | --- | 
| オブジェクトテーブルを作成します。 | アプリケーションの機能に不可欠なオブジェクトを特定し、`DEPENDENT_ANALYSIS_BASELINE` という名前のテーブルを作成します。各オブジェクトのレコードをテーブルに追加します。サンプルは「*追加情報*」セクションからご確認ください。 | データエンジニア、DBA | 
| データベースプロシージャを作成します。 | `DBA_DEPENDENCIES` テーブルのデータを使用して、オブジェクトの依存関係を双方向 (前後) で分析する `sp_object_dependency_analysis` という名前のストアドプロシージャを作成します。サンプルは「*追加情報*」セクションからご確認ください。 | データエンジニア、DBA | 
| プロシージャを実行します。 | 新しいオブジェクトの依存関係が見つからなくなるまで、連続する各レベルでスクリプトを実行します。すべての依存関係とレベルが `DEPENDENT_ANALYSIS_BASELINE` テーブルに保存されます。 | DBA、データエンジニア | 

### セグメントレベルの依存関係のプロシージャを作成する
<a name="create-a-procedure-for-segment-level-dependencies"></a>


| タスク | 説明 | 必要なスキル | 
| --- | --- | --- | 
| 依存関係テーブルを作成します。 | `REFERENTIAL_ANALYSIS_BASELINE` という名前のセグメントレベルの依存関係テーブルを作成します。すべてのオブジェクトレベルの依存関係が検出されたら、`DBA_CONSTRAINT` テーブルをクエリして `DEPENDENT_ANALYSIS_BASELINE` の親テーブルを確認します。<br />ベースラインテーブルが他のテーブルによって参照される依存関係を除外します。バックフィルは、これらの関係を処理します。スクリプトの例を次に示します。<pre>CREATE TABLE REFERENTIAL_ANALYSIS_BASELINE<br />(CHILD_OWNER VARCHAR2(50 BYTE),<br />CHILD_NAME VARCHAR2(100 BYTE),<br />PARENT_OWNER VARCHAR2(50 BYTE),<br />PARENT_NAME VARCHAR2(50 BYTE),<br />REFERENCE_PATH VARCHAR2(1000 BYTE));</pre> | データエンジニア、DBA | 
| データベースプロシージャを作成します。 | `SP_OBJECT_REFERENTIAL_ANALYSIS` というプロシージャを作成し、識別されたすべてのオブジェクトの参照分析を生成します。サンプルは「*追加情報*」セクションからご確認ください。 | データエンジニア、DBA | 
| プロシージャを実行します。 | 手順を実行して、参照依存関係を取得します。`REFERENTIAL_ANALYSIS_BASELINE` で参照分析オブジェクトの詳細を生成します。 | データエンジニア、DBA | 

### 読み取りと書き込みを行うオブジェクトを特定する
<a name="identify-objects-that-read-and-write"></a>


| タスク | 説明 | 必要なスキル | 
| --- | --- | --- | 
| 読み取りおよび書き込みオブジェクトのテーブルを作成します。 | 次のスクリプトを使用して、`TABLE_WRITE_OBJECT_DETAILS` という名前の読み取りオブジェクトテーブルと `TABLE_READ_OBJECT_DETAILS` という名前の書き込みオブジェクトテーブルを作成します。<pre>CREATE TABLE TABLE_READ_OBJECT_DETAILS<br />(OWNER VARCHAR2(50 BYTE),<br />TAB_NAME VARCHAR2(50 BYTE),<br />READER_OWNER VARCHAR2(50 BYTE),<br />READER_NAME VARCHAR2(50 BYTE),<br />READER_TYPE VARCHAR2(50 BYTE));</pre><pre>CREATE TABLE TABLE_WRITE_OBJECT_DETAILS<br />(TABLE_NAME VARCHAR2(100 BYTE),<br />WRITEOBJ_OWNER VARCHAR2(100 BYTE),<br />WRITEOBJ_NAME VARCHAR2(100 BYTE),<br />WRITEOBJ_TYPE VARCHAR2(100 BYTE),<br />LINE VARCHAR2(100 BYTE),<br />TEXT VARCHAR2(4000 BYTE),<br />OWNER VARCHAR2(50 BYTE));</pre> | データエンジニア、DBA | 
| 分析のためのプロシージャを作成します。 | 読み取りオブジェクトと書き込みオブジェクトをそれぞれ分析するためのプロシージャ `SP_READER_OBJECTS_ANALYSIS` と `SP_WRITER_OBJECTS_ANALYSIS` を作成します。これらのプロシージャは、パターンマッチングを使用して関連オブジェクトを検索します。サンプルは「*追加情報*」セクションからご確認ください。 | データエンジニア、DBA | 
| プロシージャを実行します。 | プロシージャを実行して、依存オブジェクトを識別します。 | DBA、データエンジニア | 

### データベース権限を確認する
<a name="review-database-privileges"></a>


| タスク | 説明 | 必要なスキル | 
| --- | --- | --- | 
| 権限を確認するためのテーブルを作成します。 | 権限分析を行う `OBJECT_PRIVS_ANALYSIS` という名称のテーブルを作成します。`DEPENDENT_ANALYSIS_BASELINE` テーブルでオブジェクト権限を再帰的にキャプチャするには、次のスクリプトを使用します。<pre>CREATE TABLE OBJECT_PRIVS_ANALYSIS<br />(OWNER VARCHAR2(50 BYTE),<br />OBJECT_NAME VARCHAR2(50 BYTE),<br />USER_NAME VARCHAR2(50 BYTE),<br />PRIVS VARCHAR2(50 BYTE));</pre> | データエンジニア、DBA | 
| 権限を確認するためのプロシージャを作成します。 | `SP_OBJECT_PRIVS_ANALYSIS` という名称のプロシージャを作成します。識別されたオブジェクトの権限分析を生成します。サンプルは「*追加情報*」セクションからご確認ください。 | DBA、データエンジニア | 
| プロシージャを実行します。 | プロシージャを実行し、`OBJECT_PRIVS_ANALYSIS` テーブルにキャプチャします。 | DBA、データエンジニア | 

## トラブルシューティング
<a name="multilevel-object-analysis-for-database-migration-from-oracle-to-postgresql-troubleshooting"></a>


| 問題 | ソリューション | 
| --- | --- | 
| ディクショナリテーブルにアクセスできない | 分析オブジェクトを作成したユーザーが DBA テーブルにアクセスできることを確認します。 | 

## 関連リソース
<a name="multilevel-object-analysis-for-database-migration-from-oracle-to-postgresql-resources"></a>

**AWS ドキュメント**
+ [Amazon RDS および Aurora ドキュメント](https://docs.aws.amazon.com/rds/)
+ [Oracle database 19c to Amazon Aurora PostgreSQL migration playbook](https://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.html)
+ [とは AWS Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/)
+ [What is the AWS Schema Conversion Tool?](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/)

**その他のドキュメント**
+ [Oracle データベースオブジェクト](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Database-Objects.html)

## 追加情報
<a name="multilevel-object-analysis-for-database-migration-from-oracle-to-postgresql-additional"></a>

**`DEPENDENT_ANALYSIS_BASELINE` のスクリプト**

```
CREATE TABLE DEPENDENT_ANALYSIS_BASELINE
(OWNER VARCHAR2(128 BYTE) NOT NULL ENABLE,
OBJECT_NAME VARCHAR2(128 BYTE) NOT NULL ENABLE,
OBJECT_TYPE VARCHAR2(20 BYTE),
DEPEDNCY_LEVEL NUMBER,
PROJECT_NEED VARCHAR2(20 BYTE),
CATAGORY VARCHAR2(4000 BYTE),
COMMENTS VARCHAR2(4000 BYTE),
CATAGORY1 CLOB,
COMMENTS1 CLOB,
CUSTOMER_COMMENTS VARCHAR2(1000 BYTE),
BACKFILL_TO_GUS VARCHAR2(1000 BYTE),
BACKFILL_NEAR_REAL_TIME_OR_BATCH VARCHAR2(1000 BYTE),
PK_EXISTS VARCHAR2(3 BYTE),
UI_EXISTS VARCHAR2(3 BYTE),
LOB_EXISTS VARCHAR2(3 BYTE),
MASTER_LINK VARCHAR2(100 BYTE),
CONSTRAINT PK_DEPENDENT_ANALYSIS_BASELINE PRIMARY KEY (OWNER,OBJECT_NAME,OBJECT_TYPE));
```

**`SP_WRITER_OBJECTS_ANALYSIS` のプロシージャ**

```
CREATE OR REPLACE PROCEDURE SP_WRITER_OBJECTS_ANALYSIS IS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TABLE_WRITE_OBJECT_DETAILS';
  FOR I IN (SELECT OWNER, OBJECT_NAME FROM DEPENDENT_ANALYSIS_BASELINE WHERE OBJECT_TYPE = 'TABLE')
  LOOP
    INSERT INTO TABLE_WRITE_OBJECT_DETAILS(OWNER, TABLE_NAME, WRITEOBJ_OWNER, WRITEOBJ_NAME, WRITEOBJ_TYPE, LINE, TEXT)
    SELECT DISTINCT I.OWNER, I.OBJECT_NAME, OWNER WRITEOBJ_OWNER, NAME, TYPE, LINE, TRIM(TEXT)
    FROM DBA_SOURCE 
    WHERE UPPER(TEXT) LIKE '%' || I.OBJECT_NAME || '%'
      AND (UPPER(TEXT) LIKE '%INSERT%' || I.OBJECT_NAME || '%' 
        OR UPPER(TEXT) LIKE '%UPDATE%' || I.OBJECT_NAME || '%' 
        OR UPPER(TEXT) LIKE '%DELETE%' || I.OBJECT_NAME || '%' 
        OR UPPER(TEXT) LIKE '%UPSERT%' || I.OBJECT_NAME || '%' 
        OR UPPER(TEXT) LIKE '%MERGE%' || I.OBJECT_NAME || '%') 
      AND UPPER(TEXT) NOT LIKE '%PROCEDURE%' 
      AND UPPER(TEXT) NOT LIKE 'PROCEDURE%' 
      AND UPPER(TEXT) NOT LIKE '%FUNCTION%' 
      AND UPPER(TEXT) NOT LIKE 'FUNCTION%'
      AND UPPER(TEXT) NOT LIKE '%TRIGGER%' 
      AND UPPER(TEXT) NOT LIKE 'TRIGGER%' 
      AND UPPER(TRIM(TEXT)) NOT LIKE '%AFTER UPDATE%' 
      AND UPPER(TRIM(TEXT)) NOT LIKE 'BEFORE UPDATE%' 
      AND UPPER(TRIM(TEXT)) NOT LIKE 'BEFORE INSERT%' 
      AND UPPER(TRIM(TEXT)) NOT LIKE 'AFTER INSERT%' 
      AND UPPER(TRIM(TEXT)) NOT LIKE 'BEFORE DELETE%' 
      AND UPPER(TRIM(TEXT)) NOT LIKE 'AFTER DELETE%' 
      AND UPPER(TRIM(TEXT)) NOT LIKE '%GGLOGADM.GG_LOG_ERROR%' 
      AND (TRIM(TEXT) NOT LIKE '/*%' AND TRIM(TEXT) NOT LIKE '--%' ) 
      AND (OWNER, NAME, TYPE) IN (SELECT OWNER, NAME, TYPE FROM DBA_DEPENDENCIES WHERE REFERENCED_NAME = I.OBJECT_NAME);
  END LOOP;
END;
```

**`SP_READER_OBJECTS_ANALYSIS` のスクリプト**

```
CREATE OR REPLACE PROCEDURE SP_READER_OBJECTS_ANALYSIS IS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TABLE_READ_OBJECT_DETAILS';
  FOR I IN (SELECT OWNER, OBJECT_NAME FROM DEPENDENT_ANALYSIS_BASELINE WHERE OBJECT_TYPE = 'TABLE')
  LOOP
    INSERT INTO TABLE_READ_OBJECT_DETAILS
    SELECT DISTINCT i.owner, i.object_name, owner, name, type 
    FROM dba_dependencies 
    WHERE referenced_name = I.OBJECT_NAME
    AND referenced_type = 'TABLE' 
    AND type NOT IN ('SYNONYM', 'MATERIALIZED VIEW', 'VIEW') 
    AND (owner, name, type) NOT IN (
      SELECT DISTINCT owner, trigger_name, 'TRIGGER' 
      FROM dba_triggers 
      WHERE table_name = I.OBJECT_NAME 
      AND table_owner = i.owner
      UNION ALL
      SELECT DISTINCT owner, name, type 
      FROM dba_source
      WHERE upper(text) LIKE '%' || I.OBJECT_NAME || '%' 
      AND (upper(text) LIKE '%INSERT %' || I.OBJECT_NAME || '%' 
        OR upper(text) LIKE '%UPDATE% ' || I.OBJECT_NAME || '%' 
        OR upper(text) LIKE '%DELETE %' || I.OBJECT_NAME || '%' 
        OR upper(text) LIKE '%UPSERT %' || I.OBJECT_NAME || '%' 
        OR upper(text) LIKE '%MERGE %' || I.OBJECT_NAME || '%') 
      AND upper(text) NOT LIKE '%PROCEDURE %' 
      AND upper(text) NOT LIKE 'PROCEDURE %'
      AND upper(text) NOT LIKE '%FUNCTION %' 
      AND upper(text) NOT LIKE 'FUNCTION %'
      AND upper(text) NOT LIKE '%TRIGGER %'
      AND upper(text) NOT LIKE 'TRIGGER %'
      AND upper(trim(text)) NOT LIKE 'BEFORE INSERT %'
      AND upper(trim(text)) NOT LIKE 'BEFORE UPDATE %' 
      AND upper(trim(text)) NOT LIKE 'BEFORE DELETE %' 
      AND upper(trim(text)) NOT LIKE 'AFTER INSERT %' 
      AND upper(trim(text)) NOT LIKE 'AFTER UPDATE %' 
      AND upper(trim(text)) NOT LIKE 'AFTER DELETE %' 
      AND (trim(text) NOT LIKE '/*%' AND trim(text) NOT LIKE '--%'));
  END LOOP;
END;
```

**`SP_OBJECT_REFERENTIAL_ANALYSIS` のスクリプト**

```
CREATE OR REPLACE PROCEDURE SP_OBJECT_REFERENTIAL_ANALYSIS IS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE REFERENTIAL_ANALYSIS_BASELINE';
  INSERT INTO REFERENTIAL_ANALYSIS_BASELINE
  WITH rel AS (
    SELECT DISTINCT c.owner, c.table_name, c.r_owner r_owner,
      (SELECT table_name FROM dba_constraints 
       WHERE constraint_name = c.r_constraint_name 
       AND owner = c.r_owner) r_table_name 
    FROM dba_constraints c 
    WHERE constraint_type = 'R' 
    AND c.owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')
    AND c.r_owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')),
  tab_list AS (
    SELECT OWNER, object_name 
    FROM DEPENDENT_ANALYSIS_BASELINE 
    WHERE UPPER(OBJECT_TYPE) = 'TABLE')
  SELECT DISTINCT owner child_owner, table_name child, r_owner parent_owner,
    r_table_name parent, SYS_CONNECT_BY_PATH(r_table_name, ' -> ') || ' -> ' || table_name PATH
  FROM rel 
  START WITH (r_owner, r_table_name) IN (SELECT * FROM tab_list)
  CONNECT BY NOCYCLE (r_owner, r_table_name) = ((PRIOR owner, PRIOR table_name))
  UNION
  SELECT DISTINCT owner child_owner, table_name child, r_owner parent_owner,
    r_table_name parent, SYS_CONNECT_BY_PATH(table_name, ' -> ') || ' -> ' || r_table_name PATH
  FROM rel 
  START WITH (owner, table_name) IN (SELECT * FROM tab_list)
  CONNECT BY NOCYCLE (owner, table_name) = ((PRIOR r_owner, PRIOR r_table_name));
END;
```

**`SP_OBJECT_PRIVS_ANALYSIS` のスクリプト**

```
CREATE OR REPLACE PROCEDURE SP_OBJECT_PRIVS_ANALYSIS IS
  V_SQL VARCHAR2(4000);
  V_CNT NUMBER;
BEGIN
  V_SQL := 'TRUNCATE TABLE OBJECT_PRIVS_ANALYSIS';
  EXECUTE IMMEDIATE V_SQL;
  FOR I IN (SELECT OWNER, OBJECT_NAME FROM DEPENDENT_ANALYSIS_BASELINE WHERE OBJECT_TYPE = 'TABLE')
  LOOP
    INSERT INTO OBJECT_PRIVS_ANALYSIS(OWNER, OBJECT_NAME, USER_NAME, PRIVS)
    WITH obj_to_role AS (
      SELECT DISTINCT GRANTEE role_name, 
        DECODE(privilege, 'SELECT', 'READ', 'REFERENCE', 'READ', 'INSERT', 'WRITE', 
               'UPDATE', 'WRITE', 'DELETE', 'WRITE', privilege) privs
      FROM DBA_TAB_PRIVS t, DBA_ROLES r 
      WHERE OWNER = I.OWNER 
      AND TYPE = 'TABLE' 
      AND TABLE_NAME = I.OBJECT_NAME 
      AND t.GRANTEE = r.ROLE 
      AND r.ROLE IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED = 'N')
    )
    SELECT I.OWNER, I.OBJECT_NAME, grantee, privs 
    FROM (
      -- Recursively Role to User mapping with privilege
      SELECT DISTINCT grantee, privs 
      FROM (SELECT rp.granted_role, rp.grantee, privs,
        (SELECT DECODE(COUNT(*), 0, 'ROLE', 'USER') 
         FROM (SELECT 'User' FROM DBA_users WHERE username = rp.GRANTEE)) grantee_type 
        FROM DBA_role_privs rp, obj_to_role r 
        WHERE rp.granted_role = r.role_name 
        AND grantee IN ((SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED = 'N') 
                       UNION (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED = 'N'))
        AND granted_role IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED = 'N') 
        START WITH granted_role IN (SELECT DISTINCT role_name FROM obj_to_role) 
        CONNECT BY granted_role = PRIOR grantee) 
      WHERE grantee_type = 'USER'
    )
    UNION
    (
      -- Direct Object grants to User
      SELECT I.OWNER, I.OBJECT_NAME, GRANTEE, 
        DECODE(privilege, 'SELECT', 'READ', 'REFERENCE', 'READ', 'INSERT', 'WRITE',
               'UPDATE', 'WRITE', 'DELETE', 'WRITE', privilege) privs 
      FROM DBA_TAB_PRIVS, DBA_USERS 
      WHERE GRANTEE = USERNAME 
      AND OWNER = I.OWNER 
      AND TYPE = 'TABLE' 
      AND TABLE_NAME = I.OBJECT_NAME
    ) 
    ORDER BY 2 DESC;
  END LOOP;
END;
```

**`SP_OBJECT_DEPENDENCY_ANALYSIS` のプロシージャ**

```
CREATE OR REPLACE PROCEDURE SP_OBJECT_DEPENDENCY_ANALYSIS (v_level NUMBER) IS
  TYPE typ IS RECORD (
    schema VARCHAR2(100),
    obj_type VARCHAR2(100),
    obj_name VARCHAR2(100),
    path VARCHAR2(5000)
  );
  TYPE array IS TABLE OF typ;
  l_data array;
  c SYS_REFCURSOR;
  l_errors NUMBER;
  l_errno NUMBER;
  l_msg VARCHAR2(4000);
  l_idx NUMBER;
  l_level NUMBER;
BEGIN
  l_level := v_level + 1;
  OPEN c FOR 
    WITH obj_list AS (
      SELECT owner schema_name, object_type, object_name 
      FROM DEPENDENT_ANALYSIS_BASELINE 
      WHERE depedncy_level = v_level
    ),
    fw_dep_objects AS (
      SELECT level lvl, owner, name, type, referenced_owner, referenced_name,
        referenced_type, SYS_CONNECT_BY_PATH(name, ' -> ') || ' -> ' || referenced_name PATH 
      FROM dba_dependencies
      START WITH (owner, CASE WHEN type = 'PACKAGE BODY' THEN 'PACKAGE' ELSE type END, name) 
        IN (SELECT schema_name, object_type, object_name FROM obj_list)
      CONNECT BY NOCYCLE (owner, type, name) = 
        ((PRIOR referenced_owner, PRIOR referenced_type, PRIOR referenced_name))
    ),
    bw_dep_objects AS (
      SELECT level lvl, owner, name, type, referenced_owner, referenced_name,
        referenced_type, SYS_CONNECT_BY_PATH(name, ' <- ') || ' <- ' || referenced_name PATH 
      FROM dba_dependencies
      START WITH (referenced_owner, CASE WHEN referenced_type = 'PACKAGE BODY' THEN 'PACKAGE' 
        ELSE referenced_type END, referenced_name) IN (SELECT schema_name, object_type, object_name FROM obj_list)
      CONNECT BY NOCYCLE (referenced_owner, referenced_type, referenced_name) = 
        ((PRIOR owner, PRIOR type, PRIOR name))
    )
    SELECT * FROM (
      (SELECT DISTINCT referenced_owner schema, referenced_type obj_type, 
        referenced_name obj_name, path FROM fw_dep_objects)
      UNION
      (SELECT DISTINCT owner schema, type obj_type, name obj_name, path 
       FROM bw_dep_objects)
    )
    WHERE schema IN (SELECT username FROM all_users WHERE oracle_maintained = 'N')
    ORDER BY obj_type;

  LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT 100;
    BEGIN
      FORALL i IN 1..l_data.count SAVE EXCEPTIONS
        INSERT INTO DEPENDENT_ANALYSIS_BASELINE (
          owner, object_name, object_type, catagory, depedncy_level, project_need, comments
        ) 
        VALUES (
          l_data(i).schema, 
          l_data(i).obj_name,
          CASE WHEN l_data(i).obj_type = 'PACKAGE BODY' THEN 'PACKAGE' ELSE l_data(i).obj_type END,
          'level ' || l_level || ' dependency',
          l_level,
          '',
          'from dependency proc' || l_data(i).path
        );
    EXCEPTION
      WHEN OTHERS THEN
        l_errors := sql%bulk_exceptions.count;
        FOR i IN 1..l_errors LOOP
          l_errno := sql%bulk_exceptions(i).error_code;
          l_msg := SQLERRM(-l_errno);
          l_idx := sql%bulk_exceptions(i).error_index;
          UPDATE DEPENDENT_ANALYSIS_BASELINE 
          SET catagory1 = catagory1 || ', found in level' || l_level || ' dependent of ' || l_data(l_idx).path,
              comments1 = comments1 || ', from dependency proc exception ' || l_data(i).path
          WHERE owner = l_data(l_idx).schema 
          AND object_name = l_data(l_idx).obj_name 
          AND object_type = l_data(l_idx).obj_type;
        END LOOP;
    END;
    EXIT WHEN c%NOTFOUND;
  END LOOP;
  CLOSE c;
END;
```