Oracle8 Migration Release 8.0 A58243-01 |
Library | Product | Contents | Index

# 7
Migration Issues for the Version 8 ROWIDs

Version 8 ROWIDs embody new internal and external formats that enable you to use two new version 8 features: partitioned tables and tablespace-relative data block addresses (DBAs).

See Also: The Oracle8 Application Developer's Guide and Oracle8 Concepts for more information.

The following migration issues related to the new version 8 ROWIDs are covered in this chapter:

- Migrating Applications and Data
- DBMS_ROWID Package
- Conversion Procedure Examples
- Snapshot Refresh
- Pre-Version 8 Client Compatibility Issues
- ROWID-Related Migration Questions and Answers

## Migrating Applications and Data

ROWIDs can be stored in columns of ROWID type and in columns of character type. However, stored version 7 ROWIDs become invalid after migration of the version 7 database to version 8. Therefore, version 7 ROWIDs must be converted to version 8.

#### Applications

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

Applications that attempt to manufacture or analyze the contents of ROWIDs have to use the new package, DBMS_ROWID, provided in version 8 to deal with the format and contents of the new version 8 ROWIDs. The package contains functions that extract the information that was available directly from a version 7 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 they point to tables that were migrated to version 8. 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 version 8 tables still point to pre-version 8 tables, you do not need to migrate the columns.

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

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

The data migration can be performed only after the system has been opened in version 8. You can migrate different tables at different times or multiple tables in parallel. Make sure the migration is done before the version 7 database file limit is 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 version 8 DBMS_ROWID functionality, to migrate stored ROWIDs from version 7 format to version 8 format.

Data migration by the Migration Utility applies only to ROWIDs stored in a user-defined column. All system-stored ROWIDs (such as in indexes) remain valid after migration by the Migration Utility and do not require specific actions to be migrated.

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

## DBMS_ROWID Package

The DBMS_ROWID PL/SQL package is provided with version 8 and contains the following functionality:

- Creation of ROWIDs in version 7 and version 8 format.
- Interpretation of version 7 and version 8 ROWIDs.
- Conversion between version 7 and version 8 ROWIDs.

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

### ROWID Conversion Types

You must specify the type of ROWID being converted, because the ROWID conversion functions perform the conversion differently depending on whether the ROWID is stored in the user column of ROWID type, or in the user column of (VAR)CHAR type.

For a column of ROWID type, the caller of the conversion procedures must pass the following value as a procedure parameter:

    rowid_convert_internal constant integer := 0;

For a column of (VAR)CHAR type, the caller of the conversion procedures must pass the following value as a procedure parameter:

    rowid_convert_external constant integer := 1;

### ROWID Conversion Functions

The following functions perform the ROWID conversion:

- ROWID_TO_EXTENDED converts a ROWID from the version 7 (restricted) format to the version 8 (extended) format.
- ROWID_TO_RESTRICTED converts a ROWID from the version 8 (extended) to the version 7 (restricted) format.
- ROWID_VERIFY checks whether a given ROWID can be converted from version 7 format to version 8 format.

The following sections contain detailed information about ROWID_TO_EXTENDED and ROWID_VERIFY procedures.

#### ROWID_TO_EXTENDED Conversion Procedure

ROWID_TO_EXTENDED uses 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.

  See Also: "ROWID Conversion Types" on page 7-3 for more information.

ROWID_TO_EXTENDED returns a version 8 (extended) ROWID in External Character format. Its parameters have the following meaning:

- If the schema name and table name for the target table are 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 cause problems 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, but this conversion is very inefficient, and is only recommended as a last resort, when the target table is not known. The user still must know the correct table name when using the converted value.
- If the schema name and table name is given (a preferred approach), ROWID_TO_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 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 a version 8 ROWID is supplied, the data object in the ROWID is verified against the actual data object number (which depends on the table name specification). If these two numbers do not match, the "INVALID ROWID" error appears; otherwise, the original ROWID is returned.

#### ROWID_VERIFY

A ROWID verification procedure, ROWID_VERIFY, is provided with version 8.
This procedure uses the same parameters as ROWID_TO_EXTENDED and returns 0 if the ROWID can be converted successfully to extended format; otherwise, it returns 1.

However, ROWID_VERIFY returns security violation errors, or an "object not found" error, if the user does not have SELECT authority on the underlying table, or if the table does not exist. ROWID_VERIFY can be used to identify bad ROWIDs prior to migration using the ROWID_TO_EXTENDED procedure, because it can identify all bad ROWIDs.

## Conversion Procedure Examples

The following sections provide examples of conversion procedures.

### Example 1

Assume a table SCOTT.T contains a column C of ROWID format. All these ROWIDs reference a single table, SCOTT.T1.

The values of 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 can be found based on the values of some other columns in the same row. For example, assume that the column TNAME of the table T contains 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 to extended format using the following statement:

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

### Example 3

You can 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 this case, 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 accomplished using the following statement:

    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 version 8 ROWID format forces all ROWID snapshots to perform a complete refresh when both master and snapshot sites are upgraded to version 8.

See Also: Oracle8 Replication, Appendix B, "Migration and Compatibility", for more information.

## Pre-Version 8 Client Compatibility Issues

Pre-version 8 clients can access a version 8 database, and version 8 clients can access a pre-version 8 database. Binary and character values of the pseudo column ROWID and of columns of type ROWID that are returned by a pre-version 8 database to a version 8 database are always in version 7 (restricted) format because the pre-version 8 system cannot recognize the extended format ROWID.

The DBMS_ROWID package supplied with version 8 can be used for interpreting the contents of the version 7 ROWIDs and for creating the ROWIDs in version 7 format.

A pre-version 8 client accessing a version 8 database receives the ROWID in version 8 extended format. Therefore, the pre-version 8 client cannot interpret the contents of ROWIDs returned by the version 8 server.

Version 8 snapshot compatibility is restricted to version 7, release 7.1.4 and higher. Further, when a master site is upgraded, the version 8 upgrade script invalidates the logs so that snapshots are forced to do a complete refresh before they can do fast refreshes again.

## ROWID-Related Migration Questions and Answers

Q: Is there any version 8 restriction on a version 7 import client?

A: A version 7 client cannot import a version 8 table with ROWID user column if a row of this table 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 version 8 databases have to be relinked using the patch #380655. More detailed information about this patch is available in the Oracle release 8.0.4 README document.

Q: How do the version 8 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 version 7 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 a version 8 server from a pre-version 8 client or when accessing a pre-version 8 server from a version 8 client.

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

A: The version 8 ROWID fits into the version 7 storage requirements for host variables; therefore, no changes or additional space allocations are necessary.

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 requires 10 bytes versus the 6 bytes required in version 7.

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

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

Q: Will I need to rebuild any indexes?

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

Q: I use ROWID datatype in pre-version 8 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 the following specific known case:

A remote mapped query from a version 7 server to a version 8 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 version 7 server avoids the problem without invoking CHR conversion.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index