Re: Non-Blocking Online Index Creation
Date: Thu, 17 Dec 2020 13:17:14 +0000 (UTC)
Message-ID: <1057625017.1742656.1608211034169_at_mail.yahoo.com>
Thanks Andy,
On Thursday, December 17, 2020, 12:31:29 PM GMT, Andy Sayer <andysayer_at_gmail.com> wrote:
Hi Charlotte,
Oracle 18.10
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
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 likeHost 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:
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 - 14:17:14 CET