Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Frederic.

Asked: February 10, 2003 - 8:51 am UTC

Last updated: January 04, 2013 - 3:15 pm UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi Tom,
We created a “IND” schema in which we created a “TEST” procedure and granted execute on “TEST” to public. When connected as IND or any other user, execution of “TEST” fails:
SQL> exec ind.test
BEGIN ind.test; END;

*
ERROR at line 1:
ORA-06550: line 1, column 11:
PLS-00302: component 'TEST' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

We know of the public synonym IND which points to USER_INDEXES, but to us this error is nonsense. How can Oracle be confused between a synonym and a schema? And why isn’t IND one of Oracle’s reserved words?

Could you tell us how Oracle is resolving this call. Thank you.


and Tom said...

Hey, yet another reason to avoid public synonyms -- thanks for that.

IND isn't a reserved word -- it just is public synonym to a dictionary table.

Now, the name resolution routines take ind.test and say "hmmm, what could ind be. is it a local object (eg: is there a package IND in the current_schema or a private synonym). No, there isn't. Ok, is there a public synonym? yes, yes there is -- ok, replace ind with the synonym".

It would goto checking to see if IND was a schema after that. Consider this -- what if it went after the schema IND first and not the public synonym?

select * from ind;

would be non-functional. Something has to give here -- in this case, the schema "loses".


You will have to find another schema name in order to avoid this issue entirely.

Oracle is not "confused" here at all -- IND to Oracle is first and foremost a public synonym. The fact that there is a schema IND is somewhat secondary.



Rating

  (33 ratings)

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

Comments

A reader, February 10, 2003 - 4:21 pm UTC


Is there any case?

Bo, February 10, 2003 - 4:25 pm UTC

Hi, Tom.
There're several discussions about public synonym on this web site. You suggested to avoid public synonym if it's possible.

Is there any case to show advantage of public synonym? Otherwise, why do we need it?

Tom Kyte
February 11, 2003 - 7:52 am UTC

I use it for a few top level things to get into the application. for example, look at the URL above. F is a public synonym in my database. there are hundreds of stored procedures "below f", but we do not use synonyms for anything other then F.

it is purely a matter of convienence - it has its good sides (ease of use) and its downsides (scalability, resource consumption, confusion)

Is this a bug?

A reader, November 26, 2003 - 1:38 pm UTC

I ran the following in 9.2.0.4.


SQL> conn scott/tiger@test
Connected.
SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> create public synonym emp for hr.employees;

Synonym created.

SQL> conn hr/hr@test
Connected.
SQL> grant select on hr.employees to scott;

Grant succeeded.

SQL> select count(*) from emp;

  COUNT(*)
----------
       107

SQL> conn scott/tiger@test
Connected.
SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> rename emp to emps;

Table renamed.

SQL> select count(*) from emp;

  COUNT(*)
----------
       107

SQL> rename emps to emp;

Table renamed.

SQL> select count(*) from emp;

  COUNT(*)
----------
       107

SQL> select count(1) from emp;

  COUNT(1)
----------
        14


Why do the last two queries return different results? 

Tom Kyte
November 26, 2003 - 2:52 pm UTC

whats session_cached_cursors set to?

Thanks Tom. Here are the parameters related with cursors:

A reader, November 26, 2003 - 4:38 pm UTC

SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     0 

Tom Kyte
November 27, 2003 - 10:15 am UTC

that, that would be an issue -- yes. I've a simplier testcase. i filed a bug on this. #3285915 (its not public right at this instant until it gets screened and all)

Public Synonym under Public Schema

Abhi, December 17, 2003 - 5:53 am UTC

hello Sir,

Could you pls. Explain relationship between

OBJECT_NAME OWNER OBJECT_TYPE
---------------------------------------- -------- ----------------
GV_$MAP_FILE_EXTENT SYS VIEW
V_$MAP_FILE_EXTENT SYS VIEW
GV$MAP_FILE_EXTENT PUBLIC SYNONYM
V$MAP_FILE_EXTENT PUBLIC SYNONYM

How could v$map and gv$map be created under public schema .. and whats is reason for that.

Thanks

Tom Kyte
December 17, 2003 - 7:01 am UTC

public synonyms are always in the schema -- PUBLIC.


v_$<name> is a view (as all v_$.... views are)

v$<name> is the public synonym we all use to access this view.


GV is for RAC (clustered environments) -- lets you see V$ views from all instances in a single monster view.


It is just the way it is. there is a view (v_$....) and a public synonym that points to the view (v$.....)



Thanks Sir,

Abhi, December 17, 2003 - 7:17 am UTC

Thanks Sir ... :)
A personal Request.
Sir From where we can get your pics.
I have searched many times at www.oracle.com for your
Video Clips .. etc.. but did not get any.


Thanks
Abhi


A reader, December 17, 2003 - 3:33 pm UTC

Abhi, the lastest issue of Oracle Nov/Dec 03
has a picture of Tom on the cover page and inside as well,
didn't you see it.

FYI last row 2nd column.



Reply to Reader

A reader, December 17, 2003 - 10:33 pm UTC

Dear Reader,

In last two lines ..

I said , "I have searched for VIDEO CLIPS"

VIDEO CLIPS != PICS

FYI

Sir, I am expecting your answer .. :)



Stalker Alert !

Another Reader, December 18, 2003 - 5:45 am UTC

Scarey..... VERY Scarey

Ravi, July 06, 2005 - 3:04 am UTC

Tom,
In the original question, if the user had logged on to IND schema and simply said

exec Test;

1)Would that have executed Test?

2)If yes, is it better practice NOT to qualify schema names while running jobs and let Oracle decide name resolution?


3)Is the order of resolution when NOT qualifying schema is

Say if I'd just said exec test;

a)Look for an object with name Test in current Schema
b)If a) not found, look for a Private Synonym
c)If a)and b) not found, look for a Public Synonym?

4)I find that Oracle gives almost similar priority for objects and private synonyms, ie wouldn't let me create same named objects and private synonyms. So is it right in saying that they pretty much run dead-heat during name resolution?

Am I right in saying the reason why 4) happens is because if I were to do something like

Create table x as select * from x;

Where x is an existing object and an either be a table or a private synonym, the Select part of query would evaluate to the existing private synonym and prevent duplication of object name. Am I right here?



Tom Kyte
July 06, 2005 - 7:49 am UTC

1) then wrote:

<b>When connected as IND</b> or any other user, execution 
of TEST fails:
SQL> exec ind.test
BEGIN ind.test; END;


 

A reader, July 06, 2005 - 8:05 am UTC

Did you on purpose leave white space for answers to Questions other than 1)?

Tom Kyte
July 06, 2005 - 8:25 am UTC

yes, they were "and if so" questions -- but it was not so...

public synonyms

ian galllacher, September 02, 2005 - 7:36 am UTC

Tom,

I note your distaste for public synomyms !

However, I have been using then on tables for the last 10 years without any hassle. I use Oracle Forms for my applications and have very few stored procedures - all code in Forms ( except some nice ones pickuped from yourself !)

Each public synonym has the same name as the table it is created for
ie. table patient has public synomym patient

All users access the database via Forms and never via say
sqlplus

Have i been going about it the wrong way for the last 10 Years?

Any comments would be appreciated

Thanks

Ian



Tom Kyte
September 03, 2005 - 7:03 am UTC

consolidate your application with 5 other applications now.

You have a "global namespace" name. There can only be one public synonym "ABC", suppose both you and some other application want it?


Yes, I would suggest reducing the number of public synonyms to as little as possible, 0 and 1 being the best numbers :)



public synonyms again

Ian W Gallacher, September 11, 2005 - 10:00 am UTC

Hi Tom,

The chances of my system being consolidated with another are about 0.

If you accept this then what are the downwfalls of using public synomyms ? Again bearing mind I use Oracle forms exclusively and have have only one package in the database -
the one described by yourself? for auditing columns

Comments again would be appreciated

Thanks

Ian

Tom Kyte
September 11, 2005 - 10:28 am UTC

you say 0 today, three years from now - you have no idea.


I prefer to not use any more than ONE public synonym personally.


You can put an

alter session set current_schema=YOUR_APP_SCHEMA;

in your form right after you get logged in, then


