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
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.
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
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>
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
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?
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
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
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
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
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
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?
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?
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.
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
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>
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!!!
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.
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?
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
February 08, 2018 - 1:35 pm UTC
If you're looking for event-based actions then Advanced Queueing is a better way to go