3.4 Creating and Editing Database Objects

You can create and edit objects using Create and Edit Object wizards available from the Navigator tab in the SQL and Data Modeler pages.

The wizards for creating and editing various object types are described in the following sections:

• The Table Properties Dialog

• The Index Properties Dialog

• The Sequence Properties Dialog

• The View Properties Dialog

• The Synonym Properties Dialog

• Implied Foreign Keys

• The Materialized View Log Properties Dialog

If you do not have any existing objects for a object type (such as tables), you can create objects by using the Object submenu icon, after first selecting the object type from the drop-down list. For all objects, the context menu includes Quick DDL, which saves the DDL statements for creating the object to a worksheet or to a text file. The various object types and the corresponding options are:

• Tables: Tables are used to hold data. Each table has multiple columns that describe the attributes of the database entity associated with the table, and each column has an associated data type.

  You can create new tables, edit existing tables, and add indexes by using the options available in the context menu. Or you can select Use as Template, which displays the dialog box with information for the selected object filled in. You should then change the object name and any other aspects of the definition, as needed, to create a new object of that type.

• Views: Views are virtual tables that select data from one or more underlying tables.

• Indexes: An index contains an entry for each value that appears in the indexed column of the table and provides direct, fast access to rows.

• Packages, Functions, Procedures: A package contains subprograms, which are programming objects that can be stored and executed in the database server, and called from other programming objects or applications. A package can contain functions or procedures, or both. Select Run from the context menu to insert the code for a selected function, procedure, or package into the worksheet.

• Triggers: Triggers are stored PL/SQL blocks associated with a table, a schema, the database, or anonymous PL/SQL blocks or calls to a procedure implemented in PL/SQL or Java.

• Types: A data type associates a fixed set of properties with the values that can be used in a column of a table or in an argument of a function or procedure.

• Sequences: Sequences are used to generate unique integers. You can use sequences to automatically generate primary key values.

• Materialized View: A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views.

• Materialized Views Logs: A materialized view log is a table associated with the master table of a materialized view. When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table.

• Synonyms, Public Synonyms: Synonyms provide alternative names for tables, views, sequences, procedures, stored functions, packages, materialized views, Java class database objects, user-defined object types, or other synonyms.

• Database Links: A database link is a database object in one database that enables you to access objects on another database. After you have created a database link, you can use it to refer to tables and views in the other database.

3.4.1 The Table Properties Dialog

The Table Properties Dialog is displayed when you create a table, edit an existing table, or create a table using an existing one as a template.

You can open the Table Properties dialog from the Navigator tab in SQL or Data Modeler.

To create a table for a specific schema, in the Navigator tab, select Tables from the object type drop-down list, click Object submenu , and select Create Object.

Figure 3-15 Create Object

Description of "Figure 3-15 Create Object"

To create a table from an existing one for a specific schema, right-click the table object in the Navigator tab, and select Use as Template as shown in Figure 3-16.

To edit a table for a specific schema, right-click a table object in the Navigator tab, and select Edit.

Figure 3-16 Edit Option for Existing Table

Description of "Figure 3-16 Edit Option for Existing Table"

The table properties are grouped in several panes.

If you are editing an existing table, you can visit the panes in any order. If you click Create before you finish creating the table, right-click the table name, select Edit, and continue creating the table.

Note:

Editing a partitioned table is not recommended. To identify whether a table is partitioned or not, right-click the table name and select Edit. If the table is partitioned, a warning message will be displayed.

Schema: Database schema in which to create the table. By default, a new table is created in the existing schema or the schema that you are logged into.

Name: Name for the table.

The different panes in the dialog are described in the following sections:

• Columns Pane

• Primary Key Pane

• Unique Keys Pane

• Indexes Pane

• Foreign Keys Pane

• Table Constraints Pane

• Comments Pane

• Storage Pane

• External Table Properties Pane

• Materialized View Pane

• DDL Pane

• Output Pane

3.4.1.1 Columns Pane

Specifies properties for each column in the table.

General tab

Lists the columns available in the table.

To add a column, click Add Column (+). A new row is added to the table below. Select the row and enter the details for the column.

To delete a column, select the row and click Remove Column (-). To move a column up or down in the table, select it and use the up-arrrow and down-arrow icons.

Figure 3-17 Add a Column

Description of "Figure 3-17 Add a Column"

• Name: Name for the column.

• Datatype: Data type for the column.

• Default: If no value is specified, the default value inserted into the column when a row is inserted.

• Default on NULL: Applicable for Oracle Database 12c and later releases. If this option is selected, when a row is inserted into the table and the value specified for the column is NULL, the default value is inserted into the column.

• Expression: Expression for computing the value in the column.

• Comments: Optional descriptive comments about the column. Use this field to provide descriptions for the attributes.

In the table:

• PK: If this option is selected, the column becomes the primary key.

• Identity Column: If this option is selected, the column becomes an identity column. This is applicable only for Oracle Database 12c and later releases. For more details, see the Identity Column tab.

Constraints tab

Displays the Not Null and Check Constraints for a column. A check constraint requires values in a column to comply with a specified condition.

• Not Null Constraint: Name: Name for the Not Null constraint.

• Not Null Constraint: Not Null: If this option is selected, the column must contain data. You cannot specify no value or an explicit null value for this column when you insert a row. If this option is not checked, the column can contain either data or no data. A primary key column cannot be null.

• Check Constraint: Name: Name for the check constraint definition.

• Check Constraint: Constraint: Condition that must be met for a column to fulfill the check constraint. You can use any valid CHECK clause (without the CHECK keyword). For example, to indicate that the value in a numeric column named RATING must be from 1 to 10, you can specify: rating >=1 and rating <= 10.

• Enabled: If this option is selected, the constraint is checked when data is entered or updated in the column.

• Deferrable: If this option is selected, you can defer checking the validity of the constraint until the end of a transaction.

• Initially Immediate: If this option is selected, the constraint is checked whenever you add, update, or delete data from the column.

• Validate: If this option is selected, the existing data is checked to see if it conforms to the constraint.

Identity Column tab

Applicable for Oracle Database 12c and later releases. The Identity Column tab lists the properties of the identity column. This tab becomes available only after the Identity Column checkbox is selected for the column in the General tab. An identity column is an autoincrement column that can be used to identify a table row. Only one identity column can be specified for a table.

• Generate: Always means that values cannot be explicitly included for the identity column in INSERT OR UPDATE statements, By Default means values for the identity column are generated automatically if no values are specified explicitly, By Default on Null means values are generated for the column only when a NULL value is supplied.

• Start with: Starting value of the sequence.

• Increment: Interval between successive numbers in a sequence.

• Min value: Lowest possible value for the sequence. The default is 1 for an ascending sequence and -(10^26) for a descending sequence.

• Max value: Highest possible value for the sequence. The default is 10^27 for an ascending sequence and -1 for a descending sequence.

• Cache and Cache size: Cache causes sequence values to be preallocated in cache, which can improve application performance; Cache size indicates the number of sequence values preallocated in cache. No Cache causes sequence values not to be preallocated in cache.

• Cycle: Indicates whether the sequence "wraps around" to reuse numbers after reaching its maximum value (for an ascending sequence) or its minimum value (for a descending sequence). If cycling of values is not enabled, the sequence cannot generate more values after reaching its maximum or minimum value.

• Order: Indicates whether sequence numbers are generated in the order in which they are requested. If No Order is specified, sequence numbers are not guaranteed to be in the order in which they were requested.

3.4.1.2 Primary Key Pane

Specifies the primary key for the table.

The primary key is the column, or set of columns, that uniquely identifies each row in the table. If the Primary Key checkbox is selected for a column in the General tab, the corresponding fields are automatically populated in the Primary Key pane. You can make changes to the properties as required.

An index is automatically created on the primary key.

• Name: Name of the constraint to be associated with the primary key definition.

• Enabled: If this option is checked, the primary key constraint is enforced: that is, the data in the primary key column (or set of columns) must be unique and not null.

• Index: Name of the index to which the primary key refers.

• Tablespace: Name of the tablespace associated with the index.

• Available Columns: Lists the columns that are available to be added to the primary key definition. You can select multiple attributes, if required, for the primary key.

• Selected Columns: Lists the columns that are included in the primary key definition.

To add a column to the primary key definition, select it in Available Columns and click the Add (>) icon; to remove a column from the primary key definition, select it in Selected Columns and click the Remove (<) icon. To move all columns from available to selected (or the reverse), use the Add All (>>) or Remove All (<<) icon. To move a column up or down in the primary key definition, select it in Selected Columns and use the arrow buttons.

3.4.1.3 Unique Keys Pane

Specifies one or more unique constraints for the table.

A unique constraint specifies a column, or set of columns, whose data values must be unique: each data value must not be null, and it must not be the same as any other value in the column.

To add a unique constraint, click the Add button; to delete a unique constraint, select it and click the Remove button.

• Name: Name of the unique constraint.

• Enabled: If this option is selected, the unique constraint is enforced.

• Rely: If this option is selected, the constraint in NOVALIDATE mode is taken into account during query rewrite.

• Deferrable: If this option is selected, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the SET CONSTRAINT(S) statement.

• Initially Immediate: If this option is selected, the constraint is checked at the end of each subsequent SQL statement.

• Validate: If the option is selected, the existing data is checked to see if it conforms to the constraint.

• Index: Name of the index to which the unique key refers.

• Tablespace: Name of the tablespace associated with the index.

• Available Columns: Lists the columns that are available to be added to the unique constraint definition.

• Selected Columns: Lists the columns that are included in the unique constraint definition.

To add a column to the unique constraint definition, select it in Available Columns and click the Add (>) icon; to remove a column from the unique constraint definition, select it in Selected Columns and click the Remove (<) icon. To move all columns from available to selected (or the reverse), use the Add All (>>) or Remove All (<<) icon. To move a column up or down in the unique constraint definition, select it in Selected Columns and use the arrow buttons.