select * from t;

becomes:

select * from YOUR_APP_SCHEMA.t;

intead of

select * from CURRENT_USER.t;



public synonym

ian gallache, September 11, 2005 - 12:16 pm UTC

Thanks

have tried that out some time ago once I read your comments

worked fine in the form that i put the edit in but once i called another form ( another session ? ) schema was lost and couldnt access table(s) in form called

so gave up !! and put public synonyms back on

Application has been running for last 15 years without any
consolidation with other apps

Regards

Ian





Tom Kyte
September 11, 2005 - 12:27 pm UTC

"when new form instance" trigger.

If you are happy, that is perfectly fine. 15 years ago, a single machine typically couldn't consolidate.

Now, I'm quite sure my laptop could blow away the server you were using back then and consolidation a fact.

public synonym

ian gallacher, September 11, 2005 - 3:58 pm UTC

Hi

What makes consolidation a big issue ?. Most pcs today would blow away servers 15 years old - big deal !

Again, forget about consolidation. any other reasons for NOT
using public synonyms ?

Performance issues ?

Look forward to hearing your comments





Tom Kyte
September 11, 2005 - 6:31 pm UTC

I already told you, if you be happy - cool, great.

The point about the pc's of today was -- 15 years ago consolidation wasn't an option perhaps.

Today it is. Things change. You are 100% sure this will never be consolidated into a larger collection of applications (it is one thing I would do immediately personally)

The only thing I know is that the second I'm 100% sure of something - something will come along to change my mind. Or someone else will change my mind for me.

Yes, there are performance implications - used to be very measurable, have gotten less so over time.

But, if you've been running this for 15 years, it must already run faster than fast enough

public synonym

A reader, September 12, 2005 - 4:12 am UTC

Hi

Thanks for getting back to me

Will leave public synonym issue alone and look for more pertinent issues

As a point of intererst my particualr application is allows Clinical Information within Hospitals to be recorded and reported on. Interfaces are made to other Hospital systems via ODBC/HS connections and uploading of CSV files

Thanks again for your time

Ian

drop public synonym dual

A reader, September 12, 2005 - 5:58 am UTC


Tom Kyte
September 12, 2005 - 7:35 am UTC

er? useful, thanks.

public synonym

ian gallacher, September 12, 2005 - 4:53 pm UTC

Came across this trigger on the ioxra web site which replaces public synonyms

REM my version
REM From IXORA web Site
REM
REM replace public synonyms with this trigger
REM dont have to amend application code
REM


drop table system.default_schema;

create table system.default_schema (user_name varchar2(30), schema_name varchar2(30));

REM
REM User PRS has created all tables and indexes
REM

insert into system.default_schema values ('PRS','PRS');

create or replace trigger
system.set_current_schema after logon on database
declare
default_schema varchar2(30);
begin
select schema_name into default_schema from system.default_schema where user_name = 'PRS';
execute immediate 'alter session set current_schema = ' || default_schema;
end;
/

Have dropped my 1000+ public synonyms and application running without any problems and forms compiling Ok

Is this a reasonable solution to replacing synonyms with
alter session ?

Seems fine to me

any comments would be appreciated

Ian

ps bookmarked this page and now getting

ORA-01400: cannot insert NULL into ("ASK_TOM"."WWC_ASK_QUESTION_ACCESS_LOG$"."DISPLAYID")
Error !



Tom Kyte
September 12, 2005 - 5:44 pm UTC

Use the bookmark this page link to get a safe link to bookmark.


Yes, that is just like using a when new form instance trigger to do the same in your application.

I don't like the idea of using SYSTEM however, use your own account

public synonym

ian gallacher, September 13, 2005 - 4:47 am UTC

Hi

Thanks for your comments

will use my account to create trigger

Hopefully last of this thread from me

Ian

Object Name Resolution

VLS, January 08, 2009 - 5:13 am UTC

Hi Tom,

This is in the same line of the Original Question posted by a reader long back.

I was going thru Oracle 10g Concepts Documentation, Chapter 6 "Dependencies Among Schema Objects". The document, under heading "Object Name Resolution" says, that

