Often we asked to run a script and we don't want to have the schema name as prefix inside our script.
We can set the schema of the current session to a schema different from our own:
SQL>ALTER SESSION SET CURRENT_SCHEMA = <schema name>;
Oracle Database will use this schema as a schema qualifier when it's omitted.
And also database will use the temporary tablespace of the specified schema for sorts.
But we have to remember if our own user doesn't have access to the new schema objects still we will not have access . It means by altering your session to set to a new schema you'll still need to have right privilege to that schema objects to do any action otherwise it will not elevate your access.
Like:
CONNECT scott/tiger
ALTER SESSION SET CURRENT_SCHEMA = AMIN;
SELECT * FROM emp;
Because emp is not schema qualified, the table name will be resolved under schema Amin.
If Amin doesn't have privilege to select scot.emp table the above select will be failed.
You can do this through system trigger as below and any time Amin connects to the database will have this ability :
CREATE OR REPLACE TRIGGER SYS.AMIN_LOGIN
AFTER LOGON
ON DATABASE
ENABLE
declare
v_user varchar2(30):=user;
sql_stmt1 varchar2(256) :='ALTER SESSION SET current_schema =AMIN';
begin
if (v_user in () THEN
execute immediate sql_stmt1;
end if;
end;
/
Use the query below to get your current_schema info:
SQL>SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') from DUAL;
Don’t forget to change back your current schema settings after your operations on other schema.
