what is the alternative of
use database_name
command of SQL Server in Oracle database?
1 Answer
The alternative would be alter session statement:
alter session set current_schema=<<schema_name>>
The one of the main differences is it will only change schema qualifier when a qualifier is omitted in the select statement, for example. For instance, if you logged in as user1 and executed the above alter session statement specifying user2 as current_schema then the user2 will be used as schema qualifier and not the user1. And unlike SQL Server's use statement, Oracle's original session will retain its privileges and wont acquire new one after the execution of alter session statement.
In order to be able to select(for example) from a table that resides in the user2 schema, user1 will have to have select on that table or select any table privilege. Same goes for any other object, if there is no privilege to operate on that object, statement that references that object will fail.
2 Comments
Shans Muga
Thanks for help. But I don't have alter access. Is there any way I can do this without alter command? I don't want to qualify my table name with the schema name.
Nick Krasnov
@user2617588 Firstly. This operation does not require
alter session system privilege. Secondly. Is there any way I can do this without alter command? I'm not aware of what you are trying to accomplish, but maybe you do not need to do it at all just because you got used to do it in SQL server. As an alternative to reference objects in another schema you could create synonyms(will require create synonym system) for that objects create synonym <<synonym_name>> for <<schema_name>>.<<object_name>> - would be safer.
SETalter session set current_schema = new_schema_name