20 Managing Tables

Design Tables Before Creating Them

Usually, the application developer is responsible for designing the elements of an application, including the tables. Database administrators are responsible for establishing the attributes of the underlying tablespace that will hold the application tables. Either the DBA or the applications developer, or both working jointly, can be responsible for the actual creation of the tables, depending upon the practices for a site.

Working with the application developer, consider the following guidelines when designing tables:

• Use descriptive names for tables, columns, indexes, and clusters.

• Be consistent in abbreviations and in the use of singular and plural forms of table names and columns.

• Document the meaning of each table and its columns with the COMMENT command.

• Normalize each table.

• Select the appropriate data type for each column.

• Consider whether your applications would benefit from adding one or more virtual columns to some tables.

• Define columns that allow nulls last, to conserve storage space.

• Cluster tables whenever appropriate, to conserve storage space and optimize performance of SQL statements.

Before creating a table, you should also determine whether to use integrity constraints. Integrity constraints can be defined on the columns of a table to enforce the business rules of your database automatically.

Specify the Type of Table to Create

Here are the types of tables that you can create:

Specify the Location of Each Table

It is advisable to specify the TABLESPACE clause in a CREATE TABLE statement to identify the tablespace that is to store the new table. For partitioned tables, you can optionally identify the tablespace that is to store each partition. Ensure that you have the appropriate privileges and quota on any tablespaces that you use. If you do not specify a tablespace in a CREATE TABLE statement, the table is created in your default tablespace.

When specifying the tablespace to contain a new table, ensure that you understand implications of your selection. By properly specifying a tablespace during the creation of each table, you can increase the performance of the database system and decrease the time needed for database administration.

The following situations illustrate how not specifying a tablespace, or specifying an inappropriate one, can affect performance:

• If users' objects are created in the SYSTEM tablespace, the performance of the database can suffer, since both data dictionary objects and user objects must contend for the same data files. Users' objects should not be stored in the SYSTEM tablespace. To avoid this, ensure that all users are assigned default tablespaces when they are created in the database.

• If application-associated tables are arbitrarily stored in various tablespaces, the time necessary to complete administrative operations (such as backup and recovery) for the data of that application can be increased.

Consider Parallelizing Table Creation

You can use parallel execution when creating tables using a subquery (AS SELECT) in the CREATE TABLE statement. Because multiple processes work together to create the table, performance of the table creation operation is improved.

Parallelizing table creation is discussed in the section "Parallelizing Table Creation".

Consider Using NOLOGGING When Creating Tables

To create a table most efficiently use the NOLOGGING clause in the CREATE TABLE...AS SELECT statement. The NOLOGGING clause causes minimal redo information to be generated during the table creation. This has the following benefits:

• Space is saved in the redo log files.

• The time it takes to create the table is decreased.

• Performance improves for parallel creation of large tables.

The NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader and direct load INSERT operations are not logged. Subsequent DML statements (UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING attribute of the table and generate redo.

If you cannot afford to lose the table after you have created it (for example, you will no longer have access to the data used to create the table) you should take a backup immediately after the table is created. In some situations, such as for tables that are created for temporary use, this precaution may not be necessary.

In general, the relative performance improvement of specifying NOLOGGING is greater for larger tables than for smaller tables. For small tables, NOLOGGING has little effect on the time it takes to create a table. However, for larger tables the performance improvement can be significant, especially when also parallelizing the table creation.

Consider Using Table Compression

As your database grows in size, consider using table compression. Compression saves disk space, reduces memory use in the database buffer cache, and can significantly speed query execution during reads. Compression has a cost in CPU overhead for data loading and DML. However, this cost is offset by reduced I/O requirements. Because compressed table data stays compressed in memory, compression can also improve performance for DML operations, as more rows can fit in the database buffer cache (and flash cache if it is enabled).

Table compression is completely transparent to applications. It is useful in decision support systems (DSS), online transaction processing (OLTP) systems, and archival systems.

You can specify compression for a tablespace, a table, or a partition. If specified at the tablespace level, then all tables created in that tablespace are compressed by default.

Oracle Database supports several methods of table compression. They are summarized in Table 20-1.

When you use basic table compression, warehouse compression, or archive compression, compression only occurs when data is bulk loaded into a table.

When you use advanced row compression, compression occurs while data is being inserted, updated, or bulk loaded into a table. Operations that permit compression include:

• Single-row or array inserts and updates

  Inserts and updates are not compressed immediately. When updating an already compressed block, any columns that are not updated usually remain compressed. Updated columns are stored in an uncompressed format similar to any uncompressed block. The updated values are re-compressed when the block reaches a database-controlled threshold. Inserted data is also compressed when the data in the block reaches a database-controlled threshold.

• The following direct-path INSERT methods:

  • Direct path SQL*Loader

  • CREATE TABLE AS SELECT statements

  • Parallel INSERT statements

  • INSERT statements with an APPEND or APPEND_VALUES hint

Basic table compression compresses data inserted by direct path load only and supports limited data types and SQL operations. Advanced row compression is intended for OLTP applications and compresses data manipulated by any SQL operation.

Warehouse compression and archive compression achieve the highest compression levels because they use Hybrid Columnar Compression technology. Hybrid Columnar Compression technology uses a modified form of columnar storage instead of row-major storage. This enables the database to store similar data together, which improves the effectiveness of compression algorithms. For data that is updated, Hybrid Columnar Compression uses more CPU and moves the updated rows to row format so that future updates are faster. Because of this optimization, you should use it only for data that is updated infrequently.

The higher compression levels of Hybrid Columnar Compression are achieved only with data that is direct-path inserted. Conventional inserts and updates are supported, but cause rows to be moved from columnar to row format, and reduce the compression level. You can use Automatic Data Optimization (ADO) policies to move these rows back to the desired level of Hybrid Columnar Compression automatically.

Regardless of the compression method, DELETE operations on a compressed block are identical to DELETE operations on a non-compressed block. Any space obtained on a data block, caused by SQL DELETE operations, is reused by subsequent SQL INSERT operations. With Hybrid Columnar Compression technology, when all the rows in a compression unit are deleted, the space in the compression unit is available for reuse.

Table 20-2 lists characteristics of each table compression method.

You specify table compression with the COMPRESS clause of the CREATE TABLE statement. You can enable compression for an existing table by using these clauses in an ALTER TABLE statement. In this case, only data that is inserted or updated after compression is enabled is compressed. Using the ALTER TABLE MOVE statement also enables compression for data that is inserted and updated, but it compresses existing data as well. Similarly, you can disable table compression for an existing compressed table with the ALTER TABLE...NOCOMPRESS statement. In this case, all data that was already compressed remains compressed, and new data is inserted uncompressed.

The COLUMN STORE COMPRESS FOR QUERY HIGH option is the default data warehouse compression mode. It provides good compression and performance when using Hybrid Columnar Compression on Exadata storage. The COLUMN STORE COMPRESS FOR QUERY LOW option should be used in environments where load performance is critical. It loads faster than data compressed with the COLUMN STORE COMPRESS FOR QUERY HIGH option.

The COLUMN STORE COMPRESS FOR ARCHIVE LOW option is the default archive compression mode. It provides a high compression level and is ideal for infrequently-accessed data. The COLUMN STORE COMPRESS FOR ARCHIVE HIGH option should be used for data that is rarely accessed.

A compression advisor, provided by the DBMS_COMPRESSION package, helps you determine the expected compression level for a particular table with a particular compression method.

CREATE TABLE orders  ...  ROW STORE COMPRESS ADVANCED;

CREATE TABLE sales_history  ...  ROW STORE COMPRESS BASIC;

CREATE TABLE sales_history  ...  ROW STORE COMPRESS;

INSERT /*+ APPEND */ INTO sales_history SELECT * FROM sales WHERE cust_id=8890;
COMMIT;

CREATE TABLE sales_history  ...  COLUMN STORE COMPRESS FOR QUERY;

CREATE TABLE sales_history  ...  COLUMN STORE COMPRESS FOR ARCHIVE;

ALTER TABLE ... MOVE PARTITION ... ROW STORE COMPRESS ADVANCED...

SQL> SELECT table_name, compression, compress_for FROM user_tables;
 
TABLE_NAME       COMPRESSION   COMPRESS_FOR
---------------- ------------  -----------------
T1               DISABLED
T2               ENABLED       BASIC
T3               ENABLED       ADVANCED
T4               ENABLED       QUERY HIGH
T5               ENABLED       ARCHIVE LOW

SQL> SELECT table_name, partition_name, compression, compress_for
  FROM user_tab_partitions;

TABLE_NAME  PARTITION_NAME   COMPRESSION   COMPRESS_FOR
----------- ---------------- -----------   ------------------------------
SALES       Q4_2004          ENABLED       ARCHIVE HIGH
  ...
SALES       Q3_2008          ENABLED       QUERY HIGH
SALES       Q4_2008          ENABLED       QUERY HIGH
SALES       Q1_2009          ENABLED       ADVANCED
SALES       Q2_2009          ENABLED       ADVANCED

SELECT DECODE(DBMS_COMPRESSION.GET_COMPRESSION_TYPE(
                 ownname    => 'HR', 
                 objname    => 'EMPLOYEES', 
                 subobjname => '',
                 row_id     => 'AAAVEIAAGAAAABTAAD'), 
   1,  'No Compression',
   2,  'Advanced Row Compression', 
   4,  'Hybrid Columnar Compression for Query High',
   8,  'Hybrid Columnar Compression for Query Low',
   16, 'Hybrid Columnar Compression for Archive High',
   32, 'Hybrid Columnar Compression for Archive Low',
   4096, 'Basic Table Compression', 
   'Unknown Compression Type') compression_type
FROM DUAL;

Managing Table Compression Using Enterprise Manager Cloud Control

Enterprise Manager displays several central compression pages that summarize the compression features at the database and tablespace levels and contains links to different compression pages. The Compression pages display summaries of the compressed storage space at the database level and the tablespace level.

On the database level, the Compression Summary for Database page shows the total database size (total size of all the objects, both compressed and uncompressed), the total size of compressed objects in the database, the total size of uncompressed objects in the database and the ratio of the total size of compressed objects to the total database size. This provides you with a general idea on how much storage space within a database is compressed. You can then take action based on the information displayed.

Likewise on the tablespace level, the Compression Summary for Tablespace page shows the total tablespace size (total size of all the objects, both compressed and uncompressed), the total size of compressed objects in the tablespace, the total size of uncompressed objects in the tablespace and the ratio of the total size of compressed objects to the total tablespace size.

You can use the Compression feature to perform the following tasks:

• View a summary of the compressed storage space for the top 100 tablespaces at the database level or the top 100 objects at the tablespace level. You can view a summary on how much storage space is compressed within each of top 100 tablespaces that use the most database storage, including the total size of the tablespace, the compressed size of a tablespace, the uncompressed size of tablespace, and the percentage of compressed storage within a tablespace. You can the perform compression tasks based on the information displayed.

• View the storage size that is compressed by each compression type for four object types: Table, Index, LOB (Large Objects), and DBFS (Oracle Database File System).

• Calculate the compression ratio for a specific object.

• Compress an object (tablespace, table, partition or LOB). This allows you to save storage space. You can run the Compression Advisor to ascertain how much space can be saved and then perform the compression action on the object.

• View compression advice from the Segment Advisor. You can access a link to the Segment Advisor to compress segments.

Consider Using Segment-Level and Row-Level Compression Tiering

Segment-level compression tiering enables you to specify compression at the segment level within a table. Row-level compression tiering enables you to specify compression at the row level within a table. You can use a combination of these on the same table for fine-grained control over how the data in the table is stored and managed.

As user modifications to segments and rows change over time, it is often beneficial to change the compression level for them. For example, some segments and rows might be modified often for a short period of time after they are added to the database, but modifications might become less frequent over time.

You can use compression tiering to specify which segments and rows are compressed based on rules. For example, you can specify that rows that have not been modified in two weeks are compressed with advanced row compression. You can also specify that segments that have not been modified in six months are compressed with warehouse compression.

The following prerequisites must be met before you can use segment-level and row-level compression tiering:

• The HEAT_MAP initialization parameter must be set to ON.

• The COMPATIBLE initialization parameter must be set to 12.0.0 or higher.

To use segment-level compression tiering or row-level compression tiering, execute one of the following SQL statements and include an Automatic Data Optimization (ADO) policy that specifies the rules:

• CREATE TABLE

• ALTER TABLE

ALTER TABLE oe.orders ILM ADD POLICY
  ROW STORE COMPRESS ADVANCED 
  ROW 
  AFTER 14 DAYS OF NO MODIFICATION;

ALTER TABLE oe.order_items ILM ADD POLICY 
  COLUMN STORE COMPRESS FOR ARCHIVE HIGH 
  SEGMENT 
  AFTER 6 MONTHS OF NO ACCESS;

Consider Using Attribute-Clustered Tables

An attribute-clustered table is a heap-organized table that stores data in close proximity on disk based on user-specified clustering directives. The directives are as follows:

• The CLUSTERING ... BY LINEAR ORDER directive orders data in a table according to specified columns.

  BY LINEAR ORDER clustering, which is the default, is best when queries qualify the prefix of columns specified in the clustering clause. For example, if queries of sh.sales often specify either a customer ID or both customer ID and product ID, then you could cluster data in the table using the linear column order cust_id, prod_id. Note that the specified columns can be in multiple tables.

• The CLUSTERING ... BY INTERLEAVED ORDER directive orders data in one or more tables using a special algorithm, similar to a z-order function, that permits multicolumn I/O reduction.

  BY INTERLEAVED ORDER clustering is best when queries specify a variety of column combinations. The columns can be in one or more tables. For example, if queries of sh.sales specify different dimensions in different orders, then you could cluster data in the sales table according to columns in these dimensions.

Attribute clustering is available for the following types of operations:

• Direct-path INSERT

  See "Improving INSERT Performance with Direct-Path INSERT".

• Online redefinition

  See "Redefining Tables Online".

• Data movement operations, such as ALTER TABLE ... MOVE operations

  See "Moving a Table to a New Segment or Tablespace".

• Partition maintenance operations that create new segments, such as ALTER TABLE ... MERGE PARTITION operations

  See Oracle Database VLDB and Partitioning Guide.

Attribute clustering is ignored for conventional DML.

An attribute-clustered table has the following advantages:

• More optimized single block I/O is possible for table lookups when attribute clustering is aligned with common index access. For example, optimized I/O is possible for an index range scan on the leading column you chose for attribute clustering.

• Data ordering enables more optimal pruning for Exadata storage indexes and in-memory min/max pruning.

• You can cluster fact tables based on joined attributes from other tables.

• Attribute clustering can improve data compression and in this way indirectly improve table scan costs. When the same values are close to each other on disk, the database can more easily compress them.

Attribute-clustered tables are often used in data warehousing environments, but they are useful in any environment that can benefit from these advantages. Use the CLUSTERING clause in a CREATE TABLE SQL statement to create an attribute-clustered table.

Consider Using Zone Maps

A zone is a set of contiguous data blocks on disk. A zone map tracks the minimum and maximum of specified columns for all individual zones.

When a SQL statement contains predicates on columns stored in a zone map, the database compares the predicate values to the minimum and maximum stored in the zone to determine which zones to read during SQL execution. The primary benefit of zone maps is I/O reduction for table scans. I/O is reduced by skipping table blocks that are not needed in the query result. Use the CREATE MATERIALIZED ZONEMAP SQL statement to create a zone map.

Whenever attribute clustering is specified on a table, you can automatically create a zone map on the clustered columns. Due to clustering, minimum and maximum values of the columns are correlated with consecutive data blocks in the attribute-clustered table, which allows for more effective I/O pruning using the associated zone map.

Consider Storing Tables in the In-Memory Column Store

The In-Memory Column Store is an optional portion of the system global area (SGA) that stores copies of tables, table partitions, and other database objects that is optimized for rapid scans. In the In-Memory Column Store, table data is stored by column rather than row in the SGA.

Understand Invisible Columns

You can make individual table columns invisible. Any generic access of a table does not show the invisible columns in the table. For example, the following operations do not display invisible columns in the output:

• SELECT * FROM statements in SQL

• DESCRIBE commands in SQL*Plus

• %ROWTYPE attribute declarations in PL/SQL

• Describes in Oracle Call Interface (OCI)

You can use a SELECT statement to display output for an invisible column only if you explicitly specify the invisible column in the column list. Similarly, you can insert a value into an invisible column only if you explicitly specify the invisible column in the column list for the INSERT statement. If you omit the column list in the INSERT statement, then the statement can only insert values into visible columns.

You can make a column invisible during table creation or when you add a column to a table, and you can later alter the table to make the same column visible. You can also alter a table to make a visible column invisible.

You might use invisible columns if you want to make changes to a table without disrupting applications that use the table. After you add an invisible column to a table, queries and other operations that must access the invisible column must refer to the column explicitly by name. When you migrate the application to account for the invisible columns, you can make the invisible columns visible.

Virtual columns can be invisible. Also, you can use an invisible column as a partitioning key during table creation.

The following restrictions apply to invisible columns:

• The following types of tables cannot have invisible columns:

  • External tables

  • Cluster tables

  • Temporary tables

• Attributes of user-defined types cannot be invisible.

CREATE TABLE mytable (a INT, b INT INVISIBLE, c INT);

ALTER TABLE mytable MODIFY (b VISIBLE);

CREATE TABLE mytable2 (x INT, y INT, z INT);

ALTER TABLE mytable2 MODIFY (y INVISIBLE);

ALTER TABLE mytable2 MODIFY (y VISIBLE);

Consider Encrypting Columns That Contain Sensitive Data

You can encrypt individual table columns that contain sensitive data. Examples of sensitive data include social security numbers, credit card numbers, and medical records. Column encryption is transparent to your applications, with some restrictions.

Although encryption is not meant to solve all security problems, it does protect your data from users who try to circumvent the security features of the database and access database files directly through the operating system file system.

Column encryption uses the Transparent Data Encryption feature of Oracle Database, which requires that you create a keystore to store the master encryption key for the database. The keystore must be open before you can create a table with encrypted columns and before you can store or retrieve encrypted data. When you open the keystore, it is available to all sessions, and it remains open until you explicitly close it or until the database is shut down.

Transparent Data Encryption supports industry-standard encryption algorithms, including the following Advanced Encryption Standard (AES) and Triple Data Encryption Standard (3DES) algorithms:

• AES256

• AES192

• AES128

• 3DES168

You choose the algorithm to use when you create the table. All encrypted columns in the table use the same algorithm. The default is AES192. The encryption key length is implied by the algorithm name. For example, the AES128 algorithm uses 128-bit keys.

If you plan on encrypting many columns in one or more tables, you may want to consider encrypting an entire tablespace instead and storing these tables in that tablespace. Tablespace encryption, which also uses the Transparent Data Encryption feature but encrypts at the physical block level, can perform better than encrypting many columns. Another reason to encrypt at the tablespace level is to address the following limitations of column encryption:

• Certain data types, such as object data types, are not supported for column encryption.

• You cannot use the transportable tablespace feature for a tablespace that includes tables with encrypted columns.

• Other restrictions, which are detailed in Oracle Database Advanced Security Guide.

Understand Deferred Segment Creation

When you create heap-organized tables in a locally managed tablespace, the database defers table segment creation until the first row is inserted.

In addition, segment creation is deferred for any LOB columns of the table, any indexes created implicitly as part of table creation, and any indexes subsequently explicitly created on the table.

The advantages of this space allocation method are the following:

• It saves a significant amount of disk space in applications that create hundreds or thousands of tables upon installation, many of which might never be populated.

• It reduces application installation time.

There is a small performance penalty when the first row is inserted, because the new segment must be created at that time.

To enable deferred segment creation, compatibility must be set to 11.2.0 or higher.

The new clauses for the CREATE TABLE statement are:

• SEGMENT CREATION DEFERRED

• SEGMENT CREATION IMMEDIATE

These clauses override the default setting of the DEFERRED_SEGMENT_CREATION initialization parameter, TRUE, which defers segment creation. To disable deferred segment creation, set this parameter to FALSE.

Note that when you create a table with deferred segment creation, the new table appears in the *_TABLES views, but no entry for it appears in the *_SEGMENTS views until you insert the first row.

You can verify deferred segment creation by viewing the SEGMENT_CREATED column in *_TABLES, *_INDEXES, and *_LOBS views for nonpartitioned tables, and in *_TAB_PARTITIONS, *_IND_PARTITIONS, and *_LOB_PARTITIONS views for partitioned tables.

The following example creates two tables to demonstrate deferred segment creation. The first table uses the SEGMENT CREATION DEFERRED clause. No segments are created for it initially. The second table uses the SEGMENT CREATION IMMEDIATE clause and, therefore, segments are created for it immediately.

CREATE TABLE part_time_employees (
    empno NUMBER(8),
    name VARCHAR2(30),
    hourly_rate NUMBER (7,2)
    )   
    SEGMENT CREATION DEFERRED;
 
CREATE TABLE hourly_employees (
    empno NUMBER(8),
    name VARCHAR2(30),
    hourly_rate NUMBER (7,2)
    ) 
   SEGMENT CREATION IMMEDIATE
   PARTITION BY RANGE(empno)
    (PARTITION empno_to_100 VALUES LESS THAN (100),
    PARTITION empno_to_200 VALUES LESS THAN (200));

The following query against USER_SEGMENTS returns two rows for HOURLY_EMPLOYEES, one for each partition, but returns no rows for PART_TIME_EMPLOYEES because segment creation for that table was deferred.

SELECT segment_name, partition_name FROM user_segments;
 
SEGMENT_NAME         PARTITION_NAME                                
-------------------- ------------------------------                             
HOURLY_EMPLOYEES     EMPNO_TO_100                       
HOURLY_EMPLOYEES     EMPNO_TO_200       

The USER_TABLES view shows that PART_TIME_EMPLOYEES has no segments:

SELECT table_name, segment_created FROM user_tables;
 

TABLE_NAME                     SEGMENT_CREATED
------------------------------ ----------------------------------------
PART_TIME_EMPLOYEES            NO
HOURLY_EMPLOYEES               N/A

For the HOURLY_EMPLOYEES table, which is partitioned, the segment_created column is N/A because the USER_TABLES view does not provide that information for partitioned tables. It is available from the USER_TAB_PARTITIONS view, shown below.

SELECT table_name, segment_created, partition_name
 FROM user_tab_partitions;

TABLE_NAME           SEGMENT_CREATED      PARTITION_NAME
-------------------- -------------------- ------------------------------
HOURLY_EMPLOYEES     YES                  EMPNO_TO_100
HOURLY_EMPLOYEES     YES                  EMPNO_TO_200

The following statements add employees to these tables.

INSERT INTO hourly_employees VALUES (99, 'FRose', 20.00);
INSERT INTO hourly_employees VALUES (150, 'LRose', 25.00);
 
INSERT INTO part_time_employees VALUES (50, 'KReilly', 10.00);

Repeating the same SELECT statements as before shows that PART_TIME_EMPLOYEES now has a segment, due to the insertion of row data. HOURLY_EMPLOYEES remains as before.

SELECT segment_name, partition_name FROM user_segments;
 
SEGMENT_NAME         PARTITION_NAME
-------------------- ------------------------------
PART_TIME_EMPLOYEES
HOURLY_EMPLOYEES     EMPNO_TO_100
HOURLY_EMPLOYEES     EMPNO_TO_200

SELECT table_name, segment_created FROM user_tables;
 
TABLE_NAME           SEGMENT_CREATED
-------------------- --------------------
PART_TIME_EMPLOYEES  YES
HOURLY_EMPLOYEES     N/A
                                         

The USER_TAB_PARTITIONS view does not change.

Materializing Segments

The DBMS_SPACE_ADMIN package includes the MATERIALIZE_DEFERRED_SEGMENTS() procedure, which enables you to materialize segments for tables, table partitions, and dependent objects created with deferred segment creation enabled.

You can add segments as needed, rather than starting with more than you need and using database resources unnecessarily.

The following example materializes segments for the EMPLOYEES table in the HR schema.

BEGIN
  DBMS_SPACE_ADMIN.MATERIALIZE_DEFERRED_SEGMENTS(
    schema_name  => 'HR',
    table_name   => 'EMPLOYEES');
END;

Estimate Table Size and Plan Accordingly

Estimate the sizes of tables before creating them. Preferably, do this as part of database planning. Knowing the sizes, and uses, for database tables is an important part of database planning.

You can use the combined estimated size of tables, along with estimates for indexes, undo space, and redo log files, to determine the amount of disk space that is required to hold an intended database. From these estimates, you can make correct hardware purchases.

You can use the estimated size and growth rate of an individual table to better determine the attributes of a tablespace and its underlying data files that are best suited for the table. This can enable you to more easily manage the table disk space and improve I/O performance of applications that use the table.

Restrictions to Consider When Creating Tables

Here are some restrictions that may affect your table planning and usage:

• Tables containing object types cannot be imported into a pre-Oracle8 database.

• You cannot merge an exported table into a preexisting table having the same name in a different schema.

• You cannot move types and extent tables to a different schema when the original data still exists in the database.

• Oracle Database has a limit on the total number of columns that a table (or attributes that an object type) can have. See Oracle Database Reference for this limit.

  Further, when you create a table that contains user-defined type data, the database maps columns of user-defined type to relational columns for storing the user-defined type data. This causes additional relational columns to be created. This results in "hidden" relational columns that are not visible in a DESCRIBE table statement and are not returned by a SELECT * statement. Therefore, when you create an object table, or a relational table with columns of REF, varray, nested table, or object type, be aware that the total number of columns that the database actually creates for the table can be more than those you specify.

  See Also:

  Oracle Database Object-Relational Developer's Guide for more information about user-defined types

Example: Creating a Table

When you issue the following statement, you create a table named admin_emp in the hr schema and store it in the admin_tbs tablespace:

CREATE TABLE hr.admin_emp (
         empno      NUMBER(5) PRIMARY KEY,
         ename      VARCHAR2(15) NOT NULL,
         ssn        NUMBER(9) ENCRYPT USING 'AES256',
         job        VARCHAR2(10),
         mgr        NUMBER(5),
         hiredate   DATE DEFAULT (sysdate),
         photo      BLOB,
         sal        NUMBER(7,2),
         hrly_rate  NUMBER(7,2) GENERATED ALWAYS AS (sal/2080),
         comm       NUMBER(7,2),
         deptno     NUMBER(3) NOT NULL
                     CONSTRAINT admin_dept_fkey REFERENCES hr.departments
                     (department_id),
         comments   VARCHAR2(32767),
         status     VARCHAR2(10) INVISIBLE)
   TABLESPACE admin_tbs
   STORAGE ( INITIAL 50K);

COMMENT ON TABLE hr.admin_emp IS 'Enhanced employee table';

Note the following about this example:

• Integrity constraints are defined on several columns of the table.

• The STORAGE clause specifies the size of the first extent. See Oracle Database SQL Language Reference for details on this clause.

• Encryption is defined on one column (ssn), through the Transparent Data Encryption feature of Oracle Database. The keystore must therefore be open for this CREATE TABLE statement to succeed.

• The photo column is of data type BLOB, which is a member of the set of data types called large objects (LOBs). LOBs are used to store semi-structured data (such as an XML tree) and unstructured data (such as the stream of bits in a color image).

• One column is defined as a virtual column (hrly_rate). This column computes the employee's hourly rate as the yearly salary divided by 2,080. See Oracle Database SQL Language Reference for a discussion of rules for virtual columns.

• The comments column is a VARCHAR2 column that is larger than 4000 bytes. Beginning with Oracle Database 12c, the maximum size for the VARCHAR2, NVARCHAR2, and RAW data types is increased to 32767 bytes.

  To use extended data types, set the MAX_STRING_SIZE initialization parameter to EXTENDED. See Oracle Database Reference for information about setting this parameter.

• The status column is invisible.

• A COMMENT statement is used to store a comment for the table. You query the *_TAB_COMMENTS data dictionary views to retrieve such comments. See Oracle Database SQL Language Reference for more information.

Creating a Temporary Table

Temporary tables are useful in applications where a result set is to be buffered (temporarily persisted), perhaps because it is constructed by running multiple DML operations. For example, consider the following:

A Web-based airlines reservations application allows a customer to create several optional itineraries. Each itinerary is represented by a row in a temporary table. The application updates the rows to reflect changes in the itineraries. When the customer decides which itinerary she wants to use, the application moves the row for that itinerary to a persistent table.

During the session, the itinerary data is private. At the end of the session, the optional itineraries are dropped.

The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table.

Use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table. The ON COMMIT clause indicates if the data in the table is transaction-specific (the default) or session-specific, the implications of which are as follows:

This statement creates a temporary table that is transaction specific:

CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS;

Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.

By default, rows in a temporary table are stored in the default temporary tablespace of the user who creates it. However, you can assign a temporary table to another tablespace upon creation of the temporary table by using the TABLESPACE clause of CREATE GLOBAL TEMPORARY TABLE. You can use this feature to conserve space used by temporary tables. For example, if you must perform many small temporary table operations and the default temporary tablespace is configured for sort operations and thus uses a large extent size, these small operations will consume lots of unnecessary disk space. In this case it is better to allocate a second temporary tablespace with a smaller extent size.

The following two statements create a temporary tablespace with a 64 KB extent size, and then a new temporary table in that tablespace.

CREATE TEMPORARY TABLESPACE tbs_t1 
    TEMPFILE 'tbs_t1.f' SIZE 50m REUSE AUTOEXTEND ON
    MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;

CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS
      TABLESPACE tbs_t1;

Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed. Therefore, if a SELECT, UPDATE, or DELETE is performed before the first INSERT, then the table appears to be empty.

DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table.

If you rollback a transaction, the data you entered is lost, although the table definition persists.

A transaction-specific temporary table allows only one transaction at a time. If there are several autonomous transactions in a single transaction scope, each autonomous transaction can use the table only as soon as the previous one commits.

Because the data in a temporary table is, by definition, temporary, backup and recovery of temporary table data is not available in the event of a system failure. To prepare for such a failure, you should develop alternative methods for preserving temporary table data.

Parallelizing Table Creation

When you specify the AS SELECT clause to create a table and populate it with data from another table, you can use parallel execution. The CREATE TABLE...AS SELECT statement contains two parts: a CREATE part (DDL) and a SELECT part (query). Oracle Database can parallelize both parts of the statement. The CREATE part is parallelized if one of the following is true:

• A PARALLEL clause is included in the CREATE TABLE...AS SELECT statement

• An ALTER SESSION FORCE PARALLEL DDL statement is specified

The query part is parallelized if all of the following are true:

• The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the CREATE part includes the PARALLEL clause or the schema objects referred to in the query have a PARALLEL declaration associated with them.

• At least one of the tables specified in the query requires either a full table scan or an index range scan spanning multiple partitions.

If you parallelize the creation of a table, that table then has a parallel declaration (the PARALLEL clause) associated with it. Any subsequent DML or queries on the table, for which parallelization is possible, will attempt to use parallel execution.

The following simple statement parallelizes the creation of a table and stores the result in a compressed format, using table compression:

CREATE TABLE hr.admin_emp_dept
     PARALLEL COMPRESS
     AS SELECT * FROM hr.employees
     WHERE department_id = 10;

In this case, the PARALLEL clause tells the database to select an optimum number of parallel execution servers when creating the table.

Methods for Loading Tables

There are several means of inserting or initially loading data into your tables. Most commonly used are the following:

See Oracle Database SQL Language Reference for details on the CREATE TABLE ... AS SELECT, INSERT, and MERGE statements.

Improving INSERT Performance with Direct-Path INSERT

When loading large amounts of data, you can improve load performance by using direct-path INSERT.

This section contains:

• About Direct-Path INSERT

• How Direct-Path INSERT Works

• Loading Data with Direct-Path INSERT

• Specifying the Logging Mode for Direct-Path INSERT

• Additional Considerations for Direct-Path INSERT

INSERT /*+ APPEND */ INTO sales_hist SELECT * FROM sales WHERE cust_id=8890;

FORALL i IN 1..numrecords
  INSERT /*+ APPEND_VALUES */ INTO orderdata 
  VALUES(ordernum(i), custid(i), orderdate(i),shipmode(i), paymentid(i));
COMMIT;

Using Conventional Inserts to Load Tables

During conventional INSERT operations, the database reuses free space in the table, interleaving newly inserted data with existing data. During such operations, the database also maintains referential integrity constraints. Unlike direct-path INSERT operations, conventional INSERT operations do not require an exclusive lock on the table.

Several other restrictions apply to direct-path INSERT operations that do not apply to conventional INSERT operations. See Oracle Database SQL Language Reference for information about these restrictions.

You can perform a conventional INSERT operation in serial mode or in parallel mode using the NOAPPEND hint.

The following is an example of using the NOAPPEND hint to perform a conventional INSERT in serial mode:

INSERT /*+ NOAPPEND */ INTO sales_hist SELECT * FROM sales WHERE cust_id=8890;

The following is an example of using the NOAPPEND hint to perform a conventional INSERT in parallel mode:

INSERT /*+ NOAPPEND PARALLEL */ INTO sales_hist
   SELECT * FROM sales;

To run in parallel DML mode, the following requirements must be met:

• You must have Oracle Enterprise Edition installed.

• You must enable parallel DML in your session. To do this, submit the following statement:

  ALTER SESSION { ENABLE | FORCE } PARALLEL DML;
  

• You must meet at least one of the following requirements:

  • Specify the parallel attribute for the target table, either at create time or subsequently

  • Specify the PARALLEL hint for each insert operation

  • Set the database initialization parameter PARALLEL_DEGREE_POLICY to AUTO

Avoiding Bulk INSERT Failures with DML Error Logging

When you load a table using an INSERT statement with subquery, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature.

To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time.

DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements. This section focuses on INSERT statements.

To insert data with DML error logging:

1. Create an error logging table. (Optional)

   You can create the table manually or use the DBMS_ERRLOG package to automatically create it for you. See "Creating an Error Logging Table" for details.

2. Execute an INSERT statement and include an error logging clause. This clause:

   • Optionally references the error logging table that you created. If you do not provide an error logging table name, the database logs to an error logging table with a default name. The default error logging table name is ERR$_ followed by the first 25 characters of the name of the table that is being inserted into.

   • Optionally includes a tag (a numeric or string literal in parentheses) that gets added to the error log to help identify the statement that caused the errors. If the tag is omitted, a NULL value is used.

   • Optionally includes a REJECT LIMIT subclause.

     This subclause indicates the maximum number of errors that can be encountered before the INSERT statement terminates and rolls back. You can also specify UNLIMITED. The default reject limit is zero, which means that upon encountering the first error, the error is logged and the statement rolls back. For parallel DML operations, the reject limit is applied to each parallel execution server.

   Note:

   If the statement exceeds the reject limit and rolls back, the error logging table retains the log entries recorded so far.

   See Oracle Database SQL Language Reference for error logging clause syntax information.

3. Query the error logging table and take corrective action for the rows that generated errors.

   See "Error Logging Table Format", later in this section, for details on the error logging table structure.

Example The following statement inserts rows into the DW_EMPL table and logs errors to the ERR_EMPL table. The tag 'daily_load' is copied to each log entry. The statement terminates and rolls back if the number of errors exceeds 25.

INSERT INTO dw_empl
  SELECT employee_id, first_name, last_name, hire_date, salary, department_id 
  FROM employees
  WHERE hire_date > sysdate - 7
  LOG ERRORS INTO err_empl ('daily_load') REJECT LIMIT 25

For more examples, see Oracle Database SQL Language Reference and Oracle Database Data Warehousing Guide.

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('DW_EMPL', 'ERR_EMPL');

Reasons for Using the ALTER TABLE Statement

You can use the ALTER TABLE statement to perform any of the following actions that affect a table:

• Modify physical characteristics (INITRANS or storage parameters)

• Move the table to a new segment or tablespace

• Explicitly allocate an extent or deallocate unused space

• Add, drop, or rename columns, or modify an existing column definition (data type, length, default value, NOT NULL integrity constraint, column expression (for virtual columns), and encryption properties.)

• Modify the logging attributes of the table

• Modify the CACHE/NOCACHE attributes

• Add, modify or drop integrity constraints associated with the table

• Enable or disable integrity constraints or triggers associated with the table

• Modify the degree of parallelism for the table

• Rename a table

• Put a table in read-only mode and return it to read/write mode

• Add or modify index-organized table characteristics

• Alter the characteristics of an external table

• Add or modify LOB columns

• Add or modify object type, nested table, or varray columns

• Modify table partitions

  Starting with Oracle Database 12c, you can perform some operations on more than two partitions or subpartitions at a time, such as split partition and merge partitions operations. See Oracle Database VLDB and Partitioning Guide for information.

Many of these operations are discussed in succeeding sections.

Altering Physical Attributes of a Table

When altering the transaction entry setting INITRANS of a table, note that a new setting for INITRANS applies only to data blocks subsequently allocated for the table.

The storage parameters INITIAL and MINEXTENTS cannot be altered. All new settings for the other storage parameters (for example, NEXT, PCTINCREASE) affect only extents subsequently allocated for the table. The size of the next extent allocated is determined by the current values of NEXT and PCTINCREASE, and is not based on previous values of these parameters.

Moving a Table to a New Segment or Tablespace

The ALTER TABLE...MOVE statement enables you to relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota. This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE. You can also use the ALTER TABLE...MOVE statement with a COMPRESS clause to store the new segment using table compression.

Tables are usually moved either to enable compression or to perform data maintenance. For example, you can move a table from one tablespace to another.

Most ALTER TABLE...MOVE statements do not permit DML against the table while the statement is executing. The exceptions are the following statements:

• ALTER TABLE ... MOVE PARTITION ... ONLINE

• ALTER TABLE ... MOVE SUBPARTITION ... ONLINE

These two statements support the ONLINE keyword, which enables DML operations to run uninterrupted on the partition or subpartition that is being moved. For operations that do not move a partition or subpartition, you can use online redefinition to leave the table available for DML while moving it. See "Redefining Tables Online".

This section includes the following topics:

• Moving a Table

• Moving a Table Partition or Subpartition Online

ALTER TABLE hr.jobs MOVE
      STORAGE ( INITIAL 20K
                NEXT 40K
                MINEXTENTS 2
                MAXEXTENTS 20
                PCTINCREASE 0 )
  TABLESPACE hr_tbs;

ALTER TABLE sales MOVE PARTITION sales_q4_2003 
  ROW STORE COMPRESS ADVANCED UPDATE INDEXES ONLINE;

Manually Allocating Storage for a Table

Oracle Database dynamically allocates additional extents for the data segment of a table, as required. However, perhaps you want to allocate an additional extent for a table explicitly. For example, in an Oracle Real Application Clusters environment, an extent of a table can be allocated explicitly for a specific instance.

A new extent can be allocated for a table using the ALTER TABLE...ALLOCATE EXTENT clause.

You can also explicitly deallocate unused space using the DEALLOCATE UNUSED clause of ALTER TABLE. This is described in "Reclaiming Unused Space".

Modifying an Existing Column Definition

Use the ALTER TABLE...MODIFY statement to modify an existing column definition. You can modify column data type, default value, column constraint, column expression (for virtual columns), column encryption, and visible/invisible property.

You can increase the length of an existing column, or decrease it, if all existing data satisfies the new length. Beginning with Oracle Database 12c, you can specify a maximum size of 32767 bytes for the VARCHAR2, NVARCHAR2, and RAW data types. Before this release, the maximum size was 4000 bytes for the VARCHAR2 and NVARCHAR2 data types, and 2000 bytes for the RAW data type. To use extended data types, set the MAX_STRING_SIZE initialization parameter to EXTENDED.

You can change a column from byte semantics to CHAR semantics or vice versa. You must set the initialization parameter BLANK_TRIMMING=TRUE to decrease the length of a non-empty CHAR column.

If you are modifying a table to increase the length of a column of data type CHAR, then realize that this can be a time consuming operation and can require substantial additional storage, especially if the table contains many rows. This is because the CHAR value in each row must be blank-padded to satisfy the new column length.

If you modify the visible/invisible property of a column, then you cannot include any other column modification options in the same SQL statement.

ALTER TABLE oe.product_information MODIFY(product_description VARCHAR2(32767));

Adding Table Columns

To add a column to an existing table, use the ALTER TABLE...ADD statement.

The following statement alters the hr.admin_emp table to add a new column named bonus:

ALTER TABLE hr.admin_emp
      ADD (bonus NUMBER (7,2));

If a new column is added to a table, then the column is initially NULL unless you specify the DEFAULT clause. If you specify the DEFAULT clause for a nullable column for some table types, then the default value is stored as metadata, but the column itself is not populated with data. However, subsequent queries that specify the new column are rewritten so that the default value is returned in the result set. This behavior optimizes the resource usage and storage requirements for the operation.

You can add a column with a NOT NULL constraint only if the table does not contain any rows, or you specify a default value.

Renaming Table Columns

Oracle Database lets you rename existing columns in a table. Use the RENAME COLUMN clause of the ALTER TABLE statement to rename a column. The new name must not conflict with the name of any existing column in the table. No other clauses are allowed with the RENAME COLUMN clause.

The following statement renames the comm column of the hr.admin_emp table.

ALTER TABLE hr.admin_emp
      RENAME COLUMN comm TO commission;

As noted earlier, altering a table column can invalidate dependent objects. However, when you rename a column, the database updates associated data dictionary tables to ensure that function-based indexes and check constraints remain valid.

Oracle Database also lets you rename column constraints. This is discussed in "Renaming Constraints".

Dropping Table Columns

You can drop columns that are no longer needed from a table, including an index-organized table. This provides a convenient means to free space in a database, and avoids your having to export/import data then re-create indexes and constraints.

You cannot drop all columns from a table, nor can you drop columns from a table owned by SYS. Any attempt to do so results in an error.

ALTER TABLE hr.admin_emp DROP COLUMN sal;

ALTER TABLE hr.admin_emp DROP (bonus, commission);

ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);

