Re: Non-Blocking Online Index Creation

I didn’t expect that, nice find! Fingers crossed that the implementation of dbms_job through dbms_scheduler manages to replicate the same behaviour - I believe they managed to make it transactional when called through dbms_job in 19c so it’s promising.

Thanks,
Andy

On Thu, 17 Dec 2020 at 13:17, Charlotte Hammond < charlottejanehammond_at_yahoo.com> wrote:

> Thanks Andy,
>
> Unfortunately we don't have access to the O/S from within the script.
>
> I've found that it works if we use DBMS_JOB instead of DBMS_SCHEDULER, at
> least in 18c - I suspect it won't once we upgrade to 19c although I've not
> had a chance to try yet.
>
> Charlotte
>
>
>
> On Thursday, December 17, 2020, 12:31:29 PM GMT, Andy Sayer <
> andysayer_at_gmail.com> wrote:
>
>
> 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