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 ?
Keep Reading