SELECT * FROM DBA_UNUSED_COL_TABS;

OWNER                       TABLE_NAME                  COUNT
--------------------------- --------------------------- -----
HR                          ADMIN_EMP                       2

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;

Placing a Table in Read-Only Mode

You can place a table in read-only mode with the ALTER TABLE...READ ONLY statement, and return it to read/write mode with the ALTER TABLE...READ WRITE statement. An example of a table for which read-only mode makes sense is a configuration table. If your application contains configuration tables that are not modified after installation and that must not be modified by users, your application installation scripts can place these tables in read-only mode.

To place a table in read-only mode, you must have the ALTER TABLE privilege on the table or the ALTER ANY TABLE privilege. In addition, the COMPATIBLE initialization parameter must be set to 11.1.0 or higher.

The following example places the SALES table in read-only mode:

ALTER TABLE SALES READ ONLY;

The following example returns the table to read/write mode:

ALTER TABLE SALES READ WRITE;

When a table is in read-only mode, operations that attempt to modify table data are disallowed. The following operations are not permitted on a read-only table:

• All DML operations on the table or any of its partitions

• TRUNCATE TABLE

• SELECT FOR UPDATE

• ALTER TABLE ADD/MODIFY/RENAME/DROP COLUMN

• ALTER TABLE SET COLUMN UNUSED

• ALTER TABLE DROP/TRUNCATE/EXCHANGE (SUB)PARTITION

• ALTER TABLE UPGRADE INCLUDING DATA or ALTER TYPE CASCADE INCLUDING TABLE DATA for a type with read-only table dependents

• Online redefinition

• FLASHBACK TABLE

The following operations are permitted on a read-only table:

• SELECT

• CREATE/ALTER/DROP INDEX

• ALTER TABLE ADD/MODIFY/DROP/ENABLE/DISABLE CONSTRAINT

• ALTER TABLE for physical property changes

• ALTER TABLE DROP UNUSED COLUMNS

• ALTER TABLE ADD/COALESCE/MERGE/MODIFY/MOVE/RENAME/SPLIT (SUB)PARTITION

• ALTER TABLE MOVE

• ALTER TABLE ENABLE ROW MOVEMENT and ALTER TABLE SHRINK

• RENAME TABLE and ALTER TABLE RENAME TO

• DROP TABLE

• ALTER TABLE DEALLOCATE UNUSED

• ALTER TABLE ADD/DROP SUPPLEMENTAL LOG

Features of Online Table Redefinition

Online table redefinition enables you to:

• Modify the storage parameters of a table or cluster

• Move a table or cluster to a different tablespace

  Note:

  If it is not important to keep a table available for DML when moving it to another tablespace, then you can use the simpler ALTER TABLE MOVE command. See "Moving a Table to a New Segment or Tablespace".

• Add, modify, or drop one or more columns in a table or cluster

• Add or drop partitioning support (non-clustered tables only)

• Change partition structure

• Change physical properties of a single table partition or subpartition, including moving it to a different tablespace in the same schema

  Starting with Oracle Database 12c, you can move a partition or subpartition online without using online table redefinition. DML operations can continue to run uninterrupted on the partition or subpartition that is being moved. See "Moving a Table to a New Segment or Tablespace".

• Change physical properties of a materialized view log or an Oracle Database Advanced Queuing queue table

• Add support for parallel queries

• Re-create a table or cluster to reduce fragmentation

  Note:

  In many cases, online segment shrink is an easier way to reduce fragmentation. See "Reclaiming Unused Space".

• Change the organization of a normal table (heap organized) to an index-organized table, or do the reverse.

• Convert a relational table into a table with object columns, or do the reverse.

• Convert an object table into a relational table or a table with object columns, or do the reverse.

• Compress, or change the compression type for, a table, partition, index key, or LOB columns.

• Convert LOB columns from BasicFiles LOB storage to SecureFiles LOB storage, or do the reverse.

You can combine two or more of the usage examples above into one operation. See "Example 8" in "Online Table Redefinition Examples" for an example.

Performing Online Redefinition with the REDEF_TABLE Procedure

You can use the REDEF_TABLE procedure in the DBMS_REDEFINITION package to perform online redefinition of a table's storage properties. See Oracle Database PL/SQL Packages and Types Reference for procedure details.

The REDEF_TABLE procedure enables you to perform online redefinition a table's storage properties in a single step when you want to change the following properties:

• Tablespace changes, including a tablespace change for a table, partition, index, or LOB columns

• Compression type changes, including a compression type change for a table, partition, index key, or LOB columns

• For LOB columns, a change to SECUREFILE or BASICFILE storage

When your online redefinition operation is not limited to these changes, you must perform online redefinition of the table using multiple steps. The steps include invoking multiple procedures in the DBMS_REDEFINITION package, including the following procedures: CAN_REDEF_TABLE, START_REDEF_TABLE, COPY_TABLE_DEPENDENTS, and FINISH_REDEF_TABLE.

Performing Online Redefinition with Multiple Procedures in DBMS_REDEFINITION

You use the DBMS_REDEFINITION package to perform online redefinition of a table. See Oracle Database PL/SQL Packages and Types Reference for package details.

To redefine a table online using multiple steps:

1. Choose the redefinition method: by key or by rowid

   By key—Select a primary key or pseudo-primary key to use for the redefinition. Pseudo-primary keys are unique keys with all component columns having NOT NULL constraints. For this method, the versions of the tables before and after redefinition should have the same primary key columns. This is the preferred and default method of redefinition.

   By rowid—Use this method if no key is available. In this method, a hidden column named M_ROW$$ is added to the post-redefined version of the table. It is recommended that this column be dropped or marked as unused after the redefinition is complete. The final phase of redefinition automatically sets this column unused. You can then use the ALTER TABLE ... DROP UNUSED COLUMNS statement to drop it.

   You cannot use this method on index-organized tables.

2. Verify that the table can be redefined online by invoking the CAN_REDEF_TABLE procedure. If the table is not a candidate for online redefinition, then this procedure raises an error indicating why the table cannot be redefined online.

