REM
REM || Title : set_def_schema.sql
REM ||
REM || Purpose : Set default schema for users. Creating this trigger helps having to avoid
REM || create private/public synonyms.
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : SYS
REM ||
REM

CREATE OR REPLACE TRIGGER set_current_schema
AFTER LOGON ON DATABASE
DECLARE
default_schema VARCHAR2 (30) := 'SCOTT';
BEGIN
IF ( login_user != 'SYS'
AND login_user != 'SYSTEM'
AND login_user != 'DBSNMP'
AND login_user != 'OUTLN'
AND login_user != 'TIGER'
AND login_user != 'TIGER_OWNER')
THEN
EXECUTE IMMEDIATE 'alter session set current_schema = ' || default_schema;
END IF;
END;
/