On Oracle: Oracle Package Body Invalid without DBA

This Blog demonstrates the case in which Oracle Package Spec/Body Invalid, but no entry stored in DBA_ERRORS.
We reveal Oracle Internals with SQL Trace, and shows approach to detect such Invalid.

Note: Tested in Oracle 19c

1. Test Code

At first, we create one table and one Plsql package using it.


drop table test_tab;

create table test_tab(x1 number(2));

insert into test_tab (x1) values (1);

commit;

select * from test_tab;

drop package test_pkg;

create or replace package test_pkg as
  --b_ret test_tab.x1%type;  -- this makes both Package Spec and Body INVALID
  function func1 (p_x number) return number;
end;
/

create or replace package body test_pkg as
  function func1 (p_x number) return number as
    l_ret number;
  begin
    -- comment out this query to make Package Body not depend on any table
    select x1 into l_ret from test_tab where x1 = p_x;   
    return l_ret;
  end;
end;  
/

2. Modify Table to trigger Package Body Invalid

We query dba_dependencies to list the dependencies:


select name, type, referenced_name, referenced_type, dependency_type from dba_dependencies where name = 'TEST_PKG';

NAME       TYPE            REFERENCED REFERENCED DEPENDENCY
---------- --------------- ---------- ---------- ----------
TEST_PKG   PACKAGE         STANDARD   PACKAGE    HARD
TEST_PKG   PACKAGE BODY    STANDARD   PACKAGE    HARD
TEST_PKG   PACKAGE BODY    TEST_TAB   TABLE      HARD
TEST_PKG   PACKAGE BODY    TEST_PKG   PACKAGE    HARD

Modify test_tab to invalidate package body:


alter table test_tab modify x1 number(9);

--  alter table test_tab add x2 number(9);
--  alter table test_tab drop column x2;

ALTER SESSION SET NLS_DATE_FORMAT         ='YYYY-MON-DD HH24:MI:SS';    
ALTER SESSION SET NLS_TIMESTAMP_FORMAT    ='YYYY-MON-DD HH24:MI:SS';

select object_name, object_id, data_object_id, created, last_ddl_time, timestamp, status 
  from dba_objects where object_name in ( 'TEST_PKG', 'TEST_TAB');
  
  OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID CREATED              LAST_DDL_TIME        TIMESTAMP           STATUS
  ---------- ---------- -------------- -------------------- -------------------- ------------------- ----------
  TEST_TAB      6256609        6256609 2026-AUG-25 13:41:38 2026-AUG-25 13:46:02 2026-08-25:13:46:02 VALID
  TEST_PKG      6256610                2026-AUG-25 13:43:24 2026-AUG-25 13:43:24 2026-08-25:13:43:24 VALID
  TEST_PKG      6256611                2026-AUG-25 13:43:24 2026-AUG-25 13:43:24 2026-08-25:13:43:24 INVALID

-- dba_objects.status= decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID')
select obj#, name, type#, ctime, mtime, stime, status from sys.obj$ o where name = 'TEST_PKG';  

     OBJ# NAME            TYPE# CTIME                MTIME                STIME                STATUS
  ------- ---------- ---------- -------------------- -------------------- -------------------- ------
  6256610 TEST_PKG            9 2026-AUG-25 13:43:24 2026-AUG-25 13:43:24 2026-AUG-25 13:43:24      1
  6256611 TEST_PKG           11 2026-AUG-25 13:43:24 2026-AUG-25 13:43:24 2026-AUG-25 13:43:24      5

When we query dba_errors, no entries found:


select * from dba_errors where name = 'TEST_PKG';

  no rows selected

select * from sys.error$ e, dba_objects o 
 where e.obj# = o.object_id and object_name in ( 'TEST_PKG', 'TEST_TAB');

  no rows selected

If we invoke package program, Package Body becomes VALID:


declare
  l_ret number;
begin
  l_ret := test_pkg.func1(1);
  dbms_output.put_line('l_ret = '||l_ret);
end;
/

  l_ret = 1

select object_name, object_id, data_object_id, created, last_ddl_time, timestamp, status 
  from dba_objects where object_name in ( 'TEST_PKG', 'TEST_TAB');

  OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID CREATED              LAST_DDL_TIME        TIMESTAMP           STATUS
  ---------- ---------- -------------- -------------------- -------------------- ------------------- -------
  TEST_TAB      6256609        6256609 2026-AUG-25 13:41:38 2026-AUG-25 14:11:44 2026-08-25:14:11:44 VALID
  TEST_PKG      6256610                2026-AUG-25 13:43:24 2026-AUG-25 13:43:24 2026-08-25:13:43:24 VALID
  TEST_PKG      6256611                2026-AUG-25 13:43:24 2026-AUG-25 14:12:08 2026-08-25:14:12:08 VALID

3. SQL Trace to Reveal Dependency and Invalid

We modify the table again with SQL Trace level 12 (contains bind variables):


alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier = 'trc_1';
alter session set events '10046 trace name context forever, level 12';  

alter table test_tab add x1 number(11);   

alter session set events '10046 trace name context off';

TEST_PKG Body is INVALID:


select object_name, object_id, data_object_id, created, last_ddl_time, timestamp, status 
  from dba_objects where object_name in ( 'TEST_PKG', 'TEST_TAB');

  OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID CREATED              LAST_DDL_TIME        TIMESTAMP           STATUS
  ---------- ---------- -------------- -------------------- -------------------- ------------------- -------
  TEST_TAB      6256609        6256609 2026-AUG-25 13:41:38 2026-AUG-25 14:16:41 2026-08-25:14:16:41 VALID
  TEST_PKG      6256610                2026-AUG-25 13:43:24 2026-AUG-25 13:43:24 2026-08-25:13:43:24 VALID
  TEST_PKG      6256611                2026-AUG-25 13:43:24 2026-AUG-25 14:12:08 2026-08-25:14:12:08 INVALID  

-- dba_objects.status= decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID')
--   set test_pkg package body sys.OBJ$.status = 5, i.e, dba_objects.status = 'INVALID'
--   no any modification of sys.ERROR$ for test_pkg package body

