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.The Shoe DropsFor example, here is an object type definition:
CREATE TYPE Animal_t AS OBJECT (Now, let's create a table of animals:
genus VARCHAR2(40),
species VARCHAR2(40)
);
/CREATE TABLE animals OF Animal_t (If I "describe" this table, I see that it has two columns, one for each of the object type's attributes::
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)
);Name Null? TypeHowever, 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.
------------------------------- -------- ----
GENUS NOT NULL VARCHAR2(40)
SPECIES NOT NULL VARCHAR2(40)SQL> INSERT INTO animals VALUES ('canus','major');Now we can retrieve the OID (along with any other columns we want):1 row created.
SQL> INSERT INTO animals VALUES ('feline', 'minor');
1 row created.
SQL> SELECT SYS_NC_OID$, genus, species FROM animals;It's not easy to see where, but the OIDs do differ (check the 12th position).SYS_NC_OID$ GENUS SPECIES
-------------------------------- ------------ ------------
3B595E6FB73A63B8E0340800202318C8 canus major
3B595E6FB73B63B8E0340800202318C8 feline minorYou 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';(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.REF(A)
--------------------------------------------------------------------------------
00002802093B595E6FB73A63B8E0340800202318C83B595E6FB73963B8E0340800202318C800407F
D10000
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.
...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!