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?
migrated from serverfault.com Oct 30 '12 at 6:27
This question came from our site for system and network administrators.
I wouldnt think there is a way to set one. The user is the schema. AFAIK you can only set default tablespace.
There is nothing like PostgreSQL's
The closest thing I can think of would be a logon trigger for the user that run's an
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:
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: