A Closer Look at the New Edition
Our technologist redefines and defers with Oracle Database 11g Release 2.
By Tom Kyte
January/February 2010
Instead of using the usual question-and-answer format of the Ask Tom column, I’m going to continue in this issue to explore some of the many new features of Oracle Database 11g Release 2. This time I’ll be looking at two features:
- Edition-Based Redefinition
- Deferred Segment Creation
I consider Edition-Based Redefinition the killer new feature of Oracle Database 11g Release 2. In short, it’s the ability to perform an online application upgrade of your application. It’s also a huge feature—so huge that it’ll take at least three columns to describe it. I’ll start with how to use Edition-Based Redefinition to “patch” systems. Next time, I’ll show how to use Edition-Based Redefinition to minimize downtime during a full-blown application upgrade that includes physical schema changes. Last, I’ll show how to remove downtime during that same full-blown application upgrade.
Over the years, Oracle Database has given us many online operations, such as
- Modifying most parameters (Only 90 out of the 350 are not modifiable online.)
- Reorganizing objects (turning a nonpartitioned table into a partitioned one, reclaiming free space, and so on)
- Creating indexes
- Applying database patches with Oracle Real Application Clusters
- Upgrading Oracle Database from major release to major release
The result is that almost any change at the database level could be accomplished while the database was up and running and performing transactions—with a few glaring exceptions, such as re-creating a stored procedure, changing a trigger, adding a grant, revoking a grant, and changing a view. In short, the objects that constitute your application in the database could not be modified while users were using them. If someone was executing a stored procedure and the DBA tried to “patch” it (CREATE OR REPLACE the code with new code supplied by the developer to fix a bug), the DBA would get locked out (blocked) by the person executing the code.
Further, anyone else who tried to subsequently run a procedure the DBA was trying to replace would be blocked by the DBA. And in most cases, the DBA would be replacing not a single procedure but many procedures, and the CREATE OR REPLACE of the new procedure code would tend to invalidate other dependent objects as well. The database would appear to “freeze”—the DBA could not accomplish the patching tasks (replace a few procedures, packages, views, triggers, and so on), and the end users could not accomplish their tasks (run the procedures to perform transactions). They ended up blocking and locking each other out.
This all ends with Oracle Database 11g Release 2 and Edition-Based Redefinition, which enables DBAs and end users to access more than one occurrence of a stored procedure, a trigger, a view, and other objects and therefore to stage—in isolation—the changes in a schema. Starting with Oracle Database 11g Release 2, a single schema can now have two or more occurrences (think “versions”) of a stored PL/SQL unit (function, type, trigger, and so on) or a view or synonym and all of their related metadata such as GRANTs on those objects. (These two occurrences are independent—they coexist but do not interfere with each other.) The “magic” that permits this is the new edition object type—it introduces a new transparent namespace that allows more than one occurrence of a stored procedure, trigger, and so on in a schema at a time.
In the past, a schema object was referenced with two components: the owner of the object and the object name itself. That would, of course, prevent the existence of two stored procedures named “P” in a single schema. At most, you could have one schema object referenced by OWNER.P.
The edition object in Oracle Database 11g Release 2 introduces a third dimension in the name resolution scheme: all objects are now referenced by the edition of the session, the owner of the object, and the object name. Every database has at least one edition associated with it, and the database always has a default edition. When you create a session in Oracle Database 11g Release 2, your session will have an attribute associated with it that denotes the edition your session will be using (by default, this will be the default edition of the database). Using ALTER SESSION, you may specify any edition to which you have been granted access in your session.
When you invoke or reference objects in Oracle Database 11g Release 2, those objects are dereferenced with the edition currently set in your session. That means that an application administrator can now log in; alter a session to use an edition named VERSION2, for example; and compile code into this edition. The work performed by the application administrator in the VERSION2 edition is visible only in sessions using the VERSION2 edition. Because VERSION2 is not the default edition, no one else sees these changes (new procedures, modified views, dropped packages, and so on) unless the person specifically requests to (via ALTER SESSION) and has the privilege to use—to “see”—that edition. The application administrator is able to create or replace any code without contending with the others running the code. If the application administrator needs to replace 50 PL/SQL units, that person can do so—in isolation—all while the in-use production application continues to execute.
The following small example demonstrates this concept. I’ll start as the DBA by creating a demonstration account and granting it the minimum set of privileges:
SQL> create user demo 2 identified by demo; User created. SQL> grant create session, 2 create procedure 3 to demo; Grant succeeded.
Now I’ll start the process that will enable me to create more than one occurrence of my code objects in the database. I’ll need a new edition in order to do this. The following command creates the new edition:
SQL> create edition version2 2 as child of ora$base; Edition created.
I’ve created an edition named VERSION2 in my database, and it starts as a child of ORA$BASE. As mentioned above, every database has at least one edition and every database has a default edition. For example, every fresh Oracle Database 11g Release 2 install will have a default edition named ORA$BASE. As a child of ORA$BASE, my VERSION2 edition starts life as a complete copy of the default ORA$BASE edition—everything that is true in ORA$BASE is true in VERSION2.
It is important to note, however, that this VERSION2 copy is a virtual copy. The CREATE EDITION statement did not physically copy every object from ORA$BASE into a new VERSION2 edition. Rather, VERSION2 knows to point to ORA$BASE objects, and it won’t start to use storage in the dictionary until I modify objects in the context of the VERSION2 edition.
So, now I install version 1 of my application in the DEMO account:
DEMO> create or replace 2 procedure my_procedure 3 as 4 begin 5 dbms_output.put_line 6 ( 'I am version 1.0' ); 7 end; 8 / Procedure created. DEMO> create or replace 2 procedure my_procedure2 3 as 4 begin 5 my_procedure; 6 end; 7 / Procedure created.
Pretty simple so far, and when I execute this “application,” I see
DEMO> exec my_procedure2 I am version 1.0 PL/SQL procedure successfully completed.
Now, suppose this application code has been running in production for a while now and I discover a bug, something I need to fix in the stored procedure. In the past, I would have needed a maintenance outage in order to install the new code.
Now, however, I can stage the change in the database while the database is up and running and users are executing my code with the default edition (ORA$BASE). First, as the DBA, I need to permit the DEMO account to use editions, and then I need to permit the DEMO account to see and use the VERSION2 edition. Additionally, for this demonstration, I am going to let the SCOTT account see and use VERSION2:
SQL> alter user demo 2 enable editions; User altered. SQL> grant use 2 on edition version2 3 to demo; Grant succeeded. SQL> grant use 2 on edition version2 3 to scott; Grant succeeded.
The ALTER USER statement enabled the DEMO account to use editions of the application code. Without this permission, the DEMO account would be able to have only one copy of the code in place at any given time, just as in Release 7.0 through Release 11.1 of Oracle Database. The GRANT USE statements then permit the DEMO and SCOTT accounts to see the VERSION2 edition. Because the DEMO account is edition enabled, it will be able to modify the VERSION2 edition as well—it can change the implementation of various objects in this edition without affecting other editions in place. The SCOTT account will be able to use just this VERSION2 edition—to set its current edition in its session to VERSION2 and see the objects as they exist in that edition.
Now I’ll “patch” my application. Logging back in as the DEMO account, I’ll first see what I have in place after altering my session to use the VERSION2 edition, as shown in Listing 1.
Code Listing 1: Setting the VERSION2 edition and selecting procedures
DEMO> alter session 2 set edition = version2; Session altered. DEMO> select object_name, 2 object_type, 3 status, 4 edition_name 5 from user_objects; OBJECT_NAME OBJECT_TYPE STATUS EDITION_NAME ------------- ----------- ------ ------------ MY_PROCEDURE2 PROCEDURE VALID ORA$BASE MY_PROCEDURE PROCEDURE VALID ORA$BASE
So, right now, in the VERSION2 edition, I have both PL/SQL units (MY_PROCEDURE and MY_PROCEDURE2), but as the EDITION_NAME column denotes, they are “inherited” from ORA$BASE. The VERSION2 edition is pointing at the copy in ORA$BASE; it is not a physical copy of the code.
I have to fix two bugs. First, I have a bug in the implementation of MY_PROCEDURE, and I will be replacing that code. Second, I missed a GRANT when I deployed my application—the SCOTT user was supposed to have had the EXECUTE privilege on MY_PROCEDURE2, but somehow that didn’t happen when I last installed the application. I’ll start by updating the code:
DEMO> create or replace 2 procedure my_procedure 3 as 4 begin 5 dbms_output.put_line 6 ( 'I am version 2.0' ); 7 end; 8 / Procedure created. DEMO> select object_name, 2 edition_name 3 from user_objects; OBJECT_NAME EDITION_NAME ------------- ------------ MY_PROCEDURE2 ORA$BASE MY_PROCEDURE VERSION2
As you can see, I still have only two objects—but there are really three physical objects there, as you’ll see in a second. I have MY_PROCEDURE2, which is inherited from ORA$BASE, and I have MY_PROCEDURE, which now physically exists in the VERSION2 edition. This is called actualizing the code—when I have a physical copy of the code in the edition, I have actualized the code in that edition.
Using a new set of views that see across all editions—and end in _AE—I can see the entire state of my DEMO schema. I query the USER_OBJECTS_AE view for OBJECT_NAME and EDITION_NAME:
DEMO> select object_name, 2 edition_name 3 from user_objects_AE; OBJECT_NAME EDITION_NAME ------------- ------------ MY_PROCEDURE ORA$BASE MY_PROCEDURE2 ORA$BASE MY_PROCEDURE VERSION2
Looking across the editions, you can see that I have actualized two physical copies of MY_PROCEDURE now: one in the default ORA$BASE edition and one in the new VERSION2 edition I’m working in. Now I’ll apply the needed EXECUTE grant to SCOTT:
DEMO> grant execute 2 on my_procedure2 3 to scott; Grant succeeded. DEMO> select object_name, 2 edition_name 3 from user_objects; OBJECT_NAME EDITION_NAME ------------- ------------ MY_PROCEDURE2 VERSION2 MY_PROCEDURE VERSION2 DEMO> select object_name, 2 edition_name 3 from user_objects_AE; OBJECT_NAME EDITION_NAME ------------- ------------ MY_PROCEDURE2 ORA$BASE MY_PROCEDURE ORA$BASE MY_PROCEDURE2 VERSION2 MY_PROCEDURE VERSION2
If you look at USER_OBJECTS, you’ll see that I have the two procedures but that both have now been actualized in the VERSION2 edition. By granting the EXECUTE privilege on the MY_PROCEDURE2 stored procedure to SCOTT, I made a physical version of the stored procedure appear in the VERSION2 edition, and I applied the grant in that edition in isolation, without encountering any concurrency issues (blocking/locking issues because someone was running the procedure). Looking at USER_OBJECTS_AE, you can see that there are now four physical objects, two in each edition.
Now when I run the MY_PROCEDURE2 stored procedure in the VERSION2 edition, I see
DEMO> SELECT SYS_CONTEXT
2 ('userenv',
3 'current_edition_name') sc
4 FROM DUAL;
SC
----------------------
VERSION2
DEMO> exec my_procedure2
I am version 2.0
PL/SQL procedure successfully completed.
The version 2.0 code is executed.
Now by simply connecting—and therefore using the database’s default edition (still ORA$BASE)—I instead see
DEMO> connect demo/demo
Connected.
DEMO> SELECT SYS_CONTEXT
2 ('userenv',
3 'current_edition_name') sc
4 FROM DUAL;
SC
-----------------------
ORA$BASE
DEMO> exec my_procedure2
I am version 1.0
PL/SQL procedure successfully completed.
The version 1.0 code is still there, and by default it will be executed.
Further, to see the effect of the EXECUTE privilege I granted to SCOTT, I can connect as SCOTT and try to execute the MY_PROCEDURE2 stored procedure in the default edition and the VERSION2 edition, as shown in Listing 2.
Code Listing 2: Executing my_procedure2 in current and VERSION2 editions
DEMO> connect scott/tiger
Connected.
SCOTT> SELECT SYS_CONTEXT
2 ('userenv',
3 'current_edition_name') sc
4 FROM DUAL;
SC
-----------------------
ORA$BASE
SCOTT> exec demo.my_procedure2
BEGIN demo.my_procedure2; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DEMO.MY_PROCEDURE2' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SCOTT> alter session
2 set edition = version2;
Session altered.
SCOTT> exec demo.my_procedure2
I am version 2.0
PL/SQL procedure successfully completed.
As you can see, when SCOTT is using the ORA$BASE edition, SCOTT cannot see or execute the MY_PROCEDURE2 stored procedure. However, in the VERSION2 edition, SCOTT can see and execute that procedure, so when version 2.0 of the application goes into production, SCOTT will get the ability to execute that procedure by default.
At this point, I’m ready to release this application code to production to be used by the user community at large. A single, simple ALTER DATABASE issued by the DBA sets the current edition for the database to VERSION2, and this code becomes immediately accessible.
I would like to point out that this is just the tip of the iceberg. Here I had to do relatively common operations: replace some existing PL/SQL units and change grants on some objects. I did not take the entire application from version 1.0 to version 2.0, because that would have involved changing tables and other objects that are not “editionable.” So what this column has discussed so far is the very common need to “patch” a system, whereby you need to apply code changes and privilege changes but no physical schema changes such as adding columns, adding indexes, and altering tables. I will be addressing how to approach those tasks in the next column.
Until then, look at Chapter 19 in Oracle Database Advanced Application Developer’s Guide 11g Release 2 (11.2), at www.bit.ly/1m2n0J.
Look, Nothing Up My SleeveHistorically, when you or an application created any database object—a table, an index, anything that consumed storage—the database would create the appropriate segments and allocate at least one initial extent. This extent might be small—say, 64K—but it was allocated.
Although creating one new 64K extent sounds small, if you do something small over and over, it starts to add up. For example, some third-party (or in-house-developed) applications create hundreds or even thousands of tables that never get used, but new corresponding segments and extents are nevertheless created. (The application tables would be used if you used a particular feature or option of the application, but in many of our installations, we do not use these tables.).
Enter a new feature of Oracle Database 11g Release 2: Deferred Segment Creation. It can be set systemwide (via an init.ora parameter), session by session, or even at the data definition language (DDL) statement level. I’ll demonstrate this with a session setting, which is probably what you would use with a third-party install script to have the creation of the initial extents deferred until data is actually placed into each segment.
First, I’ll demonstrate what has happened historically when we’ve created objects in the database and observe just how many segments might be created by a very small, simple CREATE TABLE statement, as shown in Listing 3.
Code Listing 3: Create one table, create many segments
SQL> alter session set 2 deferred_segment_creation=false; Session altered. SQL> create table t1 2 ( x int 3 constraint t1_pk 4 primary key, 5 y int 6 constraint t1_y 7 unique, 8 z clob 9 ) 10 lob( z ) 11 store as t1_z_lob 12 (index t1_z_lobidx); Table created. SQL> select segment_name, 2 extent_id, 3 bytes 4 from user_extents 5 order by segment_name; SEGMENT_NAME EXTENT_ID BYTES ------------ --------- ----- T1 0 65536 T1_PK 0 65536 T1_Y 0 65536 T1_Z_LOB 0 65536 T1_Z_LOBIDX 0 65536
I ran this DDL in a schema that had no created objects whatsoever, so every segment you see was created by that single CREATE TABLE statement. I have segment T1 for the table data, T1_PK for the default index created in support of the primary key, T1_Y for the default index created in support of the unique constraint, T1_Z_LOB for the large object (LOB) segment created in support of the character large object (CLOB) column, and finally T1_Z_LOBIDX for the LOB index created on the LOB segment. A simple CREATE TABLE statement (with three columns) meant the creation of five segments!
Now, let’s see what can happen if I enable Deferred Segment Creation, as shown in Listing 4.
Code Listing 4: DEFERRED_SEGMENT_CREATION=TRUE
SQL> alter session set 2 deferred_segment_creation=true; Session altered. SQL> create table t2 2 ( x int 3 constraint t2_pk 4 primary key, 5 y int 6 constraint t2_y 7 unique, 8 z clob 9 ) 10 lob( z ) 11 store as t2_z_lob 12 (index t2_z_lobidx); Table created. SQL> select segment_name, 2 extent_id, 3 bytes 4 from user_extents 5 order by segment_name; SEGMENT_NAME EXTENT_ID BYTES ------------ --------- ----- T1 0 65536 T1_PK 0 65536 T1_Y 0 65536 T1_Z_LOB 0 65536 T1_Z_LOBIDX 0 65536
As you can see, no new segments have been created, even though I executed a CREATE TABLE statement that was identical to the first (with the exception of the names). That is the goal of Deferred Segment Creation: to defer the allocation of storage until we need it. So, let’s need some storage and see what happens:
SQL> insert into t2 2 values ( 1, 2, 'hello world' ); 1 row created. SQL> select segment_name, 2 extent_id, 3 bytes 4 from user_extents 5 order by segment_name; SEGMENT_NAME EXTENT_ID BYTES ------------ --------- ----- T1 0 65536 T1_PK 0 65536 T1_Y 0 65536 T1_Z_LOB 0 65536 T1_Z_LOBIDX 0 65536 T2 0 65536 T2_PK 0 65536 T2_Y 0 65536 T2_Z_LOB 0 65536 T2_Z_LOBIDX 0 65536 10 rows selected.
Now I see the segments—my INSERT statement caused their creation. (Note that the statements used to create these segments were executed as a recursive transaction and committed immediately. Even if I were to roll back the insert, the segments would remain.)
ASK Tom Tom Kyte answers your most difficult technology questions. Highlights from that forum appear in this column.  asktom.oracle.comREAD more Tom
Edition-Based Redefinition |
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.