3.4.1.4 Indexes Pane

Lists the indexes defined for the table.

To add an index, click Add Index (+); to delete an index, select it and click Remove Index (-).

• Name: Name of the index.

• Type: The type of Oracle index. Non-unique means that the index can contain multiple identical values; Unique means that no duplicate values are permitted; Bitmap stores rowids associated with a key value as a bitmap.

• Tablespace: Name of the tablespace for the index.

• Expression: A column expression is an expression built from columns, constants, SQL functions, and user-defined functions. When you specify a column expression, you create a function-based index.

• Available Columns and Selected Columns: Columns selected for the index. To select a column, click the column in the Available Columns box, and then click the click the Add Selected Columns icon to move it to the Selected Columns box.

3.4.1.5 Foreign Keys Pane

Specifies one or more foreign keys for the table.

A foreign key specifies a column ("local column"), whose data values match values in the primary key or unique constraint of another table.

• Name: Name of the foreign key definition.

• Enabled: If this option is checked, the foreign key is enforced.

• Rely, Deferrable, Initially Immediate, Validate: See the description of these fields in the Unique Keys pane.

• Referenced Constraint: Schema: Name of the schema containing the table with the primary key or unique constraint to which this foreign key refers.

• Referenced Constraint: Table: Name of the table with the primary key or unique constraint to which this foreign key refers.

• Referenced Constraint: Constraint: Name of the primary key or unique constraint to which this foreign key refers.

• Referenced Constraint: On Delete: Action to take automatically when a row in the referenced table is deleted and rows with that value exist in the table containing this foreign key: NO ACTION (shown by a crossing line in diagrams) performs no action on these rows; CASCADE (shown by an "X") deletes these rows; SET NULL (shown by a small circle) sets null all columns in those rows that can be set to a null value.

• Assocations: Local Column: Lists the column in the currently selected (local) table that is included in the foreign key definition. For each referenced column in the foreign key definition, select the name of a column in the edited table.

• Associations: Referenced Column: For each local column, identifies the column in the other (foreign) table that must have a value matching the value in the local column.

3.4.1.6 Table Constraints Pane

Specifies one or more check constraints for the table.

A check constraint specifies a condition that must be met when a row is inserted into the table or when an existing row is modified.

• Name: Name of the check constraint definition.

• Check Condition: Condition that must be met for a row to fulfil the check constraint. You can use any valid CHECK clause (without the CHECK keyword). For example, to indicate that the value in a numeric column named RATING must be from 1 to 10, you can specify rating >=1 and rating <= 10.

• Enabled: If this option is checked, the check constraint is enforced.

3.4.1.7 Comments Pane

Enter descriptive comments in this pane. This is optional.

3.4.1.8 Storage Pane

Enables you to specify storage options for the table.

When you create or edit a table or an index, you can override the default storage options.

• Organization: Specifies that the table is stored and organized with (Index) or without an index (Heap) or as an external table (External).

• Tablespace: Name of the tablespace for the table or index.

• Logging: ON means that the table creation and any subsequent INSERT operations against the table are logged in the redo log file. OFF means that these operations are not logged in the redo log file.

• Row Archival: YES enables in-database archiving, which allows you to archive rows within the table by marking them as invisible.

3.4.1.9 External Table Properties Pane

Specifies options for an external table.

An external table is a read-only table whose metadata is stored in the database but whose data in stored outside the database.

External Table

• Access Driver Type: Specifies the type of external table.

  • ORACLE_LOADER: Extracts data from text data files. This is the default access driver, which loads data from external tables to internal tables.

  • ORACLE_DATAPUMP: Extracts data from binary dump files. This access driver can perform both loads and unloads.

  • ORACLE_BIGDATA: Extracts data from Oracle Big Data Appliance.

  • ORACLE_HDFS: Extracts data stored in a Hadoop Distributed File System (HDFS).

  • ORACLE_HIVE: Extracts data stored in Apache HIVE.

• Default Directory: Specifies the default directory to use for all input and output files that do not explicitly name a directory object. The location is specified with a directory object, not a directory path.

• Access Params: Assigns values to the parameters of the specific access driver for the external table. Access parameters are optional.

  • OPAQUE_FORMAT_SPEC: The opaque_format_spec specifies all access parameters for the ORACLE_LOADER, ORACLE_DATAPUMP, ORACLE_HDFS, and ORACLE_HIVE access drivers. For descriptions of the access parameters, see Oracle Database Utilities. Field names specified in the opaque_format_spec must match columns in the table definition, else Oracle Database ignores them.

  • USING CLOB: Enables you to derive the parameters and their values through a subquery. The subquery cannot contain any set operators or an ORDER BY clause. It must return one row containing a single item of data type CLOB.

• Reject Limit: The number of conversion errors that can occur during a query of the external data before an Oracle Database error is returned and the query is aborted.

