Skip Headers
Oracle® Warehouse Builder OMB*Plus Command Reference
11g Release 2 (11.2)

Part Number E14406-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
Mobi · ePub

OMBCREATE MATERIALIZED_VIEW

Purpose

To create a materialized view.

Prerequisites

In the context of an Oracle Module.

Syntax

createMaterializedViewCommand =  OMBCREATE ( MATERIALIZED_VIEW 
          "QUOTED_STRING" [ SET ( "setPropertiesClause" [ SET ( REF | REFERENCE 
          ) "setReferenceIconSetClause" ] | ( REF | REFERENCE ) 
          "setReferenceIconSetClause" ) ] [ 
          "addMaterializedViewSCOandDependentClause" ] )
     setPropertiesClause =  PROPERTIES "(" "propertyNameList" ")" VALUES "(" 
          "propertyValueList" ")"
     setReferenceIconSetClause =  ICONSET "QUOTED_STRING"
     addMaterializedViewSCOandDependentClause =  ADD ( "addColumnClause" | 
          "addViewConstraintClause" | "addSCOClause" | 
          "addRelationalDependentClause" ) [ 
          "addMaterializedViewSCOandDependentClause" ]
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }
     propertyValueList =  "propertyValue" { "," "propertyValue" }
     addColumnClause =  COLUMN "QUOTED_STRING" [ AT POSITION "INTEGER_LITERAL" ]
           [ SET "setPropertiesClause" ]
     addViewConstraintClause =  "addUkPkClause" | "addFkClause"
     addSCOClause =  "addIndexClause" | "addIndexPartitionClause" | 
          "addIndexPartitionKeyClause" | "addPartitionClause" | 
          "addPartitionKeyClause" | "addSubpartitionClause" | 
          "addaddMaterializedViewSCOandDependentClauseClause" | 
          "addSubPartitionKeyClause" | "addIndexColumnClause" | 
          "addAttributeSetClause"
     addRelationalDependentClause =  ( REFERENCE | REF ) ( TABLE | VIEW | 
          MATERIALIZED_VIEW ) "QUOTED_STRING"
     propertyValue =  ( "QUOTED_STRING" | "INTEGER_LITERAL" | 
          "FLOATING_POINT_LITERAL" )
     addUkPkClause =  ( UNIQUE_KEY | PRIMARY_KEY ) "QUOTED_STRING" [ SET 
          "setUkPkPropertiesAndReferencesColumnsClauses" ]
     addFkClause =  FOREIGN_KEY "QUOTED_STRING" [ SET "setFkSubClauses" ]
     addIndexClause =  INDEX "QUOTED_STRING" [ SET 
          "setSCOConfigurationPropertiesClauses" ]
     addIndexPartitionClause =  INDEX_PARTITION "QUOTED_STRING" OF INDEX 
          "QUOTED_STRING" [ AT POSITION "INTEGER_LITERAL" ] ( 
          "renameSCOConfigurationClause" [ SET 
          "setSCOConfigurationPropertiesClauses" ] | [ SET 
          "setSCOConfigurationPropertiesClauses" ] )
     addIndexPartitionKeyClause =  INDEX_PARTITION_KEY "QUOTED_STRING" OF INDEX 
          "QUOTED_STRING" [ SET "setSCOConfigurationPropertiesClauses" ]
     addPartitionClause =  PARTITION "QUOTED_STRING" [ AT POSITION 
          "INTEGER_LITERAL" ] [ SET "setSCOConfigurationPropertiesClauses" ]
     addPartitionKeyClause =  PARTITION_KEY "QUOTED_STRING" [ SET 
          "setSCOConfigurationPropertiesClauses" ]
     addSubpartitionClause =  SUBPARTITION "QUOTED_STRING" OF PARTITION 
          "QUOTED_STRING" [ AT POSITION "INTEGER_LITERAL" ] [ SET 
          "setSCOConfigurationPropertiesClauses" ]
     addaddMaterializedViewSCOandDependentClauseClause =  TEMPLATE_SUBPARTITION 
          "QUOTED_STRING" [ AT POSITION "INTEGER_LITERAL" ] [ SET 
          "setSCOConfigurationPropertiesClauses" ]
     addSubPartitionKeyClause =  SUBPARTITION_KEY "QUOTED_STRING" [ SET 
          "setSCOConfigurationPropertiesClauses" ]
     addIndexColumnClause =  INDEX_COLUMN "QUOTED_STRING" OF INDEX 
          "QUOTED_STRING" [ SET "setSCOConfigurationPropertiesClauses" ]
     addAttributeSetClause =  ATTRIBUTE_SET "QUOTED_STRING"
     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"
     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" ]

Parameters

createMaterializedViewCommand

This command creates a materialized view.

QUOTED_STRING

Specify the name of the materialized view to be created.

setPropertiesClause

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

getPropertiesClause

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: Any valid character string in supported character set.
Default: Empty string
Business name of the View, MaterializedView 

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

Name: VIEW_QUERY
Type: STRING(4000)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Sets the query definition in View and MaterializedView.

getPropertiesClause

Basic properties for COLUMN: 

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

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

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

Default: NUMBER
The datatype of a column

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

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

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

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

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

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

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

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

getPropertiesClause

Basic properties for PARTITION_KEY: 

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

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

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

Basic properties for PARTITION: 

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

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

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

getPropertiesClause

Basic properties for SUBPARTITION_KEY: 

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

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

Basic properties for SUBPARTITION: 

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

Basic properties for TEMPLATE_SUBPARTITION: 

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

getPropertiesClause

Basic properties for INDEX: 

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

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

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

Basic properties for INDEX_PARTITION_KEY: 

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

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

Basic properties for INDEX_PARTITION: 

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

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

getPropertiesClause

Basic properties for CHECK_CONSTRAINTS: 

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

propertyNameList

The list of properties.

propertyValueList

The list of property values.

addColumnClause

This clause adds a column.

When you create a table or alter a table to add a set of columns, the

position that you specify for a column must be either less than or equal to

the number of columns that you have listed up to that point in the command.

For example, the following OMBCREATE command does not add the specified

columns to the table:

OMBCREATE TABLE 'MY_TABLE' \

SET PROPERTIES (DESCRIPTION) VALUES ('TRIAL TABLE') \

ADD COLUMN 'C1' \

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

ADD COLUMN 'C2' AT POSITION 3 \

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

ADD COLUMN 'C3' AT POSITION 2 \

SET PROPERTIES (DATATYPE) VALUES ('VARCHAR2')

This is because at the point when you specify the position of the column

C2 as 3, you have added just two columns to the table.But the following

OMBALTER command adds the specified columns to the table. This is because

at the point when you specify the position of the column C2 as 2, you are

adding the second column to the table.

OMBCREATE TABLE 'MY_TABLE' \

SET PROPERTIES (DESCRIPTION) VALUES ('TRIAL TABLE') \

ADD COLUMN 'C1' \

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

ADD COLUMN 'C2' AT POSITION 2 \

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

ADD COLUMN 'C3' AT POSITION 1 \

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

In the above example, the order in which the columns are added are as

follows:

C1

C1, C2

C3, C1, C2

addViewConstraintClause

This clause adds the view's configuration clause.

addSCOClause

This clause will add SCOs.

addRelationalDependentClause

This clause adds referential dependencies to other relational objects.

propertyValue

This clause adds the property values.

addUkPkClause

This clause adds a unique key and primary key.

QUOTED_STRING

name of the unique key or primary key.

addFkClause

This clause adds foreign key.

QUOTED_STRING

Name of the foreign key.

addIndexClause

This clause adds an index.

QUOTED_STRING

Name of the index.

addPartitionClause

This clause adds a partition.

QUOTED_STRING

Name of the partition.

addPartitionKeyClause

This clause adds a partition key.

QUOTED_STRING

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

addIndexColumnClause

This clause will add index column to a specified index.

QUOTED_STRING

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

addAttributeSetClause

This clause adds an attribute set.

QUOTED_STRING

name of the attribute set.

setUkPkPropertiesAndReferencesColumnsClauses

This clause adds properties and references to columns.

setFkSubClauses

This clause sets references to a foreign key.

setSCOConfigurationPropertiesClauses

Set the configuration properties for the following:

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

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

renameSCOConfigurationClause

This clause renames configuration objects.

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.

MATERIALIZED_VIEW Object

Table 8-51 MATERIALIZED_VIEW Object

Property Type Choices Min Max Default Description

BASE_TABLES

STRING

none

none

none

empty string

Description not available.

BUFFER_POOL

STRING

, DEFAULT, KEEP, RECYCLE

none

none

empty string

 

BUILD

STRING

, DEFERRED, IMMEDIATE, PREBUILT

none

none

empty string

Description not available.

CACHE_MODE

STRING

, CACHE, NOCACHE

none

none

empty string

Description not available.

CONSTRAINTS

STRING

, ENFORCED, TRUSTED

none

none

empty string

Description not available.

DATA_SEGMENT_COMPRESSION

STRING

, COMPRESS, COMPRESS ALL, NOCOMPRESS

none

none

empty string

Description not available.

DEFAULTINDEXBUFFERPOOL

STRING

, DEFAULT, KEEP, RECYCLE

none

none

empty string

 

DEFAULTINDEXFREELISTGROUPS

STRING

none

none

none

empty string

 

DEFAULTINDEXFREELISTS

STRING

none

none

none

empty string

 

DEFAULTINDEXINITIAL

STRING

none

none

none

empty string

 

DEFAULTINDEXINITRANS

STRING

none

none

none

empty string

 

DEFAULTINDEXMAXEXTENTS

STRING

none

none

none

empty string

 

DEFAULTINDEXMAXTRANS

STRING

none

none

none

empty string

 

DEFAULTINDEXMINEXTENTS

STRING

none

none

none

empty string

 

DEFAULTINDEXNEXT

STRING

none

none

none

empty string

 

DEFAULTINDEXPCTINCREASE

STRING

none

none

none

empty string

 

DEFAULT_INDEX_TABLESPACE

STRING(30)

none

none

none

empty string

 

DEPLOYABLE

BOOLEAN

true, false

none

none

true

Description not available.

ERROR_TABLESPACE

STRING(30)

none

none

none

empty string

 

ERROR_TABLE_NAME

STRING(30)

none

none

none

empty string

 

FOR_UPDATE

STRING

, NO, YES

none

none

empty string

 

FREELISTGROUPS

STRING

none

none

none

empty string

 

FREELISTS

STRING

none

none

none

empty string

 

GENERATE_ERROR_TABLE_ONLY

BOOLEAN

true, false

none

none

false

Create, drop, replace or upgrade only the error table.

GENERATE_MV_LOG

STRING

, NO, YES

none

none

empty string

 

GENERATION_COMMENTS

STRING

none

none

none

empty string

Enter additional comments for the generated code.

HASH_PARTITION_TABLESPACE_LIST

STRING

none

none

none

empty string

Description not available.

INITIAL

STRING

none

none

none

empty string

 

INITRANS

STRING

none

none

none

empty string

 

LOGGING_MODE

STRING

, LOGGING, NOLOGGING

none

none

empty string

Description not available.

MAXEXTENTS

STRING

none

none

none

empty string

 

MAXTRANS

STRING

none

none

none

empty string

 

MINEXTENTS

STRING

none

none

none

empty string

 

MV_LOG_NEW_VALUES

STRING

, EXCLUDING, INCLUDING

none

none

empty string

 

MV_LOG_PRIMARY_KEY

STRING

, PRIMARY KEY

none

none

empty string

 

MV_LOG_ROWID

STRING

, ROWID

none

none

empty string

 

MV_LOG_SEQUENCE

STRING

, SEQUENCE

none

none

empty string

 

NEXT

STRING

none

none

none

empty string

Description not available.

NEXTDATE

STRING

none

none

none

empty string

 

PARALLEL_ACCESS_MODE

STRING

, NOPARALLEL, PARALLEL

none

none

empty string

Description not available.

PARALLEL_DEGREE

STRING

none

none

none

empty string

 

PCTFREE

STRING

none

none

none

empty string

 

PCTINCREASE

STRING

none

none

none

empty string

 

PCTUSED

STRING

none

none

none

empty string

 

QUERY_REWRITE

STRING

, DISABLE, ENABLE

none

none

empty string

 

REDUCED_PRECISION

STRING

, NO, YES

none

none

empty string

Description not available.

REFRESH

STRING

, COMPLETE, FAST, FORCE, NEVER

none

none

empty string

Description not available.

REFRESH_ON

STRING

, COMMIT, DEMAND

none

none

empty string

Description not available.

ROLLBACK

STRING

, DEFAULT, DEFAULT LOCAL, DEFAULT MASTER, NONE

none

none

DEFAULT LOCAL

 

ROLLBACKSEGMENTLOCAL

STRING

none

none

none

empty string

Description not available.

ROLLBACKSEGMENTMASTER

STRING

none

none

none

empty string

Description not available.

STARTWITH

STRING

none

none

none

empty string

Description not available.

TABLESPACE

STRING(30)

none

none

none

empty string

 

USING_INDEX_MODE

STRING

, USING_INDEX, USING_NO_INDEX

none

none

empty string

Description not available.

WITH_KEY

STRING

, PRIMARY_KEY, ROWID

none

none

empty string

Description not available.


COLUMN Object

Table 8-52 COLUMN Object

Property Type Choices Min Max Default Description

META_IMPORT_BYTES_PER_CHAR

NUMBER

none

none

none

1

 

META_IMPORT_CHARSET

STRING(45)

none

none

none

empty string

 

META_IMPORT_USE_CHAR_SEMANTICS

BOOLEAN

true, false

none

none

false

 

UNIQUE_KEY Object

Table 8-53 UNIQUE_KEY Object

Property Type Choices Min Max Default Description

DB_LOCATION

STRING

none

none

none

empty string

Location for referenced database objects.

DEFERRABLE

STRING

, DEFERRABLE, NOT DEFERRABLE

none

none

empty string

Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The default is NOT DEFERRABLE.

DEPLOYABLE

BOOLEAN

true, false

none

none

true

Warehouse Builder generates a set of scripts to create an object only for those object marked as Deployable = true

ENABLECONSTRAINT

STRING

, DISABLE, ENABLE

none

none

empty string

Specify ENABLE if you want the constraint to be applied to the data in the table. Specify DISABLE to disable the integrity constraint. The default is ENABLE.

EXCEPTIONSINTO

STRING

none

none

none

empty string

Specify an exceptions table ([schema.]table). The EXCEPTIONS table or the table you specify must exist on your local database. If you create your own exceptions table, then it must follow the format prescribed by one of the two scripts supplied by Oracle. Do not use this property with NOVALIDATE option.

INDEX_TABLESPACE

STRING(30)

none

none

none

empty string

Specify index tablespace to be used for a constraint if created as an index.

INITIALLY

STRING

, DEFERRED, IMMEDIATE

none

none

empty string

Specify (INITIALLY) IMMEDIATE to indicate that Oracle should check a DEFERRABLE constraint at the end of each subsequent SQL statement. Specify (INITIALLY) DEFERRED to indicate that Oracle should check a DEFERRABLE constraint at the end of subsequent transactions. The default is (INITIALLY) IMMEDIATE.

RELY

STRING

, NORELY, RELY

none

none

empty string

Specify RELY to activate an existing constraint in NOVALIDATE mode for query rewrite in an unenforced query rewrite integrity mode. The default is NORELY.

SUBSTITUTE_KEY

BOOLEAN

true, false

none

none

false

