Home / / ORA-04088: error during execution of trigger ‘SYS.XDB_PI_TRIG’ PLS-00302: component ‘IS_VPD_ENABLED’ must be declared

ORA-04088: error during execution of trigger ‘SYS.XDB_PI_TRIG’ PLS-00302: component ‘IS_VPD_ENABLED’ must be declared

While I was testing something on a 12.1 test database got this below error whenever I’m trying to execute specific admin commands:

SQL> drop user xx;
drop user xx
*
ERROR at line 1:
ORA-04088: error during execution of trigger ‘SYS.XDB_PI_TRIG’
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 3, column 13:
PLS-00302: component ‘IS_VPD_ENABLED’ must be declared
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored

 
SQL> alter table bb move online compress;  
alter table bb move online compress
            *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04088: error during execution of trigger ‘SYS.XDB_PI_TRIG’
ORA-00604: error occurred at recursive SQL level 2
ORA-06550: line 3, column 13:
PLS-00302: component ‘IS_VPD_ENABLED’ must be declared
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored
The above was just a sample but the error with showing up with lots of admin commands!
I checked the trigger SYS.XDB_PI_TRIG which causing this error and it was already valid, so I decided to DISABLE it, and then admin commands ran as usual:
SQL> alter trigger SYS.XDB_PI_TRIG disable;
 
Trigger altered.
Above failing admin commands have run smoothly:
SQL> alter table bb move online compress; 
 
Table altered.
Frankly speaking, I tried to google that error without any success, I didn’t dig deeper, so I took the shortest/laziest way and disabled the root cause trigger as a dirty fix, the database where I disabled that trigger was a test DB, most probably one of my fancy test scenarios caused this issue to happen.

In case you have the same error on a Production Database I strongly recommend you to contact Oracle Support before disabling the above-mentioned trigger.

Update: I’ve dug more and found that the root cause was that someone created a table with the name “sys” under SYS user. Bug 17431402 yes it’s a bug because the engine should throw an error if someone tries to create an object with a “reserved word”.

I’ve dropped that object “sys.sys” and the error disappeared:

SQL> alter trigger SYS.XDB_PI_TRIG disable;

Trigger altered.

SQL> drop table sys.sys;

Table dropped.

SQL> alter trigger SYS.XDB_PI_TRIG enable;

Trigger altered.

SQL> alter table bb move online compress; 
 
Table altered.
 
phew!

About Mahmmoud ADEL

View all posts by Mahmmoud ADEL →

One thought on “ORA-04088: error during execution of trigger ‘SYS.XDB_PI_TRIG’ PLS-00302: component ‘IS_VPD_ENABLED’ must be declared”

Leave a Reply

Your email address will not be published. Required fields are marked *