4 Using Oracle Application Express Utilities

This section describes how to use Oracle Application Express utilities to load and unload data from an Oracle database, generate DDL, view object reports, and restore dropped database objects.

This section contains the following topics:

About Importing, Exporting, Loading, and Unloading Data

You have several options when copying data between Oracle databases or between an Oracle database and external files. Data copying is accomplished by exporting and importing data, and by unloading and loading data. The following table defines these terms.

Term Definition
Exporting Copying database data to external files for import into another Oracle database only. The files are in a proprietary binary format.
Importing Copying data into the database from external files that were created by exporting from another Oracle database.
Unloading Copying database data to external text files for consumption by another Oracle database or another application (such as a spreadsheet application). The text files are in an industry-standard format such as tab-delimited or comma-delimited (CSV).
Loading Copying data into the database from external text files that are in either a standard delimited format or in any of the formats that are supported by the Oracle SQL*Loader utility.

You can export data from any Oracle Database edition (Express Edition, Standard Edition, and Enterprise Edition) into any other edition.

This section contains the following topics:

Choosing the Right Import/Export/Load/Unload Option

The Oracle Database and Oracle Application Express provide several powerful options for importing, exporting, loading, and unloading data. Table 4-1 provides a summary of these options.

Table 4-1 Summary of Oracle Application Express Import/Export Options

Feature or Utility Description

Data Load/Unload wizards in Oracle Application Express

  • Easy to use graphical interface

  • Loads/unloads from and to external text files (delimited fields) or XML files

  • Loads/unloads tables only, one table at a time

  • Access only to schema of logged-in user

  • No data filtering

SQL*Loader utility

  • Command-line interface, invoked with sqlldr command

  • Bulk-loads data into the database from external files

  • Supports numerous input formats, including delimited, fixed record, variable record, and stream

  • Loads multiple tables simultaneously

  • Powerful data filtering capabilities

Data Pump Export and Data Pump Import utilities

  • Command-line interface, invoked with expdp and impdp commands

  • Exports and imports from one Oracle database to another (proprietary binary format)

  • Imports/exports all schema object types

  • Imports/exports entire database, entire schema, multiple schemas, multiple tablespaces, or multiple tables

  • Powerful data filtering capabilities

  • High speed

Export and Import utilities

  • Command-line interface, invoked with exp and imp commands

  • Exports and imports from one Oracle database to another (proprietary binary format)

  • Supports XMLType data

  • Does not support the FLOAT and DOUBLE data types

  • Capabilities similar to Data Pump; Data Pump is preferred

Table 4-2 provides several load, unload, import, and export scenarios and suggests the appropriate option to use for each.

Table 4-2 Import/Export Scenarios and Recommended Options

Import/Export Scenario Recommended Option

You have fewer than 10 tables to load, the data is in spreadsheets or tab- or comma-delimited text files, and there are no complex data types (such as objects or multivalued fields).

Data Load/Unload wizards in Oracle Application Express

You have to load data that is not delimited. The records are fixed length, and field definitions depend on column positions.

SQL*Loader

You have tab-delimited text data to load, and there are more than 10 tables.

SQL*Loader

You have text data to load, and you want to load only records that meet certain selection criteria (for example, only records for employees in department number 3001).

SQL*Loader

You want to import or export an entire schema from or to another Oracle database. There is no XMLType data in any of the data.

Data Pump Export and Data Pump Import

You want to import or export data from or to another Oracle database. The data contains XMLType data and contains no FLOAT or DOUBLE data types.

Import (imp) and Export (exp)

See Also:

Oracle Database Utilities for more information on Data Pump, the Import and Export utilities, and SQL*Loader

Loading and Unloading Data from the Database

