Oracle’s Edition Based Redefinition (EBR) feature allows modifying database objects such as PLSQL code or database tables without interfering with a functioning application. In this series of articles I’ll introduce some of the underlying concepts of EBR and show some examples of its use. This first post describes a feature of Oracle views that helps enable EBR – the fact that views do not automatically change when their underlying tables do. Here is an example:
-- create demo table 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. -- insert a record into the view and only provdide value for col1 SQL> INSERT INTO demo_view 2 VALUES(1); 1 row created. -- add a second column to the table SQL> ALTER TABLE demo 2 ADD ( col2 NUMBER ); Table altered. -- Does the view see the new column right away? No! SQL> INSERT INTO demo_view 2 VALUES(2); 1 row created. -- The view remains blissfully unaware SQL> SELECT * 2 FROM demo_view; COL1 ---------- 1 2 -- the table sure knows about the column though SQL> INSERT INTO demo 2 VALUES(3); INSERT INTO demo * ERROR at line 1: ORA-00947: not enough values
Inserts directly into the table fail because of the new column. But not the view! The view does not see the columns until it is recreated.
SQL> CREATE OR REPLACE VIEW demo_view AS 2 SELECT * 3 FROM demo; View created. SQL> INSERT INTO demo_view 2 VALUES(3); INSERT INTO demo_view * ERROR at line 1: ORA-00947: not enough values
In future articles I’ll explain how this relates to EBR.
Thanks for reading!