Re: How to read table using Synonym

On 1 Sep 2005 15:15:20 -0700, sunny076_at_yahoo.com wrote:

>Thank you for your reply. I want to get the metadata from the synonym.
>How can I do so? I was using the SQL syntax below to get Column name
>from Oracle Table.
>"select column_name, data_type, data_precision, data_scale
> from all_tab_columns"

 ALL_SYNONYMS will tell you the owner and table name to which the synonym points. Using that, you can then query ALL_TAB_COLUMNS, matching against the owner and table_name that you now know is the real table. e.g.

SQL> SELECT tc.column_name, tc.data_type, tc.data_precision, tc.data_scale   2 FROM all_synonyms s
  3 JOIN all_tab_columns tc
  4 ON (tc.owner = s.table_owner   5 AND tc.table_name = s.table_name)   6 WHERE s.owner = 'PUBLIC'
  7 AND s.synonym_name = 'PLAN_TABLE';

COLUMN_NAME                    DATA_TYPE            DATA_PRECISION DATA_SCALE
------------------------------ -------------------- -------------- ----------
STATEMENT_ID                   VARCHAR2
PLAN_ID                        NUMBER
TIMESTAMP                      DATE
REMARKS                        VARCHAR2
OPERATION                      VARCHAR2
OPTIONS                        VARCHAR2
OBJECT_NODE                    VARCHAR2
OBJECT_OWNER                   VARCHAR2
OBJECT_NAME                    VARCHAR2
OBJECT_ALIAS                   VARCHAR2
OBJECT_INSTANCE                NUMBER                                       0
OBJECT_TYPE                    VARCHAR2
OPTIMIZER                      VARCHAR2
SEARCH_COLUMNS                 NUMBER
ID                             NUMBER                                       0
PARENT_ID                      NUMBER                                       0
DEPTH                          NUMBER                                       0
POSITION                       NUMBER                                       0
COST                           NUMBER                                       0
CARDINALITY                    NUMBER                                       0
BYTES                          NUMBER                                       0
OTHER_TAG                      VARCHAR2
PARTITION_START                VARCHAR2
PARTITION_STOP                 VARCHAR2
PARTITION_ID                   NUMBER                                       0
OTHER                          LONG
OTHER_XML                      CLOB
DISTRIBUTION                   VARCHAR2
CPU_COST                       NUMBER                                       0
IO_COST                        NUMBER                                       0
TEMP_SPACE                     NUMBER                                       0
ACCESS_PREDICATES              VARCHAR2
FILTER_PREDICATES              VARCHAR2
PROJECTION                     VARCHAR2
TIME                           NUMBER                                       0
QBLOCK_NAME                    VARCHAR2

36 rows selected.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool