Oracle8 Server Migration
Release 8.0
A54650_01

Library

Product

Contents

Index

7
Migration Issues for the New Oracle8 ROWIDs

Oracle8 ROWIDs embody new internal and external formats that enable use of two new Oracle8 features, partitioned tables and tablespace-relative data block addresses (DBAs). The new Oracle8 ROWID formats are described in "ROWID Enhancement" on page A-13. For further information see the Oracle8 Server Application Developer's Guide and Oracle8 Server Concepts.

Several migration issues related to the new Oracle8 ROWIDs are discussed in this chapter:

Migrating Applications and Data

ROWIDs can be stored in columns of ROWID type and in columns of character type. Stored Oracle7 ROWIDs become invalid after migration of the Oracle7 database to Oracle8. Explicit conversion of Oracle7 ROWIDs to Oracle8 is required.

Applications

Applications that do not attempt to manually assemble/disassemble ROWIDs need not be changed or recompiled because the new ROWIDs fit the current storage requirements for host variables.

Applications that attempt to manufacture ROWIDs or to analyze their contents have to use the new package, DBMS_ROWID, provided in Oracle8 to deal with the format and contents of the new Oracle8 ROWIDs. The package contains functions that extract the information that was available directly from an Oracle7 ROWID (including file and block address) plus the data object number.

Data

The columns that contain ROWID values (in ROWID format or in character format) must be migrated if the tables to which they point are migrated to Oracle8. Otherwise, it will not be possible to retrieve any rows using their stored values. On the other hand, if the ROWID values stored in the Oracle8 tables still point to pre-Oracle8 tables, migration of the columns is not needed.

Columns are migrated in two stages: definition migration and data migration.

Column definition is automatically adjusted during Oracle7 to Oracle8 dictionary migration. The maximum size of ROWID user columns is increased to the size of the extended disk ROWIDs, basically changing the LENGTH column of COL$ for ROWID columns from 6 to 10 bytes.

The data migration must be performed by the user after the system has been opened in Oracle8. The user may choose to migrate different tables at different times or to migrate multiple tables in parallel. Make sure the migration is done before the Oracle7 database file limit might be exceeded, thereby guarding against the creation of ambiguous block addresses.

You can use existing ROWID refresh procedures that are available at your installation (or the Oracle8 DBMS_ROWID functionality) to migrate stored ROWIDs from Oracle 7 format to Oracle 8 format.

Note: This discussion of data migration by the Migration Utility applies only to ROWIDs stored in a user-defined column. All system-stored ROWIDs (for example, in indexes) remain valid after migration by the Migration Utility and do not require specific user actions to be migrated.

Note: Importing a column containing ROWIDs should produce a message warning that special attention might be required to reestablish the validity of the ROWIDs. Such attention is necessary for all ROWIDs being imported. Thus, migration to Oracle8 by Export/Import would require special attention to every column containing ROWIDs (not just to user-defined columns).

DBMS_ROWID Package

The DBMS_ROWID PL/SQL package is provided in Oracle8. It contains the following functionality:

Creation of ROWIDs in Oracle7 and Oracle8 format.
Interpretation of Oracle7 and Oracle8 ROWIDs.
Conversion between Oracle7 and Oracle8 ROWIDs.

Migration of the stored ROWIDs can be accomplished using conversion functions, as described in the following sections.

ROWID Conversion Types

The ROWID conversion functions have to know the type of ROWID being converted, because the conversion is performed differently depending on whether the ROWID is stored in the user column of ROWID type, or in a the user column of (VAR)CHAR type. Therefore, the caller of the conversion procedures must pass one of the following values as a procedure parameter:

rowid_convert_internal constant integer := 0; [for column of ROWID type]
rowid_convert_external constant integer := 1; [for column of character type]

ROWID Conversion Functions.

The following functions perform the ROWID conversion:

ROWID_TO_EXTENDED converts a ROWID from the Oracle7(restricted) format to the Oracle8 (extended) format.
ROWID_TO_RESTRICTED converts a ROWID from the Oracle8(extended) to the Oracle7(restricted) format.
ROWID_VERIFY checks whether a given ROWID can be converted from Oracle7 format to Oracle8 format.

A detailed description of ROWID_TO_EXTENDED and ROWID_VERIFY procedures follows.

ROWID_TO_EXTENDED Conversion Procedure

ROWID_TO_EXTENDED has the following parameters:

ROWID to be converted (in External Character format).
Schema Name - schema name of the table which contains a row whose ROWID will be converted to the extended format.
Table Name - table name of the table which contains a row whose ROWID will be converted to the extended format.
Conversion Type - the type of ROWID being converted. The value is rowid_convert_internal for ROWIDs stored in a column of ROWID type, and rowid_convert_external for ROWIDs stored in columns of (var)character type.

ROWID_TO_EXTENDED returns an Oracle8 (extended) ROWID in the External Character format. Parameters have the following meaning:

If the schema name and table name for the target table is not specified (NULL), ROWID_TO_EXTENDED attempts to fetch the page specified by the ROWID to be converted. It will treat the file number stored in this ROWID as the absolute file number, which can create a problem if the file has been dropped and its number has been reused prior to the migration. If the fetched page belongs to a valid table, the ROWID will be converted to an extended format using the Data Object ID of this table. This is very inefficient, and is only recommended as the last resort, when the target table is not known. The user still must know the correct table name at the time of using the converted value.
If the schema name and table name is given (a preferred approach), ROWIDTO_EXTENDED will verify SELECT authority on the table and convert the ROWID to an extended format using the Data Object Number of this table. There is no guarantee that the converted ROWID actually references a real row in this table, neither at the time of conversion, nor at the time when the ROWID is actually used.
If NULL value is supplied for the ROWID, the procedure ignores the table specification and returns a NULL value.
If a value of `0', or, more generally `<n>0.<m>0.<p>0' is supplied for ROWID, the table name is ignored and a restricted ROWID of the form `00000000.0000.0000' is returned.
If an Oracle8 ROWID is supplied, the data object in the ROWID is verified against the actual data object number (which is computed depending on the table name specification), as above. If these two numbers do not match, the "INVALID ROWID" error is raised; otherwise, the original ROWID is returned.

ROWID_VERIFY

A ROWID verification procedure, ROWID_VERIFY, is provided in Oracle8. This procedure has the same parameters as ROWID_TO_EXTENDED and returns 0 if the ROWID can be successfully converted to extended format, or returns 1 otherwise. It will, however, return security violation errors, or an "object not found" error, if the user does not have SELECT authority on the underlying table, or the table does not exist. ROWID_VERIFY can be used to identify bad ROWIDs prior to migration using ROWID_TO_EXTENDED procedure, because it can identify all bad ROWIDs.

Conversion Procedure Examples

Example 1

Assume a table SCOTT.T contains a column C of ROWID format. All these ROWIDs reference a single table, SCOTT.T1. Then the values of the column C can be converted to extended format using the following statement:

UPDATE SCOTT.T SET C = DBMS_ROWID.ROWID_TO_EXTENDED(C,`SCOTT','T1',0);

Example 2

In a more general situation, ROWIDs stored in column C may reference different tables, but the table name is computable based on the values of some other columns in the same row. For example, let the column TNAME of the table T contain a name of the table which is referenced by a ROWID from column C. In this case, the values in column C can be converted as follows:

UPDATE SCOTT.T SET C = DBMS_ROWID.ROWID_TO_EXTENDED(C,`SCOTT',TNAME,0);

Example 3

It is possible to use the ROWID_TO_EXTENDED function in the CREATE AS SELECT statement. This use may be desirable in some cases because conversion can increase the size of the user column of ROWID type (typically from 6 bytes to 10 bytes, although this depends on a specific port) which may create indirect rows. In such a situation, CREATE AS SELECT may be a better choice than UPDATE:

CREATE TABLE SCOTT.TNEW (A, B, C) AS SELECT A, B, 
DBMS_ROWID.ROWID_TO_EXTENDED(C, `SCOTT','T1',0) FROM SCOTT.T;

Example 4

If the target table for ROWIDs stored in column C is not known, conversion can be performed as follows:

UPDATE SCOTT.T SET C = DBMS_ROWID.ROWID_TO_EXTENDED(C, NULL,NULL,0);

Example 5

The following SQL statement may be used to find bad ROWIDs prior to conversion:

SELECT ROWID,C FROM SCOTT.T WHERE 
                                                          DBMS_ROWID.ROWID_VERIFY(C,NULL,NULL,0)=1;

Snapshot Refresh

The Oracle8 ROWID format forces all ROWID snapshots to perform a complete refresh when both master and snapshot sites are upgraded to Oracle8. See Oracle8 Server Replication, Appendix B, "Migration and Compatibility" for more information.

Pre-Oracle8 Client Compatibility Issues

Pre-Oracle8 clients can access an Oracle8 database and visa versa. Binary and character values of the pseudo column ROWID and of columns of type ROWID that are returned by a pre-Oracle8 database to an Oracle8 database are always in Oracle7 (restricted) format because the pre-Oracle8 system cannot know about extended format ROWID.

The DBMS_ROWID package supplied with Oracle8 can be used for interpreting the contents of the Oracle7 ROWIDs and for creating the ROWIDs in Oracle7 format.

A pre-Oracle8 client accessing an Oracle8 database receives the ROWID in Oracle8 extended format. The pre-Oracle8 client cannot, therefore, interpret the contents of ROWIDs returned by the Oracle8 server.

Oracle8 snapshot compatibility is restricted to Oracle7.1.4 and higher. Further, when a master site is upgraded, the Oracle8 upgrade script invalidates the logs so that snapshots are forced to do a complete refresh before they are allowed to do fast refreshes again.

ROWID-Related Migration Questions and Answers

Q: Is there any Oracle8 restriction on an Oracle7 import client?

A: An Oracle7 client cannot import an Oracle8 table with ROWID user column if a row of this table actually contains the extended ROWID value.

Q: Do Forms3 (and Forms4) understand the new ROWID format for base table updates?

A: Forms applications which intend to access Oracle8 databases have to be relinked using a patch #380655. More detailed information about this patch is available from the Oracle 8.0.3 README document.

Q: How do the Oracle8 ROWID changes affect PRO* precompiled programs?

A: Programs that use ROWID but do not rely on its format are not affected. Programs that rely on the Oracle7 ROWID format must be modified to use the new package, DBMS_ROWID.

Q: Do "WHERE CURRENT of CURSOR" operations still work?

A: Yes, even when accessing an Oracle8 server from a pre-Oracle8 client or when accessing a pre-Oracle8 server from a Oracle8 client.

Q: I currently use dynamic SQL and bind as internal ROWID format. Will I need to malloc() more space?

A: The Oracle8 ROWID fits into the Oracle7 storage requirements for host variables, and therefore no change or more space are needed.

Q: Can I still define a column of my table to be of ROWID type?

A: Columns can still be defined of ROWID type. ROWID column will require 10 bytes versus the 6 bytes required in Oracle7.

Q: I rely on the Oracle7 ROWID format at present. Will the conversion algorithm be documented?

A: The new Oracle8 ROWID format is not documented for such use. But Oracle8 provides the DBMS_ROWID (PL/SQL) package to interpret Oracle8 ROWID contents.

Q: Will I need to rebuild any indexes?

A: Only indexes built on a column that stores the old ROWID format will need to be rebuilt after data migration.

Q: I use ROWID datatype in pre-Oracle8 PL/SQL, RPC, or from FORMS. Will this continue to work?

A: The format in which ROWIDs are returned into host variables of ROWID type will be the same, and generally no change is needed, except in one specific known case, as follows:

A remote mapped query from an Oracle7 server to an Oracle8 database across a dblink (considered a heterogeneous dblink) terminates with error ORA3116 upon a ROWID fetch as a type DTYRID (without CHR conversion) through OCI.

Using ROWID fetches as type DTYCHR instead invokes an implicit conversion and avoids the problem.
Using SQLT_RID and a patch (available from Oracle) on the Oracle7 server avoids the problem without invoking CHR conversion.