what is the alternative of
use database_name
command of SQL server in oracle?
Advertisement
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.