Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Ramon.

Asked: December 13, 2007 - 12:40 pm UTC

Last updated: January 10, 2024 - 4:55 pm UTC

Version: 10 g

Viewed 100K+ times! This question is

You Asked

I have an application which requires us to change schema's within the application to update a table for various users. We have a variable in cobol called DB-SCHEMA which gets set.

I am able to issue the with a cobol module.

EXEC SQL
ALTER SESSION SET CURRENT_SCHEMA=DEVLPMT
END-EXEC

However, it fails when trying to use a variable in place of the schema name. Any ideas?

EXEC SQL
ALTER SESSION SET CURRENT_SCHEM=:DB-SCHEMA
END-EXEC

and Tom said...

You cannot user a bind variable in DDL statements and ALTER is considered a DDL statement.

You would need to use dynamic sql for this

exec sql execute immediate .... some string ...;




Rating

  (22 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ramon Santiago, December 14, 2007 - 12:01 pm UTC

Thanks. That is what I figured out late yesterday. I ended up having them code dynamic sql to instead as you suggested.

Switching to a Different Schema

amr, January 25, 2009 - 5:17 pm UTC

hi...plz tell me what the main reason to use the 'alter session CURRENT_SCHEMA = <schema name>' statement and we have to Switching to a Different Schema ...
thanks
Tom Kyte
January 26, 2009 - 1:15 am UTC

so you can avoid using evil synonyms.

Some people do not want to have to use the schema name when selecting from a table. Hence, they tend to flood the system with a lot of evil public synonyms. This is an easy way to avoid that.

If your reporting schema is in the schema 'X', you can log in as yourself and alter session to use 'X' by default

select * from t

will really be

select * from "X".t

instead of

select * from "YOUR_SCHEMA".t


subjective comments

Chris Gould, January 26, 2009 - 5:04 am UTC

I wish you wouldn't use emotive descriptions such as "evil" to describe bits of Oracle you don't like.
Synonyms, even public synonyms, have their uses (as do triggers which I know you've described similarly in the past).

Some of us who have been around a long time assume your comments are probably tongue-in-cheek, but others won't.

In this particular case, altering current schema at logon avoids having public synonyms or private synonyms in your login schema pointing to your object-owning schema. Enough said.

Tom Kyte
January 26, 2009 - 6:20 am UTC

I hope they won't (assume they are tongue in cheek). It wasn't.


public synonyms like triggers, autonomous transactions, when others then null, .... should be avoided, especially by those that cannot figure out when they would not be evil (I'm specifically saying you are *not* in that category).

Not enough said in my opinion, not in the year 2009 anyway. People skim, scan, do whatever - find the answer on the internet, quick. I would rather them avoid - even perhaps be afraid of - synonyms and triggers than think they are an OK approach in general as well.

If I said "altering current schema at logon avoids having public synonyms or private synonyms in your login schema pointing to your object-owning schema" that begs the question "well, why not use synonyms" - so to short circuit that, I define them as evil.

I'll let an application have at most one public synonym, if any. Public synonyms are another 'feature' I wish we didn't have... They make trojan horses all too possible and as importantly they flood the namespace - making consolidation of many developed applications impossible.

Kai, January 26, 2009 - 5:30 am UTC

I think Tom is right in this case. If a feature is abused far more often than used, you have every right to declare it "evil" if only tounge in cheek.

Remember "GOTO considered harmful"? It's not called "GOTO considered inappropriate except in very special cases".

In this case: developers use several schemas for an application (good). They decide they don't like to prefix their tables and just create public synonyms for everything (bad). Along comes the new DBA, sees that the company has database servers running multiple instances (very bad) and suggests to consolidate them into a single instance to simplify management...and the developers tell him it can't be done because every single application has a table named users, a table named logging, a table named configuration, all competing for the same public namespace. So just because someone liked synonyms, the server performs far worse than possible with the given hardware...or needs more expensive hardware for the same performance. Bad, bad, bad.

Yes, public synonyms have their uses. But just like triggers...you better be sure you really need them if you want to use them.

Oleksandr Alesinskyy, January 26, 2009 - 6:53 am UTC

I may understand why public synonyms should be marked as "evil", but what is not Ok with private synonyms?

You have written "evil synonyms" providing no distinction between public and private ones.
Tom Kyte
January 28, 2009 - 7:49 am UTC

they are not necessary, they add to the parse process, just yet another level of indirection hiding what the real object is. just use an explicit reference to the object.

private synonyms, simply less evil than public.


(if the private synonym is used to hide a database link, I would rather use a view as it imports the metadata locally...)

A reader, January 26, 2009 - 10:20 am UTC

What if you have an application that needs tables from multiple schema? SET CURRENT_SCHEMA is not going to help in this case because you are selecting from many schema. Private synonyms work but what if there are hundreds of users - you don't want to create private synonyms for that many users. Talk about maintenance - every time you add or drop an object you have to maintain private synonyms for every single user.

I think Public synonyms are okay as long as you use them judiciously and are fully aware of the implications - i.e. you can only have one instance of a customer or client or orders or whatever.

How about using views to mask the schema name. You create a view for every single table and the select clause has the schema name embedded. You create all the views an application needs in a single user/schema and then you could use SET CURRENT_SCHEMA to the schema containing all the views. Thus you can overcome the situation where tables from multiple schema are needed without using synonyms. What do you think of that Tom?


Tom Kyte
January 28, 2009 - 7:58 am UTC

Your application can (and should) just reference the objects.

public synonyms -> evil.
private synonyms -> less evil than public.

I have no problems with actually referencing the objects specifically. Not a fan of layers of indirection.

synonyms

A reader, January 26, 2009 - 4:19 pm UTC

I think if you have to change the schema implies you have a problem in your design. private synonym is the way to go..

ALTER SESSION SET CURRENT_SCHEMA=DEVLPMT


UNLESS you are doing it in development to write a generic script that may support multiple application...

synonym can be wrapped with any table/views and only certain grants can be given to those objects so may be it is little secure too!

Tom Kyte
January 28, 2009 - 8:04 am UTC

layers of indirection are not needed, not necessary. There is absolutely nothing wrong with scott.dept, a lot less confusing than just dept when you have to chase down "what the heck is dept" and follow the pointers.


and no, using the alter command does not mean a flaw in your design. Not anymore than your desire to use a synonym would be.

But why?

A reader, January 27, 2009 - 1:10 pm UTC

>making consolidation of many developed applications impossible

Why consolidate when you can have 27 databases on a single machine? ;-)

That way we could leave all are public synonyms in place. ;-)

It does get a little tricky each time we add a new database... (PGA, SGA sizing)

The whole thing seems to be getting a little sluggish too...
Tom Kyte
January 28, 2009 - 2:51 pm UTC

error sys.htp

yagmur, January 27, 2009 - 2:03 pm UTC

hi tom ;

I have a problem with my codes.the lines are ...
create or replace PROCEDURE hello_world
IS
BEGIN
HTP.print ('<html>');
HTP.print ('<head>');
HTP.print ('<title>You knew it was coming...</title>');
HTP.print ('</head>');
HTP.print ('<!-- ');
HTP.print ('This phrase is in every computer book.');
HTP.print ('--!>');
HTP.print ('<body bgcolor=blue>');
HTP.print ('And here it is .... Hello, World!');
HTP.print ('</body>');
HTP.print ('</html>');
END;

and my errors are :

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 354
ORA-06512: at "SYS.HTP", line 7
ORA-06512: at "SYS.HELLO_WORLD", line 4
ORA-06512: at line 2

can you help me.have can I solve this errors ??

Tom Kyte
January 28, 2009 - 3:07 pm UTC

if you want to run from sqlplus, you need to 'pretend it was the web'. You need to call owa init, consider:


ops$tkyte%ORA11GR1> exec htp.p( 'hi' );
BEGIN htp.p( 'hi' ); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 356
ORA-06512: at "SYS.HTP", line 1368
ORA-06512: at "SYS.HTP", line 1443
ORA-06512: at "SYS.HTP", line 1735
ORA-06512: at line 1


ops$tkyte%ORA11GR1> set echo on
ops$tkyte%ORA11GR1> @owainit
ops$tkyte%ORA11GR1> declare
  2          nm      owa.vc_arr;
  3          vl      owa.vc_arr;
  4  begin
  5          nm(1) := 'WEB_AUTHENT_PREFIX';
  6          vl(1) := 'WEB$';
  7          owa.init_cgi_env( nm.count, nm, vl );
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> exec htp.p( 'hi' );

PL/SQL procedure successfully completed.



I have a little "owainit" script I call to set up the package.

Flexibility

Tony, January 28, 2009 - 9:29 am UTC

We have support environments containing multiple historic versions of the app, which are held in their own schema, on a single db instance. Private synonyms allow us to switch to the appropriate version for testing, without having to maintain multiple database instances.

Private synonyms also allow us to provide a single entry point into a system combining multiple application schemas. ie. as in eBS user APPS is the master for GL/BOM/AP/AR.

Hardcoding schema.object_name is not an option for us, we see the schema as a logical container for the APP which could change over time. Products change their name, companies get new owners etc..

Surely there has to be a better way to provide flexibility without the burden on SQL parsing/versions against the database?
Tom Kyte
January 28, 2009 - 3:57 pm UTC

well, you can always templatize your install scripts, couldn't you?

wait, you did - you know, for the install of your synonyms! Why not for the base case itself?

rhetorical question, APEX for example does that...

...
Surely there has to be a better way to provide flexibility without the burden
on SQL parsing/versions against the database?
....

not sure what you mean, your approach actually increases this burden - a lot.

synonyms

A reader, January 30, 2009 - 5:27 pm UTC

Ok I saw a reason behind
1.)public synonyms -> evil.

