DataCraft, Inc.
DataCraft logo
.
.

DataCraft -> Oracle8 OID migration

What happens to an Oracle8 row object's identifier (OID) when you move the object to another machine?

This question originally came up when I was delivering my PL/SQL 8 seminar to some very sharp folks in New York City in September 1998.  It took a while to get the answer out of Oracle...and it may surprise you.
Background
I have taught for months in the seminar about what happens behind the scenes when you create an object table and populate it.  An "object table" is a table built on a user-defined "object type" in Oracle8.

For example, here is an object type definition:

CREATE TYPE Animal_t AS OBJECT (
  genus VARCHAR2(40),
  species VARCHAR2(40)
);
/
Now, let's create a table of animals:
CREATE TABLE animals OF Animal_t (
   CONSTRAINT animals_pk PRIMARY KEY (genus, species),
   CONSTRAINT genus_not_null CHECK (genus IS NOT NULL),
   CONSTRAINT species_not_null CHECK (species IS NOT NULL)
);
If I "describe" this table, I see that it has two columns, one for each of the object type's attributes::
 Name                            Null?    Type
 ------------------------------- -------- ----
 GENUS                           NOT NULL VARCHAR2(40)
 SPECIES                         NOT NULL VARCHAR2(40)
However, Oracle also creates a hidden column called SYS_NC_OID$ and assigns it a unique value that you can retrieve once you have populated the table.  (There are some other hidden columns that I've never mentioned before...like SYS_NC_ROWINFO$...but that's a topic for another day).  Let's put in a couple of rows.
SQL> INSERT INTO animals VALUES ('canus','major');

1 row created.

SQL> INSERT INTO animals VALUES ('feline', 'minor');

1 row created.

Now we can retrieve the OID (along with any other columns we want):
SQL> SELECT SYS_NC_OID$, genus, species FROM animals;

SYS_NC_OID$                      GENUS        SPECIES
-------------------------------- ------------ ------------
3B595E6FB73A63B8E0340800202318C8 canus        major
3B595E6FB73B63B8E0340800202318C8 feline       minor

It's not easy to see where, but the OIDs do differ (check the 12th position).

You don't usually have any business messing with the OID; Oracle provides a "referencing" operator that encapsulates the OID.  It is this "REF" that we can then insert when we need to associate other objects with this row object in the animals table.  Let's take a look:

SQL> SELECT REF(a) FROM animals a WHERE genus = 'canus';

REF(A)
--------------------------------------------------------------------------------
00002802093B595E6FB73A63B8E0340800202318C83B595E6FB73963B8E0340800202318C800407F
D10000

(By the way, I've highlighted the object's OID that gets presented in the REF.)  So far, so good; everything I've stated above is true, and corresponds to what I've taught in the seminar.
The Shoe Drops
    I've claimed that the reason the REF is so large is that it combines information from the OID with other information about the table, the database, and the machine, which makes it unique across the table, the database, and all other databases.  This is not true.  The REF contains no information about the database.  I don't think the REF contains any added information about the underlying machine, either; any level of uniqueness that accomplished by the REF comes from elsewhere.  Sorry, I was wrong on that score.

    Oracle has told me in the past that the server achieves the inter-machine uniqueness by exploiting a call to a low-level function that uniquely identifies the particular machine.  Oracle more recently told me that "OIDs are generated using a function provided by the network layer".  More significantly, there is apparently no subsequent checking to ensure that an OID is in the "correct" (originating) location.

    So it would appear that the OIDs themselves are globally unique; but if that were the case, why do we need to use a REF at all?  I think the answer to that has to do with performance.  The REF also contains information about what table the object resides in.  Without that information, Oracle would have to look in every object table in the database to resolve the reference!

    Let's look now at the questions I sent to Oracle and their answers, and see if we can read between the lines.

    Question 1: Export/Import.  Given two object tables, table A and B, and table B contains REFs to row object in table A.  The tables are exported and then imported at another site.  What happens to the OIDs and the REFs?

    Oracle speaks: The original OIDs for the row objects are preserved. Also, the OIDs of the object tables are also preserved.  Now, the REFs are constructed from the object table's OID and the row object's OID.  Therefore, in your scenario any tables with REFs to these row objects will continue to point to valid row objects.

    So, apparently, Oracle is making a claim that at least the OID of the table itself (something I didn't cover in the seminar--each object table gets an OID, and you can see it in the OID$ column of the SYS.OBJ$ table) won't be duplicated across databases.  If it were possible to duplicate the table OID across databases, export/import might break.

    In addition, when you export and import an object table, any underlying object types come along for the ride.  That way it maintains consistency between the type and the table.
     

    Question 2: Oracle Parallel Server.  What about the case where the objects option is used on a parallel server (OPS) configuration?  If the OID uniquess via REFs is enforced by encoding some sort of hashed value of the underlying machine, and there are n underlying parallel machines, any one of which might be servicing the request, how does this work?  Or is the objects option not supported on OPS?

    Oracle speaks: System-generated OIDs are generated using a function provided by the network layer that guarantees uniqueness of their value. Objects are supported in OPS mode and I don't believe this is a problem.

    OK, that's good...
     

    Question 3: Failover.  How about failover?  I know there is at least one configurations where Oracle can be made to failover to a secondary CPU.  And I suppose that there is a "failback" facility.  How does Oracle preserve the correctness of REFs in this situation?

    Oracle speaks:  Again, I don't believe this has any bearings on OID generation.

    ...that would follow, since we've learned that Oracle doesn't raise an error if OID is not on its originating system.
     

    Question 4: Object migration.  In the future, will there be some sort of object migration support? That is, if I move a row object to a new home (either a new table, a new database, and/or a new machine), I don't want to have to worry about chasing down and updating all the REFs to that object.  [This question sort of presumes there will be support for remote REFs.]  Any guess as to what version might see these enhancements?

    Oracle speaks: We already ensure that the REFs are preserved on export and import of row objects. But it works because the object tables are also preserved.

    [And, I might add, the underlying types.]

    Moving a row object along with its OID to some other table with a different OID is not a supported operation today (however, you can move the row object without its identity).  If you did this then existing REFs won't be valid because REFs not only contain the OID of the row object but they also contain the OID of the object table that the row object is supposed to live in.

    Furthermore, the REF currently doesn't contain information about which database ot came from.

    However, it is conceivable that in a distributed database session the OID of object tables in a REF can get mapped to the appropriate database (assuming that object tables that are "replicated" across databases with the same OID are not accessed in the same session). Having said this, the design and work on replicated and distributed objects is still very much in infancy, so I cannot give you any assurances. But your expectation doesn't appear to be unreasonable.  I do not know in which release these enhancements will be available.

    So...there you have it.  Distributing persistent objects is a big, bad problem, and there has not been a lot of progress on this issue by Oracle.  As usual, we are staying tuned...

    I gratefully acknowledge my source of this information, a developer at Oracle Corporation.
     

Post Script: Useless (?) Information Department
    By the way, I recently discovered that Oracle has exposed the built-in function that they use to generate OIDs.  Try this at home:
     
      SELECT SYS_OP_GUID() FROM DUAL;


    ...that and $2.00 might get you a good cup of coffee.  Actually it's kind of interesting, because it lets me know that there is absolutely no table-specific information in an OID!

.
.
Last modified June 26, 2000 11:55 AM. Standard trademark disclaimer text goes here.