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



  Oracle Tips by Burleson

Creation of Object Tables

Object tables differ from relational tables in that an object table has an object identifier that is system-generated and -maintained.

Object Table CREATE Command Definition

Oracle8, Oracle8i, and Oracle allow the creation of OBJECT tables as well as relational tables. An object table is made up of object types or a combination of standard and object types. Prior to Oracle8i, an object table could not be partitioned. Object tables have OIDs and can be used for a REF call. To use a standard nonobject relational table in a REF, it must be masked with an object view.

The details of the command to create an object table are contained in the SQL reference in the Web documentation on the Oracle Technet site. Here is a simplified version:

See Code Depot

The object_properties clause includes column and attribute information, as well as default value, constraint, and table constraints, along with any REF information. A REF is similar to a foreign key reference in straight relational tables, but instead of holding an actual value used to tie back to the parent table, it holds a pointer (in the form of an OID) to the actual row  in the parent table.

The [[NOT] SUBSTITUTABLE AT ALL LEVELS] clause indicates that the object table types are or are not substitutable at all levels. If a column is substitutable, it means that if a TYPE is a subtype or is somewhere in a chain of subtypes, any type or supertype for which this type is a subtype can be inserted to its place in the table simply by calling the supertype or type’s constructor rather than the subtype constructor. For example, assume we have the PERSON_T ? EMPLOYEE_T ? PARTTIME_EMP_T type hierarchy, where PERSON_T is the main type, EMPLOYEE_T is a subtype of PERSON_T, and PARTTIME_EMP_T is a subtype of the EMPLOYEE_T type. This would indicate that PARTTIME_PERSON_T has its own attributes, plus all those in EMPLOYEE_T, including any the EMPLOYEE_T inherited from PERSON_T. If a table were defined using the PARTTIME_EMP_T type, you could also use the EMPLOYEE_T or PERSON_T constructor methods to insert a subset of data to the same column location. Using NOT SUBSTITUTABLE turns off this capability.

The ON COMMIT clause is used only if the object table is a GLOBAL TEMPORARY object table.

The OID_clause is used to tell Oracle whether or not the OID is SYSTEM GENERATED (the default) or the PRIMARY KEY. The OID_index_clause specifies how the OID is to be indexed, and specifies the storage parameters for the index.

The physical_properties and table_properties clauses are the same as for a relational table.


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.