Solved

linking different user/schema

Posted on 2010-11-30
8
323 Views
Last Modified: 2012-05-10
Hi,
I want to know is a way to linking different user/schema on same database other than dblink?
e.g. abc/abc007@samedb , def/def007@samedb
Will it must using dblink?
or any other way to do linking?
Thank  you!
Francis SZE
0
Comment
Question by:fsze88
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 12

Accepted Solution

by:
enachemc earned 200 total points
ID: 34237562
you can use schemas directly, no need to use DB Links if you're on the same DB

you just say:

select * from a,schema.b b
where a.id = b.f_id
0
 
LVL 1

Assisted Solution

by:sunny25
sunny25 earned 200 total points
ID: 34237844
First you need to grant privileges on the objects of abc/abc007 to def/def007 and then can access those objects in def/def007 schema just the way enachemc explained
0
 
LVL 15

Author Comment

by:fsze88
ID: 34237853
what's that grant statement?
Thank YOU!

Francis SZE
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Assisted Solution

by:sunny25
sunny25 earned 200 total points
ID: 34237868
grant privileges like select,insert or excute etc. to other schema
eg:
abc has a table A and you want to access this table in user schema def,
then run below statement in abc schema
grant select on A to def;
and in user schema def you can query: select * from abc.A;
0
 
LVL 12

Assisted Solution

by:enachemc
enachemc earned 200 total points
ID: 34237869
grant select any table to &user;
grant delete any table to &user;
grant update any table to &user;
grant insert any table to &user;
0
 
LVL 15

Author Comment

by:fsze88
ID: 34238417
wait,,
select * from a,schema.b b
where a.id = b.f_id

schema.b is meaning username.b?

select * from a,def.b b
where a.id = b.f_id
0
 
LVL 5

Assisted Solution

by:manzoor_dba
manzoor_dba earned 100 total points
ID: 34238893
Hi,

You need to grant privilges to other schema (user) , so that the other user can
do the
Hope the below examples clears your doubt.

username :  abc
his object  : emp

username : def
his object : employ.

as abc user execute below.
> grant select,insert, update,delete on emp to def;

as def user execute below.
> grant select, insert, update,delete on employ to abc.

Once the privileges are granted you can do the above said dml operation on the tables of the other schema..

Thanks..
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question