--example 1
drop table emp_copy;
create table emp_copy
as
select * from employees;
select * from emp_copy;
create or replace view v_emp_copy
as
select * from emp_copy;
select object_name,object_type, status
from user_objects
where object_name='V_EMP_COPY';
drop table emp_copy;
select object_name,object_type, status
from user_objects
where object_name='V_EMP_COPY';
select * from v_emp_copy
-----------------
--example 2
drop table dept_copy;
create table dept_copy
as
select * from departments;
select * from dept_copy;
create or replace view v_dept_copy
as
select department_id, department_name
from dept_copy;
select object_name,object_type, status
from user_objects
where object_name='V_DEPT_COPY';
ALTER TABLE dept_copy
ADD ( DSIZE NUMBER);
select object_name,object_type, status
from user_objects
where object_name='V_DEPT_COPY';
alter table dept_copy
drop column department_name;
select object_name,object_type, status
from user_objects
where object_name='V_DEPT_COPY';
------------------------------------------------------------------------------------------------
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;
select * from students;
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;
select *
from
user_dependencies
where referenceD_name='STUDENTS';
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;
select *
from
user_dependencies
where referenceD_name='STUDENTS';
--in order to see the direct and indirect dependencies
--we run script called utldtree.sql
@f:\utldtree.sql
select * from user_objects
where lower(object_name) like '%tree%';
exec DEPTREE_FILL ('TABLE','HR','STUDENTS');
SELECT * FROM DEPTREE
ORDER BY SEQ#
CREATE OR REPLACE PROCEDURE TRT
IS
BEGIN
print_all_students_from_v1;
END;
exec DEPTREE_FILL ('TABLE','HR','STUDENTS');
SELECT * FROM DEPTREE
ORDER BY SEQ#
-------------------------------------------------------------------------------------------------
drop table students;
drop view v1_students;
drop view v2_students;
drop procedure print_all_students;
drop procedure print_all_students_from_v1;
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;
select * from students;
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 student_id from students)
loop
dbms_output.put_line(i.student_id);
end loop;
end;
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;
select * from user_objects
where lower(object_name) in
('v1_students','v2_students','print_all_students','print_all_students_from_v1');
alter table students
modify student_name varchar2(200);
select * from user_objects
where lower(object_name) in ('v1_students','v2_students','print_all_students','print_all_students_from_v1');
execute PRINT_ALL_STUDENTS_FROM_V1;--now the server will recompile
select * from user_objects
where lower(object_name) in ('v1_students','v2_students','print_all_students','print_all_students_from_v1');
------------------------------------------------------------------------------------------------------------------------
------case 1 impact on the view when adding/altering orginal table
DROP TABLE customer;
create table customer
( cust_id number,
name varchar2(100),
tel varchar2(10)
);
create or replace view v_customer
as
select cust_id,name
from customer;
create or replace view v2_customer
as
select *
from customer;
select * from user_objects
where lower(object_name) in ('customer','v_customer','v2_customer');
--adding column to table customer will not invalidate v_customer
--this started in 11g
alter table customer
add( dob date);
select * from user_objects
where lower(object_name)in ('customer','v_customer','v2_customer');
--alter column to table customer may or may not invalidate the views
alter table customer
modify tel varchar2(20);
select * from user_objects
where lower(object_name) in ('customer','v_customer','v2_customer');
--here it will compile again
select * from v2_customer;
select * from user_objects
where lower(object_name) in ('customer','v_customer','v2_customer');
------------------------------------------------------------
------case 2 impact on the function when adding/altering orginal table
create or replace function get_cust_name
( p_cust_id number)
return varchar2
is
v_name customer.name%type;
begin
select name into v_name
from customer
where cust_id=p_cust_id;
exception
when others then return null;
end;
select * from user_objects
where lower(object_name) in ('customer','v_customer','v2_customer','get_cust_name');
--adding col to a table will invalidate the function
alter table customer
add( location number);
select * from user_objects
where lower(object_name) in ('customer','v_customer','v2_customer','get_cust_name');
--but when you run it, it will compile it again
select get_cust_name(10) from dual;
select * from user_objects
where lower(object_name) in ('customer','v_customer','v2_customer','get_cust_name');
--alter col in table may or may not invalidate the function
alter table customer
modify tel varchar2(300);
select * from user_objects
where lower(object_name) in ('customer','v_customer','v2_customer','get_cust_name');
alter table customer
modify name varchar2(300);
select * from user_objects
where lower(object_name) in ('customer','v_customer','v2_customer','get_cust_name');
-----------------------------
------case 3 impact on the proc when adding/altering orginal table
create or replace procedure p1
is
begin
for i in (select cust_id from customer)
loop
dbms_output.put_line(i.cust_id);
end loop;
end;
create or replace procedure p2
is
begin
for i in (select * from customer)
loop
dbms_output.put_line(i.cust_id);
end loop;
end;
select * from user_objects
where lower(object_name) in ('p1','p2');
--ading column to proce May or May not invalidate according for the logic of changes
alter table customer
add( addresss varchar2(100));
select * from user_objects
where lower(object_name) in ('p1','p2');
--altering column to proce May or May not invalidate according for the logic of changes
alter table customer
modify cust_id number(10);
select * from user_objects
where lower(object_name) in ('p1','p2');
-------------------------------------------------------------------------------------------------------------
create or replace package pkg
is
procedure p1;
end;
create or replace package body pkg
is
procedure p1
is
begin
dbms_output.put_line ('welcome');
end;
end;
create or replace procedure call_from_pkg
is
begin
pkg.p1;
end;
select * from user_objects
where lower(object_name) in ('pkg','call_from_pkg');
create or replace package body pkg
is
procedure p1
is
begin
dbms_output.put_line ('welcome');
if 1=1 then
dbms_output.put_line ('yes');
end if;
end;
end;
select * from user_objects
where lower(object_name) in ('pkg','call_from_pkg');
create or replace package pkg
is
procedure p1;
procedure p2;
end;
create or replace package body pkg
is
procedure p1
is
begin
dbms_output.put_line ('welcome');
end;
procedure p2
is
begin
dbms_output.put_line ('welcome');
end;
end;
select * from user_objects
where lower(object_name) ='call_from_pkg';
------------------------------------------------------------------------------------------------------------
drop table products;
drop table all_products;
drop synonym products ;
create table all_products
(producut_id number,
producut_name varchar2(100),
producut_category varchar2(100)
);
insert into all_products values (1,'Sony 52 Inch/Led M0014','TV');
create or replace synonym products for all_products;
select * from all_products;
select * from products;
create or replace view v_all_products
as
select * from products;
create or replace procedure read_from_synonym
is
begin
for i in (select * from products)
loop
null;
end loop;
end;
create or replace procedure read_from_synonym2
is
begin
for i in (select producut_id from products)
loop
null;
end loop;
end;
select * from user_objects
where lower(object_name) in ('all_products','v_all_products','read_from_synonym','read_from_synonym2');
create or replace synonym products for employees;
select * from user_objects
where lower(object_name) in ('all_products','v_all_products','read_from_synonym','read_from_synonym2');
select * from products;
exec read_from_synonym;
select * from user_objects
where lower(object_name) in ('read_from_synonym');
exec read_from_synonym2;
select * from user_objects
where lower(object_name) in ('read_from_synonym2');
select * from V_ALL_PRODUCTS;
---------------------------------------------------------------------
drop synonym products ;
drop table products;
drop table all_products;
drop table all_products2;
create table all_products
(producut_id number,
producut_name varchar2(100),
producut_category varchar2(100)
);
insert into all_products values (1,'Sony 52 Inch/Led M0014','TV');
create table all_products2 as select * from all_products;
create or replace synonym products for all_products;
select * from all_products;
select * from products;
create or replace view v_all_products
as
select * from products;
select * from user_objects
where lower(object_name) in ('all_products','v_all_products');
create or replace synonym products for all_products2;
select * from user_objects
where lower(object_name) in ('all_products','v_all_products');
------------------------------------------------------------------------------------------------------------------
/*step 1
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
-------take code from here
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;
select * from students;
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;
select * from user_objects
where object_name in ('PRINT_ALL_STUDENTS','STUDENTS','V1_STUDENTS');
*/
--There are 2 dependencies modes TIMESTAMP(default) and SIGNATURE
SELECT name, value
FROM v$parameter
WHERE name='remote_dependencies_mode';
--to read any table from the remote db, you should use table_name@db_link
select * from students@READ_REMOTE;
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 * from user_objects
where lower(object_name)='read_from_remote_db';
--now the user ICT will do this : alter table students modify student_name varchar2(200)
--then
--select * from user_objects where object_name in ('PRINT_ALL_STUDENTS','STUDENTS','V1_STUDENTS');
--this alter will make the view and proc in the remote db invalid,
--but the proc read_from_remote_db will remain valid
select * from user_objects
where lower(object_name)='read_from_remote_db';
--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
select * from user_objects
where lower(object_name)='read_from_remote_db';
--exec second time will validate the local proc again
exec read_from_remote_db;
select * from user_objects
where lower(object_name)='read_from_remote_db';
----------------------------------------------------------------------------------------------------
/*step 1
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
-------take code from here
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;
select * from students;
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;
select * from user_objects
where object_name in ('PRINT_ALL_STUDENTS','STUDENTS','V1_STUDENTS');
*/
--to read any table from the remote db, you should use table_name@db_link
select * from students@READ_REMOTE;
SELECT name, value
FROM v$parameter
WHERE name='remote_dependencies_mode';
alter session set remote_dependencies_mode='SIGNATURE';
SELECT name, value
FROM v$parameter
WHERE name='remote_dependencies_mode';
drop procedure read_from_remote_db;
select * from students@READ_REMOTE;
create or replace procedure read_from_remote_db
is
begin
dbms_output.put_line('executing the remote procedure');
print_all_students@READ_REMOTE;
end;
select * from user_objects
where lower(object_name)='read_from_remote_db';
--now go and recompile the print_all_students without any changes
exec read_from_remote_db
--now go and recompile the print_all_students and do some changes in parameters
exec read_from_remote_db
No comments:
Post a Comment