3. Create an empty interim table (in the same schema as the table to be redefined) with all of the desired logical and physical attributes. If columns are to be dropped, then do not include them in the definition of the interim table. If a column is to be added, then add the column definition to the interim table. If a column is to be modified, then create it in the interim table with the properties that you want.

   It is not necessary to create the interim table with all the indexes, constraints, grants, and triggers of the table being redefined, because these will be defined in step 7 when you copy dependent objects.

4. If you are redefining a partitioned table with the rowid method, then enable row movement on the interim table.

   ALTER TABLE ... ENABLE ROW MOVEMENT;
   

5. (Optional) If you are redefining a large table and want to improve the performance of the next step by running it in parallel, issue the following statements:

   ALTER SESSION FORCE PARALLEL DML PARALLEL degree-of-parallelism;
   ALTER SESSION FORCE PARALLEL QUERY PARALLEL degree-of-parallelism;
   

6. Start the redefinition process by calling START_REDEF_TABLE, providing the following:

   • The schema and table name of the table to be redefined in the uname and orig_table parameters, respectively

   • The interim table name in the int_table parameter

   • A column mapping string that maps the columns of table to be redefined to the columns of the interim table in the col_mapping parameter

     See "Constructing a Column Mapping String" for details.

   • The redefinition method in the options_flag parameter

     Package constants are provided for specifying the redefinition method. DBMS_REDEFINITION.CONS_USE_PK is used to indicate that the redefinition should be done using primary keys or pseudo-primary keys. DBMS_REDEFINITION.CONS_USE_ROWID is use to indicate that the redefinition should be done using rowids. If this argument is omitted, the default method of redefinition (CONS_USE_PK) is assumed.

   • Optionally, the columns to be used in ordering rows in the orderby_cols parameter

   • The partition name or names in the part_name parameter when redefining one partition or multiple partitions of a partitioned table

     See "Online Redefinition of One or More Partitions" for details.

   • The method for handling Virtual Private Database (VPD) policies defined on the table in the copy_vpd_opt parameter

     See "Handling Virtual Private Database (VPD) Policies During Online Redefinition" for details.

   Because this process involves copying data, it may take a while. The table being redefined remains available for queries and DML during the entire process.

   Note:

   • You can query the DBA_REDEFINITION_OBJECTS view to list the objects currently involved in online redefinition.

   • If START_REDEF_TABLE fails for any reason, you must call ABORT_REDEF_TABLE, otherwise subsequent attempts to redefine the table will fail.

7. Copy dependent objects (such as triggers, indexes, materialized view logs, grants, and constraints) and statistics from the table being redefined to the interim table, using one of the following two methods. Method 1 is the preferred method because it is more automatic, but there may be times that you would choose to use method 2. Method 1 also enables you to copy table statistics to the interim table.

   • Method 1: Automatically Creating Dependent Objects

     Use the COPY_TABLE_DEPENDENTS procedure to automatically create dependent objects on the interim table. This procedure also registers the dependent objects. Registering the dependent objects enables the identities of these objects and their copied counterparts to be automatically swapped later as part of the redefinition completion process. The result is that when the redefinition is completed, the names of the dependent objects will be the same as the names of the original dependent objects.

     For more information, see "Creating Dependent Objects Automatically".

   • Method 2: Manually Creating Dependent Objects

     You can manually create dependent objects on the interim table and then register them. For more information, see "Creating Dependent Objects Manually".

     Note:

     In Oracle9i, you were required to manually create the triggers, indexes, grants, and constraints on the interim table, and there may still be situations where you want to or must do so. In such cases, any referential constraints involving the interim table (that is, the interim table is either a parent or a child table of the referential constraint) must be created disabled. When online redefinition completes, the referential constraint is automatically enabled. In addition, until the redefinition process is either completed or aborted, any trigger defined on the interim table does not execute.

8. Execute the FINISH_REDEF_TABLE procedure to complete the redefinition of the table. During this procedure, the original table is locked in exclusive mode for a very short time, independent of the amount of data in the original table. However, FINISH_REDEF_TABLE will wait for all pending DML to commit before completing the redefinition.

   You can use the dml_lock_timeout parameter in the FINISH_REDEF_TABLE procedure to specify how long the procedure waits for pending DML to commit. The parameter specifies the number of seconds to wait before the procedure ends gracefully. When you specify a non-NULL value for this parameter, you can restart the FINISH_REDEF_TABLE procedure, and it continues from the point at which it timed out. When the parameter is set to NULL, the procedure does not time out. In this case, if you stop the procedure manually, then you must abort the online table redefinition using the ABORT_REDEF_TABLE procedure and start over from step 6.

9. Wait for any long-running queries against the interim table to complete, and then drop the interim table.

   If you drop the interim table while there are active queries running against it, you may encounter an ORA-08103 error ("object no longer exists").

[expression]  column_name

override*1.02  override_commission

Results of the Redefinition Process

The following are the end results of the redefinition process:

• The original table is redefined with the columns, indexes, constraints, grants, triggers, and statistics of the interim table, assuming that either REDEF_TABLE or COPY_TABLE_DEPENDENTS was used.

• Dependent objects that were registered, either explicitly using REGISTER_DEPENDENT_OBJECT or implicitly using COPY_TABLE_DEPENDENTS, are renamed automatically so that dependent object names on the redefined table are the same as before redefinition.

  Note:

  If no registration is done or no automatic copying is done, then you must manually rename the dependent objects.

• The referential constraints involving the interim table now involve the redefined table and are enabled.

• Any indexes, triggers, materialized view logs, grants, and constraints defined on the original table (before redefinition) are transferred to the interim table and are dropped when the user drops the interim table. Any referential constraints involving the original table before the redefinition now involve the interim table and are disabled.

• Some PL/SQL objects, views, synonyms, and other table-dependent objects may become invalidated. Only those objects that depend on elements of the table that were changed are invalidated. For example, if a PL/SQL procedure queries only columns of the redefined table that were unchanged by the redefinition, the procedure remains valid. See "Managing Object Dependencies" for more information about schema object dependencies.

Performing Intermediate Synchronization

After the redefinition process has been started by calling START_REDEF_TABLE and before FINISH_REDEF_TABLE has been called, a large number of DML statements might have been executed on the original table. If you know that this is the case, then it is recommended that you periodically synchronize the interim table with the original table. This is done by calling the SYNC_INTERIM_TABLE procedure. Calling this procedure reduces the time taken by FINISH_REDEF_TABLE to complete the redefinition process. There is no limit to the number of times that you can call SYNC_INTERIM_TABLE.

The small amount of time that the original table is locked during FINISH_REDEF_TABLE is independent of whether SYNC_INTERIM_TABLE has been called.

Aborting Online Table Redefinition and Cleaning Up After Errors

In the event that an error is raised during the redefinition process, or if you choose to terminate the redefinition process manually, call ABORT_REDEF_TABLE. This procedure drops temporary logs and tables associated with the redefinition process. After this procedure is called, you can drop the interim table and its dependent objects.

If the online redefinition process must be restarted, if you do not first call ABORT_REDEF_TABLE, then subsequent attempts to redefine the table will fail.

Restrictions for Online Redefinition of Tables

The following restrictions apply to the online redefinition of tables:

• If the table is to be redefined using primary key or pseudo-primary keys (unique keys or constraints with all component columns having not null constraints), then the post-redefinition table must have the same primary key or pseudo-primary key columns. If the table is to be redefined using rowids, then the table must not be an index-organized table.

• After redefining a table that has a materialized view log, the subsequent refresh of any dependent materialized view must be a complete refresh.

• Tables that are replicated in an n-way master configuration can be redefined, but horizontal subsetting (subset of rows in the table), vertical subsetting (subset of columns in the table), and column transformations are not allowed.

• The overflow table of an index-organized table cannot be redefined online independently.

• Tables for which Flashback Data Archive is enabled cannot be redefined online. You cannot enable Flashback Data Archive for the interim table.

• Tables with BFILE columns cannot be redefined online.

• Tables with LONG columns can be redefined online, but those columns must be converted to CLOBS. Also, LONG RAW columns must be converted to BLOBS. Tables with LOB columns are acceptable.

• On a system with sufficient resources for parallel execution, and in the case where the interim table is not partitioned, redefinition of a LONG column to a LOB column can be executed in parallel, provided that:

  • The segment used to store the LOB column in the interim table belongs to a locally managed tablespace with Automatic Segment Space Management (ASSM) enabled.

  • There is a simple mapping from one LONG column to one LOB column, and the interim table has only one LOB column.

  In the case where the interim table is partitioned, the normal methods for parallel execution for partitioning apply.

• Tables in the SYS and SYSTEM schema cannot be redefined online.

• Temporary tables cannot be redefined.

• A subset of rows in the table cannot be redefined.

• Only simple deterministic expressions, sequences, and SYSDATE can be used when mapping the columns in the interim table to those of the original table. For example, subqueries are not allowed.

• If new columns are being added as part of the redefinition and there are no column mappings for these columns, then they must not be declared NOT NULL until the redefinition is complete.

• There cannot be any referential constraints between the table being redefined and the interim table.

• Table redefinition cannot be done NOLOGGING.

• For materialized view logs and queue tables, online redefinition is restricted to changes in physical properties. No horizontal or vertical subsetting is permitted, nor are any column transformations. The only valid value for the column mapping string is NULL.

• You cannot perform online redefinition on a table that is partitioned if the table includes one or more nested tables.

• You can convert a VARRAY to a nested table with the CAST operator in the column mapping. However, you cannot convert a nested table to a VARRAY.

• When the columns in the col_mapping parameter of the DBMS_REDEFINITION.START_REDEF_TABLE procedure include a sequence, the orderby_cols parameter must be NULL.

• For tables with a Virtual Private Database (VPD) security policy, when the copy_vpd_opt parameter is specified as DBMS_REDEFINITION.CONS_VPD_AUTO, the following restrictions apply:

  • The column mapping string between the original table and interim table must be NULL or '*'.

  • No VPD policies can exist on the interim table.

  See "Handling Virtual Private Database (VPD) Policies During Online Redefinition". Also, see Oracle Database Security Guide for information about VPD policies.

• Online redefinition cannot run on multiple tables concurrently in separate DBMS_REDEFINITION sessions if the tables are related by reference partitioning.

  See Oracle Database VLDB and Partitioning Guide for more information about reference partitioning.

Online Redefinition of One or More Partitions

You can redefine online one or more partitions of a table. This is useful if, for example, you want to move partitions to a different tablespace and keep the partitions available for DML during the operation.

You can redefine multiple partitions in a table at one time. If you do, then multiple interim tables are required during the table redefinition process. Ensure that you have enough free space and undo space to complete the table redefinition.

When you redefine multiple partitions, you can specify that the redefinition continues even if it encounters an error for a particular partition. To do so, set the continue_after_errors parameter to TRUE in redefinition procedures in the DBMS_REDEFINITION package. You can check the DBA_REDEFINITION_STATUS view to see if any errors were encountered during the redefinition process. The STATUS column in this view shows whether the redefinition process succeeded or failed for each partition.

You can also redefine an entire table one partition at a time to reduce resource requirements. For example, to move a very large table to a different tablespace, you can move it one partition at a time to minimize the free space and undo space required to complete the move.

Redefining partitions differs from redefining a table in the following ways:

• There is no need to copy dependent objects. It is not valid to use the COPY_TABLE_DEPENDENTS procedure when redefining a single partition.

• You must manually create and register any local indexes on the interim table.

  See "Creating Dependent Objects Manually".

• The column mapping string for START_REDEF_TABLE must be NULL.

Online Table Redefinition Examples

For the following examples, see Oracle Database PL/SQL Packages and Types Reference for descriptions of all DBMS_REDEFINITION subprograms.

Example 1

This example illustrates online redefinition of a table's storage properties using the REDEF_TABLE procedure.

The original table, named print_ads, is defined in the pm schema as follows:

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 AD_ID                                              NUMBER(6)
 AD_TEXT                                            CLOB

In this table, the LOB column ad_text uses BasicFiles LOB storage.

An index for the table was created with the following SQL statement:

CREATE INDEX pm.print_ads_ix
    ON print_ads (ad_id)
  TABLESPACE example;

The table is redefined as follows:

• The table is compressed with advanced row compression.

• The table's tablespace is changed from EXAMPLE to NEWTBS. This example assumes that the NEWTBS tablespace exists.

• The index is compressed with COMPRESS 1 compression.

• The index's tablespace is changed from EXAMPLE to NEWIDXTBS. This example assumes that the NEWIDXTBS tablespace exists.

• The LOB column in the table is compressed with COMPRESS HIGH compression.

• The tablespace for the LOB column is changed from EXAMPLE to NEWLOBTBS. This example assumes that the NEWLOBTBS tablespace exists.

• The LOB column is changed to SecureFiles LOB storage.

The steps in this redefinition are illustrated below.

1. In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table.

   Specifically, the user must have the privileges described in "Privileges Required for the DBMS_REDEFINITION Package".

   See "Connecting to the Database with SQL*Plus".

2. Run the REDEF_TABLE procedure:

   BEGIN
     DBMS_REDEFINITION.REDEF_TABLE(
       uname                      => 'PM',
       tname                      => 'PRINT_ADS',
       table_compression_type     => 'ROW STORE COMPRESS ADVANCED',
       table_part_tablespace      => 'NEWTBS',
       index_key_compression_type => 'COMPRESS 1',
       index_tablespace           => 'NEWIDXTBS',
       lob_compression_type       => 'COMPRESS HIGH',
       lob_tablespace             => 'NEWLOBTBS',
       lob_store_as               => 'SECUREFILE');
   END;
   / 
   

Example 2

This example illustrates online redefinition of a table by adding new columns and adding partitioning.

The original table, named emp_redef, is defined in the hr schema as follows:

 Name      Type
 --------- ----------------------------
 EMPNO     NUMBER(5)    <- Primary key
 ENAME     VARCHAR2(15)
 JOB       VARCHAR2(10)
 DEPTNO    NUMBER(3)

The table is redefined as follows:

• New columns mgr, hiredate, sal, and bonus are added.

• The new column bonus is initialized to 0 (zero).

• The column deptno has its value increased by 10.

• The redefined table is partitioned by range on empno.

The steps in this redefinition are illustrated below.

1. In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table.

   Specifically, the user must have the privileges described in "Privileges Required for the DBMS_REDEFINITION Package".

   See "Connecting to the Database with SQL*Plus".

2. Verify that the table is a candidate for online redefinition. In this case you specify that the redefinition is to be done using primary keys or pseudo-primary keys.

   BEGIN
     DBMS_REDEFINITION.CAN_REDEF_TABLE(
       uname        => 'hr',
       tname        =>'emp_redef',
       options_flag => DBMS_REDEFINITION.CONS_USE_PK);
   END;
   /
   

3. Create an interim table hr.int_emp_redef.

   CREATE TABLE hr.int_emp_redef
           (empno      NUMBER(5) PRIMARY KEY,
            ename      VARCHAR2(15) NOT NULL,
            job        VARCHAR2(10),
            mgr        NUMBER(5),
            hiredate   DATE DEFAULT (sysdate),
            sal        NUMBER(7,2),
            deptno     NUMBER(3) NOT NULL,
            bonus      NUMBER (7,2) DEFAULT(0))
        PARTITION BY RANGE(empno)
          (PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE admin_tbs,
           PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE admin_tbs2);
   

   Ensure that the specified tablespaces exist.

4. Start the redefinition process.

   BEGIN
     DBMS_REDEFINITION.START_REDEF_TABLE(
       uname        => 'hr', 
       orig_table   => 'emp_redef',
       int_table    => 'int_emp_redef',
       col_mapping  => 'empno empno, ename ename, job job, deptno+10 deptno, 
                        0 bonus',
       options_flag => DBMS_REDEFINITION.CONS_USE_PK);
   END;
   /
   

5. Copy dependent objects. (Automatically create any triggers, indexes, materialized view logs, grants, and constraints on hr.int_emp_redef.)

   DECLARE
   num_errors PLS_INTEGER;
   BEGIN
     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
       uname            => 'hr', 
       orig_table       => 'emp_redef',
       int_table        => 'int_emp_redef',
       copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
       copy_triggers    => TRUE, 
       copy_constraints => TRUE, 
       copy_privileges  => TRUE, 
       ignore_errors    => TRUE, 
       num_errors       => num_errors);
   END;
   /
   

   Note that the ignore_errors argument is set to TRUE for this call. The reason is that the interim table was created with a primary key constraint, and when COPY_TABLE_DEPENDENTS attempts to copy the primary key constraint and index from the original table, errors occur. You can ignore these errors, but you must run the query shown in the next step to see if there are other errors.

