Restrict performing DDL in a Schema

To restrict Schema owner/user to perform any kind of DDL, the following TRIGGER can be useful:

CREATE OR REPLACE TRIGGER trigger_name
BEFORE ALTER OR CREATE OR DROP ON SCHEMA
RAISE_APPLICATION_ERROR(-20001,' give your custom message ');
END;
/

To disable/enable the TRIGGER :

ALTER TRIGGER trigger_name { ENABLE | DISABLE };

but the problem of this script is the schema owner must have DBA role to enable/disable the trigger .

IF the schema owner does not have DBA role Please use the following script...

CREATE TABLE trigger_lock ( status NUMBER(1) );

INSERT INTO trigger_lock VALUES(1);

CREATE OR REPLACE TRIGGER ddl_trigger
BEFORE ALTER OR CREATE OR DROP
ON SCHEMA
DECLARE
x NUMBER(1);

BEGIN

x:=0;

SELECT MAX(status) INTO x from trigger_lock;

IF (x=1) THEN

raise_application_error(-20001,' You are not authorize to perform DDL. Please contact DBA team.');

END IF;

END;

/

now you can enable/disable the trigger by insertion of updating trigger_lock table (1 for disable, 0 for enable)