Search code examples
sqloracletimeoutlimitsettimeout

Stop Oracle sql query execution automatically using profile limits

I want to limit Oracle user by executing query more then 15 second, for this I've created new profile with this script bellow and assign it to user.


CREATE PROFILE   jibo_test_profile   LIMIT
SESSIONS_PER_USER                       UNLIMITED
CPU_PER_SESSION                            UNLIMITED
CPU_PER_CALL                                   1500
CONNECT_TIME                                   45
LOGICAL_READS_PER_SESSION    DEFAULT
LOGICAL_READS_PER_CALL           1000
PRIVATE_SGA                                       15K
COMPOSITE_LIMIT                               5000000;

then I tried to execute simple select query (select * from something) which works more then 15 second, but it does not stops when query execution time reaches 15 second ( even 5 minute :) ), then i tried to assign "CPU_PER_SESSION" 1500, but without any change.

Do you have any ideas why this limits does not works for me?

Solution

  • The reason of problem was, that resource limit was set on "false", so profile limits were not working for user.

    To use profile limits, RESOURCE_LIMIT should be set on "true"

    ALTER SYSTEM SET RESOURCE_LIMIT = true;