but what is the exact reason for #2

private synonyms -> less evil than public.

what is the problem using it..

if you say "what the hell is dept"
then doesn't it apply to Views as well ?




Tom Kyte
January 30, 2009 - 9:07 pm UTC

why do people have to put unnecessary words into my mouth. Where did I say anything similar to what you attribute to me? Please.



it is an entirely unnecessary level of indirection.

a view "create or replace view select * from scott.dept" would be a bad idea, not smart, evil.


Now, I did go further and say "if database link involved..." - Maybe you do want to hide database link (in hopes that tomorrow silly database link goes away and we get to the single database we meant to have...). I said then - use a view, not a synonym as the view imports the metadata (makes compiling easier, makes lots of things easier, we can actually describe the view without hitting the dblink - build reports on it as they typically want to query user_tab_columns and so on).

private synonyms - less evil than public.

views for things over dblinks - created as "select * from t@remote", wish we didn't have to do it, but better than synonym.

If you were going to do it without dblinks, I'd prefer the view as the view does in fact import the metadata into the schema - more things work correctly with that (many tools, reporting things, whatever won't be able to understand a synonym - if you have something in user_tables and user_tab_columns - they do, that would be a view)

Private synonyms

Oleksandr Alesinskyy, February 01, 2009 - 8:12 am UTC

