ERP application schemas have thousands of tables and views. At our company, it was taking a full hour to "Drop User Cascade." By using 1 Drop_master session and 3 drop_slave sessions, the time was cut in half. These procedures allow multiple sessions to drop tables and views simultaneously. I wrote and used these procedures on an Oracle 22.214.171.124 database. My testing and usage has been against a Peoplesoft install of 12,000+ tables and 6,000+ views.
These procedures are using user_objects to get the name of all the tables and views to drop from a schema. Remember to grant execute on dbms_alert to &Schema_name. We have had good success with 3 slave sessions running.
-----------Begin Script--------------- create or replace procedure DROP_MASTER as v_msg varchar2(2048); v_status integer := 10000; cursor c_obj is select object_name, object_type from user_objects where object_type in ('TABLE','VIEW'); snapshot_too_old EXCEPTION; -- Expected condition to ignore PRAGMA EXCEPTION_INIT(snapshot_too_old, -1555); --ORA-01555: snapshot too old: rollback segment number 2 with name "RBS01" too begin dbms_alert.register('A1'); <<STARTITUP>> BEGIN for c_obj_rec in c_obj loop dbms_alert.signal('A1','drop '||c_obj_rec.object_type||' '||c_obj_rec.object_name); commit; end loop; EXCEPTION when snapshot_too_old then null; end; --Since messages can be skipped, checks to see if job is done select count(*) into v_status from user_objects where object_type in ('TABLE','VIEW'); if v_status <> 0 then --if it is not done, restart job goto STARTITUP; else --cleanup dbms_alert.removeall; commit; end if; end; / create or replace procedure DROP_SLAVE as v_msg varchar2(2048); v_status integer := 10000; obj_doesnot_exist EXCEPTION; -- Expected condition to ignore PRAGMA EXCEPTION_INIT(obj_doesnot_exist, -942); --ORA-00942: table or view does not exist resource_busy EXCEPTION; -- Expected condition to ignore PRAGMA EXCEPTION_INIT(resource_busy, -54); --ORA-00054: resource busy and acquire with NOWAIT specified begin dbms_alert.register('A1'); loop dbms_alert.waitone('A1',v_msg, v_status); dbms_output.put_line(v_msg||' Dropped'); begin execute immediate v_msg; exception when obj_doesnot_exist then null; when resource_busy then null; end; end loop; end; / -----------End Script---------------
Brian Peasland writes: This is a good tip and the author does some nice error trapping. The only problem I see with it is that it doesn't handle Foreign Key constraints. You can attempt to drop a table and the FK constraint disallows the drop operation.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free DB2 tips and scripts.
- Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
- Best Web Links: DB2 tips, tutorials, and scripts from around the Web.