Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Ahmad.

Asked: January 25, 2018 - 12:37 pm UTC

Last updated: February 08, 2018 - 1:35 pm UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hello Tom,

Is it possible for a user without DBA priv and Select privs on base tables to create SQL Tuning Tasks?

what if we create a procedure under another user with DBA priv and grant the user execute on the procedure?


Thx for the Support :)



and Chris said...

The docs contain lots of details on the privileges needed for SQL tuning tasks:

This package is available to PUBLIC and performs its own security checking.

- As SQL Tuning Advisor relies on the advisor framework, all tuning task interfaces (XXX_TUNING_TASK) require privilege ADVISOR.

- SQL tuning set subprograms (XXX_SQLSET) require either the ADMINISTER SQL TUNING SET or the ADMINISTER ANY SQL TUNING SET privilege. Users having the ADMINISTER SQL TUNING SET privilege can only create and modify a SQL tuning set they own, while the ADMINISTER ANY SQL TUNING SET privilege allows them to operate upon all SQL tuning sets, even those owned by other users. For example, using the CREATE_SQLSET Procedure and Function you can create a SQL tuning set to be owned by another user. In this case, the user need not necessarily have the ADMINISTER SQL TUNING SET privilege to operate upon her tuning set.

- Previously, three different privileges were needed to invoke subprograms concerned with SQL profiles:

* CREATE ANY SQL PROFILE

* ALTER ANY SQL PROFILE

* DROP ANY SQL PROFILE

The preceding privileges have been deprecated in favor of ADMINISTER SQL MANAGEMENT OBJECT .


https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_SQLTUNE.html#GUID-821462BF-1695-41CF-AFF7-FD23E9999C6A

Some procedures in dbms_sqltune need further privileges, such as being able to query certain v$ views. Read the documentation for a description of when you need to grant what.

Rating

  (22 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ahmad, January 25, 2018 - 4:09 pm UTC

Hello,

First of all, thank you for the prompt Response.

We have a User (User A) in our environemt who wants to optimize some SQL Statements that belong to another Schema User (User B)

(User A) doesn't have Read Privs on objects that belong to (User B).

(User A) has been granted only the following privs:

ADVISOR
ADMINISTER SQL TUNING SET
SELECT ANY DICTIONARY
SELECT_CATALOG_ROLE



When (User A) tries to create Tuning Task using DBMS_SQLTUNE.CREATE_TUNING_TASK, he receives 'ORA-00942': table or view does not exist'.

Unfortunately, due to security/ Privacy concerns we are not allowed to grant (User A) neither DBA priv nor Read Privs on Objects that belong to (User B).

Now the question, how to solve this issue ?

will it work if we create a Custom Stored Procedure under SYS Schema and grant (User A) Execute priv on the Procedure ?

Thank you

Chris Saxon
January 26, 2018 - 10:38 am UTC

What exactly are you doing? Can you share with us a complete test case, starting with create user, that shows the issue you're hitting?

Ahmad, January 26, 2018 - 11:24 am UTC

Hello,


Here is a test scenario that shows the problem as well as our requirement:



conn tuner/***/

show user
tuner

DECLARE
stmt_task VARCHAR2(64);
BEGIN
stmt_task:=dbms_sqltune.create_tuning_task(sql_id => '9s1fcgvx16qu2', plan_hash_value => '3012306510', time_limit => 60, task_name => 'Tune_9s1fcgvx16qu2', description => 'Task to tune 9s1fcgvx16qu2 sql_id');
END;
/

PL/SQL procedure successfully completed

EXECUTE dbms_sqltune.execute_tuning_task('Tune_9s1fcgvx16qu2');

PL/SQL procedure successfully completed

SELECT dbms_sqltune.report_tuning_task('Tune_9s1fcgvx16qu2', 'TEXT', 'ALL') FROM dual;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : Tune_9s1fcgvx16qu2
Tuning Task Owner : tuner
Tuning Task ID : 87701
Workload Type : Single SQL Statement
Execution Count : 1
Current Execution : EXEC_83140
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 26/01/2018 10:40:06
Completed at : 26/01/2018 10:40:13

-------------------------------------------------------------------------------
Schema Name: RPADMIN
SQL ID : 9s1fcgvx16qu2
SQL Text : SELECT DISTINCT st.run_nr ,
st.per_date
FROM "RPADMIN.DK_STR" st
WHERE st.run_nr IS NOT NULL
AND st.per_date IS NOT NULL

-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- ORA-00942: table or view does not exist



We already know that the user "TUNER" has no select privs on "RPADMIN" objects.

BTW we are not allowed to grant "TUNER" read privs on "RPADMIN" tables, reason is the tables contain private data!


is there any way that "tuner" can optimize Statements that belong to "RPADMIN" without being expressly granted Select priv on "RPADMIN" tables ?

What if we create a procedure under "sys" as SYS can read "RPADMIN" tables, and then grant "tuner" execute priv on that procedure?


Thank you.


Chris Saxon
January 26, 2018 - 2:56 pm UTC

I don't know of an easy way around this. The tuning advisor needs privileges on the underlying tables to run the task.

What if we create a procedure under "sys" as SYS can read "RPADMIN" tables, and then grant "tuner" execute priv on that procedure?

Noooo! Don't create objects in sys!

Ahmad, January 26, 2018 - 3:43 pm UTC

Oracle DB supports execution of PL/SQL with DEFINER Rights (AUTHID DEFINER) so that PL/SQL package gets executed with the privileges of the owner of the schema in which the package resides.

Why the creation of Tuning tasks will not work properly if we create a custom procedure (that calls DBMS_SQLTUNE) under a privileged schema and grant execute priv to users who lack privs on underlying tables?

Why won't the procedure get executed with the Definer Rights?

so there is no solution to our requirement?

Thx
Chris Saxon
January 26, 2018 - 5:04 pm UTC

Whichever user runs the tuning task needs privileges on the tables used in the analyzed queries.

You could do this creating a privileged user, building a PL/SQL wrapper there and then granting privileges on this wrapper to others.

But then you've got a user that has read privileges on the tables RPADMIN admin. Which you were trying to avoid...

A reader, January 29, 2018 - 8:46 am UTC

Hello,


I have create a detailed test-case- see below.

A PL/SQL Wrapper has been created under Schema Owner "Appdata" and Execute Priv has been granted to external User "Consultant".. Unfortunately i didn't work, i dunno why Oracle is not executing the Procedure with Definer Rights:


Could you please check the problem?




----- Create a User who owns the DATA.


C:\Windows\System32>sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 29 09:18:25 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> CREATE USER APPDATA IDENTIFIED BY enq076755;

User created.

SQL>
SQL> GRANT CONNECT,RESOURCE,CREATE TABLE,CREATE PROCEDURE, CREATE VIEW TO APPDATA;

Grant succeeded.

SQL> ALTER USER APPDATA IDENTIFIED BY ***** DEFAULT TABLESPACE USERS;

User altered.

SQL> GRANT SELECT ANY DICTIONARY TO APPDATA;

Grant succeeded.

SQL> GRANT SELECT_CATALOG_ROLE TO APPDATA;

Grant succeeded.

SQL> GRANT ADVISOR TO APPDATA;

Grant succeeded.

SQL>
SQL> GRANT ADMINISTER SQL TUNING SET TO APPDATA;

Grant succeeded.




----- Create another User who will try to create tuning task.

SQL>
SQL>
SQL> CREATE USER CONSULTANT IDENTIFIED BY *****;

User created.

SQL> GRANT CONNECT,RESOURCE TO CONSULTANT;

Grant succeeded.

SQL>
SQL> ALTER USER CONSULTANT IDENTIFIED BY ******* DEFAULT TABLESPACE USERS;

User altered.

SQL> GRANT UNLIMITED TABLESPACE TO APPDATA;

Grant succeeded.


SQL> GRANT SELECT ANY DICTIONARY TO CONSULTANT;

Grant succeeded.

SQL> GRANT SELECT_CATALOG_ROLE TO CONSULTANT;

Grant succeeded.

SQL> GRANT ADVISOR TO CONSULTANT;

Grant succeeded.

SQL> GRANT ADMINISTER SQL TUNING SET TO CONSULTANT;

Grant succeeded.

SQL>




-- Conn as Appdata and create a Test Table:

C:\Windows\System32>sqlplus appdata

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 29 09:24:14 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter password:
Last Successful login time: Mon Jan 29 2018 09:22:17 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table tab_test_1 as select a.sid,a.sql_id from v$session a;

Table created.


SQL> create table tab_test_2 as select b.sql_id,b.optimizer_cost from v$sqlarea b;

Table created.


SQL> create view my_view (sql_id,optimizer_cost) as select a.sql_id,b.optimizer_cost
from tab_test_1 a, tab_test_2 b where a.sql_id=b.sql_id ;

View created.



SQL>

-- Create PL/SQL wrapper that calls DBMS_SQLTUNE.


SQL>
SQL> CREATE OR REPLACE PROCEDURE CREATE_TASK(
2 SQL_ID IN VARCHAR2,
3 PLAN_HASH_VALUE IN NUMBER,
4 TIME_LIMIT IN NUMBER,
5 SCOPE IN VARCHAR2,
6 TASK_NAME IN VARCHAR2,
7 DESCRIPTION IN VARCHAR2,
8 snap_first IN NUMBER DEFAULT NULL ,
9 snap_last IN NUMBER DEFAULT NULL,
10 output OUT clob )
11 AUTHID DEFINER
12 IS
13 stmt_task VARCHAR2(64);
14 xml_bits clob;
15
16
17 BEGIN
18 IF SNAP_FIRST IS NULL THEN
19
20 stmt_task:= dbms_sqltune.create_tuning_task(
21 sql_id => SQL_ID,
22 plan_hash_value => PLAN_HASH_VALUE,
23 scope => SCOPE ,
24 time_limit => TIME_LIMIT,
25 task_name => TASK_NAME,
26 description => DESCRIPTION);
27 dbms_sqltune.execute_tuning_task(TASK_NAME);
28 SELECT dbms_sqltune.report_tuning_task(TASK_NAME, 'TEXT', 'ALL') INTO xml_bits FROM dual;
29 output:= xml_bits;
30
31 ELSE
32
33 stmt_task:= dbms_sqltune.create_tuning_task
34 ( begin_snap => snap_first,
35 end_snap => snap_last,
36 sql_id => sql_id,
37 scope => SCOPE,
38 time_limit => time_limit,
39 task_name => task_name,
40 description => description);
41 SELECT dbms_sqltune.report_tuning_task(TASK_NAME, 'TEXT', 'ALL') INTO
42 xml_bits FROM dual;
43 output:= xml_bits;
44 END IF;
45 exception
46 WHEN OTHERS THEN
47 raise_application_error(-20001,'An error was encountered - '||SQLCODE||'-ERROR- '||SQLERRM);
48 END;
49 /

Procedure created.

-- Grant Execute Priv on PL/SQL To Consultant:

SQL> grant execute on CREATE_TASK to consultant;

Grant succeeded.

SQL>



SQL> select * from my_view;

SQL_ID OPTIMIZER_COST
------------- --------------
f3yfg50ga0r8n 3
b3h2tykc2588b 0
akxjxthj4fjus 1


SQL> select sql_id,plan_hash_value,substr(sql_text,1,20) from v$sqlarea where sql_text like '%my_view%';

SQL_ID PLAN_HASH_VALUE SUBSTR(SQL_TEXT,1,20
------------- --------------- --------------------
ba155sps8h4gy 1538754654 select * from my_vie

SQL>




-- Connect As consultant to execute the Task:




C:\Windows\System32>sqlplus consultant

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 29 09:36:27 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user
USER is "CONSULTANT"
SQL>
SQL> SET serveroutput on;
SQL> DECLARE
2 InParam1 varchar2(100);
3 InParam2 number(32);
4 InParam3 number(32);
5 InParam4 varchar2(100);
6 InParam5 varchar2(100);
7 InParam6 varchar2(100);
8 InParam7 number(32);
9 InParam8 number(32);
10 OutParam clob;
11 BEGIN
12 /* Assign values to IN parameters */
13 InParam1 := 'ba155sps8h4gy';
14 InParam2 := 1538754654;
15 InParam3 := 30;
16 InParam4 := 'COMPREHENSIVE';
17 InParam5 := 'Tune_ba155sps8h4gy';
18 InParam6 := 'Tune SQL_ID ba155sps8h4gy';
19 InParam7 := null;
20 InParam8 := null;
21 --OutParam := clob;
22
23 appdata.CREATE_TASK
24 (InParam1,InParam2,InParam3,InParam4,InParam5,InParam6,InParam7,InParam8,OutParam );
25 dbms_output.put_line(OutParam);
26
27 END;
28 /
GENERAL INFORMATION SECTION
------------------------------------------------------------------------
Tuning Task Name : Tune_ba155sps8h4gy
Tuning Task Owner :
CONSULTANT
Tuning Task ID : 4483
Workload Type : Single SQL
Statement
Execution Count : 1
Current Execution : EXEC_5053
Execution Type
: TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 30
Completion
Status : COMPLETED
Started at : 01/29/2018 09:37:24
Completed at
: 01/29/2018
09:37:25

----------------------------------------------------------------------
Schema Name: APPDATA
SQL ID : ba155sps8h4gy
SQL Text : select * from my_view

-----------------------------------------------------------------------
ERRORS SECTION
------------------------------------------------------------------------
- ORA-00942: table or view does not exist

-------------------------------------------------------------------------


PL/SQL procedure successfully completed.

SQL>
SQL>
Chris Saxon
January 29, 2018 - 4:27 pm UTC

Sorry, I don't think what you're trying to do is possible without granting consultant privileges on appdata's objects.

As you can see the in the report, the consultant owns the task:

Tuning Task Owner : 
CONSULTANT 

Try

Gh, January 29, 2018 - 6:45 pm UTC

Grant select To Consultant on
DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. 

Maybe by holly spirit it will work
Connor McDonald
January 30, 2018 - 2:35 am UTC

Well that the silliest thing I've read today :-)

Ahmad, January 30, 2018 - 11:29 am UTC

Hello Chris

Thank you for following up on this issue

No surrender ;)

I have found a way to accomplish this and Need your confirmation whether will be successful.

We will basically create a scheduled Job (DBMS_SCHEDULER) under a priveligied User and create a table that will hold the SQLs, Plan-Hash etc.

A "After-Insert" Trigger will be created on the table and will fire the execution of the Job.

The Consultant User will just do an insert into the table, the Trigger will execute the scheduled Job and insert the Outcomes into a Result table.

I am 99% sure that the Job will run with the privs of the Priveligied user and will be run in a Background session.

What do you think?
Chris Saxon
January 30, 2018 - 4:12 pm UTC

It may work, but I'm not convinced. What happens when you try?

Ahmad, January 30, 2018 - 11:36 am UTC

Hello Connor


Hahha, yeah i believe in Oracle holly Spirits ;)

Thx for the tip, i will try it and get back to you.

But why something like that is silly according to you :)

Best regards

Chris Saxon
January 30, 2018 - 4:14 pm UTC

Please tell us you're not really trying to do this...

Ahmad, January 30, 2018 - 11:55 am UTC

@Connor,

CDB_LOCAL_ADMINAUTH$ is available >= 12c.

We would like to implement a Standard Solution that also compatible with lower versions < 12c.

Any idea?

Thx
Chris Saxon
January 30, 2018 - 4:18 pm UTC

Oh, you are...

Please don't do this. Weird stuff can happen when you tinker around in the sys $ tables. See MOS note Database Hang Due to Grant on SYS-owned Table (User$) (Doc ID 2254339.1) for an example:

SYS.USER$ is an internal, bootstrap table that is not meant to be queried by end users. Grants should not be executed against these types of SYS owned tables. By performing direct DDL on SYS.USER$, it causes an exclusive lock to be acquired on this object very early during execution of the grant. It breaks the internal locking sequence of Oracle because USER$ plays a key role in parsing any statement and the potential for this kind of deadlock to occur is very high.
Hence, it is highly recommended not to execute any DDL statements on the SYS-owned tables.

Ahmad, January 30, 2018 - 4:19 pm UTC

Hi Chris,