"1. Oracle attempts to qualify the first piece of the name referenced in the SQL statement. For example, in hr.employees, hr is the first piece".
Further, it says

"a. In the current schema, Oracle searches for an object whose name matches the first piece of the object name. If it does not find such an object, then it continues with step b."

Now, assuming that I have a select privilege on hr.employees and I create a table called HR in my own schema, if I execute a query from my schema

"select count(*) from hr.employees",
since hr is a local table in my current schema, shall it get the count from hr table or from hr.employees ? If later, then can you please explain this concept.

Regards
VLS
Tom Kyte
January 08, 2009 - 9:27 am UTC

http://docs.oracle.com/docs/cd/B28359_01/server.111/b28318/dependencies.htm#i3136

use that instead, that was corrected.

hr.employees refers to the table employees in the hr schema.

Security Issue in using Public Synonym

A reader, December 16, 2009 - 11:41 am UTC

Is there any securoty issue in using public synonym ?
Tom Kyte
December 16, 2009 - 1:22 pm UTC

not specifically, some would say "yes", I vote "no, not really"

If you do not have access to the object the synonym points to, you won't be able to select/modify/execute it. It does not give you any privileges beyond what you have.

Those that say it is a security risk would point to the fact that "if you can see the synonym, you can see the name of what it points to, it'll tell you the name of a table/procedure/whatever - that you shouldn't know"

for example

scott%ORA11GR2> select * from all_synonyms where synonym_name = 'DBA_OBJECTS';

OWNER                          SYNONYM_NAME
------------------------------ ------------------------------
TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
DB_LINK
-------------------------------------------------------------------------------
PUBLIC                         DBA_OBJECTS
SYS                            DBA_OBJECTS



scott%ORA11GR2> desc dba_objects
ERROR:
ORA-04043: object "SYS"."DBA_OBJECTS" does not exist



Scott can tell that DBA_OBJECTS is a synonym.

Further, Scott knows that SYS owned a Thing called DBA_OBJECTS. This is what some would say is a security issue.

It is, to a degree, but a pretty small degree. It is part of what I call 'security via obscurity', the less you know about something, the harder it would be for you to attack it.

But if it were a big deal, then open source software..... you fill in the blanks.



In a system that is not secured - so that I could gain access to schema X's objects - yes, knowing the name of what is there would get me one step closer. But the fact remains - that you have allowed me somehow to gain access to schema X's objects!! and I'm not supposed to.

security issue with public synonyms

Ben, December 16, 2009 - 2:02 pm UTC

The security problem I see with public synonyms is who has the CREATE PUBLIC SYNONYM privilege. Every schema with this priv in effect has the priv to REPLACE any object by
1)create their own object
2)modify public synonym to point to their object

yes, there are security restrictions that limit the impact of this (the man in middle schema would need privs on supporting objects or call the real object, etc)

good policing of this priv makes this a mostly moot point.

However, I can attest that overuse of public synonms can cause development headaches as developers copy objects into their own schemas

Tom - I laughed and cried during your WorstPractice presentation at SLOUG & thought you should include a "create a public synonm for every object" because it makes it simpler to code ;)
Tom Kyte
December 16, 2009 - 2:29 pm UTC

Yes, there are many reasons to avoid public synonyms like the plague - I should mention that there.

Public synonyms should not be used - it makes consolidation virtually impossible.


And thanks for the Trojan Horse story - yes, they can be abused by a privileged user with create public synonym - very much like the 'ANY' privileges, it should not be granted lightly.

Public synonyms: can't we use them in Stored procedures/packages.

Durgesh, December 28, 2009 - 2:38 am UTC

Hi Tom,
Thanks for very useful information on synonyms!
Could you please also put some light on how to use a public synonym in a stored procedure or package.
On doing so, I'm getting ORA-00942 "table or view does not exist" error.Whereas, the stand alone SELECTs/DML are working fine for the same public synonym.
Even, If I use the same code of the procedure in an anonymous block it works.
Please Help!
Tom Kyte
January 04, 2010 - 7:23 am UTC

sounds like

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551289900368934430


scott%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> drop public synonym tkyte_t;

Synonym dropped.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> create public synonym tkyte_t for ops$tkyte.t;

Synonym created.

