Home » SQL & PL/SQL » SQL & PL/SQL » trigger executing a procedure to execute dbms_stats.gather_table_stats (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
trigger executing a procedure to execute dbms_stats.gather_table_stats [message #686504] |
Wed, 28 September 2022 15:28 |
wtolentino
Messages: 413 Registered: March 2005
|
Senior Member |
|
|
i had this code that when i inserted a row on a table it will fire a trigger to execute a procedure that will eventually execute the oracle built-in package dbms_stats.gather_table_stats. though there is no commit but i know that there is implicit commit is being done thru that dbms_stats.gather_table_stats package. how do i go around about this? any advise or help will be appreciated. thank you.
SQL> create table test_load_tab
2 (table_owner varchar2(40), table_name varchar2(40), load_status varchar2(10), comments varchar2(80));
Table created.
SQL> create or replace procedure test_load_pro (pTableOwner varchar2, pTableName varchar2) as
2 begin
3 dbms_stats.gather_table_stats (ownname => pTableOwner,
4 tabname => pTableName,
5 cascade => TRUE,
6 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
7 method_opt => 'FOR ALL COLUMNS SIZE AUTO',
8 granularity => 'ALL',
9 degree => 2);
10
11 update test_load_tab
12 set load_status = 'Completed'
13 where table_owner = pTableOwner
14 and table_name = pTableName
15 and load_status = 'In-Process';
16
17 exception
18 when others then
19 dbms_output.put_line('gather stats on table partition '||pTableName||' failed ');
20 dbms_output.put_line('error '||substr(sqlerrm, 1, 200));
21 end;
22 /
Procedure created.
SQL> create or replace trigger test_load_trg
2 before insert on test_load_tab for each row
3 begin
4 if inserting then
5 if (:new.table_owner is not null) and (:new.table_name is not null) and (:new.load_status = 'In-Process') then
6 test_load_pro (:new.table_owner, :new.table_name);
7 end if;
8 end if;
9 end;
10 /
Trigger created.
SQL> create table test_stat_tab
2 (stat_id number, attribute varchar2(40), create_date date);
Table created.
SQL>
SQL> insert into test_stat_tab values (1, 'A', sysdate);
1 row created.
SQL> insert into test_stat_tab values (2, 'B', sysdate);
1 row created.
SQL> insert into test_stat_tab values (3, 'C', sysdate);
1 row created.
SQL> insert into test_stat_tab values (4, 'D', sysdate);
1 row created.
SQL> insert into test_stat_tab values (5, 'E', sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> set serveroutput on;
SQL> insert into test_load_tab values ('WTOLENTINO','TEST_STAT_TAB','In-Process','Test 1');
gather stats on table partition TEST_STAT_TAB failed
error ORA-04092: cannot COMMIT in a trigger
1 row created.
SQL>
|
|
|
Re: trigger executing a procedure to execute dbms_stats.gather_table_stats [message #686505 is a reply to message #686504] |
Thu, 29 September 2022 05:56 |
|
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
A workaround can be declaring the procedure as autonomous transaction, which also means that it must commit (or rollback); it is then "isolated" from the main transaction. Something like this (see lines #4 and #21):
SQL> CREATE OR REPLACE PROCEDURE test_load_pro (pTableOwner VARCHAR2,
2 pTableName VARCHAR2)
3 AS
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 DBMS_STATS.gather_table_stats (
7 ownname => pTableOwner,
8 tabname => pTableName,
9 cascade => TRUE,
10 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
11 method_opt => 'FOR ALL COLUMNS SIZE AUTO',
12 granularity => 'ALL',
13 degree => 2);
14
15 UPDATE test_load_tab
16 SET load_status = 'Completed'
17 WHERE table_owner = pTableOwner
18 AND table_name = pTableName
19 AND load_status = 'In-Process';
20
21 COMMIT;
22 EXCEPTION
23 WHEN OTHERS
24 THEN
25 DBMS_OUTPUT.put_line (
26 'gather stats on table partition ' || pTableName || ' failed ');
27 DBMS_OUTPUT.put_line ('error ' || SUBSTR (SQLERRM, 1, 200));
28 END;
29 /
Procedure created.
Insert that previously failed now works:
SQL> show user
USER is "SCOTT"
SQL> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') right_now from dual;
RIGHT_NOW
-------------------
29.09.2022 12:55:17
SQL> insert into test_load_tab values ('SCOTT','TEST_STAT_TAB','In-Process','Test 1');
1 row created.
SQL> select * from test_load_tab;
TABLE_OWNER TABLE_NAME LOAD_STATUS COMMENTS
------------ --------------- ------------ --------------------
SCOTT TEST_STAT_TAB In-Process Test 1
SQL> select to_char(last_analyzed, 'dd.mm.yyyy hh24:mi:ss') last_analyzed
2 from user_tables
3 where table_name = 'TEST_STAT_TAB';
LAST_ANALYZED
-------------------
29.09.2022 12:55:32
SQL>
|
|
|
|
Re: trigger executing a procedure to execute dbms_stats.gather_table_stats [message #686507 is a reply to message #686506] |
Thu, 29 September 2022 12:26 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Both "solutions" are not correct, mainly in the consistency of the work.
First wtolentino's one (after creating the objects):
SQL> insert into test_load_tab values ('WTOLENTINO','TEST_STAT_TAB','In-Process','Test 1');
gather stats on table partition TEST_STAT_TAB failed
error ORA-04092: cannot COMMIT in a trigger
1 row created.
Ok there's the expected error but:
SQL> select * from test_load_tab;
TABLE_OWNER TABLE_NAME LOAD_STATU COMMENTS
------------ --------------- ---------- -------------
WTOLENTINO TEST_STAT_TAB In-Process Test 1
1 row selected.
In-process really? Should be "aborted"
Now Littlefoot's one:
SQL> rollback;
Rollback complete.
SQL> select * from test_load_tab;
no rows selected
SQL> CREATE OR REPLACE PROCEDURE test_load_pro (pTableOwner VARCHAR2,
2 pTableName VARCHAR2)
3 AS
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 DBMS_STATS.gather_table_stats (
7 ownname => pTableOwner,
8 tabname => pTableName,
9 cascade => TRUE,
10 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
11 method_opt => 'FOR ALL COLUMNS SIZE AUTO',
12 granularity => 'ALL',
13 degree => 2);
14
15 UPDATE test_load_tab
16 SET load_status = 'Completed'
17 WHERE table_owner = pTableOwner
18 AND table_name = pTableName
19 AND load_status = 'In-Process';
20
21 COMMIT;
22 EXCEPTION
23 WHEN OTHERS
24 THEN
25 DBMS_OUTPUT.put_line (
26 'gather stats on table partition ' || pTableName || ' failed ');
27 DBMS_OUTPUT.put_line ('error ' || SUBSTR (SQLERRM, 1, 200));
28 END;
29 /
Procedure created.
19:15:39 SQL> insert into test_load_tab values ('MICHEL','TEST_STAT_TAB','In-Process','Test 1');
1 row created.
19:15:47 SQL> commit;
Commit complete.
19:15:50 SQL> select to_char(last_analyzed, 'dd.mm.yyyy hh24:mi:ss') last_analyzed
19:15:54 2 from user_tables
19:15:54 3 where table_name = 'TEST_STAT_TAB';
LAST_ANALYZED
-------------------
29.09.2022 19:15:47
1 row selected.
19:15:54 SQL> select * from test_load_tab;
TABLE_OWNER TABLE_NAME LOAD_STATU COMMENTS
------------ --------------- ---------- ----------------------------------------------------------
MICHEL TEST_STAT_TAB In-Process Test 1
1 row selected.
Still "In-Process" when it is "Completed", the reason is that an AUTONOMOUS_TRANSACTION is another transaction and so can't see the new inserted line.
See slightly modifying the procedure:
SQL> select * from test_load_tab;
no rows selected
SQL> CREATE OR REPLACE PROCEDURE test_load_pro (pTableOwner VARCHAR2,
2 pTableName VARCHAR2)
3 AS
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 DBMS_STATS.gather_table_stats (
7 ownname => pTableOwner,
8 tabname => pTableName,
9 cascade => TRUE,
10 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
11 method_opt => 'FOR ALL COLUMNS SIZE AUTO',
12 granularity => 'ALL',
13 degree => 2);
14
15 UPDATE test_load_tab
16 SET load_status = 'Completed'
17 WHERE table_owner = pTableOwner
18 AND table_name = pTableName
19 AND load_status = 'In-Process';
20
21 dbms_output.put_line('>>> '||sql%rowcount||' rows updated');
22
23 COMMIT;
24 EXCEPTION
25 WHEN OTHERS
26 THEN
27 DBMS_OUTPUT.put_line (
28 'gather stats on table partition ' || pTableName || ' failed ');
29 DBMS_OUTPUT.put_line ('error ' || SUBSTR (SQLERRM, 1, 200));
30 END;
31 /
Procedure created.
SQL> insert into test_load_tab values ('MICHEL','TEST_STAT_TAB','In-Process','Test 1');
>>> 0 rows updated
1 row created.
SQL> select * from test_load_tab;
TABLE_OWNER TABLE_NAME LOAD_STATU COMMENTS
------------ --------------- ---------- ----------------------------------------------------
MICHEL TEST_STAT_TAB In-Process Test 1
1 row selected.
And still the same issue if an error occurs:
SQL> rollback;
Rollback complete.
SQL> select * from test_load_tab;
no rows selected
SQL> insert into test_load_tab values ('SCOTT','TEST_STAT_TAB','In-Process','Test 1');
gather stats on table partition TEST_STAT_TAB failed
error ORA-20000: Unable to analyze TABLE "SCOTT"."TEST_STAT_TAB", insufficient privileges or does not exist
1 row created.
SQL> select * from test_load_tab;
TABLE_OWNER TABLE_NAME LOAD_STATU COMMENTS
------------ --------------- ---------- ----------------------------------------------------------------------
SCOTT TEST_STAT_TAB In-Process Test 1
1 row selected.
Once more, read WHEN_OTHERS.
It should not be used but in very specific cases and in these cases it should always end with "RAISE;".
|
|
|
Re: trigger executing a procedure to execute dbms_stats.gather_table_stats [message #686508 is a reply to message #686507] |
Thu, 29 September 2022 12:38 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
So one correct way to do is:
SQL> CREATE OR REPLACE PROCEDURE test_load_pro (pTableOwner VARCHAR2,
2 pTableName VARCHAR2)
3 AS
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 DBMS_STATS.gather_table_stats (
7 ownname => pTableOwner,
8 tabname => pTableName,
9 cascade => TRUE,
10 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
11 method_opt => 'FOR ALL COLUMNS SIZE AUTO',
12 granularity => 'ALL',
13 degree => 2);
14 COMMIT;
15 END;
16 /
Procedure created.
SQL> create or replace trigger test_load_trg
2 before insert on test_load_tab for each row
3 begin
4 if inserting then
5 if (:new.table_owner is not null) and (:new.table_name is not null) and (:new.load_status = 'In-Process') then
6 test_load_pro (:new.table_owner, :new.table_name);
7 :new.load_status := 'Completed';
8 end if;
9 end if;
10 end;
11 /
Trigger created.
SQL> select * from test_load_tab;
no rows selected
SQL> set time on
19:32:09 SQL> insert into test_load_tab values ('MICHEL','TEST_STAT_TAB','In-Process','Test 1');
1 row created.
19:32:11 SQL> select * from test_load_tab;
TABLE_OWNER TABLE_NAME LOAD_STATU COMMENTS
------------ --------------- ---------- ----------------------------------------------------------
MICHEL TEST_STAT_TAB Completed Test 1
1 row selected.
19:32:18 SQL> select to_char(last_analyzed, 'dd.mm.yyyy hh24:mi:ss') last_analyzed
19:32:25 2 from user_tables
19:32:25 3 where table_name = 'TEST_STAT_TAB';
LAST_ANALYZED
-------------------
29.09.2022 19:32:11
1 row selected.
And you are sure that the row that is updated to "Completed" is the current one and not a previous one that was in the table as with the UPDATE statement.
And in case of error:
SQL> rollback;
Rollback complete.
SQL> select * from test_load_tab;
no rows selected
SQL> insert into test_load_tab values ('SCOTT','TEST_STAT_TAB','In-Process','Test 1');
insert into test_load_tab values ('SCOTT','TEST_STAT_TAB','In-Process','Test 1')
*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SCOTT"."TEST_STAT_TAB", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 24682
ORA-06512: at "SYS.DBMS_STATS", line 24751
ORA-06512: at "MICHEL.TEST_LOAD_PRO", line 6
ORA-06512: at "MICHEL.TEST_LOAD_TRG", line 4
ORA-04088: error during execution of trigger 'MICHEL.TEST_LOAD_TRG'
SQL> select * from test_load_tab;
no rows selected
The row is not inserted and so no false information is given on the work (no "In-Process" when nothing is in process).
No need of EXCEPTION block, you have the same information in standard Oracle error (and much more).
[Updated on: Fri, 30 September 2022 01:54] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 22:11:38 CDT 2024
|