You said "hey are not necessary, they add to the parse process, just yet another level of indirection hiding what the real object is. just use an explicit reference to the object.".

I dare to disagree - there are absolutely valid situations where private synonyms would definitely be beneficial, just a couple of examples:

1. Application that uses data from "additional" schemes with names are not known in advance (e. g. vary from installation to installation), and likely are out of control of application developer.

2. Many schema sets in the same DB (e. g. development, internal testing, acceptance testing and so on) - this scenario we have in our company (each release undergo 4 stages before it reaches production - and with each stage its own set of schemes is associated).

4. Yes, DB installation scripts may be parametrized, but the problem is that sometimes "foreign" objects are referred from application code, which mean that code needs to know that some objects are foreign. Not very wise idea as for me.

3. Yes, installation scripts may be parametrized but it means that each piece of code (even if it resides in DB) needs to know that this object may be foreign, e.g. this information is multiplied, I dare to say "denormalized". And if schema referred need to be changed at some point in time, they you need to hunt it down all over your code - and thing become much worse if you need do it as well in the code that lives outside of database. Or if you (or person that has to do this switch) do not have access to the sources (e.g. DB code is wrapped).

4. Concerning views - it may be a good idea to hide remote table with view, but what would you do with remote procedure?

As for above mentioned benefits of private synonyms highly overweight possible drawbacks. Yes, I may imagine application for which parsing overhead imposed by synonyms would be intolerable, but my guess that it is rather rare case.
Tom Kyte
February 02, 2009 - 10:47 am UTC

