Preventing DDL operations on a table

One of my friends from OracleTURK asked me how is it possible to lock(avoid doing) tables to DDL statements. He was after a simple implementation similar to for example how we lock(avoid re-gathering) object statistics with DBMS_STATS package.

Oracle permits DDL operations on a table only if the table can be locked during the operation. Such table locks are not required during DML operations. One exception is table locks are not acquired on temporary tables.

I mentioned this option in my two day seminar notes of Mr.Jonathan Lewis before for another need –
“alter table fk_table disable table lock; command will produce an error for a locking need dml on child, there will not be any lock problem even if the fkey column is not indexed”

There can be several different ways to handle this need and this option may not be handy for your own enviroment. For example if you are not the only one who knows the password of the schema user which the table is owned by or there are “alter any” or some wide privileged, like roles dba or import_full_database granted, accounts someone can quickly alter table to enable lock to get rid of the error :)

“alter table disable table lock” may be one of the simplest way to do this – Code Listing 143 – How to prevent DDL on a table

1 Comment

Leave a Reply to H.Tonguç Yılmaz Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s