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

OMBALTER PROFILE_REFERENCE

Purpose

To alter a profile reference, such as to add or delete a data rule usage, or set properties on a profile attribute.

Prerequisites

Must be done in the context of a Data Profile.

Syntax

alterProfileReferenceCommand =  OMBALTER ( PROFILE_REFERENCE 
          "QUOTED_STRING" ( [ SET "setPropertiesClause" ] { ADD 
          "addDataRuleUsageClause" | DELETE "deleteDataRuleUsageClause" | MODIFY
           ( "modifyDataRuleUsageClause" | "modifyProfileAttributeClause" ) } ) 
          )
     setPropertiesClause =  PROPERTIES "(" "propertyNameList" ")" VALUES "(" 
          "propertyValueList" ")"
     addDataRuleUsageClause =  DATA_RULE_USAGE ( [ "QUOTED_STRING" ] [ SET REF 
          DATA_RULE "QUOTED_STRING" ] ( DERIVE FROM ( ( 
          "getProfileAttributeDerivationClause" | 
          "getProfileForeignKeyDerivationClause" | 
          "getProfileUniqueKeyDerivationClause" | 
          "getFunctionalDependencyDerivationClause" | 
          "getRowRelationshipDerivationClause" ) [ CREATE IN DATA_RULE_MODULE 
          "QUOTED_STRING" ] ) | ( GROUP "QUOTED_STRING" SET REF TABLE 
          "QUOTED_STRING" ( ATTRIBUTE "QUOTED_STRING" SET REF COLUMN 
          "QUOTED_STRING" )+ )+ [ SET "setPropertiesClause" ] ) )
     deleteDataRuleUsageClause =  DATA_RULE_USAGE "QUOTED_STRING"
     modifyDataRuleUsageClause =  DATA_RULE_USAGE "QUOTED_STRING" ( 
          "renameClause" [ SET "setPropertiesClause" ] | SET 
          "setPropertiesClause" )
     modifyProfileAttributeClause =  PROFILE_ATTRIBUTE "QUOTED_STRING" SET 
          "setPropertiesClause"
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }
     propertyValueList =  "propertyValue" { "," "propertyValue" }
     getProfileAttributeDerivationClause =  PROFILE_ATTRIBUTE "QUOTED_STRING" 
          PROFILE_TYPE "QUOTED_STRING"
     getProfileForeignKeyDerivationClause =  PROFILE_FOREIGN_KEY "QUOTED_STRING"
     getProfileUniqueKeyDerivationClause =  PROFILE_UNIQUE_KEY "QUOTED_STRING"
     getFunctionalDependencyDerivationClause =  FUNCTIONAL_DEPENDENCY 
          "QUOTED_STRING"
     getRowRelationshipDerivationClause =  ROW_RELATIONSHIP "QUOTED_STRING"
     renameClause =  RENAME TO "QUOTED_STRING"
     propertyValue =  ( "QUOTED_STRING" | "INTEGER_LITERAL" | 
          "FLOATING_POINT_LITERAL" )

Parameters

alterProfileReferenceCommand

This clause alters the profile reference.

QUOTED_STRING

This is the name of the source pointed to by the profile reference.

setPropertiesClause

This clause sets properties for the corresponding object.

getPropertiesClause

Configuration properties for PROFILE_REFERENCE that affect loading:

Name: COPY_DATA

Type: BOOLEAN

Valid Values: true | false

Default: true

Setting this to true will enable copying of data from source to profile workspace.

Name: FORCE_COPY_DATA

Type: BOOLEAN

Valid Values: true | false

Default: false

Setting this to true will always force a profile to run.

Name: CALCULATE_DATATYPES

Type: BOOLEAN

Valid Values: true | false

Default: false

Setting this to true will enable data type discovery for the selected table.

Name: CALCULATE_COMMON_FORMATS

Type: BOOLEAN

Valid Values: true | false

Default: false

Setting this to true will enable common format discovery for the selected table.

Name: NULL_VALUE