1) make it part of the installation process then, use STATIC SQL with a direct reference. You can make making the synonym part of the install right? If yes, then you can make putting the schema name in part of the install as well.


2) here it were synonyms are truly bad (think trojan horse for example, but it is confusing none the less)

4) why not? so what if they know they are "foreign". Code needs to know stuff - it isn't bad to have it CLEAR "this is something in some other place".

3) (we need an order by! and a unique index on the numbered list :) )
Hunt it down? Or query user_dependencies. But in any case, if you change a synonym to point to something else, that is a pretty big major league change - huge I would say.

4) There might be one case for a private synonym.



as for the rather rare comment, I'd say the same about your point #3



bottom line: avoid synonyms, you usually do not NEED them.

Avoiding Synonyms

Lewis Cunningham, February 12, 2009 - 1:31 pm UTC

Hi Tom,

Just wanted to throw out an example.

For those with installations and multiple schemas, it's easy enough in the code to, when referencing an object, use "&SCHEMA_NAME".<object>. Most automated install tools and processes use SQL Plus. It is ridiculously easy in sql plus to define a variable that will point at the correct schema. In test, use DEFINE SCHEMA_NAME = TEST, in dev use DEFINE SCHEMA_NAME = DEV, etc.

You'll even see code like this in mutiple Oracle products when they get installed. It works, it's easy and it avoids synonyms.

Thanks,

LewisC

Which schema's table space will use

Atanu Chatterjee, November 11, 2010 - 1:07 pm UTC


If I connected to schema SCOTT and then execute

ALTER SESSION SET CURRENT_SCHEMA=DEVLPMT;

After that if we execute any query then which sachem's temp table space will use, scott's or Devlpmt's ?

Tom Kyte
November 11, 2010 - 3:19 pm UTC

ops$tkyte%ORA11GR2> create global temporary table gtt( x int );

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> select username, tablespace from v$sort_usage where username is not null;

no rows selected

ops$tkyte%ORA11GR2> insert into gtt values ( 1 );

1 row created.

ops$tkyte%ORA11GR2> select username, tablespace from v$sort_usage where username is not null;

USERNAME                       TABLESPACE
------------------------------ -------------------------------
OPS$TKYTE                      TEMP   <<<=== ops$tkyte's

ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> alter session set current_schema = scott;

Session altered.

ops$tkyte%ORA11GR2> select username, tablespace from v$sort_usage where username is not null;

no rows selected

ops$tkyte%ORA11GR2> insert into ops$tkyte.gtt values ( 1 );

1 row created.

ops$tkyte%ORA11GR2> select username, tablespace from v$sort_usage where username is not null;

USERNAME                       TABLESPACE
------------------------------ -------------------------------
OPS$TKYTE                      <b>TEMP2</b>           <<== scotts.



documentation states this is what will happen:

http://docs.oracle.com/docs/cd/E11882_01/server.112/e17120/general009.htm#ADMIN02101

A reader, February 09, 2011 - 4:43 am UTC