Well, i think an After-Insert Trigger is enough, no Need for scheduled Job.
The Trigger will call the custom Procedure and execute the Task with Definer Rights.

I will test this Approach tonight and tell you the result.

Let's Keep the fingers crossed :)


Btw, i dunno if the tip from "Gh" is relevant, especially after i saw Connor's comment on it.

Cheers
Chris Saxon
January 30, 2018 - 4:22 pm UTC

i dunno if the tip from "Gh" is relevant

read my previous comment...

Ahmad, January 31, 2018 - 9:09 am UTC

Hi,

Thx for the reply, i haven't done any of Gh's weird stuff.

I tested These two Solution approaches:

1- Trigger based solution (objects have been created under Appdata Schema Owner):

A table has been created under APPDATA Schema which will be populated by Consultant, a Trigger has been created which will run a custom procedure to create a Tuning Task.

Unfortunately, this Approach failed because Oracle is still executing the Task with consultant's right even the table and the Trigger are created in appdata Schema!

1- Job based solution (objects have been created under Appdata Schema Owner):

A table has been created under APPDATA Schema which will be populated by Consultant, a DBMS_SCHEDULER has been created with 1 Min repeat interval, it will run a custom procedure to create a Tuning Task.

This Approach worked fine, however i have two issues:

1- is it possible to create a Trigger that will start the DBMS_SCHEDULER after insert ? will it work?

2- Can you please give me an example how to create an event-based Job which runs after insert? i just want to reduce the executions of the Job, actually it runs every minutes.. i want that the Job runs only when a insert occurs.

Thx


Chris Saxon
January 31, 2018 - 11:00 am UTC

You can call run_job in a trigger:

create table t (
  x int
);
create table log (
  y date
);

begin
  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'jb', 
    job_type => 'PLSQL_BLOCK', 
    job_action => 'begin insert into log values (sysdate); end;'
  );
end;
/

create or replace trigger trg 
  before insert on t
begin
  dbms_scheduler.run_job('jb');
end;
/

insert into t values (1);

select * from log;

Y                      
31-JAN-2018 02:59:03   

insert into t values (2);

select * from log;

Y                      
31-JAN-2018 02:59:03   
31-JAN-2018 02:59:04  

Ahmad, January 31, 2018 - 12:39 pm UTC

Hi,

The trigger is not working! yours is before-insert, i changed it into after-insert but same result, trigger runs but doesnt start the scheduler job.



CREATE TABLE tasks
(
taskid NUMBER(12),
sql_id VARCHAR(24),
plan_hash_value NUMBER(24),
time_limit NUMBER(24),
TASK_SCOPE VARCHAR2(24),
task_name VARCHAR2(24),
description VARCHAR2(24),
snap_first NUMBER(24),
snap_last NUMBER(24),
status VARCHAR2(24)
);


CREATE TABLE TASKS_RESULT
(
TASKID NUMBER(12),
result CLOB,
ERROR_MESSAGE CLOB
);

grant select, insert,update, delete on tasks to consultant;

grant select, insert,update, delete on TASKS_RESULT to consultant;


CREATE OR REPLACE
PROCEDURE load_tasks
AS
stmt_task VARCHAR2(64);
err_code VARCHAR2(24);
err_msg VARCHAR2(4000);
output CLOB;
task_counter number(24);

BEGIN
FOR cr IN
( SELECT * FROM tasks WHERE status <>'EXECUTED' OR status IS NULL)
LOOP
IF cr.SNAP_FIRST IS NULL THEN
task_counter :=cr.taskid;

stmt_task :=dbms_sqltune.create_tuning_task( sql_id =>
cr.SQL_ID, plan_hash_value => cr.PLAN_HASH_VALUE, scope => cr.TASK_SCOPE,
time_limit => cr.TIME_LIMIT, task_name => cr.TASK_NAME, description =>
cr.description);
dbms_sqltune.execute_tuning_task(cr.task_name);

SELECT dbms_sqltune.report_tuning_task(cr.TASK_NAME, 'TEXT', 'ALL')
INTO output
from dual;

INSERT INTO TASKS_RESULT
( taskid, result
) VALUES
( cr.taskid, output
);

update tasks set status='EXECUTED' where taskid=cr.taskid;

elsif cr.snap_first is not null then
task_counter :=cr.taskid;

stmt_task :=dbms_sqltune.create_tuning_task ( begin_snap =>
cr.snap_first, end_snap => cr.snap_last, sql_id => cr.sql_id, scope =>
cr.TASK_SCOPE, time_limit => cr.time_limit, task_name => cr.task_name,
description => cr.description);

SELECT dbms_sqltune.report_tuning_task(cr.TASK_NAME, 'TEXT', 'ALL')
INTO output
FROM dual;

INSERT INTO TASKS_RESULT
( taskid, RESULT
) VALUES
( cr.taskid, output
);

update tasks set status='EXECUTED' where taskid=cr.taskid;

END IF;
end loop;

EXCEPTION
WHEN OTHERS THEN
err_code := SQLCODE;
err_msg := sqlerrm;


INSERT
INTO tasks_result
(
taskid,
ERROR_MESSAGE
)
VALUES
(
task_counter,
err_code
|| ':'
||err_msg
);
commit;

END;
/
show errors




BEGIN
DBMS_SCHEDULER.create_job ( job_name => 'APPADMIN.EXECUTE_TASKS',
job_type => 'STORED_PROCEDURE', job_action => 'load_tasks',
start_date => NULL, repeat_interval => null, enabled
=> TRUE, auto_drop => FALSE, comments => 'Job to execute ITOC Tuning
Tasks' );
END;
/



CREATE OR REPLACE TRIGGER tasks_trg
after insert on tasks
begin
dbms_scheduler.run_job('EXECUTE_TASKS');
end;
/




When i start the job manually with:

exec dbms_scheduler.run_job('EXECUTE_TASKS');

The job works susccessfuly and insert the result into the task_results table.


What is the problem with the trigger?

Thxx
Chris Saxon
January 31, 2018 - 2:55 pm UTC

What error do you get?

Ahmad

Ahmad, January 31, 2018 - 1:21 pm UTC

I think the trigger is firing but not executing the job because the trigger is firing before the transaction is commited (insert on master table!)


The logic here is that for each new inserted Task (sql-id,plan-hash etc) the job should gets executed.

the job process the data in the master table and create the tuning task.

any idead how to solve this dilemma?




Chris Saxon
January 31, 2018 - 2:59 pm UTC

Stop trying to build something overly complicated?

It sounds like you're trying to allow external people to run SQL tuning reports. Is the effort you've gone to really less than them asking you to run it and you give them the results?

Ahmad, January 31, 2018 - 4:09 pm UTC

Chris,


The trigger runs smoothly and doesnt produce any error.
it simply doesnt execute the scheduler job!

i guess because it gets triggered too early!
Data is still not yet committed /visible to other session!!! The scheduler's job needs to process/check the content of the master table and create the relevant tuning tasks.



DBMS_SCHEDULER

Shimmy Kocherry, January 31, 2018 - 6:35 pm UTC

Just my two cents to the long reviews ...:)
Can we user DBMS_SCHEDULER in a TRIGGER? I thought DBMS_SCHEDULER does auto commit?

Chris Saxon
February 01, 2018 - 4:33 pm UTC

The scheduler commit is on create_job. Not run_job

Read thé Oracle documentation about this new 12 c

Gh, January 31, 2018 - 6:42 pm UTC

dear Masters . Gh is pretty aware of security that your are talking about. I wanted Ahmed to check if it works and he could revoke back if he wants.

After all I invite you to read from the documentation :

SELECT ANY DICTIONARY Privilege No Longer Accesses Some SYS Data Dictionary Tables

For better security, the SELECT ANY DICTIONARY system privilege no longer permits you to query the SYS schema system tables DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. Only user SYS has access to these tables, but user SYS can grant object privileges (such as GRANT SELECT ON USER$ TO sec_admin) to other users.



So if your documentation says
" ..but user SYS can grant object privileges (such as GRANT SELECT ON USER$ TO sec_admin) to other users."

That wasn't me who invent it. Also this is a very limited task asked by Ahmed for tuning and could be thrown away anytime.

Bests great Masters.

Chris Saxon
February 01, 2018 - 4:28 pm UTC

Just because you can do something doesn't mean you should!

Ahmad, January 31, 2018 - 8:36 pm UTC

Gh,

The user misses rights on the application's tables not on data dictionary views.

The trigger won't solve the problem because the issue is that we have a table (i call it master table) that gets updated regularly by external user (who misses the rights on app data), the trigger will try to call the DBMS_SCHEDULER job that itself will check the new added data in the master table, the data/ transaction meanwhile is not yet committed, that makes the DBMS_SCHEDULER will see no data in the table.






A reader, February 01, 2018 - 11:29 am UTC

Chris,

In order to start the Job from trigger, we need to pass input arguments to the the job.

so we need a DBMS_scheduler job that accepts arguments.

I have created an example, however I am defining the arguments dynamically inside the trigger (sort of for each row)

everything looked fine except the trigger which will start the job, the trigger at compilation is throwing :
PLS-00049: bad bind variable 'NEW.TASK_ID'


SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE
2 PROCEDURE load_itoc_tasks (TASK_ID IN NUMBER,
3 SQL_ID IN VARCHAR2,
4 PLAN_HASH_VALUE IN NUMBER,
5 TIME_LIMIT IN NUMBER,
6 TASK_NAME IN VARCHAR2,
7 DESCRIPTION IN VARCHAR2,
8 SNAP_FIRST IN NUMBER,
9 SNAP_LAST IN NUMBER,
10 TO_BE_DELETED IN VARCHAR2)
11 AS
12 stmt_task VARCHAR2(64);
13 err_code VARCHAR2(24);
14 err_msg VARCHAR2(4000);
15 output CLOB;
16
17 BEGIN
18
19 IF SNAP_FIRST IS NULL THEN
20
21 stmt_task:=dbms_sqltune.create_tuning_task( SQL_ID => SQL_ID,
22 PLAN_HASH_VALUE => PLAN_HASH_VALUE,
23 SCOPE => 'COMPREHENSIVE',
24 TIME_LIMIT => TIME_LIMIT,
25 TASK_NAME => TASK_NAME,
26 DESCRIPTION => DESCRIPTION);
27
28 dbms_sqltune.execute_tuning_task(TASK_NAME);
29
30 SELECT dbms_sqltune.report_tuning_task(TASK_NAME, 'TEXT', 'ALL')
31 INTO output
32 from dual;
33
34 INSERT INTO ITOC_TASKS_RESULT
35 ( taskid, result
36 ) VALUES
37 (TASK_ID, output
38 );
39
40
41
42 elsif snap_first is not null then
43
44 stmt_task:=dbms_sqltune.create_tuning_task ( begin_snap => snap_first,
45 end_snap => snap_last,
46 sql_id => sql_id,
47 scope => 'COMPREHENSIVE',
48 time_limit => time_limit,
49 task_name => task_name,
50 description => description);
51
52 SELECT dbms_sqltune.report_tuning_task(TASK_NAME, 'TEXT', 'ALL')
53 INTO output
54 FROM dual;
55
56 INSERT INTO ITOC_TASKS_RESULT
57 ( taskid, RESULT
58 ) VALUES
59 ( TASK_ID, output
60 );
61
62
63
64 ELSIF to_be_deleted ='Y' THEN
65
66 dbms_sqltune.drop_tuning_task(task_name);
67
68 delete from itoc_tasks where taskid=task_id;
69
70 END IF;
71
72 commit;
73
74 EXCEPTION
75 WHEN OTHERS THEN
76 err_code := SQLCODE;
77 err_msg := sqlerrm;
78
79
80 INSERT
81 INTO itoc_tasks_result
82 (
83 taskid,
84 ERROR_MESSAGE
85 )
86 VALUES
87 (
88 task_id,
89 err_code
90 || ':'
91 ||err_msg
92 );
93 commit;
94
95 END;
96 /

Procedure created.

