triger: after servererror on database

You will always find some unusual ideas from Tom, today I just found another one incidentally.
Here is the url: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:40115659055475
And below are some extracts from this article which I think valuable for me:[@more@]

You can use this trigger -- just grab whatever info you want and record it:
ops$tkyte@ORA9IR2> create table t ( msg varchar2(4000) );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace trigger snapshot_too_old
2 after servererror on database
3 declare
4 l_sql_text ora_name_list_t;
5 l_n number;
6 begin
7 if ( is_servererror(1555) )
8 then
9 insert into t values ( 'ora_sysevent = ' || ora_sysevent );
10 insert into t values ( 'ora_login_user = ' || ora_login_user );
11 insert into t values ( 'ora_server_error = ' || ora_server_error(1) );
12
13 l_n := ora_sql_txt( l_sql_text );
14 for i in 1 .. l_n
15 loop
16 insert into t values ( 'l_sql_text(' || i || ') = ' || l_sql_text(i) );
17 end loop;
18 end if;
19 end;
20 /

Trigger created.

ops$tkyte@ORA9IR2>

no we must get a 1555 - so go small:

ops$tkyte@ORA9IR2> create undo tablespace small datafile size 2m autoextend OFF;

Tablespace created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table ora_1555;

Table dropped.

ops$tkyte@ORA9IR2> create table ora_1555 as select * from all_objects;

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter system set undo_tablespace = small;

System altered.

Now open a cursor -- it is read consistent as of NOW

ops$tkyte@ORA9IR2> variable x refcursor
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec open :x for select * from ora_1555;

PL/SQL procedure successfully completed.

generate lots of undo and commit and do it over again and again, ugly... don't do this
at home kids...

ops$tkyte@ORA9IR2> begin
2 for i in 1 .. 10
3 loop
4 loop
5 delete from ora_1555 where rownum <= 1000;
6 exit when sql%rowcount = 0;
7 commit;
8 end loop;
9 commit;
10 insert into ora_1555 select * from all_objects;
11 commit;
12 end loop;
13 end;
14 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> print x
ERROR:
ORA-01555: snapshot too old: rollback segment number 13 with name "_SYSSMU13$" too small



no rows selected

got the error, and....

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter system set undo_tablespace = undo;

System altered.

ops$tkyte@ORA9IR2> drop tablespace small;

Tablespace dropped.

ops$tkyte@ORA9IR2> drop trigger snapshot_too_old;

Trigger dropped.

ops$tkyte@ORA9IR2> spool off
ops$tkyte@ORA9IR2> select * from t;

MSG
------------------------------------------------------------------------------------------
-------------------------------
ora_sysevent = SERVERERROR
ora_login_user = OPS$TKYTE
ora_server_error = 1555
l_sql_text(1) = SELECT * FROM ORA_1555

we have the record of it

Isn't a nice idea?
With this idea I remembered that I have ever writen one triger for one of my oversea's colleague requirement,he got an strange requirement that every owner mustn't get any privilege to execute any DDL, I tested several other ways but no one works, and suddenly I got the idea maybe triggers can help.
here is the code:
-- Please executing this script by user sys
CREATE OR REPLACE TRIGGER trg_deny_ddl
BEFORE DDL ON DATABASE
BEGIN
-- if the DDL fired by TEST1, just abort it
IF USER = 'TEST1'
THEN
raise_application_error (-20000,'user: '||USER||' are NOT permited for executing DDL!');
END IF;
END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9728406/viewspace-916001/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9728406/viewspace-916001/