Re: Is there a way to create, by default, an index in UNUSABLE state?

From: Sayan Malakshinov <>
Date: Sat, 17 Aug 2019 01:33:47 +0300
Message-ID: <>

Hi Luis,

[Just for fun]: You can execute "alter index unusable" from after-create ddl trigger with "when (ora_dict_obj_type='INDEX' and ora_sysevent='CREATE')"

But I did it just for fun and wouldn't recommend to use it in production :)

when (ora_dict_obj_type='INDEX' and ora_sysevent='CREATE') DECLARE
  ix_owner all_objects.owner%type;
  ix_name all_indexes.index_name%type;
  s_ddl varchar2(300);
  ix_name :=ora_dict_obj_name;
  s_ddl:=utl_lms.format_message('alter index "%s"."%s" unusable',ix_owner,ix_name);
  dbms_output.put_line('generated from the trigger: '||s_ddl);   /*

  • DDL requires internal second commit and
  • the index doesn't exist yet,
  • so we can't alter it within the trigger: begin for r in (select index_name,status from all_indexes i where i.index_name=ix_name) loop dbms_output.put_line(r.status); end loop; execute immediate s_ddl; exception when others then dbms_output.put_line(sqlerrm); end; */
  • that's why we need to use job to alter it later: dbms_scheduler.create_job( job_name => substr('disable_index_'||ix_name,1,30), job_type => 'PLSQL_BLOCK', job_action => 'begin execute immediate q''['|| s_ddl ||']''; end;', start_date => sysdate, enabled => TRUE, auto_drop => TRUE, comments => 'job: disable index '||ix_name ); END trg_after_create; / create table xtest as select dummy x from dual; create index ixtest on xtest(x); select index_name,status from user_indexes i where index_name='IXTEST';

On Fri, Aug 16, 2019 at 8:10 PM Luis Santos <> wrote:

> I'm planning to perform a big schema transfer, using impdp by
> network_link. The schema has few big tables, with several partitions,
> subpartitions and indexes. So we have just eight tables but 3760 distinct
> index segments.
> When using network_link feature the impdp doesn't create the indexes
> concurrently.
> I measured the time impdp took to create the indexes, and it last 19
> hours. We are performing several tests before the real production move.
> I have done a test, putting all indexes from these 8 tables unusable, and
> rebuilded them using (undocomented, I know...) package dbms_index_utl,
> procedure build_schema_indexes. The concurrent rebuild tooks 45 minutes,
> indeed using all machine (CPU, IO) resources.
> I was wondering, for next test, a way to make impdp create the indexes
> already unusable. We are using TABLE_EXISTS_ACTION=REPLACE on IMPDP, as
> each day the partitions changes (day rolling window) in the source database.
> If this were possible the process would be too much easily to design. I
> don't want to generate indexes script and manually create them, appending
> the UNUSABLE clause...
> *--*
> *Att*
> *Luis Santos*

Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate

Received on Sat Aug 17 2019 - 00:33:47 CEST

Original text of this message