0

I would like to connect to an Oracle database with UserId "MY_SCHEMA" and then be able to perform queries where the table name was not qualified with the schema name:

SELECT * FROM TABLE1

When executing SQL, I would like my unqualified queries to go against a different schema other than my own. In other words, I would like this to be equivalent to 

SELECT * FROM SCHEMA_OTHER.TABLE1

instead of 

SELECT * FROM MY_SCHEMA.TABLE1

I know it seems a little weird and so I expect that this is not possible.

I do not have any sort of admin rights to the database to see if I can associate a default schema with a login.

wweicker
  • 4,833
  • 5
  • 35
  • 60
Chad
  • 23,658
  • 51
  • 191
  • 321

1 Answers1

2

After you login, you can run the following:

ALTER SESSION SET CURRENT_SCHEMA=schema_other;

If you really want this to happen automatically, you can create a system trigger for the logon action that looks for your username and uses execute immediate to run the alter session command:

CREATE OR REPLACE TRIGGER my_schema_logon
   AFTER LOGON
   ON DATABASE
BEGIN
   IF SYS_CONTEXT ('USERENV', 'CURRENT_USER') = 'MY_SCHEMA' THEN
      EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=schema_other';
   END IF;
END my_schema_logon;
/
Allan
  • 17,141
  • 4
  • 52
  • 69