CODEWITHSUNDEEP
oracle-databaseoracle11g
Chad
Chad

Reputation: 24699

In oracle, can I associate a default Schema with my USER ID where SCHEMA <> USERID?

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.

Upvotes: 0

Views: 4504

Answers (1)

Allan
Allan

Reputation: 17429

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;
/

Upvotes: 2

Related Questions