• Project Column: Determines how the access driver validates the rows of an external table in subsequent queries.

  • ALL: Processes all column values, regardless of which columns are selected, and validates only those rows with fully valid column entries. If any column value raises an error, such as a data type conversion error, the row is rejected even if that column was not referenced in the select list of the query.

  • REFERENCED: Processes only those columns in the select list of the query.

  The ALL setting guarantees consistent result sets. The REFERENCED setting can result in different numbers of rows returned, depending on the columns referenced in subsequent queries, but is faster than the ALL setting. If a subsequent query selects all columns of the external table, then the settings behave identically.

• Location: Specifies the data files for the external table. Use the Add (+) icon to add each location specification.

  • For ORACLE_LOADER and ORACLE_DATAPUMP, the files are named in the form directory:file. The directory portion is optional. If it is missing, then the default directory is used as the directory for the file. If you are using the ORACLE_LOADER access driver, then you can use wildcards in the file name. An asterisk (*) signifies multiple characters and a question mark (?) signifies a single character.

  • For ORACLE_HDFS, LOCATION  is a list of Uniform Resource Identifiers (URIs) for a directory or for a file. There is no directory object associated with a URI.

  • For ORACLE_HIVE, LOCATION  is not used. Instead, the Hadoop HCatalog table is read to obtain information about the location of the data source (which could be a file or another database).

Opaque Format Spec

Specifies all access parameters for the ORACLE_LOADER, ORACLE_DATAPUMP, ORACLE_HDFS, and ORACLE_HIVE access drivers.

For example:

RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    TERRITORY AMERICAN
    BADFILE log_file_dir:'ext_1v3.bad'
    LOGFILE log_file_dir:'ext_1v3.log'
    FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '^' LDRTRIM
    ( PROD_ID,
      CUST_ID ,
      TIME_ID DATE(10) "YYYY-MM-DD",
      CHANNEL_ID ,
      PROMO_ID ,
      QUANTITY_SOLD ,
      AMOUNT_SOLD ,
      UNIT_COST ,
      UNIT_PRICE
    )

and the full statement:

CREATE TABLE SH.SALES_TRANSACTIONS_EXT
    (
      PROD_ID NUMBER ,
      CUST_ID NUMBER , 
      TIME_ID DATE ,
      CHANNEL_ID NUMBER ,
      PROMO_ID NUMBER ,
      QUANTITY_SOLD NUMBER , 
     AMOUNT_SOLD NUMBER (10,2) , 
     UNIT_COST NUMBER (10,2) , 
     UNIT_PRICE NUMBER (10,2)
     )
     ORGANIZATION EXTERNAL
     (
     TYPE ORACLE_LOADER
     DEFAULT DIRECTORY DATA_FILE_DIR
     ACCESS PARAMETERS
     (
     RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    TERRITORY AMERICAN
    BADFILE log_file_dir:'ext_1v3.bad'
    LOGFILE log_file_dir:'ext_1v3.log'
    FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '^' LDRTRIM
    ( PROD_ID ,
      CUST_ID ,
      TIME_ID DATE(10) "YYYY-MM-DD",
      CHANNEL_ID ,
      PROMO_ID ,
      QUANTITY_SOLD ,
      AMOUNT_SOLD ,
      UNIT_COST ,
      UNIT_PRICE
    )
    )
     LOCATION ( "DATA_FILE_DIR":'sale1v3.dat')
     )
     REJECT LIMIT 100
 ;

CLOB Subquery

Type or copy and paste the query.

Note:

For more information about the external table fields, see Oracle Database Utilities and Oracle Database SQL Language Reference

3.4.1.10 Materialized View Pane

Specifies options for a materialized view.

Query: Contains the SQL code for the query part of the view definition. Type or copy and paste the query.

General

• On Pre-built Table: If Yes, an existing table is registered as a preinitialized materialized view. This option is particularly useful for registering large materialized views in a data warehousing environment. The table must have the same name and be in the same schema as the resulting materialized view, and the table should reflect the materialization of a subquery.

• Reduced Precision: Yes authorizes the loss of precision that will result if the precision of the table or materialized view columns do not exactly match the precision returned by the subquery. If No , the precision of the table or materialized view columns must exactly match the precision returned by the subquery, or the create operation will fail.

• For Update: Select Yes to allow a subquery, primary key, object, or rowid materialized view to be updated. When used in conjunction with Advanced Replication, these updates will be propagated to the master.

• Real Time MV: Select Yes to create a real-time materialized view or a regular view. A real-time materialized view provides fresh data to user queries even when the materialized view is not in sync with its base tables due to data changes. Instead of modifying the materialized view, the optimizer writes a query that combines the existing rows in the materialized view with changes recorded in log files (either materialized view logs or the direct loader logs). This is called on-query computation.

• Query Rewrite: If Enable, the materialized view is enabled for query rewrite, which transforms a user request written in terms of master tables into a semantically equivalent request that includes one or more materialized views.

• Build: Specifies when to populate the materialized view. Immediate indicates that the materialized view is to be populated immediately. Deferred indicates that the materialized view is to be populated by the next refresh operation. If you specify Deferred, the first (deferred) refresh must always be a complete refresh; until then, the materialized view has a staleness value of unusable, so it cannot be used for query rewrite.

• Use Index: If Yes, a default index is created and used to speed up incremental (fast) refresh of the materialized view. If No, this default index is not created. (For example, you might choose to suppress the index creation now and to create such an index explicitly later.)

• Index Tablespace: Specifies the tablespace in which the materialized view is to be created. If a tablespace is not selected, the materialized view is created in the default tablespace of the schema containing the materialized view.

• Cache: If Yes, the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This setting is useful for small lookup tables. If No, the blocks are placed at the least recently used end of the LRU list.

Refresh Clause

• Refresh: Select Yes to enable refresh operations.

• Refresh Type: The method of refresh operation to be performed:

  • Complete Refresh: Executes the defining query of the materialized view, even if a fast refresh is possible.

  • Fast Refresh: Uses the incremental refresh method, which performs the refresh according to the changes that have occurred to the master tables. The changes for conventional DML changes are stored in the materialized view log associated with the master table. The changes for direct-path INSERT operations are stored in the direct loader log.

  • Force Refresh: Performs a fast refresh if one is possible; otherwise, performs a complete refresh.

• Action: The type of refresh operation to be performed:

  • On Demand: Performs a refresh when one of the DBMS_MVIEW refresh procedures are called.

  • On Commit: Performs a fast refresh whenever the database commits a transaction that operates on a master table of the materialized view. This may increase the time taken to complete the commit, because the database performs the refresh operation as part of the commit process.

  • Specify: Performs refresh operations according to what you specify in the Start on and Next fields.

• Start Date: Starting date and time for the first automatic refresh operation. Must be in the future.

• Next Date: Time for the next automatic refresh operation. The interval between the Start on and Next times establishes the interval for subsequent automatic refresh operations. If you do not specify a value, the refresh operation is performed only once at the time specified for Start on.

• With: Refresh type, which determines the type of materialized view:

  • Primary Key: Creates a primary key materialized view, which allows materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.

  • Row ID: Creates a rowid materialized view, which is useful if the materialized view does not include all primary key columns of the master tables.

• Default Storage: If Yes, DEFAULT specifies that Oracle Database will choose automatically which rollback segment to use. If you specify DEFAULT, you cannot specify the rollback_segment. DEFAULT is most useful when modifying, rather than creating, a materialized view.

• Storage Type: MASTER specifies the remote rollback segment to be used at the remote master site for the individual materialized view. LOCAL specifies the remote rollback segment to be used for the local refresh group that contains the materialized view. This is the default.

• Rollback Segment: Enter the name of the rollback segment.

• Using Constraint: If this option is checked, more rewrite alternatives can be used during the refresh operation, resulting in more efficient refresh execution. The behavior of this option is affected by whether you select Enforced or Trusted.

  • Enforced: Causes only enforced constraints to be used during the refresh operation.

  • Trusted: Enables the use of dimension and constraint information that has been declared trustworthy by the database administrator but that has not been validated by the database. If the dimension and constraint information is valid, performance may improve. However, if this information is invalid, then the refresh procedure may corrupt the materialized view even though it returns a success status.

3.4.1.11 DDL Pane

You can review and save the SQL statements that are generated when creating or editing the object. If you want to make any changes, go back to the relevant panes and make the changes there.

• For a new table, click CREATE to view the generated DDL statements.

• When you edit table properties, click UPDATE to view the generated ALTER statements. For a new table, the UPDATE tab will not be available.

When you are finished, click Apply.

3.4.1.12 Output Pane

Displays the results of the DDL commands. If there are any errors, go to the appropriate pane, fix the errors, and run the commands again. You can save to a text file or clear the output.

3.4.2 The Index Properties Dialog

The Index Properties dialog box is displayed when you create or edit an index.

To create an index for a selected schema, in SQL, in the Navigator tab, select Indexes from the object type drop-down list, click Object submenu , and select Create Object.

To edit an index for a selected schema, right-click a table object in the Navigator tab, and select Edit.

Definition pane

• Schema: Database schema that owns the table associated with the index.

• Table: Name of the table associated with the index.

• Schema: Database in which to create the index.

• Tablespace: Tablespace for the index.

• Name: Name of the index.

• Type: The type of Oracle index.

  • Non-unique means that the index can contain multiple identical values.

  • Unique means that no duplicate values are permitted.

  • Bitmap stores rowids associated with a key value as a bitmap.

• Expression: A column name or column expression. A column expression is an expression built from columns, constants, SQL functions, and user-defined functions. When you specify a column expression, you create a function-based index.

• Available Columns: Columns available in the table.

• Selected Columns: Columns selected for the index. Click Add Selected Columns > to move columns from the Available Columns list.

• Order: ASC for an ascending index (index values sorted in ascending order); DESC for a descending index (index values sorted in descending order).

DDL pane

You can review and save the SQL statements that are generated when creating or editing the index. If you want to make any changes, go back to the Definition pane and make the changes there.

