I created a few new users in Oracle. However, when running sqlplus, they all need to fully qualified the table names in query. What's the best way to set a default schema for these new users?
There is nothing like PostgreSQL's
set search_path in Oracle.
The closest thing I can think of would be a logon trigger for the user that run's an
ALTER SESSION SET CURRENT_SCHEMA ...
CREATE OR REPLACE TRIGGER LOGON_TRG AFTER LOGON ON SCHEMA BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar'; EXCEPTION when others then null; -- prevent a login failure due to an exception END; /
If the list of users isn't too long, you can create a database logon trigger so you don't have to create that trigger for each user:
CREATE OR REPLACE TRIGGER LOGON_TRG AFTER LOGON ON DATABASE BEGIN if (user in ('TOM', 'DICK', 'HARRY')) then EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar'; end if; exception when others then null; -- prevent a login failure due to an exception END logon_trg; /
Of course the list of users where you want to change the default schema, can also be taken from a table. In that case you only need to insert or delete rows from there in order to "activate" this feature (rather than re-creating the trigger each time).
Another option would be to create synonyms each time you create user that point to the real tables. You could automate that using a stored procedure that loops through all tables in one schema and creates the synonyms for them in the other schema.
Unless all your Oracle users work on the same tables I would strongly advise against using public synonyms which you would have to create only once - they can cause a lot of trouble if different application users exist in your installation.
Following Alex's suggestion, here is a logon trigger that checks the role rather than a username:
CREATE OR REPLACE TRIGGER LOGON_TRG AFTER LOGON ON DATABASE declare has_role boolean; BEGIN has_role := dbms_session.is_role_enabled('FOOBAR_ROLE'); if (has_role) then EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar'; end if; exception when others then null; -- prevent a login failure due to an exception END logon_trg; /
After starting SQLPlus, there is a
glogin.sql, and a
login.sql script that is run.
You can put:
ALTER SESSION SET CURRENT_SCHEMA=YOUR_SCHEMA;
in either, and they will be run.
login.sql needs to be in a directory set by an environment variable:
SQLPATH, and the
glogin.sql is in the same directory as
sqlplus.exe is installed into.
I couldn't get the
login.sql part working, but the
glogin.sql script executed fine.