In my last post regarding Edition Based Redefinition (EBR) I explained that Oracle views do not automatically take on changes to the structures underneath them. This makes them a very handy feature. But they do have limitations – such as when an new NOT NULL column is added. Here is an example:
-- create a table with just one lonely column SQL> CREATE TABLE demo 2 ( col1 NUMBER ); Table created. -- create a view on top of the table SQL> CREATE VIEW demo_view AS 2 SELECT * 3 FROM demo; View created. -- add a NOT NULL column to the table SQL> ALTER TABLE demo 2 ADD ( col2 NUMBER NOT NULL ); Table altered. -- try to insert a record into the view SQL> INSERT INTO demo_view 2 VALUES(2); INSERT INTO demo_view * ERROR at line 1: ORA-01400: cannot insert NULL into ("DRH"."DEMO"."COL2")
The INSERT does not succeed because a value is not provided for COL2 in the DEMO table.
In the next few post’s I’ll explain how this failure might be worked around using a methodology that requires downtime.
Then I’ll segue into how EBR handles it with minimal downtime.
Thanks for reading!