This is related to Streams Support. If this is true, deployment will result only in creation of the key metadata. The constraint itself will not be enforced. This will be done by creating a Streams substitute key.

USING_INDEX

BOOLEAN

true, false

none

none

false

Specify True to create a constraint as an index.

VALIDATECONSTRAINT

STRING

, NOVALIDATE, VALIDATE

none

none

empty string

The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default. (ENABLE) VALIDATE specifies that all old and new data must compliy with the constraint. (ENABLE) NOVALIDATE only ensures that all new DML operations on the constrained data comply with the constraint. (DISABLE) VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. (DISABLE) NOVALIDATE signifies that Oracle makes no effort to maintain the constraint (because it is disabled) and cannot guarantee that the constraint is true (because it is not being validated). The default is NOVALIDATE.


FOREIGN_KEY Object

Table 8-54 FOREIGN_KEY Object

Property Type Choices Min Max Default Description

DB_LOCATION

STRING

none

none

none

empty string

Location for referenced database objects.

DEFERRABLE

STRING

, DEFERRABLE, NOT DEFERRABLE

none

none

empty string

Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The default is NOT DEFERRABLE.

DEPLOYABLE

BOOLEAN

true, false

none

none

true

Warehouse Builder generates a set of scripts to create an object only for those object marked as Deployable = true

ENABLECONSTRAINT

STRING

, DISABLE, ENABLE

none

none

empty string

Specify ENABLE if you want the constraint to be applied to the data in the table. Specify DISABLE to disable the integrity constraint. The default is ENABLE.

EXCEPTIONSINTO

STRING

none

none

none

empty string

Specify an exceptions table ([schema.]table). The EXCEPTIONS table or the table you specify must exist on your local database. If you create your own exceptions table, then it must follow the format prescribed by one of the two scripts supplied by Oracle. Do not use this property with NOVALIDATE option.

INITIALLY

STRING

, DEFERRED, IMMEDIATE

none

none

empty string

Specify (INITIALLY) IMMEDIATE to indicate that Oracle should check a DEFERRABLE constraint at the end of each subsequent SQL statement. Specify (INITIALLY) DEFERRED to indicate that Oracle should check a DEFERRABLE constraint at the end of subsequent transactions. The default is (INITIALLY) IMMEDIATE.

ONDELETE

STRING

, CASCADE, SET NULL

none

none

empty string

Specify CASCADE if you want Oracle to remove dependent foreign key values. Specify SET NULL if you want Oracle to convert dependent foreign key values to NULL.

RELY

STRING

, NORELY, RELY

none

none

empty string

Specify RELY to activate an existing constraint in NOVALIDATE mode for query rewrite in an unenforced query rewrite integrity mode. The default is NORELY.

SUBSTITUTE_KEY

BOOLEAN

true, false

none

none

false

This is related to Streams Support. If this is true, deployment will result only in creation of the key metadata. The constraint itself will not be enforced. This will be done by creating a Streams substitute key.

VALIDATECONSTRAINT

STRING

, NOVALIDATE, VALIDATE

none

none

empty string

The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default. (ENABLE) VALIDATE specifies that all old and new data must compliy with the constraint. (ENABLE) NOVALIDATE only ensures that all new DML operations on the constrained data comply with the constraint. (DISABLE) VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. (DISABLE) NOVALIDATE signifies that Oracle makes no effort to maintain the constraint (because it is disabled) and cannot guarantee that the constraint is true (because it is not being validated). The default is NOVALIDATE.


CHECK_CONSTRAINT Object

Table 8-55 CHECK_CONSTRAINT Object

Property Type Choices Min Max Default Description

DB_LOCATION

STRING

none

none

none

empty string

Location for referenced database objects.

DEFERRABLE

STRING

, DEFERRABLE, NOT DEFERRABLE

none

none

empty string

Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The default is NOT DEFERRABLE.

DEPLOYABLE

BOOLEAN

true, false

none

none

true

Warehouse Builder generates a set of scripts to create an object only for those object marked as Deployable = true