6. Query the DBA_REDEFINITION_ERRORS view to check for errors.

   SET LONG  8000
   SET PAGES 8000
   COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A20
   COLUMN BASE_TABLE_NAME HEADING 'Base Table Name' FORMAT A10
   COLUMN DDL_TXT HEADING 'DDL That Caused Error' FORMAT A40
    
   SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT FROM 
            DBA_REDEFINITION_ERRORS;
   
   Object Name          Base Table DDL That Caused Error
   -------------------- ---------- ----------------------------------------
   SYS_C006796          EMP_REDEF  CREATE UNIQUE INDEX "HR"."TMP$$_SYS_C006
                                   7960" ON "HR"."INT_EMP_REDEF" ("EMPNO")
                                     PCTFREE 10 INITRANS 2 MAXTRANS 255
                                     STORAGE(INITIAL 65536 NEXT 1048576 MIN
                                   EXTENTS 1 MAXEXTENTS 2147483645
                                     PCTINCREASE 0 FREELISTS 1 FREELIST GRO
                                   UPS 1
                                     BUFFER_POOL DEFAULT)
                                     TABLESPACE "ADMIN_TBS"
   SYS_C006794          EMP_REDEF  ALTER TABLE "HR"."INT_EMP_REDEF" MODIFY
                                   ("ENAME" CONSTRAINT "TMP$$_SYS_C0067940"
                                    NOT NULL ENABLE NOVALIDATE)
   SYS_C006795          EMP_REDEF  ALTER TABLE "HR"."INT_EMP_REDEF" MODIFY
                                   ("DEPTNO" CONSTRAINT "TMP$$_SYS_C0067950
                                   " NOT NULL ENABLE NOVALIDATE)
   SYS_C006796          EMP_REDEF  ALTER TABLE "HR"."INT_EMP_REDEF" ADD CON
                                   STRAINT "TMP$$_SYS_C0067960" PRIMARY KEY
                                    ("EMPNO")
                                     USING INDEX PCTFREE 10 INITRANS 2 MAXT
                                   RANS 255
                                     STORAGE(INITIAL 65536 NEXT 1048576 MIN
                                   EXTENTS 1 MAXEXTENTS 2147483645
                                     PCTINCREASE 0 FREELISTS 1 FREELIST GRO
                                   UPS 1
                                     BUFFER_POOL DEFAULT)
                                     TABLESPACE "ADMIN_TBS"  ENABLE NOVALID
                                   ATE
   

   These errors are caused by the existing primary key constraint on the interim table and can be ignored. Note that with this approach, the names of the primary key constraint and index on the post-redefined table are changed. An alternate approach, one that avoids errors and name changes, would be to define the interim table without a primary key constraint. In this case, the primary key constraint and index are copied from the original table.

   Note:

   The best approach is to define the interim table with a primary key constraint, use REGISTER_DEPENDENT_OBJECT to register the primary key constraint and index, and then copy the remaining dependent objects with COPY_TABLE_DEPENDENTS. This approach avoids errors and ensures that the redefined table always has a primary key and that the dependent object names do not change.

7. (Optional) Synchronize the interim table hr.int_emp_redef.

   BEGIN 
     DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
       uname      => 'hr', 
       orig_table => 'emp_redef', 
       int_table  => 'int_emp_redef');
   END;
   /
   

8. Complete the redefinition.

   BEGIN
     DBMS_REDEFINITION.FINISH_REDEF_TABLE(
       uname      => 'hr', 
       orig_table => 'emp_redef', 
       int_table  => 'int_emp_redef');
   END;
   /
   

   The table hr.emp_redef is locked in the exclusive mode only for a small window toward the end of this step. After this call the table hr.emp_redef is redefined such that it has all the attributes of the hr.int_emp_redef table.

   Consider specifying a non-NULL value for the dml_lock_timeout parameter in this procedure. See step 8 in "Performing Online Redefinition with Multiple Procedures in DBMS_REDEFINITION" for more information.

9. Wait for any long-running queries against the interim table to complete, and then drop the interim table.

Example 3

This example redefines a table to change columns into object attributes. The redefined table gets a new column that is an object type.

The original table, named customer, is defined as follows:

Name         Type          
------------ ------------- 
CID          NUMBER            <- Primary key
NAME         VARCHAR2(30)  
STREET       VARCHAR2(100) 
CITY         VARCHAR2(30)  
STATE        VARCHAR2(2)   
ZIP          NUMBER(5)     

The type definition for the new object is:

CREATE TYPE addr_t AS OBJECT (  
   street VARCHAR2(100),        
   city VARCHAR2(30),           
   state VARCHAR2(2),           
   zip NUMBER(5, 0) );          
/

Here are the steps for this redefinition:

1. In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table.

   Specifically, the user must have the privileges described in "Privileges Required for the DBMS_REDEFINITION Package".

   See "Connecting to the Database with SQL*Plus".

2. Verify that the table is a candidate for online redefinition. Specify that the redefinition is to be done using primary keys or pseudo-primary keys.

   BEGIN
     DBMS_REDEFINITION.CAN_REDEF_TABLE(
       uname        => 'steve',
       tname        =>'customer',
       options_flag => DBMS_REDEFINITION.CONS_USE_PK);
   END;
   /
   

3. Create the interim table int_customer.

   CREATE TABLE int_customer(
     CID   NUMBER,
     NAME  VARCHAR2(30),          
     ADDR  addr_t);             
     
   

   Note that no primary key is defined on the interim table. When dependent objects are copied in step 6, the primary key constraint and index are copied.

4. Because customer is a very large table, specify parallel operations for the next step.

   ALTER SESSION FORCE PARALLEL DML PARALLEL 4;
   ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;
   

5. Start the redefinition process using primary keys.

   BEGIN
     DBMS_REDEFINITION.START_REDEF_TABLE(
      uname       => 'steve',
      orig_table  => 'customer',
      int_table   => 'int_customer',
      col_mapping => 'cid cid, name name,
         addr_t(street, city, state, zip) addr');
   END;
   /
   

   Note that addr_t(street, city, state, zip) is a call to the object constructor.

6. Copy dependent objects.

   DECLARE
   num_errors PLS_INTEGER;
   BEGIN
     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
       uname            => 'steve', 
       orig_table       => 'customer',
       int_table        => 'int_customer',
       copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
       copy_triggers    => TRUE, 
       copy_constraints => TRUE, 
       copy_privileges  => TRUE, 
       ignore_errors    => FALSE, 
       num_errors       => num_errors,
       copy_statistics  => TRUE);
   END;
   /
   

   Note that for this call, the final argument indicates that table statistics are to be copied to the interim table.

7. Optionally synchronize the interim table.

   BEGIN 
     DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
       uname      => 'steve', 
       orig_table => 'customer', 
       int_table  => 'int_customer');
   END;
   /
   

8. Complete the redefinition.

   BEGIN
     DBMS_REDEFINITION.FINISH_REDEF_TABLE(
       uname      => 'steve', 
       orig_table => 'customer', 
       int_table  => 'int_customer');
   END;
   /
   

   Consider specifying a non-NULL value for the dml_lock_timeout parameter in this procedure. See step 8 in "Performing Online Redefinition with Multiple Procedures in DBMS_REDEFINITION" for more information.

9. Wait for any long-running queries against the interim table to complete, and then drop the interim table.

Example 4

This example addresses the situation where a dependent object must be manually created and registered.

The table to be redefined is defined as follows:

CREATE TABLE steve.t1
  (c1 NUMBER);

The table has an index for column c1:

CREATE INDEX steve.index1 ON steve.t1(c1);

Consider the case where column c1 becomes column c2 after the redefinition. In this case, COPY_TABLE_DEPENDENTS tries to create an index on the interim table corresponding to index1, and tries to create it on a column c1, which does not exist in the interim table. This results in an error. You must therefore manually create the index on column c2 and register it.

Here are the steps for this redefinition:

1. In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table.

   Specifically, the user must have the privileges described in "Privileges Required for the DBMS_REDEFINITION Package".

   See "Connecting to the Database with SQL*Plus".

2. Ensure that t1 is a candidate for online redefinition with CAN_REDEF_TABLE, and then begin the redefinition process with START_REDEF_TABLE.

   BEGIN
     DBMS_REDEFINITION.CAN_REDEF_TABLE(
      uname        => 'steve',
      tname        => 't1',
      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
   END;
   /
   

3. Create the interim table int_t1 and create an index int_index1 on column c2.

   CREATE TABLE steve.int_t1
     (c2 NUMBER);
   
   CREATE INDEX steve.int_index1 ON steve.int_t1(c2);
   

4. Start the redefinition process.

   BEGIN
     DBMS_REDEFINITION.START_REDEF_TABLE(
       uname        => 'steve', 
       orig_table   => 't1',
       int_table    => 'int_t1',
       col_mapping  => 'c1 c2',
       options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
   END;
   /
   

5. Register the original (index1) and interim (int_index1) dependent objects.

   BEGIN
    DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
      uname         => 'steve',
      orig_table    => 't1',
      int_table     => 'int_t1',
      dep_type      => DBMS_REDEFINITION.CONS_INDEX,
      dep_owner     => 'steve',
      dep_orig_name => 'index1',
      dep_int_name  => 'int_index1');
   END;
   /
   

6. Copy the dependent objects.

   DECLARE
   num_errors PLS_INTEGER;
   BEGIN
     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
       uname            => 'steve', 
       orig_table       => 't1',
       int_table        => 'int_t1',
       copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
       copy_triggers    => TRUE, 
       copy_constraints => TRUE, 
       copy_privileges  => TRUE, 
       ignore_errors    => TRUE, 
       num_errors       => num_errors);
   END;
   /
   

7. Optionally synchronize the interim table.

   BEGIN 
     DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
       uname      => 'steve', 
       orig_table => 't1', 
       int_table  => 'int_t1');
   END;
   /
   

8. Complete the redefinition.

   BEGIN
     DBMS_REDEFINITION.FINISH_REDEF_TABLE(
       uname      => 'steve', 
       orig_table => 't1', 
       int_table  => 'int_t1');
   END;
   /
   

9. Wait for any long-running queries against the interim table to complete, and then drop the interim table.

Example 5

This example demonstrates redefining multiple partitions. It moves two of the partitions of a range-partitioned sales table to new tablespaces. The table containing the partitions to be redefined is defined as follows:

CREATE TABLE steve.salestable
  (s_productid NUMBER,
  s_saledate DATE,
  s_custid NUMBER,
  s_totalprice NUMBER)
  TABLESPACE users
  PARTITION BY RANGE(s_saledate)
  (PARTITION sal10q1 VALUES LESS THAN (TO_DATE('01-APR-2010', 'DD-MON-YYYY')),
  PARTITION sal10q2 VALUES LESS THAN (TO_DATE('01-JUL-2010', 'DD-MON-YYYY')),
  PARTITION sal10q3 VALUES LESS THAN (TO_DATE('01-OCT-2010', 'DD-MON-YYYY')),
  PARTITION sal10q4 VALUES LESS THAN (TO_DATE('01-JAN-2011', 'DD-MON-YYYY')));

This example moves the sal10q1 partition to the sales1 tablespace and the sal10q2 partition to the sales2 tablespace. The sal10q3 and sal10q4 partitions are not moved.

To move the partitions, the tablespaces sales1 and sales2 must exist. The following examples create these tablespaces:

CREATE TABLESPACE sales1 DATAFILE '/u02/oracle/data/sales01.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
 
CREATE TABLESPACE sales2 DATAFILE '/u02/oracle/data/sales02.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

The table has a local partitioned index that is defined as follows:

CREATE INDEX steve.sales_index ON steve.salestable 
   (s_saledate, s_productid, s_custid) LOCAL;

Here are the steps. In the following procedure calls, note the extra argument: partition name (part_name).

1. In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table.

   Specifically, the user must have the privileges described in "Privileges Required for the DBMS_REDEFINITION Package".

   See "Connecting to the Database with SQL*Plus".

2. Ensure that salestable is a candidate for redefinition.

   BEGIN
     DBMS_REDEFINITION.CAN_REDEF_TABLE(
      uname        => 'steve',
      tname        => 'salestable',
      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
      part_name    => 'sal10q1, sal10q2');
   END;
   /
   

3. Create the interim tables in the new tablespaces. Because this is a redefinition of a range partition, the interim tables are nonpartitioned.

   CREATE TABLE steve.int_salestb1
     (s_productid NUMBER,
     s_saledate DATE,
     s_custid NUMBER,
     s_totalprice NUMBER)
     TABLESPACE sales1;
   
   CREATE TABLE steve.int_salestb2
     (s_productid NUMBER,
     s_saledate DATE,
     s_custid NUMBER,
     s_totalprice NUMBER)
     TABLESPACE sales2;
   

4. Start the redefinition process using rowid.

   BEGIN
     DBMS_REDEFINITION.START_REDEF_TABLE(
      uname        => 'steve',
      orig_table   => 'salestable',
      int_table    => 'int_salestb1, int_salestb2',
      col_mapping  => NULL,
      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
      part_name    => 'sal10q1, sal10q2',
      continue_after_errors => TRUE);
   END;
   /
   

   Notice that the part_name parameter specifies both of the partitions and that the int_table parameter specifies the interim table for each partition. Also, the continue_after_errors parameter is set to TRUE so that the redefinition process continues even if it encounters an error for a particular partition.

5. Manually create any local indexes on the interim tables.

   CREATE INDEX steve.int_sales1_index ON steve.int_salestb1 
   (s_saledate, s_productid, s_custid)
   TABLESPACE sales1; 
   
   CREATE INDEX steve.int_sales2_index ON steve.int_salestb2 
   (s_saledate, s_productid, s_custid)
   TABLESPACE sales2; 
   

6. Optionally synchronize the interim tables.

   BEGIN 
     DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
      uname      => 'steve', 
      orig_table => 'salestable', 
      int_table  => 'int_salestb1, int_salestb2',
      part_name    => 'sal10q1, sal10q2',
      continue_after_errors => TRUE);
   END;
   /
   

7. Complete the redefinition.

   BEGIN 
     DBMS_REDEFINITION.FINISH_REDEF_TABLE(
      uname      => 'steve', 
      orig_table => 'salestable', 
      int_table  => 'int_salestb1, int_salestb2',
      part_name    => 'sal10q1, sal10q2',
      continue_after_errors => TRUE);
   END;
   /
   

   Consider specifying a non-NULL value for the dml_lock_timeout parameter in this procedure. See step 8 in "Performing Online Redefinition with Multiple Procedures in DBMS_REDEFINITION" for more information.

8. Wait for any long-running queries against the interim tables to complete, and then drop the interim tables.

9. (Optional) Query the DBA_REDEFINITION_STATUS view to ensure that the redefinition succeeded for each partition.

   SELECT BASE_TABLE_OWNER, BASE_TABLE_NAME, PREV_OPERATION, STATUS
     FROM DBA_REDEFINITION_STATUS;
   

   If redefinition failed for any partition, then query the DBA_REDEFINITION_ERRORS view to determine the cause of the failure. Correct the conditions that caused the failure, and rerun online redefinition.

The following query shows that two of the partitions in the table have been moved to the new tablespaces:

SELECT PARTITION_NAME, TABLESPACE_NAME FROM DBA_TAB_PARTITIONS
 WHERE TABLE_NAME = 'SALESTABLE';
 
PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL10Q1                        SALES1
SAL10Q2                        SALES2
SAL10Q3                        USERS
SAL10Q4                        USERS
 
4 rows selected.

Example 6

This example illustrates online redefinition of a table with virtual private database (VPD) policies. The example disables all triggers for a table without changing any of the column names or column types in the table.

The table to be redefined is defined as follows:

CREATE TABLE hr.employees( 
   employee_id    NUMBER(6)  PRIMARY KEY, 
   first_name     VARCHAR2(20), 
   last_name      VARCHAR2(25)
                  CONSTRAINT     emp_last_name_nn  NOT NULL, 
   email          VARCHAR2(25)
                  CONSTRAINT     emp_email_nn  NOT NULL, 
   phone_number   VARCHAR2(20), 
   hire_date      DATE
                  CONSTRAINT     emp_hire_date_nn  NOT NULL, 
   job_id         VARCHAR2(10)
                  CONSTRAINT     emp_job_nn  NOT NULL, 
   salary         NUMBER(8,2), 
   commission_pct NUMBER(2,2), 
   manager_id     NUMBER(6), 
   department_id  NUMBER(4),
                  CONSTRAINT     emp_salary_min
                     CHECK (salary > 0), 
                  CONSTRAINT     emp_email_uk
                     UNIQUE (email));

If you installed the Oracle-supplied sample schemas when you created your Oracle database, then this table exists in your database.

Assume that the following auth_emp_dep_100 function is created for the VPD policy:

CREATE OR REPLACE FUNCTION hr.auth_emp_dep_100( 
  schema_var IN VARCHAR2,
  table_var IN VARCHAR2
 )
 RETURN VARCHAR2
 AS
  return_val VARCHAR2 (400);
  unm        VARCHAR2(30);
 BEGIN
  SELECT USER INTO unm FROM DUAL;
  IF (unm = 'HR') THEN
  return_val := NULL;
 ELSE
  return_val := 'DEPARTMENT_ID = 100';
  END IF;
 RETURN return_val;
END auth_emp_dep_100;
/

The following ADD_POLICY procedure specifies a VPD policy for the original table hr.employees using the auth_emp_dep_100 function:

BEGIN
  DBMS_RLS.ADD_POLICY (
    object_schema    => 'hr',
    object_name      => 'employees',
    policy_name      => 'employees_policy',
    function_schema  => 'hr',
    policy_function  => 'auth_emp_dep_100',
    statement_types  => 'select, insert, update, delete'
   );
 END;
/

In this example, the hr.employees table is redefined to disable all of its triggers. No column names or column types are changed during redefinition. Therefore, specify DBMS_REDEFINITION.CONS_VPD_AUTO for the copy_vpd_opt in the START_REFEF_TABLE procedure.

The steps in this redefinition are illustrated below.

1. In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table and the required privileges for managing VPD policies.

   Specifically, the user must have the privileges described in "Privileges Required for the DBMS_REDEFINITION Package" and EXECUTE privilege on the DBMS_RLS package.

   See "Connecting to the Database with SQL*Plus".

2. Verify that the table is a candidate for online redefinition. In this case you specify that the redefinition is to be done using primary keys or pseudo-primary keys.

   BEGIN
     DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','employees',
         DBMS_REDEFINITION.CONS_USE_PK);
   END;
   /
   

3. Create an interim table hr.int_employees.

   CREATE TABLE hr.int_employees( 
      employee_id    NUMBER(6), 
      first_name     VARCHAR2(20), 
      last_name      VARCHAR2(25), 
      email          VARCHAR2(25), 
      phone_number   VARCHAR2(20), 
      hire_date      DATE,
      job_id         VARCHAR2(10), 
      salary         NUMBER(8,2), 
      commission_pct NUMBER(2,2), 
      manager_id     NUMBER(6), 
      department_id  NUMBER(4));
   

4. Start the redefinition process.

   BEGIN
     DBMS_REDEFINITION.START_REDEF_TABLE (
       uname          => 'hr',
       orig_table     => 'employees',
       int_table      => 'int_employees',
       col_mapping    => NULL,
       options_flag   => DBMS_REDEFINITION.CONS_USE_PK,
       orderby_cols   => NULL,
       part_name      => NULL,
       copy_vpd_opt   => DBMS_REDEFINITION.CONS_VPD_AUTO);
   END;
   /
   

   When the copy_vpd_opt parameter is set to DBMS_REDEFINITION.CONS_VPD_AUTO, only the table owner and the user invoking online redefinition can access the interim table during online redefinition.

   Also, notice that the col_mapping parameter is set to NULL. When the copy_vpd_opt parameter is set to DBMS_REDEFINITION.CONS_VPD_AUTO, the col_mapping parameter must be NULL or '*'. See "Handling Virtual Private Database (VPD) Policies During Online Redefinition".

5. Copy dependent objects. (Automatically create any triggers, indexes, materialized view logs, grants, and constraints on hr.int_employees.)

   DECLARE
   num_errors PLS_INTEGER;
   BEGIN
     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
       uname            => 'hr', 
       orig_table       => 'employees',
       int_table        => 'int_employees',
       copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
       copy_triggers    => TRUE, 
       copy_constraints => TRUE, 
       copy_privileges  => TRUE, 
       ignore_errors    => FALSE, 
       num_errors       => num_errors);
   END;
   /
   

6. Disable all of the triggers on the interim table.

   ALTER TABLE hr.int_employees
     DISABLE ALL TRIGGERS;
   

7. (Optional) Synchronize the interim table hr.int_employees.

   BEGIN 
     DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
       uname      => 'hr', 
       orig_table => 'employees', 
       int_table  => 'int_employees');
   END;
   /
   

8. Complete the redefinition.

   BEGIN
     DBMS_REDEFINITION.FINISH_REDEF_TABLE(
       uname      => 'hr', 
       orig_table => 'employees', 
       int_table  => 'int_employees');
   END;
   /
   

   The table hr.employees is locked in the exclusive mode only for a small window toward the end of this step. After this call the table hr.employees is redefined such that it has all the attributes of the hr.int_employees table.

   Consider specifying a non-NULL value for the dml_lock_timeout parameter in this procedure. See step 8 in "Performing Online Redefinition with Multiple Procedures in DBMS_REDEFINITION" for more information.

9. Wait for any long-running queries against the interim table to complete, and then drop the interim table.

Example 7

This example illustrates online redefinition of a table with virtual private database (VPD) policies. The example changes the name of a column in the table.

The table to be redefined is defined as follows:

CREATE TABLE oe.orders( 
   order_id      NUMBER(12)  PRIMARY KEY,
   order_date    TIMESTAMP WITH LOCAL TIME ZONE CONSTRAINT order_date_nn NOT NULL,
   order_mode    VARCHAR2(8),
   customer_id   NUMBER(6) CONSTRAINT order_customer_id_nn NOT NULL,
   order_status  NUMBER(2),
   order_total   NUMBER(8,2),
   sales_rep_id  NUMBER(6),
   promotion_id  NUMBER(6),
   CONSTRAINT    order_mode_lov
                 CHECK (order_mode in ('direct','online')),
   CONSTRAINT    order_total_min
                 check (order_total >= 0));

If you installed the Oracle-supplied sample schemas when you created your Oracle database, then this table exists in your database.

Assume that the following auth_orders function is created for the VPD policy:

CREATE OR REPLACE FUNCTION oe.auth_orders( 
  schema_var IN VARCHAR2,
  table_var IN VARCHAR2
 )
 RETURN VARCHAR2
 AS
  return_val VARCHAR2 (400);
  unm        VARCHAR2(30);
 BEGIN
  SELECT USER INTO unm FROM DUAL;
  IF (unm = 'OE') THEN
  return_val := NULL;
 ELSE
  return_val := 'SALES_REP_ID = 159';
  END IF;
 RETURN return_val;
END auth_orders;
/

The following ADD_POLICY procedure specifies a VPD policy for the original table oe.orders using the auth_orders function:

BEGIN
  DBMS_RLS.ADD_POLICY (
    object_schema    => 'oe',
    object_name      => 'orders',
    policy_name      => 'orders_policy',
    function_schema  => 'oe',
    policy_function  => 'auth_orders',
    statement_types  => 'select, insert, update, delete');
 END;
/

In this example, the table is redefined to change the sales_rep_id column to sale_pid. When one or more column names or column types change during redefinition, you must specify DBMS_REDEFINITION.CONS_VPD_MANUAL for the copy_vpd_opt in the START_REFEF_TABLE procedure.

The steps in this redefinition are illustrated below.

1. In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table and the required privileges for managing VPD policies.

   Specifically, the user must have the privileges described in "Privileges Required for the DBMS_REDEFINITION Package" and EXECUTE privilege on the DBMS_RLS package.

   See "Connecting to the Database with SQL*Plus".

2. Verify that the table is a candidate for online redefinition. In this case you specify that the redefinition is to be done using primary keys or pseudo-primary keys.

   BEGIN
     DBMS_REDEFINITION.CAN_REDEF_TABLE(
       uname        => 'oe',
       tname        => 'orders',
       options_flag => DBMS_REDEFINITION.CONS_USE_PK);
   END;
   /
   

3. Create an interim table oe.int_orders.

   CREATE TABLE oe.int_orders( 
      order_id      NUMBER(12),
      order_date    TIMESTAMP WITH LOCAL TIME ZONE,
      order_mode    VARCHAR2(8),
      customer_id   NUMBER(6),
      order_status  NUMBER(2),
      order_total   NUMBER(8,2),
      sales_pid     NUMBER(6),
      promotion_id  NUMBER(6));
   

   Note that the sales_rep_id column is changed to the sales_pid column in the interim table.

4. Start the redefinition process.

   BEGIN
     DBMS_REDEFINITION.START_REDEF_TABLE (
       uname          => 'oe',
       orig_table     => 'orders',
       int_table      => 'int_orders',
       col_mapping    => 'order_id order_id, order_date order_date, order_mode 
                          order_mode, customer_id customer_id, order_status 
                          order_status, order_total order_total, sales_rep_id 
                          sales_pid, promotion_id promotion_id',
       options_flag   => DBMS_REDEFINITION.CONS_USE_PK,
       orderby_cols   => NULL,
       part_name      => NULL,
       copy_vpd_opt   => DBMS_REDEFINITION.CONS_VPD_MANUAL);
   END;
   /
   

   Because a column name is different in the original table and the interim table, DBMS_REDEFINITION.CONS_VPD_MANUAL must be specified for the copy_vpd_opt parameter. See "Handling Virtual Private Database (VPD) Policies During Online Redefinition".

5. Create the VPD policy on the interim table.

   In this example, complete the following steps:

   1. Create a new function called auth_orders_sales_pid for the VPD policy that specifies the sales_pid column instead of the sales_rep_id column:

      CREATE OR REPLACE FUNCTION oe.auth_orders_sales_pid( 
        schema_var IN VARCHAR2,
        table_var IN VARCHAR2
       )
       RETURN VARCHAR2
       AS
        return_val VARCHAR2 (400);
        unm        VARCHAR2(30);
       BEGIN
        SELECT USER INTO unm FROM DUAL;
        IF (unm = 'OE') THEN
        return_val := NULL;
       ELSE
        return_val := 'SALES_PID = 159';
        END IF;
       RETURN return_val;
      END auth_orders_sales_pid;
      /
      

   2. Run the ADD_POLICY procedure and specify the new function auth_orders_sales_pid and the interim table int_orders:

      BEGIN
        DBMS_RLS.ADD_POLICY (
          object_schema    => 'oe',
          object_name      => 'int_orders',
          policy_name      => 'orders_policy',
          function_schema  => 'oe',
          policy_function  => 'auth_orders_sales_pid',
          statement_types  => 'select, insert, update, delete');
       END;
      /
      

6. Copy dependent objects. (Automatically create any triggers, indexes, materialized view logs, grants, and constraints on oe.int_orders.)

   DECLARE
   num_errors PLS_INTEGER;
   BEGIN
     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
       uname            => 'oe', 
       orig_table       => 'orders',
       int_table        => 'int_orders',
       copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
       copy_triggers    => TRUE, 
       copy_constraints => TRUE, 
       copy_privileges  => TRUE, 
       ignore_errors    => TRUE, 
       num_errors       => num_errors);
   END;
   /
   

   Note that the ignore_errors argument is set to TRUE for this call. The reason is that the original table has an index and a constraint related to the sales_rep_id column, and this column is changed to sales_pid in the interim table. The next step shows the errors and describes how to create the index and the constraint on the interim table.

7. Query the DBA_REDEFINITION_ERRORS view to check for errors.

   SET LONG  8000
   SET PAGES 8000
   COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A20
   COLUMN BASE_TABLE_NAME HEADING 'Base Table Name' FORMAT A10
   COLUMN DDL_TXT HEADING 'DDL That Caused Error' FORMAT A40
    
   SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT FROM 
            DBA_REDEFINITION_ERRORS;
   
   Object Name          Base Table DDL That Caused Error
   -------------------- ---------- ----------------------------------------
   ORDERS_SALES_REP_FK  ORDERS     ALTER TABLE "OE"."INT_ORDERS" ADD CONSTR
                                   AINT "TMP$$_ORDERS_SALES_REP_FK1" FOREIG
                                   N KEY ("SALES_REP_ID")
                                             REFERENCES "HR"."EMPLOYEES" 
                                   ("EMPLOYE
                                   E_ID") ON DELETE SET NULL DISABLE
   ORD_SALES_REP_IX     ORDERS     CREATE INDEX "OE"."TMP$$_ORD_SALES_REP_I
                                   X0" ON "OE"."INT_ORDERS" ("SALES_REP_ID"
                                   )
                                     PCTFREE 10 INITRANS 2 MAXTRANS 255 COM
                                   PUTE STATISTICS
                                     STORAGE(INITIAL 65536 NEXT 1048576 MIN
                                   EXTENTS 1 MAXEXTENTS 2147483645
                                     PCTINCREASE 0 FREELISTS 1 FREELIST GRO
                                   UPS 1
                                     BUFFER_POOL DEFAULT)
                                     TABLESPACE "EXAMPLE"
   TMP$$_ORDERS_SALES_R ORDERS     ALTER TABLE "OE"."INT_ORDERS" ADD CONSTR
   EP_FK0                          AINT "TMP$$_TMP$$_ORDERS_SALES_RE0" FORE
                                   IGN KEY ("SALES_REP_ID")
                                             REFERENCES "HR"."INT_EMPLOYEES" 
                                   ("EMP
                                   LOYEE_ID") ON DELETE SET NULL DISABLE
   

   If necessary, correct the errors reported in the output.

   In this example, original table has an index and a foreign key constraint on the sales_rep_id column. The index and the constraint could not be copied to the interim table because the name of the column changed from sales_rep_id to sales_pid.

   To correct the problems, add the index and the constraint on the interim table by completing the following steps:

   1. Add the index:

      ALTER TABLE oe.int_orders 
        ADD (CONSTRAINT orders_sales_pid_fk 
            FOREIGN KEY (sales_pid) 
            REFERENCES hr.employees(employee_id)
            ON DELETE SET NULL);
      

   2. Add the foreign key constraint:

      CREATE INDEX ord_sales_pid_ix ON oe.int_orders (sales_pid);
      

8. (Optional) Synchronize the interim table oe.int_orders.

   BEGIN 
     DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
       uname      => 'oe', 
       orig_table => 'orders', 
       int_table  => 'int_orders');
   END;
   /
   

9. Complete the redefinition.

   BEGIN
     DBMS_REDEFINITION.FINISH_REDEF_TABLE(
       uname      => 'oe', 
       orig_table => 'orders', 
       int_table  => 'int_orders');
   END;
   /
   

   The table oe.orders is locked in the exclusive mode only for a small window toward the end of this step. After this call the table oe.orders is redefined such that it has all the attributes of the oe.int_orders table.

   Consider specifying a non-NULL value for the dml_lock_timeout parameter in this procedure. See step 8 in "Performing Online Redefinition with Multiple Procedures in DBMS_REDEFINITION" for more information.

10. Wait for any long-running queries against the interim table to complete, and then drop the interim table.

Example 8

This example illustrates making multiple changes to a table using online redefinition.

The table to be redefined is defined as follows:

CREATE TABLE testredef.original( 
   col1 NUMBER PRIMARY KEY,
   col2 VARCHAR2(10),
   col3 CLOB,
   col4 DATE)
ORGANIZATION INDEX;

The table is redefined as follows:

• The table is compressed with advanced row compression.

• The LOB column is changed to SecureFiles LOB storage.

• The table's tablespace is changed from example to testredeftbs, and the table's block size is changed from 8KB to 16KB.

  This example assumes that the database block size is 8KB. This example also assumes that the DB_16K_CACHE_SIZE initialization parameter is set and that the testredef tablespace was created with a 16KB block size. For example:

  CREATE TABLESPACE testredeftbs  
    DATAFILE '/u01/app/oracle/oradata/testredef01.dbf' SIZE 500M   EXTENT MANAGEMENT LOCAL AUTOALLOCATE 
    SEGMENT SPACE MANAGEMENT AUTO 
    BLOCKSIZE 16384;
  

• The table is partitioned on the col1 column.

• The col5 column is added.

• The col2 column is dropped.

• Columns col3 and col4 are renamed, and their position in the table is changed.

• The type of the col3 column is changed from DATE to TIMESTAMP.

• The table is changed from an index-organized table (IOT) to a heap-organized table.

• The table is defragmented.

  To demonstrate defragmentation, the table must be populated. For the purposes of this example, you can use this PL/SQL block to populate the table:

  DECLARE
    V_CLOB CLOB;
  BEGIN
     FOR I IN 0..999 LOOP
        V_CLOB := NULL;
        FOR J IN 1..1000 LOOP
           V_CLOB := V_CLOB||TO_CHAR(I,'0000');
        END LOOP;
        INSERT INTO testredef.original VALUES(I,TO_CHAR(I),V_CLOB,SYSDATE+I);
        COMMIT;
     END LOOP;
     COMMIT;
  END;
  /
  

  Run the following SQL statement to fragment the table by deleting every third row:

  DELETE FROM testredef.original WHERE (COL1/3) <> TRUNC(COL1/3);
  

  You can confirm the fragmentation by using the DBMS_SPACE.SPACE_USAGE procedure.

  See Also:

  Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_SPACE.SPACE_USAGE procedure

The steps in this redefinition are illustrated below.

1. In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table.

   Specifically, the user must have the privileges described in "Privileges Required for the DBMS_REDEFINITION Package".

   See "Connecting to the Database with SQL*Plus".

2. Verify that the table is a candidate for online redefinition. In this case you specify that the redefinition is to be done using primary keys or pseudo-primary keys.

   BEGIN
     DBMS_REDEFINITION.CAN_REDEF_TABLE(
       uname        => 'testredef',
       tname        => 'original',
       options_flag => DBMS_REDEFINITION.CONS_USE_PK);
   END;
   /
   

3. Create an interim table testredef.interim.

   CREATE TABLE testredef.interim( 
       col1 NUMBER,
       col3 TIMESTAMP,
       col4 CLOB,
       col5 VARCHAR2(3))
       LOB(col4) STORE AS SECUREFILE (NOCACHE FILESYSTEM_LIKE_LOGGING)
       PARTITION BY RANGE (COL1) (
          PARTITION par1 VALUES LESS THAN (333),
          PARTITION par2 VALUES LESS THAN (666),
          PARTITION par3 VALUES LESS THAN (MAXVALUE))
      TABLESPACE testredeftbs
      ROW STORE COMPRESS ADVANCED;
   

4. Start the redefinition process.

   BEGIN
     DBMS_REDEFINITION.START_REDEF_TABLE(
        uname        => 'testredef',
        orig_table   => 'original',
        int_table    => 'interim',
        col_mapping  => 'col1 col1, TO_TIMESTAMP(col4) col3, col3 col4',
        options_flag => DBMS_REDEFINITION.CONS_USE_PK);
   END;
   /
   

5. Copy the dependent objects.

   DECLARE
   num_errors PLS_INTEGER;
   BEGIN
     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
       uname            => 'testredef', 
       orig_table       => 'original',
       int_table        => 'interim',
       copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
       copy_triggers    => TRUE, 
       copy_constraints => TRUE, 
       copy_privileges  => TRUE, 
       ignore_errors    => TRUE, 
       num_errors       => num_errors);
   END;
   /
   

6. Optionally synchronize the interim table.

   BEGIN 
     DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
       uname       => 'testredef', 
       orig_table  => 'original',
       int_table   => 'interim');
   END;
   /
   

7. Complete the redefinition.

   BEGIN
     DBMS_REDEFINITION.FINISH_REDEF_TABLE(
       uname       => 'testredef', 
       orig_table  => 'original',
       int_table   => 'interim');
   END;
   /
   

Privileges Required for the DBMS_REDEFINITION Package

Execute privileges on the DBMS_REDEFINITION package are required to run subprograms in the package. Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE.

In addition, for a user to redefine a table in the user's schema using the package, the user must be granted the following privileges:

• CREATE TABLE

• CREATE MATERIALIZED VIEW

The CREATE TRIGGER privilege is also required to execute the COPY_TABLE_DEPENDENTS procedure.

For a user to redefine a table in other schemas using the package, the user must be granted the following privileges:

• CREATE ANY TABLE

• ALTER ANY TABLE

• DROP ANY TABLE

• LOCK ANY TABLE

• SELECT ANY TABLE

The following additional privileges are required to execute COPY_TABLE_DEPENDENTS on tables in other schemas:

• CREATE ANY TRIGGER

• CREATE ANY INDEX

What Is the Recycle Bin?

The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

Each user can be thought of as having his own recycle bin, because, unless a user has the SYSDBA privilege, the only objects that the user has access to in the recycle bin are those that the user owns. A user can view his objects in the recycle bin using the following statement:

SELECT * FROM RECYCLEBIN;

Only the DROP TABLE SQL statement places objects in the recycle bin. It adds the table and its associated objects so that they can be recovered as a group. In addition to the table itself, the associated objects that are added to the recycle bin can include the following types of objects:

• Nested tables

• LOB segments

• Indexes

• Constraints (excluding foreign key constraints)

• Triggers

• Clusters

When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:

• When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.

• When you drop a cluster, its member tables are not placed in the recycle bin and any former member tables in the recycle bin are purged.

• When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and any former dependent objects in the recycle bin are purged.

Object Naming in the Recycle Bin

When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names. This is necessary to avoid name conflicts that may arise if multiple tables have the same name. This could occur under the following circumstances:

• A user drops a table, re-creates it with the same name, then drops it again.

• Two users have tables with the same name, and both users drop their tables.

The renaming convention is as follows:

BIN$unique_id$version

where:

• unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases

• version is a version number assigned by the database

Enabling and Disabling the Recycle Bin

When the recycle bin is enabled, dropped tables and their dependent objects are placed in the recycle bin. When the recycle bin is disabled, dropped tables and their dependent objects are not placed in the recycle bin; they are just dropped, and you must use other means to recover them (such as recovering from backup).

Disabling the recycle bin does not purge or otherwise affect objects already in the recycle bin. The recycle bin is enabled by default.

You enable and disable the recycle bin by changing the recyclebin initialization parameter. This parameter is not dynamic, so a database restart is required when you change it with an ALTER SYSTEM statement.

To disable the recycle bin:

1. Issue one of the following statements:

   ALTER SESSION SET recyclebin = OFF;
   
   ALTER SYSTEM SET recyclebin = OFF SCOPE = SPFILE;
   

2. If you used ALTER SYSTEM, restart the database.

To enable the recycle bin:

1. Issue one of the following statements:

   ALTER SESSION SET recyclebin = ON;
   
   ALTER SYSTEM SET recyclebin = ON SCOPE = SPFILE;
   

2. If you used ALTER SYSTEM, restart the database.

Viewing and Querying Objects in the Recycle Bin

Oracle Database provides two views for obtaining information about objects in the recycle bin:

One use for these views is to identify the name that the database has assigned to a dropped object, as shown in the following example:

SELECT object_name, original_name FROM dba_recyclebin
   WHERE owner = 'HR';

OBJECT_NAME                    ORIGINAL_NAME
------------------------------ --------------------------------
BIN$yrMKlZaLMhfgNAgAIMenRA==$0 EMPLOYEES

You can also view the contents of the recycle bin using the SQL*Plus command SHOW RECYCLEBIN.

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
EMPLOYEES        BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TABLE        2003-10-27:14:00:19

You can query objects that are in the recycle bin, just as you can query other objects. However, you must specify the name of the object as it is identified in the recycle bin. For example:

SELECT * FROM "BIN$yrMKlZaVMhfgNAgAIMenRA==$0";

Purging Objects in the Recycle Bin

If you decide that you are never going to restore an item from the recycle bin, you can use the PURGE statement to remove the items and their associated objects from the recycle bin and release their storage space. You need the same privileges as if you were dropping the item.

When you use the PURGE statement to purge a table, you can use the name that the table is known by in the recycle bin or the original name of the table. The recycle bin name can be obtained from either the DBA_ or USER_RECYCLEBIN view as shown in "Viewing and Querying Objects in the Recycle Bin". The following hypothetical example purges the table hr.int_admin_emp, which was renamed to BIN$jsleilx392mk2=293$0 when it was placed in the recycle bin:

PURGE TABLE "BIN$jsleilx392mk2=293$0";

You can achieve the same result with the following statement:

PURGE TABLE int_admin_emp;

You can use the PURGE statement to purge all the objects in the recycle bin that are from a specified tablespace or only the tablespace objects belonging to a specified user, as shown in the following examples:

PURGE TABLESPACE example;
PURGE TABLESPACE example USER oe;

Users can purge the recycle bin of their own objects, and release space for objects, by using the following statement:

PURGE RECYCLEBIN;

If you have the SYSDBA privilege or the PURGE DBA_RECYCLEBIN system privilege, then you can purge the entire recycle bin by specifying DBA_RECYCLEBIN, instead of RECYCLEBIN in the previous statement.

You can also use the PURGE statement to purge an index from the recycle bin or to purge from the recycle bin all objects in a specified tablespace.

Restoring Tables from the Recycle Bin

Use the FLASHBACK TABLE ... TO BEFORE DROP statement to recover objects from the recycle bin. You can specify either the name of the table in the recycle bin or the original table name. An optional RENAME TO clause lets you rename the table as you recover it. The recycle bin name can be obtained from either the DBA_ or USER_RECYCLEBIN view as shown in "Viewing and Querying Objects in the Recycle Bin". To use the FLASHBACK TABLE ... TO BEFORE DROP statement, you need the same privileges required to drop the table.

The following example restores int_admin_emp table and assigns to it a new name:

FLASHBACK TABLE int_admin_emp TO BEFORE DROP 
   RENAME TO int2_admin_emp;

The system-generated recycle bin name is very useful if you have dropped a table multiple times. For example, suppose you have three versions of the int2_admin_emp table in the recycle bin and you want to recover the second version. You can do this by issuing two FLASHBACK TABLE statements, or you can query the recycle bin and then flashback to the appropriate system-generated name, as shown in the following example. Including the create time in the query can help you verify that you are restoring the correct table.

SELECT object_name, original_name, createtime FROM recyclebin;    

OBJECT_NAME                    ORIGINAL_NAME   CREATETIME
------------------------------ --------------- -------------------
BIN$yrMKlZaLMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP  2006-02-05:21:05:52
BIN$yrMKlZaVMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP  2006-02-05:21:25:13
BIN$yrMKlZaQMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP  2006-02-05:22:05:53

FLASHBACK TABLE "BIN$yrMKlZaVMhfgNAgAIMenRA==$0" TO BEFORE DROP;

Restoring Dependent Objects

When you restore a table from the recycle bin, dependent objects such as indexes do not get their original names back; they retain their system-generated recycle bin names. You must manually rename dependent objects to restore their original names. If you plan to manually restore original names for dependent objects, ensure that you make note of each dependent object's system-generated recycle bin name before you restore the table.

The following is an example of restoring the original names of some of the indexes of the dropped table JOB_HISTORY, from the HR sample schema. The example assumes that you are logged in as the HR user.

1. After dropping JOB_HISTORY and before restoring it from the recycle bin, run the following query:

   SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;
   
   OBJECT_NAME                    ORIGINAL_NAME             TYPE
   ------------------------------ ------------------------- --------
   BIN$DBo9UChtZSbgQFeMiAdCcQ==$0 JHIST_JOB_IX              INDEX
   BIN$DBo9UChuZSbgQFeMiAdCcQ==$0 JHIST_EMPLOYEE_IX         INDEX
   BIN$DBo9UChvZSbgQFeMiAdCcQ==$0 JHIST_DEPARTMENT_IX       INDEX
   BIN$DBo9UChwZSbgQFeMiAdCcQ==$0 JHIST_EMP_ID_ST_DATE_PK   INDEX
   BIN$DBo9UChxZSbgQFeMiAdCcQ==$0 JOB_HISTORY               TABLE
   

2. Restore the table with the following command:

   FLASHBACK TABLE JOB_HISTORY TO BEFORE DROP;
   

3. Run the following query to verify that all JOB_HISTORY indexes retained their system-generated recycle bin names:

   SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'JOB_HISTORY';
    
   INDEX_NAME
   ------------------------------
   BIN$DBo9UChwZSbgQFeMiAdCcQ==$0
   BIN$DBo9UChtZSbgQFeMiAdCcQ==$0
   BIN$DBo9UChuZSbgQFeMiAdCcQ==$0
   BIN$DBo9UChvZSbgQFeMiAdCcQ==$0
   

4. Restore the original names of the first two indexes as follows:

   ALTER INDEX "BIN$DBo9UChtZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_JOB_IX;
   ALTER INDEX "BIN$DBo9UChuZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_EMPLOYEE_IX;
   

   Note that double quotes are required around the system-generated names.

What Are Index-Organized Tables?

An index-organized table has a storage organization that is a variant of a primary B-tree. Unlike an ordinary (heap-organized) table whose data is stored as an unordered collection (heap), data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Each leaf block in the index structure stores both the key and nonkey columns.

The structure of an index-organized table provides the following benefits:

• Fast random access on the primary key because an index-only scan is sufficient. And, because there is no separate table storage area, changes to the table data (such as adding new rows, updating rows, or deleting rows) result only in updating the index structure.

• Fast range access on the primary key because the rows are clustered in primary key order.

• Lower storage requirements because duplication of primary keys is avoided. They are not stored both in the index and underlying table, as is true with heap-organized tables.

Index-organized tables have full table functionality. They support features such as constraints, triggers, LOB and object columns, partitioning, parallel operations, online reorganization, and replication. And, they offer these additional features:

• Prefix compression

• Overflow storage area and specific column placement

• Secondary indexes, including bitmap indexes.

Index-organized tables are ideal for OLTP applications, which require fast primary key access and high availability. For example, queries and DML on an orders table used in electronic order processing are predominantly based on primary key access, and heavy volume of concurrent DML can cause row chaining and inefficient space usage in indexes, resulting in a frequent need to reorganize. Because an index-organized table can be reorganized online and without invalidating its secondary indexes, the window of unavailability is greatly reduced or eliminated.

Index-organized tables are suitable for modeling application-specific index structures. For example, content-based information retrieval applications containing text, image and audio data require inverted indexes that can be effectively modeled using index-organized tables. A fundamental component of an internet search engine is an inverted index that can be modeled using index-organized tables.

These are but a few of the applications for index-organized tables.

Creating Index-Organized Tables

You use the CREATE TABLE statement to create index-organized tables, but you must provide additional information:

• An ORGANIZATION INDEX qualifier, which indicates that this is an index-organized table

• A primary key, specified through a column constraint clause (for a single column primary key) or a table constraint clause (for a multiple-column primary key).

Optionally, you can specify the following:

• An OVERFLOW clause, which preserves dense clustering of the B-tree index by enabling the storage of some of the nonkey columns in a separate overflow data segment.

• A PCTTHRESHOLD value, which, when an overflow segment is being used, defines the maximum size of the portion of the row that is stored in the index block, as a percentage of block size. Rows columns that would cause the row size to exceed this maximum are stored in the overflow segment. The row is broken at a column boundary into two pieces, a head piece and tail piece. The head piece fits in the specified threshold and is stored along with the key in the index leaf block. The tail piece is stored in the overflow area as one or more row pieces. Thus, the index entry contains the key value, the nonkey column values that fit the specified threshold, and a pointer to the rest of the row.

• An INCLUDING clause, which can be used to specify the nonkey columns that are to be stored in the index block with the primary key.

