How to access other user tables in oracle rather than prefix user name and synonyms

set up user synonym.


logged in as the app_user with privs to access the prod tables.

create synonym accounts from prod.accounts.

something like that will work, just check the syntax and it should do the trick.


good luck,
daniels@asix.com

You could alternatively set up views

as table owner :-

create view <vname>
as select * from <owner>.<table_name>
;

create public synonym <sname> for <vname>
;

Tony

You could also use this:

ALTER SESSION SET CURRENT_SCHEMA=target_username;


You must have the BECOME USER privilege to do this, and also any other object privileges to access and manipulate that user's objects.



Andrew

whoops. correct syntax is "for" not "from"

create synonym accounts for prod.accounts.


good luck,
daniels@asix.com

ALTER SESSION SET CURRENT_SCHEMA=target_username

i have 300 more user if every one using alter session and set current_schema=targer_username  does it effect the performance

no, it should not. but you could always set up a login trigger to fire a stored procedure that does all this for you so your users don't have to issue the command. then pin both the trigger and the procedure in memory.


good luck,
daniels@asix.com

The login trigger will affect performance during connection times, especially if you have a high rate of connect/disconnect per second.  You would probably be better of with public/private synonyms.



Andrew

hi Mr daniels,

thnks for ur update

but the problem is
  ALTER SESSION SET CURRENT_SCHEMA=target_username

  we canot use this command in procedure or in trigger
we get a error ,can u pls.. mail me how to use this command in procedure  or in trigger

since in procedure or trigger we cannot include the command alter session

CREATE OR REPLACE TRIGGER login_trg
ON DATABASE AFTER LOGON...

BEGIN

execute immediate 'alter session set current_schema=target_username';

END;
/





Andrew

Just to add one more thing, you should have an exception handler. If you hit an exception, a user might not be able to log in at all:

CREATE OR REPLACE TRIGGER login_trg
ON DATABASE AFTER LOGON...

BEGIN

execute immediate 'alter session set current_schema=target_username';

EXCEPTION WHEN OTHERS THEN null;

END;

hi daniels@asix.com


  i try to create the above trigger at system user
i cannot logon to system or any other users
only i can logon thu internal, so i tried to drop the trigger, trigger sucessfuly droped. but still i cannot logon because i think the trigger is firing

You should ALWAYS put in an exception handler for logon triggers, even if just this:

EXCEPTION WHEN OTHERS THEN null;


If there is an error in the code, the trigger will hang, and all users will continue to have problems logging in.  All users will be essentially locked out, as you have experienced.  


Andrew

hi Mr Daniels,

  how r you,thnks for your response for my query,

i tried to create the user_connections tables
and triggers at sys user it is working fine when i am trying on 9i,8.1.7

when i am trying on 8.1.5 the trigger is working fine but the problem is it is not setting the
alter session current_schema =PRODUCTION

when i try on the sqlplus the above command is working fine
when i am using the above command inside a trigger after logon database it is not setting the current_schema

when i created the above  trigger after insert at table level the trigger is working and setting the current_schema to production

can u pls .. guide me the reason why in 8.1.5 the it is not setting the current_schema in after logon database

regds
Srinivasan

many tables are sitting in wrong tablespace
if i want to move the table into new tablespace in oracle 7.3.4 any alter query to move the change the table tablespace


currently

eg

table emp sitting on index_ts tablespace

i want to move the table to

table emp on User_ts (tablespace)







 

Open a new question. This one is closed.