Very good, thanks
A reader, March 22, 2002 - 10:16 am UTC
Never seen this system events before.
URGENT HELP:Strange problem....
Neeti, April 04, 2002 - 4:05 pm UTC
Hi Tom,
We have a production issue.I was trying to write a database
trigger and tested it fine. This trigger logs the ALL DDL
operation performed on a prticular schema.To keep the
activity transparent from users I wrote a package and called
it inside the trigger.I wrote a wrapper for the package.
It was working fine then I created a copy of the trigger and
by mistake compiled the copy in the base.
Now I have two trigger trigger1 and trigger2 in the base doing the same thing and somehow the package got invalidated.
As the trigger is at the database level and both do the same
thing so whenever I try to create any object in database under any schema then one or the other trigger is failing due to invalidation.
It's kind of mutation and I am getting the following error..
HONDEV>create table t(t number);
create table t(t number)
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
VUPRE.DBUPDATE_DDL_AUDIT_TRIGGER
ORA-01031: insufficient privileges
It's not even allowing me to drop either of the trigger or any object because the other trigger is invalid.
Please help its urgent....please please please....
April 04, 2002 - 4:20 pm UTC
well -- no version, not much info.
Try this:
connect internal
or in 9i
connect user/pass as sysdba;
drop trigger vupre.DBUPDATE_DDL_AUDIT_TRIGGER;
without further info -- I cannot really give you much more. There is a hidden _ parameter (contact support) that disables these triggers alltogether as well.
Sorry for not sending you full info...
Neeti, April 04, 2002 - 4:39 pm UTC
Well we are oracle Oracle8i Enterprise Edition Release 8.1.6.2.0 and running on Solaris. I panicked because
the trigger is not allowing anyone to create anything.
The two triggers mutate if I say
DROP TRIGGER A;
Then trigger B fails with insufficient previleges
and if I try to drop trigger B then trigger A fails
with insufficient priveleges.
I have DBA priveleges. Is there a way that I can prevent
user with alter any trigger privileges to disable this trigger.
April 04, 2002 - 7:14 pm UTC
get internal. do it with that.
AUDIT ANY DDL
A reader, November 07, 2004 - 10:16 pm UTC
With 9iR2, what is the easiest way to AUDIT ANY DDL on any schema? I dont really care about the SQL text in the DDL, just the fact that a object was created, by whom, from where, at what time, object name/type, etc?
Thanks
November 08, 2004 - 10:10 am UTC
enable auditing (audit_trail=true) and use the audit command -- you'll want to read about it in the Admin Guide.
Or, a DDL trigger -- but I'm keen on the audit command itself, easier, less code.
A reader, November 08, 2004 - 10:45 am UTC
Right, but there is no AUDIT ANY ON SCHEMA; command! So I would have to do audit create default; audit alter default; etc, etc.
Is there an faster way to audit all DDL (on specific schemas or on all schemas)?
November 08, 2004 - 4:53 pm UTC
begin
for x in ( select * from all_users )
loop
execute immediate 'anything you want at all...';
end loop;
end;
/
plsql is a awesome tool for scripting things as well as a first class programming environment.
DDL event trigger
Emmanuel Makondambuta, June 28, 2007 - 5:49 am UTC
Hi Tom,
I'm writing a DDL event trigger to keep track of all DDL on the database. Everything works fine as long as the DDL is issued from the schema where the trigger and the associated package are defined. When other schemas issue a DDL statement, the original object definition cannot be retrieved.
The goal of the trigger is to retrieve the current definition of the object before "drop" or "alter".
Could you see what's wrong with my code/logic?
As user "drab01"
CREATE TABLE DRAT_DDL_LOG
( DRAC_OPER VARCHAR2(30 BYTE),
DRAC_OBJ_OWNER VARCHAR2(30 BYTE),
DRAC_OBJ_NAME VARCHAR2(30 BYTE),
DRAC_SQL_TEXT CLOB,
DRAC_ORA_USER VARCHAR2(30 BYTE),
DRAC_USER_INFO VARCHAR2(255 BYTE),
DRAC_DATE DATE,
DRAC_STATUS CHAR(2 BYTE)
);
create or replace package draddl IS
function drag_schema_ddl
(
v_schema_name IN VARCHAR2
) RETURN CLOB;
function drag_object_ddl
(
obj_name IN VARCHAR2,
obj_type IN VARCHAR2,
obj_owner IN VARCHAR2
) RETURN CLOB;
end draddl;
/
create or replace package body draddl IS
function drag_schema_ddl
(
v_schema_name IN VARCHAR2
) RETURN CLOB
IS
-- Define local variables.
n NUMBER; -- handle returned by OPEN
tn NUMBER; -- handle returned by ADD_TRANSFORM
doc CLOB; -- individual metadata for each object
ddl CLOB; -- Final ddl to be returned
BEGIN
-- Specify the object type.
n := DBMS_METADATA.OPEN('SCHEMA_EXPORT');
-- Use filters to specify the schema.
DBMS_METADATA.SET_FILTER(n,'SCHEMA',v_schema_name);
-- Request that the metadata be transformed into creation DDL.
tn := DBMS_METADATA.ADD_TRANSFORM(n,'DDL');
-- Fetch the objects.
LOOP
doc := DBMS_METADATA.FETCH_CLOB(n);
-- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
EXIT WHEN doc IS NULL;
-- Store the metadata in the table.
-- INSERT INTO drab01.my_metadata(md) VALUES (doc);
-- COMMIT;
ddl := ddl || ';' || doc;
END LOOP;
-- Release resources.
DBMS_METADATA.CLOSE(n);
RETURN ddl;
END; -- drag_schema_ddl
function drag_object_ddl
(
obj_name IN VARCHAR2,
obj_type IN VARCHAR2,
obj_owner IN VARCHAR2
) RETURN CLOB
IS
-- Define local variables.
n NUMBER; --handle returned by OPEN
tn NUMBER; -- handle returned by ADD_TRANSFORM
doc CLOB;
BEGIN
-- Specify the object type.
n := DBMS_METADATA.OPEN(obj_type);
-- Use filters to specify the particular object desired.
DBMS_METADATA.SET_FILTER(n,'SCHEMA',obj_owner);
DBMS_METADATA.SET_FILTER(n,'NAME',obj_name);
-- Request that the metadata be transformed into creation DDL.
tn := DBMS_METADATA.ADD_TRANSFORM(n,'DDL');
-- Fetch the object.
doc := DBMS_METADATA.FETCH_CLOB(n);
-- Release resources.
DBMS_METADATA.CLOSE(n);
RETURN doc;
END drag_object_ddl;
END;
/
--- Now the trigger ---
create or replace TRIGGER DRAG_DUMP_DDL before DDL ON DATABASE
DECLARE
stmt CLOB;
old_ddl CLOB;
sql_text ora_name_list_t;
n pls_integer;
BEGIN
IF ora_dict_obj_owner LIKE '___B__%'
AND ora_dict_obj_owner NOT LIKE '%USER' THEN
-- check if NBB owner or not
n := ora_sql_txt(sql_text);
FOR i IN 1 .. n
LOOP
-- put all statement lines in 1 field
stmt := stmt || sql_text(i);
END LOOP;
CASE ora_sysevent --check type of event
WHEN 'DROP' THEN
-- on drop retrieve old DDL
INSERT INTO drab01.drat_ddl_log
SELECT 'DROP',
ora_dict_obj_owner,
ora_dict_obj_name,
stmt || ';' || drab01.draddl.drag_object_ddl(ora_dict_obj_name,ora_dict_obj_type,ora_dict_obj_owner),
USER,
sys_context('USERENV', 'OS_USER') || '-' || sys_context('USERENV', 'HOST'),
sysdate,
NULL
FROM dual;
WHEN 'ALTER' THEN
-- on alter retrieve old DDL or skip message for compile
IF(instr(UPPER(stmt), 'COMPILE')) = 0
AND -- not a compile command
(instr(stmt, 'BIN$')) = 0 -- not an automatic rename during drop
THEN
INSERT INTO drab01.drat_ddl_log
SELECT ora_sysevent,
ora_dict_obj_owner,
ora_dict_obj_name,
stmt || ';' || drab01.draddl.drag_object_ddl(ora_dict_obj_name,ora_dict_obj_type,ora_dict_obj_owner),
USER,
sys_context('USERENV', 'OS_USER') || '-' || sys_context('USERENV', 'HOST'),
sysdate,
NULL
FROM dual;
end if;
ELSE
-- default case, log any command other then ANALYZE (truncate, create ...)
if ora_sysevent <> 'ANALYZE' THEN
INSERT INTO drab01.drat_ddl_log
SELECT ora_sysevent,
ora_dict_obj_owner,
ora_dict_obj_name,
stmt,
USER,
sys_context('USERENV', 'OS_USER') || '-' || sys_context('USERENV', 'HOST'),
sysdate,
NULL
FROM dual;
end if;
END CASE;
END IF;
END drag_dump_ddl;
/
---
Now, still connected as user drab01 :
create table t5 (a number, b clob) tablespace USERS;
I get (from the drat_ddl_log):
"create table t5 (a number, b clob) tablespace USERS" in the drat_ddl_log.drac_sql_text
For the alter statement:
alter table t5 add c CHAR(10);
I get (in drat_ddl_log.drac_sql_text column):
alter table t5 add c CHAR(10);
CREATE TABLE "DRAB01"."T5"
( "A" NUMBER,
"B" CLOB
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
LOB ("B") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
But, when connected as user drab02 (with execute on drab01.draddl package privilege)
create table test1 (x number);
I get (in drat_ddl_log.drac_sql_text column):
create table test1 (x number);
Then when I issue:
alter table test1 add y varchar2(10);
I only get (in drat_ddl_log.drac_sql_text column):
alter table test1 add y varchar2(10)
Could you help?
Thanks a lot,
Emmanuel.
July 02, 2007 - 10:15 am UTC
http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#sthref4208 it is not going to happen via a trigger - see the last bullet point there
... In stored procedures, functions, and definers-rights packages, roles (such as SELECT_CATALOG_ROLE) are disabled. Therefore, such a PL/SQL program can only fetch metadata for objects in its own schema. If you want to write a PL/SQL program that fetches metadata for objects in a different schema (based on the invoker's possession of SELECT_CATALOG_ROLE), you must make the program invokers-rights. ...
a trigger can never be "invokers rights", the trigger has no roles, hence - dbms_metadata can only see what it sees without roles - which is basically just the stuff in the schema that owns the trigger.
suggestion: instead of recording the entire old DDL, why not just log the DDL that was applied - that would be more than sufficient to reconstruct what happened. Also, dbms_metadata is not know for "speed", this would dramatically impact DDL operations.
DDL trigger and dbms_metadata
Emmanuel Makondambuta, July 03, 2007 - 8:53 am UTC
Thank you Tom. You pointed me to the security model I read too quick before writing my code. As you advised, I will stop retrieving metadata info (impact on DDL is really visible). In fact recording the applied DDL should be enough to reconstruct the history of any of my monitored objects.
Emmanuel.
DDl
A reader, May 07, 2009 - 12:55 pm UTC
Tom:
I have a DDL audit script that tracks DDL changes in production.
I got an alert that a trigger was changed using "ALTER".
is it possible for a user call while using an application to trigger that or it has to be somone tampering with the code.
I was wondering if trigger was in "invalid" mode and became "valid" when it was called. I think oracle does implicit alter recompile.
May 11, 2009 - 1:37 pm UTC
definitely could be an implicit recompile
ops$tkyte%ORA10GR2> create table t ( x int );
Table created.
ops$tkyte%ORA10GR2> create procedure p
2 as
3 begin
4 null;
5 end;
6 /
Procedure created.
ops$tkyte%ORA10GR2> create trigger t_trigger
2 before insert on t
3 begin
4 dbms_output.put_line( 'hello' );
5 p;
6 end;
7 /
Trigger created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table log
2 ( operation varchar2(25),
3 owner varchar2(25),
4 name varchar2(25),
5 extra varchar2(4000) );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace trigger ddl_trigger
2 before create or alter or drop on SCHEMA
3 declare
4 l_sysevent varchar2(25);
5 begin
6 select ora_sysevent into l_sysevent from dual;
7
8 if ( l_sysevent in ('DROP','CREATE') )
9 then
10 insert into log
11 select ora_sysevent, ora_dict_obj_owner,
12 ora_dict_obj_name, null
13 from dual;
14 elsif ( l_sysevent = 'ALTER' )
15 then
16 insert into log
17 select ora_sysevent, ora_dict_obj_owner,
18 ora_dict_obj_name, sql_text
19 from v$open_cursor
20 where upper(sql_text) like 'ALTER%' ;
21 end if;
22 end;
23 /
Trigger created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure p( x in number default 0 )
2 as
3 begin
4 null;
5 end;
6 /
Procedure created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select object_name, object_type, status from user_objects where object_name = 'T_TRIGGER';
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
T_TRIGGER TRIGGER INVALID
ops$tkyte%ORA10GR2> insert into t values ( 1 );
hello
1 row created.
ops$tkyte%ORA10GR2> select object_name, object_type, status from user_objects where object_name = 'T_TRIGGER';
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
T_TRIGGER TRIGGER VALID
ops$tkyte%ORA10GR2> select * from log;
OPERATION OWNER NAME EXTRA
---------- --------- ---------- ------------------------------
CREATE OPS$TKYTE P
ALTER OPS$TKYTE T_TRIGGER ALTER TRIGGER
"OPS$TKYTE"."T_TRIGGER"
COMPILE REUSE SETTINGS
ddl
sam, May 11, 2009 - 4:15 pm UTC
Tom:
That is excellent. I noticed you have the trigger "after create or drop on schmea" while i have it "after database".
would this be different (database incldues all schemas i assume)? Also, is that extra column for the SQL? If i recompile a long package would that would store the whole package code.
create or replace trigger trg_audit_ddl
AFTER DDL ON DATABASE DECLARE
BEGIN
insert into AUDIT_DDL
(
ddl_date,
user_name,
ddl_type,
object_type,
object_name,
owner,
osuser,
host,
terminal
)
VALUES
(
sysdate,
ora_login_user,
ora_sysevent,
ora_dict_obj_type,
ora_dict_obj_name,
ora_dict_obj_owner,
sys_context('USERENV','OS_USER'),
sys_context('USERENV','HOST'),
sys_context('USERENV','TERMINAL')
);
May 11, 2009 - 7:27 pm UTC
smk - aka sam
documentation..... we document what "on schema" is and what "on database" is - although - they are somewhat self documenting.
... Also, is that
extra column for the SQL? If i recompile a long package would that would store
the whole package code.
...
did you read my snippet of code? (do you see it does different things for drop/create than for alter...) (do you see it got the sql for the alter from v$open_cursor...) (do you know how big the biggest sql_text it will get is therefore....)
did you observe the output?
did you try it out?
SQL> l
1 select email, count(*)
2 from reviews
3 group by email
4 having count(*) > 100
5* order by 2
...
xxxx@yyy.zzz 473
xxxxxxx@yyy.zzz 601
smk_xxxxxxx@yyy.zzz 1097
55 rows selected.
Catching "create tablespace"
Gary Cowell, March 24, 2010 - 3:02 am UTC
I need to prevent developers creating tablespaces in the wrong place. I need them to use OMF naming in the ASM disk group. Of course they 'need' sysdba (I have no choice in this) so they can put them where they like in reality. I found one in /tmp the other day, just ready to be deleted when the box reboots :(
I've been trying to do this with a DDL trigger but I don't have access to the SQL in v$open_cursor so I can't tell if they used a path name or not.
i really want to enforce the syntax:
create tablespace xyz datafile size 32M ;
NOT
create tablespace xyz datfile '/tmp/stupid' size 32M;
Can I do this with a DDL trigger? This is on 10GR2, and later, 11GR2.
March 26, 2010 - 10:51 am UTC
.. Of course they 'need' sysdba (I
have no choice in this)...
run away, if you give someone sysdba, they can literally do whatever they want, you are "screwed". You cannot secure a thing, you cannot do anything, they can do everything. Give up.
The way to do this is
a) developers get minimal privileges, just what they need to do their job
b) developers do not actually create tablespaces, if they have a need to do that, and it is part of their application design - you need to get your resume dusted off - you'll be looking for a new position soon as this application is destined for complete and utter failure.
If they truly truly really had a bona-fide reason for needing to create a tablespace - a truly, real, honest to goodness one (and I, for the LIFE of me, cannot come up with a single real or fake example, I'd love to hear about it), then you would
a) write a stored procedure
b) put it into an account with create tablespace and create procedure and nothing else
c) this procedure would accept nothing other than a tablespace name
d) this procedure would use dbms_assert to ensure that what they've sent you is a good "sql name"
e) it would issue the create
But it would still be only marginally less "not smart" than giving sysdba to people. I've never heard of such a "not smart" approach to anything.
I'm willing to get on the phone and talk with your management if they would like to hear "this is officially the 'least smart' thing I can imagine anyone doing - ever"
triggers do not affect sysdba, they don't count. sysdba can get around anything at all, anything. No matter what you set up.
full sql text
Darko Egersdorfer, March 04, 2011 - 7:26 am UTC
If you want full SQL text for ALTER part (instead of first 60 characters that are in v$open_cursor) you can use:
SELECT listagg(st.SQL_TEXT) WITHIN GROUP (ORDER BY st.PIECE)
INTO v_msg
FROM v$sqltext st
WHERE st.SQL_ID = (
SELECT t.sql_id
FROM v$open_cursor t
WHERE upper(sql_text) LIKE 'ALTER%' || ora_dict_obj_name || '%'
AND sid = (SELECT sid
FROM v$session
WHERE audsid = userenv('sessionid')));
Reader, November 25, 2011 - 10:33 am UTC
I have DDL trigger as show below. If I run - alter trigger TRG_DDL_EVENTS disable;, it is not captured in the audit tables. Can you explain why?
CREATE OR REPLACE TRIGGER TRG_DDL_EVENTS
AFTER DDL
ON SCHEMA
DECLARE
l_event_id NUMBER(10,0);
l_sql_text ORA_NAME_LIST_T;
BEGIN
SELECT seq_audit_ddl_events.NEXTVAL
INTO l_event_id
FROM DUAL;
INSERT INTO ldhe_staging.audit_ddl_events
( event_id,
event_dt_key,
event_timestamp,
ora_login_user,
ora_dict_obj_name ,
ora_dict_obj_owner ,
ora_dict_obj_type,
ora_sys_event,
machine ,
program,
osuser
)
( SELECT l_event_id,
to_number(to_char(SYSDATE,'yyyymmdd')),
systimestamp,
ora_login_user,
ora_dict_obj_name,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_sysevent,
machine,
program,
osuser
FROM V$SESSION
WHERE SYS_CONTEXT('USERENV','SESSIONID') = audsid(+));
FOR l IN 1..ORA_SQL_TXT(l_sql_text)
LOOP
INSERT INTO audit_ddl_events_sql
( event_id,
sql_line,
sql_text )
VALUES
( l_event_id,
l,
l_sql_text(l) );
END LOOP;
END trg_ddl_events;
/
November 28, 2011 - 10:18 am UTC
Not sure if that would be considered a bug or not. I presume it happens that way because you cannot really run something that is having DDL performed on it (I could have seen this deadlocking itself instead...)
One thing - WHERE SYS_CONTEXT('USERENV','SESSIONID') = audsid(+));
is just *wrong*. You need at least two tables to perform an outer join. That (+) is just misleading. It cannot be doing ANYTHING.
ops$tkyte%ORA11GR2> select * from dual where 'Y' = dummy(+);
no rows selected
ops$tkyte%ORA11GR2> select * from dual where 'X' = dummy(+);
D
-
X
ops$tkyte%ORA11GR2>
If you want a NULL row with a valid event id in it, do this instead:
select ...
from (select l_event_id event_id, to_number(to_char(sysdate,'yyyymmdd')) dt,
ora_login_user, ora_dict_obj_name, .....,
SYS_CONTEXT('USERENV','SESSIONID') sid
from dual) d,
(select audsid, machine, program, osuser
from v$session) v
where d.sid = v.audsid(+);
Reader, November 28, 2011 - 10:45 am UTC
Can i use this query instead:
SELECT --l_event_id,
to_number(to_char(SYSDATE,'yyyymmdd')) dt,
systimestamp,
ora_login_user,
ora_dict_obj_name,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_sysevent,
SYS_CONTEXT('USERENV','HOST') machine_name,
SYS_CONTEXT('USERENV','MODULE') MODULE_NAME,
SYS_CONTEXT('USERENV','OS_USER') OS_USER
FROM dual;
Trigger code:
CREATE OR REPLACE TRIGGER TRG_LDHE_STAGING_DDL_EVENTS
AFTER DDL
ON SCHEMA
DECLARE
l_event_id NUMBER(10,0);
l_sql_text ORA_NAME_LIST_T;
BEGIN
SELECT seq_audit_ddl_events.NEXTVAL
INTO l_event_id
FROM DUAL;
INSERT INTO audit_ddl_events
( event_id,
event_dt_key,
event_timestamp,
login_user,
object_name ,
object_owner ,
object_type,
sys_event,
machine_name ,
program_name,
os_user
)
( SELECT l_event_id,
to_number(to_char(SYSDATE,'yyyymmdd')),
systimestamp,
ora_login_user,
ora_dict_obj_name,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_sysevent,
SYS_CONTEXT('USERENV','HOST') machine_name,
SYS_CONTEXT('USERENV','MODULE') MODULE_NAME,
SYS_CONTEXT('USERENV','OS_USER') OS_USER
FROM dual
FOR i IN 1..ORA_SQL_TXT(l_sql_text)
LOOP
INSERT INTO audit_ddl_events_sql
( event_id,
sql_line,
sql_text )
VALUES
( l_event_id,
i,
substr(l_sql_text(i),1,4000) );
END LOOP;
END ;
/
Reader, November 28, 2011 - 10:51 am UTC
If I need to capture the session's timezone and timestamp, can I use localtimestamp function?
November 29, 2011 - 7:24 am UTC
ops$tkyte%ORA11GR2> select sessiontimezone, localtimestamp from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
-05:00
29-NOV-11 08.24.08.383177 AM
Reader, November 29, 2011 - 10:49 am UTC
If I need to insert sessiontimezone into a field in a table, what should the datatype of the field be?
November 30, 2011 - 6:59 am UTC
ops$tkyte%ORA11GR2> create or replace view v as select sessiontimezone x from dual;
View created.
ops$tkyte%ORA11GR2> desc v
Name Null? Type
----------------------------------- -------- ------------------------
X VARCHAR2(75)
ops$tkyte%ORA11GR2>
Reader, November 30, 2011 - 8:07 am UTC
Instead of this -
select ...
from (select l_event_id event_id, to_number(to_char(sysdate,'yyyymmdd')) dt,
ora_login_user, ora_dict_obj_name, .....,
SYS_CONTEXT('USERENV','SESSIONID') sid
from dual) d,
(select audsid, machine, program, osuser
from v$session) v
where d.sid = v.audsid(+);
can I use the following query?
SELECT --l_event_id,
to_number(to_char(SYSDATE,'yyyymmdd')) dt,
systimestamp,
ora_login_user,
ora_dict_obj_name,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_sysevent,
SYS_CONTEXT('USERENV','HOST') machine_name,
SYS_CONTEXT('USERENV','MODULE') MODULE_NAME,
SYS_CONTEXT('USERENV','OS_USER') OS_USER
FROM dual;
Trigger code:
CREATE OR REPLACE TRIGGER TRG_LDHE_STAGING_DDL_EVENTS
AFTER DDL
ON SCHEMA
DECLARE
l_event_id NUMBER(10,0);
l_sql_text ORA_NAME_LIST_T;
BEGIN
SELECT seq_audit_ddl_events.NEXTVAL
INTO l_event_id
FROM DUAL;
INSERT INTO audit_ddl_events
( event_id,
event_dt_key,
event_timestamp,
login_user,
object_name ,
object_owner ,
object_type,
sys_event,
machine_name ,
program_name,
os_user
)
( SELECT l_event_id,
to_number(to_char(SYSDATE,'yyyymmdd')),
systimestamp,
ora_login_user,
ora_dict_obj_name,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_sysevent,
SYS_CONTEXT('USERENV','HOST') machine_name,
SYS_CONTEXT('USERENV','MODULE') MODULE_NAME,
SYS_CONTEXT('USERENV','OS_USER') OS_USER
FROM dual
FOR i IN 1..ORA_SQL_TXT(l_sql_text)
LOOP
INSERT INTO audit_ddl_events_sql
( event_id,
sql_line,
sql_text )
VALUES
( l_event_id,
i,
substr(l_sql_text(i),1,4000) );
END LOOP;
END ;
/
November 30, 2011 - 12:22 pm UTC
sure, or even just use values (... ) - no select at all...
How to write trigger on ALTER TABLE DROP PARTITION?
Ebrahim, November 27, 2023 - 12:01 am UTC
Dear Tom,
One of our important tables is partitioned by day.
Since this schema is used by multiple applications, I would like to monitor and prevent if any application attempts to drop a partition of this table, however old it may be.
I wrote this, but obviously is not working since ora_dict_obj_type is TABLE in the command and not PARTITION:
ALTER TABLE <my_table> DROP PARTITION SYS_P146453;
create trigger can_not_drop_partition_trg
before drop or alter on ea303v.schema
begin
if ora_sysevent = 'ALTER' and ora_dict_obj_type = 'PARTITION' then
raise_application_error(-20001,'Cannot Drop partition');
end if;
end;
/
Can you please guide me here?
Thanks !
November 27, 2023 - 3:17 pm UTC
You can inspect the ora_sql_txt to see details of triggering statement and take appropriate action:
create table t (
c1 int
) partition by range ( c1 ) (
partition p0 values less than ( 11 ),
partition p1 values less than ( 21 )
);
create or replace trigger can_not_drop_partition_trg
before alter on chris.schema
declare
sql_text ora_name_list_t;
n pls_integer;
begin
n := ora_sql_txt(sql_text);
dbms_output.put_line ( 'Triggering text' );
for i in 1..n loop
dbms_output.put_line ( sql_text(i) );
end loop;
end;
/
set serveroutput on
alter table t drop partition p0;
/*
Triggering text
alter table t drop partition p0
Ebrahim, November 28, 2023 - 4:48 am UTC
Wonderful!
This will allow us to prevent partition drops and also alert us if there is an attempt. We can then have the application owners examine their code and take corrective action.
Thank you.
November 29, 2023 - 2:02 pm UTC
Glad this helps