The Data Load/Unload wizards in Oracle Application Express enable you to easily load and unload delimited text data to and from the database. The step-by-step wizards have the following features:

  • You can load or unload XML files or delimited-field text files (such as comma-delimited (.csv) or tab-delimited files).

  • You can load by copying and pasting from a spreadsheet.

  • You can omit (skip) columns when loading or unloading.

  • You can load into an existing table or create a new table from the loaded data.

  • When loading into a new table, the primary key can be taken from the data or generated from a new or existing Oracle sequence.

  • When loading into a new table, column names can be taken from the loaded data.

  • Each time that you load from a file, file details are saved in a Text Data Load Repository. You can access these files from within the repository at any time.

Limitations include the following:

  • The wizards load and unload table data only. They do not load or unload other kinds of schema objects.

  • You can load and unload to and from your own schema only. This is also true for users with administrator privileges.

  • You can load or unload only a single table at a time.

  • There are no data type limitations for unloading to text or XML files, or for loading from XML files. However, when loading from spreadsheets (through copy and paste) or from text files, only the following data types are supported: NUMBER, DATE, VARCHAR2, CLOB, BINARY_FLOAT, and BINARY_DOUBLE.

Supported unload formats include:

  • Text such as comma-delimited or tab-delimited data

  • XML documents

This section contains the following topics:

Accessing the Data Load/Unload Page

To access the Data Load/Unload page:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Data Load/Unload.

    The Data Load/Unload page appears.

  3. Click the appropriate icon to load data, unload data, or view the repository.

Loading Data

You can load data into the Oracle Application Express database in the following ways:

  • Copy and paste data from a spreadsheet.

  • Upload a spreadsheet file in a delimited format (such as comma-delimited (.csv) or tab-delimited).

  • Upload a text file containing comma-delimited or tab-delimited data.

Topics in this section include:

See Also:

"Choosing the Right Import/Export/Load/Unload Option"

Loading a Text File

You can copy and paste tab-delimited data directly into the Load Data Wizard.

To load a text file:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Data Load/Unload.

  3. Click Load.

  4. Click Load Text Data.

    The Load Data Wizard appears.

  5. Under Load To, select either Existing table or New table.

  6. Under Load From, select either Upload file or Copy and paste.

  7. Follow the on-screen instructions.

Loading Spreadsheet Data

You can load spreadsheet data by either copying and pasting text, or by loading a file.

To load spreadsheet data:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Data Load/Unload.

  3. Click Load.

  4. Click Load Spreadsheet Data.

    The Load Data Wizard appears.

  5. Under Load To, select either Existing table or New table.

  6. Under Load From, select either Upload file or Copy and paste.

  7. Follow the on-screen instructions.

Loading an XML Document

Oracle Application Express supports XML documents in Oracle's canonical XML format.

In Oracle's canonical XML format, each element represents a column value, each element is named after the column, all elements that are part of the same row are children of a <ROW> element, and all <ROW> elements are children of a <ROWSET> element.

To load an XML document:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Data Load/Unload.

  3. Click Load.

  4. Click Load XML Data.

    The Load XML Data Wizard appears.

  5. Follow the on-screen instructions.

Unloading Data

You can use the Unload page to export the contents of a table to a text file or XML document.

Topics in this section include:

See Also:

"Choosing the Right Import/Export/Load/Unload Option"

Unloading a Text File

Use the Unload to Text Wizard to export the contents of a table to a text file. For example, you could export an entire table to a comma-delimited file (.csv).

To unload a table to a text file:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Data Load/Unload.

  3. Click Unload.

  4. Click Unload To Text.

    The Unload to Text Wizard appears.

  5. Follow the on-screen instructions.

You select the schema and choose the table and columns to be exported. Additionally, you can specify the type of separator to be used to separate column values and also whether column text strings are identified using single or double quotation marks.

Unloading to an XML Document

Use the Unload to XML Wizard to export the contents of a table to an XML document adhering to the Canonical XML specification.

To unload a table to an XML document:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Data Load/Unload.

  3. Click Unload.

  4. Click Unload to XML.

    The Unload to XML Wizard appears.

  5. Follow the on-screen instructions.

You select the schema and choose the table and columns to be exported.

Using Text Data Load Repository

Loaded text data files are stored in the Text Data Load Repository.

