Switching to a Different Schema

Who remembers to their first week as DBA or Developer? I do, my very first week as an Application Database Administrator Job, I was assigned to run bunch of SQL scripts. Those SQL scripts are written by developers and database developer, which our team reviews and run the scripts in QA, PRE-PROD and PROD  database environments. Sometime, the developer will come to your desk and ask to add an Index on a table for performance which they missed inside a script for us to run. I will just run their script on what they ask them if we feel it is appropriate to add Index.

I have been doing these favors of one-off request to developers. Later a colleague of mine found out that the Index that were added by me were owned by me not the table owner. The table is owned by application schema and the Index or Constraints that I added were owned by me. Why did this happen and what are the consequences? Any guess?

I never realized it until he explained how that happened and the problem this might create in the future. I am writing about the mistake I made and what I got out of it so my readers will not repeat the same mistake instead they will learn from it. How awesome is that?

As a best practices, both the table and the Index should be owned by same schema. The objects should never be owned by DBA user in Production or in Development database. The DBA may leave the company or promoted to other dept or team. When the DBA moves or leaves, the account gets dropped and the objects that belongs to the DBA needs to be dropped as well. Therefore, the objects should belong to schema not the user account.

How did I own the Index when I didn't own the table? this is because I logged in as myself (where I had a dba role) and ran a DDL to create Index on a table without adding a schema on the add Index command.
How I ran?

ALTER TABLE employee ADD INDEX my_idx (empid);

What it SHOULD have been?

ALTER TABLE hr.employee ADD INDEX my_idx (empid);

The best practice is to include schema name before an object name but not everyone does that. I get this a lot from my developers and I explain the importance and consequence for not including it. When you have a script without schema precedence object name, you either run the script as a the schema owner or alter session to set current schema as the user you wanted to run it.

"Always prefix the object name with the schema name"

The below is a piece from Oracle document on how to alter your session to switch to different schema.

ALTER SESSION SET CURRENT_SCHEMA = schema_name ;

In subsequent SQL statements, Oracle Database uses this schema name as the schema qualifier when the qualifier is omitted. In addition, the database uses the temporary tablespace of the specified schema for sorts, joins, and storage of temporary database objects. The session retains its original privileges and does not acquire any extra privileges by the preceding ALTER SESSION statement.
In the following example, provide the password tiger when prompted:

CONNECT scott
ALTER SESSION SET CURRENT_SCHEMA = joe;
SELECT * FROM emp;

Because emp is not schema-qualified, the table name is resolved under schema joe. But if scott does not have select privilege on table joe.emp, then scott cannot execute the SELECT statement.
How to verify current schema session?

SELECT sys_context('USERENV','SESSION_SCHEMA') FROM dual;

If your script does not include schema owner, use ALTER SESSION SET CURRENT_SCHEMA = schema_name. Also, remember to change the current schema back to the original schema to avoid confusion.