No question at this time
DBA Top 10
1 A. Kavsek 10000
2 M. Cadot 9900
3 B. Vroman 5700
4 P. Wisse 4300
5 J. Péran 1800
6 . Lauri 1200
7 J. Schnackenberg 600
8 F. Pachot 500
9 Z. Hudec 400
9 G. Lambregts 400
9 N. Havard 400
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48503
Total active users1429
Act. users last 24h2
Act. users last hour0
Registered user hits last week104
Registered user hits last month599
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: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts Michel Cadot Sep 19, 2019, 09:32
Hello Javed, the following might be interesting...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 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, 252 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, 252 days ago
Score:   Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 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, 252 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 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