-- 데이터 준비
drop table students
/
create table students
(student_id number,
student_name varchar2(20),
dob date
)
/
insert into students(student_id,student_name,dob) values (1,'aya ahmed','1-jan-1980');
insert into students(student_id,student_name,dob) values (2,'sara mahmoud','1-jan-1980');
insert into students(student_id,student_name,dob) values (3,'nabil alail','1-jan-1980');
commit;
--테스트용 테이블 확인
select * from students;
/*
STUDENT_ID STUDENT_NAME DOB
---------- -------------------- ---------
1 aya ahmed 01-JAN-80
2 sara mahmoud 01-JAN-80
3 nabil alail 01-JAN-80
*/
-- direct 뷰, 프로시져 생성
create or replace view v1_students
as
select * from students;
/
create or replace view v2_students
as
select student_id,dob
from students;
/
create or replace procedure print_all_students
is
begin
for i in (select * from students)
loop
dbms_output.put_line(i.student_id||' '||i.student_name);
end loop;
end;
--direct dependencies 확인
select name, type, REFERENCED_OWNER as owner, REFERENCED_NAME,REFERENCED_TYPE -- *
from user_dependencies
where referenceD_name='STUDENTS';
/*
NAME TYPE OWNER REFERENCED_NAME REFERENCED_TYPE
-------------------- ------------------- ---------- -------------------- -------------------
V1_STUDENTS VIEW HR STUDENTS TABLE
V2_STUDENTS VIEW HR STUDENTS TABLE
PRINT_ALL_STUDENTS PROCEDURE HR STUDENTS TABLE
*/
-- indirect 프로시져
create or replace procedure print_all_students_from_v1
is
begin
for i in (select * from v1_students)
loop
dbms_output.put_line(i.student_id);
end loop;
end;
-- 여기에서는 direct dependencies 인 객체만 조회 가능함
select name, type, REFERENCED_OWNER as owner, REFERENCED_NAME,REFERENCED_TYPE -- *
from user_dependencies
where referenceD_name='STUDENTS';
Direct/Indirect dependencies 객체 조회를 위한 준비
--in order to see the direct and indirect dependencies
--we run script called utldtree.sql
--파일 위치 확인
[oracle@test admin]$ ll utldtree.sql
-rw-r--r-- 1 oracle oinstall 5040 May 29 2017 utldtree.sql
[oracle@test admin]$ pwd
/u01/app/oracle/product/19/db_1/rdbms/admin
--hr 유저로 로그인 후, 실행
[oracle@test admin]$ pwd
/u01/app/oracle/product/19/db_1/rdbms/admin
[oracle@test admin]$ sqlplus hr/hr@orclpdb
SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 16 22:49:58 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Thu May 16 2024 22:21:47 +01:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
-- 실행, 몇가지 오류가 발생하지만 무시해도 된다. 대부분 없는 객체에 대한 drop 오류
SQL> @utldtree.sql
drop sequence deptree_seq
*
ERROR at line 1:
ORA-02289: sequence does not exist
Sequence created.
drop table deptree_temptab
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
Procedure created.
drop view deptree
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> REM This view will succeed if current user is sys. This view shows
SQL> REM which shared cursors depend on the given object. If the current
SQL> REM user is not sys, then this view get an error either about lack
SQL> REM of privileges or about the non-existence of table x$kglxs.
SQL>
SQL> set echo off
from deptree_temptab d, dba_objects o
*
ERROR at line 5:
ORA-00942: table or view does not exist
SQL>
SQL> REM This view will succeed if current user is not sys. This view
SQL> REM does *not* show which shared cursors depend on the given object.
SQL> REM If the current user is sys then this view will get an error
SQL> REM indicating that the view already exists (since prior view create
SQL> REM will have succeeded).
SQL>
SQL> set echo off
View created.
drop view ideptree
*
ERROR at line 1:
ORA-00942: table or view does not exist
View created.
SQL>
select * from user_objects
where lower(object_name) like '%tree%';
객체 조회하는 방법
-- 생성된 객체 확인
select * from user_objects
where lower(object_name) like '%tree%';
-- STUDENTS 와 관련된 모든 객체 찾기 프로시져 실행
exec DEPTREE_FILL ('TABLE','HR','STUDENTS');
-- 실행결과 조회 쿼리, 현재는 원본 테이블을 포함해서 5개의 객체가 조회됨
SELECT * FROM DEPTREE
ORDER BY SEQ#
--NESTED_LEVEL, 0 is original table, 1=direct(one step to find itself), 2~-indirect(2 or more step)
/*
NESTED_LEVEL TYPE SCHEMA NAME SEQ#
------------ ----------------------- ---------- ------------------------------ ----------
0 TABLE HR STUDENTS 0
1 VIEW HR V1_STUDENTS 1
2 PROCEDURE HR PRINT_ALL_STUDENTS_FROM_V1 2
1 VIEW HR V2_STUDENTS 3
1 PROCEDURE HR PRINT_ALL_STUDENTS 4
*/
--Indirect 프로시져 생성,
CREATE OR REPLACE PROCEDURE TRT
IS
BEGIN
print_all_students_from_v1;
END;
exec DEPTREE_FILL ('TABLE','HR','STUDENTS');
-- 실행결과 조회 쿼리, 현재는 원본 테이블을 포함해서 6개의 객체가 조회됨
SELECT * FROM DEPTREE
ORDER BY SEQ#
/*
NESTED_LEVEL TYPE SCHEMA NAME SEQ#
------------ ----------------------- ---------- ------------------------------ ----------
0 TABLE HR STUDENTS 0
1 VIEW HR V1_STUDENTS 5
2 PROCEDURE HR PRINT_ALL_STUDENTS_FROM_V1 6
3 PROCEDURE HR TRT 7
1 VIEW HR V2_STUDENTS 8
1 PROCEDURE HR PRINT_ALL_STUDENTS 9
*/
