Oracle Enterprise Manager Administrator's Guide

Library

Product

Contents

Index

Managing Schema Objects

Starting Schema Manager
Schema Menus
Schema Objects Folder
Schema Property Sheets
Clusters Folder
Database Links Folder
Functions Folder
Indexes Folder
Package Bodies Folder
Packages Folder
Procedures Folder
Refresh Groups Folder
Sequences Folder
Snapshot Logs Folder
Snapshots Folder
Synonyms Folder
Tables Folder
Triggers Folder
Views Folder

This chapter describes how to use Schema Manager to administer schema objects in your database. This chapter assumes that you have read Chapter 7, Overview of the Database Tools and are familiar with the interface elements of the database tools.

With Schema Manager, you can create, alter, or drop schema objects such as clusters, database links, execution plans, PL/SQL, sequences, snapshots, snapshot logs, synonyms, tables, and views.

The topics in this chapter are:

Starting Schema Manager

To start Schema Manager, click on the Schema Manager icon in the Launch palette or choose Schema Manager from the Tools menu.

If you have already connected to a database, the Schema window appears.
If a valid database connection has not been made, the Connect dialog box appears. See Connecting to an Instance on page 1-17.

Note:
You can change the database connection with the Change Database Connection option in the File menu. For information, see Application Menus on page 7-7.

After Schema Manager successfully connects to a database, the schema navigator displays on the left side of Schema Manager window. The navigator consists of a series of schema object containers, one for each type schema object contained in the main Schema Objects folder. The name of the connected database is displayed next to the Schema Objects folder.

The display on the right side of the window is determined by the objects selected on the left side of the screen. The right side may contain a multi-column scrolling list, property sheet.

For general information, see:

An example of a Schema Manager window is shown in Figure 11-1: Schema Manager Window.

Figure 11-1: Schema Manager Window

Schema Menus

Schema manager includes four standard menus, File, View, Log, and Help, plus the Object menu. The Object menu contains the following menu options:

Create to create a new schema object.
Create Like to create a new schema object based on the currently selected object in the navigator.
Remove to remove the selected schema object in the navigator.
Grant Privilege on to grant privileges for a schema object to users and roles.
Create Synonym for to create a new synonym for a schema object.
Create Index on to create an index on a table.
Truncate to remove all rows from a partition in a table. (Oracle 8)
Make Local Indexes Unusable to mark the local index partitions corresponding to a table partition unusable.
Rebuild Unusable Local Indexes to rebuild all unusable local index partitions corresponding to a table partition. This operation is not parallelized.
Merge Partition to combine adjacent partitions.

Schema Objects Folder

The Schema Objects folder contains all database objects pertinent to database schema. Specific schema object types are listed alphabetically under the Schema Object folder.

Alternatively, you can view the navigator by specific schemas defined within the connected database rather than by schema type. To view the navigator by defined schemas, select By Schema from the View menu. All schemas defined in the database are listed alphabetically in the navigator as containers. You expand the specific schema folder in the navigator to view the complete list of schema object types defined for that schema.

Schema Property Sheets

There are two property pages that are used in several schema object property sheets. They are available when AdvancedMode is selected from the View menu. The common property sheets are as follows:

Storage
Options

Storage

The Storage page allows you to specify database storage parameters for a particular schema object. This page contains the following information:

Use Cluster Parameters (Table Property Sheet only)
Indicates that the storage parameters for the table being defined be obtained from the cluster to which the table belongs. The setting of this control is automatically determined based on whether or not the table is a member of a cluster.

Tablespace
Name of the tablespace that contains the database object being defined.

Extents
Initial Size: Size of the object's first extent. You can enter a value, the minimum being the size of one data block. The default value is the size of five data blocks.

Next Size: Size of the next extent to be allocated to the object. The default value is the size of five datablocks. The smallest permissible value is the size of one datablock.

Increase Size by: Percent by which each extent grows (after the second extent) compared to the previous extent.

Minimum Number: Total number of extents allocated when a segment is created. The default value is one. You can enter a value of one or greater.

Maximum Number: Total number of extents, including the first, that an Oracle database can allocate for the object. There are two extent options available, Unlimited and Value.

Unlimited: When selected, allows you to create a number of extents that is only limited by the amount of contiguous free space in a tablespace.
Value: When selected, allows you to specify the maximum number of extents. If you do not specify a value, the default value applies. The default and maximum values depend on the data block size. You can enter a value of one or greater. The default value varies.

Options

Use Cluster Parameters (Table Property Sheet only)
Indicates that the storage parameters for the table being defined will be obtained from the cluster to which the table belongs. The setting of this control is automatically determined based on whether or not the table is a member of a cluster.

Space Usage
% Free: Percentage of space in each of the object's data blocks that is reserved for future updates to the object. You can enter values from 0 to 99.

% Used: Minimum percentage of used space that an Oracle database maintains for each data block of the object. A block becomes a candidate for row insertions when its used space falls below the % Used value. You can enter a value from 1 to 99. The default value is 40.

Number of Transactions
Initial: Initial number of transaction entries allocated within each data block allocated to the object. You can enter a value from 1 or 2 (for clusters and indexes) to 255.

Maximum: Maximum number of concurrent transactions that can update a data block allocated to the object. You can enter a value from 1 to 255.

Freelists
Lists: Number of free lists for each of the free list groups for the table, cluster, or index. You can enter a value of one or greater. The default value is one.

Groups: Number of groups of free lists for a table, cluster, or index. You can enter a value of one or greater. The default value is one.

Parallel
Degree: The degree of parallelism when creating an index using the parallel query option.

Instances: Maximum number of instances allowed to participate in parallel query processing.

Clusters Folder

The Clusters folder consolidates information about clustered tables.

A cluster is a group of tables that share the same data blocks because they share common columns. Clustered tables are often joined in queries.

Clusters Multi-Column List

The Clusters multi-column list displays when the Clusters folder is selected from the navigator. The Clusters multi-column list contains the following columns:

Schema
Name of the schema to which the cluster belongs. This column is not shown when viewing a particular schema.

Cluster
Name of the cluster.

Type
Type of cluster (index or hash).

Tablespace
Tablespace in which the cluster is stored.

Creating a Cluster

To create a new cluster:

1.: Choose Create from the Object menu. The Create Object dialog box appears.
2.: Select Cluster from the Create Object list.
3.: Click OK. The Create Cluster property sheet appears.

The Cluster property sheet contains the following pages:

General
Storage (Advanced Mode only, see Storage on page 11-4)
Options (Advanced Mode only, see Options on page 11-5)

General

The General page contains the following:

Cluster
Name of the cluster being defined.

Schema
Name of the schema containing the cluster. The drop-down list contains all schemas available in the current database.

Size
An estimate of the total amount of space required to store the average number of rows associated with each cluster key value or hash value (kilobytes or megabytes).

Type: Index Cluster
When selected, indicates that an index is specifically defined for the cluster.

Type: Hash Cluster
When selected, indicates that related rows are stored in the same data block.

Hashkeys: Maximum number of unique hash values that can be generated by the hash function.

Hash Function: Specify that Oracle's internal hash function use the current function (Default) or bypass the Oracle internal hash function and specify a SQL expression (Expression). You enter the SQL expression in the text entry field.

Cluster Key Columns
Column: Name of the column.

Datatype: CHAR, VARCHAR2, NUMBER, LONG, RAW, LONG RAW, DATE, ROWID, MLSLABEL

New: Displays the Column Details dialog box.

Edit: Displays the Column Details dialog box for the cluster key column selected in the list.

Remove: Deletes a cluster key column that is selected in the list.

Column Details Dialog Box

Note:
This dialog box displays when you click on the New or Edit button.

Name
Name of the table column being defined or edited. You can specify a valid Oracle identifier as the name of the column. This field is disabled for columns that have already been defined in the database.

Datatype
Column's Oracle datatype. See Oracle7 Server SQL Reference for specific information on Oracle datatypes.

You can select one of the following items: CHAR, VARCHAR2, NUMBER, LONG, RAW, LONG RAW, DATE, ROWID, MLSLABEL

Length: Number of bytes allowed for values defined in the column (for CHAR, VARCHARZ, NUMBER, RAW).

Precision (for NUMBER datatype): Number of digits to the right of the decimal point. If CHAR is selected in the Datatype drop-down list, you may specify length. If VARCHAR2 or RAW is selected, you may specify a length. If NUMBER is selected, you may specify a length, a length and a precision, or neither.

Length
Maximum size for values of a given datatype. For specific information on datatypes, see Oracle7 Server SQL Reference.

Precision (Visible when Datatype is Number)
For Number datatypes, value specifies the precision (total number of digits). Precisions values can range from 1 to 38.

Create Like Cluster

To create a cluster with parameters that are identical to an existing cluster:

1.: Select a cluster from the clusters multi-column list.
2.: Choose Create Like from the Object menu. The Create Cluster property sheet appears with the appropriate parameters filled in.
3.: Specify a new cluster name and modify (if necessary) any property sheet parameters.
4.: Click Create.

Editing a Cluster

To edit an existing cluster:

1.: Select a specific cluster in the navigator. The Clusters property sheet appears with the current parameters.
2.: Modify the parameter entries as necessary.
3.: Click Apply.

Database Links Folder

The Database Links folder contains database links defined within the connected database.

A database link allows you to access a remote database. For information about database links, see Oracle7 Server SQL Reference.

Database Links Multi-Column List

The Database Links multi-column list displays when the Database Links folder is selected in the navigator. The list contains information about the status of links defined within the connected database. For information about database links, see Oracle7 Server Concepts.

The columns of the Database Links list are described below:

Schema
The name of the schema containing the link. This column is not shown when viewing a particular schema.

Database Link
The name of the database link.

Username
The name of the user the database connects as when connecting to the remote database.

Host
SQLNet connect string to the host.

Created
Date on which the link was created.

If you view the navigator by schema, a Database Links folder appears in an alphabetical list of all schema objects under each schema in the navigator. When the Database Links folder is selected, the multi-column list is displayed.

Creating a Database Link

To create a new database link:

1.: Choose Create from the Object menu.
2.: Select Database link from the list in the Create Object dialog box. This object will be selected by default if the Database Links folder is selected in the navigator.
3.: Click OK. The Create Database Link property sheet appears.
4.: Define the parameters of the new database link.
5.: Click Create.

The Database Link property sheet consists of a single General page:

General

The General page contains the following information:

Name
The name of the database link being defined. You can specify any valid Oracle identifier.

Public
Specifies that the database link be available to all users (public) or only the owner (private).

Connection Details
Privileged Link (Oracle 8 only): Need to do determine function.

Anonymous Link: Specifies that the database link use the username and password of the user accessing the database link.

Named Link

Username: Username used to connect to the remote database. If the Username field is left blank during the creation process, the newly defined link acquires the username of the person accessing the remote database.

Password: Password used to connect to the remote database. If the Username and Password fields are left blank, the database link uses the username password of the person accessing the database link.

Service Name: Database specification of a remote database.

Test
Checks the validity of the Username, Password, and Service Name when a database link is created. This button is only available when altering an existing database link.

Creation Date
Displays the date of database link creation. By default, the current date is displayed.

Create Like Database Link

To create a database link with parameters that are identical to an existing database link:

1.: Select a specific link from the database link multi-column list.
2.: Select Create Like from the Object menu. The Create Database Link property sheet appears with same connection details and service name as the original link.
3.: Enter a new name and modify any parameters (if necessary).
4.: Click Create.

Viewing a Database Link

To look at the parameters for an existing link:

Select a specific database link from the navigator. A property sheet with the link's current parameters is displayed. The property sheet is nearly identical to the Create Database Link property sheet with the following exceptions:

All fields are disabled.
The Creation date displays the date the database link was created.

Functions Folder

The Functions folder contains an alphabetical list of functions defined within the database.

A function is a PL/SQL subprogram that executes an operation and returns a value at the completion of the operation. A function can be either built-in or user-named. For information about functions, see Oracle7 Server SQL Reference.

Functions Multi-Column List

The Functions multi-column list displays when the Functions folder is selected in the navigator. The list contains information about the status of links defined within the connected database.

The columns of the Functions list are described below:

Schema
Name of the schema containing the function. This column is not shown when viewing a particular schema.

Function
Name of the function.

Created
Date on which the function was created.

