기계 번역으로 제공되는 번역입니다. 제공된 번역과 원본 영어의 내용이 상충하는 경우에는 영어 버전이 우선합니다.
# Oracle에서 PostgreSQL로의 부분 데이터베이스 마이그레이션에 대한 객체 종속성 분석
*anuradha 중국, Amazon Web Services*
## 요약
이 패턴은 부분 Oracle 데이터베이스를 Amazon Relational Database Service(RDS) 또는 Amazon Aurora PostgreSQL로 마이그레이션할 때 시스템 종속성을 체계적으로 식별하고 관리하는 것의 중요성을 설명합니다. 부분 마이그레이션에서는 원본 데이터베이스의 데이터베이스 객체 및 데이터의 하위 집합만 마이그레이션되는 반면, 소스 데이터베이스는 마이그레이션되지 않은 구성 요소에 의존하는 애플리케이션을 계속 운영하고 제공합니다.
애플리케이션을 업스트림 및 다운스트림 종속성과 긴밀하게 결합한 대규모 데이터베이스를 처리할 때는 마이그레이션 범위를 식별하고 분석해야 합니다. 부분 마이그레이션을 시작하려면 테이블, 트리거, 뷰, 저장 프로시저, 함수 및 패키지를 포함한 범위 객체를 식별합니다. 범위 식별 프로세스는 포괄적인 접근 방식을 따릅니다.
+ 1단계 범위 객체는 애플리케이션 코드 및 중요한 모듈별 작업의 직접 참조를 통해 식별됩니다.
+ 2단계 객체는 포괄적인 종속성 분석을 통해 파생됩니다.
시스템의 여러 부분이 상호 작용하는 방식을 이해하면 데이터베이스 구성 요소를 이동하는 올바른 순서를 더 잘 계획하고 마이그레이션 실패 리스크를 줄일 수 있습니다. 다음 표에는 다양한 유형의 종속성 분석이 나열되어 있습니다.
|
|
| 분석 유형 | 중점 영역 | 용도 |
| --- |--- |--- |
| 객체 종속성 | 테이블뷰저장 프로시저함수트리거 | 데이터베이스 객체와 해당 계층 구조 간의 관계를 식별합니다. |
| 세그먼트 종속성 | 외래 키 관계기본 키 체인교차 스키마 참조 | 데이터 관계를 매핑하고 참조 무결성을 유지합니다. |
| 보안 종속성 | 사용자 권한역할 계층 구조객체 권한 | 적절한 액세스 제어 마이그레이션 및 보안 유지 관리 보장 |
| 액세스 패턴 | 읽기 작업쓰기 작업 | 데이터베이스 상호 작용 패턴을 결정합니다. |
소스 시스템과 대상 시스템 간의 일관성을 유지하려면 전환 기간 동안 데이터 동기화 메커니즘을 설정합니다. 또한 소스 Oracle 및 대상 PostgreSQL 데이터베이스 모두에서 데이터 배포를 처리하도록 애플리케이션 코드와 함수를 수정해야 합니다.
## 사전 조건 및 제한 사항
**사전 조건 **
+ 활성 AWS 계정
+ 소스: Oracle Database
+ 대상: Amazon RDS for PostgreSQL/Amazon Aurora PostgreSQL
**제품 버전**
+ Oracle 19c 이상
+ PostgreSQL 16 이상
## 아키텍처
**소스 기술 스택 **
+ Oracle 19c 이상
**대상 기술 스택**
+ Amazon RDS for PostgreSQL / 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)을 참조하세요.
![\[alt text not found\]](http://docs.aws.amazon.com/ko_kr/prescriptive-guidance/latest/patterns/images/pattern-img/90160825-3199-4382-95a8-ad63139c5c89/images/b09c36a4-27fa-412e-877e-57a31bcce0dc.png)
## 도구
**AWS 서비스**
+ Oracle용 Amazon Relational Database Service(RDS)는 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\$1Plus](https://docs.oracle.com/cd/B19306_01/server.102/b14357/qstart.htm)를 사용할 수 있습니다.
## 모범 사례
Oracle 데이터베이스 프로비저닝 및 마이그레이션에 대한 모범 사례는 [Amazon RDS for Oracle로 마이그레이션하는 모범 사례](https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-oracle-database/best-practices.html)를 참조하세요.
## 에픽
### 종속성 파악
| 작업 | 설명 | 필요한 기술 |
| --- | --- | --- |
| 객체 테이블을 생성합니다. | 애플리케이션의 기능에 필수적인 객체를 식별하고 라는 테이블을 생성합니다`DEPENDENT_ANALYSIS_BASELINE`. 테이블에 각 객체의 레코드를 추가합니다. 예제를 보려면 *추가 정보* 섹션을 참조하세요. | 데이터 엔지니어, DBA |
| 데이터베이스 프록시 생성 | `DBA_DEPENDENCIES` 테이블의 데이터를 사용하여 양방향(정방향 및 역방향)으로 객체 종속성을 분석`sp_object_dependency_analysis`하는 라는 저장 프로시저를 생성합니다. 예제를 보려면 *추가 정보* 섹션을 참조하세요. | 데이터 엔지니어, DBA |
| 프로시저를 실행합니다. | 새 객체 종속성이 발견되지 않을 때까지 각 연속 수준에서 스크립트를 실행합니다. 모든 종속성과 수준은 `DEPENDENT_ANALYSIS_BASELINE` 테이블에 저장됩니다. | DBA, 데이터 엔지니어 |
### 세그먼트 수준 종속성에 대한 프로시저 생성
| 작업 | 설명 | 필요한 기술 |
| --- | --- | --- |
| 종속성 테이블을 생성합니다. | 라는 세그먼트 수준 종속성 테이블을 생성합니다`REFERENTIAL_ANALYSIS_BASELINE`. 모든 객체 수준 종속성이 검색되면 테이블을 쿼리`DEPENDENT_ANALYSIS_BASELINE`하여의 상위 `DBA_CONSTRAINT` 테이블을 확인합니다.기준 테이블이 다른 테이블에서 참조되는 종속성을 제외합니다. 채우기는 이러한 관계를 처리합니다. 다음은 예제 스크립트입니다.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));
| 데이터 엔지니어, DBA |
| 데이터베이스 프록시 생성 | 라는 프로시저를 `SP_OBJECT_REFERENTIAL_ANALYSIS`생성하고 식별된 모든 객체에 대한 참조 분석을 생성합니다. 예제를 보려면 *추가 정보* 섹션을 참조하세요. | 데이터 엔지니어, DBA |
| 프로시저를 실행합니다. | 프로시저를 실행하여 참조 종속성을 가져옵니다. 에서 참조 분석 객체 세부 정보를 생성합니다`REFERENTIAL_ANALYSIS_BASELINE`. | 데이터 엔지니어, DBA |
### 읽고 쓰는 객체 식별
| 작업 | 설명 | 필요한 기술 |
| --- | --- | --- |
| 읽기 및 쓰기 객체에 대한 테이블을 생성합니다. | 다음 스크립트를 사용하여 이름이 인 읽기 객체 테이블`TABLE_READ_OBJECT_DETAILS`과 이름이 인 쓰기 객체 테이블을 생성합니다`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));
| 데이터 엔지니어, DBA |
| 분석 절차를 생성합니다. | 읽기 객체`SP_READER_OBJECTS_ANALYSIS`와 쓰기 객체를 각각 분석하기 `SP_WRITER_OBJECTS_ANALYSIS` 위한 프로시저 및를 생성합니다. 이 절차에서는 패턴 일치를 사용하여 관련 객체를 찾습니다. 예는 *추가 정보* 섹션을 참조하세요. | 데이터 엔지니어, DBA |
| 프로시저를 실행합니다. | 다음 절차를 실행하여 종속 객체를 식별합니다. | DBA, 데이터 엔지니어 |
### 데이터베이스 권한 검토
| 작업 | 설명 | 필요한 기술 |
| --- | --- | --- |
| 권한을 검토할 테이블을 생성합니다. | 라는 이름의 권한을 분석하기 위한 테이블을 생성합니다`OBJECT_PRIVS_ANALYSIS`. `DEPENDENT_ANALYSIS_BASELINE` 테이블에서 객체 권한을 재귀적으로 캡처하려면 다음 스크립트를 사용합니다.CREATE TABLE OBJECT_PRIVS_ANALYSIS
(OWNER VARCHAR2(50 BYTE),
OBJECT_NAME VARCHAR2(50 BYTE),
USER_NAME VARCHAR2(50 BYTE),
PRIVS VARCHAR2(50 BYTE));
| 데이터 엔지니어, DBA |
| 권한을 검토하는 절차를 생성합니다. | 라는 프로시저를 생성합니다`SP_OBJECT_PRIVS_ANALYSIS`. 식별된 객체에 대한 권한 분석을 생성합니다. 예제를 보려면 *추가 정보* 섹션을 참조하세요. | DBA, 데이터 엔지니어 |
| 프로시저를 실행합니다. | 프로시저를 실행하여 `OBJECT_PRIVS_ANALYSIS` 테이블에서 캡처합니다. | DBA, 데이터 엔지니어 |
## 문제 해결
| 문제 | Solution |
| --- | --- |
| 사전 테이블에 액세스할 수 없음 | 분석 객체를 생성한 사용자가 DBA 테이블에 액세스할 수 있는지 확인합니다. |
## 관련 리소스
**AWS 설명서**
+ [Amazon RDS 및 Aurora 설명서](https://docs.aws.amazon.com/rds/)
+ [Oracle Database 19c - 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/)
+ AWS Schema Conversion Tool을 시작합니다.
**기타 설명서**
+ [Oracle 데이터베이스 객체](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Database-Objects.html)
## 추가 정보
**용 스크립트 `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));
```
의 절차
```
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;
```
의 절차
```
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;
```