1.8 DDL Operations Related to Version-Enabled Tables

To perform DDL (data definition language) operations on a version-enabled table, you must use special Workspace Manager procedures before and after the DDL operations, and you must specify the name of a special table created by Workspace Manager.

You cannot perform DDL operations in the usual manner on the table or any index or trigger that refers to the table. For example, to add a column to a table named EMPLOYEES that has been version-enabled, you cannot simply enter a statement in the form ALTER TABLE EMPLOYEES ADD (column-name data-type).

The reason for these requirements is to ensure that Workspace Manager versioning metadata is updated to reflect the DDL changes. Therefore, DDL operations affecting a version-enabled table must be preceded by a call to the BeginDDL procedure, and must be concluded by a call to either the CommitDDL or RollbackDDL procedure. The BeginDDL procedure creates an empty temporary table with a name in the form <table-name>_LTS (the S standing for skeleton). The actual DDL statement must specify the name of the temporary <table-name>_LTS table, and must not specify the <table-name> or <table-name>_LT name. The CommitDDL and RollbackDDL procedures delete the temporary <table-name>_LTS table.

Note:

An exception to this procedure is adding valid time support to an existing version-enabled table. To add valid time support, use the AlterVersionedTable procedure, as explained in Adding Valid Time Support to an Existing Table.

The following DDL operations related to version-enabled tables are supported:

  • Table-related: Modifying the following table properties: logging, pctfree, pctused, initrans, next, minextents, maxextents, pctincrease, freelists, and buffer_pool; adding and removing supplemental logging on the table; modifying the compression options on the table

  • Column-related: ADD, DROP, MODIFY (but for MODIFY only the following operations: changing the default value of a column; changing the data type of a column that contains only null values or for which there are no existing data rows; changing the length of a column of type VARCHAR2, VARCHAR, CHAR, NCHAR, NVCHAR, or NVCHAR2; changing the precision of a column of type NUMBER); renaming a column.

    Note that any new length, scale, or precision for a column should be adequate for any existing data in the column.

  • Index-related: CREATE INDEX, DROP INDEX, ALTER INDEX (but for ALTER INDEX only the following options: logging, pctfree, initrans, initialextent, minextents, nextextent, maxextents, pctincrease, freelists, freelist groups, and buffer_pool)

    If the name of the index on a version-enabled table is longer than 26 characters, you must use the AlterVersionedTable procedure if you want to rename the index; you cannot use the ALTER INDEX statement with the RENAME clause. If the name of the index on a version-enabled table is 26 or fewer characters long, you can do either of the following to rename the index: use the AlterVersionedTable procedure, or use the ALTER INDEX statement with the RENAME clause between calls to the BeginDDL and CommitDDL procedures. See the Usage Notes for AlterVersionedTable for more information.

  • Trigger-related: CREATE TRIGGER, DROP TRIGGER, ALTER TRIGGER ENABLE/DISABLE

  • Referential integrity constraint-related: add, drop, enable, or disable a referential integrity constraint. For information about Workspace Manager referential integrity support, see Referential Integrity Support.

  • Unique constraint-related: add, drop, enable, or disable a unique constraint. For information about Workspace Manager unique constraint support, see Unique Constraints.

  • Privilege-related: grant table-level privileges to users and revoke these privileges from users.

You can create the following types of indexes on version-enabled tables: normal, bitmap, function-based normal, function-based bitmap, invisible, reverse, and domain. You cannot create or drop a partitioned or join index on a version-enabled table. (You can, however, version-enable a table that has a partitioned or join index.) You can use the compress and prefix_length parameters in index DDL operations.

Invisible columns are not supported in any DDL operations.

If an identity column is added to the table during a DDL session, there is no support for the LIMIT VALUE keyword. If that keyword is specified, the start value is reset based on the start value of the skeleton _LTS table.

If you try to perform an unsupported DDL operation, the change will not be made, and an exception might be raised by the CommitDDL procedure.

If the DDL operation involving a version-enabled table is on a domain index (for example, creating an R-tree index on the table), you must have the CREATE TABLE privilege.

If you need to perform DDL operations on a version-enabled table in an Oracle Label Security (OLS) environment, you can use the apply_table_policy, remove_table_policy, enable_table_policy, and disable_table_policy procedures of the SA_POLICY_ADMIN package on the skeleton (_LTS) table, and the changes will be transferred to the version-enabled table.

The following example shows the statements needed to add a column named COMMENTS to the COLA_MARKETING_BUDGET table by using the special table named COLA_MARKETING_BUDGET_LTS. It also includes a DESCRIBE statement to show the addition of the column.

Example 1-2 DDL Operation on a Version-Enabled Table

EXECUTE DBMS_WM.BeginDDL('COLA_MARKETING_BUDGET');
ALTER TABLE cola_marketing_budget_lts ADD (comments VARCHAR2(100));
DESCRIBE cola_marketing_budget_lts;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRODUCT_ID                                NOT NULL NUMBER
 PRODUCT_NAME                                       VARCHAR2(32)
 MANAGER                                            VARCHAR2(32)
 BUDGET                                             NUMBER
 COMMENTS                                           VARCHAR2(100)

EXECUTE DBMS_WM.CommitDDL('COLA_MARKETING_BUDGET');

In the preceding example, the ALTER TABLE statement specifies the COLA_MARKETING_BUDGET_LTS table, which is created by the BeginDDL procedure. The CommitDDL procedure applies the change to the COLA_MARKETING_BUDGET table and deletes the COLA_MARKETING_BUDGET_LTS table.

Parent topic: Introduction to Workspace Manager