CODEWITHSUNDEEP
oracle-databasereference
Aram Ahmad
Aram Ahmad

Reputation: 45

reference to table from another schema in oracle

I have 2 different schemas ( finn, hr ).
I have a table from hr whose name is ( hr_employee_info_tbl ) and another table from finn whose name is FINN_BUDGET_TBL.
I tried to make a reference from FINN_BUDGET_TBL to hr_employee_info_tbl but get ora-01031 insufficient privileges.

which privilige i have to grant my user?

-- Create/Recreate primary, unique and foreign key constraints 

alter table FINN_BUDGET_TBL
  add constraint employee_id_fk foreign key (EMPLOYEE_ID)
  references hr.hr_employee_info_tbl (EMPLOYEE_INFO_ID);

Upvotes: 0

Views: 4302

Answers (2)

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

Make sure you are logging in as the owner of FINN_BUDGET_TBL (in this case FINN) and the current logged in user has read on the hr schema.

grant select on hr.hr_employee_info_tbl to FINN;

"Alter any table" to the current user (FINN) also might work, but is in general, not the right thing to do.

Upvotes: 2

Florin Ghita
Florin Ghita

Reputation: 17643

Connected with user hr, run:

grant select on hr.hr_employee_info_tbl to finn;

Upvotes: 2

Related Questions