OMBALTER TABLE

Purpose

To alter properties and definition of a table.

Prerequisites

In the context of an Oracle Module.

Syntax

alterTableCommand =  OMBALTER ( TABLE "QUOTED_STRING" ( "renameClause" [ 
          "alterPropertiesOrIconSetClause" ] [ "alterTableSCOClauses" ] | 
          "alterPropertiesOrIconSetClause" [ "alterTableSCOClauses" ] | 
          "alterTableSCOClauses" ) )
     renameClause =  RENAME TO "QUOTED_STRING"
     alterPropertiesOrIconSetClause =  SET ( "setPropertiesClause" [ SET ( REF |
           REFERENCE ) "setReferenceIconSetClause" | UNSET ( REF | REFERENCE ) 
          "unsetReferenceIconSetClause" ] | ( REF | REFERENCE ) 
          "setReferenceIconSetClause" ) | UNSET ( REF | REFERENCE ) 
          "unsetReferenceIconSetClause"
     alterTableSCOClauses =  ADD ( "addColumnClauseForAlter" | 
          "addConstraintClause" | "addSCOClause" | "addDataRuleUsageClause" ) [ 
          "alterTableSCOClauses" ] | MODIFY ( "modifyColumnClause" | 
          "modifyConstraintClause" | "modifySCOClause" | 
          "modifyDataRuleUsageClause" ) [ "alterTableSCOClauses" ] | DELETE ( 
          "deleteColumnClause" | "deleteConstraintClause" | "deleteSCOClause" | 
          "deleteDataRuleUsageClause" ) [ "alterTableSCOClauses" ]
     setPropertiesClause =  PROPERTIES "(" "propertyNameList" ")" VALUES "(" 
          "propertyValueList" ")"
     setReferenceIconSetClause =  ICONSET "QUOTED_STRING"
     unsetReferenceIconSetClause =  ICONSET
     addColumnClauseForAlter =  COLUMN "QUOTED_STRING" [ AT POSITION 
          "INTEGER_LITERAL" ] [ SET "setPropertiesClause" ]
     addConstraintClause =  "addUkPkClause" | "addFkClause" | 
          "addCheckConstraintClause"
     addSCOClause =  "addIndexClause" | "addIndexPartitionClause" | 
          "addIndexPartitionKeyClause" | "addPartitionClause" | 
          "addPartitionKeyClause" | "addSubpartitionClause" | 
          "addaddMaterializedViewSCOandDependentClauseClause" | 
          "addSubPartitionKeyClause" | "addIndexColumnClause" | 
          "addAttributeSetClause"
     addDataRuleUsageClause =  DATA_RULE_USAGE "QUOTED_STRING" SET REF DATA_RULE
           "QUOTED_STRING" ( GROUP "QUOTED_STRING" SET REF ( TABLE | VIEW | 
          MATERIALIZED_VIEW | EXTERNAL_TABLE ) "QUOTED_STRING" ( ATTRIBUTE 
          "QUOTED_STRING" SET REF COLUMN "QUOTED_STRING" )+ )+ [ SET 
          "setPropertiesClause" ]
     modifyColumnClause =  COLUMN "QUOTED_STRING" ( "renameClause" [ 
          "moveToClause" ] [ SET "setPropertiesClause" ] | "moveToClause" [ SET 
          "setPropertiesClause" ] | SET "setPropertiesClause" )
     modifyConstraintClause =  "modifyUkPkClause" | "modifyFkClause" | 
          "modifyCheckConstraintClause"
     modifySCOClause =  "modifyIndexClause" | "modifyIndexPartitionClause" | 
          "modifyIndexPartitionKeyClause" | "modifyPartitionClause" | 
          "modifyPartitionKeyClause" | 
          "modifyaddMaterializedViewSCOandDependentClauseClause" | 
          "modifySubPartitionClause" | "modifySubPartitionKeyClause" | 
          "modifyIndexColumnClause" | "modifyAttributeSetClause"
     modifyDataRuleUsageClause =  DATA_RULE_USAGE "QUOTED_STRING" ( 
          "renameClause" [ SET "setPropertiesClause" ] | SET 
          "setPropertiesClause" )
     deleteColumnClause =  COLUMN "QUOTED_STRING"
     deleteConstraintClause =  UNIQUE_KEY "QUOTED_STRING" | PRIMARY_KEY 
          "QUOTED_STRING" | FOREIGN_KEY "QUOTED_STRING" | CHECK_CONSTRAINT 
          "QUOTED_STRING"
     deleteSCOClause =  INDEX "QUOTED_STRING" | PARTITION "QUOTED_STRING" | 
          PARTITION_KEY "QUOTED_STRING" | TEMPLATE_SUBPARTITION "QUOTED_STRING" 
          | SUBPARTITION_KEY "QUOTED_STRING" | INDEX_COLUMN "QUOTED_STRING" OF 
          INDEX "QUOTED_STRING" | INDEX_PARTITION "QUOTED_STRING" OF INDEX 
          "QUOTED_STRING" | INDEX_PARTITION_KEY "QUOTED_STRING" OF INDEX 
          "QUOTED_STRING" | SUBPARTITION "QUOTED_STRING" OF PARTITION 
          "QUOTED_STRING" | ATTRIBUTE_SET "QUOTED_STRING"
     deleteDataRuleUsageClause =  DATA_RULE_USAGE "QUOTED_STRING"
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }
     propertyValueList =  "propertyValue" { "," "propertyValue" }
     addUkPkClause =  ( UNIQUE_KEY | PRIMARY_KEY ) "QUOTED_STRING" [ SET 
          "setUkPkPropertiesAndReferencesColumnsClauses" ]
     addFkClause =  FOREIGN_KEY "QUOTED_STRING" [ SET "setFkSubClauses" ]
     addCheckConstraintClause =  CHECK_CONSTRAINT "QUOTED_STRING" [ SET 
          "setPropertiesClause" ]
     addIndexClause =  INDEX "QUOTED_STRING" [ SET 
          "setSCOConfigurationPropertiesClauses" ]
     addIndexPartitionClause =  INDEX_PARTITION "QUOTED_STRING" OF INDEX 
          "QUOTED_STRING" [ AT POSITION "INTEGER_LITERAL" ] ( 
          "renameSCOConfigurationClause" [ SET 
          "setSCOConfigurationPropertiesClauses" ] | [ SET 
          "setSCOConfigurationPropertiesClauses" ] )
     addIndexPartitionKeyClause =  INDEX_PARTITION_KEY "QUOTED_STRING" OF INDEX 
          "QUOTED_STRING" [ SET "setSCOConfigurationPropertiesClauses" ]
     addPartitionClause =  PARTITION "QUOTED_STRING" [ AT POSITION 
          "INTEGER_LITERAL" ] [ SET "setSCOConfigurationPropertiesClauses" ]
     addPartitionKeyClause =  PARTITION_KEY "QUOTED_STRING" [ SET 
          "setSCOConfigurationPropertiesClauses" ]
     addSubpartitionClause =  SUBPARTITION "QUOTED_STRING" OF PARTITION 
          "QUOTED_STRING" [ AT POSITION "INTEGER_LITERAL" ] [ SET 
          "setSCOConfigurationPropertiesClauses" ]
     addaddMaterializedViewSCOandDependentClauseClause =  TEMPLATE_SUBPARTITION 
          "QUOTED_STRING" [ AT POSITION "INTEGER_LITERAL" ] [ SET 
          "setSCOConfigurationPropertiesClauses" ]
     addSubPartitionKeyClause =  SUBPARTITION_KEY "QUOTED_STRING" [ SET 
          "setSCOConfigurationPropertiesClauses" ]
     addIndexColumnClause =  INDEX_COLUMN "QUOTED_STRING" OF INDEX 
          "QUOTED_STRING" [ SET "setSCOConfigurationPropertiesClauses" ]
     addAttributeSetClause =  ATTRIBUTE_SET "QUOTED_STRING"
     moveToClause =  MOVE TO POSITION "INTEGER_LITERAL"
     modifyUkPkClause =  ( UNIQUE_KEY | PRIMARY_KEY ) "QUOTED_STRING" ( 
          "renameClause" [ SET "setUkPkPropertiesAndReferencesColumnsClauses" ] 
          | SET "setUkPkPropertiesAndReferencesColumnsClauses" )
     modifyFkClause =  FOREIGN_KEY "QUOTED_STRING" ( "renameClause" [ SET 
          "setFkSubClauses" ] | SET "setFkSubClauses" )
     modifyCheckConstraintClause =  CHECK_CONSTRAINT "QUOTED_STRING" ( 
          "renameClause" [ SET "setPropertiesClause" ] | SET 
          "setPropertiesClause" )
     modifyIndexClause =  INDEX "QUOTED_STRING" ( "renameSCOConfigurationClause"
           [ SET "setSCOConfigurationPropertiesClauses" ] | SET 
          "setSCOConfigurationPropertiesClauses" )
     modifyIndexPartitionClause =  INDEX_PARTITION "QUOTED_STRING" OF INDEX 
          "QUOTED_STRING" ( "renameSCOConfigurationClause" [ 
          "moveToClauseIndexPartition" ] [ SET 
          "setSCOConfigurationPropertiesClauses" ] | 
          "moveToClauseIndexPartition" [ SET 
          "setSCOConfigurationPropertiesClauses" ] | SET 
          "setSCOConfigurationPropertiesClauses" )
     modifyIndexPartitionKeyClause =  INDEX_PARTITION_KEY "QUOTED_STRING" OF 
          INDEX "QUOTED_STRING" ( SET "setSCOConfigurationPropertiesClauses" )
     modifyPartitionClause =  PARTITION "QUOTED_STRING" ( 
          "renameSCOConfigurationClause" [ "moveToClausePartition" ] [ SET 
          "setSCOConfigurationPropertiesClauses" ] | "moveToClausePartition" [ 
          SET "setSCOConfigurationPropertiesClauses" ] | SET 
          "setSCOConfigurationPropertiesClauses" )
     modifyPartitionKeyClause =  PARTITION_KEY "QUOTED_STRING" ( 
          "renameSCOConfigurationClause" [ SET 
          "setSCOConfigurationPropertiesClauses" ] | SET 
          "setSCOConfigurationPropertiesClauses" )
     modifyaddMaterializedViewSCOandDependentClauseClause =  
          TEMPLATE_SUBPARTITION "QUOTED_STRING" ( "renameSCOConfigurationClause"
           [ "moveToClauseTemplateSubPartition" ] [ SET 
          "setSCOConfigurationPropertiesClauses" ] | 
          "moveToClauseTemplateSubPartition" [ SET 
          "setSCOConfigurationPropertiesClauses" ] | SET 
          "setSCOConfigurationPropertiesClauses" )
     modifySubPartitionClause =  SUBPARTITION "QUOTED_STRING" OF PARTITION 
          "QUOTED_STRING" ( "renameSCOConfigurationClause" [ 
          "moveToClauseSubPartition" ] [ SET 
          "setSCOConfigurationPropertiesClauses" ] | "moveToClauseSubPartition" 
          [ SET "setSCOConfigurationPropertiesClauses" ] | SET 
          "setSCOConfigurationPropertiesClauses" )
     modifySubPartitionKeyClause =  SUBPARTITION_KEY "QUOTED_STRING" ( 
          "renameSCOConfigurationClause" [ SET 
          "setSCOConfigurationPropertiesClauses" ] | SET 
          "setSCOConfigurationPropertiesClauses" )
     modifyIndexColumnClause =  INDEX_COLUMN "QUOTED_STRING" OF INDEX 
          "QUOTED_STRING" ( "renameSCOConfigurationClause" [ 
          "moveToClauseForIndexColumn" ] [ SET 
          "setSCOConfigurationPropertiesClauses" ] | 
          "moveToClauseForIndexColumn" [ SET 
          "setSCOConfigurationPropertiesClauses" ] | SET 
          "setSCOConfigurationPropertiesClauses" )
     modifyAttributeSetClause =  ATTRIBUTE_SET "QUOTED_STRING" ( "renameClause" 
          [ SET "setAttributeSetSubClauses" ] | SET "setAttributeSetSubClauses" 
          )
     propertyValue =  ( "QUOTED_STRING" | "INTEGER_LITERAL" | 
          "FLOATING_POINT_LITERAL" )
     setUkPkPropertiesAndReferencesColumnsClauses =  "setPropertiesClause" [ SET
           ( REF | REFERENCE ) "constraintColumnReferencesClause" ] | ( REF | 
          REFERENCE ) "constraintColumnReferencesClause"
     setFkSubClauses =  "setPropertiesClause" [ SET ( REF | REFERENCE ) 
          "setFkReferencesClauses" ] | ( REF | REFERENCE ) 
          "setFkReferencesClauses"
     setSCOConfigurationPropertiesClauses =  PROPERTIES "(" "propertyNameList" 
          ")" VALUES "(" "propertyValueList" ")"
     renameSCOConfigurationClause =  RENAME TO "QUOTED_STRING"
     moveToClauseIndexPartition =  MOVE TO POSITION "INTEGER_LITERAL"
     moveToClausePartition =  MOVE TO POSITION "INTEGER_LITERAL"
     moveToClauseTemplateSubPartition =  MOVE TO POSITION "INTEGER_LITERAL"
     moveToClauseSubPartition =  MOVE TO POSITION "INTEGER_LITERAL"
     moveToClauseForIndexColumn =  MOVE TO POSITION "INTEGER_LITERAL"
     setAttributeSetSubClauses =  "setPropertiesClause" [ SET 
          "attributeSetAttributesClause" ] | "attributeSetAttributesClause"
     constraintColumnReferencesClause =  COLUMNS "(" "quotedNameList" ")"
     setFkReferencesClauses =  "constraintColumnReferencesClause" [ SET ( REF | 
          REFERENCE ) "constraintUkReferencesClause" ] | 
          "constraintUkReferencesClause" [ SET ( REF | REFERENCE ) 
          "constraintColumnReferencesClause" ]
     attributeSetAttributesClause =  ATTRIBUTES "(" "quotedNameList" ")"
     quotedNameList =  "QUOTED_STRING" { "," "QUOTED_STRING" }
     constraintUkReferencesClause =  ( UNIQUE_KEY | PRIMARY_KEY ) 
          "QUOTED_STRING" [ OF ( TABLE | VIEW ) "QUOTED_STRING" ]

Parameters

alterTableCommand

This clause alters a table.

QUOTED_STRING

name of the table.

renameClause

renames a table with a different name.

alterTableSCOClauses

This clause will add, modify, delete, columns, configuration, and keys.

setPropertiesClause

Used to set properties (core, logical, physical, user-defined) for tables (including partitions and subpartitions) and their columns, indexes (including index partitions), check constraints, unique keys, foreign keys, and primary keys.

getPropertiesClause

Basic properties for TABLE: 

Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Business name of the table

Name: DESCRIPTION
Type: STRING(4000)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Description of the table

getPropertiesClause

Basic properties for COLUMN: 

Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Business name of the column

Name: DESCRIPTION
Type: STRING(4000)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Description of the column 

Name: DATATYPE
Type: STRING
Valid Values: 
(Oracle) BINARY_DOUBLE, BINARY_FLOAT, BLOB, CHAR, CLOB, DATE, FLOAT, INTEGER, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, NCHAR, NCLOB, NUMBER, NVARCHAR2, RAW, LONG RAW, SYS.ANYDATA, SYS.LCR$_ROW_RECORD, XMLFORMAT, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE, TIMESTAMP WITH TIME ZONE, VARHCAR, VARCHAR2, XMLTYPE
(DB2 UDB) BIGINT, BLOB, CHAR, CLOB, DATE, DBCLOB, DECIMAL, DOUBLE, FLOAT, GRAPHIC, INT, LONG VARCHAR, LONG VARGRAPHIC, NUMERIC, REAL, SMALLINT, TIME, TIMESTAMP, VARCHAR, VARGRAPHIC, XML
(SQL Server) BIGINT, BINARY, BIT, CHAR, DATETIME, DECIMAL, FLOAT, IMAGE, INT, MONEY, NCHAR, NTEXT, NUMERIC, NVARCHAR, NVARCHAR(MAX), REAL, SMALLDATETIME, SMALLINT, SMALLMONEY, SQL_VARIANT, TEXT, TIMESTAMP, TINYINT, UNIQUEIDENTIFIER, VARBINARY, VARBINARY(MAX), VARCHAR, VARCHAR(MAX), XML

Default: NUMBER
The datatype of a column

Name: LENGTH
Type: NUMBER
Valid Values: 
Default: 1
The length of a number 

Name: PRECISION
Type: NUMBER
Valid Values: 0 - 38
Default: 1
The precision of a number. Use 0 to specify floating-point numbers.
Also use this to specify Year or Day precision for INTERVAL data types.

Name: SCALE
Type: NUMBER
Valid Values: -84 - 127
Default: 1
The scale of a number.

Name: FRACTIONAL_SECONDS_PRECISION
Type: NUMBER
Valid Values: 0 - 9
Default: 0
The precision of a timestamp or interval.

Name: DEFAULT_VALUE
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: Empty string
Default value of the column 

Name: NOT_NULL
Type: BOOLEAN
Valid Values: true, false
Default: false
Specify "true" to enforce Not Null restriction on a column. 

Name: VIRTUAL
Type: BOOLEAN
Valid Values: true, false
Default: false
Specify "true" to indicate a virtual Column. 

Name: EXPRESSION
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: Empty string
Column expression for a virtual column

getPropertiesClause

Basic properties for PARTITION_KEY:

Name: TYPE
Type: STRING
Valid Values: RANGE, LIST, HASH, HASH BY QUANTITY
Default: (No default, must be one of the choices above)
Ask Oracle to partition the table rows according to a Hash Algorithm, lists of values, or specified ranges.

Name: HASH_QUANTITY
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: '0'
Specify how many HASH partitions the database should create on HASH BY QUANTITY partitioning. For optimal load balancing you should specify a number of partitions that is a power of 2. If you have multiple Partition Keys, you only have to specify once.

Name: INTERVAL
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: Empty string
Specify valid interval value or expression (NUMBER or DATE) for Interval (Range) Partitioning.

Basic properties for PARTITION:

Name: VALUES_LESS_THAN
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: Empty string
Specify the noninclusive upper bound for the current RANGE partition. The value list is a comma-delimited, ordered list of literal values corresponding to the index partitioning column list. Always specify MAXVALUE(s) as the value(s) of the last partition, and make sure you have specified PARTITION_KEY(s) before you specify any PARTITION.

Name: VALUES_EQUAL_TO
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: Empty string
Specify a list of literal values for the current LIST partition. The value list is a comma-delimited, ordered list of literal values corresponding to the index partitioning column. Commas can be escaped using "" (e.g. '1,2,3'). Always specify DEFAULT as the value of the last partition, and make sure you have specified PARTITION_KEY(s) before you specify any PARTITION.. Each LIST partition must have at least one value. No value, including NULL, can appear in more than one partition.

Name: HASH_QUANTITY
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: '0'
Specify how many HASH customized subpartitions the database should create for a particular main RANGE partition (RANGE-HASH BY QUANTITY partitioning). For optimal load balancing you should specify a number of subpartitions that is a power of 2. If you have multiple Subpartition Keys, you only have to specify once. Set it to 0 to reverse to the use of generic template HASH_QUANTITY specified in SUBPARTITION_KEY.

getPropertiesClause

Basic properties for SUBPARTITION_KEY:

Name: TYPE
Type: STRING
Valid Values: LIST, HASH, HASH BY QUANTITY
Default: (No default, must be one of the choices above)
For partition-level partitioning according to a Hash Algorithm or lists of values. Each partition is further sorted into subpartitions.

Name: HASH_QUANTITY
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: '0'
Specify how many HASH subpartitions the database should create on HASH BY QUANTITY partitioning. For optimal load balancing you should specify a number of subpartitions that is a power of 2. If you have multiple Subpartition Keys, you only have to specify once.

Basic properties for SUBPARTITION:

Name: VALUES_EQUAL_TO
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: Empty string
Specify a list of literal values for the current LIST subpartition. The value list is a comma-delimited, ordered list of literal values corresponding to the index partitioning column. Always specify DEFAULT as the value of the last subpartition, and make sure you have specified SUBPARTITION_KEY(s) before you specify any SUBPARTITION. Each LIST subpartition must have at least one value. No value, including NULL, can appear in more than one subpartition.

Basic properties for TEMPLATE_SUBPARTITION:

Name: VALUES_EQUAL_TO
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: Empty string
In composite partitioning, template subpartitions are automatically applied to those partitions without their subpartitions specified. Here for LIST subpartitions only, specify a comma-delimited, ordered list of literal values corresponding to the LIST subpartitioning column. Always specify DEFAULT as the value of the last template LIST subpartition, and and make sure you have specified SUBPARTITION_KEY(s) before you specify any TEMPLATE_SUBPARTITION. Each LIST template subpartition must have at least one value. No value, including NULL, can appear in more than one template subpartition.

getPropertiesClause

Basic properties for INDEX:

Name: INDEX_TYPE
Type: STRING
Valid Values: UNIQUE, NON-UNIQUE, BITMAP, FUNCTION-BASED
Default: (No default, must be one of the choices above)
Specify the type of an index. NORMAL can be used in place of NON-UNIQUE.

Name: LOCAL_INDEX
Type: BOOLEAN
Valid Values: true, false
Default: false
Specify if an index is Global or Local. The default is Global.
Specify Local so that the index is partitioned on the same columns, with the same number of partitions and the same partition bounds as table. Oracle Database automatically maintains local index partitioning as the underlying table is repartitioned.

Name: COLUMN_EXPRESSION
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: Empty string
Specify an expression built from columns of table, constants, SQL functions, and user-defined functions to create a FUNCTION-BASED index.

Basic properties for INDEX_PARTITION_KEY:

Name: TYPE
Type: STRING
Valid Values: RANGE, HASH, HASH BY QUANTITY
Default: (No default, must be one of the choices above)
Ask Oracle to partition the index rows according to a Hash Algorithm, lists of values, or specified ranges. Hash index partitioning is supported starting with Oracle 10g version.

Name: HASH_QUANTITY
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: '0'
Specify how many HASH index partitions the database should create based on HASH BY QUANTITY partitioning. For optimal load balancing you should specify a number of index partitions that is a power of 2. If you have multiple index Partition Keys, you only have to specify once.

Basic properties for INDEX_PARTITION:

Name: VALUES_LESS_THAN
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: Empty string
Specify the noninclusive upper bound for the current RANGE partition in a global index. The value list is a comma-delimited, ordered list of literal values corresponding to the index partitioning column list. Always specify MAXVALUE(s) as the value(s) of the last partition. No need to specify VALUES_LESS_THAN for Local index.

Name: VALUES_EQUAL_TO
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: Empty string
Specify a list of literal values for the current LIST partition in a global index. The value list is a comma-delimited, ordered list of literal values corresponding to the index partitioning column. Always specify DEFAULT as the value of the last partition. Each LIST partition must have at least one value. No value, including NULL, can appear in more than one partition. No need to specify VALUES_EQUAL_TO for Local index.

getPropertiesClause

Basic properties for CHECK_CONSTRAINTS: 

Name: CHECK_CONDITION
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: Empty string
Specify a condition that each row in the table must satisfy.

addColumnClauseForAlter

This clause adds a column at a particular position.

When you alter a table and add columns to it, the position you specify for a new column must be less than or equal to the number of columns added up to that point in the OMBALTER command.

For example, a table TEMP_TAB contains three columns. You use the following

OMBALTER TABLE command to add three more columns:

OMBALTER TABLE 'TEMP_TAB' \

ADD COLUMN 'C4' AT POSITION 4 \

SET PROPERTIES(DATATYPE,PRECISION) VALUES('NUMBER',7) \

ADD COLUMN 'C5' AT POSITION 6 \

SET PROPERTIES(DATATYPE) VALUES('VARCHAR2') \

ADD COLUMN 'C6' AT POSITION 5 \

SET PROPERTIES(DATATYPE,PRECISION) VALUES('NUMBER',10);

This command does not execute successfully because at the point when you specify the position of the column C5 as 6, the table is contains only 5 columns.

QUOTED_STRING

The column name.

addConstraintClause

Adds primary and unique key, and add check constraints.

addSCOClause

This clause will add SCOs.

addDataRuleUsageClause

Add a data rule usage to the relation.

modifyColumnClause

This clause renames, set properties, and move columns.

modifyConstraintClause

This clause modifies keys and check constraints

modifySCOClause

This clause will modify SCOs.

modifyDataRuleUsageClause

Rename or modify the properties of a data rule usage.

deleteColumnClause

This clause deletes a column.

deleteConstraintClause

This clause deletes a key or check constraint.

deleteSCOClause

This clause deletes a SCO(Second Class Object).

QUOTED_STRING

Either index, partition, partition_key, or index column name.

deleteDataRuleUsageClause

Delete a data rule usage.

propertyNameList

The list of properties.

propertyValueList

The list of property values.

addUkPkClause

This clause adds a unique key and primary key.

QUOTED_STRING

name of the unique key or primary key.

addFkClause

This clause adds foreign key.

QUOTED_STRING

Name of the foreign key.

addCheckConstraintClause

add a check constraint.

QUOTED_STRING

Name of the CheckConstraint.

addIndexClause

This clause adds an index.

QUOTED_STRING

Name of the index.

addPartitionClause

This clause adds a partition.

QUOTED_STRING

Name of the partition.

addPartitionKeyClause

This clause adds a partition key.

QUOTED_STRING

Name of the partition key. This should be a column identifier.

addIndexColumnClause

This clause will add index column to a specified index.

QUOTED_STRING

This should be a column identifier of owning object (such as a table) of the index.

addAttributeSetClause

This clause adds an attribute set.

QUOTED_STRING

name of the attribute set.

moveToClause

This clause will move the column to given position.

modifyUkPkClause

This clause modifies a unique or primary key.

modifyFkClause

This clause modifies the foreign key.

modifyCheckConstraintClause

This clause modifies the check constraint.

modifyIndexClause

This clause modifies the Index.

QUOTED_STRING

Name of the index.

modifyPartitionClause

This clause modifies a partition.

QUOTED_STRING

Name of the partition.

modifyPartitionKeyClause

This clause modifies a partition key.

QUOTED_STRING

Name of the partition key.

modifyIndexColumnClause

Modifies the Index Column. The first quoted_string in this clause denotes index column name, and the latter denotes index.

modifyAttributeSetClause

This clause modifies an attribute set.

propertyValue

This clause adds the property values.

setUkPkPropertiesAndReferencesColumnsClauses

This clause adds properties and references to columns.

setFkSubClauses

This clause sets references to a foreign key.

setSCOConfigurationPropertiesClauses

Set the configuration properties for the following:

- Partition, Subpartition, and Template Subpartition: All refer to configuration properties of Partition.

- Index, and Index Partition: For Index Partition, refer to configuration properties of Partition.

renameSCOConfigurationClause

This clause renames configuration objects.

setAttributeSetSubClauses

This clause is used to set up an attribute set.

constraintColumnReferencesClause

This clause provides names of all columns.

setFkReferencesClauses

This clause sets foreign key references.

attributeSetAttributesClause

This clause is used to specify attributes belonging to an attribute set.

quotedNameList

This clause gives column names.

constraintUkReferencesClause

The first QUOTED_STRING denotes the UniqueKey or Primay key name, and the latter denotes the table's or view's name.

TABLE Object

Table 4-54 TABLE Object

Property	Type	Choices	Min	Max	Default	Description
ANALYZE_TABLE_ESTIMATE_PERCENT	STRING	none	none	none	empty string	Value represents the sample size as a percentage (0-100) of total rows. When set to a nonzero value, Builder generates a DDL script to analyze the table. The default is 99.
BUFFER_POOL	STRING	, DEFAULT, KEEP, RECYCLE	none	none	empty string	Specify a default buffer pool (cache) for table or partition object. The default is DEFAULT.
CACHE_MODE	STRING	, CACHE, NOCACHE	none	none	empty string	Indicate how Oracle should store blocks in the buffer cache.
CDC_POSITION	NUMBER	none	none	none	0	Table position in change data capture.
DATA_SEGMENT_COMPRESSION	STRING	, COMPRESS, COMPRESS ALL, NOCOMPRESS	none	none	empty string	Use this clause to instruct Oracle whether to compress data segments to reduce disk use. The default is NOCOMPRESS.
DEPLOYABLE	BOOLEAN	true, false	none	none	false	Warehouse Builder generates a set of scripts to create an object only for those object marked as Deployable = true
ERROR_TABLESPACE	STRING(30)	none	none	none	empty string	Use the Tablespace parameter to specify the name of tablespace.
ERROR_TABLE_NAME	STRING(30)	none	none	none	empty string	Use the error table name to specify the name of Error Table.
FREELISTGROUPS	STRING	none	none	none	empty string	Specify the number of groups of free lists for the database object you are creating. The default is 1.
FREELISTS	STRING	none	none	none	empty string	Specify the number of free lists for each of the free list groups for the table, partition, cluster, or index. The default is 1.
GENERATE_ERROR_TABLE_ONLY	BOOLEAN	true, false	none	none	false	Create, drop, replace or upgrade only the error table.
GENERATION_COMMENTS	STRING	none	none	none	empty string	Enter additional comments for the generated code.
INITIAL	STRING	none	none	none	empty string	Specify in bytes the size of the first extent. Use K or M to specify size in kilobytes or megabytes.
INITRANS	STRING	none	none	none	empty string	Specify the initial number (1-255) of concurrent transaction entries allocated within each data block allocated to the database object. The default is 1.
IS_CDC	BOOLEAN	true, false	none	none	false	Enable table for change data capture
LOGGING_MODE	STRING	, LOGGING, NOLOGGING	none	none	empty string	Specify whether the creation of the table and of any indexes required because of constraints, partition, or LOB storage characteristics will be logged in the redo log file (LOGGING) or not (NOLOGGING). The default is LOGGING.
MAXEXTENTS	STRING	none	none	none	empty string	Specify the total number of extents, including the first, that Oracle can allocate for the object.
MAXTRANS	STRING	none	none	none	empty string	Specify the maximum number (1-255) of concurrent transactions that can update a data block allocated to the database object.
MINEXTENTS	STRING	none	none	none	empty string	Specify the total number of extents to allocate when the object is created.
MONITORING_MODE	STRING	, MONITORING, NOMONITORING	none	none	empty string	Specify MONITORING if you want modification statistics to be collected on this table.
NEXT	STRING	none	none	none	empty string	Specify in bytes the size of the next extent to be allocated. Use K or M to specify size in kilobytes or megabytes.
OVERFLOW	STRING	none	none	none	empty string	Enter a comma separated list of tablespaces for overflow data. For simple-partitoned object, it is used for HASH BY QUANTITY partition overflow tablespaces. The number of tablespaces does not have to equal the number of partitions. If the number of partitions is greater than the number of tablespaces, then Oracle cycles through the names of the tablespaces.
PARALLEL_ACCESS_MODE	STRING	, NOPARALLEL, PARALLEL	none	none	empty string	Enables or disables parallel processing when the table is created. Also enables or disables parallel processing or access. The default is PARALLEL.
PARALLEL_DEGREE	STRING	none	none	none	empty string	Enter degree of parallelism, which is the number of parallel threads used in the parallel operation.
PARTITION_TABLESPACE_LIST	STRING	none	none	none	empty string	Enter a comma separated list of tablespaces. For simple-partitoned object, it is used for HASH BY QUANTITY partition tablespaces. For composite-partitioned tables, it is used for subpartition template to store a list of tablespaces.
PCTFREE	STRING	none	none	none	empty string	Specify a whole number representing the percentage (0-99) of space in each data block of the database object reserved for future updates to the rows of the object. The default is 10.
PCTINCREASE	STRING	none	none	none	empty string	Specify the percent by which the third and subsequent extents grow over the preceding extent. The default is 50.
PCTUSED	STRING	none	none	none	empty string	Specify a whole number representing the minimum percentage (0-99) of used space that Oracle maintains for each data block of the database object. The default is 40.
ROWDEPENDENCIES_MODE	STRING	, NOROWDEPENDENCIES, ROWDEPENDENCIES	none	none	empty string	Specify ROWDEPENDENCIES to use row-level dependency tracking.
ROW_MOVEMENT	STRING	, DISABLE, ENABLE	none	none	empty string	Specify whether Oracle can move a table row.
TABLESPACE	STRING(30)	none	none	none	empty string

COLUMN Object

Table 4-55 COLUMN Object

Property	Type	Choices	Min	Max	Default
META_IMPORT_BYTES_PER_CHAR	NUMBER	none	none	none	1
META_IMPORT_CHARSET	STRING(45)	none	none	none	empty string
META_IMPORT_USE_CHAR_SEMANTICS	BOOLEAN	true, false	none	none	false

PRIMARY_KEY Object

Table 4-56 PRIMARY_KEY Object

Property	Type	Choices	Min	Max	Default	Description
DB_LOCATION	STRING	none	none	none	empty string	Location for referenced database objects.
DEFERRABLE	STRING	, DEFERRABLE, NOT DEFERRABLE	none	none	empty string	Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The default is NOT DEFERRABLE.
DEPLOYABLE	BOOLEAN	true, false	none	none	true	Warehouse Builder generates a set of scripts to create an object only for those object marked as Deployable = true
ENABLECONSTRAINT	STRING	, DISABLE, ENABLE	none	none	empty string	Specify ENABLE if you want the constraint to be applied to the data in the table. Specify DISABLE to disable the integrity constraint. The default is ENABLE.
EXCEPTIONSINTO	STRING	none	none	none	empty string	Specify an exceptions table ([schema.]table). The EXCEPTIONS table or the table you specify must exist on your local database. If you create your own exceptions table, then it must follow the format prescribed by one of the two scripts supplied by Oracle. Do not use this property with NOVALIDATE option.
INDEX_TABLESPACE	STRING(30)	none	none	none	empty string
INITIALLY	STRING	, DEFERRED, IMMEDIATE	none	none	empty string	Specify (INITIALLY) IMMEDIATE to indicate that Oracle should check a DEFERRABLE constraint at the end of each subsequent SQL statement. Specify (INITIALLY) DEFERRED to indicate that Oracle should check a DEFERRABLE constraint at the end of subsequent transactions. The default is (INITIALLY) IMMEDIATE.
RELY	STRING	, NORELY, RELY	none	none	empty string	Specify RELY to activate an existing constraint in NOVALIDATE mode for query rewrite in an unenforced query rewrite integrity mode. The default is NORELY.
STREAMS_ADMINISTRATOR_LOCATION	STRING	none	none	none	empty string	The location corresponding to the Streams Administrator
SUBSTITUTE_KEY	BOOLEAN	true, false	none	none	false	This is related to Streams Support. If this is true, deployment will result only in creation of the key metadata. The constraint itself will not be enforced. This will be done by creating a Streams substitute key.
USING_INDEX	BOOLEAN	true, false	none	none	false
VALIDATECONSTRAINT	STRING	, NOVALIDATE, VALIDATE	none	none	empty string	The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default. (ENABLE) VALIDATE specifies that all old and new data must compliy with the constraint. (ENABLE) NOVALIDATE only ensures that all new DML operations on the constrained data comply with the constraint. (DISABLE) VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. (DISABLE) NOVALIDATE signifies that Oracle makes no effort to maintain the constraint (because it is disabled) and cannot guarantee that the constraint is true (because it is not being validated). The default is NOVALIDATE.

UNIQUE_KEY Object

Table 4-57 UNIQUE_KEY Object

Property	Type	Choices	Min	Max	Default	Description
DB_LOCATION	STRING	none	none	none	empty string	Location for referenced database objects.
DEFERRABLE	STRING	, DEFERRABLE, NOT DEFERRABLE	none	none	empty string	Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The default is NOT DEFERRABLE.
DEPLOYABLE	BOOLEAN	true, false	none	none	true	Warehouse Builder generates a set of scripts to create an object only for those object marked as Deployable = true
ENABLECONSTRAINT	STRING	, DISABLE, ENABLE	none	none	empty string	Specify ENABLE if you want the constraint to be applied to the data in the table. Specify DISABLE to disable the integrity constraint. The default is ENABLE.
EXCEPTIONSINTO	STRING	none	none	none	empty string	Specify an exceptions table ([schema.]table). The EXCEPTIONS table or the table you specify must exist on your local database. If you create your own exceptions table, then it must follow the format prescribed by one of the two scripts supplied by Oracle. Do not use this property with NOVALIDATE option.
INDEX_TABLESPACE	STRING(30)	none	none	none	empty string	Specify index tablespace to be used for a constraint if created as an index.
INITIALLY	STRING	, DEFERRED, IMMEDIATE	none	none	empty string	Specify (INITIALLY) IMMEDIATE to indicate that Oracle should check a DEFERRABLE constraint at the end of each subsequent SQL statement. Specify (INITIALLY) DEFERRED to indicate that Oracle should check a DEFERRABLE constraint at the end of subsequent transactions. The default is (INITIALLY) IMMEDIATE.
RELY	STRING	, NORELY, RELY	none	none	empty string	Specify RELY to activate an existing constraint in NOVALIDATE mode for query rewrite in an unenforced query rewrite integrity mode. The default is NORELY.
SUBSTITUTE_KEY	BOOLEAN	true, false	none	none	false	This is related to Streams Support. If this is true, deployment will result only in creation of the key metadata. The constraint itself will not be enforced. This will be done by creating a Streams substitute key.
USING_INDEX	BOOLEAN	true, false	none	none	false	Specify True to create a constraint as an index.
VALIDATECONSTRAINT	STRING	, NOVALIDATE, VALIDATE	none	none	empty string	The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default. (ENABLE) VALIDATE specifies that all old and new data must compliy with the constraint. (ENABLE) NOVALIDATE only ensures that all new DML operations on the constrained data comply with the constraint. (DISABLE) VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. (DISABLE) NOVALIDATE signifies that Oracle makes no effort to maintain the constraint (because it is disabled) and cannot guarantee that the constraint is true (because it is not being validated). The default is NOVALIDATE.

FOREIGN_KEY Object

Table 4-58 FOREIGN_KEY Object

Property	Type	Choices	Min	Max	Default	Description
DB_LOCATION	STRING	none	none	none	empty string	Location for referenced database objects.
DEFERRABLE	STRING	, DEFERRABLE, NOT DEFERRABLE	none	none	empty string	Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The default is NOT DEFERRABLE.
DEPLOYABLE	BOOLEAN	true, false	none	none	true	Warehouse Builder generates a set of scripts to create an object only for those object marked as Deployable = true
ENABLECONSTRAINT	STRING	, DISABLE, ENABLE	none	none	empty string	Specify ENABLE if you want the constraint to be applied to the data in the table. Specify DISABLE to disable the integrity constraint. The default is ENABLE.
EXCEPTIONSINTO	STRING	none	none	none	empty string	Specify an exceptions table ([schema.]table). The EXCEPTIONS table or the table you specify must exist on your local database. If you create your own exceptions table, then it must follow the format prescribed by one of the two scripts supplied by Oracle. Do not use this property with NOVALIDATE option.
INITIALLY	STRING	, DEFERRED, IMMEDIATE	none	none	empty string	Specify (INITIALLY) IMMEDIATE to indicate that Oracle should check a DEFERRABLE constraint at the end of each subsequent SQL statement. Specify (INITIALLY) DEFERRED to indicate that Oracle should check a DEFERRABLE constraint at the end of subsequent transactions. The default is (INITIALLY) IMMEDIATE.
ONDELETE	STRING	, CASCADE, SET NULL	none	none	empty string	Specify CASCADE if you want Oracle to remove dependent foreign key values. Specify SET NULL if you want Oracle to convert dependent foreign key values to NULL.
RELY	STRING	, NORELY, RELY	none	none	empty string	Specify RELY to activate an existing constraint in NOVALIDATE mode for query rewrite in an unenforced query rewrite integrity mode. The default is NORELY.
SUBSTITUTE_KEY	BOOLEAN	true, false	none	none	false	This is related to Streams Support. If this is true, deployment will result only in creation of the key metadata. The constraint itself will not be enforced. This will be done by creating a Streams substitute key.
VALIDATECONSTRAINT	STRING	, NOVALIDATE, VALIDATE	none	none	empty string	The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default. (ENABLE) VALIDATE specifies that all old and new data must compliy with the constraint. (ENABLE) NOVALIDATE only ensures that all new DML operations on the constrained data comply with the constraint. (DISABLE) VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. (DISABLE) NOVALIDATE signifies that Oracle makes no effort to maintain the constraint (because it is disabled) and cannot guarantee that the constraint is true (because it is not being validated). The default is NOVALIDATE.

CHECK_CONSTRAINT Object

Table 4-59 CHECK_CONSTRAINT Object

Property	Type	Choices	Min	Max	Default	Description
DB_LOCATION	STRING	none	none	none	empty string	Location for referenced database objects.
DEFERRABLE	STRING	, DEFERRABLE, NOT DEFERRABLE	none	none	empty string	Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The default is NOT DEFERRABLE.
DEPLOYABLE	BOOLEAN	true, false	none	none	true	Warehouse Builder generates a set of scripts to create an object only for those object marked as Deployable = true
ENABLECONSTRAINT	STRING	, DISABLE, ENABLE	none	none	empty string	Specify ENABLE if you want the constraint to be applied to the data in the table. Specify DISABLE to disable the integrity constraint. The default is ENABLE.
EXCEPTIONSINTO	STRING	none	none	none	empty string	Specify an exceptions table ([schema.]table). The EXCEPTIONS table or the table you specify must exist on your local database. If you create your own exceptions table, then it must follow the format prescribed by one of the two scripts supplied by Oracle. Do not use this property with NOVALIDATE option.
INITIALLY	STRING	, DEFERRED, IMMEDIATE	none	none	empty string	Specify (INITIALLY) IMMEDIATE to indicate that Oracle should check a DEFERRABLE constraint at the end of each subsequent SQL statement. Specify (INITIALLY) DEFERRED to indicate that Oracle should check a DEFERRABLE constraint at the end of subsequent transactions. The default is (INITIALLY) IMMEDIATE.
RELY	STRING	, NORELY, RELY	none	none	empty string	Specify RELY to activate an existing constraint in NOVALIDATE mode for query rewrite in an unenforced query rewrite integrity mode. The default is NORELY.
SUBSTITUTE_KEY	BOOLEAN	true, false	none	none	false	This is related to Streams Support. If this is true, deployment will result only in creation of the key metadata. The constraint itself will not be enforced. This will be done by creating a Streams substitute key.
VALIDATECONSTRAINT	STRING	, NOVALIDATE, VALIDATE	none	none	empty string	The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default. (ENABLE) VALIDATE specifies that all old and new data must compliy with the constraint. (ENABLE) NOVALIDATE only ensures that all new DML operations on the constrained data comply with the constraint. (DISABLE) VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. (DISABLE) NOVALIDATE signifies that Oracle makes no effort to maintain the constraint (because it is disabled) and cannot guarantee that the constraint is true (because it is not being validated). The default is NOVALIDATE.

PARTITION Object

Table 4-60 PARTITION Object

Property	Type	Choices	Min	Max	Default	Description
BUFFER_POOL	STRING	, DEFAULT, KEEP, RECYCLE	none	none	empty string	Specify a default buffer pool (cache) for table or partition object. The default is DEFAULT.
DATA_SEGMENT_COMPRESSION	STRING	, COMPRESS, COMPRESS ALL, NOCOMPRESS	none	none	empty string	Use this clause to instruct Oracle whether to compress data segments to reduce disk use. The default is NOCOMPRESS.
DEPLOYABLE	BOOLEAN	true, false	none	none	true	Warehouse Builder generates a set of scripts to create an object only for those object marked as Deployable = true
FREELISTGROUPS	STRING	none	none	none	empty string	Specify the number of groups of free lists for the database object you are creating. The default is 1.
FREELISTS	STRING	none	none	none	empty string	Specify the number of free lists for each of the free list groups for the table, partition, cluster, or index.
INITIAL	STRING	none	none	none	empty string	Specify in bytes the size of the first extent. Use K or M to specify size in kilobytes or megabytes.
INITRANS	STRING	none	none	none	empty string	Specify the initial number (1-255) of concurrent transaction entries allocated within each data block allocated to the database object. The default is 1 for Table and 2 for Index.
LOGGING_MODE	STRING	, LOGGING, NOLOGGING	none	none	empty string	Specify whether the creation of the table and of any indexes required because of constraints, partition, or LOB storage characteristics will be logged in the redo log file (LOGGING) or not (NOLOGGING). The default is LOGGING.
MAXEXTENTS	STRING	none	none	none	empty string	Specify the total number of extents, including the first, that Oracle can allocate for the object.
MAXTRANS	STRING	none	none	none	empty string	Specify the maximum number (1-255) of concurrent transactions that can update a data block allocated to the database object.
MINEXTENTS	STRING	none	none	none	empty string	Specify the total number of extents to allocate when the object is created.
NEXT	STRING	none	none	none	empty string	Specify in bytes the size of the next extent to be allocated. Use K or M to specify size in kilobytes or megabytes.
OPTIMAL	STRING	none	none	none	empty string	Specifies an optimal size in bytes for a rollback segment. Use K or M to specify this size in kilobytes or megabytes. Specify NULL for no optimal size for the rollback segment. The default is NULL.
PCTFREE	STRING	none	none	none	empty string	Specify a whole number representing the percentage (0-99) of space in each data block of the database object reserved for future updates to the rows of the object. The default is 10.
PCTINCREASE	STRING	none	none	none	empty string	Specify the percent by which the third and subsequent extents grow over the preceding extent. The default is 50.
PCTUSED	STRING	none	none	none	empty string	Specify a whole number representing the minimum percentage (0-99) of used space that Oracle maintains for each data block of the database object. The default is 40.
TABLESPACE	STRING(30)	none	none	none	empty string

INDEX Object

Table 4-61 INDEX Object

