117 - [Oracle PL/SQL] Remote Dependencies - Time stamp

 

 

 

 

 

 

 

• ict@newd 에 접속해서 아래 스크립트를 실행

step 1 - conn ict

the User ICT is exist in remote database called newd
the user will create table, view on table, 
and procedure read form the view 
the user will check that all theses objects are valid


-- 
drop table students;
/
create table students
(student_id number,
 student_name varchar2(100),
 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;

col STUDENT_NAME for a20

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


create or replace view v1_students
as
select * from students;


create or replace procedure print_all_students
is
begin
  for i in (select * from v1_students )
  loop
  dbms_output.put_line(i.student_id||' '||i.student_name);
  end loop;

end;


-- 객체들의 상태가 모두 정상임, TIMESTAMP를 잘 확인하세요
select object_name, object_type, created, status, timestamp
from user_objects
where object_name in ('PRINT_ALL_STUDENTS','STUDENTS','V1_STUDENTS');
-----------------------------------------------------------------------
OBJECT_NAME             OBJECT_TYPE      CREATED   STATUS  TIMESTAMP          
----------------------- ---------------- --------- ------- -------------------
PRINT_ALL_STUDENTS      PROCEDURE        31-MAY-24 VALID   2024-05-31:17:54:32
STUDENTS                TABLE            31-MAY-24 VALID   2024-05-31:17:52:42
V1_STUDENTS             VIEW             31-MAY-24 VALID   2024-05-31:17:54:26

 

 

 

• hr@orclpdb 에 접속해서 아래 스크립트를 실행

--There are 2 dependencies modes  TIMESTAMP(default) and SIGNATURE

SELECT name, value
FROM v$parameter
WHERE name='remote_dependencies_mode';
/*
NAME                           VALUE               
------------------------------ --------------------
remote_dependencies_mode       TIMESTAMP 
*/



--to read any table from the remote db, you should use table_name@db_link
select * from students@READ_REMOTE;
/*
STUDENT_ID STUDENT_NAME         DOB      
---------- -------------------- ---------
         1 aya ahmed            01-JAN-80
         2 sara mahmoud         01-JAN-80
         3 nabil alail          01-JAN-80
*/

drop procedure read_from_remote_db;
/
create or replace procedure read_from_remote_db
is
begin
    dbms_output.put_line('executing the remote procedure');
    print_all_students@READ_REMOTE;
end;


--NOW the read_from_remote_db will be valid 
select object_name, object_type, created, status, timestamp
from user_objects
where lower(object_name)='read_from_remote_db';
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS  TIMESTAMP          
------------------------------ ----------------------- --------- ------- -------------------
READ_FROM_REMOTE_DB            PROCEDURE               31-MAY-24 VALID   2024-05-31:18:02:19
*/

 

 

 

현재까지 모든 객체의 상태는 정상

select object_name, object_type, created, status, timestamp
from user_objects
where object_name in ('PRINT_ALL_STUDENTS','STUDENTS','V1_STUDENTS');
-----------------------------------------------------------------------
OBJECT_NAME             OBJECT_TYPE      CREATED   STATUS  TIMESTAMP          
----------------------- ---------------- --------- ------- -------------------
PRINT_ALL_STUDENTS      PROCEDURE        31-MAY-24 VALID   2024-05-31:17:54:32
STUDENTS                TABLE            31-MAY-24 VALID   2024-05-31:17:52:42
V1_STUDENTS             VIEW             31-MAY-24 VALID   2024-05-31:17:54:26




select object_name, object_type, created, status, timestamp
from user_objects
where lower(object_name)='read_from_remote_db';
/*
OBJECT_NAME             OBJECT_TYPE      CREATED   STATUS  TIMESTAMP          
----------------------- ---------------- --------- ------- -------------------
READ_FROM_REMOTE_DB     PROCEDURE        31-MAY-24 VALID   2024-05-31:18:02:19
*/

 

 

 

• ict@newd 에 접속해서 아래 스크립트를 실행

alter table students modify student_name varchar2(200)


-- 객체 2개의 상태가 비정상으로 변경됨
select object_name, object_type, created, status, timestamp
from user_objects
where object_name in ('PRINT_ALL_STUDENTS','STUDENTS','V1_STUDENTS');
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS  TIMESTAMP          
------------------------------ ----------------------- --------- ------- -------------------
PRINT_ALL_STUDENTS             PROCEDURE               31-MAY-24 INVALID 2024-05-31:17:54:32
STUDENTS                       TABLE                   31-MAY-24 VALID   2024-05-31:18:11:14
V1_STUDENTS                    VIEW                    31-MAY-24 INVALID 2024-05-31:17:54:26
*/

 

 

 

• hr@orclpdb 에 접속, read_from_remote_db 객체의 상태는 아직 정상으로 표시

--this alter will make the view and proc in the remote db invalid, 
--but the proc read_from_remote_db will remain valid 
select object_name, object_type, created, status, timestamp
from user_objects
where lower(object_name)='read_from_remote_db';
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS  TIMESTAMP          
------------------------------ ----------------------- --------- ------- -------------------
READ_FROM_REMOTE_DB            PROCEDURE               31-MAY-24 VALID   2024-05-31:18:02:19
*/

 

-- now when execting the first time, it will give error
-- and it will validate again the view and proc in remote database
-- it will invalidate the proc in local database 
-- 프로시져를 실행하면 오류가 발생하며, 
exec read_from_remote_db
/*
Error report -
ORA-04062: timestamp of procedure "ICT.PRINT_ALL_STUDENTS" has been changed
ORA-06512: at "HR.READ_FROM_REMOTE_DB", line 5
ORA-06512: at line 1
04062. 00000 -  "%s of %s has been changed"
*Cause:    Attempt to execute a stored procedure to serve
           an RPC stub which specifies a timestamp or signature that is
           different from the current timestamp/signature of the procedure.
*Action:   Recompile the caller in order to pick up the new timestamp.
*/


-- 객체의 상태는 비정상으로 표시된다
select object_name, object_type, created, status, timestamp
from user_objects
where lower(object_name)='read_from_remote_db';
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS  TIMESTAMP          
------------------------------ ----------------------- --------- ------- -------------------
READ_FROM_REMOTE_DB            PROCEDURE               31-MAY-24 INVALID 2024-05-31:18:02:19
*/

 

 

 

• ict@newd 에 접속해서 객체의 상태를 확인.

-- hr 객체를 실행하면서 관련된 ict의 객체들이 자동으로 컴파일된다


-- 이때 ict의 객체들은 자동 컴파일되면서 상태가 정상으로 변경된다. TIMESTAMP 변경된 시점 확인
-- 하지만 hr.read_from_remote_db 상태는 아직도 비정상임.

select object_name, object_type, created, status, timestamp
from user_objects
where object_name in ('PRINT_ALL_STUDENTS','STUDENTS','V1_STUDENTS');
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS  TIMESTAMP          
------------------------------ ----------------------- --------- ------- -------------------
PRINT_ALL_STUDENTS             PROCEDURE               31-MAY-24 VALID   2024-05-31:18:14:10
STUDENTS                       TABLE                   31-MAY-24 VALID   2024-05-31:18:11:14
V1_STUDENTS                    VIEW                    31-MAY-24 VALID   2024-05-31:18:14:10
*/


-- hr 객체의 상태
select object_name, object_type, created, status, timestamp
from user_objects
where lower(object_name)='read_from_remote_db';
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS  TIMESTAMP          
------------------------------ ----------------------- --------- ------- -------------------
READ_FROM_REMOTE_DB            PROCEDURE               31-MAY-24 INVALID 2024-05-31:18:02:19
*/

 

 

 

• hr@orclpdb 에 접속, read_from_remote_db 실행하면 자동 컴파일되면서 상태가 정상으로 변경된다

--exec second time will validate the local proc again 
exec read_from_remote_db;
-- executing the remote procedure

-- 관련된 리모트 객체들이 모두 정상이므로, read_from_remote_db 객체의 상태도 정상으로 변경된다

select object_name, object_type, created, status, timestamp
from user_objects
where lower(object_name)='read_from_remote_db';
--------------------------------
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS  TIMESTAMP          
------------------------------ ----------------------- --------- ------- -------------------
READ_FROM_REMOTE_DB            PROCEDURE               31-MAY-24 VALID   2024-05-31:18:20:29
*/