Re: Is there a way/hack to allow the query only to run for specified duration in Oracle and kill it.

You may want to check (if available to your platform) a new Oracle 19c feature, called *SQL Quarantine*.

https://db.geeksinsight.com/2019/04/26/oracle19c-sqlquarantine/

*--*
*Att*

*Luis Santos*

Em qua, 1 de mai de 2019 às 16:08, kunwar singh <krishsingh.111_at_gmail.com> escreveu:

> Thank you for all valuable suggestions..I willlook into rm and job
> suggestions
>
> On Wed, May 1, 2019 at 2:02 PM Andy Sayer <andysayer_at_gmail.com> wrote:
>
>> What undocumented parameters are you planning on brute forcing until
>> something works?
>>
>> Why do you need to use this strict remote DB to test it? Surely there is
>> a less important test database that you can test on.
>>
>> I suggest you just have some victim/DBA (that does have the right access)
>> to watch over your session as it runs and kill it manually if needs be.
>>
>> Regards,
>> Andy
>>
>> On Wed, 1 May 2019 at 18:54, <post.ethan_at_gmail.com> wrote:
>>
>>> I am going to assume you have very limited access to this database and
>>> likely ability to implement some of the other solutions.
>>>
>>>
>>>
>>> Using a scheduled job is going to be your best bet. Something akin to
>>> this…
>>>
>>>
>>>
>>> -- anonymous PL/SQL block (not real code)
>>>
>>> begin
>>>
>>> get current session id and schedule job to run in one minute to kill
>>> this session using dbms package or alter statement.
>>>
>>> end;
>>>
>>> /
>>>
>>>
>>>
>>> Your SQL here…
>>>
>>>
>>>
>>> The problem is your user will likely require some explicit grants on
>>> alter or dbms package to work.
>>>
>>>
>>>
>>> The other idea would be to set up a job that monitors for the specific
>>> SQL and automatically kills it after it has been running one minute. Or
>>> perhaps you can submit two .sqls, one to run the SQL and the other to find
>>> the session and kill it after 1 minute of sleep (dbms_lock).
>>>
>>>
>>>
>>> It is really best when you kill sessions to make sure it is killed both
>>> on the DB and on the OS. Ideally someone with a lot more access could set
>>> this up for you.
>>>
>>>
>>>
>>> Ethan
>>>
>>> notaframework.com
>>>
>>> _at_poststop
>>>
>>>
>>>
>>>
>>>
>>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
>>> Behalf Of *kunwar singh
>>> *Sent:* Wednesday, May 1, 2019 10:46 AM
>>> *To:* ORACLE-L <oracle-l_at_freelists.org>
>>> *Subject:* Is there a way/hack to allow the query only to run for
>>> specified duration in Oracle and kill it.
>>>
>>>
>>>
>>> Hi Listers,
>>>
>>> Small question.
>>>
>>>
>>>
>>> I currently working on a remote database in which i can only pass a
>>> query through a tool and i want to kill it after letting it run only for 1
>>> minute
>>>
>>> The reason for this is i have to try different underscore parameters at
>>> session to check if the issue is fixed or not.
>>>
>>>
>>>
>>> I am looking for any solution which is completely in a single sql . What
>>> i pass to the tool is a .sql file , thats all.
>>>
>>> If you have a simple solution in shell then i will try that one as well.
>>> Meaning i can run a shell command like this:
>>>
>>> !hostname
>>>
>>>
>>>
>>>
>>>
>>> The query that i want to run is a big select sql , which unfortunately i
>>> cannot paste here.
>>>
>>>
>>>
>>> --
>>>
>>> Cheers,
>>> Kunwar
>>>
>> --
> Cheers,
> Kunwar
>

--
http://www.freelists.org/webpage/oracle-l