Forward Engineer/Alter Script › Check Your Model/Validate SQL › Validation Criteria for Oracle Models

Validation Criteria for Oracle Models

The Check Model/Validate SQL option allows you to reduce occurrences of syntax errors during forward engineering. For Oracle physical models, the following objects and properties are validated during the Check Model process:

• Tables

• At least 1 column.
• Only 1 LONG column.
• LOB segments containing more than 1 column are unnamed.

• Clustered tables

• No cluster columns specified.
• Number of cluster columns do not match the number of columns in the cluster.
• Datatype of table cluster column matches datatype of cluster column (only data type is checked, not precision/scale).

• Index organized tables

• No LONG columns.
• Primary key constraint exists.
• Overflow is specified if a UROWID column exists.
• Overflow is specified if there is a LOB with enable storage in row.
• Pctused is not specified.
• Partition columns are part of the primary key.

• External Tables

• Column data types are valid.
• No constraints.
• No defaults on columns.
• No LOBs.

• Materialized Views

• Table specified in select statement has a primary key.
• LOB segments containing more than 1 column are unnamed.
• Refresh Fast valid only if 1 master table and materialized view log exists on master table.
• Refresh Fast with Primary Key is not valid if materialized view log is recording only rowed.
• Refresh Fast with Rowid is valid only if materialized view log is recording rowid.

• Clustered materialized views

• Clustered materialized views have no LOB columns.

• Index organized materialized views

• Only 1 master table.
• Primary key constraint exists.

• Materialized Views on prebuilt tables

• Table in select statement is has a different name from the view.
• No LONG columns.

• Indexes

• Compress Prefix length for unique index is greater than or equal to 1 and less than the number of key columns.
• Compress Prefix length for non-unique index is greater than or equal to 1 and less than or equal to the number of key columns.
• Function bitmap index not valid on index organized table.
• Index members have valid datatypes.
• No duplicate indexes.

• Partitions

• Partition names are unique.
• Partition columns reference attributes in the table.
• Partition columns are of valid datatype.
• Partition values are specified for Composite, Range and List partitions.
• LOB segments containing more than 1 column are unnamed.

• Composite Partitions

• If a subpartition template is specified, partitions do not specify STORE IN or SUBPARTITIONS clauses.
• If a subpartition template is specified, all subpartitions must specify a tablespace if any do.
• Subpartition templates must all contain the same LOB segments.
• Subpartition templates must have unique LOB segment names.

• Range Partitions

• MAXVALUE is specified for the last partition's values.

• List Partitions

• DEFAULT is specified for the last partition's value

• Index Global Partition

• Partition columns are a prefix of the index members.
• MAXVALUE is specified for the last partition's values when a range partition.

• Index Local Partition

• Number of partitions matches the number of table partitions.
• Number of subpartitions matches the number of table subpartitions.
• Partition type matches the partition type of the table.
• For a unique index, the table partition and subpartition columns are part of the index.

• Views

• Foreign key constraint specified with RELY requires the primary key of the parent table to have RELY specified.

• Materialized View Logs

• Parent table contains a primary key.
• Selected columns can not be part of the primary key if primary key is selected.
• Partition columns must be log filtering columns or part of the primary key of the master table.

• Clusters

• If single table only 1 table is attached to the cluster.
• If sorted hash cluster no more than 2 tables are attached to the cluster.
• If single table or hash expression is specified, hash keys is also specified

• Relationships

• Temporary table is not a child table.
• If parent table is a temporary table, child table must be a view or materialized view.
• If relationship is from a table to materialized view, only one can be clustered.

• Sequences

• The Min value is less than or equal to Initial value.
• The Min value is less than the Max value.
• The Max value is greater than the Initial value.
• The Increment is less than the range of values.
• If Cycle specified – Max value must be specified for ascending sequence, Min value must be specified for descending sequence.
• If Cycle specified – number of possible values is greater than the number of values in cycle.
• If Max On specified – Max value must be specified.
• If Min On specified – Min value must be specified.
• If Cache On specified – Cache value must be specified.

• Rollback Segment

• Referenced tablespace is marked online.

• Disk Group

• Normal redundancy has at least 2 fail groups.
• High redundancy has as least 3 fail groups.
• External redundancy has only 1 fail group which is unnamed.

• DB Link

• Shared links specify an authorized user.
• Non-shared links do not specify an authorized user.

• Supplemental Log Group

• Contains at least 1 log column.