ops$tkyte%ORA10GR2> grant select on t to CONNECT;

Grant succeeded.

ops$tkyte%ORA10GR2> connect scott/tiger
Connected.
scott%ORA10GR2> select * from tkyte_t;

no rows selected

scott%ORA10GR2> begin
  2  for x in (select * from tkyte_t) loop null; end loop;
  3  end;
  4  /

PL/SQL procedure successfully completed.

scott%ORA10GR2> create or replace procedure p
  2  as
  3  begin
  4          for x in (select * from tkyte_t)
  5          loop
  6                  null;
  7          end loop;
  8  end;
  9  /

Warning: Procedure created with compilation errors.

scott%ORA10GR2> show errors
Errors for PROCEDURE P:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/11     PL/SQL: SQL Statement ignored
4/25     PL/SQL: ORA-00942: table or view does not exist
scott%ORA10GR2>
scott%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> grant select on t to scott;

Grant succeeded.

ops$tkyte%ORA10GR2> connect scott/tiger
Connected.
scott%ORA10GR2> create or replace procedure p
  2  as
  3  begin
  4          for x in (select * from tkyte_t)
  5          loop
  6                  null;
  7          end loop;
  8  end;
  9  /

Procedure created.

scott%ORA10GR2> show errors
No errors.
scott%ORA10GR2>



you have access to the object via a role, roles are not enabled during the compilation of a stored procedure by design.

public synonym

A reader, May 25, 2010 - 10:24 pm UTC

Tom:

someone suggests to use a public synonym instead of a public db link like "xxx_link".

Do you see any correlation between the two or any benefit of using a synonym for a db link (I think that is what they mean).

Tom Kyte
May 26, 2010 - 7:20 am UTC

you cannot use a synonym INSTEAD OF a database link

you can use a synonym to HIDE a database link
or you can use a view


I will never propose the widespread use of PUBLIC synonyms - they are evil, to be avoided at all costs. They make consolidation impossible (namespace is cluttered) and when used widely can open opportunities for security issues (not because they are insecure, but because it is far too easy to redirect code to use something it was not meant to use).

I will propose the use of a view if you want to hide the database link, create view v as select * from t@remote.

In fact, I would prefer that over using the dblink directly, that create view will import the metadata from the remote size - populate the dictionary - make it so you can compile plsql faster (since it need not dereference the dblink to compile) - it is a pretty good idea.


So, use a view.

public synonym

A reader, May 26, 2010 - 8:36 am UTC

Tom:

yes, the claim that the code is easier to maintain using the synonym and if we merge the databses in th efuture we wont need to change the code hat reference the link. you just change the synonym.

so if the code is like this

select * from tableA@db1_link

create synonym tableA for tableA@db1_link

and then code becomes

select * from tableA

DO you agree with that?

I do not like the idea much. Synonyms makes things hidden for me. I like things more visible when reading code.

If I use views, i need to create a view per table in the remote DB. THe only issue here is that you have to recreate the view everytime the remote table changes if you want the view to mirror the table.


Tom Kyte
May 26, 2010 - 9:02 am UTC

use the view

do not use the synonym.


I tried to be unambiguous above, this should clear it up:


do not use public synonyms.

please do use a VIEW to hide the database link.




INSTEAD OF:
create public synonym a_bad_idea for t@remote;

DO THIS:
create view a_good_idea as select * from t@remote;



... THe only
issue here is that you have to recreate the view everytime the remote table
changes if you want the view to mirror the table.
...

so, I view (all pun intended) that as a positive good thing. It'll help you with change management and change control. It'll be a very good thing indeed, something to embrace, not a downside.



Let me ask you something - are you planning on using this in real time - frequently - or as a method to pull data in the background on a schedule?


synonym

A reader, May 26, 2010 - 10:59 am UTC

Tom:

It is used real-time in an online transactional system.

I have been thinking about what you said when code reference a synonym it is really insecure. Some one changes the definition of the synonym and the code is screwed.

Refrencing a table@db_link would be better.

is the only reason though to avoid using them?

From what i gather, you also say that using a view is faster and better than referencing table@db_link. Would it make maintenance anyway easier if we consolidated both databases in the future.
Tom Kyte
May 26, 2010 - 11:11 am UTC

