Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








  Oracle Database Tips by Donald Burleson

The Concept of Object Identifiers (OID) in Oracle9i

Oracle8 introduced the concept of objects as they apply to Oracle. Each object in Oracle8, Oracle8i, and Oracle9i (i.e., table, cluster, etc.) has a 16-byte object identifier. This object identifier (OID) is guaranteed to be globally unique across all databases in your environment. It is a 16-byte, base-64 number that allows for a ridiculously high number of objects to be identified (in the peta-region of countability—a quadrillion? The maximum is 2**128 (340,283,266,920,938,463,463,374,607,431,768,211,456)).

The OID, as well as being a globally unique identifier, is used to construct REFs for nested tables. In some statements involving nested tables, if you don't specifically tell Oracle to bring back the UNREF value (i.e., translate the OID and get the data), you will get a 42- to 46-byte REF number, as useful as that sounds. The number itself is simply an identifier and contains no "intelligence" such as would be in a rowid. The REF value can vary in size between 42 and 46 bytes of internal storage.

Oracle9i Object Types

Before we can begin to discuss objects in Oracle8, Oracle8i, and Oracle9i, we have to address object types. As their name implies, an object type is used to define an object. To bridge the gulf between Oracle7 and Oracle9i, you can think of an object type as a predefined row that you can then use to build Oracle9i objects. The exact format for creating an object table is shown in the SQL reference in the documentation area of the site link on the Wiley Web site. The examples that follow demonstrate how to use Oracle types to create an object table.

Before you can build an object table in Oracle8, 8i or 9i, you must define its types. A table can consist of single columns, types, or a mix, as well as varrays (which are discussed later in this chapter). There are only object TYPEs. Under Oracle8i, the AUTHID clause was added to the CREATE TYPE command. In Oracle9i, the CREATE TYPE command has been extended with the clauses required to support inheritance. Also in Oracle9i, type bodies support overloading, thus providing polymorphism.  Let's look at a simple type definition and how it is used to build an Oracle9i object table. The only instances the AS OBJECT clause is not used is with varray, TABLE, or incomplete type specifications.

See Code Depot

Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.