| Oracle® HTML DB User's Guide Release 2.0 Part Number B16373-01 |
|
|
View PDF |
| Oracle® HTML DB User's Guide Release 2.0 Part Number B16373-01 |
|
|
View PDF |
This section describes how to use SQL Workshop utilities to import and export data from the database, generate DDL, view object reports, monitor the database, and restore dropped database objects.
This section contains the following topics:
You can import data into and export data from the hosted database using the Data Import/Export page. Supported import formats include:
Text file containing comma-delimited or tab-delimited data
XML documents
Spreadsheets
Supported export formats include:
Text such as comma-delimited or tab-delimited data
XML documents
This section contains the following topics:
To access the Data Import/Export page:
Click the SQL Workshop icon on the Workspace home page.
The SQL Workshop home page appears.
Click Utilities.
Click Data Import/Export.
The Data Import/Export page appears.
Click the appropriate icon to import data, export data, or view the Import Repository.
You can import data into the Oracle database using Oracle HTML DB 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:
For files less than 30KB, you can copy and paste tab-delimited data directly into the Import Text Wizard. For files larger than 30KB, you must upload a separate file.
To import a text file:
Navigate to the Data Import/Export page:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities.
Click Data Import/Export.
Click Import.
Click Import Text Data.
Under Import To, select either Existing table or New table.
Under Import from, select either Upload file or Copy and paste.
Follow the on-screen instructions.
Data Workshop supports the import of XML documents adhering to the Canonical XML specification.
To import an XML document:
You can load spreadsheet data by either copying and pasting text, or by importing a file. To copy and paste text, the spreadsheet file must be less than 30KB. For files larger than 30KB, you can import the file in a delimited format (such as comma-delimited (.csv) or tab-delimited), upload the file, and then load the data into a new or existing table.
To import spreadsheet data:
Navigate to the Data Import/Export page:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities.
Click Data Import/Export.
Click Import.
Click Import Spreadsheet Data.
Under Import to, select either Existing table or New table.
Under Import from, select either Upload file or Copy and paste.
Follow the on-screen instructions.
You can view imported information in the Text Data Import Repository.
To view imported data:
Navigate to the Data Import/Export page:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities.
Click Data Import/Export.
Click Import Repository.
To filter the view, make a selection from the Show list and click Go.
To view details about the imported file, click the Details icon.
To download a file, click the file name.
To delete a file, select the check box adjacent to the file name and click Delete Checked.
You can also use the Data Import/Export page to export the contents of a table to a text file or XML document.
Topics in this section include:
Use the Export Text Data 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 export a table to a text file:
Navigate to the Data Import/Export page:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities.
Click Data Import/Export.
Click Export.
Click Export To Text.
The Export to Text Wizard appears.
Follow the on-screen instructions.
You select the schema and choose the table and columns to be exported. You can also specify the type of separator to be used to separate column values as well as whether column text strings are identified using single or double quotation marks.
Use the Export XML Wizard to export the contents of a table to an XML document adhering to the Canonical XML specification.
To export a table to an XML document:
Navigate to the Data Import/Export page:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities.
Click Data Import/Export.
Click Export.
Click Export to XML.
Follow the on-screen instructions.
You select the schema and choose the table and columns to be exported.
Imported files are stored in the Text Data Import Repository.
To access the Text Data Import Repository:
Navigate to the Data Import/Export page:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities.
Click Data Import/Export.
Click Import Repository.
To filter the display, make a selection from the Show list and click Go.
To view information about a specific file, click the View icon.
To delete an imported file, select it and click Delete Checked.
If you are running Oracle HTML DB with Oracle Database 10g release 1 (10.1) or later, you can generate Data Definition Language (DDL) statements from the Oracle data dictionary. These scripts can be used to create or recreate 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:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities and then Generate DDL.
The Generate DDL Wizard appears.
Follow the on-screen instructions.
|
See Also:
|
SQL Workshop includes a variety to object reports to help you better manage the objects in your database.
Topics in this section include:
Use the reports on the All Objects page to view all objects for the selected schema. Available reports include All Objects, Invalid Objects, Object Calendar, and Objects Counts by Type.
To access the reports available on the All Objects page:
Click the SQL Workshop icon on the Workspace home page.
The SQL Workshop home page appears.
Click Utilities, Object Reports, and then All Objects.
Select one of the following reports:
All Objects. Sort objects by creation date as well as last DDL (data definition language). To filter the report, select a object type, specify an object name, and click Go.
Invalid Objects. View all invalid objects in the database by object type. To filter the report, enter an object name, select a object type, and click Go.
Object Calendar. View all objects in a calendar format based on the date each database object was created.
Object Counts by Type. View counts of database object types for the selected schema.
Make a selection from the Schema list (optional).
To filter the report, select a object type, specify an object name, and click Go.
Each Oracle database has a data dictionary. An Oracle data dictionary is a set of tables and views that are 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 as well as how much of it is being used.
To browse the data dictionary:
Click the SQL Workshop icon on the Workspace home page.
The SQL Workshop home page appears.
Click Utilities, Object Reports, and then Data Dictionary.
The Data Dictionary appears, listing all the Oracle Data Dictionary views
To filter the report, enter a query in the Search field and click Go.
You can query for details about database objects in the Data Dictionary Browser.
Click the View icon to display Data Dictionary Browser. Use this form to query the Oracle Data Dictionary for details about database objects.
In the Data Dictionary Browser, select the appropriate views and click Query.
|
See Also: Oracle Database Concepts for information about the data dictionary |
PL/SQL reports enable you to view program unit arguments or unit line counts or search PL/SQL source code.
Topics in this section include:
Use the Program Unit Arguments report to view package input and output parameters.
To view the PL/SQL Arguments report:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities and then Object Reports.
Click PL/SQL and then Program Unit Arguments.
To filter the report, enter a query in Object Name and click Go.
Use the Unit Line Counts report to view then 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:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities and then Object Reports.
Click PL/SQL and then Unit Line Counts.
To filter the report, enter an object name and click Go.
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:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities and then Object Reports.
Click PL/SQL and then Search PL/SQL Source.
To filter the report:
In Object Name, enter a query.
In Text, enter the PL/SQL code you want to search for.
Click Go.
Security reports enable you to see privileges granted on database objects owned by other schemas. You can also use these reports to view database roles and system privileges. The SQL injection report is only available with Oracle Database 10g release 1 (10.2) or later.
Topics in this section include:
Role Privileges report shows the database roles that have been granted to a selected schema. Roles are collections of various privileges.
To view Role Privileges:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities and then Object Reports.
Click Security and then Role Privileges.
The Role Privileges report appears.
The Object Grants report identifies privileges granted from or to the selected database schema. Use this report to determine the privileges for an existing schema has as well understand what privileges have been granted from the selected schema to other schemas.
To view the Object Grants report:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities, Object Reports, and Security.
Click User Privileges and then Object Grants.
The Object Grants report appears.
From Schema, select the database schema owner.
To filter the report, make a selection from the Show list and click Go.
The Column Privileges report identifies column privileges granted from or to the selected database schema. Use this report to determine the privileges for an existing schema as well as understand what privileges have been granted from the selected schema to other schemas.
To view the Column Privileges report:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities, Object Reports, and Security.
Click User Privileges and then Column Privileges.
The Column Privileges report appears.
From Schema, select the database schema owner.
To filter the report, make a selection from the Show list and click Go.
A SQL infection is a potential security vulnerability for any database-driven application. In a SQL Injection attack, the attacker modifies the parameters of an application in order to change the SQL statements passed to the database. For example, a SQL Injection attack could pass the text of a SQL statement or clause instead of user data from the application UI. A successful SQL injection attack may corrupt the database or grant access to privileged data.
|
Note: The SQL injection report is only available with Oracle Database 10g release 1 (10.2) or later. |
You can use the following two reports to check for vulnerabilities within a given schema:
Compile PL/SQL - This report analyzes and reports how user values concatenated into the text of dynamic SQL statements are transformed and passed through the whole application.
Review Vulnerabilities - Displays a report of potential vulnerable code.
To run the Compile PL/SQL Injection report:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities, Object Reports, and then Security.
Click SQL Injection and then Compile PL/SQL.
Select a schema from the Schema list on the right side of the page.
Only objects in the current schema display. Remember that the values available in the schema depend upon your workspace privileges.
To search for an object, enter a case insensitive query in the Object field and click Go.
Select the program units to be compiled and click Compile.
To remove program units, select the program units to be removed and click Remove Checked.
To view the Review Vulnerabilities report:
Run the Compile PL/SQL Injection report as described in the previous procedure.
Click the Review Vulnerabilities icon.
The Potential Vulnerable Code report appears, displaying packages, procedures, and links to potential program units with vulnerabilities.
To search for a package or process, enter a case insensitive query in the Search field and click Go.
To view details about a specific program unit, select the appropriate link.
To access the SQL Injection Tree View, click the Tree icon.
You can view specific details about the tables within your database by accessing the reports available on the Tables page.
To view the reports available on the Tables page:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities.
Click Object Reports and then Tables.
The Tables page appears.
Select a report as report to review.
To filter each report, enter search criteria in the fields provided and click Go.
The Database Monitor page features a variety reports that describe the activity, storage, and configuration of the current database instance.
|
Note: Only users having a database user account that has been granted a DBA role can access the Database Monitor page. |
This section contains the following topics:
The reports available under Activity detailed 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 gain a better understand the workload of the database.
To access database activity reports:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities and then Database Monitor.
Accessing the Database Monitor page requires database administrator privileges. You must have a database account that has been granted a DBA role.
When prompted, enter the appropriate username and password and click Login.
Under Activity, select the appropriate report.
The following sections describe each report page.
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.
The Sessions page contains six reports as described Table 21-1. To view a report, select the appropriate tab.
Table 21-1 Database Monitor Activity Reports
The System Statistics report 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. Displays memory consumption of the database.
Time. Shows various times consumed by the database
SQL Cursor. Displays statistics about the cursors in the Oracle database. See
Transaction. Shows the number of transactions performed.
Additional controls on the System Statistics page include:
Refresh Report - Click this button to refresh the System Statistics report.
Save Statistics - Click this button to save the current report.
Removed Saved Statistics - Click this link to create a baseline which is used on subsequent refreshes of the page. The statistics will then show deltas from what was saved and what is current.
|
See Also: "Memory and Configuration Use" and "Cursor Access and Management" in Oracle Database Performance Tuning Guide |
Use the Top SQL page to identify poorly performing SQL. Use the search fields and lists and the top of the page to narrow the display.
Click the View icon to access the SQL Plan page. The SQL Plan page contains 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.
This view 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.
|
See Also: V$SESSION_LONGOPS" in Oracle Database Reference |
Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace. The reports available under Storage provide information about tablespaces, data files, and free space.
To access database storage reports:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities and then Database Monitor.
Accessing the Database Monitor page requires database administrator privileges. You must have a database account that has been granted a DBA role.
When prompted, enter the appropriate username and password and click Login.
Under Storage, select the appropriate icon:
Tablespaces -Displays the size of each tablespace in MB as well as the number of related data files.
Data Files - Displays information about each data file, including the related table space, related file name, size in MB, and auto extensible status.
Free Space - Displays free space in the database by tablespaces.
|
See Also: Oracle Database Reference for information about tablespaces and data files |
The reports in the Configuration section of the Database Monitor provide details about how the database is configured. This information is useful in understanding your database version and configuration options.
To access database storage reports:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities and then Database Monitor.
Accessing the Database Monitor page requires database administrator privileges. You must have a database account that has been granted a DBA role.
When prompted, enter the appropriate username and password and click Login.
Under Configuration, select the appropriate icon:
Click Database to view details about the current database instance. The About Database page is divided into two sections: Database and Version. To view additional information about installed options, currently used features, or National Language Support, expand the following:
Options
Feature Usage
National Language Support
CGI Environment
Click Parameters to view configuration parameters for the current database instance.
If you are running Oracle HTML DB with Oracle Database 10g release 1 (10.1) or later, 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 where it can be recovered at a later time.
This section contains the following topics:
|
Note: The Recycle Bin feature is only available if you are running Oracle HTML DB with an Oracle 10g or later database. |
To view objects in the Recycle Bin:
Click the SQL Workshop icon on the Workspace home page.
Click Utilities.
Click Recycle Bin.
Click View Objects.
To filter the report, select an object type, enter the object name in the Original Name field, and click Go.
Click the View icon to access the Object Detail page. On this page you can:
To view object details, click the View icon adjacent to the original name.
On the Object Details page, you can:
Click Restore Object to restore the current object
Click Purge to permanently delete the current object
