| |
 |
|
Oracle Tips by Burleson |
Monitoring Synonyms Using
the V$ and DBA_ Views
Synonyms remain the same in Oracle8, Oracle8i, and Oracle9i. The
major changes that have affected synonyms are those to the structure
of the connection strings from SQL*NET V1 to V2 to NET8. Despite the
fact that synonyms are the key to providing cross-database access
for queries, and a means of implementing distributed data across
nodes, systems, and databases, in all the reports reviewed for this
book, not one seemed to cover synonyms. Recall that a synonym allows
a shorthand version of an object name to be specified. The parts of
a synonym are the object name (which usually includes an owner) and,
possibly, a database link that will also provide an Oracle user name
and password to a remote system. A complete report will show all of
these items.
Why is it important to monitor synonyms? Synonyms can be used to
access data, sometimes data that shouldn’t be accessed if object
grants have been too widely granted. In addition, they are the means
for reaching other nodes and databases. If a connect string becomes
invalid, a user name is disconnected or its password changes or node
name changes, it is good to be able to see which object synonyms
will be affected. Source 11.20 shows a script for a synonym report,
and Listing 11.17 shows an example of output from a synonym script.
SOURCE 11.20 Script for synonym report.
REM
REM NAME : SYNONYM.SQL
REM PURPOSE : GENERATE REPORT OF A USERS SYNONYMS
REM USE : FROM SQLPLUS
REM Limitations : None
REM Revisions:
REM Date Modified by Reason for change
REM 12/MAY/93 Mike Ault Initial Creation
REM 15/Jun/97 Mike Ault Verified for Oracle8
REM 16/May/99 Mike Ault Verified for Oracle8i
REM
PROMPT Percent signs are Wild Cards
PROMPT
ACCEPT own PROMPT 'Enter the user who owns synonym: '
SET PAGES 56 LINES 130 VERIFY OFF FEEDBACK OFF TERM OFF
START title132 "Synonym Report"
SPOOL rep_out/&&db/synonym
COLUMN host FORMAT a24 HEADING "Connect String"
COLUMN owner FORMAT a15
COLUMN table FORMAT a35
COLUMN db_link FORMAT a6 HEADING Link
COLUMN username FORMAT a15
SELECT
a.owner, synonym_name ,
table_owner ||'.'|| table_name "Table" ,
b.db_link,username,host
FROM
dba_synonyms a,
dba_db_links b
See code depot
This is an excerpt by Mike Ault’s book “Oracle9i
Administration & Management” . If you want more current Oracle
tips by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s
Oracle Scripts Download. |
