Home » SQL & PL/SQL » SQL & PL/SQL » Edition Based Redefinition - Triggers on Editioning Views (Oracle Database 11g Enterprise Edition 11.2.0.4.0 64 Bit Production, Linux)
Edition Based Redefinition - Triggers on Editioning Views [message #659452] Wed, 18 January 2017 03:36 Go to next message
fixxxer
Messages: 32
Registered: August 2014
Member
Hi all,

I have question regarding triggers on editioning views for a database that is utilizing Edition Based Redefinition. This is on: Oracle Database 11g Enterprise Edition 11.2.0.4.0 64 Bit Production, Linux.

We are seeing this problem in our production environment, but I have created a simple example (code below) to show the behavior. In short - when we add a column to the underlying table which the editioning view is based on, this does not invalidate the trigger on the editioning view - however, when we drop that new column it causes the trigger to go invalid, even though the editioning view does not make use of that column. Why would it go invalid when this column is not referenced at all in the editioniong view?

SQL> ALTER SESSION SET current_schema = sys;

Session altered.

SQL> ALTER USER bguiney ENABLE EDITIONS FORCE;

User altered.

SQL> CREATE EDITION test_edition AS CHILD OF ORA$BASE;

Edition created.

SQL> GRANT USE ON EDITION test_edition TO bguiney;

Grant succeeded.

SQL> ALTER SESSION SET current_schema = bguiney;

Session altered.

SQL> ALTER SESSION SET edition = test_edition;

Session altered.

SQL> CREATE TABLE test_drop_col(col1 VARCHAR2(10), col2 NUMBER);

Table created.

SQL> CREATE OR REPLACE EDITIONING VIEW test_drop_col_vw AS SELECT col1, col2 FROM test_drop_col;

View created.

SQL> CREATE OR REPLACE TRIGGER test_drop_col_trg
BEFORE INSERT OR UPDATE ON test_drop_col_vw
FOR EACH ROW
BEGIN
  :NEW.col1 := 'TEST';
END;
/  2    3    4    5    6    7

Trigger created.

SQL> SELECT object_name, status FROM dba_invalid_objects WHERE owner='BGUINEY';

no rows selected

SQL> ALTER TABLE test_drop_col ADD (col3 NUMBER);

Table altered.

SQL> SELECT object_name, status FROM dba_invalid_objects WHERE owner='BGUINEY';

no rows selected

SQL> ALTER TABLE test_drop_col DROP COLUMN col3;

Table altered.

SQL> SELECT object_name, status FROM dba_invalid_objects WHERE owner='BGUINEY';

OBJECT_NAME                                                                                                                      STATUS
-------------------------------------------------------------------------------------------------------------------------------- -------
TEST_DROP_COL_TRG                                                                                                                INVALID

SQL>
Re: Edition Based Redefinition - Triggers on Editioning Views [message #659453 is a reply to message #659452] Wed, 18 January 2017 03:48 Go to previous messageGo to next message
John Watson
Messages: 7669
Registered: January 2010
Location: Global Village
Senior Member
11.2 introduced what they call "fine-grained invalidation", it may be that you have come across an anomaly with that. I ran your test in 12.1.0.2, same result.
Re: Edition Based Redefinition - Triggers on Editioning Views [message #659454 is a reply to message #659453] Wed, 18 January 2017 03:58 Go to previous messageGo to next message
fixxxer
Messages: 32
Registered: August 2014
Member
Yeah, I tested it on 12c myself and seen the same behavior. We wouldn't drop columns very often, but it is a bit frustrating as it adds to the limits of EBR - I was hoping I had done something wrong! I might raise an SR with Oracle and see what they say.
Re: Edition Based Redefinition - Triggers on Editioning Views [message #659465 is a reply to message #659452] Wed, 18 January 2017 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 65971
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Read SYS is special.

- Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
- SYS/SYSDBA is special
- SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
- SYS/SYSDBA does not act like any other user
- When you use SYS/SYSDBA Oracle deactivates some code path and activates others
(no flashback query for it, no read only transactions, no triggers, no consistency...)
- Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS/SYSDBA for anything that can be done by another user.
Use SYS/SYSDBA ONLY for something that can't be done by someone else.


Re: Edition Based Redefinition - Triggers on Editioning Views [message #659576 is a reply to message #659465] Mon, 23 January 2017 03:22 Go to previous messageGo to next message
fixxxer
Messages: 32
Registered: August 2014
Member
Just an update - ORACLE have come back with the below, informing it is an existing bug which has a patchset available Smile

Quote:
The scenario is matching with existing BUG :

Please check below doc :

EBR: Triggers On Editioning View Invalidated After Running Set Unused On A Column Bug 22575531 https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=22575531 https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=22575531 ( Doc ID 2135103.1 )

[Updated on: Mon, 23 January 2017 03:48] by Moderator

Report message to a moderator

Re: Edition Based Redefinition - Triggers on Editioning Views [message #659580 is a reply to message #659576] Mon, 23 January 2017 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 65971
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback.
The direct url are not accessible and it seems the doc is hidden.

Re: Edition Based Redefinition - Triggers on Editioning Views [message #659581 is a reply to message #659580] Mon, 23 January 2017 04:05 Go to previous messageGo to next message
fixxxer
Messages: 32
Registered: August 2014
Member
Michel Cadot wrote on Mon, 23 January 2017 09:57

Thanks for the feedback.
The direct url are not accessible and it seems the doc is hidden.

Does this link work for you:

https://support.oracle.com/epmos/faces/BugDisplay?_afrLoop=184387645199130&parent=SrDetailText&sourceId=3-14064411971&id= 22575531&_afrWindowMode=0&_adf.ctrl-state=eav2zrexf_94
Re: Edition Based Redefinition - Triggers on Editioning Views [message #659582 is a reply to message #659581] Mon, 23 January 2017 04:08 Go to previous message
Michel Cadot
Messages: 65971
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The bug page is OK but gives nothing (as almost always everything is hidden); the most important is the doc page but the Doc Id you gave returns nothing, so it seems to also be hidden.

Previous Topic: Oracle Outer join Operator(+) with operand of OR or IN
Next Topic: SQL Execution Plan
Goto Forum:
  


Current Time: Sun Nov 18 16:30:39 CST 2018