SYS user connect to current schema at session level.

SYS user connect to different Schema object by setting current_schema at session level

If you are Development DBA,It is very use full in executing of script for particulars schema if you do not have password of that user/schema in which you run the query.Simply set the current_schema parameter at session level and execute the scripts for that schema.

1. Connecting with the SYSDBA user.

sqlplus / as sysdba

2. Check the username from the database.

select username from dba_users;

3. Select the username which need to use.

Alter session set Current_schema=SCOTT;

4. You can access the SCOTT objects without putting owner name in front of object_name from sys users as shown below:

-- Connect with sysdba users
conn sys as sysdba-- Try to access the Scott schema object "EMP"
select count(*) from scott.emp;
-- Now try to access the same object without the Scott schema specify.
select count(*) from emp;
select count(*) from emp
ERROR at line 1:
ORA-00942: table or view does not exist

–Now you can schema at session level in oracle
alter session set current_schema=SCOTT;
Session altered.

— Now you can access the emp table because of current schema set to scott user
select count(*) from emp;

Note: In same way if application team send you script for executing for particular user, please use the current schema at session level if you don’t have password otherwise object may created on SYS user if application team not specify the schema.object name in script


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.