Preventing Oracle DDL unauthorized schema changes by Donald Burleson

One of the biggest problems in Oracle are "unexpected" schema changes.  In production environments changes are often allowed only during special maintenance times, and changes such as re-analyzing schema statistics and DDL to change table/index structures must be prohibited at other times.

This can be tracked with an Oracle DDL trigger, but the DBA can disable DDL at the object level with a simple script to "disable table lock" syntax:

alter table sapr3.vbelm disable table lock;

This script could be used to lock all tables within a schema:

connect sys/xxx as sysdba;
spool runme.sql
select "alter table "||owner||"."||table_name||" disable table lock;"
from
   dba_tables
where
   owner = "SAPR3";

spool off;
@runme.sql

While locked, any attempted schema changes will be rejected, and the attempt can be logged with a servererror trigger:

SQL>ALTER TABLE SAPR3.VBELM ADD (NEWCOL NUMBER);

ALTER TABLE SAPR3.VBELM
*

ERROR at line 1:
ORA-00069: cannot acquire lock-- table locks disabled for SAPR3.VBELM