Solved

linking different user/schema

Posted on 2010-11-30
8
315 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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Syntax 8 58
Oracle SQL Select Statement 19 58
Oracle regular expression 6 30
Best RAID for a BDD Oracle 4 24
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 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

20 Experts available now in Live!

Get 1:1 Help Now