Type: STRING

Valid Values: any string value

Default: null

This value will be considered as the null value when profiling. Please enclose the value in single quotes. An unqouted null (the current default value) will be considered a database null.

Name: SAMPLE_RATE

Type: 100

Valid Values: 1-100

Default: 100

This value will be the percent of total rows that will be randomly selected during loading.

getPropertiesClause

Configuration properties for PROFILE_REFERENCE that affect profiling:

Name: CALCULATE_DOMAINS

Type: BOOLEAN

Valid Values: true | false

Default: true

Setting this to true will enable domain discovery.

Name: DOMAIN_MAX_COUNT

Type: NUMBER

Valid Values: 1-any number

Default: true

The maximum number of distinct values in a column in order for that column to be discovered as possibly being defined by a domain. Domain Discovery of a column occurs if the number of distinct values in that column is at or below the Max Distinct Values Count property, AND, the number of distinct values as a percentage of total rows is at or below the Max Distinct Values Percent property.

Name: DOMAIN_MAX_PERCENT

Type: NUMBER

Valid Values: 1-100

Default: true

The maximum number of distinct values in a column, expressed as a percentage of the total number of rows in the table, in order for that column to be discovered as possibly being defined by a domain. Domain Discovery of a column occurs if the number of distinct values in that column is at or below the Max Distinct Values Count property, AND, the number of distinct values as a percentage of total rows is at or below the Max Distinct Values Percent property.

Name: DOMAIN_MIN_COUNT

Type: NUMBER

Valid Values: 1-any number

Default: true

The minimum number of rows for the given distinct value in order for that distinct value to be considered as compliant with the domain. Domain Value Compliance for a value occurs if the number of rows with that value is at or above the Min Rows Count property, AND, the number of rows with that value as a percentage of total rows is at or above the Min Rows Percent property.

Name: DOMAIN_MIN_PERCENT

Type: NUMBER

Valid Values: 1-100

Default: true

The minimum number of rows, expressed as a percentage of the total number of rows, for the given distinct value in order for that distinct value to be considered as compliant with the domain. Domain Value Compliance for a value occurs if the number of rows with that value is at or above the Min Rows Count property, AND, the number of rows with that value as a percentage of total rows is at or above the Min Rows Percent property.

Name: CALCULATE_UK

Type: BOOLEAN

Valid Values: true | false

Default: true

Setting this to true will enable unique key discovery.

Name: UK_MIN_PERCENT

Type: NUMBER

Valid Values: 1-100

Default: 75

This is the minimum percentage of rows that need to satisfy a unique key relationship.

Name: CALCULATE_FD

Type: BOOLEAN

Valid Values: true | false

Default: true

Setting this to true will enable functional dependency discovery.

Name: FD_MIN_PERCENT

Type: NUMBER

Valid Values: 1-100

Default: 75

This is the minimum percentage of rows that need to satisfy a functional dependency relationship.

Name: FD_UK_LHS_COUNT

Type: NUMBER

Valid Values: 1-number of attributes of source less 1

Default: 1

This is the maximum number of attributes for unique key and functional dependency profiling.

getPropertiesClause

Name: CALCULATE_FK

Type: BOOLEAN

Valid Values: true | false

Default: true

Setting this to true will enable foreign key discovery.

Name: FK_MIN_PERCENT

Type: NUMBER

Valid Values: 1-100

Default: 75

This is the minimum percentage of rows that need to satisfy a foreign key relationship.

Name: CALCULATE_REDUNDANT_COLUMNS

Type: BOOLEAN

Valid Values: true | false

Default: false

Setting this to true will enable redundant column discovery with respect to a foreign key-unique key pair.

Name: REDUNDANT_MIN_PERCENT

Type: NUMBER

Valid Values: 1-100

Default: 75

This is the minimum percentage of rows that are redundant.

Name: CALCULATE_DATA_RULES

Type: BOOLEAN

Valid Values: true | false

Default: false

Setting this to true will enable data rule profiling for the selected table.

Configuration properties for PROFILE_ATTRIBUTE that affect loading:

Name: USE_IN_LOADING

Type: BOOLEAN

Valid Values: true | false

Default: true

This tells the profiler if the data for this column is to be copied from the source schema to the profile workspace schema.

Name: CALCULATE_PATTERNS

Type: BOOLEAN

Valid Values: true | false

Default: false

Setting this to true will enable pattern discovery.

Name: MAX_NUM_PATTERNS

Type: NUMBER

Valid Values: any number less than the number of rows of the source

Default: 10

This tells the profiler to get the top-N patterns for the attribute.

getPropertiesClause

Configuration properties for PROFILE_ATTRIBUTE that affect loading:

Name: USE_IN_FK

Type: BOOLEAN

Valid Values: true | false

Default: true

This tells the profiler if this column is to be part of the determinant in functional dependency discovery.By default, all non-numeric columns set this property to false

Name: USE_IN_LOADING

Type: BOOLEAN

Valid Values: true | false

Default: true

Setting this to true will include this column in profiling.

Name: CALCULATE_DATATYPES

Type: BOOLEAN

Valid Values: true | false

Default: false

Setting this to true will enable data type discovery for the selected table.

Name: CALCULATE_COMMON_FORMATS

Type: BOOLEAN

Valid Values: true | false

Default: false

Setting this to true will enable common format discovery for the selected table.

Name: NULL_VALUE

Type: STRING

Valid Values: any string value

Default: null

This value will be considered as the null value when profiling. Please enclose the value in single quotes. An unqouted null (the current default value) will be considered a database null.

Configuration properties for PROFILE_ATTRIBUTE that affect profiling:

Name: USE_IN_DETERMINANT

Type: BOOLEAN

Valid Values: true | false

Default: true

This tells the profiler if this column is to be part of the determinant in functional dependency discovery.By default, all non-numeric columns set this property to false

Name: CALCULATE_DOMAINS

Type: BOOLEAN

Valid Values: true | false

Default: true

This tells the profiler if domain values are to be discovered for this column.

Name: DOMAIN_MAX_COUNT

Type: NUMBER

Valid Values: 1-any number

Default: true

This tells the profiler the maximum number of distinct values this column can have to be considered as domain attributes.

Name: DOMAIN_MAX_PERCENT

Type: NUMBER

Valid Values: 1-100

Default: true

This tells the profiler the maximum number of distinct values as a percentage this column can have to be considered as domain attributes.

Name: DOMAIN_MIN_COUNT

Type: NUMBER

Valid Values: 1-any number

Default: true

This tells the profiler the minimum number of rows a particular value should have in order to be considered a domain value.

Name: DOMAIN_MIN_PERCENT

Type: NUMBER

Valid Values: 1-100

Default: true

This tells the profiler the minimum number of rows as a percentage a particular value should have in order to be considered a domain value.

addDataRuleUsageClause

Add a data rule usage to the profile table.

QUOTED_STRING

name of data rule usage.

deleteDataRuleUsageClause

Delete a data rule usage.

QUOTED_STRING

name of data rule usage.

modifyDataRuleUsageClause

Rename or modify the properties of a data rule usage.

QUOTED_STRING

name of data rule usage.

modifyProfileAttributeClause

Modify the attribute of a profile reference.

propertyNameList

Comma separated list of property names. Property names are unquoted.

propertyValueList

Comma separated list of property values.

getProfileAttributeDerivationClause

Derive a data rule usage for an attribute from a profile type.

getProfileForeignKeyDerivationClause

Derive a foreign key data rule usage from a profile foreign key.

getProfileUniqueKeyDerivationClause

Derive a unique key data rule usage from a profile unique key.

getFunctionalDependencyDerivationClause

Derive a functional dependency data rule usage from a profile functional dependency.

getRowRelationshipDerivationClause

Derive a row relationship data rule usage for an attribute from a profile row relationship.

renameClause

Renames an object.

propertyValue

Value of a property.

