My client DBA wrote a small script: To drop a column to a busy table. This application is mostly an OLTP application and table is modified heavily. It is hard to get a lock on the table, even though DDL to drop the column will be quick completing within sub-seconds. Following script was tried in the middle of night so as to minimize production impact.
set serveroutput on size 100000 declare begin -- lock table in exclusive mode execute immediate 'lock table t1 in exclusive mode'; dbms_output.put_line ('**** Table locked. Dropping column ****' ) ; -- If we lock it, drop the column execute immediate 'alter table t1 drop column n5 '; exception when others then dbms_output.put_line(' No luck '); dbms_output.put_line(' SQL code'||sqlcode||','|| 'error '||sqlerrm ); end; /
Logic of the above script is a) wait for 5 minutes to lock the table in exclusive mode, before timing out. b) If we acquire exclusive lock on that table, then add a column.
Do you see any issues with this script?
So, did we add that column ?