| Oracle® Warehouse Builder API and Scripting Reference 10g Release 2 (10.2) Part Number B28225-01 |
|
|
View PDF |
| Oracle® Warehouse Builder API and Scripting Reference 10g Release 2 (10.2) Part Number B28225-01 |
|
|
View PDF |
This chapter lists commands associated with OMBALTER in alphabetical order, starting with the command 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"
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"
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"
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" ]
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" )
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"
constraintColumnReferencesClause = COLUMNS "(" "quotedNameList" ")"
setFkReferencesClauses = "constraintColumnReferencesClause" [ SET ( REF |
REFERENCE ) "constraintUkReferencesClause" ] |
"constraintUkReferencesClause" [ SET ( REF | REFERENCE )
"constraintColumnReferencesClause" ]
quotedNameList = "QUOTED_STRING" { "," "QUOTED_STRING" }
constraintUkReferencesClause = ( UNIQUE_KEY | PRIMARY_KEY )
"QUOTED_STRING" [ OF ( TABLE | VIEW ) "QUOTED_STRING" ]
Keywords And 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.
Basic properties for TABLE:
Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: N/A
Default: ''
Business name of the table
Name: DESCRIPTION
Type: STRING(4000)
Valid Values: N/A
Default: ''
Description of the table
Basic properties for COLUMN:
Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: N/A
Default: ''
Business name of the column
Name: DESCRIPTION
Type: STRING(4000)
Valid Values: N/A
Default: ''
Description of the column
Name: DATATYPE
Type: STRING
Valid Values: BINARY_DOUBLE, BINARY_FLOAT, BLOB, CHAR, CLOB, DATE, FLOAT,
INTEGER, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, NCHAR, NCLOB,
NUMBER, NVARCHAR2, RAW, SYS.ANYDATA, SYS.LCR$_ROW_RECORD, SYS.XMLFORMAT,
TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE, TIMESTAMP WITH TIME ZONE,
VARHCAR, VARCHAR2, XMLTYPE
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.
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: N/A
Default: ''
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.
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 preceding preceding choices)
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: N/A
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.
Basic properties for PARTITION:
Name: VALUES_LESS_THAN
Type: STRING
Valid Values: N/A
Default: ''
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: N/A
Default: ''
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 "" (for example
'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: N/A
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.
Basic properties for SUBPARTITION_KEY:
Name: TYPE
Type: STRING
Valid Values: LIST, HASH, HASH BY QUANTITY
Default: (No default, must be one of the preceding choices)
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: N/A
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: N/A
Default: ''
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: N/A
Default: ''
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.
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 preceding choices)
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: N/A
Default: ''
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 preceding choices)
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 Database 10g version.
Name: HASH_QUANTITY
Type: STRING
Valid Values: N/A
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: N/A
Default: ''
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: N/A
Default: ''
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.
Basic properties for CHECK_CONSTRAINTS:
Name: CHECK_CONDITION
Type: STRING
Valid Values: N/A
Default: ''
Specify a condition that each row in the table must satisfy.
Properties for TABLE:
Name: BUFFER_POOL
Type: STRING
Valid Values: , DEFAULT, KEEP, RECYCLE
Default: ''
Specify a default buffer pool (cache) for table or partition object. The
default is DEFAULT.
Name: CACHE_MODE
Type: STRING
Valid Values: , CACHE, NOCACHE
Default: ''
Indicate how Oracle should store blocks in the buffer cache.
Name: DATA_SEGMENT_COMPRESSION
Type: STRING
Valid Values: , COMPRESS, NOCOMPRESS
Default: ''
Use this clause to instruct Oracle whether to compress data segments to
reduce disk use. The default is NOCOMPRESS.
Name: DEPLOYABLE
Type: BOOLEAN
Valid Values: true, false
Default: true
Warehouse Builder generates a set of scripts to create an object only for
those object marked as Deployable = true
Name: FREELISTGROUPS
Type: STRING
Valid Values: N/A
Default: ''
Specify the number of groups of free lists for the database object you are
creating. The default is 1.
Name: FREELISTS
Type: STRING
Valid Values: N/A
Default: ''
Specify the number of free lists for each of the free list groups for the
table, partition, cluster, or index. The default is 1.
Name: GENERATION_COMMENTS
Type: STRING
Valid Values: N/A
Default: ''
Enter additional comments for the generated code.
Name: INITIAL
Type: STRING
Valid Values: N/A
Default: ''
Specify in bytes the size of the first extent. Use K or M to specify size
in kilobytes or megabytes.
Name: INITRANS
Type: STRING
Valid Values: N/A
Default: ''
Specify the initial number (1-255) of concurrent transaction entries
allocated within each data block allocated to the database object. The
default is 1.
Name: LOGGING_MODE
Type: STRING
Valid Values: , LOGGING, NOLOGGING
Default: ''
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.
Name: MAXEXTENTS
Type: STRING
Valid Values: N/A
Default: ''
Specify the total number of extents, including the first, that Oracle can
allocate for the object.
Name: MAXTRANS
Type: STRING
Valid Values: N/A
Default: ''
Specify the maximum number (1-255) of concurrent transactions that can
update a data block allocated to the database object.
Name: MINEXTENTS
Type: STRING
Valid Values: N/A
Default: ''
Specify the total number of extents to allocate when the object is created.
Name: MONITORING_MODE
Type: STRING
Valid Values: , MONITORING, NOMONITORING
Default: ''
Specify MONITORING if you want modification statistics to be collected on
this table.
Name: NEXT
Type: STRING
Valid Values: N/A
Default: ''
Specify in bytes the size of the next extent to be allocated. Use K or M to
specify size in kilobytes or megabytes.
Name: OVERFLOW
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: PARALLEL_ACCESS_MODE
Type: STRING
Valid Values: , NOPARALLEL, PARALLEL
Default: ''
Enables or disables parallel processing when the table is created. Also
enables or disables parallel processing or access. The default is PARALLEL.
Name: PARALLEL_DEGREE
Type: STRING
Valid Values: N/A
Default: ''
Enter degree of parallelism, which is the number of parallel threads used
in the parallel operation.
Name: PARTITION_TABLESPACE_LIST
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: PCTFREE
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: PCTINCREASE
Type: STRING
Valid Values: N/A
Default: ''
Specify the percent by which the third and subsequent extents grow over the
preceding extent. The default is 50.
Name: PCTUSED
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: ROWDEPENDENCIES_MODE
Type: STRING
Valid Values: , NOROWDEPENDENCIES, ROWDEPENDENCIES
Default: ''
Specify ROWDEPENDENCIES to use row-level dependency tracking.
Name: ROW_MOVEMENT
Type: STRING
Valid Values: , DISABLE, ENABLE
Default: ''
Specify whether Oracle can move a table row.
Name: SHADOW_TABLESPACE
Type: STRING(30)
Valid Values: N/A
Default: ''
Use the Tablespace parameter to specify the name of tablespace.
Name: SHADOW_TABLE_NAME
Type: STRING(30)
Valid Values: N/A
Default: ''
Use the shadow table name to specify the name of Shadow Table.
Name: TABLESPACE
Type: STRING(30)
Valid Values: N/A
Default: ''
Use the Tablespace parameter to specify the name of tablespace.
Properties for PRIMARY_KEY:
Name: DEFERRABLE
Type: STRING
Valid Values: , DEFERRABLE, NOT DEFERRABLE
Default: ''
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.
Name: DEPLOYABLE
Type: BOOLEAN
Valid Values: true, false
Default: true
Warehouse Builder generates a set of scripts to create an object only for
those object marked as Deployable = true
Name: ENABLECONSTRAINT
Type: STRING
Valid Values: , DISABLE, ENABLE
Default: ''
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.
Name: EXCEPTIONSINTO
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: INDEX_TABLESPACE
Type: STRING(30)
Valid Values: N/A
Default: ''
oracle.owb.scripting.help.OMBHelpGenerator(557): NLS Lookup error for key
"9i.TABLE.CONSTRAINT.INDEXTABLESPACE:DESCRIPTION"
Name: INITIALLY
Type: STRING
Valid Values: , DEFERRED, IMMEDIATE
Default: ''
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.
Name: RELY
Type: STRING
Valid Values: , NORELY, RELY
Default: ''
Specify RELY to activate an existing constraint in NOVALIDATE mode for
query rewrite in an unenforced query rewrite integrity mode. The default is
NORELY.
Name: SUBSTITUTE_KEY
Type: BOOLEAN
Valid Values: true, false
Default: 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.
Name: USING_INDEX
Type: BOOLEAN
Valid Values: true, false
Default: false
oracle.owb.scripting.help.OMBHelpGenerator(557): NLS Lookup error for key
"9i.TABLE.CONSTRAINT.USINGINDEX:DESCRIPTION"
Name: VALIDATECONSTRAINT
Type: STRING
Valid Values: , NOVALIDATE, VALIDATE
Default: ''
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.
Properties for UNIQUE_KEY:
Name: DEFERRABLE
Type: STRING
Valid Values: , DEFERRABLE, NOT DEFERRABLE
Default: ''
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.
Name: DEPLOYABLE
Type: BOOLEAN
Valid Values: true, false
Default: true
Warehouse Builder generates a set of scripts to create an object only for
those object marked as Deployable = true
Name: ENABLECONSTRAINT
Type: STRING
Valid Values: , DISABLE, ENABLE
Default: ''
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.
Name: EXCEPTIONSINTO
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: INDEX_TABLESPACE
Type: STRING(30)
Valid Values: N/A
Default: ''
oracle.owb.scripting.help.OMBHelpGenerator(557): NLS Lookup error for key
"9i.TABLE.CONSTRAINT.INDEXTABLESPACE:DESCRIPTION"
Name: INITIALLY
Type: STRING
Valid Values: , DEFERRED, IMMEDIATE
Default: ''
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.
Name: RELY
Type: STRING
Valid Values: , NORELY, RELY
Default: ''
Specify RELY to activate an existing constraint in NOVALIDATE mode for
query rewrite in an unenforced query rewrite integrity mode. The default is
NORELY.
Name: SUBSTITUTE_KEY
Type: BOOLEAN
Valid Values: true, false
Default: 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.
Name: USING_INDEX
Type: BOOLEAN
Valid Values: true, false
Default: false
oracle.owb.scripting.help.OMBHelpGenerator(557): NLS Lookup error for key
"9i.TABLE.CONSTRAINT.USINGINDEX:DESCRIPTION"
Name: VALIDATECONSTRAINT
Type: STRING
Valid Values: , NOVALIDATE, VALIDATE
Default: ''
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.
Properties for FOREIGN_KEY:
Name: DEFERRABLE
Type: STRING
Valid Values: , DEFERRABLE, NOT DEFERRABLE
Default: ''
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.
Name: DEPLOYABLE
Type: BOOLEAN
Valid Values: true, false
Default: true
Warehouse Builder generates a set of scripts to create an object only for
those object marked as Deployable = true
Name: ENABLECONSTRAINT
Type: STRING
Valid Values: , DISABLE, ENABLE
Default: ''
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.
Name: EXCEPTIONSINTO
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: INITIALLY
Type: STRING
Valid Values: , DEFERRED, IMMEDIATE
Default: ''
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.
Name: ONDELETE
Type: STRING
Valid Values: , CASCADE, SET NULL
Default: ''
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.
Name: RELY
Type: STRING
Valid Values: , NORELY, RELY
Default: ''
Specify RELY to activate an existing constraint in NOVALIDATE mode for
query rewrite in an unenforced query rewrite integrity mode. The default is
NORELY.
Name: SUBSTITUTE_KEY
Type: BOOLEAN
Valid Values: true, false
Default: 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.
Name: VALIDATECONSTRAINT
Type: STRING
Valid Values: , NOVALIDATE, VALIDATE
Default: ''
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.
Properties for CHECK_CONSTRAINT:
Name: DEFERRABLE
Type: STRING
Valid Values: , DEFERRABLE, NOT DEFERRABLE
Default: ''
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.
Name: DEPLOYABLE
Type: BOOLEAN
Valid Values: true, false
Default: true
Warehouse Builder generates a set of scripts to create an object only for
those object marked as Deployable = true
Name: ENABLECONSTRAINT
Type: STRING
Valid Values: , DISABLE, ENABLE
Default: ''
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.
Name: EXCEPTIONSINTO
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: INITIALLY
Type: STRING
Valid Values: , DEFERRED, IMMEDIATE
Default: ''
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.
Name: RELY
Type: STRING
Valid Values: , NORELY, RELY
Default: ''
Specify RELY to activate an existing constraint in NOVALIDATE mode for
query rewrite in an unenforced query rewrite integrity mode. The default is
NORELY.
Name: SUBSTITUTE_KEY
Type: BOOLEAN
Valid Values: true, false
Default: 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.
Name: VALIDATECONSTRAINT
Type: STRING
Valid Values: , NOVALIDATE, VALIDATE
Default: ''
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.
Properties for PARTITION:
Name: BUFFER_POOL
Type: STRING
Valid Values: , DEFAULT, KEEP, RECYCLE
Default: ''
Specify a default buffer pool (cache) for table or partition object. The
default is DEFAULT.
Name: DATA_SEGMENT_COMPRESSION
Type: STRING
Valid Values: , COMPRESS, NOCOMPRESS
Default: ''
Use this clause to instruct Oracle whether to compress data segments to
reduce disk use. The default is NOCOMPRESS.
Name: DEPLOYABLE
Type: BOOLEAN
Valid Values: true, false
Default: true
Warehouse Builder generates a set of scripts to create an object only for
those object marked as Deployable = true
Name: FREELISTGROUPS
Type: STRING
Valid Values: N/A
Default: ''
Specify the number of groups of free lists for the database object you are
creating. The default is 1.
Name: FREELISTS
Type: STRING
Valid Values: N/A
Default: ''
Specify the number of free lists for each of the free list groups for the
table, partition, cluster, or index.
Name: INITIAL
Type: STRING
Valid Values: N/A
Default: ''
Specify in bytes the size of the first extent. Use K or M to specify size
in kilobytes or megabytes.
Name: INITRANS
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: LOGGING_MODE
Type: STRING
Valid Values: , LOGGING, NOLOGGING
Default: ''
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.
Name: MAXEXTENTS
Type: STRING
Valid Values: N/A
Default: ''
Specify the total number of extents, including the first, that Oracle can
allocate for the object.
Name: MAXTRANS
Type: STRING
Valid Values: N/A
Default: ''
Specify the maximum number (1-255) of concurrent transactions that can
update a data block allocated to the database object.
Name: MINEXTENTS
Type: STRING
Valid Values: N/A
Default: ''
Specify the total number of extents to allocate when the object is created.
Name: NEXT
Type: STRING
Valid Values: N/A
Default: ''
Specify in bytes the size of the next extent to be allocated. Use K or M to
specify size in kilobytes or megabytes.
Name: OPTIMAL
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: PCTFREE
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: PCTINCREASE
Type: STRING
Valid Values: N/A
Default: ''
Specify the percent by which the third and subsequent extents grow over the
preceding extent. The default is 50.
Name: PCTUSED
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: TABLESPACE
Type: STRING(30)
Valid Values: N/A
Default: ''
Use the Tablespace parameter to specify the name of tablespace.
Properties for INDEX:
Name: BUFFER_POOL
Type: STRING
Valid Values: , DEFAULT, KEEP, RECYCLE
Default: ''
Specify a default buffer pool (cache) for table or partition object. The
default is DEFAULT.
Name: COMPUTESTATISTICS
Type: STRING
Valid Values: , NO, YES
Default: ''
Specify YES to collect statistics at relatively little cost during the
creation of an index.
Name: DEPLOYABLE
Type: BOOLEAN
Valid Values: true, false
Default: true
Warehouse Builder generates a set of scripts to create an object only for
those object marked as Deployable = true
Name: FREELISTGROUPS
Type: STRING
Valid Values: N/A
Default: ''
Specify the number of groups of free lists for the database object you are
creating. The default is 1.
Name: FREELISTS
Type: STRING
Valid Values: N/A
Default: ''
Specify the number of free lists for each of the free list groups for the
table, partition, cluster, or index. The default is 1.
Name: INDEXORDER
Type: STRING
Valid Values: , ASC, DESC
Default: ''
Use ASC or DESC to indicate whether the index should be created in
ascending or descending order. The Oracle default is ASC.
Name: INITIAL
Type: STRING
Valid Values: N/A
Default: ''
Specify in bytes the size of the first extent. Use K or M to specify size
in kilobytes or megabytes.
Name: INITRANS
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: KEYCOMPRESS
Type: STRING
Valid Values: , COMPRESS, NOCOMPRESS
Default: ''
Specify COMPRESS to enable key compression.
Name: KEYCOMPRESSPREFIXLENGTH
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: LOGGING_MODE
Type: STRING
Valid Values: , LOGGING, NOLOGGING
Default: ''
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.
Name: MAXEXTENTS
Type: STRING
Valid Values: N/A
Default: ''
Specify the total number of extents, including the first, that Oracle can
allocate for the object.
Name: MAXTRANS
Type: STRING
Valid Values: N/A
Default: ''
Specify the maximum number (2-255) of concurrent transactions that can
update a data block allocated to the database object.
Name: MINEXTENTS
Type: STRING
Valid Values: N/A
Default: ''
Specify the total number of extents to allocate when the object is created.
Name: NEXT
Type: STRING
Valid Values: N/A
Default: ''
Specify in bytes the size of the next extent to be allocated. Use K or M to
specify size in kilobytes or megabytes.
Name: ONLINE
Type: STRING
Valid Values: , NO, YES
Default: ''
Specify YES to indicate that DML operations on the table will be allowed
during creation of the index.
Name: OPTIMAL
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: OVERFLOW
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: PARALLEL_ACCESS_MODE
Type: STRING
Valid Values: , NOPARALLEL, PARALLEL
Default: ''
Enables or disables parallel processing when the table is created. Also
enables or disables parallel processing or access. The default is PARALLEL.
Name: PARALLEL_DEGREE
Type: STRING
Valid Values: N/A
Default: ''
Enter degree of parallelism, which is the number of parallel threads used
in the parallel operation.
Name: PARTITION_TABLESPACE_LIST
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: PCTFREE
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: PCTINCREASE
Type: STRING
Valid Values: N/A
Default: ''
Specify the percent by which the third and subsequent extents grow over the
preceding extent. The default is 50.
Name: SORT
Type: STRING
Valid Values: , NOSORT, REVERSE, SORT
Default: ''
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.
Name: SUBSTITUTE_KEY
Type: BOOLEAN
Valid Values: true, false
Default: 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.
Name: TABLESPACE
Type: STRING(30)
Valid Values: N/A
Default: ''
Use the Tablespace parameter to specify the name of tablespace.
Note:
1. N/A means any valid character in supported character set.
2. '' represents an empty string
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.
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 the adds unique key and primary keys.
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.
moveToClause
This clause will move the column to given position.
modifyUkPkClause
It modifies 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.
propertyValue
This clause adds the property values.
setUkPkPropertiesAndReferencesColumnsClauses
This clause adds properties and references to columns.
setFkSubClauses
This clause set 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.
constraintColumnReferencesClause
This clause provides names of all columns.
setFkReferencesClauses
This clause sets foreign key references.
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.
Examples
OMBALTER TABLE 'new_table' SET PROPERTIES (DESCRIPTION, BUSINESS_NAME)
VALUES ('this is an altered desc of new table', 'Altered New Table')
This will alter a table named "NEW_TABLE", its description is "this is an
altered desc of new table", and business name is "Altered New Table".
See Also
OMBALTER, OMBCREATE TABLE, OMBDROP TABLE, OMBRETRIEVE TABLE
Purpose
Alter the Table Function by renaming it, and/or reset its properties, and/or resetting its Ordered/Partitioned fields,
and/or adding Parameters.
Prerequisites
Should be in the context of Oracle Module or Package. The REFCursorType and
PLSQLTableType which are set as Datatype for parameters should preexist in
corresponding Package.
Syntax
alterFunctionCommand = OMBALTER ( FUNCTION "QUOTED_STRING" (
"renameClause" [ "alterPropertiesOrIconSetClause" ] [
"alterFuncProcParameterSCOClause" ] | "alterPropertiesOrIconSetClause"
[ "alterFuncProcParameterSCOClause" ] |
"alterFuncProcParameterSCOClause" ) )
renameClause = RENAME TO "QUOTED_STRING"
alterPropertiesOrIconSetClause = SET ( "setPropertiesClause" [ SET
"setReferenceIconSetClause" | UNSET "unsetReferenceIconSetClause" ] |
"setReferenceIconSetClause" ) | UNSET "unsetReferenceIconSetClause"
alterFuncProcParameterSCOClause = ( ADD ( "alterFuncProcParameterClause" |
"addRelationalDependentClause" ) | MODIFY
"modifyFuncProcParameterClause" | DELETE (
"deleteFuncProcParameterClause" | "deleteRelationalDependentClause" )
) [ "alterFuncProcParameterSCOClause" ]
setPropertiesClause = PROPERTIES "(" "propertyNameList" ")" VALUES "("
"propertyValueList" ")"
setReferenceIconSetClause = ( REFERENCE | REF ) ICONSET "QUOTED_STRING"
unsetReferenceIconSetClause = ( REFERENCE | REF ) ICONSET
alterFuncProcParameterClause = PARAMETER "QUOTED_STRING" [ AT POSITION
"INTEGER_LITERAL" ] [ SET "setPropertiesClause" ]
addRelationalDependentClause = ( REFERENCE | REF ) ( TABLE | VIEW |
MATERIALIZED_VIEW | SEQUENCE | FUNCTION | PROCEDURE | PACKAGE )
"QUOTED_STRING"
modifyFuncProcParameterClause = ( PARAMETER "QUOTED_STRING" (
"renameClause" | "moveToClause" | [ SET "setPropertiesClause" ] ) )
deleteFuncProcParameterClause = ( PARAMETER "QUOTED_STRING" )
deleteRelationalDependentClause = ( REFERENCE | REF ) ( TABLE | VIEW |
MATERIALIZED_VIEW | SEQUENCE | FUNCTION | PROCEDURE | PACKAGE )
"QUOTED_STRING"
propertyNameList = "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }
propertyValueList = "propertyValue" { "," "propertyValue" }
moveToClause = MOVE TO POSITION "INTEGER_LITERAL"
propertyValue = ( "QUOTED_STRING" | "INTEGER_LITERAL" |
"FLOATING_POINT_LITERAL" )
Keywords And Parameters
alterFunctionCommand
Alters a Table Function
renameClause
Rename a Table Function
alterFuncProcParameterSCOClause
Modify, delete or add a Parameter for Function/Procedure, or add or delete
dependencies to some other relational objects.
setPropertiesClause
Associate a set of properties with a Table Function.
Properties for TABLE_FUNCTION:
Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: N/A
Default: ''
Business name of the Table Function
Name: DESCRIPTION
Type: STRING(4000)
Valid Values: N/A
Default: ''
Description of the Table Function
Name: PARALLEL_EXECUTION
Type: STRING(5)
Valid Values: TRUE, FALSE
Default: ''
Enables Parallel Execution of the Table Function
Name: PIPELINED_EXECUTION
Type: STRING(5)
Valid Values: TRUE, FALSE
Default: ''
Enables Partitioned Execution of the Table Function
Name: ORDER_METHOD
Type: STRING(9)
Valid Values: ORDERBY, CLUSTERBY
Default: ORDERBY
Order Method for the Table Function
Name: PARTITION_METHOD
Type: STRING(5)
Valid Values: NONE, ANY, HASH, RANGE
Default: NONE
Partition Method for the Table Function
Name: RETURN_TYPE
Type: STRING(4000)
Valid Values: N/A
Default: ''
Name of the Return Type of this Table Function. For this release, this has
to be a PLSQL Table Type whose datatype has to be PLSQLRecordType. Also, it
should already be defined in USER_TYPES Package in this Module.
Name: IMPLEMENTATION
Type: STRING(4000)
Valid Values: N/A
Default: ''
Implementation code for this Table Function.
Name: IS_DETERMINISTIC
Type: STRING(5)
Valid Values: TRUE, FALSE
Default: ''
Whether this Table Function is Deterministic.
Properties for PARAMETER:
Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: N/A
Default: ''
Business name of the Parameter
Name: DESCRIPTION
Type: STRING(4000)
Valid Values: N/A
Default: ''
Description of the Parameter
Name: DATATYPE
Type: STRING(4000)
Valid Values: Any valid REF cursor type
Default: ''
Datatype of the Parameter. Parameter will always be IN type for Table
Function. For this release, it has to be a REF Cursor type. Also, this REF
Cursor should already be defined in USER_TYPES Package in this Module.
Properties for TABLE_FUNCTION:
Name: AUTHID
Type: STRING
Valid Values: Current_User, Definer, None
Default: None
Generate the transformation with selected AUTHID option. Function will be
executed with the permissions defined by the AUTHID clause rather than the
function owner's permissions.
Name: DEPLOYABLE
Type: BOOLEAN
Valid Values: true, false
Default: true
Warehouse Builder generates a set of scripts to create an object only for
those object marked as Deployable = true
Name: GENERATION_COMMENTS
Type: STRING
Valid Values: N/A
Default: ''
Enter additional comments for the generated code.
Note:
1. N/A means any valid character in supported character set.
2. '' represents an empty string
alterFuncProcParameterClause
Alter the parameters of a table function, including adding, dropping
parameters, changing their direction, datatype and default values.
addRelationalDependentClause
This clause adds referential dependencies to other relational objects.
modifyFuncProcParameterClause
Modify one or more Parameters to this Function/Procedure.
deleteFuncProcParameterClause
Delete one or more Parameters to this Function/Procedure.
deleteRelationalDependentClause
This clause deletes referential dependencies to other relational objects.
propertyNameList
Comma separated list of property names. Property names are unquoted.
propertyValueList
Comma separated list of property values.
moveToClause
Move a Parameters of this Function/Procedure.
propertyValue
Value of a property.
Examples
OMBALTER TABLE_FUNCTION 'table_function' RENAME TO 'table_function2' SET
PROPERTIES (PARTITION_METHOD, ORDER_METHOD) VALUES ('RANGE', 'CLUSTERBY')
This will rename the Table Function "table_function" to "table_function2",
and set its property OrderMethod to "CLUSTERBY" and property
PartitionMethod to "RANGE".
See Also
OMBALTER, OMBCREATE TABLE_FUNCTION, OMBDROP TABLE_FUNCTION
Purpose
This command alter a time dimension.
Prerequisites
Should be in Oracle Module context.
Syntax
alterTimeDimensionCommand = OMBALTER TIME_DIMENSION "TimeDimensionName" (
( "renameDimensionClause" [ "setPropertiesClause" ] |
"setPropertiesClause" ) | "setFiscalPropertyClause" |
"renameMapClause" | "useSequenceClause" | ( "addDimensionRoleClause" |
DELETE ( "deleteDimensionRoleClause" | "deleteLevelClause" ) | MODIFY
( "modifyDimensionRoleClause" | "modifyLevelClause" ) )+ | ( {
"addCalendarHierarchyClause" | "modifyCalendarHierarchyClause" |
"addFiscalCalendarHierarchyClause" |
"modifyFiscalCalendarHierarchyClause" | "deleteHierarchyClause" } [
"implementationClause" ] [ "populationClause" ] ) )
TimeDimensionName = "QUOTED_STRING"
renameDimensionClause = RENAME DIMENSION TO "QUOTED_STRING"
setPropertiesClause = SET PROPERTIES "propertyKeyList" VALUES
"propertyValueList"
setFiscalPropertyClause = "setFiscalPropertiesClause"
renameMapClause = RENAME MAPPING TO "QUOTED_STRING"
useSequenceClause = SET REF SEQUENCE "QUOTED_STRING"
addDimensionRoleClause = ADD DIMENSION_ROLE "roleName" [
"setPropertiesClause" ]
deleteDimensionRoleClause = DIMENSION_ROLE "roleName"
deleteLevelClause = "levelLocator"
modifyDimensionRoleClause = DIMENSION_ROLE "roleName" RENAME TO "roleName"
[ "setPropertiesClause" ]
modifyLevelClause = "levelLocator" ( "renameClause" [
"setPropertiesClause" ] )
addCalendarHierarchyClause = ADD ( ( NORMAL_CALENDAR HIERARCHY
"hierarchyName" [ "setPropertiesClause" ] SET ( REF | REFERENCE )
"normalCalendarLevelList" ) | ( WEEK_CALENDAR HIERARCHY
"hierarchyName" [ "setPropertiesClause" ] SET ( REF | REFERENCE )
"weekCalendarLevelList" ) )
modifyCalendarHierarchyClause = MODIFY ( ( NORMAL_CALENDAR
"hierarchyLocator" ( [ "renameClause" ] [ "setPropertiesClause" ] SET
( REF | REFERENCE ) "normalCalendarLevelList" ) ) | ( WEEK_CALENDAR
"hierarchyLocator" [ "renameClause" ] [ "setPropertiesClause" ] SET (
REF | REFERENCE ) "weekCalendarLevelList" ) )
addFiscalCalendarHierarchyClause = ADD FISCAL_CALENDAR HIERARCHY
"hierarchyName" [ "setPropertiesClause" ] SET ( REF | REFERENCE )
"fiscalCalendarLevelList"
modifyFiscalCalendarHierarchyClause = MODIFY FISCAL_CALENDAR
"hierarchyLocator" [ "renameClause" ] [ "setPropertiesClause" ] SET (
REF | REFERENCE ) "fiscalCalendarLevelList"
deleteHierarchyClause = DELETE "hierarchyLocator"
implementationClause = IMPLEMENTED BY ( STAR | SNOWFLAKE ) [ USING
COMPOSITE_UNIQUE_KEY ]
populationClause = POPULATE DATA FROM "calendarYear" FOR "yearCount" YEARS
propertyKeyList = "(" "propertyKey" { "," "propertyKey" } ")"
propertyValueList = "(" "propertyValue" { "," "propertyValue" } ")"
setFiscalPropertiesClause = SET FISCAL_CALENDAR PROPERTIES
"propertyKeyList" VALUES "propertyValueList"
roleName = "QUOTED_STRING"
levelLocator = LEVEL "levelName"
renameClause = RENAME TO "QUOTED_STRING"
hierarchyName = "QUOTED_STRING"
normalCalendarLevelList = "(" ( "normalCalendarLevelType" LEVEL [
"levelName" ] [ "setPropertiesClause" ] ) { ","
"normalCalendarLevelType" LEVEL [ "levelName" ] [
"setPropertiesClause" ] } ")"
weekCalendarLevelList = "(" ( "weekCalendarLevelType" LEVEL [ "levelName"
] [ "setPropertiesClause" ] ) { "," "weekCalendarLevelType" LEVEL [
"levelName" ] [ "setPropertiesClause" ] } ")"
hierarchyLocator = HIERARCHY "hierarchyName"
fiscalCalendarLevelList = "(" ( "fiscalCalendarLevelType" LEVEL [
"levelName" ] [ "setPropertiesClause" ] ) { ","
"fiscalCalendarLevelType" LEVEL [ "levelName" ] [
"setPropertiesClause" ] } ")"
calendarYear = "INTEGER_LITERAL"
yearCount = "INTEGER_LITERAL"
propertyKey = "UNQUOTED_STRING"
propertyValue = ( "QUOTED_STRING" | "INTEGER_LITERAL" |
"FLOATING_POINT_LITERAL" )
levelName = "QUOTED_STRING"
normalCalendarLevelType = ( DAY | CALENDAR_MONTH | CALENDAR_QUARTER |
CALENDAR_YEAR )
weekCalendarLevelType = ( DAY | CALENDAR_WEEK )
fiscalCalendarLevelType = ( DAY | FISCAL_WEEK | FISCAL_MONTH |
FISCAL_QUARTER | FISCAL_YEAR )
Keywords And Parameters
TimeDimensionName
The name of the time dimension.
renameDimensionClause
This clause renames the time dimension.
setPropertiesClause
This clause sets the following properties:
Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: N/A
Default: ''
Name: DESCRIPTION
Type: STRING(4000)
Valid Values: N/A
Default: ''
setFiscalPropertyClause
set the fiscal properties.
renameMapClause
This clause renames the map that is created by the OMBCREATE TIME_DIMENSION
command.
useSequenceClause
This clause sets the Sequence.
addDimensionRoleClause
This clause adds a dimension role.
deleteDimensionRoleClause
This clause deletes a dimension role.
deleteLevelClause
This clause finds the level to be deleted.
modifyDimensionRoleClause
This clause allows to rename the dimension role, or it change the dimension
role's properties.
modifyLevelClause
This clause modifies level by either renaming it, or setting level
properties.
addCalendarHierarchyClause
This clause adds a new hierarchy to the time dimension by: renaming the
hierarchy, setting of hierarchy properties, or setting level references.
modifyCalendarHierarchyClause
This clause modifies a hierarchy of the time dimension by: renaming the
hierarchy, setting of hierarchy properties, or setting level references.
addFiscalCalendarHierarchyClause
This clause adds an fiscal hierarchy to the time dimension.
modifyFiscalCalendarHierarchyClause
This clause modifies a fiscal hierarchy of the time dimension by: renaming
the fiscal hierarchy, setting of fiscal hierarchy properties, or setting
fiscal level references.
implementationClause
Time Dimension is implemented as STAR or as SNOWFLAKE.
populationClause
This clause specifies the starting year and the number of years for which
data will be populated.
propertyKeyList
A list of time dimension properties.
propertyValueList
A list of time dimension property values.
setFiscalPropertiesClause
This clause sets the following properties:
Fiscal types allowed in OWB time dimension. Name: FISCAL_TYPE
Type: STRING
Valid Values: '544', '445'
Default: '544'
Fiscal calendar year start date, it could be any date of a year.
Name: FISCAL_CALENDAR_START_YEAR
Type: STRING
Valid Values: Dates in these format 'DD-MON-YYYY' or 'DD-MM-YYYY'
Default: '01-JAN-2000'
The day of the week when the fiscal year begins.
Name: FISCAL_CALENDAR_START_DAY_OF_WEEK
Type: STRING
Valid Values: 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY',
'SATURDAY', 'SUNDAY'
Default: 'false'
roleName
A role name.
levelLocator
This clause gets the level.
hierarchyName
The name of a hierarchy.
hierarchyLocator
This clause gets the hierarchy.
fiscalCalendarLevelList
This clause creates a fiscal hierarchy and sets reference fiscal levels.
propertyKey
Basic properties for TIME DIMENSION, TIME DIMENSION MAP,
DIMENSION_ATTRIBUTE, LEVEL, LEVEL_ATTRIBUTE and HIERARCHY:
Basic properties for TIME DIMENSION :
Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: N/A
Default: ''
Business name of the Time Dimension
Name: DESCRIPTION
Type: STRING(4000)
Valid Values: N/A
Default: ''
Description of the Time Dimension
Name: STORAGE
Type: STRING
Valid Values: 'RELATIONAL', 'AW'
Default: 'RELATIONAL'
The storage of a dimension can be AW or relational
Name: AW_NAME
Type: STRING(32)
Valid Values: N/A
Default: ''
Set the analytical workspace name where the dimension is implemented
Name: AW_DIMENSION_NAME
Type: STRING(32)
Valid Values: N/A
Default: ''
Set the Analytical Workspace dimension physical object name
Basic properties for TIME MAP :
Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: N/A
Default: ''
Business name of the Time Dimension Map
Name: DESCRIPTION
Type: STRING(4000)
Valid Values: N/A
Default: ''
Description of the Time Dimension Map
Basic properties for DIMENSION_ATTRIBUTE:
Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: N/A
Default: ''
Business name of the Dimension_Attribute
Name: DESCRIPTION
Type: STRING(4000)
Valid Values: N/A
Default: ''
Properties for DIMENSION:
Name: DEPLOYABLE
Type: BOOLEAN
Valid Values: true, false
Default: true
Warehouse Builder generates a set of scripts to create an object only for
those object marked as Deployable = true
Name: DEPLOYMENT_OPTIONS
Type: STRING
Valid Values: DEPLOY_ALL, DEPLOY_DATA_OBJECTS_ONLY, DEPLOY_TO_CATALOG_ONLY
Default: DEPLOY_DATA_OBJECTS_ONLY
Warehouse Builder generates a set of scripts for Dimension, they are DDL
Scripts for Relational Dimensional or Scripts for ROLAP or or Scripts for
AW.
Name: GENERATION_COMMENTS
Type: STRING
Valid Values: N/A
Default: ''
Enter additional comments for the generated code.
Name: VIEW_NAME
Type: STRING(30)
Valid Values: N/A
Default: ''
Name of the view that is generated to hide the control rows on the
dimension implementation table of a star schema. If this field is left
blank, the view name will default to '<Name of Dimension>_v'
Name: VISIBLE
Type: BOOLEAN
Valid Values: true, false
Default: true
The Dimension is visible to OLAP end user if value is set = true.
Note:
1. N/A means any valid character in supported character set.
2. '' represents an empty string
Examples
We assume that the time dimension 'FYR2005' already exists ...
OMBALTER TIME_DIMENSION 'FYR2005'
SET FISCAL_CALENDAR PROPERTIES ( FISCAL_TYPE, FISCAL_CALENDAR_START_YEAR,
FISCAL_CALENDAR_START_DAY_OF_WEEK )
VALUES ( '544', '01-01-2000', 'MONDAY' )
ADD FISCAL_CALENDAR HIERARCHY 'FCALH1'
SET PROPERTIES ( DESCRIPTION, BUSINESS_NAME )
VALUES ('TimeSeries Hierarchy Description 1', 'TIME DIMENSION HIERARCHY
FSTAR1 FCALH1')
DELETE FISCAL_CALENDAR HIERARCHY 'FCALH0'
MODIFY FISCAL_CALENDAR HIERARCHY 'FCALH3'
SET PROPERTIES ( DESCRIPTION, BUSINESS_NAME )
VALUES ('TimeSeries Hierarchy Description 1', 'TIME DIMENSION HIERARCHY
FSTAR1 FCALH1')
SET REF ( DAY LEVEL 'MY_DAY' , FISCAL_YEAR LEVEL 'MY_FISCAL_YEAR' )
ADD DIMENSION_ROLE 'FISCAL_2005_SHIPMENTS'
IMPLEMENTED BY STAR
ADD REF SEQUENCE 'YR2005_1'
POPULATE DATA FROM 2000 FOR 2 YEARS.
OMBALTER TIME_DIMENSION 'FYR2005'
SET PROPERTIES ( DESCRIPTION, BUSINESS_NAME )
VALUES ('TimeSeries Description 2', 'TIME SERIES 2')
See Also
OMBCREATE TIME_DIMENSION, OMBDROP TIME_DIMENSION, OMBRETRIEVE TIME_DIMENSION
Purpose
To alter the definision of a transportable module.
Prerequisites
In the context of a project.
Syntax
alterTMCommand = OMBALTER TRANSPORTABLE_MODULE "QUOTED_STRING" (
"alterTMClause" { "alterTMClause" } )
alterTMClause = "renameClause" | "setPropertiesAndLocationsAndIconSet" |
"unsetReferenceIconSetClause" | "modifyClause"
renameClause = RENAME TO "QUOTED_STRING"
setPropertiesAndLocationsAndIconSet = SET ( "setPropertiesClause" |
"setSourceLocationClause" | "setTargetLocationClause" |
"setReferenceIconSetClause" )
unsetReferenceIconSetClause = UNSET ( REF | REFERENCE ) ICONSET
modifyClause = MODIFY ( "modifyTablespaceClause" | "modifyDatafileClause"
| "modifySchemaClause" )
setPropertiesClause = ( PROPERTIES "(" "propertyNameList" ")" VALUES "("
"propertyValueList" ")" )
setSourceLocationClause = SOURCE_LOCATION "QUOTED_STRING"
setTargetLocationClause = TARGET_LOCATION "QUOTED_STRING"
setReferenceIconSetClause = ( REF | REFERENCE ) ICONSET "QUOTED_STRING"
modifyTablespaceClause = TRANSPORTABLE_MODULE_TABLESPACE "QUOTED_STRING"
modifyDatafileClause = DATAFILE "QUOTED_STRING"
modifySchemaClause = TRANSPORTABLE_MODULE_SCHEMA "QUOTED_STRING"
propertyNameList = "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }
propertyValueList = "propertyValue" { "," "propertyValue" }
propertyValue = ( "QUOTED_STRING" | "INTEGER_LITERAL" |
"FLOATING_POINT_LITERAL" )
Keywords And Parameters
alterTMCommand
This command is for altering a transportable module.
QUOTED_STRING
The name of the transportable module to be altered.
alterTMClause
Multiple altering actions can be specified with one OMBALTER
TRANSPORTABLE_MODULE command.
renameClause
Change the name of the transportable module
QUOTED_STRING
The new name for the transportable module.
setPropertiesAndLocationsAndIconSet
Set properties for the transportable module, and/or specify source and
target locations, and/or specify icon set for the newly create
transportable module.
unsetReferenceIconSetClause
Remove the reference to the icon set.
modifyClause
Change contents within a transportable module.
setPropertiesClause
Set properties for the transportable module.
Basic properties for TRANSPORTABLE_MODULE:
Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: N/A
Default: NAME
Business name of the transportable module
Name: DESCRIPTION
Type: STRING(4000)
Valid Values: N/A
Default: ''
Description for the transportable module
Properties for TRANSPORTABLE_MODULE:
Name: GENERATION_COMMENTS
Type: STRING
Valid Values: N/A
Default: ''
Enter additional comments for the generated code.
Name: TARGET_OS_TYPE
Type: STRING
Valid Values: Linux, Unix, Windows
Default: Unix
The operating system type of the target machine. This is needed for
generating shell scripts in correct style required by the operating system.
Name: TRANSPORT_TABLESPACE
Type: BOOLEAN
Valid Values: true, false
Default: true
Specifies whether transportable tablespace (TTS) feature is to be used for
deploying tables in the transportable module. If set to true, tablespaces
are copied from source to target using the server TTS mechanism. If set to
false, tables are individually extracted and deployed using Oracle Data
Pump available in Oracle Database 10g or later; but tablespaces are not transported.
Since Oracle Data Pump is new in Oracle Database 10g, setting this parameter to
false is only allowed if both source and target databases are with Oracle
10g or a higher versions.
Name: WHAT_TO_DEPLOY
Type: STRING
Valid Values: ALL_OBJECTS, TABLES_ONLY
Default: ALL_OBJECTS
Specifies whether only tables in the transportable module are deployed or
everything in it is deployed.
Name: WORK_DIRECTORY
Type: STRING
Valid Values: N/A
Default: ''
The full path of work directory on target machine, where temporary files,
logs and tablespace datafiles may be stored. If left unspecified, OWB's
runtime home directory is used as the work directory. It is highly
recommended that users specify dedicated directory for transportable module
deployment.
Properties for TRANSPORTABLE_MODULE_TABLESPACE:
Name: DROP_EXISTING_TABLESPACE
Type: BOOLEAN
Valid Values: true, false
Default: false
If this parameter is set to true, existing tablespace in target is dropped
and re-created.
Name: GENERATION_COMMENTS
Type: STRING
Valid Values: N/A
Default: ''
Enter additional comments for the generated code.
Name: TARGET_TABLESPACE_NAME
Type: STRING
Valid Values: N/A
Default: DEFAULT
Tablespace name in the target database. Target tablespace name must be same
with source tablespace name prior to Oracle 10.2.
Properties for TRANSPORTABLE_MODULE_DATAFILE:
Name: DIRECTORY
Type: STRING
Valid Values: N/A
Default: ''
The directory on target machine where the datafile will be created. If left
unspecified, the target work directory is used for storing the datafile
that is transported from source machine.
Name: FILENAME
Type: STRING
Valid Values: N/A
Default: DEFAULT
The name of the new file on target. Please check to see if there is already
a file with same name in the same directory. Transportable tablespace
deployement may overwrite any existing files.
Name: REUSE
Type: BOOLEAN
Valid Values: true, false
Default: false
If this parameter is set to true, existing datafile is overwritten. If set
to false, overwriting existing file is not allowed. In this case, if an
file exists, deployment will terminate.
Note:
1. N/A means any valid character in supported character set.
2. '' represents an empty string
setSourceLocationClause
Specify the source location name.
QUOTED_STRING
The name of an already created transportable module source location.
setTargetLocationClause
Specify the target location name.
QUOTED_STRING
The name of an already created transportable module target location.
setReferenceIconSetClause
Set the icon set for the new transportable module.
QUOTED_STRING
The name of the icon set.
modifyTablespaceClause
Modify a tablespace within a transportable module.
QUOTED_STRING
The tablespace name. Note that the tablespace name is its name in the
source database.
modifyDatafileClause
Modify a datafile within a transportable module.
QUOTED_STRING
The name of the datafile. Note that the datafile name is its full path name
in the source database. The name must be exactly same with what is in
DBA_DATA_FILES view in the source database.
modifySchemaClause
Modify a schema within a transportable module.
QUOTED_STRING
The name of the schema. Note that the schema name is its name in the source
database.
propertyNameList
The list of unquoted property names.
propertyValueList
The list of property values.
propertyValue
A property value can be a single-quoted string, an integer, or a floating
point number.
Examples
OMBALTER TRANSPORTABLE_MODULE 'TM101'
SET SOURCE_LOCATION 'TM_SRC_LOC'
SET TARGET_LOCATION 'TM_TGT_LOC2'
SET PROPERTIES (WORKING_DIRECTORY, TARGET_OS_TYPE, WHAT_TO_DEPLOY,
TRANSPORT_TABLESPACE)
VALUES ('d:mydir', 'Windows', 'TABLES_ONLY', 'true')
This example changes the source and target locations and the four
properties associated with transportable module.
OMBALTER TRANSPORTABLE_MODULE 'TM101'
MODIFY TRANSPORTABLE_MODULE_TABLESPACE 'src_tablespace_1'
SET PROPERTIES (TARGET_TABLESPACE_NAME, DROP_EXISTING_TABLESPACE)
VALUES ('tgt_tablespace_1', 'true');
This example changes properties of a tablespace within the transportable
module. The properties specify the target tablespace name, and whether to
drop and re-create target tablespace if it already exists.
OMBALTER TRANSPORTABLE_MODULE 'TM101'
MODIFY DATAFILE 'D:\TTSFILES\TTS1B.DBF'
SET PROPERTIES (DIRECTORY, FILENAME, REUSE)
VALUES ('d:\tmdir', 'TM1B.DBF', 'false')
This example changes properties of a datafile within a tablespace. The
properties specify the target datafile's directory and name, plus a flag to
indicate whether to overwrite existing file. Notice that the source
datafile name is the full path of the file.
OMBALTER TRANSPORTABLE_MODULE 'TM101'
MODIFY TRANSPORTABLE_MODULE_SCHEMA 'src_schema_1'
SET PROPERTIES (SCHEMA_NAME, PASSWORD, DEFAULT_TABLESPACE,
SCHEMA_EXISTS_ACTION, SCHEMA_DOESNT_EXIST_ACTION,
TABLE_EXISTS_ACTION, COPY_SOURCE_SCHEMA, PARALLEL)
VALUES ('TM1_U', 'TM1_U', 'USERS',
'Replace', 'Create',
'Append', 'false', 2)
This example changes the properties of a schema within a transportable
module.The properties specify target schema name, password and default
tablespace, plus some important choices. In this example, target schema
will be replaced if it already exists. If it does not exist, it will be
created. If a table already exists in target, new data will be appended to
the existing table. The transportable module will not copy the entire
schema from source to target. And finally, the degree of parallelism is 2.
See Also
OMBALTER, OMBCREATE TRANSPORTABLE_MODULE, OMBRETRIEVE TRANSPORTABLE_MODULE, OMBDROP TRANSPORTABLE_MODULE
Purpose
To alter properties of a Warehouse Builder user.
Prerequisites
Must be connected to a OWB repository.
Syntax
alterUserCommand = OMBALTER ( USER "QUOTED_STRING" ( SET
"setPropertiesClause" ) )
setPropertiesClause = PROPERTIES "(" "propertyNameList" ")" VALUES "("
"propertyValueList" ")"
propertyNameList = "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }
propertyValueList = "propertyValue" { "," "propertyValue" }
propertyValue = ( "QUOTED_STRING" | "INTEGER_LITERAL" |
"FLOATING_POINT_LITERAL" )
Keywords And Parameters
alterUserCommand
This clause alters a Warehouse Builder user.
setPropertiesClause
Used to set properties of a Warehouse Builder user. Valid properties are
as shown:
Basic properties for USER:
Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: N/A
Default: ''
Business name of the User
Name: DESCRIPTION
Type: STRING(4000)
Valid Values: N/A
Default: ''
Description of the User
Name: ISTARGETSCHEMA
Type: BOOLEAN
Valid Values: true, false
Default: false
If true, the user will be set up as target schema for deployment; and also
the property TARGETSCHEMAPWD must be provided when you are setting the
ISTARGETSCHEMA as true.
Name: TARGETSCHEMAPWD
Type: STRING(30)
Valid Values: N/A
Default: N/A
This properties will be provided only when you are seting ISTARGETSCHEMA as
true, so that the necessary target schema objects can be installed into the
potential target schema. And this property cannot be retrieved due to
security consideration.
User preferences:
Name: LOCALE
Type: STRING
Valid Values: Albanian, Arabic, Bulgarian, Byelorussian, Catalan, Chinese,
Croatian, Czech, Danish, Dutch, English, Estonian, French, German, Greek,
Hebrew, Hungarian, Icelandic, Italian, Japanese, Korean, Lithuanian,
Macedonian, Norwegian, Polish, Portuguese, Romanian, Russian, Serbian,
Serbo_Croation, Slovak, Slovenian, Spanish, Swedish, Thai, Turkish,
Ukranian
Default: ''
Name: SHOW_PROJECT
Type: BOOLEAN
Valid Values: true, false
Default: true
Name: SHOW_MODULE
Type: BOOLEAN
Valid Values: true, false
Default: true
Name: SHOW_LOCATION
Type: BOOLEAN
Valid Values: true, false
Default: true
Name: SHOW_ACTION
Type: BOOLEAN
Valid Values: true, false
Default: true
Name: SHOW_TYPE
Type: BOOLEAN
Valid Values: true, false
Default: true
Name: DEFAULT_PROFILE_LOCATION
Type: STRING
Valid Values: N/A
Default: ''
Name: ALLOW_UNDO_REDO
Type: BOOLEAN
Valid Values: true, false
Default: false
Name: PAUSE_AFTER_COMPILE
Type: BOOLEAN
Valid Values: true, false
Default: false
Name: PROMPT_FOR_COMMIT
Type: BOOLEAN
Valid Values: true, false
Default: false
Name: PROMPT_FOR_JOB_NAME
Type: BOOLEAN
Valid Values: true, false
Default: false
Name: PROMPT_FOR_EXECUTION_PARAMS
Type: BOOLEAN
Valid Values: true, false
Default: false
Name: SHOW_MONITOR
Type: BOOLEAN
Valid Values: true, false
Default: false
Name: SHOW_DEPLOYMENT_COMPLETION
Type: BOOLEAN
Valid Values: true, false
Default: false
Name: SHOW_DEPLOYMENT_DEPENDENCIES
Type: BOOLEAN
Valid Values: true, false
Default: false
Name: SHOW_MONITOR_RESULTS
Type: BOOLEAN
Valid Values: true, false
Default: true
Name: SHOW_MONITOR_LOGFILE
Type: BOOLEAN
Valid Values: true, false
Default: true
Name: PERSONALITY
Type: STRING
Valid Values: N/A
Default: Default
Name: SHOW_GUIDED_ASSISTANCE
Type: BOOLEAN
Valid Values: true, false
Default: false
Name: HIDE_WIZARD_WELCOME_PAGES
Type: BOOLEAN
Valid Values: true, false
Default: false
Name: SHOW_DELETE_CONFIRMATION
Type: BOOLEAN
Valid Values: true, false
Default: true
Name: RECYCLE_DELETED_OBJECTS
Type: BOOLEAN
Valid Values: true, false
Default: true
Name: EMPTY_RECYCLE_BIN
Type: BOOLEAN
Valid Values: true, false
Default: false
Name: CLEAR_CLIPBOARD
Type: BOOLEAN
Valid Values: true, false
Default: true
Name: SHOW_GENERATION_PROJECT
Type: BOOLEAN
Valid Values: true, false
Default: true
Name: SHOW_GENERATION_MODULE
Type: BOOLEAN
Valid Values: true, false
Default: true
Name: SHOW_GENERATION_LOCATION
Type: BOOLEAN
Valid Values: true, false
Default: false
Name: SHOW_GENERATION_ACTION
Type: BOOLEAN
Valid Values: true, false
Default: false
Name: SHOW_GENERATION_TYPE
Type: BOOLEAN
Valid Values: true, false
Default: true
Name: LOG_FILE_PATH
Type: STRING(1000)
Valid Values: N/A
Default: ''
Name: LOG_FILE_NAME
Type: STRING(1000)
Valid Values: N/A
Default: log
Name: LOG_FILE_MAX_SIZE
Type: STRING
Valid Values: 1-10000000
Default: 100
Name: LOG_ERROR_MESSAGES
Type: BOOLEAN
Valid Values: true, false
Default: true
Name: LOG_WARNING_MESSAGES
Type: BOOLEAN
Valid Values: true, false
Default: true
Name: LOG_INFORMATION_MESSAGES
Type: BOOLEAN
Valid Values: true, false
Default: false
Name: NAMING_MODE
Type: STRING
Valid Values: PHYSICAL_NAMING_MODE, BUSINESS_NAMING_MODE
Default: PHYSICAL_NAMING_MODE
Name: PROPAGATE_NAME_CHANGES
Type: BOOLEAN
Valid Values: true, false
Default: false
Name: DESIGNREPOS_PWD_PERSIST
Type: BOOLEAN
Valid Values: true, false
Default: true
Name: RUNTIMEREPOS_PWD_SHARE
Type: BOOLEAN
Valid Values: true, false
Default: true
Name: DEFAULT_SEC_POLICY
Type: STRING
Valid Values: MINIMUM_SECURITY, MAXIMUM_SECURITY
Default: MINIMUM_SECURITY
Examples
OMBALTER USER 'USER1' SET PROPERTIES(BUSINESS_NAME, DESCRIPTION,
ISTARGETSCHEMA,TARGETSCHEMA) VALUES('developer user1 changed', 'one user
from developer group', 'true','dbPwdOfUser1')
See Also
OMBREGISTER USER, OMBUNREGISTER USER, OMBRETRIEVE USER
Purpose
Alter the Varying Array by resetting its properties.
Prerequisites
Should be in the context of an Oracle Module.
Syntax
alterVaryingArrayCommand = OMBALTER ( VARYING_ARRAY "QUOTED_STRING" (
"renameClause" [ "alterPropertiesOrIconSetClause" ] |
"alterPropertiesOrIconSetClause" ) )
renameClause = RENAME TO "QUOTED_STRING"
alterPropertiesOrIconSetClause = SET ( "setPropertiesClause" [ SET ( REF |
REFERENCE ) "setReferenceIconSetClause" | UNSET ( REF | REFERENCE )
"unsetReferenceIconSetClause" ] | ( REF | REFERENCE )
"setReferenceIconSetClause" ) | UNSET ( REF | REFERENCE )
"unsetReferenceIconSetClause"
setPropertiesClause = PROPERTIES "(" "propertyNameList" ")" VALUES "("
"propertyValueList" ")"
setReferenceIconSetClause = ICONSET "QUOTED_STRING"
unsetReferenceIconSetClause = ICONSET
propertyNameList = "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }
propertyValueList = "propertyValue" { "," "propertyValue" }
propertyValue = ( "QUOTED_STRING" | "INTEGER_LITERAL" |
"FLOATING_POINT_LITERAL" )
Keywords And Parameters
renameClause
renames a table with a different name.
setPropertiesClause
Basic properties for VARYING_ARRAY:
Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: N/A
Default: ''
Business name of the Varying Array
Name: DESCRIPTION
Type: STRING(4000)
Valid Values: N/A
Default: ''
Description of the Varying Array
Name: DATATYPE
Type: STRING(20)
Valid Values: BINARY_DOUBLE, BINARY_FLOAT, BLOB, CHAR, CLOB, DATE, FLOAT,
INTEGER, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, NCHAR, NCLOB,
NUMBER, NVARCHAR2, RAW, SYS.ANYDATA, SYS.LCR$_ROW_RECORD, SYS.XMLFORMAT,
TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE, TIMESTAMP WITH TIME ZONE,
VARHCAR, VARCHAR2, XMLTYPE
Default: ''
Datatype of the Base Element of the Varying Array
Properties for VARYING_ARRAY:
Name: DEPLOYABLE
Type: BOOLEAN
Valid Values: true, false
Default: true
Warehouse Builder generates a set of scripts to create an object only for
those object marked as Deployable = true
Name: GENERATION_COMMENTS
Type: STRING
Valid Values: N/A
Default: ''
Enter additional comments for the generated code.
Note:
1. N/A means any valid character in supported character set.
2. '' represents an empty string
propertyNameList
The list of properties.
propertyValueList
The list of property values.
propertyValue
This clause adds the property values.
Examples
OMBALTER VARYING_ARRAY 'SOME_VARRAY' SET PROPERTIES
(DESCRIPTION,ARRAY_LENGTH) VALUES ('This is a new description.',10)
This will set its description to "This is a new description." and its
array length to 10.
See Also
OMBALTER, OMBCREATE VARYING_ARRAY, OMBDROP VARYING_ARRAY
Purpose
To alter properties and definition of a view.
Prerequisites
In the context of an Oracle Module.
Syntax
alterViewCommand = OMBALTER ( VIEW "QUOTED_STRING" ( "renameClause" [
"alterPropertiesOrIconSetClause" ] [ "alterViewSCOandDependentClauses"
] | "alterPropertiesOrIconSetClause" [
"alterViewSCOandDependentClauses" ] |
"alterViewSCOandDependentClauses" ) )
renameClause = RENAME TO "QUOTED_STRING"
alterPropertiesOrIconSetClause = SET ( "setPropertiesClause" [ SET ( REF |
REFERENCE ) "setReferenceIconSetClause" | UNSET ( REF | REFERENCE )
"unsetReferenceIconSetClause" ] | ( REF | REFERENCE )
"setReferenceIconSetClause" ) | UNSET ( REF | REFERENCE )
"unsetReferenceIconSetClause"
alterViewSCOandDependentClauses = ADD ( "addColumnClauseForAlter" [
"alterViewSCOandDependentClauses" ] | "addViewConstraintClause" {
"alterViewConstraintClauses" } | "addDataRuleUsageClause" {
"alterDataRuleUsageClauses" } | "addRelationalDependentClause" [
"alterViewSCOandDependentClauses" ] ) | MODIFY ( "modifyColumnClause"
[ "alterViewSCOandDependentClauses" ] | "modifyViewConstraintClause" {
"alterViewConstraintClauses" } | "modifyDataRuleUsageClause" {
"alterDataRuleUsageClauses" } ) | DELETE ( "deleteColumnClause" [
"alterViewSCOandDependentClauses" ] | "deleteViewConstraintClause" {
"alterViewConstraintClauses" } | "deleteDataRuleUsageClause" {
"alterDataRuleUsageClauses" } | "deleteRelationalDependentClause" [
"alterViewSCOandDependentClauses" ] )
setPropertiesClause = PROPERTIES "(" "propertyNameList" ")" VALUES "("
"propertyValueList" ")"
setReferenceIconSetClause = ICONSET "QUOTED_STRING"
unsetReferenceIconSetClause = ICONSET
addColumnClauseForAlter = COLUMN "QUOTED_STRING" [ AT POSITION
"INTEGER_LITERAL" ] [ SET "setPropertiesClause" ]
addViewConstraintClause = "addUkPkClause" | "addFkClause"
alterViewConstraintClauses = ADD "addViewConstraintClause" | MODIFY
"modifyViewConstraintClause" | DELETE "deleteViewConstraintClause"
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" ]
alterDataRuleUsageClauses = ADD "addDataRuleUsageClause" | MODIFY
"modifyDataRuleUsageClause" | DELETE "deleteDataRuleUsageClause"
addRelationalDependentClause = ( REFERENCE | REF ) ( TABLE | VIEW |
MATERIALIZED_VIEW ) "QUOTED_STRING"
modifyColumnClause = COLUMN "QUOTED_STRING" ( "renameClause" [
"moveToClause" ] [ SET "setPropertiesClause" ] | "moveToClause" [ SET
"setPropertiesClause" ] | SET "setPropertiesClause" )
modifyViewConstraintClause = "modifyUkPkClause" | "modifyFkClause"
modifyDataRuleUsageClause = DATA_RULE_USAGE "QUOTED_STRING" (
"renameClause" [ SET "setPropertiesClause" ] | SET
"setPropertiesClause" )
deleteColumnClause = COLUMN "QUOTED_STRING"
deleteViewConstraintClause = UNIQUE_KEY "QUOTED_STRING" | PRIMARY_KEY
"QUOTED_STRING" | FOREIGN_KEY "QUOTED_STRING"
deleteDataRuleUsageClause = DATA_RULE_USAGE "QUOTED_STRING"
deleteRelationalDependentClause = ( REFERENCE | REF ) ( TABLE | VIEW |
MATERIALIZED_VIEW ) "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" ]
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" )
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"
constraintColumnReferencesClause = COLUMNS "(" "quotedNameList" ")"
setFkReferencesClauses = "constraintColumnReferencesClause" [ SET ( REF |
REFERENCE ) "constraintUkReferencesClause" ] |
"constraintUkReferencesClause" [ SET ( REF | REFERENCE )
"constraintColumnReferencesClause" ]
quotedNameList = "QUOTED_STRING" { "," "QUOTED_STRING" }
constraintUkReferencesClause = ( UNIQUE_KEY | PRIMARY_KEY )
"QUOTED_STRING" [ OF ( TABLE | VIEW ) "QUOTED_STRING" ]
Keywords And Parameters
alterViewCommand
This clause alters a view.
QUOTED_STRING
name of the view.
renameClause
renames a table with a different name.
alterViewSCOandDependentClauses
This clause alters the view clause.
setPropertiesClause
Used to set properties (core, logical, physical, user-defined) for views
and their columns, unique keys, foreign keys, and primary keys.
Note:
Constraints can be specified but will not be generated for either View or
Materialized View in this release.
Basic properties for VIEW, MATERIALIZED_VIEW:
Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: N/A
Default: ''
Business name of the View, MaterializedView
Name: DESCRIPTION
Type: STRING(4000)
Valid Values: N/A
Default: ''
Description of the View, MaterializedView
Name: VIEW_QUERY
Type: STRING(4000)
Valid Values: N/A
Default: ''
Sets the query definition in View and MaterializedView.
Properties for VIEW:
Name: DEPLOYABLE
Type: BOOLEAN
Valid Values: true, false
Default: true
Warehouse Builder generates a set of scripts to create an object only for
those object marked as Deployable = true
Name: GENERATION_COMMENTS
Type: STRING
Valid Values: N/A
Default: ''
Enter additional comments for the generated code.
Name: SHADOW_TABLESPACE
Type: STRING(30)
Valid Values: N/A
Default: ''
Use the Tablespace parameter to specify the name of tablespace.
Name: SHADOW_TABLE_NAME
Type: STRING(30)
Valid Values: N/A
Default: ''
Use the shadow table name to specify the name of Shadow Table.
Properties for UNIQUE_KEY:
Name: DEFERRABLE
Type: STRING
Valid Values: , DEFERRABLE, NOT DEFERRABLE
Default: ''
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.
Name: DEPLOYABLE
Type: BOOLEAN
Valid Values: true, false
Default: true
Warehouse Builder generates a set of scripts to create an object only for
those object marked as Deployable = true
Name: ENABLECONSTRAINT
Type: STRING
Valid Values: , DISABLE, ENABLE
Default: ''
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.
Name: EXCEPTIONSINTO
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: INDEX_TABLESPACE
Type: STRING(30)
Valid Values: N/A
Default: ''
oracle.owb.scripting.help.OMBHelpGenerator(557): NLS Lookup error for key
"9i.TABLE.CONSTRAINT.INDEXTABLESPACE:DESCRIPTION"
Name: INITIALLY
Type: STRING
Valid Values: , DEFERRED, IMMEDIATE
Default: ''
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.
Name: RELY
Type: STRING
Valid Values: , NORELY, RELY
Default: ''
Specify RELY to activate an existing constraint in NOVALIDATE mode for
query rewrite in an unenforced query rewrite integrity mode. The default is
NORELY.
Name: SUBSTITUTE_KEY
Type: BOOLEAN
Valid Values: true, false
Default: 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.
Name: USING_INDEX
Type: BOOLEAN
Valid Values: true, false
Default: false
oracle.owb.scripting.help.OMBHelpGenerator(557): NLS Lookup error for key
"9i.TABLE.CONSTRAINT.USINGINDEX:DESCRIPTION"
Name: VALIDATECONSTRAINT
Type: STRING
Valid Values: , NOVALIDATE, VALIDATE
Default: ''
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.
Properties for FOREIGN_KEY:
Name: DEFERRABLE
Type: STRING
Valid Values: , DEFERRABLE, NOT DEFERRABLE
Default: ''
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.
Name: DEPLOYABLE
Type: BOOLEAN
Valid Values: true, false
Default: true
Warehouse Builder generates a set of scripts to create an object only for
those object marked as Deployable = true
Name: ENABLECONSTRAINT
Type: STRING
Valid Values: , DISABLE, ENABLE
Default: ''
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.
Name: EXCEPTIONSINTO
Type: STRING
Valid Values: N/A
Default: ''
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.
Name: INITIALLY
Type: STRING
Valid Values: , DEFERRED, IMMEDIATE
Default: ''
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.
Name: ONDELETE
Type: STRING
Valid Values: , CASCADE, SET NULL
Default: ''
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.
Name: RELY
Type: STRING
Valid Values: , NORELY, RELY
Default: ''
Specify RELY to activate an existing constraint in NOVALIDATE mode for
query rewrite in an unenforced query rewrite integrity mode. The default is
NORELY.
Name: SUBSTITUTE_KEY
Type: BOOLEAN
Valid Values: true, false
Default: 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.
Name: VALIDATECONSTRAINT
Type: STRING
Valid Values: , NOVALIDATE, VALIDATE
Default: ''
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.
Note:
1. N/A means any valid character in supported character set.
2. '' represents an empty string
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.
addViewConstraintClause
This clause adds the view's configuration clause.
alterViewConstraintClauses
This clause alters the view's constraint clause.
addDataRuleUsageClause
Add a data rule usage to the relation.
alterDataRuleUsageClauses
Add, modify, or delete data rule usages.
addRelationalDependentClause
This clause adds referential dependencies to other relational objects.
modifyColumnClause
This clause renames, set properties, and move columns.
modifyViewConstraintClause
This clause modifies the view's constraint clause.
modifyDataRuleUsageClause
Rename or modify the properties of a data rule usage.
deleteColumnClause
This clause deletes a column.
deleteViewConstraintClause
This clause deletes the view's constraint.
deleteDataRuleUsageClause
Delete a data rule usage.
deleteRelationalDependentClause
This clause deletes referential dependencies to other relational objects.
propertyNameList
The list of properties.
propertyValueList
The list of property values.
addUkPkClause
This clause adds the adds unique key and primary keys.
QUOTED_STRING
name of the unique key or primary key.
addFkClause
This clause adds foreign key.
QUOTED_STRING
Name of the foreign key.
moveToClause
This clause will move the column to given position.
modifyUkPkClause
It modifies unique or primary key.
modifyFkClause
This clause modifies the foreign key.
propertyValue
This clause adds the property values.
setUkPkPropertiesAndReferencesColumnsClauses
This clause adds properties and references to columns.
setFkSubClauses
This clause set references to a foreign key.
constraintColumnReferencesClause
This clause provides names of all columns.
setFkReferencesClauses
This clause sets foreign key references.
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.
Examples
OMBALTER VIEW 'NEW_VIEW' SET PROPERTIES (DESCRIPTION, BUSINESS_NAME) VALUES
('this is an altered desc of new view', 'Altered New View')
This will alter a view named "NEW_VIEW", its description is "this is an
altered desc of new view", and business name is "Altered New View".
See Also
OMBALTER, OMBCREATE VIEW, OMBDROP VIEW, OMBRETRIEVE VIEW
|
 Copyright © 2000, 2006, Oracle. All rights reserved. Legal Notices |
|