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

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')"
 https://gist.github.com/xtender/9ca69e8bef927f4bd678ea66d5f75653

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

CREATE OR REPLACE TRIGGER trg_after_create AFTER CREATE ON SCHEMA
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);
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';

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

> Hi ORACLE-L!
>
> 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
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l