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.

OMBALTER PROFILE_REFERENCE

Purpose

Prerequisites

Syntax

Parameters

alterProfileReferenceCommand

QUOTED_STRING

setPropertiesClause

getPropertiesClause

getPropertiesClause

getPropertiesClause

getPropertiesClause

addDataRuleUsageClause

QUOTED_STRING

deleteDataRuleUsageClause

QUOTED_STRING

modifyDataRuleUsageClause

QUOTED_STRING

modifyProfileAttributeClause

propertyNameList

propertyValueList

getProfileAttributeDerivationClause

getProfileForeignKeyDerivationClause

getProfileUniqueKeyDerivationClause

getFunctionalDependencyDerivationClause

getRowRelationshipDerivationClause

renameClause

propertyValue

PROFILE_REFERENCE Object

See Also