• For a new index, click CREATE to view the generated DDL statements.

• When you edit index properties, click UPDATE to view the generated ALTER statements. For a new index, the UPDATE tab will not be available.

When you are finished, click Apply.

Output pane

Displays the results of the DDL commands. If there are any errors, go to the Definition pane, fix the errors, and run the commands again. You can save to a text file or clear the output.

3.4.3 The Sequence Properties Dialog

The Sequence Properties Dialog is displayed when you create or edit a sequence.

To create a sequence for a selected schema, in SQL, in the Navigator tab, select Sequences from the object type drop-down list, click Object submenu , and select Create Object.

To edit a sequence for a selected schema, right-click a sequence object in the Navigator tab and select Edit.

A sequence is an object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

Properties pane

• Schema: Database schema in which to create the sequence.

• Name: Name of the sequence.

• Start with: Starting value of the sequence.

• Increment: Interval between successive numbers in a sequence.

• Min value: Lowest possible value for the sequence. The default is 1 for an ascending sequence and -(10^26) for a descending sequence.

• Max value: Highest possible value for the sequence. The default is 10^27 for an ascending sequence and -1 for a descending sequence.

• Cache and Cache size: Cache causes sequence values to be preallocated in cache, which can improve application performance; Cache size indicates the number of sequence values preallocated in cache. No Cache causes sequence values not to be preallocated in cache.

• Cycle: Indicates whether the sequence "wraps around" to reuse numbers after reaching its maximum value (for an ascending sequence) or its minimum value (for a descending sequence). If cycling of values is not enabled, the sequence cannot generate more values after reaching its maximum or minimum value.

• Order: Indicates whether sequence numbers are generated in the order in which they are requested. If No Order is specified, sequence numbers are not guaranteed to be in the order in which they were requested.

DDL pane

You can review and save the SQL statements that are generated when creating or editing the sequence. If you want to make any changes, go back to the Properties pane and make the changes there.

• For a new sequence, click CREATE to view the generated DDL statements.

• When you edit a sequence, click UPDATE to view the generated ALTER statements. For a new sequence, the UPDATE tab will not be available.

When you are finished, click Apply.

Output pane

Displays the results of the DDL commands. If there are any errors, go to the Properties pane, fix the errors, and run the commands again. You have save to a text file or clear the output.

3.4.4 The View Properties Dialog

The View Properties Dialog is displayed when you create or edit a view.

You can open the View Properties Dialog from the Navigator tab in SQL or Data Modeler.

To create a view for a selected schema, in SQL, in the Navigator tab, select Views from the object type drop-down list, click Object submenu , and select Create Object.

To create a view from an existing template for a selected schema, in the Navigator tab, select the view to create from, right-click and select Use as Template.

To edit a view for a selected schema, right-click a view object in the Navigator pane, and select Edit.

Schema: Database schema in which to create the view.

Name: Name of the view.

The different panes in the dialog are described in the following sections:

SQL Query pane

Enter or copy and paste the SQL query for the view, using the SELECT and FROM keywords along with the syntax needed to retrieve the desired information. A semicolon is not required after the query.

Columns pane

Click Refresh Columns to automatically populate the columns in this pane. You can edit the columns by selecting the required row and making changes in the Header Alias and Comments fields.

Storage pane

• Force on Create: Select Yes to create the view regardless of whether the base tables of the view or the referenced object types exist or the owner of the schema containing the view has privileges on them. These conditions must be true before any SELECT, INSERT, UPDATE, or DELETE statements can be issued against the view. If the view definition contains any constraints, CREATE VIEW ... FORCE fails if the base table does not exist or the referenced object type does not exist. CREATE VIEW ... FORCE also fails if the view definition names a constraint that does not exist.

• Query Restriction: Read Only prevents the view from being used to add, delete, or change data in the underlying table. Check Option prohibits any changes to the underlying table that would produce rows that are not included in this view.

Use the Primary Key, Unique Keys, Foreign Keys, and Comments panes to add or edit properties as required.

DDL pane

Based on the inputs provided, the DDL statements are generated. You can review and save the SQL statements. If you want to make any changes, go back to the relevant pane and make the changes there.

• For a new view, click CREATE to view the generated DDL statements.

• When you edit a view, click UPDATE to view the generated ALTER statements. For a new view, the UPDATE tab will not be available.

When you are finished, click Apply.

Output pane

Displays the results of the DDL commands. If there are any errors, go to the respective pane, fix the errors, and run the commands again. You can save to a text file or clear the output.

3.4.5 The Synonym Properties Dialog

The Synonym Properties Dialog is displayed when you create a synonym.

There are two ways of creating a synonym for a selected schema:

• In SQL, in the Navigator tab, right-click the object for which you want to create the synonym, and select Add Synonym. In this case, the only fields that you can edit in the Properties pane are Public and Synonym Name. The values of the remaining fields are predetermined by the object selected.

• In SQL, in the Navigator tab, select the object type as Synonyms or Public Synonyms from the drop-down list. Click Object submenu, and select Create Object. All the fields in the Properties dialog are available for edit.

The different panes in the dialog are described in the following sections:

Properties pane

• Public: If this option is checked, the synonym is accessible to all users. However, each user must have appropriate privileges on the underlying object to use the synonym. If this option is not checked, the synonym is a private synonym, and is accessible only within its schema.

• Synonym Schema: Database schema in which to create the synonym.

• Synonym Name: Name of the synonym. A private synonym must be unique within its schema; a public synonym must be unique within the database.

• Object Type: Specify the type of object to which this synonym refers.

• Object Schema: Schema containing the object or name to which this synonym refers.

• DB Filter: After selecting the Object Type and Object Schema, the list of objects of the selected type may be very long. To filter the object names, enter the search entry and click Refresh . The Object Name field is auto-filled with appropriate object names in the drop-down list.

• Object Name: Select the name of the object to which this synonym refers.

• DB Link: Enter a complete or partial database link to create a synonym for a schema object on a remote database where the object is located. If you specify DB Link and omit schema, then the synonym refers to an object in the schema specified by the database link. Oracle recommends that you specify the schema containing the object in the remote database. If you omit DB Link, then Oracle Database assumes the object is located on the local database.

DDL pane

Based on the inputs provided, the DDL statements are generated. You can review and save the SQL statements. If you want to make any changes, go back to the relevant pane and make the changes there.

• For a new view, click CREATE to view the generated DDL statements.

• When you edit a view, click UPDATE to view the generated ALTER statements. For a new view, the UPDATE tab will not be available.

When you are finished, click Apply.

Output pane

Displays the results of the DDL commands. If there are any errors, go to the respective pane, fix the errors, and run the commands again. You can save to a text file or clear the output.

3.4.6 Implied Foreign Keys

Implied foreign keys are dependencies that exist between tables but are not defined in the database. In a data warehouse environment, it is a common practice not to create foreign keys. However, it becomes necessary to show these dependencies for presentation or reporting purposes.

You can display implied foreign keys for objects in a star schema by defining them or by discovering them in the data dictionary. It is possible to have more than one source for implied foreign keys.

Define Implied Foreign Keys

You can defined implied foreign keys in two ways:

• Using the Implied Foreign Keys dialog

• By dragging the arrow to the referenced object in the diagram

Using the Implied Foreign Keys Dialog

You can define implied foreign keys using the Implied Foreign Keys dialog in Data Modeler.

1. In a Data Modeler diagram, right-click an object (table or view) and select Implied Foreign Keys.

   Figure 3-18 Select Implied Foreign Keys for an Object

   
   Description of the illustration select_implied_foreign_keys.png

   The Implied Foreign Keys dialog is displayed.

2. In the Implied Foreign Keys dialog, click + to add an entry in the grid.

3. Select the entry in the grid to enable and enter values in the following fields:

   • Referenced Object: Object in the diagram that has a dependency to the source object.

   • Local Column: Name of the column in the source object.

   • Referenced Column: Name of the column in the targeted object.

   • Discovery Sources: Automatically prefilled, displays whether the implied foreign keys have been defined or were discovered in the data dictionary.

4. Click OK. The implied foreign key dependency is displayed with a dotted line on the diagram.

   Figure 3-19 Dotted Line Between Two Objects

   

By Dragging the Arrow to the Referenced Object in the Diagram

You can also define an implied foreign key in the following way:

1. Select the source object on the diagram.

2. Click and drag the small curved arrow with a blue indicator to the referenced object. The dependency will be displayed with a dotted line on the diagram.

   Figure 3-20 Drag Blue Indicator

   
   Description of "Figure 3-20 Drag Blue Indicator"

3. Right-click the source object and enter the column names in the Implied Foreign Keys dialog.

Discover Implied Foreign Keys in the Data Dictionary

A star schema can be discovered by searching for several types of definitions in the data dictionary.

In the Navigator tab, right-click an object (table or view) and select Add Object as Star Schema to Diagram. The object must be a fact table. The data dictionary is then searched for joins and dependencies related to the object, such as:

• Foreign keys defined for the selected table to other tables. If implied foreign keys are later discovered for the same columns, they will not be displayed on the diagram.

• Joins used in the definitions of bitmap join indexes, materialized views with aggregates, and attribute clustering.

• Dependencies based on dimension definitions and column name matching in fact table.

• Fact and dimension definitions for Analytic views and OLAP cube and dimensions.

3.4.7 The Materialized View Log Properties Dialog

The Materialized View Log Properties dialog is displayed when you create or edit a materialized view log, which is a table associated with the master table of a materialized view.

To create a materialized view log for a selected schema, in SQL, in the Navigator tab, select Materialized View Logs from the object type drop-down list, click Object submenu , and select Create Object.

To edit, right-click a materialized view log object in the Navigator pane and select Edit.

Schema: Database schema in which to create the materialized view log.

Table: Name of the master table of the materialized view to be associated with this materialized view log.

Properties tab