To access the Text Data Load Repository:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Data Load/Unload.

  3. Click Repository.

  4. To filter the display, make a selection from the Show list and click Go.

  5. To view information about a specific file, click the View icon.

  6. To delete an imported file, select it and click Delete Checked.

Generating DDL

With Oracle Application Express, you can generate data definition language statements from the Oracle data dictionary. These scripts can be used to create or re-create database schema objects. The scripts can be generated to display inline or saved as a script file. You can generate the create scripts for all objects for a specific schema, specific object types, or specific objects.

If you run Oracle Application Express with Oracle Database 10g release 1 (10.1) or later, you can generate data definition language statements from the Oracle data dictionary. These scripts can be used to create or re-create database schema objects. The scripts can be generated to the screen, or they can be saved as a SQL Script. You can generate the create scripts for all objects for a specific schema, specific object types, or specific objects.

To generate a DDL statement:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Generate DDL.

    The Generate DDL page appears.

  3. Click Create Script.

    The Generate DDL Wizard appears.

  4. Select a database schema and click Next.

  5. Define the object type:

    1. Output - Specify an output format. Select either Display Inline or Save As Script File.

    2. Check All - Select this option to include all object types for which to generate DDL.

    3. Object Type - Select the object types for which to generate DDL.

    4. To select object names for the selected object types, click Next and follow the on-screen instructions.

  6. Click Generate DDL.

See Also:

Viewing Object Reports

Utilities includes a variety of object reports to help you better manage the objects in your database.

Topics in this section include:

Table Reports

Use the Table reports to view specific details about the tables within your database.

To view the Table reports:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Object Reports.

    The Object Reports page appears.

  3. In the Table Reports section, select the report you want to view:

    • Table Columns

    • Table Comments

    • Table Constraints

    • Table Statistics

    • Table Storage Sizes

  4. To filter a report, enter search criteria in the fields provided or make selections from the lists, and click Go.

  5. To view reports for a different schema, select the schema from the Schema list on the upper right side of the page.

Security Reports

Use the Security reports to view object or column privileges granted on database objects owned by other schemas. You can also use these reports to view database role and system privileges.

To view the Security reports:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Object Reports.

    The Object Reports page appears.

  3. In the Security Reports section, click the report you want to view:

    • Object Grants - View the privileges for an existing schema and also understand what privileges have been granted from the selected schema to other schemas.

    • Column Privileges - View the privileges for an existing schema and also understand what privileges have been granted from the selected schema to other schemas.

    • Role Privileges - View the database roles that have been granted to a selected schema. Roles are collections of various privileges.

    • System Privileges - View the database privileges that have been granted to a selected schema.

  4. If available, you can filter the report by making a selection from the Show list and clicking Go.

  5. To view reports for a different schema, select the schema from the Schema list on the upper right side of the page.

PL/SQL Reports

Use the PL/SQL reports to view program unit arguments or unit line counts and also to search PL/SQL source code.

Topics in this section include:

Program Unit Arguments

Use the Program Unit Arguments report to view package input and output parameters.

To view the PL/SQL Unit Arguments report:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Object Reports.

    The Object Reports page appears.

  3. In the PL/SQL Reports section, click Program Unit Arguments.

  4. To filter the report, enter a query in PL/SQL Package or Program Unit and click Go.

  5. To view reports for a different schema, select the schema from the Schema list on the upper right side of the page.

Unit Line Counts

Use the Unit Line Counts report to view the number of lines of code for each object. Use this report to identify larger PL/SQL program units.

To view the Unit Line Counts report:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Object Reports.

    The Object Reports page appears.

  3. In the PL/SQL Reports section, click Unit Line Counts.

  4. To filter the report, enter an object name and click Go.

Search PL/SQL Source Code

Use the Search PL/SQL Source code page to search the text within your PL/SQL code. Use this report to find references to tables or functions you might be thinking of deleting. You can also use this page to locate code when you can only recall a code snippet.

To search for PL/SQL source code:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Object Reports.

    The Object Reports page appears.

  3. In the PL/SQL Reports section, click Search PL/SQL Source Code.

  4. To filter the report:

    1. In Object Name, enter a query.

    2. In Text, enter the PL/SQL code you want to search for.

    3. In From/To Line, enter the range of lines you want to search.

    4. Click Go.

  5. To view reports for a different schema, select the schema from the Schema list on the upper right side of the page.

Exception Reports

Use the Exception Reports to view unindexed foreign keys and tables without primary keys, indexes, or triggers.

To view Exception reports:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Object Reports.

    The Object Reports page appears.

  3. In the Exception Reports section, click the report you want to view.

    • Tables without Primary Keys

    • Tables without Indexes

    • Unindexed Foreign Keys

    • Tables without Triggers

  4. To filter the report, enter a table name and click Go.

All Object Reports

Use the All Object reports to view objects for the selected schema.

To view the All Object reports:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Object Reports.

    The Object Reports page appears.

  3. In the All Object Reports section, select the report you want to view:

    • All Objects - Sort objects by creation date and also last DDL (data definition language).

    • Invalid Objects - View all invalid objects in the database by object type.

    • Object Creation Calendar - View all objects in a calendar format based on the date each database object was created.

    • Object Counts by Type - View the number of database objects by type for the selected schema.

    • Data Dictionary - View the data dictionary for this database.

      An Oracle data dictionary is a set of tables and views used as a read-only reference about the database. For example, a data dictionary stores information about both the logical and physical structure of the database.

      A data dictionary also stores information about valid Oracle database users, integrity constraints for tables in the database, and the amount of space allocated for a schema object and the amount being used.

  4. For All Objects and Invalid Objects reports, you can filter the report:

    1. Select an object type.

    2. Enter an object name.

    3. Click Go.

  5. For the Data Dictionary report, you can query for details about database objects:

    1. Click the Data Dictionary View Name.

      The Data Dictionary Browser appears. Use this page to query the Oracle Data Dictionary for details about database objects.

    2. On the Data Dictionary Browser page, select the specific columns you want to see data for or Check All.

    3. Click Query.

      A report appears.

    4. To begin a new query on the same data dictionary view, click New Query.

    5. To browse another data dictionary view, click Browse Another View.

See Also:

Oracle Database Concepts for information about the data dictionary

Using the Recycle Bin to View and Restore Dropped Objects

You can use the Recycle Bin to view and restore dropped database objects. When you drop a table, the space associated with the table is not immediately removed. The Oracle database renames the table and places it and any associated objects in the Recycle Bin. You can recover objects in the Recycle Bin at a later time.

This section contains the following topics:

Note:

The Recycle Bin feature is only available if you run with an Oracle 10g or later database.

See Also:

"Backing Up and Recovering the Database" in Oracle Database Express Edition 2 Day DBA

Managing Objects in the Recycle Bin

You can view objects in the Recycle Bin on the Dropped Objects page. Once you select an object and view the Object Details page, you can choose to purge the object or restore the object by clicking the appropriate button.

To view objects in the Recycle Bin:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Recycle Bin and then Dropped Objects.

    The Dropped Objects page appears.

  3. To filter the report, select an object type, enter the object name in the Original Name field, and click Go.

  4. To view object details, click the object name.

    The Object Details page appears.

  5. To restore the current object, click Restore Object.

  6. To permanently delete the current object, click Purge Object.

Emptying the Recycle Bin Without Viewing the Objects

To empty the Recycle Bin without viewing the objects:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Recycle Bin.

  3. Click Purge Recycle Bin.

    The Purge Recycle Bin page appears.

  4. Confirm your request by clicking Purge Recycle Bin again.

Reviewing Application Express Views

You can review Application Express views and also run queries within a view to find specific information.

