How to capture the query throwing ORA-00900: invalid SQL statement
ORA-00900 error when running a long SQL script during deployment. In this video I show the steps to easily identify the running SQL using the "after servererror on database" trigger.
CREATE TABLE "SYS"."DBA_SERVERERROR"
( "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
"ERROR_DATE" DATE NOT NULL ENABLE,
"ORA_SYSEVENT" VARCHAR2(128 BYTE),
"ORA_LOGIN_USER" VARCHAR2(128 BYTE),
"ORA_SERVER_ERROR" VARCHAR2(4000 BYTE),
"SID" NUMBER,
"HOST" VARCHAR2(256 BYTE),
"IP" VARCHAR2(15 BYTE),
"MODULE" VARCHAR2(4000 BYTE),
"SERVERHOST" VARCHAR2(256 BYTE),
"SQL" CLOB,
"OS_USER" VARCHAR2(255 BYTE),
"SESSION_USER" VARCHAR2(255 BYTE)
);
create or replace NONEDITIONABLE TRIGGER SYS.TRIG_AUDIT_ORA_00900
after servererror on database
declare
v_sql_text ora_name_list_t;
v_sql clob;
v_n number;
SQL_CODE VARCHAR2(100);
SQL_ERRM VARCHAR2(200);
SQLID VARCHAR2(20);
begin
--If you find a huge number of irrelevant errors, you might want to filter them out here.
--ORA-00900 invalid SQL statement when Explain Plan(F10) in SQL Developer
--ALTER TABLE sample ADD (CONSTRAINT sample_pk PRIMARY KEY (id));
if ( is_servererror(900) ) then
v_n := ora_sql_txt(v_sql_text);
for i in 1 .. v_n loop
v_sql := v_sql || v_sql_text(i);
end loop;
SYS.DBMS_SYSTEM.KSDWRT(2,'ORA-30000 '||NVL(sys_context('USERENV', 'AUTHENTICATED_IDENTITY'),SYS_CONTEXT( 'USERENV', 'OS_USER' ))||' ORA-00900 invalid SQL statement FROM '||SYS_CONTEXT( 'USERENV', 'OS_USER' )||' '||SYS_CONTEXT( 'USERENV', 'HOST' ));
insert into DBA_servererror(error_date, ora_sysevent, ora_login_user, ora_server_error, sid, host, ip, module, serverhost, sql, os_user, session_user)
values
(
sysdate,
ora_sysevent,
ora_login_user,
ora_server_error(1),
sys_context ('USERENV','SID'),
sys_context ('USERENV','HOST'),
sys_context ('USERENV','IP_ADDRESS'),
sys_context ('USERENV','MODULE'),
sys_context ('USERENV','SERVER_HOST'),
v_sql,
SYS_CONTEXT( 'USERENV', 'OS_USER' ),
SYS_CONTEXT( 'USERENV', 'SESSION_USER' )
);
commit;
end if;
--Never raise an exception from trig_servererror.
--No matter what happens we don't want recursive errors.
exception when others then
NULL;
end;
Смотрите видео How to capture the query throwing ORA-00900: invalid SQL statement онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Ken Kim 23 Июль 2023, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 478 раз и оно понравилось 4 людям.