Tuesday, November 17, 2015

Segment Creation Deferred not Available in Standard Edition

If you use Oracle Standard Edition to create a production database, you need to be sure to create any corresponding test databases in Oracle Standard Edition too. Otherwise you may find that some SQL might be tested successfully, only to fail when you implement it in production. You can see what I mean in the example below. First I created a table with segment creation deferred in the Oracle Enterprise Edition test database:
 
C:\Users\AJ0294094>sqlplus cmp
 
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 16 12:35:04 2015
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
Enter password:
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> create table tab1 (col1 number)
  2  segment creation deferred
  3  /
 
Table created.
 
SQL>
 
Later, when I tried to create the same table in the Oracle Standard Edition production database, Oracle returned an ORA-00439:
 
C:\Users\AJ0294094>sqlplus cmp
 
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 16 12:37:13 2015
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
Enter password:
 
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
 
SQL> create table tab1 (col1 number)
  2  segment creation deferred
  3  /
create table tab1 (col1 number)
*
ERROR at line 1:
ORA-00439: feature not enabled: Deferred Segment Creation

SQL>

No comments: