Home » SQL & PL/SQL » SQL & PL/SQL » DDL trigger wont fire on ALTER TABLE EXCHANGE PARTITION (Oracle 10G release 2, HP-UNIX)
DDL trigger wont fire on ALTER TABLE EXCHANGE PARTITION [message #421720] Thu, 10 September 2009 01:49 Go to next message
asantiago
Messages: 3
Registered: September 2009
Junior Member
Hi,

Greetings to all.

Im a newbie to this forum and I am currently having a problem with firing a trigger for a DDL statement.

I have tried creating a Schema level trigger to test ALTER TABLE EXCHANGE PARTITION if it will be logged on a DDL log table.

Below is my trigger:
----------------------------------

CREATE OR REPLACE
TRIGGER ICMS.ODS_MONITOR_BEFORE_DDL
BEFORE ALTER ON "ICMS".SCHEMA
DECLARE
oper ddl_log.operation%TYPE;
BEGIN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, NULL, USER, SYSDATE
FROM dual;
END ODS_MONITOR_BEFORE_DDL;



Below is the command I issue to test the trigger:
-----------------------------------------------

ALTER TABLE ICMS.DWOUT00 EXCHANGE PARTITION P_OUTA
WITH TABLE ICMS.DWOUT00_TMP;


After issuing the above command and check the DDL_LOG table, there was no record for the previously run DDL stmt.

However, I tried doing an ALTER ICMS.DWOUT00
MODIFY <column_name> <data_type>
, the DDL stmt was successfully logged on DDL_LOG table.

Do you have any idea how to resolve this or any alternative method to log the EXCHANGE PARTITION to DDL_LOG table?

Any suggestions would be greatly appreciated.

Thank you very much.

Best regards,
Anna
Re: DDL trigger wont fire on ALTER TABLE EXCHANGE PARTITION [message #421728 is a reply to message #421720] Thu, 10 September 2009 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Use SQL*Plus and copy and paste your session that shows what you posted.

Regards
Michel
Re: DDL trigger wont fire on ALTER TABLE EXCHANGE PARTITION [message #421734 is a reply to message #421728] Thu, 10 September 2009 03:23 Go to previous messageGo to next message
asantiago
Messages: 3
Registered: September 2009
Junior Member
Thanks Michel! I'll keep that in mind for my future posts.

As for my posted problem, I've already found the solution for it.

Thanks a lot.

Regards,
Anna
Re: DDL trigger wont fire on ALTER TABLE EXCHANGE PARTITION [message #421736 is a reply to message #421734] Thu, 10 September 2009 03:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What was the solution?

I'd certainly have thought that the trigger should file for all ALTER statements.
Re: DDL trigger wont fire on ALTER TABLE EXCHANGE PARTITION [message #421738 is a reply to message #421734] Thu, 10 September 2009 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As for my posted problem, I've already found the solution for it.

And what about posting the solution you found?

Please read the following post from Kevin Meade to which I concur:
http://www.orafaq.com/forum/mv/msg/149794/421716/102589/#msg_421716

Regards
Michel


Re: DDL trigger wont fire on ALTER TABLE EXCHANGE PARTITION [message #421824 is a reply to message #421736] Thu, 10 September 2009 20:24 Go to previous message
asantiago
Messages: 3
Registered: September 2009
Junior Member
Hi,

Sorry for the delayed reply. I was not able to post my my solution immediately because I have some deadlines to meet.

Anyway, you're right that the trigger fires for the ALTER statment, however, you have to be on the same schema of the tables involved on the ALTER statement.

I have copied my sqlplus session for the before and after solution. I hope my format is now correct, please bear with me because this is my first time participating on a forum.

This is the version of my databse:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production

===============
Before solution:
===============

SQL> show user
USER is "ODS_USER"
SQL> desc ddl_log
Name Null? Type
----------------------------------------- -------- ----------------------------
OPERATION VARCHAR2(30)
OBJ_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SQL_TEXT VARCHAR2(64)
ATTEMPT_BY VARCHAR2(30)
ATTEMPT_DT DATE

SQL>
SQL> select count(*) from icms.ddl_log;

COUNT(*)
----------
0

SQL> select count(*) from icms.dwout00;

COUNT(*)
----------
0

SQL> select count(*) from icms.dwout00_tmp;

COUNT(*)
----------
10

SQL> ALTER TABLE ICMS.DWOUT00 EXCHANGE PARTITION P_OUTB WITH TABLE ICMS.DWOUT00_TMP;

Table altered.

SQL> select count(*) from icms.ddl_log;

COUNT(*)
----------
0


===============
After solution:
===============

SQL> SHOW USER
USER is "ICMS"
SQL> select TRIGGER_NAME, TABLE_NAME, STATUS
2 from user_triggers
3 where trigger_name = 'ODS_MONITOR_BEFORE_DDL';

TRIGGER_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
ODS_MONITOR_BEFORE_DDL ENABLED

SQL>
SQL> SELECT COUNT(*)
2 FROM DWOUT00_TMP;

COUNT(*)
----------
10

SQL> SELECT COUNT(*)
2 FROM DWOUT00;

COUNT(*)
----------
0

SQL> ALTER TABLE DWOUT00 EXCHANGE PARTITION P_OUTB WITH TABLE DWOUT00_TMP;

Table altered.

SQL> SELECT OPERATION, OBJ_OWNER, OBJECT_NAME FROM DDL_LOG;

OPERATION OBJ_OWNER OBJECT_NAME
----------- ----------- ---------------
ALTER ICMS DWOUT00_TMP


I hope this clarifies everything.

Regards,
Annabelle
Previous Topic: ORA-01722: invalid number
Next Topic: How to get profiler report
Goto Forum:
  


Current Time: Tue Dec 06 14:37:10 CST 2016

Total time taken to generate the page: 0.06191 seconds