Oracle9i Database Migration Release 1 (9.0.1) Part Number A90191-02 |
|
Physical rowids in release 8.0 and higher embody new internal and external formats that enable you to use some new release 8.0 and higher features, including partitioning and global indexes.
See Also:
Oracle9i Application Developer's Guide - Fundamentals and Oracle9i Database Concepts for more information. |
This chapter covers the following topics:
In the rest of this chapter, references to version 8 include all version 8 and Oracle9i releases. Also, the word "rowid" means "physical rowid". This chapter does not discuss the UROWID (universal rowid) datatype. See Chapter 9, "Compatibility and Interoperability", for compatibility issues relating to the UROWID datatype.
Note:
Rowids can be stored in columns of ROWID datatype and in columns of character type. Stored version 7 rowids become invalid after migration of the version 7 database to version 8. Therefore, stored version 7 rowids must be converted to version 8 format.
Applications that do not attempt to manually assemble and disassemble rowids 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 must use the DBMS_ROWID
package, provided in release 8.0 and higher, to deal with the format and contents of the new rowids. This 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.
The columns that contain rowid values (in ROWID
datatype 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 version 7 or version 6 tables, then 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 six to ten 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 or the Oracle Data Migration Assistant 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 or the Oracle Data Migration Assistant, and do not require specific actions to be migrated. Also, indexes are not invalidated because, during migration to version 8 by the Migration utility or the Oracle Data Migration Assistant, indexes can continue to use the restricted ROWID datatype format.
The DBMS_ROWID
PL/SQL package is provided in release 8.0 and higher and contains the following functionality:
Migration of the stored rowids can be accomplished using conversion functions, as described in the following sections.
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
datatype, or in the user column of CHAR
or VARCHAR
datatype.
For a column of ROWID
datatype, 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 CHAR
or VARCHAR
datatype, the caller of the conversion procedures must pass the following value as a procedure parameter:
rowid_convert_external constant integer := 1;
The following functions perform the rowid conversion:
ROWID_TO_EXTENDED
converts a rowid from the Oracle7 (restricted) format to the version 8 (extended) format.
ROWID_TO_RESTRICTED
converts a rowid from the version 8 (extended) to the Oracle7 (restricted) format.
ROWID_VERIFY
checks whether a given rowid can be converted from Oracle7 format to version 8 format.
The following sections contain detailed information about the ROWID_TO_EXTENDED
and ROWID_VERIFY
procedures.
ROWID_TO_EXTENDED
uses the following parameters:
ROWID_TO_EXTENDED
returns a version 8 (extended) rowid in External Character format, and its parameters are interpreted in the following way:
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, then 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. You still must know the correct table name when using the converted value.
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.
The following are examples of conversion procedures for rowids:
Assume a table SCOTT.T contains a column C of ROWID datatype 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);
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);
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 datatype (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;
If the target table for rowids stored in column C is not known, then conversion can be accomplished using the following statement:
UPDATE SCOTT.T SET C = DBMS_ROWID.ROWID_TO_EXTENDED(C,NULL,NULL,0);
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;
The version 8 ROWID datatype format forces all rowid snapshots to perform a complete refresh when both master and snapshot sites are upgraded to version 8.
See Also:
Appendix G, "Migration and Compatibility for Replication Environments", for more information about replication compatibility. |
Version 7 and version 6 clients can access a version 8 database, and version 8 clients can access a version 7 or version 6 database. Binary and character values of the pseudo column ROWID and of columns of datatype ROWID that are returned by a prior database to a version 8 database are always in restricted format, because the prior 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 version 7 or version 6 client accessing a version 8 database receives the rowid in version 8 extended format. Therefore, the client cannot interpret the contents of rowids returned by the version 8 server.
Version 8 snapshot compatibility is restricted to 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.
For backward compatibility, the restricted form of the ROWID is still supported. These ROWIDs exist in massive amounts of Oracle7 data, and the extended form of the ROWID is required only in global indexes on partitioned tables. New tables always get extended ROWIDs.
It is possible for an Oracle7 client to access an Oracle9i database. Similarly, an Oracle9i client can access an Oracle7 Server. A client in this sense can include a remote database accessing a server using database links, as well as a client 3GL or 4GL application accessing a server.
There is more information on the ROWID_TO_EXTENDED function in the Oracle9i Supplied PL/SQL Packages and Types Reference.
The ROWID values that are returned are always restricted ROWIDs. Also, Oracle9i uses restricted ROWIDs when returning a ROWID value to an Oracle7 or earlier server.
The following ROWID functionality works when accessing an Oracle7 Server:
Oracle9i returns ROWIDs in the extended format. This means that you can only:
It is not possible for an Oracle7 client to import an Oracle9i table that has a ROWID column (not the ROWID pseudocolumn), if any row of the table contains an extended ROWID value.
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 a ROWID user column if a row of this table contains the extended rowid value.
Q: Do Forms3 (and Forms4) understand the new ROWID datatype format for base table updates?
A: Forms applications which intend to access version 8 databases have to be relinked using the patch #380655.
Q: How do the version 8 rowid changes affect PRO* precompiled programs?
A: Programs that use rowids but do not rely on their format are not affected. Programs that rely on the version 7 ROWID datatype 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 version 7 or version 6 client or when accessing a version 7 or version 6 server from a version 8 client.
Q: I currently use dynamic SQL and bind as internal ROWID datatype format. Will I need to malloc() more space?
A: Version 8 rowids fit 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 datatype?
A: Columns can still be defined of ROWID datatype. The ROWID column requires 10 bytes instead of the 6 bytes required in version 7. However, in most cases, this is not recommended because the ROWID values must be maintained manually.
Q: I rely on the version 7 ROWID datatype format at present. Will the conversion algorithm be documented?
A: The new version 8 ROWID datatype 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 datatype format need to be rebuilt after data migration.
Q: I use ROWID datatype in older PL/SQL, RPC, or from FORMS. Will this continue to work?
A: The format in which rowids are returned into host variables of ROWID datatype 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 an ORA-3116 error upon a rowid fetch as a type DTYRID (without CHR conversion) through OCI. The following are ways to avoid this problem:
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|