Solved

linking different user/schema

Posted on 2010-11-30
8
316 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now