Home » RDBMS Server » Server Administration » Oracle sysnoyms internal (HPUX: Version 11.2.0.2.0 )
Oracle sysnoyms internal [message #522070] Tue, 06 September 2011 06:06 Go to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Hello Expert,

Does Oracle synonyms store table object_id or it store row_id when created..?? I request if you please explain internal of Oracle synonyms.

Thanks

Jay vardhan
Re: Oracle sysnoyms internal [message #522073 is a reply to message #522070] Tue, 06 September 2011 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rowid? which rowid?

It creates a row in SYS.OBJ$ and a row in SYS.SYN$ for the synonym with the owner, name and node of the target object.

Regards
Michel
Re: Oracle sysnoyms internal [message #522074 is a reply to message #522073] Tue, 06 September 2011 06:20 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

I was wondering to know the reason why synonym stop working when we rebuild the table or move the table to other tablespace.

Kindly explain.

Thanks

Regards,
Jay vardhan
Re: Oracle sysnoyms internal [message #522075 is a reply to message #522074] Tue, 06 September 2011 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no reason.
Please show what you saw.

Regards
Michel
Re: Oracle sysnoyms internal [message #522081 is a reply to message #522075] Tue, 06 September 2011 06:51 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

SQL> create table jay ( a number);

Table created.

SQL>
SQL>
SQL> create synonym jay_sys for jay;

Synonym created.

SQL> insert into jay values (1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from jay_sys;

         A
----------
         1
         1
         1
         1

SQL>
SQL> select object_id,owner from dba_objects where object_name='JAY';

 OBJECT_ID OWNER
---------- ------------------------------
    260252 DBCHECK

SQL> alter table jay rename to jay_old ;

Table altered.

SQL> select object_id,owner from dba_objects where object_name='JAY';

no rows selected

SQL> select object_id,owner from dba_objects where object_name='JAY_OLD';

 OBJECT_ID OWNER
---------- ------------------------------
    260252 DBCHECK

SQL> select * from jay_sys;
select * from jay_sys
              *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid


SQL> alter table jay_old rename to jay;

Table altered.

SQL> select object_id,owner from dba_objects where object_name='JAY';

 OBJECT_ID OWNER
---------- ------------------------------
    260252 DBCHECK

SQL> select * from jay_sys;

         A
----------
         1
         1
         1
         1
SQL> alter table jay move tablespace DDP_TAB;

Table altered.

SQL> select object_id,owner from dba_objects where object_name='JAY';

 OBJECT_ID OWNER
---------- ------------------------------
    260252 DBCHECK

SQL> select * from jay_sys;

         A
----------
         1
         1
         1
         1



We can see that renaming of table won't change the object_id, still why synonym stop working.? and when we revert the name, synonym start working..!! So thus the concept is that synonyms point to only table name..??
Re: Oracle sysnoyms internal [message #522087 is a reply to message #522081] Tue, 06 September 2011 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You said: "stop working when we rebuild the table or move the table to other tablespace." and now you rename, this is not the same thing.

2/ I said: "It creates a row [...] in SYS.SYN$ for the synonym with the owner, name and node of the target object.

Is this not clear why the synonym no more works when you rename?

Regards
Michel

[Updated on: Tue, 06 September 2011 07:11]

Report message to a moderator

Re: Oracle sysnoyms internal [message #522089 is a reply to message #522087] Tue, 06 September 2011 07:16 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Actually, i have read in one website that synonym also store table object id to which it is pointing to. But now cleared that synonyms only store the pointing object name, nothing else.

Please correct if i'm wrong.

Thanks

Jay vardhan
Re: Oracle sysnoyms internal [message #522090 is a reply to message #522089] Tue, 06 September 2011 07:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Actually, i have read in one website that synonym also store table object id to which it is pointing to.

This is wrong.

Quote:
But now cleared that synonyms only store the pointing object name, nothing else.

SQL> desc sys.syn$
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 OBJ#                             NOT NULL NUMBER
 NODE                                      VARCHAR2(128)
 OWNER                                     VARCHAR2(30)
 NAME                             NOT NULL VARCHAR2(30)


Regards
Michel
Re: Oracle sysnoyms internal [message #522093 is a reply to message #522090] Tue, 06 September 2011 07:33 Go to previous message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Thanks Michel for explaining.

Thanks.

Regards
Jay vardhan

Previous Topic: "Oracle tablespace management", "extent management" and "segment space mana
Next Topic: dba problem
Goto Forum:
  


Current Time: Mon Apr 29 16:38:01 CDT 2024