Hi,
My db version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
I have two schemas A, B withing same database D.
I create a table emp in A
I create a table dept in B as below
In schema A
create table emp (
eno number(2),
ename varchar2(10),
deptno number(2));
alter table emp
add constraint pk_emp
primary key (eno);
In schema B
create table dept (
deptno number(2),
dname varchar2(15));
alter table dept
add constraint pk_dept
primary key (deptno);
Now when I try to establish a relation between these two tables from schema A, I get an error
alter table emp
add constraint fk_emp_to_dept
foreign key (deptno)
references B.dept (deptno);
error:
ORA-01031: insufficient privileges
I have some questions,
What privileges should I give the schema A, B to establish these table relations?
Is this a good approach, to have related tables spread across schemas? We have several tables and thought it might be good to segregate tables into different schemas?
But are there challenegs with this approach? performance? coding complexity? Or is it ok?
Please advice.