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;
/