SQL> show errors
No errors.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> BEGIN
2 DBMS_SCHEDULER.CREATE_PROGRAM (
3 PROGRAM_NAME => 'EXECUTE_ITOC_TASKS_PROG',
4 PROGRAM_TYPE => 'STORED_PROCEDURE',
5 PROGRAM_ACTION => 'LOAD_ITOC_TASKS',
6 NUMBER_OF_ARGUMENTS => 9,
7 ENABLED => FALSE,
8 COMMENTS => 'Job To execute ITOC Tuning Tasks'
9 );
10 END;
11 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> BEGIN
2 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
3 PROGRAM_NAME => 'EXECUTE_ITOC_TASKS_PROG',
4 ARGUMENT_NAME => 'TASK_ID',
5 ARGUMENT_POSITION => 1,
6 ARGUMENT_TYPE => 'VARCHAR2');
7 END;
8 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
3 PROGRAM_NAME => 'EXECUTE_ITOC_TASKS_PROG',
4 ARGUMENT_NAME => 'SQL_ID',
5 ARGUMENT_POSITION => 2,
6 ARGUMENT_TYPE => 'VARCHAR2');
7 END;
8 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
3 PROGRAM_NAME => 'EXECUTE_ITOC_TASKS_PROG',
4 ARGUMENT_NAME => 'PLAN_HASH_VALUE',
5 ARGUMENT_POSITION => 3,
6 ARGUMENT_TYPE => 'NUMBER');
7 END;
8 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
3 PROGRAM_NAME => 'EXECUTE_ITOC_TASKS_PROG',
4 ARGUMENT_NAME => 'TIME_LIMIT',
5 ARGUMENT_POSITION => 4,
6 ARGUMENT_TYPE => 'NUMBER');
7 END;
8 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
3 PROGRAM_NAME => 'EXECUTE_ITOC_TASKS_PROG',
4 ARGUMENT_NAME => 'TASK_NAME',
5 ARGUMENT_POSITION => 5,
6 ARGUMENT_TYPE => 'VARCHAR2');
7 END;
8 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
3 PROGRAM_NAME => 'EXECUTE_ITOC_TASKS_PROG',
4 ARGUMENT_NAME => 'DESCRIPTION',
5 ARGUMENT_POSITION => 6,
6 ARGUMENT_TYPE => 'VARCHAR2');
7 END;
8 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
3 PROGRAM_NAME => 'EXECUTE_ITOC_TASKS_PROG',
4 ARGUMENT_NAME => 'SNAP_FIRST',
5 ARGUMENT_POSITION => 7,
6 ARGUMENT_TYPE => 'NUMBER');
7 END;
8 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
3 PROGRAM_NAME => 'EXECUTE_ITOC_TASKS_PROG',
4 ARGUMENT_NAME => 'SNAP_LAST',
5 ARGUMENT_POSITION => 8,
6 ARGUMENT_TYPE => 'NUMBER');
7 END;
8 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
3 PROGRAM_NAME => 'EXECUTE_ITOC_TASKS_PROG',
4 ARGUMENT_NAME => 'TO_BE_DELETED',
5 ARGUMENT_POSITION => 9,
6 ARGUMENT_TYPE => 'VARCHAR2');
7 END;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL>
SQL> BEGIN
2 DBMS_SCHEDULER.CREATE_JOB (
3 JOB_NAME => 'EXECUTE_ITOC_TASKS',
4 PROGRAM_NAME => 'EXECUTE_ITOC_TASKS_PROG',
5 START_DATE => NULL,
6 REPEAT_INTERVAL => NULL,
7 END_DATE => NULL,
8 ENABLED => FALSE,
9 AUTO_DROP => FALSE,
10 COMMENTS => 'Job To execute ITOC Tuning Tasks'
11 );
12 END;
13 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER ITOC_tasks_trg
2 before insert or update on itoc_tasks
3 referencing new as new old as old
4 for each row
5 begin
6 dbms_scheduler.set_job_argument_value(job_name => 'EXECUTE_ITOC_TASKS',argument_position=>1,argument_value=>:new.TASK_ID );
7 dbms_scheduler.set_job_argument_value(job_name => 'EXECUTE_ITOC_TASKS',argument_position=>2,argument_value=>:new.SQL_ID );
8 dbms_scheduler.set_job_argument_value(job_name => 'EXECUTE_ITOC_TASKS',argument_position=>3,argument_value=>:new.PLAN_HASH_VALUE );
9 dbms_scheduler.set_job_argument_value(job_name => 'EXECUTE_ITOC_TASKS',argument_position=>4,argument_value=>:new.TIME_LIMIT);
10 dbms_scheduler.set_job_argument_value(job_name => 'EXECUTE_ITOC_TASKS',argument_position=>5,argument_value=>:new.TASK_NAME);
11 dbms_scheduler.set_job_argument_value(job_name => 'EXECUTE_ITOC_TASKS',argument_position=>6,argument_value=>:new.DESCRIPTION );
12 dbms_scheduler.set_job_argument_value(job_name => 'EXECUTE_ITOC_TASKS',argument_position=>7,argument_value=>:new.SNAP_FIRST );
13 dbms_scheduler.set_job_argument_value(job_name => 'EXECUTE_ITOC_TASKS',argument_position=>8,argument_value=>:new.SNAP_LAST );
14 dbms_scheduler.set_job_argument_value(job_name => 'EXECUTE_ITOC_TASKS',argument_position=>9,argument_value=>:new.TO_BE_DELETED );
15
16 dbms_scheduler.ENABLED('EXECUTE_ITOC_TASKS_PROG');
17 dbms_scheduler.ENABLED('EXECUTE_ITOC_TASKS');
18 dbms_scheduler.RUN_JOB('EXECUTE_ITOC_TASKS');
19 end;
20 /

Warning: Trigger created with compilation errors.

SQL> show errors
Errors for TRIGGER ITOC_TASKS_TRG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/109 PLS-00049: bad bind variable 'NEW.TASK_ID'





What's wrong with this code?

is it possible to pass arguments to DBMS_SCHEDULER from a trigger?

Thxx
Chris Saxon
February 01, 2018 - 4:24 pm UTC

This compiles fine for me...

CREATE TABLE tasks 
( 
task_id NUMBER(12), 
sql_id VARCHAR(24), 
plan_hash_value NUMBER(24), 
time_limit NUMBER(24), 
TASK_SCOPE VARCHAR2(24), 
task_name VARCHAR2(24), 
description VARCHAR2(24), 
snap_first NUMBER(24), 
snap_last NUMBER(24), 
status VARCHAR2(24) 
); 