PROFILE_REFERENCE Object

Table 4-5 PROFILE_REFERENCE Object

Property Type Choices Min Max Default Description

ANALYZE_TABLE_ESTIMATE_PERCENT

STRING

none

none

none

empty string

Value represents the sample size as a percentage (0-100) of total rows. When set to a nonzero value, Builder generates a DDL script to analyze the table. The default is 99.

BUFFER_POOL

STRING

, DEFAULT, KEEP, RECYCLE

none

none

empty string

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

CACHE_MODE

STRING

, CACHE, NOCACHE

none

none

empty string

Indicate how Oracle should store blocks in the buffer cache.

CALCULATE_COMMON_FORMATS

BOOLEAN

true, false

none

none

false

Setting this to true will enable common format discovery for the selected table.

CALCULATE_DATATYPES

BOOLEAN

true, false

none

none

true

Setting this to true will enable data type discovery for the selected table.

CALCULATE_DATA_RULES

BOOLEAN

true, false

none

none

false

Setting this to true will enable data rule profiling for the selected table.

CALCULATE_DOMAINS

BOOLEAN

true, false

none

none

true

Setting this to true will enable domain discovery.

CALCULATE_FD

BOOLEAN

true, false

none

none

true

Setting this to true will enable functional dependency discovery.

CALCULATE_FK

BOOLEAN

true, false

none

none

true

Setting this to true will enable foreign key discovery.

CALCULATE_PATTERNS

BOOLEAN

true, false

none

none

false

Setting this to true will enable pattern discovery.

CALCULATE_REDUNDANT_COLUMNS

BOOLEAN

true, false

none

none

false

Setting this to true will enable redundant column discovery with respect to a foreign key-unique key pair.

CALCULATE_UK

BOOLEAN

true, false

none

none

true

Setting this to true will enable unique key discovery.

CDC_POSITION

NUMBER

none

none

none

0

Table position in change data capture.

COPY_DATA

BOOLEAN

true, false

none

none

true

Setting this to true will enable copying of data from source to staging area.

CREATE_MVIEWS

BOOLEAN

true, false

none

none

false

Setting this to true will create a materialized view for each column in this table to enhance query performance during drill down.

DATA_RULE_SIGNATURE

STRING

none

none

none

empty string

 

DATA_SEGMENT_COMPRESSION

STRING

, COMPRESS, COMPRESS ALL, NOCOMPRESS

none

none

empty string

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

DEPLOYABLE

BOOLEAN

true, false

none

none

false

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

DOMAIN_MAX_COUNT

NUMBER

none

none

none

100

The maximum number of distinct values in a column in order for that column to be discovered as possibly being defined by a domain. Domain Discovery of a column occurs if the number of distinct values in that column is at or below the Max Distinct Values Count property, AND, the number of distinct values as a percentage of total rows is at or below the Max Distinct Values Percent property.

DOMAIN_MAX_PERCENT

NUMBER

none

0

100

50

The maximum number of distinct values in a column, expressed as a percentage of the total number of rows in the table, in order for that column to be discovered as possibly being defined by a domain. Domain Discovery of a column occurs if the number of distinct values in that column is at or below the Max Distinct Values Count property, AND, the number of distinct values as a percentage of total rows is at or below the Max Distinct Values Percent property.

DOMAIN_MIN_COUNT

NUMBER

none

none

none

2

The minimum number of rows for the given distinct value in order for that distinct value to be considered as compliant with the domain. Domain Value Compliance for a value occurs if the number of rows with that value is at or above the Min Rows Count property, AND, the number of rows with that value as a percentage of total rows is at or above the Min Rows Percent property.

DOMAIN_MIN_PERCENT

NUMBER

none

0

100

1

The minimum number of rows, expressed as a percentage of the total number of rows, for the given distinct value in order for that distinct value to be considered as compliant with the domain. Domain Value Compliance for a value occurs if the number of rows with that value is at or above the Min Rows Count property, AND, the number of rows with that value as a percentage of total rows is at or above the Min Rows Percent property.

