Re: Non-Blocking Online Index Creation

From: Andy Sayer <andysayer_at_gmail.com>
Date: Thu, 17 Dec 2020 12:30:24 +0000
Message-ID: <CACj1VR41pmm23tVKTYE1t+peQguUi+77Hn_A11vhcqGKmu+xHA_at_mail.gmail.com>



Hi Charlotte,

What OS are you running the sql*plus script from? You may be able to write a host command that starts off a separate script in the background.

Off the top of my head, in Windows something like Host start cmd /c sqlplus -l user/pass_at_connection _at_indexes.sql

Although this requires the password being known to the running script or the use of an Oracle Wallet. If that’s possible and you’re not on Windows then I’m sure the same sort of thing is easy enough.

Otherwise, I think you’ll struggle to find anything at the DB level that will allow this separate session without starting an autonomous transaction.

Hope that helps,
Andy

On Thu, 17 Dec 2020 at 12:17, Charlotte Hammond <dmarc-noreply_at_freelists.org> wrote:

>
> 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:30:24 CET

Original text of this message