15 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 datatype for each column.

• Consider invoking transparent data encryption to encrypt columns that will contain sensitive data.

• 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.

Consider Your Options for the Type of Table to Create

What types of tables can you create? Here are some choices:

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. 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 datafiles. 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 utilize 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 you are also parallelizing the table creation.

Consider Using Table Compression when Creating Tables

The Oracle Database table compression feature compresses data by eliminating duplicate values in a database block. Duplicate values in all the rows and columns in a block are stored once at the beginning of the block, in what is called a symbol table for that block. All occurrences of such values are replaced with a short reference to the symbol table.

Using table compression reduces disk use and memory use in the buffer cache, often resulting in better scale-up for read-only operations. Table compression can also speed up query execution. There is, however, a slight cost in CPU overhead.

Compression occurs when data is inserted with a bulk (direct-path) insert operation. A table can consist of compressed and uncompressed blocks transparently. Any DML operation can be applied to a table storing compressed blocks. However, conventional DML operations cause records to be stored uncompressed afterward the operations and the operations themselves are subject to a small performance overhead due to the nature of the table compression.

Consider using table compression when your data is mostly read only. Do not use table compression for tables that updated frequently.

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 datafiles 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 Application Developer's Guide - Object-Relational Features for more information about user-defined types

  
  

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 with an initial extent size of 50K:

CREATE TABLE        hr.admin_emp (
         empno      NUMBER(5) PRIMARY KEY,
         ename      VARCHAR2(15) NOT NULL,
         ssn        NUMBER(9) ENCRYPT,
         job        VARCHAR2(10),
         mgr        NUMBER(5),
         hiredate   DATE DEFAULT (sysdate),
         sal        NUMBER(7,2),
         comm       NUMBER(7,2),
         deptno     NUMBER(3) NOT NULL
                     CONSTRAINT admin_dept_fkey REFERENCES hr.departments
                     (department_id))
   TABLESPACE admin_tbs
   STORAGE ( INITIAL 50K);

In this CREATE TABLE statement, integrity constraints are defined on several columns of the table, and transparent data encryption is defined on one (ssn). Integrity constraints are discussed in "Managing Integrity Constraints", and transparent data encryption is discussed in Oracle Database Security Guide.

Creating a Temporary Table

It is also possible to create a temporary table. 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 indicate if the data in the table is transaction-specific (the default) or session-specific, the implications of which are as follows:

Temporary tables are useful in applications where a result set is to be buffered, 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.

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.

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. This means that if a SELECT, UPDATE, or DELETE is performed before the first INSERT, 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 utilize 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.

Inserting Data 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 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 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 Reference and Oracle Database Data Warehousing Guide.

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

Inserting Data Into Tables Using Direct-Path INSERT

Oracle Database inserts data into a table in one of two ways:

• 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.

• During direct-path INSERT operations, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored. These procedures combined can enhance performance.

Further, the data can be inserted either in serial mode, where one process executes the statement, or parallel mode, where multiple processes work together simultaneously to run a single SQL statement. The latter is referred to as parallel execution.

This section discusses one aspect of inserting data into tables. Specifically, using the direct-path form of the INSERT statement. It contains the following topics:

• Advantages of Using Direct-Path INSERT

• Enabling Direct-Path INSERT

• How Direct-Path INSERT Works

• Specifying the Logging Mode for Direct-Path INSERT

• Additional Considerations for Direct-Path INSERT

  
  

  Note: Only a few details and examples of inserting data into tables are included in this book. Oracle documentation specific to data warehousing and application development provide more extensive information about inserting and manipulating data in tables. For example: Oracle Database Data Warehousing Guide Oracle Database Application Developer's Guide - Fundamentals Oracle Database Application Developer's Guide - Large Objects

  
  

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 (datatype, length, default value, NOT NULL integrity constraint, and encryption.)

• 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

• 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

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. To better understand this transaction entry setting parameter, see "Specifying the INITRANS Parameter".

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. Storage parameters are discussed in "Managing Storage Parameters".

Moving a Table to a New Segment or Tablespace

The ALTER TABLE...MOVE statement enables you to relocate data of a non-partitioned 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 the COMPRESS keyword to store the new segment using table compression.

The following statement moves the hr.admin_emp table to a new segment, specifying new storage parameters:

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

Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.

If the table includes LOB column(s), this statement can be used to move the table along with LOB data and LOB index segments (associated with this table) which the user explicitly specifies. If not specified, the default is to not move the LOB data and LOB index segments.

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 Wasted Space".

Modifying an Existing Column Definition

Use the ALTER TABLE...MODIFY statement to modify an existing column definition. You can modify column datatype, default value, column constraint, and column encryption.

You can increase the length of an existing column, or decrease it, if all existing data satisfies the new length. 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 datatype CHAR, 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.

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, the column is initially NULL unless you specify the DEFAULT clause. When you specify a default value, the database updates each row in the new column with the values specified. Specifying a DEFAULT value is not supported for tables using table compression.

You can add a column with a NOT NULL constraint to a table 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 in conjunction 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;

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 in the same schema

  
  

  Note: If it is not important to keep a table available for DML when moving it to another tablespace, 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, including moving it to a different tablespace in the same schema

• Change physical properties of a materialized view log or an Oracle Streams Advanced Queueing 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 Wasted 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.

Performing Online Redefinition with DBMS_REDEFINITION

To perform online redefinition of a table with the DBMS_REDEFINITION package:

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. 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, 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, 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 6 when you copy dependent objects.

4. (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;
   
   

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

   • The schema and table name of the table to be redefined

   • The interim table name

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

     See "Constructing a Column Mapping String" for details.

   • The redefinition method

     If not specified, the default method of redefinition (using keys) is assumed.

   • Optionally, the columns to be used in ordering rows

   • If redefining only a single partition of a partitioned table, the partition name

   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: If START_REDEF_TABLE fails for any reason, you must call ABORT_REDEF_TABLE, otherwise subsequent attempts to redefine the table will fail.

   
   

6. Copy dependent objects (such as triggers, indexes, 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 Oracle Database Release 9i, 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.

     
     

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

8. If you used rowids for the redefinition and your COMPATIBLE initialization parameter is set to 10.1 or lower, set to UNUSED the hidden column M_ROW$$ that is now in the redefined table.

   ALTER TABLE table_name SET UNUSED (M_ROW$$);
   
   

   If COMPATIBLE is 10.2 or higher, this hidden column is automatically set to UNUSED for you when redefinition completes.

   You can also drop the column if you prefer.

[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.

• 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, grants and constraints defined on the original table (prior to 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.

• Any PL/SQL procedures and cursors defined on the original table (prior to redefinition) are invalidated. They are automatically revalidated whenever they are used next.

  
  

  Note: The revalidation can fail if the logical structure of the table was changed as a result of the redefinition process.

  
  

Performing Intermediate Synchronization

After the redefinition process has been started by calling START_REDEF_TABLE and before FINISH_REDEF_TABLE has been called, it is possible that a large number of DML statements have been executed on the original table. If you know that this is the case, 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, 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, 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.

• 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 with fine-grained access control (row-level security) cannot be redefined online.

• 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.

• Tables with materialized view logs defined on them cannot be redefined online.

• 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.

Online Rede