Last Modified
SQL* Net connect string to the host.

Status
Current status of the function (Valid or Invalid).

Creating a Function

To create a new function:

1.: Choose Create from the Schema Manager Object menu.
2.: Select Function from the list in the Create Object dialog box. The Create Function property sheet appears.
3.: Fill in the requisite parameters and enter the PL/SQL source for the function.
4.: Click Create.

The property sheet contains a single General page.

General

The General page consists of the following information:

Name
Name of the function being defined.

Schema
Name of the schema containing the function. The drop-down list contains all schemas available in the current database.

Recompile
Recompile the PL/SQL source for the function so that it can be stored and executed from within the database.

Dates
Created: Date the function was originally created.

Last Modified: Date the function was last modified.

Status
Current status of the Function (valid or invalid).

Show Errors: Displays the Error Message dialog box listing the errors encountered while trying to compile the function source. This button is only enabled when the function status is invalid. For detailed error message information pertaining to PL/SQL, see the Oracle Server Messages Manual.

Source
Function SQL statements or PL/SQL constructs.

Create Like Function

To create a function with parameters and code that is identical to an existing function:

1.: Select a function from the function's multi-column list.
2.: Select Create Like from the Object menu. The Create Function property sheet appears with parameters PL/SQL source identical to the original function.
3.: Enter a new Name.
4.: Click Create.

Altering a Function

To alter an existing function:

1.: Select a specific function from the navigator. A property sheet with the function's current parameters and source code displays.
2.: Modify the parameters and/or PL/SQL source as necessary.
3.: Click Apply after changing the desired parameters to recompile the function and save the changes.

Note: To change the parameters and any code modifications back to their original setting, click Revert.

Indexes Folder

The Indexes folder contains table indexes defined within the database. An index is an optional structure associated with a table, used by Oracle to locate rows of that table quickly and can be used to guarantee that every row is unique. For information about indexes, see the Oracle7 Server SQL Reference, or the PL/SQL User's Guide and Reference .

Indexes Multi-Column List

The Indexes multi-column list appears when you select the Indexes folder in the navigator. This multi-column list contains the following information:

Schema
Name of the schema. This column is not shown when viewing a particular schema.

Index
Name of the index.

Table Owner
Owner of the table for which the index is created.

Table
Table for which the index is created.

Table Type
Type of table (table or cluster).

Status
Status of the table (Valid or Invalid).

When you view the navigator by schema, an Indexes folder appears listing all available schema in alphabetical order. Selecting the Indexes folder displays a multi-column list of indexes contained in the parent schema.

Creating an Index

To create a new index:

1.: Choose Create from the Object menu
2.: Select Indexes from the list in the Create Object dialog box. The Create Index property sheets appear.
3.: Enter the requisite parameter values.
4.: Click Create.

The Create Index property sheet consists of up to three pages:

General
Storage (Advanced Mode only, see Storage on page 11-4)
Options (Advanced Mode only, see Options on page 11-5)

Note:
You can also call up the Index property sheet by selecting a table from the navigator and choosing Create Index On from the Object menu.

General

The General page consists of the following information:

Name
Name of the index.

Schema
Schema in which the index will be defined. The default schema for a new index is the user's default schema. When creating an index link, you can also select any schema defined in the connected database from the Schema drop-down list.

Index Type (Oracle 8 only)
Specifies whether a partitioned index local (equi-partitioned with the underlying table) or global (patitioning defined explicitly when you create the index). Partitioning of global indexed must be maintained manually.

Index On
Table/Cluster: Specifies whether the index is to be placed on a table or cluster.

Schema: Drop-down list displaying available schema to which the index can belong.

Table: Drop-down list displaying all available tables belonging to the selected schema. The coluns of the selected table are displayed in the Table spreadsheet.

Table Spreadsheet

The Table Spreadsheet consists of the following columns:

Table Columns: Column names of the table selected in the Table drop-down list.

Order: Order of the columns selected for the index. Columns are ordered in the sequence in which it is added to the index.

To add acolumn to the index, click on the desired column in the spreadsheet. The order of the column appears in the Order column.

To remove a column from the index click on the desired column entry in the spreadsheet. The columns are automatically reordered after the index is removed.

Options
Unique: Specifies that the value of the column (or combination of columns) in the table to be indexed must be unique.

Sorted (Default): If deselected, indicates to Oracle 7 that rows are stored in the database are in ascending order and therefore do not have to be sorted when creating the index.

Recoverable: Specifies that the creation of the index will be logged in the redo log file.

Create Like Index

To create an index with parameters that are identical to an existing index:

1.: Select a index from the multi-column list.
2.: Select Create Like from the Object menu. The Create Index property sheet appears with the appropriate parameters filled in.
3.: Enter a new name and modify the parameter settings (if necessary).
4.: Click Create to close the property sheet and create the new index.

Altering an Index

To alter an existing index:

1.: Select a specific index from the navigator. A property sheet with the index's current parameters displays.
2.: Modify the property sheet parameters as necessary.
3.: Click Apply.

Package Bodies Folder

The Package Bodies folder consists of a list of package bodies defined within the connected database. Package bodies are organized by schema within the Package Bodies folder.

A package consists of two parts: the specification and the body. The package body defines all constructs (public and private) of the package while the specification declares all public constructs. For information about package bodies, see Oracle7 Server SQL Reference, or the PL/SQL User's Guide and Reference.

Package Bodies Multi-Column List

The Package Bodies multi-column list displays when the Package Bodies folder is selected in the navigator. The list contains all package bodies defined within the connected database.

The columns of the package bodies list are described below:

Schema
Name of the schema containing the package body.

Package Body
Name of the package body.

Created
Date on which the package body was created.

Last Modified
Date on which the package body was last modified.

Status
Current status of the package body (Valid or Invalid).

Creating a Package Body

To create a new package body:

1.: Choose Create from the Schema Manager Object menu.
2.: Select Package Body from the list in the Create Object dialog box. The Create Package Body property sheet appears.
3.: Enter the Name, Schema and PL/SQL source for the package body.
4.: Click Create toe compile the package body and close the property sheet.

The Package Body property sheet contains a single General page.

General

The General page consists of the following information:

Name
Name of the package body being defined.

Schema
Name of the schema containing this package body. The drop-down list contains all schemas available in the current database.

Recompile
Recompile the PL/SQL source for the package body so that it can be stored and executed from within the database.

Source
PL/SQL source code for the package body.

Dates
Created: Date the package body was originally created.

Last Modified: Date the package body was last modified.

Status
Current status of the package body (valid or invalid).

Show Errors/Hide Errors: Opens or closes the Error Messages dialog box. This button is available when the package body status is invalid.

Create Like Package Body

To create an package body with parameters that are identical to an existing one:

1.: Select a package body from the navigator.
2.: Select Create Like from the Object menu. The Create Package Body property sheet appears.
3.: Enter a Name and modify property sheet parameters and/or PL/SQL source (if necessary).
4.: Click Create to compile the package body and close the property sheet.

Editing a Package Body

To edit an existing package body:

1.: Select a package body in the navigator. The Package Body property sheet appears with the Name and Schema fields greyed out.
2.: Modify the PL/SQL source.
3.: Click Apply to recompile the source code and commit the changes.

Packages Folder

The Package folder consists of a list of all packages defined within the connected database. Packages within this folder are organized by schema.

A package is a group of related procedures and functions, together with cursors and variables they use, stored together in the database for continued use as a unit. For information about packages, see Oracle7 Server SQL Reference, or the PL/SQL User's Guide and Reference .

Packages Multi-Column List

The Packages multi-column list appears when the Packages folder is selected in the navigator. The list contains information about:

Schema
Name of the schema containing the package. This column is not displayed when viewing a particular schema.

Package
Name of the package.

Created
Date on which the package was created.

Last Modified
Date on which the package was last modified.

Status
Current status of the package (valid or invalid).

Creating a Package

To create a new package:

1.: Choose Create from the Schema Manager Object menu.
2.: Select Package from the list in the Create Object dialog box. The Create Package property sheet appears.
3.: Enter the Name, Schema and PL/SQL source for the package. For more information on creating packages, see Oracle7 PL/SQL User's Guide and Reference.
4.: Click Create to compile the package source and create the new package.

The Package property sheet consists of a single General page.

General

The General page consists of the following information:

Name
Name of the package being defined.

Schema
Name of the schema containing this package. The drop-down list contains all schemas available in the current database.

Recompile
Recompile the PL/SQL source for the package so that it can be stored and executed from within the database.

Dates
Created: Date the package was originally created.

Last Modified: Date the package was last modified.

Status
Current status of the package (valid or invalid).

Show Errors/Hide Errors: Opens or closes the Error Messages dialog box. This button is available when the package status is invalid.

Package Source
PL/SQL source code for the package.

Create Like Package

To create an package with parameters that are identical to an existing package:

1.: Select a package from the navigator
2.: Select Create Like from the Object menu. The Create Package property sheet appears with the appropriate information filled in.
3.: Enter a name and modify the PL/SQL source (if necessary).
4.: Click Create to compile the source and create the package.

Editing a Package

To edit an existing package's PL/SQL source:

1.: Select a specific package in the navigator. A property sheet with the package's current parameters is displayed.
2.: Modify the source code.
3.: Click Apply to compile the source code and commit the changes.

Procedures Folder

The Procedures folder consists of a list of all procedures defined within the connected database. Procedures in the navigator are organized by schema.

A procedure groups a set of SQL and PL/SQL statements together to perform a specific task. For more information on procedures, see Oracle7 Server SQL Reference, Vol. II, or the PL/SQL User's Guide and Reference.

Procedures Multi-Column List

The Procedures multi-column list appears when the Procedures folder is selected in the navigator. The list contains the following information:

Schema
Name of the schema containing the procedure. This column is not shown when viewing a particular schema.

Procedure
Name of the procedure.

Created
Date the procedure was originally created.

Last Modified
Date the procedure was last modified.

Status
Current status of the procedure (Valid or Invalid).

Creating a Procedure

To create a new procedure:

1.: Choose Create from the Schema Manager Object menu.
2.: Select Procedure from the list in the Create Object dialog box. The Create Package property sheet appears.
3.: Enter the Name, Schema, andSQL or PL/SQL source code.
4.: Click Create to compile the source code and create the procedure.

The property sheet consists of a single General page.

General

The General page consists of the following information:

Name
Name of the procedure being defined.

Schema
Name of the schema containing this procedure. The drop-down list displays the names of all available schemas.

Recompile
Recompile the PL/SQL source for the procedure so that it can be stored and executed from within the database.

Package Source
PL/SQL source code for the procedure.

Status
Current status of the procedure (valid or invalid).

Show Errors: Displays the Error Message dialog box listing the errors encountered while trying to compile the function source. This button is only enabled when the function status is invalid.

Dates
Created: Date the procedure was originally created.

Last Modified: Date the procedure was last modified.

Create Like Procedure

To create an procedure with parameters that are identical to an existing one:

1.: Select a procedure from the multi-column list.
2.: Select Create Like from the Object menu. The Create Procedure property sheet appears with the appropriate parameters filled in.
3.: Enter a new name and modify the procedure source code (if necessary).
4.: Click Apply to compile the code and create the new procedure

Altering a Procedure

To alter an existing procedure:

1.: Select a procedure in the navigator.The Procedure property sheet appears with the current parameters and source code.
2.: Modify the procedure source code.
3.: Click Apply to recompile the source and commit the changes.

Refresh Groups Folder

The Refresh Group folder contains all refresh groups defined within the connected database. Within the folder, refresh groups are organized by schema.

A refresh group consolidates multiple snapshots and allows you to easily refresh two or more snapshots to a single point in time.

For information about refresh groups, see Oracle7 Server Distributed Systems, Volume II: Replicated Data.

Refresh Groups Multi-Column List

The Refresh Groups multi-column list appears when the Refresh Groups folder is selected in the navigator. The list contains the following information:

Schema
Name of the schema containing the refresh group. This column is not shown when viewing a particular schema.

Name
Name of the refresh group. Refresh group names must follow the same naming conventions as tables.

Number
Identification number of the refresh group.

Next Date
Next date the refresh will occur.

Interval
Function used to calculate the next time to refresh the snapshots in the group.

Creating a Refresh Group

To create a new refresh group:

Choose Create from the Object menu.

2.

Select Refresh Group from the list in the Create Object dialog box. The Create Refresh Group property sheet appears.

3.

Enter the requisite information.

: To add a snapshot to the refresh group, select an item from the Available list and click the Up arrow. The snapshot appears in the In Group list.
: To remove a snapshot from the refresh group, select an item in the In Group list and click the Down arrow.

4.

Click Create.

The Refresh Group property sheet consists of two pages:

General
Snapshots

General

The General page consists of the following information:

Name
Name of the Refresh group.

Schema
Name of the schema containing the refresh group.

Refresh
Refreshes the current refresh group immediately.

Broken
When checked, marks a job as broken so Oracle will not attempt to execute the job.

Delete group when last member deleted
Upon deleting the last refresh group member, the refresh group is automatically deleted.

Refresh
Next Date: Next date that you want a refresh to occur.

Interval: Function used to calculate the next time to refresh the snapshots in the refresh group.

Continue refresh despite conflicts: Select this option if you want the refresh to proceed even if there are outstanding conflicts logged in the DefError table for the snapshot's master. This option is active for updatable snapshots only.

Push changes from snapshot to master before refresh: Selected by default, thi s option allows you to push changes from the snapshot to its associated master before refreshing the snapshot. Otherwise, these changes may appear to be temporarily lost. This option is active for updatable snapshots only.

Rollback Segment
Use default rollback segment: When selected, the default rollback segment is used. Segment name and Segment Schema are greyed out.

Segment Name: Name of the rollback segment to use while refreshing snapshots.

Segment Schema: Name of the schema containing the rollback segment.

Snapshots

The Snapshot page consists of the following information:

In Group
Scrolling list containing all snapshots in the refresh group.

Available
Scrolling list containing all available snapshots.

Create Like Refresh Group

To create a refresh group with parameters that are identical to an existing one:

1.: Select a refresh group in the navigator.
2.: Select Create Like from the Object menu. The Create Refresh Group property sheet appears with the appropriate parameters filled in.
3.: Enter a name and modify any parameters (if necessary).
4.: Click Create.

Altering a Refresh Group

To alter an existing refresh group:

1.: Select a specific refresh group in the navigator. A property sheet with the refresh group's current parameters displays.
2.: Modify the property sheet entries as necessary.
3.: Click Apply.

Sequences Folder

The Sequence folder contains all sequences stored within the connected database. Sequences are organized by schema.

A sequence is a database object used to generate a serial list of unique numbers for numeric columns of a database's tables. Sequences simplify data entry in a multi-user system by automatically generating unique numerical values for the rows of a single table or multiple tables. For information about sequences, see Oracle7 Server Concepts and Oracle7 Server Administrator's Guide.

Sequences Multi-Column List

The Sequences multi-column list displays when the Sequences folder is selected in the navigator. The list contains sequences defined within the connected database. For information about sequences, see the Oracle7 Server SQL Reference.

The columns of the Sequences multi-column list are described as follows:

Schema
Name of the schema containing the sequence. This column is not shown when viewing a particular schema.

Sequence
Name of the sequence.

Minimum Value
Smallest permissible value for the sequence.

Maximum Value
Largest permissible value for the sequence.

Interval
Incremental value for the sequence (value may be positive or negative).

Recent Value
Most recent value of the sequence.

Cycle
Indicates whether or not the sequence should continue to generate values after reaching its minimum or maximum value (Yes or No).

Order
Indicates whether or not the sequence numbers should be generated in order of request (Yes or No).

Cache Size
Number of values to be preallocated and stored by the database.

Creating a Sequence

To create a new sequence:

1.: Choose Create from the Object menu.
2.: Select Sequence from the list in the Create Object dialog box. The Create Sequence property sheet appears.
3.: Enter the requisite sequence information.
4.: Click Create.

The property sheet consists of a single General page.

General

The General page contains the following information:

Name
Name of the sequence being defined. During the Create operation, you can specify a valid Oracle identifier as the name of the sequence.

Schema
Schema in which the sequence is defined. The default schema for a new sequence is the user's default schema. During the Create operation, you can choose a schema from those defined in the connected database from the drop-down list.

Type: Ascending
When selected, sequence values increase from the initial value towards the maximum value. This is the default setting when creating a sequence.

Type: Descending
When selected, the sequence values decrease from the initial value towards the minimum value.

Values
Minimum: Minimum allowed value of the sequence. When creating a sequence, this field is initially blank. If this field is blank when you select the Create button, a default value of one for an ascending sequence or -1026 for a descending sequence will be used.

Maximum: Maximum value of a sequence. When creating a sequence this field is initially blank. If left blank when the Create button is selected, a default value of 1027 for an ascending sequence and -1 for a descending sequence will be used.

Increment: Interval by which the sequence increases or decreases. When creating a sequence, this field is initially blank. If left blank when the Create button is selected, a default value of 1 is used. Only positive integers are accepted in this field.

Decrement: Interval by which the sequence decreases. When creating a sequence, this field is initially blank. If left blank when the Create button is selected, a default value of 1 is used. Only positive integers are accepted in this field.

Initial: The starting value of the sequence. If the field is blank when the Create button is selected, a default value of the sequence's minimum will be used for an ascending sequence. For a descending sequence, a default value of the sequence's maximum value is used.

Recent: The most recent value of the sequence. This field is active when altering an existing sequence.

Options
Cycle Values: When checked, specifies that the sequence should continue to generate values after reaching its minimum or maximum value. For ascending sequences, the minimum value is generated after the maximum is reached. For descending sequences, the maximum value is generated after the minimum is reached. If Cycle Values is not checked, the sequence will stop generating values when it reaches its minimum or maximum. When creating a sequence, this checkbox is unchecked by default.

Order Values: When checked, specifies that the sequence numbers are to be generated in order of request. When creating a sequence, this checkbox is unchecked by default.

Cache Size: Number of values to be pre-allocated and stored by the database. There are three options:

Default: The default value is 20. This option is selected by default.
No Cache: Specifies that values of the sequence not be pre-allocated.
Size: The smallest acceptable value is 2. For sequences that cycle, this value must be less than the number of values in the cycle. If the maximum number of values the sequence can generate is less than the cache size, the cache size will be automatically changed to the maximum number of possible values.

Create Like Sequence

To create another sequence with parameters that are identical to an existing sequence:

1.: Select an existing sequence from the navigator.
2.: Select Create Like from the Object menu. The Create Sequence property sheet displays.
3.: Enter a name and modify the property sheet parameters (if necessary).
4.: Click Create.

Altering a Sequence

To alter an existing sequence:

1.: Select a specific sequence in the navigator. A property sheet with the sequence's current parameters is displayed.
2.: Modify the property sheet parameters as necessary.
3.: Click Apply.

Snapshot Logs Folder

The Snapshot Logs folder contains a list of all snapshot logs created in the connected database.

A snapshot log is a table associated with the master table of a snapshot. The snapshot log contains information about changes made to its associated master table. The information in the snapshot log is used to refresh the snapshot.

Snapshot Logs Multi-Column List

The Snapshot Logs multi-column list displays when the Snapshots folder is selected in the navigator. The multi-column list contains snapshot log information defined within the connected database. For information about snapshot logs, see Oracle7 Server Concepts.

The columns of the Snapshots multi-column list are described as follows:

Schema
Name of the schema containing the snapshot log. This column is not shown when viewing a particular schema.

Log Table
Name of the table storing the ROWID and timestamp of rows updated in the master table.

Log Trigger
Name of the AFTER ROW trigger on the master table.

Master Table
Name of the table associated with the snapshot log.

Current Snapshots
The number of snapshots associated with the snapshot log.

Creating a Snapshot Log

To create a new snapshot log:

1.: Choose Create from the Object menu.
2.: Select Snapshot Log from the list in the Create Object dialog box. The Create Snapshot Log property sheet appears.
3.: Enter the necessary property sheet information.
4.: Click Create.

The Snapshot Logs property sheet consists of two pages:

General
Storage (Advanced Mode only, see Storage on page 11-4)
Options (Advanced Mode only, see Options on page 11-5)

General

The General page contains the following:

On Master Table
Schema: Name of the schema to which the master table belongs. The drop-down list displays all schemas available to the user.

Table: Name of the master table. The drop-down list displays all tables belonging to the selected schema.

Log Table
Name of the table that stores the ROWID and timestamp of rows updated in the master table.

Log Trigger
Name of the AFTER ROW trigger on the master table that tells Oracle to insert the ROWIDs and timestamps of inserted, updated, and deleted rows into the master snapshot log.

Current Snapshots:
The number of snapshots associated with the snapshot log.

Create Like Snapshot Log

To create a snapshot log with parameters that are identical to an existing log:

1.: Select an existing snapshot log from the navigator.
2.: Choose Create Like from the Object menu. The Create Snapshot Log property sheet appears.
3.: Enter a name and modify the property sheet entries (if necessary).
4.: Click Create.

Altering a Snapshot Log

To alter an existing snapshot log:

1.: Select a specific snapshot log in the navigator. A property sheet with the log's current parameters displays.
2.: Modify the property sheet parameters.
3.: Click Apply.

Snapshots Folder

The Snapshot folder contains of a list of all snapshots defined within the database.

A snapshot is a read-only copy of a master table located on a remote node. It is periodically refreshed to reflect changes made to the master table. A snapshot can be queried, but not updated; only the master table can be updated.

Snapshots Multi-Column List

The Snapshots multi-column list displays when the Snapshots folder is selected in the navigator. The list contains all snapshots defined within the connected database.

The columns of the snapshots list are described below:

Schema
Name of the schema containing the snapshot. This column is not shown when viewing a particular schema.

Snapshot
Name of the snapshot.

Master Owner
Owner of the snapshot's master table.

Master Table
Name of the master table.

Master Link
Database link to the master site.

Last Refresh
Date of the last refresh (at the master site).

Type
Type of refresh for automatic refreshes: Complete, Fast, or Force.

Creating a Snapshot

To create a new snapshot:

1.: Choose Create from the Schema Manager Object menu.
2.: Select Snapshot from the list in the Create Object dialog box. The Create Snapshot property sheet appears.
3.: Enter the requisite information.
4.: Click Create.

The property sheet consists of two pages:

General
Cluster
Storage (Advanced Mode only, see Storage on page 11-4)
Options (Advanced Mode only, see Options on page 11-5)

General

The General page contains the following:

Name
Name of the snapshot being defined. During the Create operation, you can specify a valid Oracle identifier as the name of the sequence.

Schema
Name of the schema containing the snapshot. The drop-down list contains all available schemas within the connected database.

Refresh Type
Type of refresh for automatic refreshes: Complete, Fast, or Force.

Complete: Specifies a refresh that executes the subquery.

Fast: Specifies a refresh using only the updated data stored in the snapshot log associated with the master table.

Force: Specifies a fast refresh if one is possible or a complete refresh if fast refresh is not possible.

Start Date: Date snapshot refresh is to begin.

Next Date: Expression that calculates the interval at which the snapshot is updated.

Updatable
When selected, indicates that the snapshot is updatable. When used in conjunction with the Replication Option, updates are propagated to the master.

Snapshot Subquery
Snapshot query executed by Oracle when you create the snapshot. Results of the query are placed in the snapshot. (Create only)

Cluster

The Cluster page allows you to define the cluster to which a snapshot belongs. This property sheet contains the following:

Cluster
Cluster to which the snapshot belongs. The drop-down list displays all clusters contained within the selected schema.

Column Name: Name of the column to be used as part of the snapshot. Click Add to add the column name to the Snapshot Columns list.

Snapshot Columns: Scrolling list of all columns added to the snapshot. Use the Up/Down arrows to reorder the list of columns (column order must match the order in the cluster).

Remove: Deletes the selected snapshot column from the list.

Create Like Snapshot

To create a snapshot with parameters that are identical to an existing snapshot:

1.: Select an existing snapshot from the navigator.
2.: Choose Create Like from the Object menu. The Create Snapshot property sheet appears.
3.: Enter a name and modify the property sheet parameters (if necessary).
4.: Click Create.

Editing a Snapshot

To edit an existing snapshot:

Select a snapshot in the navigator. A property sheet with the snapshot's current parameters displays.

: This property sheet is similar to the Create Snapshot property sheet except that the Subquery entry field is replaced by the following information:
: Trigger
: Associated AFTERROW trigger on the master table.
: Log
: Associated snapshot log.
: Master Info
: Owner: Name of the schema to which the snapshot belongs.
: Table: Name of the master table.
: Link: Database link to the database where the master table resides.
: Can Log
: Whether or not the snapshot can be logged.
: Ref. Group
: Associated refresh group.
: Last Refreshed
: Date the snapshot was last refreshed.

2.

Modify the property sheet parameters as necessary.

3.

Click Apply.

Synonyms Folder

The Synonyms folder contains a list of all synonyms defined within the connected database.

A synonym is an alias for a table, view, sequence, procedure, function, package or another synonym.

Synonyms Multi-Column List

The Synonyms multi-column list displays when the synonyms folder is selected in the navigator. The list summarizes information for synonyms defined within the connected database.

The columns of the Synonyms multi-column list are described as follows:

Schema
Name of the schema containing the synonym. This column is not shown when viewing a particular schema.

Synonym
Name of the synonym.

Object Owner
Owner of the original database object.

Object
Name of the original database object.

Link
Database link to the the original schema object (e.g. table, view, snapshot).

Creating a Synonym

To create a new synonym:

1.: Choose Create from the Object menu.
2.: Select Synonym from the list in the Create Object dialog box. The Create Synonym property sheet appears.
3.: Enter the requisite information.
4.: Click Create.

The Create Synonym property sheet contains a single General page.

General

The General page consists of the following information:

Name
Name of the synonym.

Schema
Name of the schema containing the synonym. The drop-down list contains all available schemas within the connected database.

As Alias For
Local Database/Remote Database: Allows you to create a synonym from an object on a local database or an accessible database on the network.

Object Type (Local)/DB Link (Remote): Drop-down list containing available database object types (tables, snapshots, etc.) for local databases. If Remote Database is selected the field label changes to DB Link. In this case, the drop-down list contains all database links defined for the current instance.

Schema: Drop-down list containing all available schemas for the current instance (local databases). If Remote Database is selected, the schema name must be typed in explicitly.

Object: Drop-down list containing all available database objects for the selected schema.

Create Like Synonym

To create another synonym with parameters that are identical to an existing synonym:

1.: Select an existing synonym from the navigator.
2.: Select Create Like from the Object menu. The Create Like property sheet is displayed.
3.: Enter a name.
4.: Click Create.

Viewing a Synonym

To view the parameters of an existing synonym, select a synonym in the navigator. A property sheet with the original synonym's current parameters displays.

Tables Folder

The Tables folder contains a list of all tables defined within the connected database.

As the basic unit of storage in a relational database management system, a table represents entities and relationships, and consists of one or more units of information (rows), each of which contains the same kinds of values (columns). For information about tables, see Oracle7 Server Concepts.

Tables Multi-Column List

The Tables multi-column list displays when the Tables folder is selected in the navigator. The list summarizes information for tables defined within the connected database.

The columns of the Sequences multi-column list are described as follows:

Schema
Name of the schema in which the table is defined. This column is not shown when viewing a particular schema.

Table
Name of the table.

Tablespace
Name of the tablespace to which the table belongs.

Rows
Number of rows in the table. (Shown only if the cost-based optimizer is disabled.)

Note:
Entries in the Rows column only appear if ANALYZE has been run against the table. For more information on the ANALYZE command, see Oracle7 Server SQL Reference.

Creating a Table

To create a new table:

Choose Create from the Object menu.

2.

Select Table from the list in the Create Object dialog box. The New Table dialog box appears. From here, you can choose to create a new table using either the Table Wizard or manually using the Create Table property sheet.

: The Table Wizard is a rule-based series of dialogs that guide you the process of creating a table. See Oracle Enterprise Manager online help for more information about the Table Wizard.

3.

Enter the requisite property sheet information.

4.

Click Create.

The Create Table property sheet contains the following pages and dialog boxes:

General
Constraints
Cluster Columns
Storage (Advanced Mode only, see Storage on page 11-4)
Options (Advanced Mode only, see Options on page 11-5)

Dropping a Constraint on a Table

To drop a constraint from a table:

1.: Using the left mouse button, click on the entry select button located to the immediate left of an individual row in the Constraints on the Table spreadsheet.
2.: Click the right mouse button to display the pop-up menu.
3.: Select Drop to delete the constraint, or Drop with Cascade to tell Oracle to drop the constraint and maintain referential integrity by also removing dependent foreign key values if a referenced primary or unique key value is removed.

Removing a Constraint Definition (Create mode only)

To remove a constraint definition:

1.: Using the left mouse button, click on the entry select button located to the immediate left of an individual row in the Constraints on the Table spreadsheet. The table columns or referenced columns appear in the Constraint Definition spreadsheet.
2.: In the Constraint Definition spreadsheet, select the desired constraint by clicking on the entry select button located to the immediate left of an individual row in the Constraint Definition spreadsheet.
3.: Click the right mouse button to display the pop-up menu.
4.: Select Remove.

Create Like Table

To create a new table with parameters that are identical to an existing table:

Select a table from the navigator and then select Create Like from the Object menu. The Create Table property sheet appears with all parameters filled in except Table (name), based on the values from the selected table.

Click Create to create the table and close the property sheet.

Altering a Table

To alter an existing table:

1.: Select a table in the navigator. A property sheet with the table's current parameters displays.
2.: Modify the parameters as necessary.
3.: Click Apply.

Triggers Folder

The Triggers folder contains a list of triggers defined within the connected database.

A trigger is a stored PL/SQL procedure that is implicitly executed when an INSERT, UPDATE, or DELETE statement is issued against the associated table. For more information about triggers, see Oracle7 Server Concepts.

Triggers Multi-Column List

The Triggers multi-column list displays when the Triggers folder is selected in the navigator. The list summarizes information for all triggers defined within the connected database.

The columns of the Triggers multi-column list are described as follows:

Schema
Name of the schema containing the trigger. This column is not shown when viewing a particular schema.

Trigger
Name of the trigger.

Type
Trigger type (BEFORE EACH ROW, AFTER EACH ROW, or FOR EACH ROW).

Event
Triggering statement (INSERT, UPDATE, DELETE).

Table Owner
Owner of the table with which the trigger is associated.

Table
Name of the table with which the trigger is associated.

Creating a Trigger

To create a new trigger:

1.: Choose Create from the Object menu.
2.: Select Trigger from the list in the Create Object dialog box. The Create Triggers property sheet appears. The Create Trigger property sheet appears.
3.: Enter the requisite information.
4.: Click Create.

The property sheet consists of two pages:

General
Timing

General

The General page allows you to define the primary characteristics of a trigger such as the name of trigger, schema to which the trigger belongs, and the trigger body. This page contains the following information:

Name
Name of the trigger being defined.

Schema
Schema in which the trigger is to be defined. The default schema for a new trigger is SYSTEM.

Trigger On
Schema: Drop-down list displaying all available schema in the database. This field specifies the schema that contains the table.

Table: Drop-down list displaying all available tables in the selected schema. This field specifies the name of the table on which the trigger is to be created. Triggers on SYS schema tables cannot be created.

Replace if Exists
When selected, indicates that a trigger is to be recreated (if it already exists) without dropping, recreating and regenerating object privileges previously granted.

Enabled
When checked, indicates that a trigger is enabled.

During a Create operation, this control is checked and disabled. It is not checked and enabled if a trigger is disabled.

Trigger Body
Defines the PL/SQL block that Oracle executes to fire the trigger.

Timing

The Timing page allows you to specify the conditions that initiate a trigger. This page contains the following information:

Triggering Statement
Fire Trigger:

Before: Indicates that Oracle fire the trigger before executing the triggering statement.
After: Indicates that Oracle fire the trigger after executing the triggering statement.

Insert: When checked, indicates that Oracle fire the trigger whenever an INSERT statement adds a row to a table.

Delete: When checked, indicates that Oracle fires the trigger whenever a DELETE statement removes a row from a table.

Update of Columns: Indicates that Oracle fires the trigger whenever an UPDATE statement changes a value in a column selected in the multi-select list. It is only enabled and populated when the Update checkbox is selected.

Trigger for Each Row
When checked, designates the trigger to be a row trigger. Oracle fires a row trigger once for each row that is affected by the triggering statement and meets the optional trigger constraint defined in the Condition field. When not checked, the trigger is a statement trigger and is fired only once when the triggering statement is issued (assuming the optional trigger constraint is met).

Referencing

Old as: New name for default correlation name OLD.
New as: New name for default correlation name NEW.

You can use correlation names in the PL/SQL block and WHEN clause of a row trigger to refer specifically to old and new values of the current row. The default correlation names are OLD and NEW.

Condition : Specifies the trigger restriction.

The trigger restriction contains a SQL condition that must be satisfied for Oracle to fire the trigger. This condition must contain correlation names and not a query. You can only specify a trigger restriction for a row trigger. Oracle evaluates this condition for each row affected by the triggering statement. See the Oracle7 SQL Reference manual for more information.

Create Like Trigger

To create a new trigger with parameters that are identical to an existing trigger:

1.: Select a trigger in the navigator.
2.: Select Create Like from the Object menu. The Create Trigger property sheet appears with all parameters filled in, except the name, based on the values from the selected table.
3.: Click Create.

Altering a Trigger

To alter an existing trigger:

1.: Select a trigger in the navigator. The Trigger property sheet with the selected trigger's current parameters displays.
2.: Modify the trigger body or triggering statement as necessary.
3.: Click Apply.

Attention:
Altering a trigger only affects values generated after the alter is performed.

Views Folder

The Views folder contains a list of all views defined within the connected database.

A view is a custom tailored presentation of the data in one or more tables and can also be thought of as a stored query. Views do not actually contain or store data; rather, they derive their data from the tables on which they are based, called base tables. Base tables can in turn be actual tables or can themselves be views. Views can be queried, updated, inserted into, and deleted from, with restrictions. All operations performed on a view actually affect the base tables of the view. For information about views, see Oracle7 Server Concepts.

Views Multi-Column List

The Views multi-column list displays when the Views folder is selected in the navigator. The list summarizes information for tables defined within the connected database.

The columns of the Views multi-column list are described as follows:

Schema
Name of the schema containing the view. This column is not shown when viewing a particular schema.

View
Name of the view.

Status
Current status of the view (Valid or Invalid).

Creating a View

To create a new view:

1.: Choose Create from the Object menu.
2.: Select View from the list in the Create Object dialog box. The Create View property sheet appears.
3.: Enter the requisite information.
4.: Click Create.

The Create View property sheet consists of two pages:

General
Advanced

General

The General page contains the following information:

Name
Name of the view being defined. During a Create operation, you can specify a valid Oracle identifier as the name of the view.

Schema
Schema in which the view should be defined. The default schema for a new view is the user's default schema.

Query Text
Identifies columns and rows of the table(s) that the view is based on. A view's query can be any SELECT statement without the ORDER BY or FOR UPDATE clauses. Its select list can contain up to 254 expressions.

Advanced

The Advance page allows you to specify options or place constraints on the view. This page consists of the following:

Options
Replace if Exists: When checked, specifies that the view is to be recreated, if it already exists, without dropping, recreating, and regenerating object privileges previously granted on it.

Force: When checked, specifies that the view is to be created regardless of whether the view's base tables exist or the owner of the schema containing the view has privileges on them.

Read Only: When checked, specifies that no deletes, inserts or updates can be performed through the view.

Constraint
Specifies the name assigned to the CHECK OPTION constraint.

With Check Option: When checked, specifies that inserts and updates performed through the view must result in rows that the view query can select. The CHECK OPTION cannot make this guarantee if there is a subquery in the query of this view or any view on which this view is based. If you omit this identifier, Oracle automatically assigns the constraint a name of the form: SYS_Cn, where n is an integer that makes the constraint name unique within the database.

Create Like View

To create a new view with parameters that are identical to an existing view:

1.: Select a view from the navigator.
2.: Choose Create Like from the Object menu. The Create View property sheet appears with all parameters filled in except the view name.
3.: Enter a new name and modify any parameters (if necessary).
4.: Click Create.

Altering a View

To alter an existing view:

1.: Select a view in the navigator. A property sheet with the view's current parameters displays.
2.: Modify the query text or other property sheet parameters as necessary.
3.: Click Apply.

Prev Next

Library

Product

Contents

Index