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

Interesting .. thanks !

On Thu, May 2, 2019 at 8:56 AM Luis Santos <lsantos_at_pobox.com> wrote:

> 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
>>
> --
Cheers,
Kunwar

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