• Row ID Logged: Yes indicates that the rowid of all rows changed should be recorded in the materialized view log. No indicates that the rowid of all rows changed should not be recorded in the materialized view log.

• PK Logged: Yes indicates that the primary key of all rows changed should be recorded in the materialized view log; No indicates that the primary key of all rows changed should not be recorded in the materialized view log.

• New values: Yes saves both old and new values for update DML operations in the materialized view log; No disables the recording of new values in the materialized view log. If this log is for a table on which you have a single-table materialized aggregate view, and if you want the materialized view to be eligible for fast refresh, you must specify Yes.

• Object ID Logged: For a log on an object table only: Yes indicates that the system-generated or user-defined object identifier of every modified row should be recorded in the materialized view log. No indicates that the system-generated or user-defined object identifier of every modified row should not be recorded in the materialized view log.

• Cache: For data that will be accessed frequently, CACHE specifies that the blocks retrieved for this log are placed at the most recently used end of the least recently used list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables. NOCACHE specifies that the blocks are placed at the least recently used end of the LRU list.

• Parallel: If YES, parallel operations will be supported for the materialized view log.

• Sequence Logged: Yes indicates that a sequence value providing additional ordering information should be recorded in the materialized view log. No indicates that a sequence value providing additional ordering information should not be recorded in the materialized view log. Sequence numbers (that is, Yes for this option) are necessary to support fast refresh after some update scenarios.

• Commit SCN: If this option is enabled, the database is instructed to use commit SCN data rather than timestamps.

• Available Columns and Selected Columns: Additional columns, which are non-primary-key columns referenced by subquery materialized views, to be recorded in the materialized view log. To select one or more filter columns, use the arrow buttons to move columns from Available to Selected.

Storage tab

• Tablespace: Tablespace in which the materialized view log is to be created

• Logging: YES or NO, to establish the logging characteristics for the materialized view log.

• Buffer Mode: Select KEEP to put blocks from the segment into the KEEP buffer pool. Select RECYCLE to put blocks from the segment into the RECYCLE pool. Select DEFAULT to indicate the default buffer pool.

• Percent Free: Specify a whole number representing the percentage of space in each data block of the database object reserved for future updates to rows of the object. The value of PCTFREE must be a value from 0 to 99.

• Percent Used: Specify a whole number representing the minimum percentage of used space that Oracle maintains for each data block of the database object. PCTUSED is specified as a positive integer from 0 to 99 and defaults to 40.

• Initrans: Specify the initial number of concurrent transaction entries allocated within each data block allocated to the database object. This value can range from 1 to 255 and defaults to 1.

• Freelists: In tablespaces with manual segment-space management, for objects other than tablespaces and rollback segments, specify the number of free lists for each of the free list groups for the table, partition, cluster, or index. The default and minimum value for this parameter is 1, meaning that each free list group contains one free list.

• Freelist Groups: In tablespaces with manual segment-space management, specify the number of groups of free lists for the database object you are creating.

• Initial Extent: Specify the size of the first extent of the object.

• Next Extent: Specify in bytes the size of the next extent to be allocated to the object.

• Percent Increase: In locally managed tablespaces, Oracle Database uses the value of PCTINCREASE during segment creation to determine the initial segment size and ignores this parameter during subsequent space allocation.

• Min Extent: In locally managed tablespaces, Oracle Database uses the value of MINEXTENTS in conjunction with PCTINCREASE, INITIAL and NEXT to determine the initial segment size.

• Max Extent: This storage parameter is valid only for objects in dictionary-managed tablespaces. Specify the total number of extents, including the first, that Oracle can allocate for the object.

• Unlimited: Select this option if you want extents to be allocated automatically as needed. Oracle recommends this setting as a way to minimize fragmentation.

Purge tab

• Type: In IMMEDIATE SYNCHRONOUS, the materialized view log is purged immediately after refresh. This is the default. In IMMEDIATE ASYNCHRONOUS, the materialized view log is purged in a separate Oracle Scheduler job after the refresh operation.

• Deferred, Start With, Next, Repeat Interval: Sets up a scheduled purge that is independent of the materialized view refresh and is initiated during CREATE or ALTER MATERIALIZED VIEW LOG statement.

Refresh tab

• Type: Synchronous Refresh creates a staging log that can be used for synchronous refresh. Specify the name of the staging log to be created. The staging log will be created in the schema in which the master table resides. Fast Refresh creates a materialized view log that can be used for fast refresh. The materialized view log will be created in the same schema in which the master table resides. This is the default.

DDL pane

Based on the inputs provided, the DDL statements are generated. You can review and save the SQL statements. If you want to make any changes, go back to the relevant pane and make the changes there.

• For a new materialized view log, click CREATE to view the generated DDL statements.

• When you edit a materialized view log, click UPDATE to view the generated ALTER statements. For a new materialized view log, the UPDATE tab will not be available.

When you are finished, click Apply.

Output pane

Displays the results of the DDL commands. If there are any errors, go to the respective pane, fix the errors, and run the commands again. You can save to a text file or clear the output.