Just for fun: alter indexes unusable in after-create ddl trigger

alter_index_unusable.sql

CREATE OR REPLACE TRIGGER trg_after_create
AFTER CREATE ON SCHEMA
when (ora_dict_obj_type='INDEX' and ora_sysevent='CREATE' /* and ora_dict_obj_owner='XTENDER'*/)
DECLARE
  ix_owner all_objects.owner%type;
  ix_name  all_indexes.index_name%type;
  s_ddl varchar2(300);
BEGIN
  ix_owner:=ora_dict_obj_owner;
  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';