CREATE TABLE admin_docindex(
        token char(20), 
        doc_id NUMBER,
        token_frequency NUMBER,
        token_offsets VARCHAR2(2000),
        CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id))
    ORGANIZATION INDEX 
    TABLESPACE admin_tbs
    PCTTHRESHOLD 20
    OVERFLOW TABLESPACE admin_tbs2;

CREATE OR REPLACE TYPE admin_typ AS OBJECT
    (col1 NUMBER, col2 VARCHAR2(6));
CREATE TABLE admin_iot (c1 NUMBER primary key, c2 admin_typ)
    ORGANIZATION INDEX;

CREATE TABLE admin_iot2 OF admin_typ (col1 PRIMARY KEY)
    ORGANIZATION INDEX;

CREATE TYPE project_t AS OBJECT(pno NUMBER, pname VARCHAR2(80));
/
CREATE TYPE project_set AS TABLE OF project_t;
/
CREATE TABLE proj_tab (eno NUMBER, projects PROJECT_SET)
    NESTED TABLE projects STORE AS emp_project_tab
                ((PRIMARY KEY(nested_table_id, pno)) 
    ORGANIZATION INDEX)
    RETURN AS LOCATOR;

CREATE TABLE admin_iot4(a INT, b INT, c INT, d INT, 
                primary key(c,b))
    ORGANIZATION INDEX;

CREATE TABLE admin_docindex2(
        token CHAR(20), 
        doc_id NUMBER,
        token_frequency NUMBER,
        token_offsets VARCHAR2(2000),
        CONSTRAINT pk_admin_docindex2 PRIMARY KEY (token, doc_id))
    ORGANIZATION INDEX 
    TABLESPACE admin_tbs
    PCTTHRESHOLD 20
    INCLUDING token_frequency
    OVERFLOW TABLESPACE admin_tbs2;

CREATE TABLE admin_iot3(i PRIMARY KEY, j, k, l) 
     ORGANIZATION INDEX 
     PARALLEL
     AS SELECT * FROM hr.jobs;

CREATE TABLE admin_iot5(i INT, j INT, k INT, l INT, PRIMARY KEY (i, j, k)) 
    ORGANIZATION INDEX COMPRESS;

CREATE TABLE admin_iot6(i INT, j INT, k INT, l INT, PRIMARY KEY(i, j, k)) 
    ORGANIZATION INDEX COMPRESS 2;

CREATE TABLE admin_iot7(i INT, j INT, k INT, l INT, PRIMARY KEY (i, j, k)) 
    ORGANIZATION INDEX COMPRESS 1;

ALTER TABLE admin_iot5 MOVE NOCOMPRESS;

Maintaining Index-Organized Tables

Index-organized tables differ from ordinary tables only in physical organization. Logically, they are manipulated in the same manner as ordinary tables. You can specify an index-organized table just as you would specify a regular table in INSERT, SELECT, DELETE, and UPDATE statements.

ALTER TABLE admin_docindex INITRANS 4 OVERFLOW INITRANS 6;

ALTER TABLE admin_docindex PCTTHRESHOLD 15 INCLUDING doc_id;

ALTER TABLE admin_iot3 ADD OVERFLOW TABLESPACE admin_tbs2;

ALTER TABLE admin_docindex MOVE;

ALTER TABLE admin_docindex MOVE ONLINE;

ALTER TABLE admin_docindex MOVE TABLESPACE admin_tbs2 
    OVERFLOW TABLESPACE admin_tbs3;

CREATE TABLE admin_iot_lob
   (c1 number (6) primary key,
    admin_lob CLOB)
   ORGANIZATION INDEX
   LOB (admin_lob) STORE AS (TABLESPACE admin_tbs2);
.
.
.
ALTER TABLE admin_iot_lob MOVE LOB (admin_lob) STORE AS (TABLESPACE admin_tbs3); 

Creating Secondary Indexes on Index-Organized Tables

You can create secondary indexes on an index-organized tables to provide multiple access paths. Secondary indexes on index-organized tables differ from indexes on ordinary tables in two ways:

• They store logical rowids instead of physical rowids. This is necessary because the inherent movability of rows in a B-tree index results in the rows having no permanent physical addresses. If the physical location of a row changes, its logical rowid remains valid. One effect of this is that a table maintenance operation, such as ALTER TABLE ... MOVE, does not make the secondary index unusable.

• The logical rowid also includes a physical guess which identifies the database block address at which the row is likely to be found. If the physical guess is correct, a secondary index scan would incur a single additional I/O once the secondary key is found. The performance would be similar to that of a secondary index-scan on an ordinary table.

Unique and non-unique secondary indexes, function-based secondary indexes, and bitmap indexes are supported as secondary indexes on index-organized tables.

CREATE INDEX Doc_id_index on Docindex(Doc_id, Token);

SELECT Token FROM Docindex WHERE Doc_id = 1;

Analyzing Index-Organized Tables

Just like ordinary tables, index-organized tables are analyzed using the DBMS_STATS package, or the ANALYZE statement.

EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('HR','COUNTRIES');

Using the ORDER BY Clause with Index-Organized Tables

If an ORDER BY clause only references the primary key column or a prefix of it, then the optimizer avoids the sorting overhead, as the rows are returned sorted on the primary key columns.

The following queries avoid sorting overhead because the data is already sorted on the primary key:

SELECT * FROM admin_docindex2 ORDER BY token, doc_id;
SELECT * FROM admin_docindex2 ORDER BY token;

If, however, you have an ORDER BY clause on a suffix of the primary key column or non-primary-key columns, additional sorting is required (assuming no other secondary indexes are defined).

SELECT * FROM admin_docindex2 ORDER BY doc_id;
SELECT * FROM admin_docindex2 ORDER BY token_frequency;

Converting Index-Organized Tables to Regular Tables

You can convert index-organized tables to regular (heap organized) tables using the Oracle import or export utilities, or the CREATE TABLE...AS SELECT statement.

To convert an index-organized table to a regular table:

• Export the index-organized table data using conventional path.

• Create a regular table definition with the same definition.

• Import the index-organized table data, making sure IGNORE=y (ensures that object exists error is ignored).

  Note:

  Before converting an index-organized table to a regular table, be aware that index-organized tables cannot be exported using pre-Oracle8 versions of the Export utility.

  See Also:

  Oracle Database Utilities for more details about using the original IMP and EXP utilities and the Data Pump import and export utilities

About External Tables

Oracle Database allows you read-only access to data in external tables. External tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided. By providing the database with metadata describing an external table, the database is able to expose the data in the external table as if it were data residing in a regular database table. The external data can be queried directly and in parallel using SQL.

You can, for example, select, join, or sort external table data. You can also create views and synonyms for external tables. However, no DML operations (UPDATE, INSERT, or DELETE) are possible, and no indexes can be created, on external tables.

External tables provide a framework to unload the result of an arbitrary SELECT statement into a platform-independent Oracle-proprietary format that can be used by Oracle Data Pump. External tables provide a valuable means for performing basic extraction, transformation, and loading (ETL) tasks that are common for data warehousing.

The means of defining the metadata for external tables is through the CREATE TABLE...ORGANIZATION EXTERNAL statement. This external table definition can be thought of as a view that allows running any SQL query against external data without requiring that the external data first be loaded into the database. An access driver is the actual mechanism used to read the external data in the table. When you use external tables to unload data, the metadata is automatically created based on the data types in the SELECT statement.

Oracle Database provides two access drivers for external tables. The default access driver is ORACLE_LOADER, which allows the reading of data from external files using the Oracle loader technology. The ORACLE_LOADER access driver provides data mapping capabilities which are a subset of the control file syntax of SQL*Loader utility. The second access driver, ORACLE_DATAPUMP, lets you unload data—that is, read data from the database and insert it into an external table, represented by one or more external files—and then reload it into an Oracle Database.

Creating External Tables

You create external tables using the CREATE TABLE statement with an ORGANIZATION EXTERNAL clause. This statement creates only metadata in the data dictionary.

The following example creates an external table and then uploads the data to a database table. Alternatively, you can unload data through the external table framework by specifying the AS subquery clause of the CREATE TABLE statement. External table data pump unload can use only the ORACLE_DATAPUMP access driver.

EXAMPLE: Creating an External Table and Loading Data

In this example, the data for the external table resides in the two text files empxt1.dat and empxt2.dat.

The file empxt1.dat contains the following sample data:

360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus
361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper
362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr
363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda

The file empxt2.dat contains the following sample data:

401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel
402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega
403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins
404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

The following SQL statements create an external table named admin_ext_employees in the hr schema and load data from the external table into the hr.employees table.

CONNECT  /  AS SYSDBA;
-- Set up directories and grant access to hr 
CREATE OR REPLACE DIRECTORY admin_dat_dir
    AS '/flatfiles/data'; 
CREATE OR REPLACE DIRECTORY admin_log_dir 
    AS '/flatfiles/log'; 
CREATE OR REPLACE DIRECTORY admin_bad_dir 
    AS '/flatfiles/bad'; 
GRANT READ ON DIRECTORY admin_dat_dir TO hr; 
GRANT WRITE ON DIRECTORY admin_log_dir TO hr; 
GRANT WRITE ON DIRECTORY admin_bad_dir TO hr;
-- hr connects. Provide the user password (hr) when prompted.
CONNECT hr
-- create the external table
CREATE TABLE admin_ext_employees
                   (employee_id       NUMBER(4), 
                    first_name        VARCHAR2(20),
                    last_name         VARCHAR2(25), 
                    job_id            VARCHAR2(10),
                    manager_id        NUMBER(4),
                    hire_date         DATE,
                    salary            NUMBER(8,2),
                    commission_pct    NUMBER(2,2),
                    department_id     NUMBER(4),
                    email             VARCHAR2(25) 
                   ) 
     ORGANIZATION EXTERNAL 
     ( 
       TYPE ORACLE_LOADER 
       DEFAULT DIRECTORY admin_dat_dir 
       ACCESS PARAMETERS 
       ( 
         records delimited by newline 
         badfile admin_bad_dir:'empxt%a_%p.bad' 
         logfile admin_log_dir:'empxt%a_%p.log' 
         fields terminated by ',' 
         missing field values are null 
         ( employee_id, first_name, last_name, job_id, manager_id, 
           hire_date char date_format date mask "dd-mon-yyyy", 
           salary, commission_pct, department_id, email 
         ) 
       ) 
       LOCATION ('empxt1.dat', 'empxt2.dat') 
     ) 
     PARALLEL 
     REJECT LIMIT UNLIMITED; 
-- enable parallel for loading (good if lots of data to load)
ALTER SESSION ENABLE PARALLEL DML;
-- load the data in hr employees table
INSERT INTO employees (employee_id, first_name, last_name, job_id, manager_id,
                       hire_date, salary, commission_pct, department_id, email) 
            SELECT * FROM admin_ext_employees;

The following paragraphs contain descriptive information about this example.

The first few statements in this example create the directory objects for the operating system directories that contain the data sources, and for the bad record and log files specified in the access parameters. You must also grant READ or WRITE directory object privileges, as appropriate.

The TYPE specification indicates the access driver of the external table. The access driver is the API that interprets the external data for the database. If you omit the TYPE specification, ORACLE_LOADER is the default access driver. You must specify the ORACLE_DATAPUMP access driver if you specify the AS subquery clause to unload data from one Oracle Database and reload it into the same or a different Oracle Database.

The access parameters, specified in the ACCESS PARAMETERS clause, are opaque to the database. These access parameters are defined by the access driver, and are provided to the access driver by the database when the external table is accessed. See Oracle Database Utilities for a description of the ORACLE_LOADER access parameters.

The PARALLEL clause enables parallel query on the data sources. The granule of parallelism is by default a data source, but parallel access within a data source is implemented whenever possible. For example, if PARALLEL=3 were specified, then multiple parallel execution servers could be working on a data source. But, parallel access within a data source is provided by the access driver only if all of the following conditions are met:

• The media allows random positioning within a data source

• It is possible to find a record boundary from a random position

• The data files are large enough to make it worthwhile to break up into multiple chunks

  Note:

  Specifying a PARALLEL clause is of value only when dealing with large amounts of data. Otherwise, it is not advisable to specify a PARALLEL clause, and doing so can be detrimental.

The REJECT LIMIT clause specifies that there is no limit on the number of errors that can occur during a query of the external data. For parallel access, the REJECT LIMIT applies to each parallel execution server independently. For example, if a REJECT LIMIT of 10 is specified, then each parallel query process can allow up to 10 rejections. Therefore, with a parallel degree of two and a REJECT LIMIT of 10, the statement might fail with between 10 and 20 rejections. If one parallel server processes all 10 rejections, then the limit is reached, and the statement is terminated. However, one parallel execution server could process nine rejections and another parallel execution server could process nine rejections and the statement will succeed with 18 rejections. Hence, the only precisely enforced values for REJECT LIMIT on parallel query are 0 and UNLIMITED.

In this example, the INSERT INTO TABLE statement generates a dataflow from the external data source to the Oracle Database SQL engine where data is processed. As data is parsed by the access driver from the external table sources and provided to the external table interface, the external data is converted from its external representation to its Oracle Database internal data type.

Altering External Tables

You can use any of the ALTER TABLE clauses shown in Table 20-5 to change the characteristics of an external table. No other clauses are permitted.

ALTER TABLE admin_ext_employees
   REJECT LIMIT 100;

ALTER TABLE admin_ext_employees
   PROJECT COLUMN REFERENCED;

ALTER TABLE admin_ext_employees
   PROJECT COLUMN ALL;

ALTER TABLE admin_ext_employees 
    DEFAULT DIRECTORY admin_dat2_dir;

Preprocessing External Tables

External tables can be preprocessed by user-supplied preprocessor programs. By using a preprocessing program, users can use data from a file that is not in a format supported by the driver. For example, a user may want to access data stored in a compressed format. Specifying a decompression program for the ORACLE_LOADER access driver allows the data to be decompressed as the access driver processes the data.

To use the preprocessing feature, you must specify the PREPROCESSOR clause in the access parameters of the ORACLE_LOADER access driver. The preprocessor must be a directory object, and the user accessing the external table must have EXECUTE privileges for the directory object. The following example includes the PREPROCESSOR clause and specifies the directory and preprocessor program.

CREATE TABLE sales_transactions_ext
(PROD_ID NUMBER,
 CUST_ID NUMBER,
 TIME_ID DATE,
 CHANNEL_ID CHAR,
 PROMO_ID NUMBER,
 QUANTITY_SOLD NUMBER,
 AMOUNT_SOLD NUMBER(10,2),
 UNIT_COST NUMBER(10,2),
 UNIT_PRICE NUMBER(10,2))
ORGANIZATION external
(TYPE oracle_loader
 DEFAULT DIRECTORY data_file_dir
 ACCESS PARAMETERS
  (RECORDS DELIMITED BY NEWLINE
   CHARACTERSET AL32UTF8
   PREPROCESSOR exec_file_dir:'zcat'
   BADFILE log_file_dir:'sh_sales.bad_xt'
   LOGFILE log_file_dir:'sh_sales.log_xt'
   FIELDS TERMINATED BY "|" LDRTRIM
  ( PROD_ID,
    CUST_ID,
    TIME_ID,
    CHANNEL_ID,
    PROMO_ID,
    QUANTITY_SOLD,
    AMOUNT_SOLD,
    UNIT_COST,
    UNIT_PRICE))
 location ('sh_sales.dat.gz')
)REJECT LIMIT UNLIMITED;

The PREPROCESSOR clause is not available for databases that use Oracle Database Vault.

Dropping External Tables

For an external table, the DROP TABLE statement removes only the table metadata in the database. It has no affect on the actual data, which resides outside of the database.

System and Object Privileges for External Tables

System and object privileges for external tables are a subset of those for regular table. Only the following system privileges are applicable to external tables:

• ALTER ANY TABLE

• CREATE ANY TABLE

• DROP ANY TABLE

• READ ANY TABLE

• SELECT ANY TABLE

Only the following object privileges are applicable to external tables:

• ALTER

• READ

• SELECT

However, object privileges associated with a directory are:

• READ

• WRITE

For external tables, READ privileges are required on directory objects that contain data sources, while WRITE privileges are required for directory objects containing bad, log, or discard files.