In a previous post I described a situation where the creation of an extension, independently of whether it’s carried out manually by a person or automatically by the database engine, can lead to the invalidation of PL/SQL objects. In this second post on that subject, I describe, with the help of an example (I love examples!) based on the extension_invalidate_pkg_remote.sql script, what can happen when the table on which the extension is created is stored in a remote database.
Let’s start by creating the required objects. Notice that all objects are local but, because the package body references the table through a database link that points to the very same schema owning all other objects, the database engine considers it a remote table.
SQL> CREATE DATABASE LINK loopback
2 CONNECT TO chris IDENTIFIED BY ian
3 USING 'DBB121.local';
SQL> CREATE TABLE t (id, n1, n2, pad)
2 AS
3 SELECT rownum, mod(rownum,113), mod(rownum,113), lpad('*',100,'*')
4 FROM dual
5 CONNECT BY level <= 10000;
SQL> CREATE OR REPLACE PACKAGE p AS
2 PROCEDURE p;
3 END p;
4 /
SQL> CREATE OR REPLACE PACKAGE BODY p AS
2 PROCEDURE p IS
3 c NUMBER;
4 BEGIN
5 SELECT count(*) INTO c
6 FROM t@loopback;
7 END p;
8 END p;
9 / |
The dependencies of the package body P clearly shows that the table T is referenced through a database link:
SQL> SELECT referenced_owner, referenced_name, referenced_type, referenced_link_name 2 FROM user_dependencies 3 WHERE name = 'P' 4 AND type = 'PACKAGE BODY'; REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE REFERENCED_LINK_NAME ---------------- --------------- --------------- ------------------------------ SYS STANDARD PACKAGE CHRIS P PACKAGE CHRIS T TABLE LOOPBACK.LOCAL |
It’s essential to know that to track that dependency the database engine stores, in the local data dictionary, a so called stub. Interestingly, even though it’s present in OBJ$, it’s not visible through data dictionary views like DBA_OBJECTS. Therefore, to see it you have to directly select OBJ$:
SQL> SELECT o.remoteowner AS referenced_owner, 2 o.name AS referenced_name, 3 o.linkname AS referenced_link_name, 4 to_char(o.stime, 'YYYY-MM-DD HH24:MI:SS') AS specification_timestamp 5 FROM sys.obj$ o, 6 (SELECT p_obj# 7 FROM sys.dependency$ 8 WHERE d_obj# = (SELECT object_id 9 FROM user_objects 10 WHERE object_name = 'P' 11 AND object_type = 'PACKAGE BODY')) d 12 WHERE o.obj# = d.p_obj# 13 AND o.name = 'T'; REFERENCED_OWNER REFERENCED_NAME REFERENCED_LINK_NAME SPECIFICATION_TIMESTAMP ---------------- --------------- ------------------------------ ----------------------- CHRIS T LOOPBACK.LOCAL 2016-02-06 01:33:21 |
Now that the necessary objects are available, let’s see what happens if an extension is added to the (remote) table referenced by the package body P:
SQL> DECLARE 2 l_extension VARCHAR2(30); 3 BEGIN 4 l_extension := dbms_stats.create_extended_stats@loopback(user, 'T', '(n1,n2)'); 5 END; 6 / SQL> SELECT column_name 2 FROM user_tab_cols@loopback 3 WHERE table_name = 'T' 4 AND hidden_column = 'YES'; COLUMN_NAME ------------------------------ SYS_STUBZH0IHA7K$KEBJVXO5LOHAS SQL> SELECT object_name, object_type, status 2 FROM user_objects 3 WHERE object_name = 'P' 4 AND status = 'INVALID'; no rows selected |
Obviously, no invalidation took place. But, wait, this is expected… or not?
Since the database link is unidirectional, the remote database can’t inform the local one that something has changed. As a result, whatever the change on the remote database is, no immediate invalidation of the package takes place.
So, let’s do something on the local database that uses the same remote table and, as a result, requires the same (local) stub. For that purpose, let’s execute an anonymous PL/SQL block:
SQL> DECLARE 2 c NUMBER; 3 BEGIN 4 SELECT count(*) INTO c 5 FROM t@loopback; 6 END p; 7 / |
What is the status of the body of the package P after the execution of the anonymous PL/SQL block?
SQL> SELECT object_name, object_type, status 2 FROM user_objects 3 WHERE object_name = 'P' 4 AND status = 'INVALID'; OBJECT_NAME OBJECT_TYPE STATUS --------------- --------------- ------- P PACKAGE BODY INVALID |
It is invalid. Why? Because during the compilation of the PL/SQL block the (local) database engine noticed that the (remote) table T has changed and, therefore, has updated the specification timestamp of the stub. Therefore, all objects that depends on that stub needed to be invalidated. There is no fine-grained invalidation for remote references!
SQL> SELECT o.remoteowner AS referenced_owner, 2 o.name AS referenced_name, 3 o.linkname AS referenced_link_name, 4 to_char(o.stime, 'YYYY-MM-DD HH24:MI:SS') AS specification_timestamp 5 FROM sys.obj$ o, 6 (SELECT p_obj# 7 FROM sys.dependency$ 8 WHERE d_obj# = (SELECT object_id 9 FROM user_objects 10 WHERE object_name = 'P' 11 AND object_type = 'PACKAGE BODY')) d 12 WHERE o.obj# = d.p_obj# 13 AND o.name = 'T'; REFERENCED_OWNER REFERENCED_NAME REFERENCED_LINK_NAME SPECIFICATION_TIMESTAMP ---------------- --------------- ------------------------------ ----------------------- CHRIS T LOOPBACK.LOCAL 2016-02-06 01:34:06 |
In summary, if you have PL/SQL objects that references a remote table, the creation of an extension on the remote table, independently of whether it’s carried out manually by a person or automatically by the database engine, can lead to the invalidation of the PL/SQL object that references it.
A final thought… Static references to remote objects are, in general, bad news. There are a number of reasons to avoid them. Covering those reasons and the techniques that can be used to do so is out-of-scope for this post. But that subject is actually one that I might cover in a future post.
No Comments Yet