 |
|
Oracle Tips by Burleson |
Oracle 10g Synonyms and Views
Changes to synonyms often result in invalidation
of PL/SQL program units, which causes time-consuming re-compilation
of the program units.
Prior to Oracle Database 10g, when a synonym
was re-created, the status of any dependent PL/SQL program unit was
set to INVALID. If you did not recompile the PL/SQL program units
manually, they would recompile automatically the next time they were
invoked, causing runtime performance overhead.
Now, Oracle Database 10g introduces automatic
validation of PL/SQL program units, which determines whether program
units will have to be invalidated. This is only the case in the
following circumstances.
-
The column order, column names, and column
data types of the tables need to be identical.
-
The privileges on the newly referenced
table and its columns are a superset of the set of privileges on
the original table. These privileges must not be derived through
roles alone.
-
The names and types of partitions and
sub-partitions need to be identical.
-
The tables are of the same organization
type.
-
Object type columns are of the exact same
type.
In the case of views, just like PL/SQL program
units, even when the synonym definition is changed, the dependent
views will not be validated. However, this only happens when certain
conditions are met. All the conditions specified for PL/SQL units
are relevant here also.
In addition, the following conditions must be
met to keep the VALID status of the dependent views when you
redefine a synonym:
-
Columns and order of columns defined for
primary key and unique indexes, NOT NULL constraints, and
primary key and unique constraints must be identical.
-
The dependent view cannot have any
referential constraints.
In the next section, we will examine the
changes and improvements introduced for the LogMiner utility.
Get the complete Oracle10g story:
To get the code instantly, click here:
Need an Oracle Mentor?
BEI is now offering personal mentors for Oracle DBAs where you can have an
Oracle expert right at your fingertips, anytime day or night. We work with
hundreds of Oracle databases every year, so we know exactly how to quickly
assist you with any Oracle question.
Why risk an unplanned outage? You can now get telephone access to Don
Burleson or any of his Oracle Certified DBAs with more than 20 years of
full-time IT experience. Click here for details:
http://www.dba-oracle.com/service_oracle_backup.htm
|
