Debug DDL trigger 2005-08-24 - By rjamya
Not true, see below for an example on 9204
SQL> get t1.sql
1 conn t1/t1
2 create table t1.some_tbl tablespace cons
3 as select * from dba_objects where rownum < 100
4 /
5 drop trigger raj_test_trig
6 /
7 alter session set events '10046 trace name context forever, level 4'
8 /
9 CREATE OR REPLACE TRIGGER raj_test_trig
10 BEFORE DDL ON SCHEMA
11 DECLARE
12 PRAGMA AUTONOMOUS_TRANSACTION;
13 v_datestamp DATE := sysdate;
14 v_ddl_stmt VARCHAR2(4000);
15 v_ora_error NUMBER;
16 v_table_count PLS_INTEGER := 1;
17 BEGIN
18 dbms_output.enable(1000000);
19 dbms_output.put_line('Trigger: checking for table');
20 dbms_output.put_line('Trigger: sysevent is ' || ora_sysevent);
21 if ora_sysevent = 'DROP' then
22 SELECT COUNT(*) INTO v_table_count
23 FROM user_tables
24 WHERE table_name = ora_dict_obj_name;
25 dbms_output.put_line('Trigger: table count is ' || v_table_count);
26 IF v_table_count > 0 THEN
27 v_ddl_stmt := 'CREATE TABLE t1.'||ora_dict_obj_name||'_bkp AS SELECT *
FROM t1.'||ora_dict_obj_name;
28 dbms_output.put_line('Trigger cmd: ' || v_ddl_stmt);
29 EXECUTE IMMEDIATE v_ddl_stmt;
30 END IF;
31 END IF;
32 END;
33 /
34 set serveroutput on
35 drop table some_tbl;
36* select table_name from user_tables;
SQL> @(protected)
Connected.
create table t1.some_tbl tablespace cons
*
ERROR at line 1:
ORA-00955 (See ORA-00955.ora-code.com): name is already used by an existing object
Trigger dropped.
Session altered.
Trigger created.
drop table some_tbl
*
ERROR at line 1:
ORA-00604 (See ORA-00604.ora-code.com): error occurred at recursive SQL level 1
ORA-06519 (See ORA-06519.ora-code.com): active autonomous transaction detected and rolled back
ORA-06512 (See ORA-06512.ora-code.com): at line 19
TABLE_NAME
-- ---- ---- ---- ---- ---- --
SOME_TBL
SQL> connect / as sysdba
Connected.
SQL> drop table t1.some_tbl;
Table dropped.
Raj
On 8/24/05, Jared Still <jkstill@(protected)> wrote:
>
> I believe the triggers that Raj was thinking of were logon triggers.
>
> Those do not fire for accounts with the DBA privilege.
>
> Jared
>
> On 8/23/05, Wolfgang Breitling <breitliw@(protected)> wrote:
> >
> > At 08:08 AM 8/23/2005, rjamya wrote:
> > >psst: for all this to succeed, you should be a non-dba user ...
> > >these triggers don't fire for people with DBA privs.
> >
> > Not quite correct. They don't fire for sys, but they DO fire for
> > ordinary users, even those with the DBA role.
> >
> >
> > Regards
> >
> > Wolfgang Breitling
> > Centrex Consulting Corporation
> > http://www.centrexcc.com
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
>
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>
>
--
-- ---- ---- ---- ---- ---- --
select standard_disclaimer from company_requirements where category =
'MANDATORY';
Not true, see below for an example on 9204<br>
<br>
SQL> get t1.sql<br>
1 conn t1/t1<br>
2 create table t1.some_tbl tablespace cons<br>
3 as select * from dba_objects where rownum < 100<br>
4 /<br>
5 drop trigger raj_test_trig<br>
6 /<br>
7 alter session set events '10046 trace name context forever,
level 4'<br>
8 /<br>
9 CREATE OR REPLACE TRIGGER raj_test_trig<br>
10 BEFORE DDL ON SCHEMA<br>
11 DECLARE<br>
12 PRAGMA AUTONOMOUS_TRANSACTION;<br>
13 v_datestamp  
; DATE := sysdate;<br>
14 v_ddl_stmt
VARCHAR2(4000);<br>
15 v_ora_error  
; NUMBER;<br>
16 v_table_count
PLS_INTEGER := 1;<br>
17 BEGIN<br>
18 dbms_output.enable(1000000);<br>
19 dbms_output.put_line('Trigger: checking for
table');<br>
20 dbms_output.put_line('Trigger: sysevent is ' |
| ora_sysevent);<br>
21 if ora_sysevent = 'DROP' then<br>
22 SELECT COUNT(*) INTO v_table_count<br>
23 FROM user_tables<br>
24 WHERE table_name = ora_dict_obj_name;<br>
25 dbms_output.put_line('Trigger: table count is
' || v_table_count);<br>
26 IF v_table_count > 0 THEN<br>
27 v_ddl_stmt :=
'CREATE TABLE t1.'||ora_dict_obj_name||'_bkp AS SELECT * FROM
t1.'||ora_dict_obj_name;<br>
28 dbms_output.put_line(
'Trigger cmd: ' || v_ddl_stmt);<br>
29 EXECUTE IMMEDIATE v_ddl_stmt
;<br>
30 END IF;<br>
31 END IF;<br>
32 END;<br>
33 /<br>
34 set serveroutput on<br>
35 drop table some_tbl;<br>
36* select table_name from user_tables;<br>
<br>
SQL> @(protected)<br>
Connected.<br>
create table t1.some_tbl tablespace cons<br>
*<br>
ERROR at line 1:<br>
ORA-00955 (See ORA-00955.ora-code.com): name is already used by an existing object<br>
<br>
<br>
<br>
Trigger dropped.<br>
<br>
<br>
Session altered.<br>
<br>
<br>
Trigger created.<br>
<br>
drop table some_tbl<br>
*<br>
ERROR at line 1:<br>
ORA-00604 (See ORA-00604.ora-code.com): error occurred at recursive SQL level 1<br>
ORA-06519 (See ORA-06519.ora-code.com): active autonomous transaction detected and rolled back<br>
ORA-06512 (See ORA-06512.ora-code.com): at line 19<br>
<br>
TABLE_NAME<br>
-- ---- ---- ---- ---- ---- --<br>
SOME_TBL<br>
<br>
SQL> connect / as sysdba<br>
Connected.<br>
SQL> drop table t1.some_tbl;<br>
<br>
Table dropped.<br>
<br>
<br>
Raj<br><br><div><span class="gmail_quote">On 8/24/05, <b class="gmail
_sendername">Jared Still</b> <<a href="mailto:jkstill@(protected)">jkstill
@(protected)</a>> wrote:</span><blockquote class="gmail_quote" style="border
-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left:
1ex;">
I believe the triggers that Raj was thinking of were logon triggers.<br>
<br>
Those do not fire for accounts with the DBA privilege.<br>
<br>
Jared<br><br><div><span class="gmail_quote">On 8/23/05, <b class="gmail
_sendername">Wolfgang Breitling</b> <<a href="mailto:breitliw@(protected)"
target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
breitliw@(protected)</a>> wrote:</span><blockquote class="gmail_quote"
style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex;
padding-left: 1ex;">
At 08:08 AM 8/23/2005, rjamya wrote:<br>>psst: for all this to succeed, you
should be a non-dba user ...<br>>these triggers don't fire for people with
DBA privs.<br><br>Not quite correct. They don't fire for sys, but they DO fire
for
<br>ordinary users, even those with the DBA role.<br><br><br>Regards<br><br
>Wolfgang Breitling<br>Centrex Consulting Corporation<br><a href="http://www
.centrexcc.com" target="_blank" onclick="return top.js.OpenExtLink(window,event
,this)">
http://www.centrexcc.com</a><br><br>--<br><a href="http://www.freelists.org
/webpage/oracle-l" target="_blank" onclick="return top.js.OpenExtLink(window
,event,this)">
http://www.freelists.org/webpage/oracle-l</a><br></blockquote></div><span class
="sg"><br><br clear="all"><br>-- <br>Jared Still<br>Certifiable Oracle DBA and
Part Time Perl Evangelist<br><br>
</span></blockquote></div><br><br clear="all"><br>-- <br>-- ---- ---- ---- ----
-- -----<br>select standard_disclaimer from company_requirements where category
= 'MANDATORY';<br>
|
|