ENABLECONSTRAINT

STRING

, DISABLE, ENABLE

none

none

empty string

Specify ENABLE if you want the constraint to be applied to the data in the table. Specify DISABLE to disable the integrity constraint. The default is ENABLE.

EXCEPTIONSINTO

STRING

none

none

none

empty string

Specify an exceptions table ([schema.]table). The EXCEPTIONS table or the table you specify must exist on your local database. If you create your own exceptions table, then it must follow the format prescribed by one of the two scripts supplied by Oracle. Do not use this property with NOVALIDATE option.

INITIALLY

STRING

, DEFERRED, IMMEDIATE

none

none

empty string

Specify (INITIALLY) IMMEDIATE to indicate that Oracle should check a DEFERRABLE constraint at the end of each subsequent SQL statement. Specify (INITIALLY) DEFERRED to indicate that Oracle should check a DEFERRABLE constraint at the end of subsequent transactions. The default is (INITIALLY) IMMEDIATE.

RELY

STRING

, NORELY, RELY

none

none

empty string

Specify RELY to activate an existing constraint in NOVALIDATE mode for query rewrite in an unenforced query rewrite integrity mode. The default is NORELY.

SUBSTITUTE_KEY

BOOLEAN

true, false

none

none

false

This is related to Streams Support. If this is true, deployment will result only in creation of the key metadata. The constraint itself will not be enforced. This will be done by creating a Streams substitute key.

VALIDATECONSTRAINT

STRING

, NOVALIDATE, VALIDATE

none

none

empty string

The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default. (ENABLE) VALIDATE specifies that all old and new data must compliy with the constraint. (ENABLE) NOVALIDATE only ensures that all new DML operations on the constrained data comply with the constraint. (DISABLE) VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. (DISABLE) NOVALIDATE signifies that Oracle makes no effort to maintain the constraint (because it is disabled) and cannot guarantee that the constraint is true (because it is not being validated). The default is NOVALIDATE.


INDEX Object

Table 8-56 INDEX Object

Property Type Choices Min Max Default Description

BUFFER_POOL

STRING

, DEFAULT, KEEP, RECYCLE

none

none

empty string

Specify a default buffer pool (cache) for table or partition object. The default is DEFAULT.

COMPUTESTATISTICS

STRING

, NO, YES

none

none

empty string

Specify YES to collect statistics at relatively little cost during the creation of an index.

DB_LOCATION

STRING

none

none

none

empty string

Location for referenced database objects.

DEFERRABLE

STRING

, DEFERRABLE, NOT DEFERRABLE

none

none

empty string

Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The default is NOT DEFERRABLE.

DEPLOYABLE

BOOLEAN

true, false

none

none

true

Warehouse Builder generates a set of scripts to create an object only for those object marked as Deployable = true

ENABLECONSTRAINT

STRING

, DISABLE, ENABLE

none

none

empty string

Specify ENABLE if you want the constraint to be applied to the data in the table. Specify DISABLE to disable the integrity constraint. The default is ENABLE.

EXCEPTIONSINTO

STRING

none

none

none

empty string

Specify an exceptions table ([schema.]table). The EXCEPTIONS table or the table you specify must exist on your local database. If you create your own exceptions table, then it must follow the format prescribed by one of the two scripts supplied by Oracle. Do not use this property with NOVALIDATE option.

FREELISTGROUPS

STRING

none

none

none

empty string

Specify the number of groups of free lists for the database object you are creating. The default is 1.

FREELISTS

STRING

none

none

none

empty string

Specify the number of free lists for each of the free list groups for the table, partition, cluster, or index. The default is 1.

INDEXORDER

STRING

, ASC, DESC

none

none

empty string

Use ASC or DESC to indicate whether the index should be created in ascending or descending order. The Oracle default is ASC.

INITIAL

STRING

none

none

none

empty string

Specify in bytes the size of the first extent. Use K or M to specify size in kilobytes or megabytes.

INITIALLY

STRING

