Schema 1 : User1 (Will store the parent table TAB1)
Schema 2 : User2 (Will store the parent table TAB2)
Oracle Version : 12.1.0.2
1. Create the parent table TAB1 in USER1 schema with a primary key
USER1 SQL> create table tab1 (n1 number primary key);
Table created.
USER1 SQL> desc tab1
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
N1 NOT NULL NUMBER
2. Grant 'REFERENCE on TAB1' privilege to USER2 schema.
USER1 SQL> grant references on tab1 to user2;
Grant succeeded.
USER1 SQL> exit
Schema 2 : User2 (Will store the parent table TAB2)
Oracle Version : 12.1.0.2
1. Create the parent table TAB1 in USER1 schema with a primary key
USER1 SQL> create table tab1 (n1 number primary key);
Table created.
USER1 SQL> desc tab1
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
N1 NOT NULL NUMBER
2. Grant 'REFERENCE on TAB1' privilege to USER2 schema.
USER1 SQL> grant references on tab1 to user2;
Grant succeeded.
USER1 SQL> exit
3. Create the Child Table TAB2 in USER2 Schema and create the foreign key which references User1.TAB1 table.
USER2 SQL> create table tab2 (
n2 number primary key,
n1 number,
constraint fk_tab2 foreign key(n1) references user1.tab1(n1)
);
Table created.
USER2 SQL>
4. Check the parent child relationship from dba_constraints table:
SYS SQL> SET LINES 300
col parent_owner format A8
col child_owner format A8
col parent_table format A10
col child_table format A10
col parent_cons_name format A15
col child_cons_name format A10
SELECT
SYS SQL> p.owner parent_owner,
c.owner child_owner,
p.table_name Parent_table,
p.constraint_name parent_cons_name,
SYS SQL> c.table_name Child_table,
c.constraint_name child_cons_name
FROM
dba_constraints p, dba_constraints c
where p.owner='&parent_Owner'
and p.table_name='&Parent_Table_Name'
SYS SQL> and p.constraint_type IN ('P','U')
and c.constraint_type='R'
and p.owner=c.r_owner
aSYS SQL> nd p.constraint_name=c.r_constraint_name
order by 4;
Enter value for parent_owner: USER1
old 10: where p.owner='&parent_Owner'
new 10: where p.owner='USER1'
Enter value for parent_table_name: TAB1
old 11: and p.table_name='&Parent_Table_Name'
new 11: and p.table_name='TAB1'
PARENT_O CHILD_OW PARENT_TAB PARENT_CONS_NAM CHILD_TABL CHILD_CONS
-------- -------- ---------- --------------- ---------- ----------
USER1 USER2 TAB1 SYS_C00120730 TAB2 FK_TAB2
SYS SQL>
No comments:
Post a Comment