Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.
# Analyser les dépendances des objets pour les migrations partielles de bases de données d'Oracle vers PostgreSQL
*anuradha chintha, Amazon Web Services*
## Résumé
Ce modèle décrit l'importance d'identifier et de gérer systématiquement les dépendances du système lors de la migration d'une base de données Oracle partielle vers Amazon Relational Database Service (Amazon RDS) ou Amazon Aurora PostgreSQL. Lors d'une migration partielle, seul un sous-ensemble d'objets de base de données et de données de la base de données d'origine est migré, tandis que la base de données source continue de fonctionner et de servir les applications qui dépendent de composants non migrés.
Vous devez identifier et analyser l'étendue de la migration lorsque vous manipulez des bases de données à grande échelle comportant des applications étroitement couplées avec des dépendances en amont et en aval. Pour commencer une migration partielle, identifiez les objets du scope, notamment les tables, les déclencheurs, les vues, les procédures stockées, les fonctions et les packages. Le processus d'identification du périmètre suit une approche globale :
+ Les objets du scope de premier niveau sont identifiés par des références directes dans le code de l'application et dans les tâches critiques spécifiques aux modules.
+ Les objets de second niveau sont dérivés grâce à une analyse complète des dépendances.
Lorsque vous comprenez comment les différentes parties de votre système interagissent, vous pouvez mieux planifier l'ordre correct pour déplacer les composants de base de données et réduire le risque d'échec de la migration. Le tableau suivant répertorie les différents types d'analyse de dépendance.
|
|
| Type d'analyse | Domaines d'intérêt | Objectif |
| --- |--- |--- |
| Dépendances des objets | TablesVuesProcédures stockéesFonctionsTriggers | Identifie les relations entre les objets de base de données et leurs structures hiérarchiques |
| Dépendances des segments | Relations clés avec des acteurs étrangersPorte-clés primairesRéférences entre schémas | Cartographie les relations entre les données et préserve l'intégrité référentielle |
| Dépendances de sécurité | Autorisations des utilisateursHiérarchies de rôlesPrivilèges d'objet | Garantit une migration appropriée du contrôle d'accès et de la maintenance de la sécurité |
| Modèles d’accès | Opérations de lectureOpérations d’écriture | Détermine les modèles d'interaction avec les bases |
Pour maintenir la cohérence entre les systèmes source et cible, établissez des mécanismes de synchronisation des données pendant la période de transition. Vous devez également modifier le code et les fonctions de l'application pour gérer la distribution des données dans les bases de données Oracle source et PostgreSQL cible.
## Conditions préalables et limitations
**Conditions préalables**
+ Un actif Compte AWS
+ Une base de données Oracle (source)
+ Amazon RDS ou Amazon Aurora PostgreSQL (cible)
**Versions du produit**
+ Oracle 19c ou version ultérieure
+ PostgreSQL 16 ou version ultérieure
## Architecture
**Pile technologique source**
+ Oracle 19c ou version ultérieure
**Pile technologique cible**
+ Amazon RDS ou Amazon Aurora PostgreSQL
**Architecture cible**
Le schéma suivant montre le processus de migration d'une base de données Oracle sur site vers Amazon RDS for Oracle, qui implique les étapes suivantes :
+ Identifier les dépendances des bases de données
+ Migration du code et des objets de base de données à l'aide de AWS Schema Conversion Tool ()AWS SCT
+ Migration de données à l'aide de AWS Database Migration Service ()AWS DMS
+ Répliquer les changements en cours grâce à la capture des données sur les modifications (CDC) à l'aide de AWS DMS
Pour plus d'informations, consultez la section [Intégration AWS Database Migration Service avec AWS Schema Conversion Tool](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_DMSIntegration.html) dans la AWS documentation.
![\[alt text not found\]](http://docs.aws.amazon.com/fr_fr/prescriptive-guidance/latest/patterns/images/pattern-img/90160825-3199-4382-95a8-ad63139c5c89/images/b09c36a4-27fa-412e-877e-57a31bcce0dc.png)
## Outils
**Services AWS**
+ [Amazon Relational Database Service (Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Oracle.html)) pour Oracle vous aide à configurer, exploiter et dimensionner une base de données relationnelle Oracle dans le. AWS Cloud
+ Amazon Aurora est un moteur de base de données relationnelle entièrement géré conçu pour le cloud et compatible avec MySQL et PostgreSQL.
+ AWS Schema Conversion Tool (AWS SCT) prend en charge les migrations de bases de données hétérogènes en convertissant automatiquement le schéma de base de données source et la majorité du code personnalisé dans un format compatible avec la base de données cible.
+ [AWS Database Migration Service (AWS DMS)](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html) vous aide à migrer les banques de données vers AWS Cloud ou entre des combinaisons de configurations cloud et sur site.
**Autres services**
+ [Oracle SQL Developer](https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html) est un environnement de développement intégré qui simplifie le développement et la gestion des bases de données Oracle dans les déploiements traditionnels et basés sur le cloud. Pour ce modèle, vous pouvez utiliser [SQL\$1Plus](https://docs.oracle.com/cd/B19306_01/server.102/b14357/qstart.htm).
## Bonnes pratiques
Pour connaître les meilleures pratiques relatives au provisionnement et à la migration d'une base de données Oracle, consultez la section [Meilleures pratiques pour la migration vers Amazon](https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-oracle-database/best-practices.html) RDS for Oracle.
## Épopées
### Identifier les dépendances des objets
| Sous-tâche | Description | Compétences requises |
| --- | --- | --- |
| Créez une table d'objets. | Identifiez les objets essentiels aux fonctionnalités de l'application et créez une table nommée`DEPENDENT_ANALYSIS_BASELINE`. Ajoutez des enregistrements pour chaque objet dans le tableau. Pour un exemple, consultez la section *Informations supplémentaires*. | Ingénieur de données, DBA |
| Créez une procédure de base de données. | Créez une procédure stockée nommée `sp_object_dependency_analysis` pour analyser les dépendances des objets dans les deux sens (avant et arrière) en utilisant les données du `DBA_DEPENDENCIES` tableau. Pour un exemple, consultez la section *Informations supplémentaires*. | Ingénieur de données, DBA |
| Exécutez la procédure. | Exécutez les scripts à chaque niveau successif jusqu'à ce qu'aucune nouvelle dépendance d'objet ne soit trouvée. Toutes les dépendances et tous les niveaux sont stockés dans le `DEPENDENT_ANALYSIS_BASELINE` tableau. | DBA, Ingénieur de données |
### Création d'une procédure pour les dépendances au niveau des segments
| Sous-tâche | Description | Compétences requises |
| --- | --- | --- |
| Créez une table de dépendances. | Créez une table de dépendance au niveau du segment nommée. `REFERENTIAL_ANALYSIS_BASELINE` Lorsque toutes les dépendances au niveau de l'objet sont découvertes, vérifiez les tables parents de `DEPENDENT_ANALYSIS_BASELINE` en interrogeant la table. `DBA_CONSTRAINT`Excluez les dépendances lorsque les tables de référence sont référencées par d'autres tables. Le remblayage gère ces relations. Voici un exemple de script :CREATE TABLE REFERENTIAL_ANALYSIS_BASELINE
(CHILD_OWNER VARCHAR2(50 BYTE),
CHILD_NAME VARCHAR2(100 BYTE),
PARENT_OWNER VARCHAR2(50 BYTE),
PARENT_NAME VARCHAR2(50 BYTE),
REFERENCE_PATH VARCHAR2(1000 BYTE));
| Ingénieur de données, DBA |
| Créez une procédure de base de données. | Créez une procédure appelée `SP_OBJECT_REFERENTIAL_ANALYSIS` et générez une analyse référentielle pour tous les objets identifiés. Pour un exemple, consultez la section *Informations supplémentaires*. | Ingénieur de données, DBA |
| Exécutez la procédure. | Exécutez la procédure pour obtenir les dépendances référentielles. Générez les détails des objets d'analyse référentielle dans. `REFERENTIAL_ANALYSIS_BASELINE` | Ingénieur de données, DBA |
### Identifiez les objets qui lisent et écrivent
| Sous-tâche | Description | Compétences requises |
| --- | --- | --- |
| Créez des tables pour les objets de lecture et d'écriture. | Utilisez le script suivant pour créer une table d'objets de lecture nommée `TABLE_READ_OBJECT_DETAILS` et une table d'objets d'écriture nommée `TABLE_WRITE_OBJECT_DETAILS` :CREATE TABLE TABLE_READ_OBJECT_DETAILS
(OWNER VARCHAR2(50 BYTE),
TAB_NAME VARCHAR2(50 BYTE),
READER_OWNER VARCHAR2(50 BYTE),
READER_NAME VARCHAR2(50 BYTE),
READER_TYPE VARCHAR2(50 BYTE));
CREATE TABLE TABLE_WRITE_OBJECT_DETAILS
(TABLE_NAME VARCHAR2(100 BYTE),
WRITEOBJ_OWNER VARCHAR2(100 BYTE),
WRITEOBJ_NAME VARCHAR2(100 BYTE),
WRITEOBJ_TYPE VARCHAR2(100 BYTE),
LINE VARCHAR2(100 BYTE),
TEXT VARCHAR2(4000 BYTE),
OWNER VARCHAR2(50 BYTE));
| Ingénieur de données, DBA |
| Créez une procédure d'analyse. | Créez les procédures `SP_READER_OBJECTS_ANALYSIS` et `SP_WRITER_OBJECTS_ANALYSIS` pour analyser les objets de lecture et les objets d'écriture, respectivement. Ces procédures utilisent la correspondance de modèles pour rechercher les objets associés. Pour un exemple, consultez la section *Informations supplémentaires*. | Ingénieur de données, DBA |
| Exécutez les procédures. | Exécutez ces procédures pour identifier les objets dépendants. | DBA, Ingénieur de données |
### Vérifier les privilèges de base de données
| Sous-tâche | Description | Compétences requises |
| --- | --- | --- |
| Créez un tableau pour vérifier les privilèges. | Créez un tableau pour analyser les privilèges nommés`OBJECT_PRIVS_ANALYSIS`. Pour capturer de manière récursive les privilèges des objets dans le `DEPENDENT_ANALYSIS_BASELINE` tableau, utilisez le script suivant :CREATE TABLE OBJECT_PRIVS_ANALYSIS
(OWNER VARCHAR2(50 BYTE),
OBJECT_NAME VARCHAR2(50 BYTE),
USER_NAME VARCHAR2(50 BYTE),
PRIVS VARCHAR2(50 BYTE));
| Ingénieur de données, DBA |
| Créez une procédure de révision des privilèges. | Créez une procédure nommée`SP_OBJECT_PRIVS_ANALYSIS`. Générez une analyse des privilèges pour les objets identifiés. Pour un exemple, consultez la section *Informations supplémentaires*. | DBA, Ingénieur de données |
| Exécutez la procédure. | Exécutez la procédure pour les capturer dans le `OBJECT_PRIVS_ANALYSIS` tableau. | DBA, Ingénieur de données |
## Résolution des problèmes
| Problème | Solution |
| --- | --- |
| Impossible d'accéder aux tables du dictionnaire | Assurez-vous que l'utilisateur qui a créé les objets d'analyse peut accéder aux tables DBA. |
## Ressources connexes
**Documentation AWS**
+ [Documentation Amazon RDS et Aurora](https://docs.aws.amazon.com/rds/)
+ [Manuel de migration de la base de données Oracle 19c vers Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.html)
+ [Qu'est-ce que c'est AWS Database Migration Service ?](https://docs.aws.amazon.com/dms/latest/userguide/)
+ [Qu'est-ce que l'AWS Schema Conversion Tool ?](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/)
**Autres documentations**
+ [Objets de base de données Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Database-Objects.html)
## Informations supplémentaires
**Script pour `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));
```
**Procédure pour `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;
```
**Script pour `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;
```
**Script pour `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;
```
**Script pour `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;
```
**Procédure pour `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;
```