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@xxxxxxxxx> 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@xxxxxxxxx> 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@connection @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@xxxxxxxxxxxxx> 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
