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

From: John Thomas <jt2354_at_gmail.com>
Date: Sat, 4 May 2019 20:58:53 +0100
Message-ID: <CAOHpfbEPGYuUTx5wDxL+_d_6i=LJQt2yvUbKqotW3QSOVchbtA_at_mail.gmail.com>



You may want to check out user profiles and MOS note How To Use PROFILES To Limit User Resources (Doc ID 1016552.102)

CPU_PER_SESSION Specify the CPU time limit for a session, expressed in hundredth of seconds.

CPU_PER_CALL Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.

Note that the 11g manual recommends the use of Resource Manager, not user profiles.

Regards,

John Thomas
Database Designer and Administrator
https://oracleexpert.net

On Thu, 2 May 2019 at 23:26, kunwar singh <krishsingh.111_at_gmail.com> wrote:

> 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
Received on Sat May 04 2019 - 21:58:53 CEST

Original text of this message