10G clarification
Brad, January 01, 2006 - 8:35 pm UTC
You say that PL/SQL and views in 10G would not be invalidated when the synonym was redirected. Would the view pick up the new table?
January 02, 2006 - 9:52 am UTC
Yes, the view would "pick up the new table"
We will flip flop from T1 to T2 below:
ops$tkyte@ORA10GR2> create table t1 ( x int );
Table created.
ops$tkyte@ORA10GR2> insert into t1 values ( 1 );
1 row created.
ops$tkyte@ORA10GR2> create or replace synonym t for t1;
Synonym created.
ops$tkyte@ORA10GR2> create or replace procedure p
2 as
3 begin
4 for c in ( select * from t )
5 loop
6 dbms_output.put_line( c.x );
7 end loop;
8 end;
9 /
Procedure created.
ops$tkyte@ORA10GR2> create or replace view v as select * from t;
View created.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec p
1
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2> select * from v;
X
----------
1
ops$tkyte@ORA10GR2> select object_name, status from user_objects where object_name in ( 'P', 'V' );
OBJECT_NAME STATUS
------------------------------ -------
P VALID
V VALID
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create table t2 ( x int );
Table created.
ops$tkyte@ORA10GR2> insert into t2 values ( 2 );
1 row created.
ops$tkyte@ORA10GR2> create or replace synonym t for t2;
Synonym created.
ops$tkyte@ORA10GR2> select object_name, status from user_objects where object_name in ( 'P', 'V' );
OBJECT_NAME STATUS
------------------------------ -------
P VALID
V VALID
ops$tkyte@ORA10GR2> exec p
2
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2> select * from v;
X
----------
2
A reader, March 30, 2010 - 11:20 am UTC
Hi Tom,
I have a similar program for switching synonym from the schema A to B with the same table name (like the situation as Jim). However, the current practice is drop the public synonym, and then re-create the synonym for the switching. Is there any differences between these 2 approaches (i.e. using "create or replace public synonym" or drop and re-create the public synonym)? Which one you think is better?
Thanks,
David
April 05, 2010 - 10:52 am UTC
create or replace is better. Less invalidations will happen.
ops$tkyte%ORA10GR2> create table t1 as select * from all_users;
Table created.
ops$tkyte%ORA10GR2> create table t2 as select * from all_users;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create synonym s for t1;
Synonym created.
ops$tkyte%ORA10GR2> create or replace procedure p
2 as
3 begin
4 for x in (select * from s) loop null; end loop;
5 end;
6 /
Procedure created.
ops$tkyte%ORA10GR2> create or replace view v
2 as
3 select * from s;
View created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select object_name, status from user_objects where object_name in ('P','V');
OBJECT_NAME STATUS
------------------------------ -------
P VALID
V VALID
ops$tkyte%ORA10GR2> create or replace synonym s for t2;
Synonym created.
ops$tkyte%ORA10GR2> select object_name, status from user_objects where object_name in ('P','V');
OBJECT_NAME STATUS
------------------------------ -------
P VALID
V VALID
ops$tkyte%ORA10GR2> drop synonym s;
Synonym dropped.
ops$tkyte%ORA10GR2> create synonym s for t1;
Synonym created.
ops$tkyte%ORA10GR2> select object_name, status from user_objects where object_name in ('P','V');
OBJECT_NAME STATUS
------------------------------ -------
P INVALID
V INVALID
A reader, April 08, 2010 - 6:45 am UTC
Hi Tom,
I would like to know:
1) What is the disadvantage if the status is "INVALID"? I just know that the invalid objects will turn to "VALID" if being involved next time.
2) I thought one of the disadvantages of using "drop and re-create" is the object_id will be changed (of the same synonym name). However, I found that the object_id is remain unchanged (after the testing). Does this mean that Oracle remembers the previous object_id and restore the same object_id in the next "re-create"?
Thanks.
David
April 13, 2010 - 7:56 am UTC
1) you got it, It is just a flag that says "we have to recompile this thing before we run it again"
After an upgrade, many like to have everything valid - so they know everything can be valid. If you upgrade and have 1,000 invalid objects - you don't know if they all can be recompiled successfully or not yet.
2) why would that be a disadvantage? that the object id changes? why would you care?
the disadvantage to drop and create is that you lose all associated things - like grants on a view/procedure, dependencies on objects - code goes invalid and so on. That is the disadvantage to drop and create over create or replace - that it invalidates and can lose associated "things"
A reader, April 20, 2010 - 11:02 am UTC
Hi Tom,
I thought one of the disadvantages of "different ID" of the same object name is the object name cannot be determined in logminer correctly. As there is no schedule job to create the dictionary file for logminer, if the object ID changed, the object name cannot be determined when I use the logminer to view the redo log contents. Am I right?
Thanks
David
April 20, 2010 - 12:02 pm UTC
what can you create or replace that logminer would report on?
create or replace table - doesn't work
create or replace index - doesn't work
create or replace view - works but logminer doesn't show redo/undo for a view.
Synonym Table
Bharath Moola, March 26, 2021 - 4:53 pm UTC
Hi Tom,
I have similar situation where we use synonym concept to load a table. But when user runs a select statement using synonym, Session fails with object no longer exists and I believe this issue happening when SELECT and CREATE OR REPLACE synonym statement running simultaneously or coincided . Is there any way to avoid this as our ETL job runs create or replace every 2 min and we see this issue frequently in our reports where we use this synonym.
Thanks,
Bharath
March 30, 2021 - 12:29 pm UTC
Flipping the synonym every couple of minutes is extreme; instead of trying to resolve the errors you're getting, I think you're better off looking to see if there's a better way to load the data.