Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Deferred Constraints

Oracle Tips by Burleson

New in Oracle8, and expanded in Oracle8i, is the concept of deferred constraint. A deferred constraint is one whose enforcement is deferred, or delayed, until it is either manually turned on, or a specific action, such as a transaction end point, is reached. The constraint state clause is used to set a constraint’s deferability. The generalized format for the constraint state clause is:


          [EXCEPTIONS INTO [schema.]table]

If left out, the cons_state_clause, shown here, produces a non-deferrable enabled constraint with a default index (for UNIQUE and PRIMARY KEY constraints) located in the default tablespace. Constraints are either DEFERRABLE or NOT DEFERRABLE. This means, respectively, that they can be changed in their deferrable status using the SET CONSTRAINTS command or that the SET CONSTRAINTS has no effect upon them. An example would be a large loading operation where the primary and foreign keys may not be in sync until the operation completes. If a constraint is DEFERRABLE, it can be either INITIALLY IMMEDIATE, where the constraint is checked with every DML statement, or INITIALLY DEFERRED, which means it is checked at the end of a transaction.

RELY|NORELY. Applies only to constraints on materialized views. This option specifies to the optimizer whether a materialized view to which this constraint applies is eligible for query rewrite. If all constraints have been validated, the materialized view is eligible for query rewrite. If one or more constraints have not been validated, the materialized view may not be eligible for query rewrite unless RELY is specified for the nonvalidated constraints. (Its eligibility is further dependent on the value of the REWRITE_INTEGRITY parameter.)

ENABLE VALIDATE. Specifies that the existing rows must conform to the constraint; ENABLE NOVALIDATE specifies that future rows must meet the restrictions of the constraint but that existing rows can exist as-is (unless they are updated, in which case they must conform).

DISABLE VALIDATE. Disables the constraint, drops the index on the constraint, and disallows any modification of the table. This option is most useful for unique constraints. The disabled validate state of the unique constraint enables you to load data efficiently from a non-partitioned table into a partitioned table using the EXCHANGE PARTITION option of the ALTER TABLE command. DISABLE NOVALIDATE means that Oracle ignores the constraint entirely. If you specify neither VALIDATE nor NOVALIDATE, the default is NOVALIDATE. If you disable a unique or primary constraint that is using a unique index, Oracle drops the unique index and forgets all storage specifications for the index that was dropped. The index storage parameters must be re-specified if the constraint is re-enabled at a future time.

EXCEPTIONS INTO. Allows you to capture the rowids of any rows that do not meet the validation criteria in a table that you specify.

Note: You must create an appropriate exceptions report table to accept information from the EXCEPTIONS option of the enable_disable_clause before enabling the constraint. You can create an exception table by submitting the script UTLEXCPT1.SQL, which creates a table named EXCEPTIONS. You can create additional exceptions tables with different names by modifying and resubmitting the script. (You can use the UTLEXCPT1.SQL script with index-organized tables. You cannot use earlier versions of the script for this purpose.)

A Sample Constraint Naming Convention

There are five types of constraints in an Oracle database (version 8i and 9i); these are:

* Primary key

* Foreign key

* Unique value

* Check value

* Default value

Constraints should be named by function. For primary and foreign key constraints, the constraints should be named the same as their supporting indexes.  For example, for a primary key constraint on the ACCT_PAY table:

   PK_ACCT_PAY  or  P_ACCT_PAY  or  ACCT_PAY_PK, etc.

For the unique value on the vendor code in the ACCT_PAY table, the name could be:

UV_ACCT_PAY_VENDCD  or UV_ACCT_PAY_1 or ACCT_PAY_UV_1 (If it is the first unique value)


   ACCT_PAY_VENDCD_U  (The supporting index would be named the same.)

Within the 32-character limit imposed by Oracle, this name can be as wordy as you wish.

For a foreign key constraint, the constraint indicator, the constraint table, and the foreign table should be included in the name. So, for the foreign key to the VEND table from the ACCT_PAY table, the constraint would be:




It may not always be possible to include all the column names in a foreign key name.  If this is the case, you can make it just a numeric reference or describe the situation:


For other types of constraint, use a simple type indicator, such as CK for check type constraints (e.g., NOT NULL constraints), and DV for default value. Include the table name and the column name. So, for a NOT NULL constraint on the VNDCD column, the naming convention would be: 


You may also use NN for NOT NULL instead of CK, and reserve CK for special check constraints.

Using LOB Datatypes in Relational Tables: An Example

The LOB datatypes were new to Oracle8. The essential types are BLOB, CLOB, NCLOB, and BFILE. The BLOB is a binary large object, and is generally used for pictures, video, or audio-type information storage. The CLOB is a character large object, and is generally used for character-type information storage such as large text files. NCLOB is the national character large object datatype, and is used for multibyte character large objects. BLOBs, CLOBs, and NCLOBs are all internally stored objects (up to 4,000 bytes inline with outer data; anything longer is stored in a separate tablespace with special storage characteristics). The final LOB datatype is actually a pointer used to tell Oracle where an externally stored LOB datafile of any type is stored; the BFILE is used in concert with a DIRECTORY definition. The next example shows a simple table definition using a LOB datatype.

CREATE TABLE internal_graphics (
graphic_id    NUMBER,
graphic_desc  VARCHAR2(30),
graphic_blob  BLOB,
graphic_type  VARCHAR2(10))
LOB (graphic_blob) STORE AS glob_store (
TABLESPACE graphics_data
TABLESPACE graphics_data

The boldface section of this example shows the LOB storage clause; this is a vital part of any table definition using LOB datatypes. If the LOB storage is not specified, the LOB objects are stored in the default tablespace, and severe performance problems may result. In Oracle8, the LOB index was able to be stored in a different tablespace from the specified LOB datatype storage tablespace. In Oracle8i and in Oracle the LOB indexes are stored with the LOB data objects; the tablespace specification for the LOB index is ignored. Though the preceding example shows only a BLOB definition, CLOB and NCLOB are specified identically.

TIP: Remember that the actual number of characters that can be stored in an NCLOB is:

       NCLOB / (# bytes per character)

As stated above, in order to use a BFILE, a DIRECTORY must be specified and specific grants must be given to any users needing to access the DIRECTORY. Look at the next example:

CREATE TABLE bfile_graphics (
graphic_id      NUMBER,
graphic_desc    VARCHAR2(30),
graphic_bfile   BFILE)
TABLESPACE graphics_data

Notice that no LOB storage clause is included in the table definition. All BFILE data (simply a pointer) is stored inline, so no LOB storage is required. What is required before the BFILE can be used is at least one directory specification that points to the location of the external graphics, text, or audio files. The next example shows how this directory entry is created and how the specifically required grants are done.

SQL> CREATE OR REPLACE DIRECTORY gifs_dir AS 'e:\oracle3\ortest1\bfiles'; 

Directory created. 


Grant succeeded.

Once the directory is specified, and the grants made as required (for simplicity, I granted to PUBLIC), the actual pointers to the external files are built using the BFILENAME function call. First, let’s add a column to our table to allow the type of file to be added, along with the other BFILE information:

SQL> desc bfile_graphics
 Name                            Null?    Type
 ------------------------------  -------  ---------------
 GRAPHIC_ID                               NUMBER
 GRAPHIC_DESC                             VARCHAR2(30)

 GRAPHIC_BFILE                            BINARY FILE LOB

SQL> alter table bfile_graphics add graphic_type varchar2(10); 

Table altered. 

SQL> insert into bfile_graphics
  2  values(4,
  3  'April Book of Days Woodcut',bfilename('GIFS_DIR','APRIL.JPG'),'JPEG'); 

1 row created. 

SQL> insert into bfile_graphics
  2  values(8,
  3  'August Book of Days Woodcut',bfilename('GIFS_DIR','AUGUST.JPG'),'JPEG'); 

1 row created. 

SQL> commit; 

Commit complete.

As you can see, the required inputs to the BFILENAME function are:

Directory name.  The name of the created directory passed in as a text string

BFILE name.  The actual BFILE name passed in as a text string

An automated PL/SQL anonymous block to perform the loading of BFILE data is shown in Source 4.2.

SOURCE 4.2  Example of a BFILE loading procedure.

 bfile_dir in  VARCHAR2,
 bfile_lis in  VARCHAR2,
 bfile_int_dir VARCHAR2)
 cur        INTEGER;
 bfile_int  VARCHAR2(100);
 sql_com    VARCHAR2(2000);
 file_proc  INTEGER;
 file_hand  utl_file.file_type;
 file_buff  VARCHAR2(1022);
 file_type  VARCHAR2(4);
   IF file_type='GIF'
    ELSIF file_type='JPG'
    THEN file_type:='JPEG';
   END IF; 
   sql_com:= 'INSERT INTO graphics_table '||CHR(10)||
             'VALUES (graphics_table_seq.NEXTVAL,'||CHR(39)||CHR(39)||
             ', bfilename('||
             ') ,'||CHR(39)||file_type||CHR(39)||')';
    WHEN no_data_found THEN

This procedure depends on a sequence called graphics_table_seq and a single-column list of the files placed in the location specified in the bfile_dir argument; the name of the list file is specified in the bfile_lis argument. The final argument is the internal directory name that corresponds to the actual external file directory. The procedure assumes a three-place file extension of either ‘JPG’ or ‘GIF’, and assigns the graphic type field automatically.  For example, if we had a file called file.lis  that contained a list of all of the graphics files we wanted to load into the bfile_graphics table, our call to this procedure would be:

SQL> EXECUTE get_bfiles(
2  'e:\oracle3\ortest1\bfiles',
3  'file.lis',
4     'GIFS_DIR');

Once the BFILEs are loaded into the database, they can be used to pull the external graphics or LOB files into internal LOB storage areas or simply to pass the BFILE information to an external program for further processing. An example application that uses Java to display stored BLOB data pulled into a database using the DBMS_LOB routines, can be found in my book  Oracle8 Black Book (Coriolis Press, 1998). It is more of a development action than an administration topic. In contrast, the process for loading LOB data from a BFILE into an internal LOB is an administration topic. Source 4.3 shows a PL/SQL routine that will use the loaded BFILE information to move the LOB from external to internal storage.

SOURCE 4.3  Example of a PL/SQL procedure to move BFILEs into BLOBs.

id            NUMBER;
image1        BLOB;
locator       BFILE;
bfile_len     NUMBER;
bf_desc       VARCHAR2(30);
bf_name       VARCHAR2(30);
bf_dir        VARCHAR2(30);
bf_typ        VARCHAR2(4);
ctr           INTEGER;
CURSOR get_id IS
SELECT bfile_id,bfile_desc,bfile_type
FROM graphics_table;
  open get_id;
   FETCH get_id INTO id, bf_desc, bf_typ;
   EXIT WHEN get_id%notfound;
   dbms_output.put_line('ID: '||to_char(id));
   SELECT bfile_loc
     INTO locator
     FROM graphics_table
     WHERE bfile_id=id;
   dbms_output.put_line('Dir: '||bf_dir);
 dbms_output.put_line('ID: '||to_char(id)||' length: '||
   insert into dual_lob(x) values(empty_blob());
   select x into image1 from dual_lob;
   IF bf_desc is null THEN
   END IF;
   insert into internal_graphics values (id,bf_desc,image1,bf_typ,'GENERAL');
   dbms_output.put_line(bf_desc||' Length: '||to_char(bfile_len)||
   ' Name: '||bf_name||' Dir: '||bf_dir||' '||bf_typ);
  delete dual_lob;
END load_lob;

Notice that the code in Source 4.3 pulls the location information from the bfile_graphics table and then uses the DBMS_LOB package procedures to load the BLOB data into internal storage in the internal_graphics table that we created in the BLOB table example. The anonymous PL/SQL block also uses a table called dual_lob, which must be created; it consists simply of a single row, with a null BLOB, CLOB, and BFILE as its attributes:

SQL> desc dual_lob

 Name                            Null?    Type
 ------------------------------  -------  ---------------
 X                                        BLOB
 Y                                        BINARY FILE LOB
 Z                                        CLOB

Before LOB datatypes can be utilized in PL/SQL, they must be initialized to a NULL value, which is where this table is used.

Creation of Partitioned Tables: An Example

A partitioned table has to be a straight relational table in Oracle8; in Oracle8i and in Oracloe9i, though this restriction is removed, you must be careful to allow for all LOB or nested storage to be carried through to all partition storage areas.  A partitioned table is used to split up a table’s data into separate physical and logical areas. This allows for  breaking up a large table into more manageable pieces and for the Oracle8 kernel to more optimally retrieve values.

Let’s look at a short example. Assume we have a sales entity that will store results from sales for the past 12 months. This type of table is a logical candidate for partitioning because:

* Its values have a clear separator (months).

* It has a sliding range (the last year).

* We usually access this type of date by sections (months, quarters, years).

The DDL for this type of table would look like this:

acct_no               NUMBER(5),
sales_person          VARCHAR2(32),
sales_month           NUMBER(2),
amount_of_sale        NUMBER(9,2),
po_number             VARCHAR2(10))
PARTITION BY RANGE (sales_month)
     PARTITION sales_mon_1 VALUES LESS THAN (2),
     PARTITION sales_mon_2 VALUES LESS THAN (3),
     PARTITION sales_mon_3 VALUES LESS THAN (4),
     PARTITION sales_mon_12 VALUES LESS THAN (13),
Here we created the sales table with 13 partitions, one for each month plus an extra to hold improperly entered months (values >12). Always specify a last partition to hold MAXVALUE values for your partition values.

Using Subpartitioning

New to Oracle8i was the concept of subpartitioning.  Subpartitioning allows a table partition to be further subdivided to allow for better spread of large tables. In the next example, we create a table for tracking the storage of data items stored by various departments. We partition by storage date on a quarterly basis and do a further storage subpartition on data_item. The normal activity quarters have four partitions; the slowest has two, and the busiest has eight.

CREATE TABLE test5 (data_item INTEGER, length_of_item INTEGER,
                    storage_type VARCHAR(30),
                    owning_dept NUMBER, storage_date DATE)
   PARTITION BY RANGE (storage_date)
   STORE IN (data_tbs1, data_tbs2,
             data_tbs3, data_tbs4)
   (PARTITION q1_1999
      VALUES LESS THAN (TO_DATE('01-apr-1999', 'dd-mon-yyyy')),
    PARTITION q2_1999
      VALUES LESS THAN (TO_DATE('01-jul-1999', 'dd-mon-yyyy')),
    PARTITION q3_1999
      VALUES LESS THAN (TO_DATE('01-oct-1999', 'dd-mon-yyyy'))
      (SUBPARTITION q3_1999_s1 TABLESPACE data_tbs1,
       SUBPARTITION q3_1999_s2 TABLESPACE data_tbs2),
    PARTITION q4_1999
      VALUES LESS THAN (TO_DATE('01-jan-2000', 'dd-mon-yyyy'))
      STORE IN (q4_tbs1, q4_tbs2, q4_tbs3, q4_tbs4,
                q4_tbs5, q4_tbs6, q4_tbs7, q4_tbs8),
    PARTITION q1_2000
      VALUES LESS THAN (TO_DATE('01-apr-2000', 'dd-mon-yyyy'))):

The items to notice in this code example are that the partition-level commands override the default subpartitioning commands; thus, partition Q3_1999 only gets two subpartitions instead of the default of four, and partition Q4_1999 gets eight. The main partitions are divided based on date logic, while the subpartitions use a hash value, calculated from a VARCHAR2 value. The subpartitioning is done in round-robin fashion, depending on the hash value that was calculated filling the subpartitions equally.

Note that no storage parameters were specified in the example. I created the tablespaces such that the default storage for the tablespaces matched what I needed for the subpartitions. This made the example code easier to write and to visualize the process involved.

Using List Partitioning

List partitioning, new in Oracle, allows the DBA or designer to place rows into specific partitions based on the value of a particular column; for example:

CREATE TABLE world_distributors
   ( distributor_id             NUMBER(6)
   , manager_first_name         VARCHAR2(20)
   , manager_last_name          VARCHAR2(20)
   , office_address             OFFICE_ADDRESS_TYP
   , nls_territory              VARCHAR2(30)
   , office_email               VARCHAR2(30))
   PARTITION BY LIST (nls_territory) (

List partitioning provides expanded capabilities over RANGE, HASH, or combination partitioning schemes, and can be used in combination with HASH to further spread IO across disk assets. There are, however, a number of restrictions on list partitioning:

* You can specify only one partitioning key in the column list, and it cannot be a LOB column.

* If the partitioning key is an object type column, you can partition on only one attribute of the column type.

* Each partition value in the VALUES clause must be unique among all partitions of the table.

* You cannot list partition an index-organized table.

* The string comprising the list of values for each partition can be up to 4K bytes. The total number of partition values for all partitions cannot exceed (64K-1).

* The MAXVALUE keyword is not applicable to list partitions and cannot be specified.

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter