Non-Blocking Online Index Creation
Date: Thu, 17 Dec 2020 12:15:17 +0000 (UTC)
Message-ID: <1150638706.1736500.1608207317437_at_mail.yahoo.com>
Oracle 18.10
We have an application where an upgrade is handled by some vendor code based on a single (very long) DDL script. Several times it creates a new index ONLINE which takes hours, and then moves on to do other things.
Since the index creates are all ONLINE we'd like it to do these asynchronously and concurrently and continue with the rest of the upgrade script. There is more than enough hardware capacity. (It needs to be ONLINE as the application is up during the upgrade).
So - here's the problem: We can get changes made to the DDL script but it is not supported to change the installation mechanism - it MUST all happen within a single SQL script (i.e. we cannot manually jump in with sqlplus in another window to do things, like pre-create the indexes).
I initially though we could have it create a scheduler job to fire off the index creates but this raises ORA-14426 because they are ONLINE.
Is there any other possibility to stop the online index creates happening sequentially in the foreground and blocking progress of the rest of the SQL script?
Thanks for any ideas!Charlotte
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 17 2020 - 13:15:17 CET