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@xxxxxxxxx> 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*
