No question at this time
DBA Top 10
1 A. Kavsek 11100
2 M. Cadot 7600
3 B. Vroman 5100
4 P. Wisse 5000
5 T. Boles 2000
5 . Lauri 2000
7 J. Péran 1100
8 J. Schnackenberg 700
9 R. Pattyn 600
10 T. P 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48435
Total active users1504
Act. users last 24h5
Act. users last hour0
Registered user hits last week139
Registered user hits last month778
Go up

system trigger failed
Next thread: Data Migration
Prev thread: recreate pdb

Message Score Author Date
Hi master, i have created one trigger which wil...... javed akhtar Sep 19, 2019, 08:32
<pre>ORA-30511: invalid DDL operation in system ...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Michel Cadot Sep 19, 2019, 09:32
Hello Javed, the following might be interesting...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Sep 19, 2019, 16:30

Follow up by mail Click here


Subject: system trigger failed
Author: javed akhtar, India
Date: Sep 19, 2019, 08:32, 26 days ago
Os info: linux
Oracle info: 12
Message: Hi master,

i have created one trigger which will monitor the new created table in test1 and will grant select on newly created table to test2.

SQL> CREATE OR REPLACE TRIGGER create_table_trigger
AFTER CREATE ON SCHEMA
BEGIN
IF SYS.DICTIONARY_OBJ_TYPE = 'TABLE' THEN
FOR t IN (SELECT * FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || t.table_name || ' TO test2';
END LOOP;
END IF;
END; 2 3 4 5 6 7 8 9 10
11 /

Trigger created.

SQL>
SQL>
SQL>
SQL> show user
USER is "TEST1"
SQL> create table jjk(id number);
create table jjk(id number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 5
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: system trigger failed
Author: Michel Cadot, France
Date: Sep 19, 2019, 09:32, 26 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message:
ORA-30511: invalid DDL operation in system triggers

*Cause: An attempt was made to perform an invalid DDL operation
in a system trigger. Most DDL operations currently are not
supported in system triggers. The only currently supported DDL
operations are table operations and ALTER?COMPILE operations.
*Action: Remove invalid DDL operations in system triggers.


Create a procedure to execute your loop and a job that will be executed after the DDL has been completed, something like:
create or replace procedure prc_grant is

begin
FOR t IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || t.table_name || ' TO test2';
END LOOP;
end;
/


and in your trigger replace the loop by:
dbms_job.submit(jobno, 'prc_grant;');



Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: system trigger failed
Author: Bruno Vroman, Belgium
Date: Sep 19, 2019, 16:30, 26 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello Javed,

the following might be interesting for you:
  https://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=5888
(script about "Automatic near-real time maintenance of privileges")

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here