... It is used real-time in an online transactional system....

horribly bad idea, you should rethink that - honestly.

do you want to add a rather large performance penalty?
do you want to make your system infinitely less available?
do you want to make your system infinitely harder to maintain?


forget about this dblink and synonym versus view thing, what do I have to do to convince you to change your entire approach here - a distributed database is not a good idea for a real time online transactional system - not even close to a good idea, in fact about as far away from a good idea as you can get.

synonym

A reader, May 26, 2010 - 5:23 pm UTC

Tom:

it is a long story. One db used to be on another server and was moved over now to the same server the other db is.

so even though there is a link, they are sitting on same machine. That should probably make a difference when you have a remote DB on another location. right?

the future plan is to merge them together and get rid of the links but there are some expenese involved in this.
Tom Kyte
May 27, 2010 - 7:25 am UTC

... That should
probably make a difference when you have a remote DB on another location.
right?
...

nope, it'll be

<same list as above goes right here>


Until they are SO CLOSE that they are actually THE SAME, it'll be a headache and performance issue forever.


... are some expenese involved in this. ...

as there are in the current status quo

synonym

A reader, May 27, 2010 - 12:38 pm UTC

You did not explain your rationale of why a distributed system is very bad for OLTP. You said performance will suffer, less available and harder to maintain.

How so?

I tried several queries using DB link and local tables (I copies the tables locally for testing) and did not see much difference in Elapsed time (ms).

Would now what you said also be true for high-volume OLTP such as stock brokerage, ariline reservations, amazon, etc.

If your application is trivial, it many have minimal impact.

Kind of strange, the you said if one DB is is New Your and one DB is in L.A it would be the same performance impact as if the two DB sitting one machine in New York.

You mean when DB1 makes SQL statement to DB2 on same box, it can go from server 1, then to China, stop at Russia and make it back to New York on the same box. I was thinking you are using very minimal route across the network.
Tom Kyte
May 27, 2010 - 12:51 pm UTC

failure of one database = failure of the other, less available.

2pc is a performance ping.

database links are slower than not using a database link.

distributed queries (even if the database is right next to you) are not as optimal as a single query.

Think of it this way. You are a ticket office clerk. You sell tickets (perform the transaction). Which is more reliable, faster, easier to maintain (think schedule):

a) you get ticket request, you tell them how much money, they pay you, done (commit)

b) you get ticket request, you ask BOB (and only bob, if bob went to take a bio-break, you wait) how much to charge, BOB answers, you tell them how much money, they pay you, you ask BOB "is this good", when and if he says yes, done. (commit)



as for your "kinda strange", it is always very strange to have words put into my mouth that I did not actually say...


When did I same the *same* performance impact???? I said THERE WILL BE one until and unless there is a single instance. Network performance is a tiny piece of the pie here, it would be slower than a non-database link. Just because the two things are near each other does solve anything - the only thing it MIGHT reduce would be some latency.


distributed complexity is what I call this, plain and simple, there is no reason for it.

synonym

A reader, June 01, 2010 - 4:40 pm UTC

Tom:

Did you discuss in any of your books/articles or has oracle release any articles on the cons of using PUBLIC SYNONYMS for code maintenance.

anything in oracle magazine.


thanks,
Tom Kyte
June 08, 2010 - 9:17 am UTC

search for

"public synonyms" avoid

from the home page, you'll see various articles.

Public synonym for data avilability

Nikhilesh, June 15, 2010 - 6:23 am UTC

Dear Tom,
We have to load a big table T1 every week. We load another table T2 using SQLLDR and then rename T1 to T3 and T2 to T1.
But sometimes another process that we can't avoid (its IMP) use (select only) T2 and fails.
To avoid its failure and maintain data availability can we try following
1) Load T2 with latest data -- data is accessed from T1 using table name
2) create public synonym T1 on T2 -- data is accessed from T1 using table name
3) rename table T1 to T3 -- data is accessed from T2 using synonym name T1
4) rename table T2 to T1 -- data is accessed from T2 using table name T1
5) point public synonym to T1. -- data is accessed from T2 using synonym name T1

