How Does Oracle locate objects through synonyms?

Preparations:

How to grant the View query permission to other users:

 

Sys @ ORCL> grant select on v $ paramter toscott;

Grant select on v $ paramter to scott

*

Row 3 has an error:

ORA-00942: Table or view does not exist

 

 

Sys @ ORCL> grant select on v $ parameter toscott

Grant select on v $ parameter to scott

*

Row 3 has an error:

ORA-02030: only queries from a fixed table/View

This is because v $ paramter is not a view and is actually a synonym.

 

Sys @ ORCL> SELECT SYNONYM_NAME, TABLE_NAMEFROM DBA_SYNONYMS WHERE SYNONYM_NAME = 'v $ PARAMETER ';

 

SYNONYM_NAME TABLE_NAME

------------------------------------------------------------

V $ parameter v _ $ PARAMETER

 

Therefore, you must authorize the view corresponding to the synonym.

Sys @ ORCL> grant select on v _ $ parameter toscott;

 

Authorization successful.

 

Sys @ ORCL> conn scott/tiger

Connected.

Scott @ ORCL> select * from v $ parameterwhere rownum <5;

 

 

How to locate objects through synonyms in Oracle:

 

Sys @ ORCL> alter session set events '10046trace name context forever, level 12 ';

 

The session has been changed.

 

Sys @ ORCL> select * from v $ diag_info wherename = 'default Trace file ';

 

INST_ID NAME VALUE

--------------------------------------------------------------------------------

1 Default Trace File d: \ app \ lenovo \ diag \ rdbms \ orcl \ trace

\ Orcl_ora_000068.trc

 

The important code in the first section is as follows:
Parsing in cursor #3 len = 202 dep = 1 uid = 0 oct = 3 lid = 0 tim = 1683213108hv = 3819099649 ad = '7ffc8919a70 'sqlid = '3nkd3163ju5ph1'

Select obj #, type #, ctime, mtime, stime, status, dataobj #, flags, oid $, spare1, spare2 from obj $ where owner #=: 1 andname =: 2 and namespace =: 3 and remoteowner is null and linkname is null andsubname is null

END OF STMT

PARSE #3: c = 0, e = 976, p = 0, cr = 0, cu = 0, mis = 1, r = 0, dep = 1, og = 4, plh = 0, tim = 1683213422

BINDS #3:

Bind #0

Oacdty = 02 mxl = 22 (22) mxlc = 00 mal = 00 scl = 00 pre = 00

Oacflg = 08 fl2 = 0001 frm = 00 csi = 00 siz = 24 off = 0

Kxsbbbfp = 1cc02bf8 bln = 22 avl = 02 flg = 05

Value = 84

Bind #1

Oacdty = 01 mxl = 32 (11) mxlc = 00 mal = 00 scl = 00 pre = 00

Oacflg = 18 fl2 = 0001 frm = 01 csi = 852 siz = 32 off = 0

Kxsbbbfp = 1cc02bc0 bln = 32 avl = 11 flg = 05

Value = "V $ PARAMETER"

Bind #2

Oacdty = 02 mxl = 22 (22) mxlc = 00 mal = 00 scl = 00 pre = 00

Oacflg = 08 fl2 = 0001 frm = 00 csi = 00 siz = 24 off = 0

Kxsbbbfp = 1cc02b90 bln = 24 avl = 02 flg = 05

Value = 1

 

Oracle determines the object type based on the input PARAMETER owner # = 84 name = V $ PARAMETER namespace = 1. oracle determines the object type based on the table and view priority rules, in this example, the table or view does not exist under scott.

 

Next, oracle continues to judge. At this time, you need to verify the synonym:
Parsing in cursor #3 len = 46 dep = 1 uid = 0 oct = 3 lid = 0 tim = 1683217830hv = 1343089354 ad = '7ffc84fa2d8 'sqlid = '1mjd9xp80vuqa'

Selectnode, owner, name from syn $ where obj # =: 1

ENDOF STMT

PARSE #3: c = 15600, e = 501, p = 0, cr = 0, cu = 0, mis = 1, r = 0, dep = 1, og = 4, plh = 0, tim = 1683217829

BINDS #3:

Bind #0

Oacdty = 02 mxl = 22 (22) mxlc = 00 mal = 00 scl = 00pre = 00

Oacflg = 08 fl2 = 0001 frm = 00 csi = 00 siz = 24 off = 0

Kxsbbbfp = 1eda-c6 bln = 22 avl = 03 flg = 05

Value = 1456

 

 

The input Bind Variable value is 1456:

For more details, please continue to read the highlights on the next page:

Create Oracle synonyms and their functions

Use datapump to export the import synonym (export and import synonym using datapump)

Create dblink and synonym in Oracle

Create and delete Oracle Synonyms

Oracle synonym learning

Common Oracle database objects-indexes and synonyms

1 2 Next Page