Using other users' schemas as our own

As a DBA (A for Administrator or Architect), some times you'll be required to create, edit, drop or test schema objects of users of the database.

As you want to keep your DBA rights and don't want to bother creating new connections (on GUI clients) or just don't want to close your current session con SQL*Plus and due to the fact that is a pain in the back to write every schema object with the schema name in front of it... You may consider setting your default schema for the current session to the one of the user that requires your assistance.

How? It's really easy, you can simpy execute a query to ALTER your own session and point it to another schema. That's done as follows:

ALTER SESSION SET CURRENT_SCHEMA = the_schema_you_want_to_use

This will make all of your queries over that new schema as if you were its owner, of course maintaining your privileges as DBA.

More so, you may have groups of users that should work over the same schema, you can assign a role and then implement a trigger to alter the session when they log on, with a trigger that should look like this (not tested yet):

CREATE OR REPLACE TRIGGER set_default_schema
AFTER LOGON ON DATABASE WHEN (USER = 'XYZ')
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = ABC';
END;

It can be even be modified to see if (USER IN (SELECT... FROM... WHERE...)) so you can get groups of users pointing at the same schema as soon as they login.