ERROR_TABLESPACE

STRING(30)

none

none

none

empty string

Use the Tablespace parameter to specify the name of tablespace.

ERROR_TABLE_NAME

STRING(30)

none

none

none

empty string

Use the error table name to specify the name of Error Table.

FD_MIN_PERCENT

NUMBER

none

0

100

100

This is the minimum percentage of rows that need to satisfy a functional dependency relationship.

FD_UK_FK_LHS_COUNT

NUMBER

none

none

none

1

This is the maximum number of attributes for unique key, foreign key and functional dependency profiling.

FK_MIN_PERCENT

NUMBER

none

0

100

75

This is the minimum percentage of rows that need to satisfy a foreign key relationship.

FORCE_COPY_DATA

BOOLEAN

true, false

none

none

false

Setting this to true will force a profile to run.

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.

GENERATED_OBJECTS

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.

GENERATION_COMMENTS

STRING

none

none

none

empty string

Enter additional comments for the generated code.

INITIAL

STRING

none

none

none

empty string

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

INITRANS

STRING

none

none

none

empty string

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

IS_CDC

BOOLEAN

true, false

none

none

false

Enable table for change data capture

LOGGING_MODE

STRING

, LOGGING, NOLOGGING

none

none

empty string

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

MAXEXTENTS

STRING

none

none

none

empty string

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

MAXTRANS

STRING

none

none

none

empty string

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

MAX_NUM_PATTERNS

NUMBER

none

none

none

10

This tells the profiler to get the top-N patterns for the attribute.

MINEXTENTS

STRING

none

none

none

empty string

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

MONITORING_MODE

STRING

, MONITORING, NOMONITORING

none

none

empty string

Specify MONITORING if you want modification statistics to be collected on this table.

NEXT

STRING

none

none

none

empty string

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

NULL_PERCENT

NUMBER

none

0

100

10

If the percentage of null values in a column is less than this threshold percent, then that column will be discovered as a possible Not Null column.

NULL_VALUE

STRING

none

none

none

null

This value will be considered as the null value when profiling. Please enclose the value in single quotes. An unqouted null (the current default value) will be considered a database null.

NUM_ROWS

NUMBER

none

none

none

0

 

OVERFLOW

STRING

none

none

none

empty string

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

PARALLEL_ACCESS_MODE

STRING

, NOPARALLEL, PARALLEL

none

none

empty string

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

PARALLEL_DEGREE

STRING

none

none

none

empty string

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

PARTITION_TABLESPACE_LIST

STRING

none

none

none

empty string

Enter a comma separated list of tablespaces. For simple-partitoned object, it is used for HASH BY QUANTITY partition tablespaces. For composite-partitioned tables, it is used for subpartition template to store a list of tablespaces.

PCTFREE

STRING

none

none

none

empty string

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

PCTINCREASE

STRING

none

none

none

empty string

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

PCTUSED

STRING

none

none

none

empty string

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

PROFILE_DONE

BOOLEAN

true, false

none

none

false

 

REDUNDANT_MIN_PERCENT

NUMBER

none

0

100

75

This is the minimum percentage of rows that are redundant.

ROWDEPENDENCIES_MODE

STRING

, NOROWDEPENDENCIES, ROWDEPENDENCIES

none

none

empty string

Specify ROWDEPENDENCIES to use row-level dependency tracking.

ROW_MOVEMENT

STRING

, DISABLE, ENABLE

none

none

empty string

Specify whether Oracle can move a table row.

SAMPLE_RATE

NUMBER

none

0

100

100

This value will be the percent of total rows that will be randomly selected during loading.

TABLESPACE

STRING(30)

none

none

none

empty string

 

UK_MIN_PERCENT

NUMBER

none

0

100

75

This is the minimum percentage of rows that need to satisfy a unique key relationship.

WHERE_CLAUSE

STRING

none

none

none

empty string

Where clause to be applied on the source when loading data into the profile staging area.


See Also

OMBALTER