As traduções são geradas por tradução automática. Em caso de conflito entre o conteúdo da tradução e da versão original em inglês, a versão em inglês prevalecerá.
# Análise das dependências de objetos em migrações parciais de banco de dados da Oracle para o PostgreSQL
*Anuradha Chintha, Amazon Web Services*
## Resumo
Este padrão descreve a importância de identificar e gerenciar sistematicamente as dependências do sistema ao migrar parcialmente um banco de dados da Oracle para o Amazon Relational Database Service (Amazon RDS) ou para o Amazon Aurora compatível com PostgreSQL. Em uma migração parcial, apenas um subconjunto de objetos e dados do banco de dados original é migrado, enquanto o banco de dados de origem continua operando e atendendo às aplicações que dependem de componentes não migrados.
É necessário identificar e analisar o escopo da migração ao lidar com bancos de dados de grande porte que contam com aplicações fortemente acopladas com dependências upstream e downstream. Para iniciar uma migração parcial, identifique os objetos do escopo, incluindo tabelas, acionadores, visualizações, procedimentos armazenados, funções e pacotes. O processo de identificação do escopo segue uma abordagem abrangente:
+ Os objetos de escopo de primeiro nível são identificados por meio de referências diretas no código da aplicação e em trabalhos críticos específicos de módulos.
+ Os objetos de segundo nível são obtidos por meio de uma análise abrangente de dependências.
Ao compreender como as diversas partes do seu sistema se relacionam, é possível planejar de forma mais adequada a sequência correta para migrar os componentes do banco de dados e diminuir o risco de falhas na migração. A tabela a seguir lista os diferentes tipos de análise de dependências.
|
|
| Tipo de análise | Áreas de foco | Finalidade |
| --- |--- |--- |
| Dependências de objetos | TabelasVisualizaçõesProcedimentos armazenadosFunçõesAcionadores | Identificar relacionamentos entre os objetos do banco de dados e suas estruturas hierárquicas |
| Dependências de segmento | Relacionamentos de chave estrangeiraCadeias de chave primáriaReferências entre esquemas | Mapear os relacionamentos de dados e manter a integridade referencial |
| Dependências de segurança | Permissões de usuárioHierarquias de perfisPrivilégios de objeto | Garantir a migração adequada do controle de acesso e a manutenção da segurança |
| Padrões de acesso | Operações de leituraOperações de gravação | Determinar os padrões de interação com o banco de dados |
Para manter a consistência entre os sistemas de origem e de destino, estabeleça mecanismos de sincronização de dados durante o período de transição. Além disso, é necessário modificar o código da aplicação e as funções para lidar com a distribuição de dados entre os bancos de dados de origem da Oracle e de destino do PostgreSQL.
## Pré-requisitos e limitações
**Pré-requisitos **
+ Um ativo Conta da AWS
+ Um banco de dados da Oracle (origem)
+ Um Amazon RDS ou Amazon Aurora compatível com PostgreSQL (destino)
**Versões do produto**
+ Oracle 19c ou posterior
+ PostgreSQL 16 ou posterior
## Arquitetura
**Pilha de tecnologia de origem**
+ Oracle 19c ou posterior
**Pilha de tecnologias de destino**
+ Amazon RDS ou Amazon Aurora compatível com PostgreSQL
**Arquitetura de destino**
O diagrama apresentado a seguir ilustra o processo de migração de um banco de dados da Oracle on-premises para o Amazon RDS para Oracle, que envolve:
+ Identificação das dependências do banco de dados
+ Migrando código e objetos do banco de dados usando AWS Schema Conversion Tool ()AWS SCT
+ Migração de dados usando AWS Database Migration Service ()AWS DMS
+ Replicando mudanças contínuas por meio da captura de dados de alteração (CDC) usando AWS DMS
Para obter mais informações, consulte [Integração AWS Database Migration Service com AWS Schema Conversion Tool](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_DMSIntegration.html) na AWS documentação.
## Ferramentas
**Serviços da AWS**
+ O [Amazon Relational Database Service (Amazon RDS)](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Oracle.html) para Oracle ajuda você a configurar, operar e escalar um banco de dados relacional da Oracle na Nuvem AWS.
+ O Amazon Aurora é um mecanismo de banco de dados relacional totalmente gerenciado criado para a nuvem e compatível com o MySQL e o PostgreSQL.
+ AWS Schema Conversion Tool (AWS SCT) oferece suporte a migrações heterogêneas de bancos de dados convertendo automaticamente o esquema do banco de dados de origem e a maior parte do código personalizado em um formato compatível com o banco de dados de destino.
+ [AWS Database Migration Service (AWS DMS)](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html) ajuda você a migrar armazenamentos de dados para Nuvem AWS ou entre combinações de configurações na nuvem e no local.
**Outros serviços**
+ O [Oracle SQL Developer](https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html) é um ambiente de desenvolvimento integrado que simplifica o desenvolvimento e o gerenciamento de bancos de dados Oracle em implantações tradicionais e baseadas em nuvem. Para este padrão, você pode usar [SQL\*Plus](https://docs.oracle.com/cd/B19306_01/server.102/b14357/qstart.htm).
## Práticas recomendadas
Para obter as práticas recomendadas sobre provisionamento e migração de um banco de dados da Oracle, consulte [Best practices for migrating to Amazon RDS for Oracle](https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-oracle-database/best-practices.html).
## Épicos
### Identificação das dependências de objetos
| Tarefa | Description | Habilidades necessárias |
| --- | --- | --- |
| Crie uma tabela de objetos. | Identifique os objetos que são essenciais para a funcionalidade da aplicação e crie uma tabela chamada `DEPENDENT_ANALYSIS_BASELINE`. Adicione registros para cada objeto na tabela. Para obter um exemplo, consulte a seção * Informações adicionais*. | Engenheiro de dados, DBA |
| Crie um procedimento de banco de dados. | Crie um procedimento armazenado chamado `sp_object_dependency_analysis` para analisar as dependências de objetos nas duas direções (para frente e para trás) usando os dados da tabela `DBA_DEPENDENCIES`. Para obter um exemplo, consulte a seção * Informações adicionais*. | Engenheiro de dados, DBA |
| Execute o procedimento. | Execute os scripts em cada nível sucessivo até que não sejam encontradas novas dependências de objetos. Todas as dependências e os níveis são armazenados na tabela `DEPENDENT_ANALYSIS_BASELINE`. | DBA, engenheiro de dados |
### Criação de um procedimento para dependências em nível de segmento
| Tarefa | Description | Habilidades necessárias |
| --- | --- | --- |
| Crie uma tabela de dependências. | Crie uma tabela de dependência em nível de segmento chamada `REFERENTIAL_ANALYSIS_BASELINE`. Quando todas as dependências em nível de objeto forem identificadas, verifique as tabelas principais de `DEPENDENT_ANALYSIS_BASELINE`, consultando a tabela `DBA_CONSTRAINT`.
Exclua dependências em que as tabelas de referência da linha de base sejam mencionadas por outras tabelas. O preenchimento é responsável por tratar esses relacionamentos. A seguir, apresentamos um script de exemplo: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));
| Engenheiro de dados, DBA |
| Crie um procedimento de banco de dados. | Crie um procedimento chamado `SP_OBJECT_REFERENTIAL_ANALYSIS` e gere uma análise referencial para todos os objetos identificados. Para obter um exemplo, consulte a seção * Informações adicionais*. | Engenheiro de dados, DBA |
| Execute o procedimento. | Execute o procedimento para obter as dependências referenciais. Gere os detalhes do objeto de análise referencial em `REFERENTIAL_ANALYSIS_BASELINE`. | Engenheiro de dados, DBA |
### Identificação de objetos que realizam operações de leitura e gravação
| Tarefa | Description | Habilidades necessárias |
| --- | --- | --- |
| Crie tabelas para objetos de leitura e gravação. | Use o seguinte script para criar uma tabela de objetos de leitura chamada `TABLE_READ_OBJECT_DETAILS` e uma tabela de objetos de gravação chamada `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));
| Engenheiro de dados, DBA |
| Crie um procedimento para análise. | Crie os procedimentos `SP_READER_OBJECTS_ANALYSIS` e `SP_WRITER_OBJECTS_ANALYSIS` para analisar, respectivamente, os objetos de leitura e de gravação. Esses procedimentos usam correspondência de padrões para encontrar objetos relacionados. Para obter um exemplo, consulte a seção *Informações adicionais*. | Engenheiro de dados, DBA |
| Execute os procedimentos. | Execute esses procedimentos para identificar os objetos com dependência. | DBA, engenheiro de dados |
### Análise dos privilégios do banco de dados
| Tarefa | Description | Habilidades necessárias |
| --- | --- | --- |
| Crie uma tabela para analisar privilégios. | Crie uma tabela para analisar privilégios chamada `OBJECT_PRIVS_ANALYSIS`. Para capturar recursivamente os privilégios de objetos na tabela `DEPENDENT_ANALYSIS_BASELINE`, use o seguinte script:CREATE TABLE OBJECT_PRIVS_ANALYSIS
(OWNER VARCHAR2(50 BYTE),
OBJECT_NAME VARCHAR2(50 BYTE),
USER_NAME VARCHAR2(50 BYTE),
PRIVS VARCHAR2(50 BYTE));
| Engenheiro de dados, DBA |
| Crie um procedimento para analisar privilégios. | Crie um procedimento chamado `SP_OBJECT_PRIVS_ANALYSIS`. Gere uma análise de privilégios para os objetos identificados. Para obter um exemplo, consulte a seção * Informações adicionais*. | DBA, engenheiro de dados |
| Execute o procedimento. | Execute o procedimento para capturar os dados na tabela `OBJECT_PRIVS_ANALYSIS`. | DBA, engenheiro de dados |
## Solução de problemas
| Problema | Solução |
| --- | --- |
| Não é possível acessar as tabelas do dicionário | Certifique-se de que o usuário que criou os objetos de análise consegue acessar as tabelas do DBA. |
## Recursos relacionados
**Documentação da AWS**
+ [Documentação do Amazon RDS e do 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)
+ [O que é 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/)
**Outras documentações**
+ [Oracle database objects](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Database-Objects.html)
## Mais informações
**Script para `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));
```
**Procedimento para `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 para `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 para `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 para `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;
```
**Procedimento para `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;
```