Oracle® Warehouse Builder OMB*Plus Command Reference 11g Release 2 (11.2) Part Number E14406-01 |
|
|
Mobi · ePub |
To alter a profile reference, such as to add or delete a data rule usage, or set properties on a profile attribute.
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" )
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.
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.
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.
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.
Derive a foreign key data rule usage from a profile foreign key.
Derive a functional dependency data rule usage from a profile functional dependency.
Derive a row relationship data rule usage for an attribute from a profile row relationship.
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. |