What whith DDL ?
Please correct me if I'm wrong (after all that's the goal of these topics, getting rid of false idea's etc)
I thought that a user x, which becomes user y through alter session set current_schema=y, can NOT select from tables of user y if he (user x) has no select privileges on them.
Is trhis correct ?
And if so, what happens if a user x tries to create a procedure in the schema of y (create procedure p) and x does not have the "create procdure" privilege, but y has this system privilege ?
Can he create the procedure ?
Will the procedure be valid if the procedure requires privileges on objects which belong to y and on which x has no privileges ?

Just a theoretical thought on the subject.
Tom Kyte
February 10, 2011 - 4:12 pm UTC

that is correct, alter session set current schema does NOT give you any privileges you did not already have.

Can he create the procedure ?


Not unless they have that privilege themselves, setting the current schema does NOT give you any additional privileges.


setting the current schema in after logon trigger

Raju, April 12, 2012 - 10:16 am UTC

Tom, this is a nice useful thread.

I have a question :

In our database, we have a main schema which owns all the objects and a app schema(which is to be used by front end apps) and this app shcema going to have necessary privilages on the main schema objects. now we would like to use after logon trigger on the app schema and set the current schema to the main schema.
is that ok?(i.e using the logon trigger and setting the current schema?).

Because in the below article(search for current_schema) tehre is a note that says "Note: Oracle recommends against issuing the SQL statement ALTER SESSION SET CURRENT_SCHEMA from within a stored PL/SQL unit."

Does it mean to use dbms_session instead of that?

http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions172.htm

your thoughts please?

as always, thak you.
Tom Kyte
April 12, 2012 - 1:24 pm UTC

they were talking about procedures/packages, it is not clear. It is ok in a logon trigger.


how with dbms_session?

Raju, April 12, 2012 - 1:46 pm UTC

thanks Tom.

how do we set the current schema using dbms_session? just to avoid using execute immediate in logon trigger?
Tom Kyte
April 12, 2012 - 2:15 pm UTC

you don't dbms_session does not do that.

db links of main schema

Nagaraju, February 28, 2013 - 3:23 am UTC

Hi Tom,

this is a great topic, thanks for your answers.

My Situation : i have a main schema say "main". And an app schema say "app1". app1 user has connect privilege.

we added the app1 user recently and changed the application to connect through this user than through the main user. We gave the necessary privileges on the required objects from "main" to "app1". And,
In the after logon trigger of app1 schema i set

alter session set current_schema = main;

above approach is good, as there is no code change in the existing application.

But,

main user owns a database link "db_link". We are not able to give any privilege on this db link to app1 user.

Due to this, i am not able access the remote tables like tab1@db_link from the app1 user.

I can make it public database link, But all other users get access to this link.

I can create view or synonym on the remote table(thru db_link) and grant access on this view/synonym to app1 user. But this needs a code change.

Is there another solution besides the above 2.


Tom Kyte
February 28, 2013 - 8:03 am UTC

you could create the database link in app1.

Public Synonym Use

Brian, January 15, 2014 - 12:03 am UTC

Whenever someone says something in the database is a bad idea, my first consideration is performance.... does it hurt performance.

Do public synonyms impact execution performance? Or do you mainly not like them because it creates confusion.

Personally I use 2 schemas to implement the "poor man's virtual database" in SE.

Schema1:
- holds all tables each defined like tablename_t
- each table has a client_id column
- each table has a view:
create view tablename as
select *
from tablename_t
where client_id = sys_context(....)
- each table has a public synonym this view
- each table grants select on tablename to schema2

Schema2:
- has no tables
- has no views
- sets syscontext and queries tablename

Every table in the db follows this pattern as views are created by calling as stored proc, so they always get created under the same pattern.

Having around 150 tables, should I consider getting rid of the public synonyms?




Tom Kyte
January 15, 2014 - 7:59 pm UTC

... Whenever someone says something in the database is a bad idea, my first
consideration is performance.... does it hurt performance. ...

