no DDL Trigger

This will help prevent accidental DDL in the Database. Disable the trigger when you need to do DDL and then enable it.

Please make sure you change SCHEMA1 to 4 to your database schemas.

CREATE OR REPLACE TRIGGER no_ddl_trg
BEFORE ALTER OR CREATE OR DROP ON DATABASE
DECLARE
sql_text ora_name_list_t;
v_stmt VARCHAR2(4000);
n PLS_INTEGER := 0;
v_msg VARCHAR2 (1000)
:= 'No DDL allowed on '
|| dictionary_obj_owner
|| '.'
|| dictionary_obj_name
|| ' from '
|| login_user
|| ' user. Please contact your Database Adminstrators';
BEGIN
IF dictionary_obj_owner IN
('SCHEMA1',
'SCHEMA2',
'SCHEMA3',
'SCHEMA4'
)
AND dictionary_obj_name LIKE '%'
AND dictionary_obj_type LIKE '%'
THEN
CASE dictionary_obj_type
WHEN 'VIEW' THEN -- If VIEW is being COMPILED allow it
BEGIN
n := ora_sql_txt(sql_text);
FOR i IN 1..n LOOP
IF i < 63
THEN
v_stmt := v_stmt || sql_text(i);
ELSE
EXIT;
END IF;
END LOOP;

IF INSTR(UPPER(v_stmt), 'COMPILE') = 0 -- NOT FOUND
THEN
raise_application_error (-20905, v_msg);
END IF;
END;
ELSE
raise_application_error (-20905, v_msg);
END CASE;
END IF;
END;
/