| Synonyms and the DBA_SYNONYMS view [message #536618] |
Wed, 21 December 2011 18:20  |
 |
brieck
Messages: 5
Registered: December 2011
|
Junior Member |
|
|
Quoting the Oracle 11.1 Database Concepts doc ... "A synonym is an alias for any table, view, materialized view, sequence, procedure, function, package, type, Java class schema object, user-defined object type, or another synonym."
The same document defines the DBA_SYNONYM view as "DBA_SYNONYMS describes all synonyms in the database. Its columns are the same as those in ALL_SYNONYMS."
Describing the DBA_SYNONYMS view I see:
VIEW dba_synonyms
Name Null? Type
---------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
SYNONYM_NAME NOT NULL VARCHAR2(30)
TABLE_OWNER VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
DB_LINK VARCHAR2(128)
I'm confused. Why does this view associate synonyms with a table owner and name? As the Oracle doc stated synonyms can exist for many different objects other than tables and further that this view describes all synonyms. How would you answer the question "Show me all the synonyms and their object type for a particular owner?"
It doesn't seem possible using DBA_SYNONYMS. Am I right and Oracle is wrong?  Or am I missing something? 
[Updated on: Wed, 21 December 2011 18:26] Report message to a moderator |
|
|
|
| Re: Synonyms and the DBA_SYNONYMS view [message #536619 is a reply to message #536618] |
Wed, 21 December 2011 18:31  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
many objects other than TABLE exist within DBA_SYNONYMS
1 SELECT object_type, count(*)
2 from dba_objects
3 WHERE (owner, object_name) in (select table_owner,
4 table_name
5 FROM dba_synonyms
6 MINUS
7 SELECT owner,
8 table_name
9 FROM dba_tables)
10* group by object_type
SQL> /
OBJECT_TYPE COUNT(*)
------------------- ----------
SEQUENCE 4
OPERATOR 47
PROCEDURE 21
PACKAGE 489
PACKAGE BODY 471
TYPE BODY 68
VIEW 3538
FUNCTION 205
JAVA CLASS 22846
INDEXTYPE 1
SYNONYM 34
OBJECT_TYPE COUNT(*)
------------------- ----------
TYPE 353
12 rows selected.
SQL>
[Updated on: Wed, 21 December 2011 18:41] Report message to a moderator |
|
|
|
| Re: Synonyms and the DBA_SYNONYMS view [message #536620 is a reply to message #536618] |
Wed, 21 December 2011 19:09 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
brieck wrote on Thu, 22 December 2011 01:20It doesn't seem possible using DBA_SYNONYMS. Am I right and Oracle is wrong? Or am I missing something?
Yes, you are missing the last step - reading the description of columns in ALL_SYNONYMS system view. For 11gR1, it is stated here: http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_2088.htm#i1590417
TABLE_OWNER
VARCHAR2(30)
... Although the column is called TABLE_OWNER, the object owned is not necessarily a table. It can be any general object such as a view, sequence, stored procedure, synonym, and so on.
TABLE_NAME
VARCHAR2(30)
Name of the object referenced by the synonym. Although the column is called TABLE_NAME, the object does not necessarily have to be a table. It can be any general object such as a view, sequence, stored procedure, synonym, and so on.
It might have contained only tables in some ancient Oracle release, but take into account the necessity of backward compatibility - the column name did not change although its content was enhanced.
Of course you may check it yourself as BlackSwan demonstrated.
|
|
|
|
|
|
|
|
| Re: Synonyms and the DBA_SYNONYMS view [message #536623 is a reply to message #536620] |
Wed, 21 December 2011 19:37 |
|
brieck
Messages: 5
Registered: December 2011
|
Junior Member |
|
|
|
Thanks for the further explanation with the column descriptions. I'll have to be more careful and not read to much into a column name however obvious it may seem. You're probably about the ancient history. I see no other possible reason for it.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