Open trc file, we can see that TIMESTAMP (Bind#4 stime) of TEST_TAB is updated as value="8/25/2026 14:16:41":


=== Update Table: TEST_TAB (OBJECT_ID = 6256609), set mtime(Bind#4)="8/25/2026 14:16:41", set stime(Bind#4)="8/25/2026 14:16:41", status(Bind#5)=1 (VALID)

PARSING IN CURSOR #139943683342520 len=409 dep=1 uid=0 oct=6 lid=0 tim=843833534857 hv=696165170 ad='9ecdf3e8' sqlid='c3utnxsnrx8tk'
update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,
  dataobj#=:10,flags=:11,oid$=:12,spare1=:13,spare2=:14,spare3=:15,signature=
  :16,spare7=:17,spare8=:18,spare9=:19, dflcollid=decode(:20,0,null,:20),
  creappid=:21,creverid=:22, modappid=:23,modverid=:24,crepatchid=:25,
  modpatchid=:26 
where
 owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname 
  is null and subname is null

BINDS #139943683342520:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=7f472d7acfe8  bln=22  avl=05  flg=05
  value=6256609
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7f472d7ad000  bln=22  avl=02  flg=01
  value=2
 Bind#2
  oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
  kxsbbbfp=989ee31d  bln=07  avl=07  flg=09
  value="8/25/2026 13:41:38"
 Bind#3
  oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
  kxsbbbfp=989ee324  bln=07  avl=07  flg=09
  value="8/25/2026 14:16:41"
 Bind#4
  oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
  kxsbbbfp=989ee32b  bln=07  avl=07  flg=09
  value="8/25/2026 14:16:41"
 Bind#5
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=72 off=0
  kxsbbbfp=7f472d7acf88  bln=22  avl=02  flg=05
  value=1

  ......
  
 Bind#25
  oacdty=01 mxl=32(08) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=989ee106  bln=32  avl=08  flg=09
  value="TEST_TAB"
 Bind#26
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f472d7ace68  bln=22  avl=02  flg=05
  value=1

For Package Body TEST_PKG (OBJECT_ID = 6256611), its status(Bind#5) is updated as value=5 (INVALID):


=== update package body: TEST_PKG (OBJECT_ID = 6256611), set status(Bind#5)=5 (INVALID)

update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,
       dataobj#=:10,flags=:11,oid$=:12,spare1=:13,spare2=:14,spare3=:15,signature=:16,spare7=:17,spare8=:18,spare9=:19, 
       dflcollid=decode(:20,0,null,:20),creappid=:21,creverid=:22, modappid=:23,modverid=:24,crepatchid=:25,modpatchid=:26 
 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null

BINDS #139943683342520:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=7f472e0e19a8  bln=22  avl=05  flg=05
  value=6256611
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7f472e0e19c0  bln=22  avl=02  flg=01
  value=11
 Bind#2
  oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
  kxsbbbfp=988e02ad  bln=07  avl=07  flg=09
  value="8/25/2026 13:43:24"
 Bind#3
  oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
  kxsbbbfp=988e02b4  bln=07  avl=07  flg=09
  value="8/25/2026 14:12:8"
 Bind#4
  oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
  kxsbbbfp=988e02bb  bln=07  avl=07  flg=09
  value="8/25/2026 14:12:8"
 Bind#5
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=72 off=0
  kxsbbbfp=7f472e0e1948  bln=22  avl=02  flg=05
  value=5
  
 ......
 
 Bind#25
  oacdty=01 mxl=32(08) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=988e0096  bln=32  avl=08  flg=09
  value="TEST_PKG"
 Bind#26
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f472e0e1828  bln=22  avl=02  flg=05
  value=2

Package Body TEST_PKG INVALID is due to the difference between p_timestamp (25-AUG-2026 14:11:44) of its dependency and stime (25-AUG-2026 14:16:41) of its dependent Table TEST_TAB.>
In fact, Oracle has one query to reveal such difference.>
(sys.dependency$ contains column p_timestamp, which is not exposed in dba_dependencies).


  select do.obj#                                                         d_obj,
         do.name                                                         d_name,
         do.type#                                                        d_type,
         po.obj#                                                         p_obj,
         po.name                                                         p_name,
         to_char (d.p_timestamp, 'DD-MON-YYYY HH24:MI:SS')               "P_Timestamp",
         to_char (po.stime, 'DD-MON-YYYY HH24:MI:SS')                    "STIME",
         decode (sign (po.stime - d.p_timestamp), 0, 'SAME', '*DIFFER*') "CHECK"
    from sys.obj$ do, sys.dependency$ d, sys.obj$ po
   where d.p_obj# = po.obj#(+) and d.d_obj# = do.obj# 
     --and do.status=1 /*dependent is valid*/
     --and po.status=1 /*parent is valid*/
     --and po.stime != d.p_timestamp /*parent timestamp not match*/
      and do.name = 'TEST_PKG'
order by 2, 1;

    D_OBJ D_NAME     D_TYPE      P_OBJ P_NAME     P_Timestamp                   STIME                         CHECK
  ------- ---------- ------ ---------- ---------- ----------------------------- ----------------------------- ----------
  6256610 TEST_PKG        9       1219 STANDARD   16-JUL-2018 00:00:00          16-JUL-2018 00:00:00          SAME
  6256611 TEST_PKG       11       1219 STANDARD   16-JUL-2018 00:00:00          16-JUL-2018 00:00:00          SAME
  6256611 TEST_PKG       11    6256609 TEST_TAB   25-AUG-2026 14:11:44          25-AUG-2026 14:16:41          *DIFFER*
  6256611 TEST_PKG       11    6256610 TEST_PKG   25-AUG-2026 13:43:24          25-AUG-2026 13:43:24          SAME

4. Invoke Makes Valid

We use SQL trace again to track how Oracle makes Package Body Valid when package programs are invoked:


alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier = 'trc_2';
alter session set events '10046 trace name context forever, level 12';  

declare
  l_ret number;
begin
  l_ret := test_pkg.func1(1);
  dbms_output.put_line('l_ret = '||l_ret);
end;
/ 

alter session set events '10046 trace name context off';

We can see at first that the entry in dependency$ for d_obj#: TEST_PKG (OBJECT_ID = 6256611) and p_obj#: TEST_TAB (OBJECT_ID = 6256609) is updated to p_timestamp(Bind#0)="8/25/2026 14:16:41".


=== update dependency$ for d_obj#: TEST_PKG (OBJECT_ID = 6256611) and p_obj#: TEST_TAB (OBJECT_ID = 6256609), set p_timestamp(Bind#0)="8/25/2026 14:16:41"

PARSING IN CURSOR #139943671609592 len=93 dep=1 uid=0 oct=6 lid=0 tim=845811925428 hv=1207594200 ad='979c4718' sqlid='2hztt6j3znv6s'

update dependency$    set p_timestamp = :1, d_attrs = :2    
where
 d_obj# = :3 and p_obj# = :4
 
BINDS #139943671609592:

 Bind#0
  oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=8 off=0
  kxsbbbfp=989ee68c  bln=08  avl=07  flg=09
  value="8/25/2026 14:16:41"
 Bind#1
  oacdty=23 mxl=32(05) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=32 off=0
  kxsbbbfp=7f472d7aa1d0  bln=32  avl=05  flg=09
  value=00030000
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f472d3a1850  bln=22  avl=05  flg=05
  value=6256611
 Bind#3
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f472d3a1820  bln=24  avl=05  flg=05
  value=6256609

Then status of package body: TEST_PKG (OBJECT_ID = 6256611) is updated to status(Bind#5)=1 (VALID):


=== update package body: TEST_PKG (OBJECT_ID = 6256611), set status(Bind#5)=1 (VALID)

PARSING IN CURSOR #139943671609432 len=409 dep=1 uid=0 oct=6 lid=0 tim=845811927174 hv=696165170 ad='9ecdf3e8' sqlid='c3utnxsnrx8tk'

update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,
  dataobj#=:10,flags=:11,oid$=:12,spare1=:13,spare2=:14,spare3=:15,signature=
  :16,spare7=:17,spare8=:18,spare9=:19, dflcollid=decode(:20,0,null,:20),
  creappid=:21,creverid=:22, modappid=:23,modverid=:24,crepatchid=:25,
  modpatchid=:26 
where
 owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname 
  is null and subname is null
  
BINDS #139943671609432:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=7f472d709e00  bln=22  avl=05  flg=05
  value=6256611
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7f472d709e18  bln=22  avl=02  flg=01
  value=11
 Bind#2
  oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
  kxsbbbfp=988e02ad  bln=07  avl=07  flg=09
  value="8/25/2026 13:43:24"
 Bind#3
  oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
  kxsbbbfp=988e02b4  bln=07  avl=07  flg=09
  value="8/25/2026 14:12:8"
 Bind#4
  oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
  kxsbbbfp=988e02bb  bln=07  avl=07  flg=09
  value="8/25/2026 14:12:8"
 Bind#5
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=72 off=0
  kxsbbbfp=7f472d8bb938  bln=22  avl=02  flg=05
  value=1

  ......
  
 Bind#25
  oacdty=01 mxl=32(08) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=988e0096  bln=32  avl=08  flg=09
  value="TEST_PKG"
 Bind#26
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f472d8bb770  bln=22  avl=02  flg=05
  value=2

We re-run the query, it shows P_Timestamp of Package Body TEST_PKG is SAME as STIME of TEST_TAB, both are "25-AUG-2026 14:16:41". Therefore, Package Body TEST_PKG becomes VALID.


  select do.obj#                                                         d_obj,
         do.name                                                         d_name,
         do.type#                                                        d_type,
         po.obj#                                                         p_obj,
         po.name                                                         p_name,
         to_char (d.p_timestamp, 'DD-MON-YYYY HH24:MI:SS')               "P_Timestamp",
         to_char (po.stime, 'DD-MON-YYYY HH24:MI:SS')                    "STIME",
         decode (sign (po.stime - d.p_timestamp), 0, 'SAME', '*DIFFER*') "CHECK"
    from sys.obj$ do, sys.dependency$ d, sys.obj$ po
   where d.p_obj# = po.obj#(+) and d.d_obj# = do.obj# 
     --and do.status=1 /*dependent is valid*/
     --and po.status=1 /*parent is valid*/
     --and po.stime != d.p_timestamp /*parent timestamp not match*/
      and do.name = 'TEST_PKG'
order by 2, 1;

    D_OBJ D_NAME     D_TYPE      P_OBJ P_NAME     P_Timestamp                   STIME                         CHECK
  ------- ---------- ------ ---------- ---------- ----------------------------- ----------------------------- ----------
  6256610 TEST_PKG        9       1219 STANDARD   16-JUL-2018 00:00:00          16-JUL-2018 00:00:00          SAME
  6256611 TEST_PKG       11       1219 STANDARD   16-JUL-2018 00:00:00          16-JUL-2018 00:00:00          SAME
  6256611 TEST_PKG       11    6256609 TEST_TAB   25-AUG-2026 14:16:41          25-AUG-2026 14:16:41          SAME
  6256611 TEST_PKG       11    6256610 TEST_PKG   25-AUG-2026 13:43:24          25-AUG-2026 13:43:24          SAME

On Oracle

Thursday, October 8, 2026

Oracle Package Body Invalid without DBA_ERRORS: Case Study

1. Test Code

2. Modify Table to trigger Package Body Invalid

3. SQL Trace to Reveal Dependency and Invalid

4. Invoke Makes Valid