Property	Type	Choices	Min	Max	Default	Description
BUFFER_POOL	STRING	, DEFAULT, KEEP, RECYCLE	none	none	empty string	Specify a default buffer pool (cache) for table or partition object. The default is DEFAULT.
COMPUTESTATISTICS	STRING	, NO, YES	none	none	empty string	Specify YES to collect statistics at relatively little cost during the creation of an index.
DB_LOCATION	STRING	none	none	none	empty string	Location for referenced database objects.
DEFERRABLE	STRING	, DEFERRABLE, NOT DEFERRABLE	none	none	empty string	Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The default is NOT DEFERRABLE.
DEPLOYABLE	BOOLEAN	true, false	none	none	true	Warehouse Builder generates a set of scripts to create an object only for those object marked as Deployable = true
ENABLECONSTRAINT	STRING	, DISABLE, ENABLE	none	none	empty string	Specify ENABLE if you want the constraint to be applied to the data in the table. Specify DISABLE to disable the integrity constraint. The default is ENABLE.
EXCEPTIONSINTO	STRING	none	none	none	empty string	Specify an exceptions table ([schema.]table). The EXCEPTIONS table or the table you specify must exist on your local database. If you create your own exceptions table, then it must follow the format prescribed by one of the two scripts supplied by Oracle. Do not use this property with NOVALIDATE option.
FREELISTGROUPS	STRING	none	none	none	empty string	Specify the number of groups of free lists for the database object you are creating. The default is 1.
FREELISTS	STRING	none	none	none	empty string	Specify the number of free lists for each of the free list groups for the table, partition, cluster, or index. The default is 1.
INDEXORDER	STRING	, ASC, DESC	none	none	empty string	Use ASC or DESC to indicate whether the index should be created in ascending or descending order. The Oracle default is ASC.
INITIAL	STRING	none	none	none	empty string	Specify in bytes the size of the first extent. Use K or M to specify size in kilobytes or megabytes.
INITIALLY	STRING	, DEFERRED, IMMEDIATE	none	none	empty string	Specify (INITIALLY) IMMEDIATE to indicate that Oracle should check a DEFERRABLE constraint at the end of each subsequent SQL statement. Specify (INITIALLY) DEFERRED to indicate that Oracle should check a DEFERRABLE constraint at the end of subsequent transactions. The default is (INITIALLY) IMMEDIATE.
INITRANS	STRING	none	none	none	empty string	Specify the initial number (2-255) of concurrent transaction entries allocated within each data block allocated to the database object. The default is 2 for Index.
KEYCOMPRESS	STRING	, COMPRESS, NOCOMPRESS	none	none	empty string	Specify COMPRESS to enable key compression.
KEYCOMPRESSPREFIXLENGTH	STRING	none	none	none	empty string	Specify the prefix length (number of prefix columns to compress). For unique indexes, the valid range of prefix length values is from 1 to the number of key columns minus 1. The default prefix length is the number of key columns minus 1. For nonunique indexes, the valid range of prefix length values is from 1 to the number of key columns. The default prefix length is the number of key columns. Oracle compresses only nonpartitioned indexes that are nonunique or unique indexes of at least two columns. You cannot specify COMPRESS for a bitmap index.
LOGGING_MODE	STRING	, LOGGING, NOLOGGING	none	none	empty string	Recovery requirements for a data warehouse : Logging or not logging to Redo Log File. The default is LOGGING.
MAXEXTENTS	STRING	none	none	none	empty string	Specify the total number of extents, including the first, that Oracle can allocate for the object.
MAXTRANS	STRING	none	none	none	empty string	Specify the maximum number (2-255) of concurrent transactions that can update a data block allocated to the database object.
MINEXTENTS	STRING	none	none	none	empty string	Specify the total number of extents to allocate when the object is created.
NEXT	STRING	none	none	none	empty string	Specify in bytes the size of the next extent to be allocated. Use K or M to specify size in kilobytes or megabytes.
ONLINE	STRING	, NO, YES	none	none	empty string	Specify YES to indicate that DML operations on the table will be allowed during creation of the index.
OPTIMAL	STRING	none	none	none	empty string	Specifies an optimal size in bytes for a rollback segment. Use K or M to specify this size in kilobytes or megabytes. Specify NULL for no optimal size for the rollback segment. The default is NULL.
OVERFLOW	STRING	none	none	none	empty string	Enter a comma separated list of Index tablespaces for overflow data. For simple-partitoned object, it is used for HASH BY QUANTITY partition overflow Index tablespaces. The number of Index tablespaces does not have to equal the number of partitions. If the number of partitions is greater than the number of Index tablespaces, then Oracle cycles through the names of the Index tablespaces.
PARALLEL_ACCESS_MODE	STRING	, NOPARALLEL, PARALLEL	none	none	empty string	Enables or disables parallel processing when the table is created. Also enables or disables parallel processing or access. The default is PARALLEL.
PARALLEL_DEGREE	STRING	none	none	none	empty string	Enter degree of parallelism, which is the number of parallel threads used in the parallel operation.
PARTITION_TABLESPACE_LIST	STRING	none	none	none	empty string	Enter a comma separated list of tablespaces for a locally partitioned index. For simple-partitoned object, it is used for HASH BY QUANTITY partition tablespaces. If specified, then individual local Hash index partitions specified will be ignored for Local Hash or Range-Hash Index partitioning.
PCTFREE	STRING	none	none	none	empty string	Specify a whole number representing the percentage (0-99) of space in each data block of the database object reserved for future updates to the rows of the object. The default is 10.
PCTINCREASE	STRING	none	none	none	empty string	Specify the percent by which the third and subsequent extents grow over the preceding extent. The default is 50.
RELY	STRING	, NORELY, RELY	none	none	empty string	Specify RELY to activate an existing constraint in NOVALIDATE mode for query rewrite in an unenforced query rewrite integrity mode. The default is NORELY.
SORT	STRING	, NOSORT, REVERSE, SORT	none	none	empty string	Specify NOSORT to indicate to Oracle that the rows are already stored in the database in ascending order. Specify REVERSE to store the bytes of the index block in reverse order, excluding the rowid.
SUBSTITUTE_KEY	BOOLEAN	true, false	none	none	false	This is related to Streams Support. If this is true, deployment will result only in creation of the key metadata. The constraint itself will not be enforced. This will be done by creating a Streams substitute key.
TABLESPACE	STRING(30)	none	none	none	empty string
VALIDATECONSTRAINT	STRING	, NOVALIDATE, VALIDATE	none	none	empty string	The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default. (ENABLE) VALIDATE specifies that all old and new data must compliy with the constraint. (ENABLE) NOVALIDATE only ensures that all new DML operations on the constrained data comply with the constraint. (DISABLE) VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. (DISABLE) NOVALIDATE signifies that Oracle makes no effort to maintain the constraint (because it is disabled) and cannot guarantee that the constraint is true (because it is not being validated). The default is NOVALIDATE.

OMBALTER TABLE

Purpose

Prerequisites

Syntax

Parameters

alterTableCommand

QUOTED_STRING

renameClause

alterTableSCOClauses

setPropertiesClause

getPropertiesClause

getPropertiesClause

getPropertiesClause

getPropertiesClause

getPropertiesClause

getPropertiesClause

addColumnClauseForAlter

QUOTED_STRING

addConstraintClause

addSCOClause

addDataRuleUsageClause

modifyColumnClause

modifyConstraintClause

modifySCOClause

modifyDataRuleUsageClause

deleteColumnClause

deleteConstraintClause

deleteSCOClause

QUOTED_STRING

deleteDataRuleUsageClause

propertyNameList

propertyValueList

addUkPkClause

QUOTED_STRING

addFkClause

QUOTED_STRING

addCheckConstraintClause

QUOTED_STRING

addIndexClause

QUOTED_STRING

addPartitionClause

QUOTED_STRING

addPartitionKeyClause

QUOTED_STRING

addIndexColumnClause

QUOTED_STRING

addAttributeSetClause

QUOTED_STRING

moveToClause

modifyUkPkClause

modifyFkClause

modifyCheckConstraintClause

modifyIndexClause

QUOTED_STRING

modifyPartitionClause

QUOTED_STRING

modifyPartitionKeyClause

QUOTED_STRING

modifyIndexColumnClause

modifyAttributeSetClause

propertyValue

setUkPkPropertiesAndReferencesColumnsClauses

setFkSubClauses

setSCOConfigurationPropertiesClauses

renameSCOConfigurationClause

setAttributeSetSubClauses

constraintColumnReferencesClause

setFkReferencesClauses

attributeSetAttributesClause

quotedNameList

constraintUkReferencesClause

TABLE Object

COLUMN Object

PRIMARY_KEY Object

UNIQUE_KEY Object

FOREIGN_KEY Object

CHECK_CONSTRAINT Object

PARTITION Object

INDEX Object

See Also