The user has established a trigger:
Create or Replace Trigger Ddl_deny
Before create or alter or drop on database
Declare
Begin
INSERT into ddl_logs values (ora_dict_obj_owner,ora_dict_obj_name,sysdate);
exception
When No_data_found Then
Null
End
The goal is probably to record all the DDL operations, but the trigger is built with errors and finds:
11:30:08 System@ora250>alter trigger Ddl_deny disable;
ALTER TRIGGER Ddl_deny Disable
*
ERROR is on line 1th:
ORA-04098: Invalid trigger ' Ddl_deny ' and failed to authenticate
11:31:45 System@ora250>drop trigger Ddl_deny;
Drop Trigger Ddl_deny
*
ERROR is on line 1th:
ORA-04098: Invalid trigger ' Ddl_deny ' and failed to authenticate
The trigger cannot be compiled at this time, nor can it be deleted, because the trigger itself defines a DDL operation, resulting in ORA-04098: the trigger ' Ddl_deny ' is invalid and not validated.
Workaround:
1, first check the user's permissions are correct:
Select owner, object_name, object_type, status from dba_objects where object_name = ' <TRIGGER_NAME> ';
12:42:38 system@ora250>select owner, object_name, object_type, status from Dba_o
bjects where object_name= ' Ddl_deny ';
OWNER object_name object_type STATUS
------------------------------------ --------------
SYSTEM Ddl_deny TRIGGER INVALID
There is no problem discovering user permissions.
2, then set the diagnostic event alter session set events= ' 4098 trace name Errorstack Level 3 ', view the contents of the trace file as follows:
Found to be a serious internal error, others do not see too many error messages, so think of the use of hidden parameters _system_trigger_enabled=false, when the database started to make all triggers do not work, and then delete. Database 8.1.5 Not this parameter, so the query:
14:28:32 System@ora815>select ksppinm from X$ksppi where substr (ksppinm,1,1) = ' _ '
and ksppinm like '%tri% ' order by KSPPINM;
Ksppinm
-------------------------------------------------------------------------------
_cleanup_rollback_entries
_distributed_lock_timeout
_distributed_recovery_connection_hold_time
_number_cached_attributes
_system_trig_enabled
The 8.1.5 version of the parameter was found to be _system_trig_enabled, so the user set this parameter to FALSE in the initialization parameter file, then restart the database, delete the trigger, and delete the success.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
