| Oracle® Database Express Edition Application Express User's Guide Release 2.1 Part Number B25309-01 |
|
|
View PDF |
| Oracle® Database Express Edition Application Express User's Guide Release 2.1 Part Number B25309-01 |
|
|
View PDF |
This section describes how to use SQL utilities to load and unload data from the Oracle Database Express Edition (Oracle Database XE) database, generate DDL, view object reports, and restore dropped database objects.
This section contains the following topics:
You can load data into and unload data from the Oracle Database XE database using the Data Load/Unload page. Supported load formats include:
Text file containing comma-delimited or tab-delimited data
XML documents
Spreadsheets
Supported unload formats include:
Text such as comma-delimited or tab-delimited data
XML documents
This section contains the following topics:
See Also:
"Importing, Exporting, Loading, and Unloading Data" in Oracle Database Express Edition 2 Day DBA GuideTo access the Data Load/Unload page:
On the Database Home Page, click the Utilities icon.
Click Data Load/Unload.
The Data Load/Unload page appears.
Click the appropriate icon to load data, unload data, or view the repository.
You can load data into the Oracle Database XE 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:
For files less than 30KB, you can copy and paste tab-delimited data directly into the Load Data Wizard. For files larger than 30KB, you must upload a separate file.
To load a text file:
Oracle Application Express supports XML documents adhering to the Canonical XML specification.
To load an XML document:
You can load spreadsheet data by either copying and pasting text, or by loading 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 load spreadsheet data:
On the Database Home Page, click the Utilities icon.
Click Data Load/Unload.
Click Load.
Click Load Spreadsheet Data.
The Load Data Wizard appears.
Under Load to, select either Existing table or New table.
Under Load from, select either Upload file or Copy and paste.
Follow the on-screen instructions.
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:
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:
On the Database Home Page, click the Utilities icon.
Click Data Load/Unload.
Click Unload.
Click Unload To Text.
The Unload 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 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:
On the Database Home Page, click the Utilities icon.
Click Data Load/Unload.
Click Unload.
Click Unload to XML.
Follow the on-screen instructions.
You select the schema and choose the table and columns to be exported.
Loaded text data files are stored in the Text Data Load Repository.
To access the Text Data Load Repository:
On the Database Home Page, click the Utilities icon.
Click Data Load/Unload.
Click 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.
With Oracle Application Express, 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 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.
To generate a DDL statement:
On the Database Home Page, click the Utilities icon.
Click Generate DDL.
The Generate DDL page appears.
Click Create Script.
The Generate DDL Wizard appears.
Select a database schema and click Next.
Define the object type:
Output - Specify an output format. Select either Display Inline or Save As Script File.
Object Type - Select the object types for which to generate DDL.
To select object names for the selected object types, click Next and follow the on-screen instructions.
Click Generate DDL.
See Also:
Oracle Database SQL Reference for information about DDL statements
"The Data Dictionary" in Oracle Database Concepts for information about the data dictionary
SQL 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:
On the Database Home Page, click the Utilities icon.
Click 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.
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:
On the Database Home Page, click the Utilities icon.
Click 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.
Click the View icon to display Data Dictionary Browser.
The Data Dictionary Browser appears. Use this page to query the Oracle Data Dictionary for details about database objects.
On the Data Dictionary Browser page, select the appropriate views and click Query. To select all views, select Check All and then click Query.
A report appears.
To create a new query, click New Query and repeat step 5.
To browse another view, click Browse Another View and repeat steps 4 and 5.
See Also:
Oracle Database Concepts for information about the data dictionaryPL/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:
On the Database Home Page, click the Utilities icon.
Click Object Reports, 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:
On the Database Home Page, click the Utilities icon.
Click Object Reports, 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:
On the Database Home Page, click the Utilities icon.
Click Object Reports, 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.
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:
On the Database Home Page, click the Utilities icon.
Click Object Reports, 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:
On the Database Home Page, click the Utilities icon.
Click Object Reports and then 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:
On the Database Home Page, click the Utilities icon.
Click Object Reports and then 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.
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:
On the Database Home Page, click the Utilities icon.
Click Object Reports and then Tables.
The Tables page appears.
Select a report to review. Available reports include:
Columns
Comments
Constraints
Statistics
Storage Sizes
No Primary Key
Unindexed
Unindexed Foreign Keys
To filter a report, enter search criteria in the fields provided and click Go.
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 are running with an Oracle 10g or later database.See Also:
"Backing Up and Recovering the Database" in Oracle Database Express Edition 2 Day DBA GuideYou 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:
On the Database Home Page, click the Utilities icon.
Click Recycle Bin and then Dropped Objects.
The Dropped Objects page appears.
To filter the report, select an object type, enter the object name in the Original Name field, and click Go.
To view object details, click the object name.
The Object Details page appears.
To restore the current object, click Restore Object.
To permanently delete the current object, click Purge Object.