, DEFERRED, IMMEDIATE

none

none

empty string

Specify (INITIALLY) IMMEDIATE to indicate that Oracle should check a DEFERRABLE constraint at the end of each subsequent SQL statement. Specify (INITIALLY) DEFERRED to indicate that Oracle should check a DEFERRABLE constraint at the end of subsequent transactions. The default is (INITIALLY) IMMEDIATE.

INITRANS

STRING

none

none

none

empty string

Specify the initial number (2-255) of concurrent transaction entries allocated within each data block allocated to the database object. The default is 2 for Index.

KEYCOMPRESS

STRING

, COMPRESS, NOCOMPRESS

none

none

empty string

Specify COMPRESS to enable key compression.

KEYCOMPRESSPREFIXLENGTH

STRING

none

none

none

empty string

Specify the prefix length (number of prefix columns to compress). For unique indexes, the valid range of prefix length values is from 1 to the number of key columns minus 1. The default prefix length is the number of key columns minus 1. For nonunique indexes, the valid range of prefix length values is from 1 to the number of key columns. The default prefix length is the number of key columns. Oracle compresses only nonpartitioned indexes that are nonunique or unique indexes of at least two columns. You cannot specify COMPRESS for a bitmap index.

LOGGING_MODE

STRING

, LOGGING, NOLOGGING

none

none

empty string

Recovery requirements for a data warehouse : Logging or not logging to Redo Log File. The default is LOGGING.

MAXEXTENTS

STRING

none

none

none

empty string

Specify the total number of extents, including the first, that Oracle can allocate for the object.

MAXTRANS

STRING

none

none

none

empty string

Specify the maximum number (2-255) of concurrent transactions that can update a data block allocated to the database object.

MINEXTENTS

STRING

none

none

none

empty string

Specify the total number of extents to allocate when the object is created.

NEXT

STRING

none

none

none

empty string

Specify in bytes the size of the next extent to be allocated. Use K or M to specify size in kilobytes or megabytes.

ONLINE

STRING

, NO, YES

none

none

empty string

Specify YES to indicate that DML operations on the table will be allowed during creation of the index.

OPTIMAL

STRING

none

none

none

empty string

Specifies an optimal size in bytes for a rollback segment. Use K or M to specify this size in kilobytes or megabytes. Specify NULL for no optimal size for the rollback segment. The default is NULL.

OVERFLOW

STRING

none

none

none

empty string

Enter a comma separated list of Index tablespaces for overflow data. For simple-partitoned object, it is used for HASH BY QUANTITY partition overflow Index tablespaces. The number of Index tablespaces does not have to equal the number of partitions. If the number of partitions is greater than the number of Index tablespaces, then Oracle cycles through the names of the Index tablespaces.

PARALLEL_ACCESS_MODE

STRING

, NOPARALLEL, PARALLEL

none

none

empty string

Enables or disables parallel processing when the table is created. Also enables or disables parallel processing or access. The default is PARALLEL.

PARALLEL_DEGREE

STRING

none

none

none

empty string

Enter degree of parallelism, which is the number of parallel threads used in the parallel operation.

PARTITION_TABLESPACE_LIST

STRING

none

none

none

empty string

Enter a comma separated list of tablespaces for a locally partitioned index. For simple-partitoned object, it is used for HASH BY QUANTITY partition tablespaces. If specified, then individual local Hash index partitions specified will be ignored for Local Hash or Range-Hash Index partitioning.

PCTFREE

STRING

none

none

none

empty string

Specify a whole number representing the percentage (0-99) of space in each data block of the database object reserved for future updates to the rows of the object. The default is 10.

PCTINCREASE

STRING

none

none

none

empty string

Specify the percent by which the third and subsequent extents grow over the preceding extent. The default is 50.

RELY

STRING

, NORELY, RELY

none

none

empty string

Specify RELY to activate an existing constraint in NOVALIDATE mode for query rewrite in an unenforced query rewrite integrity mode. The default is NORELY.

SORT

STRING