I have tested it on DEV box (10g Rel-2) and its working fine.

Thanks in advance

Regards
Nikhilesh
Tom Kyte
June 22, 2010 - 10:19 am UTC

why not just use a partition exchange, create T1 as a partitioned table (values less than maxvalue) and then exchange the existing partition in T1 with the table T2?

Public Synonym Vs Partition Exchange

Nikhilesh, June 24, 2010 - 5:19 am UTC

Dear Tom,
I'm not able to convince my manager for "PARTITION EXCHANGE" option. He don't want to alter existing "working functionaly fine" code on production.
He wants to know any potential draw back/risk of "public synonym" option. As we tested it on test intsance we can't show him any. Also with synonym we don't need to change any table structure, no index rebuilding and it needs minimum code change. Moreover I only had suggested the approach to him.... :(
In your reply you didn't mention not use it but suggested another better option. Can you please help me out of this. Is public synonym approach has any draw backs? Any particuler reason (apart from performance) to go for "partition exchange"?

Thanks in advance.

Reagrds
Nikhilesh.
Tom Kyte
June 24, 2010 - 7:51 am UTC

... He don't
want to alter existing "working functionaly fine" code on production.
...

backup the bus.

What you are proposing to do is alter existing working functionaly fine code on production.

If not, you wouldn't be asking me this question. So, what gives? Are you going to

a) make a change to production
b) not make a change to production


because if it is (b) we don't need to discuss anything.


There would be no index rebuilding with partitions - why do you think there would be?

There would be no code change to existing applications - why do you think there would be?


I see massive shared pool invalidations with all of those renames/synonym repoints - much more than a simple exchange. Be prepared for a hard parse storm.

Also, public synonyms should just be avoided - it makes consolidation impossible - there can only be "one" public synonym. I don't like them for that reason.

Public synonyms can also be considered a security issue as it is easy to put a "trojan" in there by creating some object in the local schema that overrides the public synonym in scope.

Dynamic Synonyms

John Gilmore, October 05, 2010 - 4:33 am UTC

Hi Tom,

I want to set up an admin role so that I can dynamically assign the admin responsibility to and from specific users.

This means giving them permissions on some admin tables. So I can grant these permissions to a role called ap_admin and then grant ap_admin to users whenever I like.

The trouble is I only want users to see the admin tables when they've been granted the ap_admin role.

I don't want to use public synonyms as, apart from the reasons given above, I'd prefer if users are not able to "describe" the admin tables unless they have privileges on them.

I also don't want to create private synonyms each time I grant the admin role to a user.

How can this be done? Basically I need the functionality of a synonym owned by a role but that's not possible.
Tom Kyte
October 05, 2010 - 12:20 pm UTC

... I don't want to use public synonyms as, apart from the reasons given above, I'd
prefer if users are not able to "describe" the admin tables unless they have
privileges on them.
...

I agree you don't want public synonyms for many reasons - however, I don't see the tie in between public synonyms and "describe", if you don't have access to DBA_USERS, you cannot describe it - but yet there IS a public synonym dba-users. the synonym DOES NOT let you see the object in any way, shape or form.



You don't want synonyms - using schema names is NOT a bad idea. Just simply teach the people that have this role that the name of the table is:

ap_admin_schema.table_name


period, you can refer to things by their schema name - it is OK, acceptable, a good thing.

Hint to use Public Synonym

Snehasish Das, January 03, 2013 - 5:02 am UTC

Hi Tom,

Wish you a happy new year 2013.

I know the down side of using Public synonym (reading from the posts above). Now As a test case I did the below.
1. Grant select on Table T1 to public in schema test.
2. Create public synonym for test.t1;
3. Login to schema DEV.
4. Create table T1;

Now can I hint Oracle to use T1 the public synonym rather than the Table in schema DEV (I am logged on to DEV schema).

This is a test i am doing for my knowledge and its not used in any application.

Regards,
Snehasish Das
Tom Kyte
January 04, 2013 - 3:15 pm UTC

You cannot - it wouldn't even make sense. if you are going to "hint", you would just reference it directly??


The normal scoping rules take precedence here, as soon as DEV created T1, the public synonym is "invisible"

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library