How to run DDL on busy objects?

Sometimes we always get resource too busy when DDL on a object. Here is how to use PL/SQL to do that.

declare
  resource_busy exception;
  pragma exception_init (resource_busy,-54);
begin
loop
   begin
     execute immediate 'alter index INDEX_NAME noparallel';
     exit;
   exception
    when resource_busy then
     dbms_lock.sleep(1);
   end;
end loop;
end;
/
Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: