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
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

OMBRETRIEVE MATERIALIZED_VIEW

Purpose

Prerequisites

Syntax

Parameters

retrieveMaterializedViewCommand

QUOTED_STRING

retrieveMaterializedViewClause

retrieveColumnClause

QUOTED_STRING

retrieveUkPkClause

QUOTED_STRING

retrieveFkClause

QUOTED_STRING

retrieveCheckConstraintClause

QUOTED_STRING

retrieveIndexConfigurationClause

QUOTED_STRING

retrievePartitionConfigurationClause

QUOTED_STRING

retrievePartitionKeyConfigurationClause

QUOTED_STRING

retrieveDataRuleUsageClause

QUOTED_STRING

GROUPS

ATTRIBUTES

TABLE

VIEW

MATERIALIZED_VIEW

EXTERNAL_TABLE

COLUMN

getPropertiesClause

getPropertiesClause

getPropertiesClause

getPropertiesClause

getPropertiesClause

getPropertiesClause

getPropertiesClause

getMaterializedViewSCOorDependentsClause

getConfigurationPropertiesClause

propertyNameList

MATERIALIZED_VIEW Object

COLUMN Object

UNIQUE_KEY Object

FOREIGN_KEY Object

INDEX Object

PARTITION Object

See Also