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

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

OMBRETRIEVE MATERIALIZED_VIEW

Purpose

To retrieve properties of a materialized view.

Prerequisites

In the context of an Oracle Module

Syntax

retrieveMaterializedViewCommand =  OMBRETRIEVE MATERIALIZED_VIEW 
          "QUOTED_STRING" ( "retrieveMaterializedViewClause" | 
          "retrieveColumnClause" | "retrieveUkPkClause" | "retrieveFkClause" | 
          "retrieveCheckConstraintClause" | "retrieveIndexConfigurationClause" |
           "retrievePartitionConfigurationClause" | 
          "retrievePartitionKeyConfigurationClause" | 
          "retrieveTemplateSubpartitionConfigurationClause" | 
          "retrieveSubPartitionConfigurationClause" | 
          "retrieveSubPartitionKeyConfigurationClause" | 
          "retrieveDataRuleUsageClause" )
     retrieveMaterializedViewClause =  GET ( "getPropertiesClause" | 
          "getReferenceIconSetClause" | 
          "getMaterializedViewSCOorDependentsClause" )
     retrieveColumnClause =  COLUMN "QUOTED_STRING" GET "getPropertiesClause"
     retrieveUkPkClause =  ( UNIQUE_KEY | PRIMARY_KEY ) "QUOTED_STRING" GET ( 
          "getPropertiesClause" | COLUMNS )
     retrieveFkClause =  FOREIGN_KEY "QUOTED_STRING" GET ( "getPropertiesClause"
           | COLUMNS | UNIQUE_KEY | PRIMARY_KEY | REFERENCED_KEY )
     retrieveCheckConstraintClause =  CHECK_CONSTRAINT "QUOTED_STRING" GET 
          "getPropertiesClause"
     retrieveIndexConfigurationClause =  INDEX "QUOTED_STRING" ( GET ( 
          "getConfigurationPropertiesClause" | INDEX_COLUMNS | INDEX_PARTITIONS 
          | INDEX_PARTITION_KEYS ) | ( INDEX_COLUMN "QUOTED_STRING" GET 
          "getConfigurationPropertiesClause" ) | ( INDEX_PARTITION_KEY 
          "QUOTED_STRING" GET "getConfigurationPropertiesClause" ) | ( 
          INDEX_PARTITION "QUOTED_STRING" GET "getConfigurationPropertiesClause"
           ) )
     retrievePartitionConfigurationClause =  PARTITION "QUOTED_STRING" GET 
          "getConfigurationPropertiesClause"
     retrievePartitionKeyConfigurationClause =  PARTITION_KEY "QUOTED_STRING" 
          GET "getConfigurationPropertiesClause"
     retrieveTemplateSubpartitionConfigurationClause =  TEMPLATE_SUBPARTITION 
          "QUOTED_STRING" GET "getConfigurationPropertiesClause"
     retrieveSubPartitionConfigurationClause =  SUBPARTITION "QUOTED_STRING" OF 
          PARTITION "QUOTED_STRING" GET "getConfigurationPropertiesClause"
     retrieveSubPartitionKeyConfigurationClause =  SUBPARTITION_KEY 
          "QUOTED_STRING" GET "getConfigurationPropertiesClause"
     retrieveDataRuleUsageClause =  DATA_RULE_USAGE "QUOTED_STRING" ( GET ( 
          "getPropertiesClause" | GROUPS ) | GROUP "QUOTED_STRING" ( GET ( 
          "getPropertiesClause" | ATTRIBUTES | REF ( TABLE | VIEW | 
          MATERIALIZED_VIEW | EXTERNAL_TABLE ) ) | ATTRIBUTE "QUOTED_STRING" ( 
          GET ( "getPropertiesClause" | REF COLUMN ) ) ) )
     getPropertiesClause =  PROPERTIES "(" "propertyNameList" ")"
     getReferenceIconSetClause =  ( REF | REFERENCE ) ICONSET
     getMaterializedViewSCOorDependentsClause =  COLUMNS | UNIQUE_KEYS | 
          PRIMARY_KEYS | PRIMARY_KEY | FOREIGN_KEYS | COLUMN AT POSITION 
          "INTEGER_LITERAL" | INDEXES | PARTITIONS | PARTITION_KEYS | 
          SUBPARTITION_KEYS | DATA_RULE_USAGES | ATTRIBUTE_SETS | ( REF | 
          REFERENCE ) ( TABLES | VIEWS | MATERIALIZED_VIEWS )
     getConfigurationPropertiesClause =  PROPERTIES "(" "propertyNameList" ")"
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }

Parameters

retrieveMaterializedViewCommand

This clause retrieves a materialized view.

QUOTED_STRING

name of the materialized view.

retrieveMaterializedViewClause

This clause retrieves a properties of a materialized view.

retrieveColumnClause

This clause will retrieve columns.

QUOTED_STRING

Name of the column.

retrieveUkPkClause

This clause will retrieve a unique key or primary key.

QUOTED_STRING

Name of the unique key or the primary key.

retrieveFkClause

This clause will retrieve a key referenced by a foreign key, either a unique key or primary key. Use REFERENCED_KEY to retrieve the referenced key for a foreign key regardless of the type of referenced key (unique or primary).

QUOTED_STRING

Name of the foreign key.

retrieveCheckConstraintClause

This clause gets the check constraint.

QUOTED_STRING

Name of the check constraint.

retrieveIndexConfigurationClause

Gets the index in this clause.

QUOTED_STRING

Name of the index.

retrievePartitionConfigurationClause

Gets the partition.

QUOTED_STRING

The partition name.

retrievePartitionKeyConfigurationClause

This clause gets the partition key.

QUOTED_STRING

The name of the partition key.

retrieveDataRuleUsageClause

This clause retrieves the data rule usages.

QUOTED_STRING

Name of data rule usage, group or attribute.

GROUPS

Retrieve the names of all relation groups in the data rule usage.

ATTRIBUTES

Retrieve the names of all attributes in a data rule usage group.

TABLE

Table name associated withthe data rule usage group.

VIEW

View name associated withthe data rule usage group.

MATERIALIZED_VIEW

Materialized view name associated with the data rule usage group.

EXTERNAL_TABLE

External table name associated with the data rule usage group.

COLUMN

Column name associated with the data rule usage group attribute.

getPropertiesClause

This clause retrieves all the properties.

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.

getMaterializedViewSCOorDependentsClause

This clause will retrieves materialized view components like columns, keys, etc. or relational objects that this materialized view have referential dependency on.

getConfigurationPropertiesClause

This clauses gets the configuration properties of the object.

propertyNameList

The list of properties.

MATERIALIZED_VIEW Object

Table 19-53 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 19-54 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 19-55 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 19-56 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.


INDEX Object

Table 19-57 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 19-58 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