I had a problem earlier this week with a package which kept going INVALID. Once I had worked out the reason, I decided to do a worked example of it on Oracle 11.2.0.2.7. I created users called SOMEBODY, JLS and CBS. JLS has nothing to do with the pop group of the same name and CBS has nothing to do with the news network. You will notice that they have some unusual permissions. This is to save me having to connect as SYS later in the example. It has nothing to do with the problem:
conn / as sysdba
Connected.
SQL> create user somebody
2 identified by somebody
3 default tablespace users
4 quota unlimited on users
5 /
User created.
SQL> grant create session, create table to somebody
2 /
Grant succeeded.
SQL> create user jls
2 identified by jls
3 default tablespace users
4 quota unlimited on users
5 /
User created.
SQL> grant
2 create session,
3 create procedure,
4 update any table,
5 select any table,
6 select any dictionary to jls
7 /
Grant succeeded.
SQL> create user cbs
2 identified by cbs
3 default tablespace users
4 quota unlimited on users
5 /
User created.
SQL> grant
2 create session,
3 create table,
4 select any dictionary,
5 create public synonym to cbs
6 /
Grant succeeded.
SQL>
SOMEBODY created a table called SITE:
SQL> conn somebody/somebody
Connected.
SQL> create table site (col1 varchar2(15))
2 /
Table created.
SQL>
JLS created a procedure to update SOMEBODY's SITE table using its fully qualified name:
SQL> conn jls/jls
Connected.
SQL> create or replace procedure update_site as
2 begin
3 update somebody.site set col1 = '10 High Street';
4 end;
5 /
Procedure created.
SQL>
DBA_DEPENDENCIES showed that JLS's UPDATE_SITE procedure referenced SOMEBODY's SITE table:
SQL> select referenced_owner, referenced_name
2 from dba_dependencies
3 where owner = 'JLS'
4 and name = 'UPDATE_SITE'
5 /
REFERENCED_OWNER REFERENCED_NAME
-------------------- ------------------------------
SYS SYS_STUB_FOR_PURITY_ANALYSIS
SOMEBODY SITE
SQL>
CBS created its own SITE table but this did not affect the validity of JLS's UPDATE_SITE procedure:
SQL> conn cbs/cbs
Connected.
SQL> create table site (col2 number)
2 /
Table created.
SQL> select owner, object_name, object_type
2 from dba_objects
3 where status = 'INVALID'
4 /
no rows selected
SQL> drop table site
2 /
Table dropped.
SQL>
A public synonym called SITE was created for SOMEBODY's SITE table:
SQL> create or replace public synonym site for somebody.site
2 /
Synonym created.
SQL>
CBS recreated its SITE table. JLS's UPDATE_SITE procedure remained VALID:
SQL> create table site (col2 number)
2 /
Table created.
SQL> select owner, object_name, object_type
2 from dba_objects
3 where status = 'INVALID'
4 /
no rows selected
SQL> drop table site
2 /
Table dropped.
SQL>
JLS changed its UPDATE_SITE procedure to access SOMEBODY's SITE table via the public synonym:
SQL> conn jls/jls
Connected.
SQL> create or replace procedure update_site as
2 begin
3 update site set col1 = '10 High Street';
4 end;
5 /
Procedure created.
SQL>
DBA_DEPENDENCIES showed that JLS's UPDATE_SITE procedure referenced a public synonym called SITE:
SQL> select referenced_owner, referenced_name
2 from dba_dependencies
3 where owner = 'JLS'
4 and name = 'UPDATE_SITE'
5 /
REFERENCED_OWNER REFERENCED_NAME
-------------------- ------------------------------
SYS SYS_STUB_FOR_PURITY_ANALYSIS
PUBLIC SITE
SQL>
CBS checked that there were no INVALID objects in the database and recreated its SITE table. This made JLS's UPDATE_SITE procedure INVALID.
SQL> conn cbs/cbs
Connected.
SQL> select owner, object_name, object_type
2 from dba_objects
3 where status = 'INVALID'
4 /
no rows selected
SQL> create table site (col2 number)
2 /
Table created.
SQL> select owner, object_name, object_type
2 from dba_objects
3 where status = 'INVALID'
4 /
OWNER OBJECT_NAME OBJECT_TYPE
---------- --------------- ---------------
JLS UPDATE_SITE PROCEDURE
SQL>
The procedure then had to be recompiled:
SQL> conn jls/jls
Connected.
SQL> alter procedure update_site compile
2 /
Procedure altered.
SQL>
So, there you have it. If a compiled procedure or package accesses a table via a public synonym, then another user creates a table with the same name as the public synonym, the compiled procedure or package will be invalidated.
Incidentally, when this happened for real, a package with over 3000 lines of code became INVALID.
When a UNIX script then tried to run it, Oracle did not attempt to
recompile the package automatically, it just failed with a tnsnames
related error, possibly because the package accessed data over a
database link. It was later recompiled without errors and the UNIX
script ran successfully.
I
have already looked at a very simple example relating to automatic
procedure recompilation. I will be looking at it in more detail in the
near future as it is clearly not as straightforward as I thought at
first.