, NOSORT, REVERSE, SORT

none

none

empty string

Specify NOSORT to indicate to Oracle that the rows are already stored in the database in ascending order. Specify REVERSE to store the bytes of the index block in reverse order, excluding the rowid.

SUBSTITUTE_KEY

BOOLEAN

true, false

none

none

false

This is related to Streams Support. If this is true, deployment will result only in creation of the key metadata. The constraint itself will not be enforced. This will be done by creating a Streams substitute key.

TABLESPACE

STRING(30)

none

none

none

empty string

 

VALIDATECONSTRAINT

STRING

, NOVALIDATE, VALIDATE

none

none

empty string

The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default. (ENABLE) VALIDATE specifies that all old and new data must compliy with the constraint. (ENABLE) NOVALIDATE only ensures that all new DML operations on the constrained data comply with the constraint. (DISABLE) VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. (DISABLE) NOVALIDATE signifies that Oracle makes no effort to maintain the constraint (because it is disabled) and cannot guarantee that the constraint is true (because it is not being validated). The default is NOVALIDATE.


PARTITION Object

Table 8-57 PARTITION Object

Property Type Choices Min Max Default Description

BUFFER_POOL

STRING

, DEFAULT, KEEP, RECYCLE

none

none

empty string

Specify a default buffer pool (cache) for table or partition object. The default is DEFAULT.

DATA_SEGMENT_COMPRESSION

STRING

, COMPRESS, COMPRESS ALL, NOCOMPRESS

none

none

empty string

Use this clause to instruct Oracle whether to compress data segments to reduce disk use. The default is NOCOMPRESS.

DEPLOYABLE

BOOLEAN

true, false

none

none

true

Warehouse Builder generates a set of scripts to create an object only for those object marked as Deployable = true

FREELISTGROUPS

STRING

none

none

none

empty string

Specify the number of groups of free lists for the database object you are creating. The default is 1.

FREELISTS

STRING

none

none

none

empty string

Specify the number of free lists for each of the free list groups for the table, partition, cluster, or index.

INITIAL

STRING

none

none

none

empty string

Specify in bytes the size of the first extent. Use K or M to specify size in kilobytes or megabytes.

INITRANS

STRING

none

none

none

empty string

Specify the initial number (1-255) of concurrent transaction entries allocated within each data block allocated to the database object. The default is 1 for Table and 2 for Index.

LOGGING_MODE

STRING

, LOGGING, NOLOGGING

none

none

empty string

Specify whether the creation of the table and of any indexes required because of constraints, partition, or LOB storage characteristics will be logged in the redo log file (LOGGING) or not (NOLOGGING). The default is LOGGING.

MAXEXTENTS

STRING

none

none

none

empty string

Specify the total number of extents, including the first, that Oracle can allocate for the object.

MAXTRANS

STRING

none

none

none

empty string

Specify the maximum number (1-255) of concurrent transactions that can update a data block allocated to the database object.

MINEXTENTS

STRING

none

none

none

empty string

Specify the total number of extents to allocate when the object is created.

NEXT

STRING

none

none

none

empty string

Specify in bytes the size of the next extent to be allocated. Use K or M to specify size in kilobytes or megabytes.

OPTIMAL

STRING

none

none

none

empty string

Specifies an optimal size in bytes for a rollback segment. Use K or M to specify this size in kilobytes or megabytes. Specify NULL for no optimal size for the rollback segment. The default is NULL.

PCTFREE

STRING

none

none

none

empty string

Specify a whole number representing the percentage (0-99) of space in each data block of the database object reserved for future updates to the rows of the object. The default is 10.

PCTINCREASE

STRING

none

none

none

empty string

Specify the percent by which the third and subsequent extents grow over the preceding extent. The default is 50.

PCTUSED

STRING

none

none

none

empty string

Specify a whole number representing the minimum percentage (0-99) of used space that Oracle maintains for each data block of the database object. The default is 40.

TABLESPACE

STRING(30)

none

none

none

empty string