CREATE OR REPLACE TRIGGER ITOC_tasks_trg 
  before insert or update on tasks 
  referencing new as new old as old 
  for each row 
  begin 
  dbms_scheduler.set_job_argument_value(job_name => 'EXECUTE_ITOC_TASKS',argument_position=>1,argument_value=>:new.TASK_ID ); 
  dbms_scheduler.set_job_argument_value(job_name => 'EXECUTE_ITOC_TASKS',argument_position=>2,argument_value=>:new.SQL_ID ); 
  dbms_scheduler.set_job_argument_value(job_name => 'EXECUTE_ITOC_TASKS',argument_position=>3,argument_value=>:new.PLAN_HASH_VALUE ); 
  dbms_scheduler.set_job_argument_value(job_name => 'EXECUTE_ITOC_TASKS',argument_position=>4,argument_value=>:new.TIME_LIMIT); 
  dbms_scheduler.set_job_argument_value(job_name => 'EXECUTE_ITOC_TASKS',argument_position=>5,argument_value=>:new.TASK_NAME); 
  dbms_scheduler.set_job_argument_value(job_name => 'EXECUTE_ITOC_TASKS',argument_position=>6,argument_value=>:new.DESCRIPTION ); 
  dbms_scheduler.set_job_argument_value(job_name => 'EXECUTE_ITOC_TASKS',argument_position=>7,argument_value=>:new.SNAP_FIRST ); 
  dbms_scheduler.set_job_argument_value(job_name => 'EXECUTE_ITOC_TASKS',argument_position=>8,argument_value=>:new.SNAP_LAST ); 
  
  dbms_scheduler.ENABLE('EXECUTE_ITOC_TASKS_PROG'); 
  dbms_scheduler.ENABLE('EXECUTE_ITOC_TASKS'); 
  dbms_scheduler.RUN_JOB('EXECUTE_ITOC_TASKS'); 
  end; 
/ 

select STATUS from user_objects
where  object_name = 'ITOC_TASKS_TRG';

STATUS   
VALID 

Ahmad, February 02, 2018 - 8:45 am UTC

Hi,


No way with the trigger!
DBMS_SCHEDULER is committing something in background, the trigger is complaining about the commits!!!



SQL> INSERT INTO APPDATA.TASKS VALUES
2 (
3 1,
4 '4j640nxswh0tg',
5 497311279,30,
6 'COMPREHENSIVE',
7 'Tune_4j640nxswh0tg',
8 'Tune 4j640nxswh0tg',
9 NULL,
10 NULL,
11 NULL,
12 NULL
13 );
INSERT INTO APPDATA.TASKS VALUES
*
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SYS.DBMS_ISCHED", line 278
ORA-06512: at "SYS.DBMS_SCHEDULER", line 819
ORA-06512: at "APPDATA.TASKS_TRG", line 2
ORA-04088: error during execution of trigger 'APPDATA.TASKS_TRG'


SQL>
Chris Saxon
February 02, 2018 - 11:21 am UTC

Well line 72 of procedure load_itoc_tasks does have:

commit; 


It's possible other dbms calls you make commit too...

A reader, February 02, 2018 - 12:52 pm UTC

Well, i have removed the two commits in procedure load_itoc_tasks.

it doesnt want to run successfuly, i got the same problem

ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SYS.DBMS_ISCHED", line 278
ORA-06512: at "SYS.DBMS_SCHEDULER", line 819
ORA-06512: at "APPDATA.TASKS_TRG", line 2
ORA-04088: error during execution of trigger 'APPDATA.TASKS_TRG'


I have no control on the rest of commits from other DBMS Calls!!!



Chris Saxon
February 05, 2018 - 10:35 am UTC

I've not checked, but there may be commits in dbms_sqltune...

A reader, February 02, 2018 - 1:00 pm UTC

I have tested another option and it worked:

Trigger calls a DBMS_SCHEDULER job that just call DBMS_OUTPUT.PUT_LINE('Table has been updated!');

Then i create another DBMS_SCHEDULER job that gets triggered by the Dummy job (DBMS_SCHEDULER.JOB_SUCCEEDED Attribute).

This appraoch worked fine BUT the issue is that we will have two jobs!

i want to make the solution simpler, do you know how can i use only one job that gets triggered by DML on the master table?


so it's sort of event based job that gets triggered immediately after DML/commit runs on the master table.

Chris Saxon
February 05, 2018 - 10:39 am UTC

Do you really need this to happen when you run the insert?

Another way of doing this is:

Have a job that runs regularly, e.g. every minute. This reads any unprocessed rows in your tasks table and runs the tuning advisor on them. Then dumps the results in a table.

So all you need to do is insert the row. OK, you'll have to wait a minute or two before the database picks up the task. But SQL tuning tasks could run for several minutes anyway. So it's not like you're sacrificing instant results.

Ahmad, February 06, 2018 - 2:03 pm UTC

Hmmm,

Letting the scheduler runs the Job is an Option but not resource-friendly ;)

Reason is there will be times,days even monthes where the source table is empty, the scheduler will keep running the Job.

I have managed to get the job runs "on demand" (after insert) but i have needed two Jobs (the final Job is triggered after the successful run of the first).

I am wondering if i can achieve thi with only one Job?




Chris Saxon
February 06, 2018 - 4:20 pm UTC

Letting the scheduler runs the Job is an Option but not resource-friendly ;)

Why? If there are no pending tasks, it won't do anything. Right? So what's the problem?

Ahmad, February 08, 2018 - 1:28 pm UTC

Hmm, well in "ideal circumstances" the DB should do his task only when needed!

At the same time i have interest in acquiring skills in developping Event-Based Job that gets triggered after DML Operation on a specified Table..

Cheers



Chris Saxon
February 08, 2018 - 1:35 pm UTC

If you're looking for event-based actions then Advanced Queueing is a better way to go

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.