why? I might think "security, does it hurt that", or "maintainability, does it hurt that", or "understandability, does it hurt that", or ".....". Performance is in there, but it is just one factor out of *many*...



... Do public synonyms impact execution performance? Or do you mainly not like them
because it creates confusion. ....

they negatively impact performance
they create confusion
they increase the change of security related issues
they make consolidation harder (namespace collisions before 12c with pluggable databases)

and so on. So, there are many reasons to avoid synonyms.



I would get rid of the public synonyms.


I would either:

o use private synonyms in schema2
o use views from schema2 to schema1
o use alter session set current_schema=schema1 after logging into schema2


https://www.google.com/#q=site:asktom.oracle.com+public+synonyms+performance

Brian, January 16, 2014 - 4:37 am UTC

Thank you for your advice.

I've created views in schemaB. It took less than an hour to modify the scripts, exec and recompile--all while the system is in full production mode and not 1 problem.

It is always good to improve security while at the same time improving performance. I've known that public synonyms are not a good first choice, just never took the time to consider why.

I think what did it for me was the fact that another schema could recreate a public synonym (either by installing 3rd party software, an misguided dba/devloper or a security hacker) and cause problems.

So now schemaB's views query schemaA's views which (created with the sys_context and "with check option".

All in all this is the greatest improvement so far this year :)

Thank you once again.


compilation issue

a reviewer, November 03, 2022 - 7:10 am UTC

I have two schemas eg. Stage and Prod. I am creating a function in Stage schema which is referencing some tables reside in Prod schema.
When i will call the function from Prod i will send schema name as a parameter so that i can change the schema using:
execute immediate 'ALTER SESSION SET CURRENT_SCHEMA='||<p_schemaname>;
after that the function can refer the tables without any issue.
but i am getting compilation error because the tables do not not exist in Stage schema.
please suggest.
Thanks in advance.
Chris Saxon
November 03, 2022 - 11:21 am UTC

This approach won't work for SQL inside procedures/functions. Setting the current schema only comes into effect when you call the unit. When you're compiling it, the objects will still resolve to whichever user you're connected as.

You need to either:

- Fully qualify schema in table references (user.table_name)
- Create synonyms/views in the stage schema that reference the corresponding object in the prod schema

Clarification assistance

Dave, December 19, 2023 - 2:24 pm UTC

Thanks in advanced for your time.

Are you saying using the alter session set current_schema is more beneficial and or useful then either prefixing the table with schema own and public synonyms?

I ask because one of our applications has a trigger for every account that logs in to set the schema to the main/app schema (only 1) in the instance.

Im just wondering if this is recommended. Unfortunately the developers are having a hard time keeping up with the new Oracle architecture, so I like to provide info for them.

Your answers above removed security concerns, knowing privs are not inherited with the set current schema.

Thanks
Dave

Chris Saxon
January 10, 2024 - 4:55 pm UTC

Compared to using schema prefixes, setting the current_schema is more flexible - your code isn't bound to a given schema so can be used for schemas with different names (assuming object names are consistent).

Public synonyms can be a security risk; they enable bad actors to change them to point to other schemas or expose objects that should stay hidden. As the security guide says:

The CREATE PUBLIC SYNONYM and DROP PUBLIC SYNONYM system privileges convey a great deal of power to these users. Do not grant these privileges to users, unless they are trusted.

Current_schema does have drawbacks though:

- If queries access objects in many schemas, you'll still need to schema prefix or create synonyms for some
- Acting as a different user to the one you're connected as can lead to surprising results. e.g. any objects you create will be in the current_schema, but user* dictionary views still reference the connection schema.

Personally, I prefer proxy users - these give a similar effect to set current_schema. You could give each developer their own proxy user with the ability to connect through to the target schema.

This has a couple of advantages:

- No password sharing; each developer uses their own password to connect
- Finer-grained control of access; if someone changes job, you can revoke their access by locking/dropping their proxy user without impacting anyone else

The big difference with proxy users (compared to current_schema) is you run with the same privileges as the user you're proxying to.