To review Application Express views:

  1. On the Workspace home page, click the Utilities icon.

  2. Click APEX Views.

    The Application Express Views page appears.

  3. To change the appearance of the page, make a selection from the View list and click Go:

    • Icons (the default) displays each Application Express view as a large icon.

    • Tree displays each Application Express view in a hierarchy.

    • Report displays each view as a line in a report.

  4. Click the view you want to review or query.

    The Application Express View Details page appears.

  5. To run a query:

    1. Under Query Columns, select the columns to query.

      For a description of each column, read the information in the Description section on this page.

    2. (Optional) Under Query Conditions, specify the column, condition, and value for the query.

    3. Scroll to the top and click Go.

    4. Scroll down to the Data section to view the results of the query. Note that the SQL Query section shows the SQL query resulting from your selections.

Comparing Schemas

You can run a report that compares database objects in two schemas, displaying differences between them. You can compare all objects in the schemas or limit your report to specific objects. To compare two schemas, both must be available to your workspace.

Examples:

  • Compare DEMO_ objects by searching for that naming convention. The report indicates if the object exists in each of the two schemas.

  • Analyze the object details in the two schemas to determine why one implementation is different. For example, the report might show that an index in one schema has an additional column or a column with a different data type.

To compare schemas:

  1. On the Workspace home page, click the Utilities icon.

  2. Click Schema Comparison.

  3. On the Schema Comparison page, make the appropriate selections to run the comparison:

    • Schema 1 and Schema 2 - Select the schemas to compare.

    • Compare - Restrict the report to show one object type or select All to show all database objects.

    • Search - Enter a case insensitive query for the object name.

    • Display - To change the number of rows that appear in the report, make a selection from the Display list.

    • Go - Click Go to find the results matching your selections.

    • Show Differences Only or Show Details - Select the type of information you want to review.

Monitoring the Database

The reports available on the Database Monitor page provide a database-wide view of the database sessions, system statistics, SQL statements, and longer operations. You can use these reports to identify poorly preforming SQL and to better understand the workload of the database.

To access any of the icons on the Database Monitor page, you must have an account that has been granted an administrator role.

This section contains the following topics:

Sessions

A session is the connection of a user to an Oracle database instance. A session lasts from the time the user connects until the time the user disconnects or exits the database application.

You must have database administrator privileges to access the Sessions page.

To access reports on the Sessions page:

  1. On the Workspace home page, click the Utilities icon and then Database Monitor.

  2. Click Sessions.

  3. If prompted, enter the appropriate administrator user name and password and click Login.

    The Sessions page appears.

  4. To view a report, select one of the following tabs at the top of the page:

    • Sessions

    • Locks

    • Waits

    • I/O

    • SQL

    • Open Cursors

    The sections that follow describe each report.

Sessions Report

The Sessions Report displays information about the current sessions in the database. Use the controls at the top of page to narrow the view:

  • Search. Enter search criteria and click Go. For search details, click the Search label.

  • Status. Select a status and click Go.

  • Show. Select how many columns to display and click Go.

  • Display. Select the number of rows to appear in the report and click Go.

To view session details, click the Session ID (SID). The Session Details page appears. To remove the current session, navigate to the Session Details page and click Kill Session.

Locks Report

The Locks report displays a report of sessions which have locks that are blocking other session(s). To control the number of rows that appear, make a selection from the Display list and click Go.

Waits Report

The Waits report displays the wait events for each session. Use the controls at the top of page to narrow the view:

  • Search. Enter search criteria and click Go. For search details, click the Search label.

  • Status. Select a status and click Go.

  • Show. Select how many columns to display and click Go.

  • Display. Select the number of rows to appear in the report and click Go.

To view session details, click the Session ID (SID). The Session Details page appears. To remove the current session, click Kill Session.

I/O Report

The I/0 report displays details about the I/O for each session. Use the controls at the top of page to narrow the view:

  • Search. Enter search criteria and click Go. For search details, click the Search label.

  • Display. Select the number of rows to appear in the report and click Go.

To view session details, click the Session ID (SID). The Session Details page appears. To remove the current session, click Kill Session.

SQL Report

The SQL report displays details about the current or last SQL statement executed for each session. Use the controls at the top of page to narrow the view:

  • Search. Enter search criteria and click Go. For search details, click the Search label.

  • Status. Select a status and click Go.

  • Show. Select how many columns to display and click Go.

  • Display. Select the number of rows to appear in the report and click Go.

To view session details, click the Session ID (SID). The Session Details page appears. To remove the current session, click Kill Session.

Open Cursors

The Open Cursors report displays details about the number of open cursors for each session. Use the controls at the top of page to narrow the view:

  • Search. Enter search criteria and click Go. For search details, click the Search label.

  • Status. Select a status and click Go.

  • Display. Select the number of rows to appear in the report and click Go.

To view details about a specific open cursor count, click the numeric link under the Open Cursor Count column.

To view session details, click the Session ID (SID). The Session Details page appears. To remove the current session, click Kill Session.

About System Statistics

The System Statistics page displays statistics for:

  • Physical I/O. A physical I/O is an I/O that requires disk access. This report displays disk access statistics for physical reads and writes.

  • Logical I/O. An logical I/O is an I/O that is satisfied in memory or disk. Displays the sum of buffer reads which might be consistent gets or current mode gets. Redo is the buffer in the SGA that contains information about changes.

  • Memory Statistics. Displays memory consumption of the database.

  • Time Statistics. Shows various times consumed by the database.

  • SQL Cursor Statistics. Displays statistics about the cursors in the Oracle database.

  • Transaction Statistics. Shows the number of transactions performed.

To view the System Statistics page:

  1. On the Workspace home page, click the Utilities icon and then Database Monitor.

  2. Click System Statistics.

  3. If prompted, enter the appropriate administrator user name and password and click Login.

    The System Statistics page appears.

Additional controls on the System Statistics page include:

  • Refresh Report - Refresh the System Statistics report.

  • Save Statistics - Save the current report.

  • Show delta between current and saved values - Click this check box to display actual statistic values, or display deltas between an saved value and the current value.

    See Also:

    "Memory Configuration and Use" in Oracle Database Performance Tuning Guide

About Top SQL

The "top" SQL statements represent the SQL statements that are executed most often, that use more system resources than other SQL statements, or that use system resources more frequently than other SQL statements.

Use the Top SQL page to identify poorly performing SQL.

To view the Top SQL page:

  1. On the Workspace home page, click the Utilities icon and then Database Monitor.

  2. Click Top SQL.

  3. If prompted, enter the appropriate administrator user name and password and click Login.

    The Top SQL page appears. Use the search fields and lists and the top of the page and click Go to narrow the display. For details on each field or list, click the Search label.

  4. To access the SQL Plan page, click the View icon.

    Description of view_icon.gif follows
    Description of the illustration view_icon.gif

    The SQL Plan page appears, containing the following sections:

    • Query Plan - Contains a color coded explain plan. Note that unindexed columns display in red.

    • SQL Text - Displays the full text of the SQL statement.

    • Indexes - Displays all indexes on the table in the query. There is a checkmark when that index is used in the query.

    • Table Columns - Shows all columns on all tables or views in the query.

About Long Operations

The Long Operations page displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.

To view the Long Operations page:

  1. On the Workspace home page, click the Utilities icon and then Database Monitor.

  2. Click Long Operations.

  3. If prompted, enter the appropriate administrator user name and password and click Login.

  4. See Also:

    "V$SESSION_LONGOPS" in Oracle Database Reference

Viewing Database Details

You can view details about your database on the About Database page.

To access details about your database:

  1. On the Workspace home page, click the Utilities icon and then Database Monitor.

  2. Click About Database.

  3. If prompted, enter the appropriate administrator user name and password and click Login.

    The About Database page appears. The About Database page is divided into two sections: Database and Version.

  4. To view additional information about installed options, currently used features, or National Language Support, select one of the following check boxes and click Go:

    • Version

    • Settings

    • Options

    • National Language Support

    • CGI Environment

    • Parameters