| 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 administer the Oracle Database Express Edition (Oracle Database XE) database.
To access any Database Administration pages, you must have a database account that has been granted a DBA role.
This section contains the following topics:
Use the Administration page to administer Oracle Database XE. To access any Database Administration pages, you must have a database account that has been granted a DBA role.
The Administration page contains the following icons:
Storage. Use the Storage page to monitor the amount of space available in your database, view tablespaces, view datafiles, compact storage, or view logging status. See "Monitoring Database Storage".
Memory. Use the Memory page to view or alter memory allocation for your database. See "Monitoring Memory".
Database Users. Use the Manage Database Users page to create, edit, or drop database users. See "Managing Database User Accounts".
Monitor. Click the Monitor icon to access reports that provide a database-wide view of the database sessions, system statistics, SQL statements, and long operations. See "Monitoring the Database".
About Database. View details about your Oracle Express Server database including the product version, installed options, currently used features, or National Language Support. See "Viewing Database Details".
About the Tasks List
A Tasks list displays on the right side of the Administration page. This list contains the following links:
Change My Password. See "Changing Your Password".
Manage Login Message. See "Changing the Login Message".
Manage HTTP Access. See "Managing HTTP Access to the User Interface".
You can change your password by clicking Change Password on the Tasks list on the Administration page.
To change your password:
On the Database Home Page, click the Administration icon.
On the Tasks list, click Change My Password.
In the Old Password field, enter your old password.
In the New Password field, enter a new password.
In the Confirm Password field, enter your new password again.
Click Apply Changes.
Passwords can be set to expire when a database user account is created. When a password expires, the user is prompted to create a new one.
To change an expired password:
In the Old Password field, enter your old password.
In the New Password field, enter a new password.
In the Confirm Password field, enter your new password again.
Click Apply Changes.
See Also:
"Creating a Database User"You can change the message that displays when users log in to Oracle Database XE by clicking Manage Login Message on the Tasks list on the Administration page.
To change the user interface login message:
On the Database Home Page, click the Administration icon.
On the Tasks list, click Manage Login Message.
If prompted, enter the appropriate administrator username and password and click Login.
From Login Message, select Custom Message.
In the Custom Message field, enter a new message.
Click Apply Changes.
Because Oracle Database XE is limited to 4 GB of user data, you need to monitor the amount of space available in your database. Use the Storage page to view a summary of the amount of space available in your database, view tablespaces, view datafiles, compact the available storage, or view logging status.
Tip:
You can also navigate to the Storage page by clicking the Storage link on the Usage Monitor. See "About the Usage Monitor"About the Database Size Limitation
Oracle Database XE data is limited to 4 GB of user data. However, the maximum amount of physical storage in the database is limited an overall size of 5 GB. This physical maximum size limit includes the system tablespace, but excludes rollback and temporary tablespace segments.
Topics in this section include:
See Also:
"Managing Database Storage" in Oracle Database Express Edition 2 Day DBA GuideYou can view a summary of the amount of space allocated, potentially available, and allowable on the Storage page. If you notice your database is approaching the 4 GB limit, consider dropping unused objects to free up space, compacting the storage, or upgrading to Oracle Database Standard Edition or Enterprise Edition.
To access the Storage page, you must have a database account that has been granted a DBA role.
On the Database Home Page, click the Administration icon and then Storage.
If prompted, enter the appropriate administrator username and password and click Login.
The Storage page appears and displays the following:
Space Allocated. Displays the amount of space in MB currently allocated to database user objects.
Available. Displays the amount of space in MB currently available.
Physical Limit. Displays the amount of space in MB available for database objects.
Percent Used. Displays a percentage that indicates the space used in comparison with the maximum database size permitted.
See Also:
"Managing Database Storage" in Oracle Database Express Edition 2 Day DBA Guide and "About the Usage Monitor"You can recover unused free space in the database on the Compact Storage page. Compacting storage is accomplished using a database job that runs in the background. This database job frees up unused spaces allocated for tables and reorganizes data for optimal space utilization.
To access the Storage page, you must have a database account that has been granted a DBA role.
To compact your database storage:
On the Database Home Page, click the Administration icon and then Storage.
If prompted, enter the appropriate administrator username and password and click Login.
On the Tasks list, click Compact Storage.
The Compact Storage page appears and displays the following:
Space Allocated. Displays the amount of space in MB currently allocated to database user objects.
Available. Displays the amount of space in MB currently available.
Physical Limit. Displays the amount of space in MB available for database objects.
Percent Used. Displays a percentage that indicates the space used in comparison with the maximum database size permitted.
Click Compact Storage.
The process may take several minutes.
Tip:
You can also navigate to the Storage page by clicking the Storage link on the Usage Monitor. See "About the Usage Monitor"See Also:
"Managing Database Storage" in Oracle Database Express Edition 2 Day DBA Guide and "Monitoring the Space Available"A database consists of one or more tablespaces. A tablespace is a logical grouping of physical datafiles. Each tablespace consists of one or more physical datafiles or tempfiles.
You can view a list of the tablespaces in your database on the Tablespaces page. To access the Tablespaces page, you must have a database account that has been granted a DBA role.
Note:
All user data is stored in the
USERS tablespace. If you notice that the USERS tablespace is approaching the 4 GB limit, consider dropping unused objects to free up space, or upgrading to Oracle Database Standard Edition or Enterprise Edition.To access the Tablespaces page:
On the Database Home Page, click the Administration icon and then Storage.
If prompted, enter the appropriate administrator username and password and click Login.
The Storage page appears. See "Monitoring the Space Available".
On the Tasks list, click View Tablespaces.
The Tablespaces page appears and displays the following:
Tablespace name. Displays the tablespace name.
Percent Used. Displays a bar chart and percentage that indicates the space used in comparison with the maximum database size permitted.
Allocated. Displays the amount of space in MB currently allocated to database user objects.
Used. Displays the amount of space in MB currently used.
Datafile. Indicates the number of datafiles in the tablespace.
To view additional details, click the tablespace name.
The Datafile page appears and displays the following information:
Tablespace name
File Name
Current size
Maximum size
Type of extent management (autoextensible or not)
See Also:
"Managing Database Storage" in Oracle Database Express Edition 2 Day DBA GuideDatafiles are the operating system files that hold the data within the database. The data is written to these files in an Oracle proprietary format that cannot be read by programs other than an Oracle database.
You can view a list of datafile in your database on the Data Files page. To access the Data Files page, you must have a database account that has been granted a DBA role.
To access the Data Files page:
On the Database Home Page, click the Administration icon and then Storage.
If prompted, enter the appropriate administrator username and password and click Login.
The Storage page appears. See "Monitoring the Space Available".
On the Tasks list, click View Data Files.
The Data Files page appears and displays the following:
Tablespace (name)
File Name
Size (MB)
Maximum Size (MB)
Auto Extensible
Auto Extensible indicates the type of extent management (that is, autoextensible or not).
Use the controls at the top of the page to control the page display:
To search for a data file, enter a file name in the Search Data File Name field and click Go.
To view a specific tablespace, select a tablespace from the Storage list and click Go.
To control the number or rows that display, make a selection from the Display list and click Go.
See Also:
"Managing Database Storage" in Oracle Database Express Edition 2 Day DBA GuideEvery Oracle database includes two or more redo log files. You can view redo log files on the Database Logging page. A set of redo log files is collectively known as the redo log for the database. These log files function to record all changes made to data.
Archiving your redo log files is an effective way to protect your database from media failure.
See Also:
"About Database Storage Structures" in Oracle Database Express Edition 2 Day DBA Guide for more information on redo log filesTopics in this section include:
To access the Database Logging page:
On the Database Home Page, click the Administration icon and then Storage.
If prompted, enter the appropriate administrator username and password and click Login.
The Storage page appears. See "Monitoring the Space Available" .
On the Tasks list, click View Logging Status.
The Database Logging page appears.
Tip:
You can also navigate to the Database Logging page by clicking the Log Archiving link on the Usage Monitor. See "About the Usage Monitor"Redo log files are the most crucial structure for database recovery. This set of files is collectively known as the redo log for the database. A redo log is made up of redo entries, which are also called redo records.The primary function of the redo log is to record all changes made to data in the database. If an Oracle instance failure or operating system failure prevents modified data from being permanently written to the datafiles, the changes can be recovered from the redo log so that committed data updates are not lost.
See Also:
"About Database Storage Structures" in Oracle Database Express Edition 2 Day DBA Guide and Oracle Database Administrator's Guide for more details about redo logOracle Database XE stores database backups and archived redo logs in the flash recovery area. The flash recovery area is a directory, separate from the database itself, where recovery-related structures are stored.
The default configuration of Oracle Database XE stores the flash recovery area on the same disk as your database files. In this configuration, if a media failure occurs, you can lose both your database and your backups. For any database where data protection is essential, change the location of the flash recovery area so that it is stored on a different disk.
See Also :
"Setting Flash Recovery Area Location and Size" in Oracle Database Express Edition 2 Day DBA GuideOracle Database XE can be configured to be in ARCHIVELOG mode or in NOARCHIVELOG mode. In ARCHIVELOG mode, a background archiving process copies filled, inactive redo log files to the flash recovery area before they are reused. In NOARCHIVELOG mode, the Oracle Database XE is not configured to archive its redo logs.
To simplify the management of your database, log archiving is disabled by default. Oracle recommends that you enable ARCHIVELOG mode for optimal data protection.
See Also:
"Enabling ARCHIVELOG Mode for Media Failure Protection" in Oracle Database Express Edition 2 Day DBA Guide
Oracle Database Administrator's Guide for more details about redo logs and log archiving
Oracle Database XE has a 1 GB memory limit. Use the Memory page to view or alter the current memory allocation for your database. There are two types of memory that the Oracle instance allocates: System Global Area (SGA) and Program Global Area (PGA).
To access any Database Administration pages, you must have a database account that has been granted a DBA role.
Tip:
You can also navigate to the Memory page by clicking the Memory link on the Usage Monitor. See "About the Usage Monitor"Topics in this section include:
See Also:
"Managing Database Memory" in Oracle Database Express Edition 2 Day DBA Guide and "About the Usage Monitor"The System Global Area (SGA) is a memory area that contains data shared between all database users such as buffer cache and a shared pool of SQL statements. SGA is allocated in memory when an Oracle Database XE instance is started. The SGA contains the following subcomponents:
Buffer Cache. Functions as the buffer to store any data being queried or modified. All user processes connected to the database share access to the buffer cache. The buffer cache helps avoid repeated access from physical disk, a time consuming operation.
Shared Pool. Caches operational information and code that can be shared among users. For example:
SQL statements are cached so that they can be reuse.
Information from the data dictionary, such as user account data, table and index descriptions, and privileges, is cached for quick access and reusability.
Stored procedures are cached for faster access.
Redo Log Buffer. Improves performance by caching redo information (used for instance recovery) until it can be written at once and at a more opportune time to the physical redo log files that are stored on disk.
Large Pool. Functions as an optional area for buffering large I/O requests for various server processes
See Also:
"Managing Database Memory" in Oracle Database Express Edition 2 Day DBA GuideThe Program Global Area (PGA) is a memory area used by a single Oracle server process. A server process services a client's requests. Each server process has its own private PGA area, which gets created by Oracle Database XE when it starts the process. The PGA is used to process SQL statements and to hold logon and other session information.
To view current memory utilization:
On the Database Home Page, click the Administration icon.
Click Memory.
If prompted, enter the appropriate administrator username and password and click Login.
The Memory page appears and displays the following:
Maximum System Global Area (SGA). Displays Maximum SGA memory in MB. This size sets the SGA_MAX_SIZE database parameter. See "About System Global Area Memory".
Program Global Area (PGA). Displays PGA memory in MB. See "About Program Global Area Memory".
Current Configuration (SGA + PGA). Displays the sum of SGA and PGA memory in MB.
To configure SGA or PGA, click Configure SGA or Configure PGA on the Tasks list on the right side of the page.
The System Global Area (SGA) is a memory area that contains data shared between all database users such as buffer cache and a shared pool of SQL statements. SGA is allocated in memory when an Oracle Database XE instance is started, and any change in the value will take effect at the next startup.
The amount of memory allocated to SGA directly affects the performance of your database. The SGA sizes are configured automatically when you install Oracle Database XE.
See Also:
"Managing Database Memory" in Oracle Database Express Edition 2 Day DBA GuideTo alter SGA memory:
On the Database Home Page, click the Administration icon.
Click Memory.
If prompted, enter the appropriate administrator username and password and click Login.
The Memory page appears. See "Viewing Current Memory Utilization".
From the Task list, select Configure SGA.
In SGA Target, enter a new value in MB.
Click Apply Changes.
For SGA size changes, you must shut down and restart the database for the changes to take effect.
See Also:
"Starting Up and Shutting Down" in Oracle Database Express Edition 2 Day DBA GuideThe Program Global Area (PGA) is a memory area used by a single Oracle server process. The amount of memory allocated to the PGA directly affects the performance of your database. The PGA sizes are configured automatically when you install Oracle Database XE.
To alter PGA memory:
On the Database Home Page, click the Administration icon.
Click Memory.
If prompted, enter the appropriate administrator username and password and click Login.
The Memory page appears. See "Viewing Current Memory Utilization".
On the Tasks list, click Configure PGA.
In Aggregate PGA Target, enter a new value in MB.
Click Apply Changes.
See Also:
"Managing Database Memory" in Oracle Database Express Edition 2 Day DBA GuideYou access Oracle Database XE using a database user account. Each database user account has a username and specifically defined the user's attributes, including a password for database authentication, privileges and roles, default tablespace for database objects, default tablespace for query processes, and tablespace quota.
To access any Database Administration pages, you must have a database account that has been granted a DBA role.
Tip:
You can also navigate to the Manage Database Users page by clicking the Users link on the Usage Monitor. See "About the Usage Monitor"Topics in this section include:
See Also:
"Managing Users and Security" in Oracle Database Express Edition 2 Day DBA Guide, "About the Usage Monitor", and "Managing Application Express End Users"You edit an existing database user account on the Manage Database Users page.
To edit an existing user account:
On the Database Home Page, click the Administration icon and then Database Users.
If prompted, enter the appropriate administrator username and password and click Login.
The Manage Database Users page appears.
By default each account displays as a large icon. The appearance of each icon indicates the current account status:
A small lock image indicates that the account is locked. Locking an account disables the database login.
A small clock image indicates that the account has an expired password. If an account has an expired password, the user will be prompted to select a new password upon the next login.
A small lock image and a clock image indicate that the account is both expired and locked.
Icons without a small lock or clock image are open and available to users.
You can use the fields and buttons at the top of the page to search for a specific user or alter the page view.
To search for an existing database user:
To locate a specific user, type a username or partial string in the Search Username field and click Go.
To view all users, leave the Search Username field blank and click Go
To alter the types of users that display, make a selection from the Show list and click Go. Available options include:
All Users
Internal Users
Database Users
To change the page display, make a selection from the View list. View options include:
Icons (the default) displays each database user account as a large icon identified by the username.
Details displays each database user account as a line in a report. Each line includes the username, account status, lock date, expiration date, default tablespace, temporary tablespace, and how long ago the account was created.
When you create a database user, you also create a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the username and can be used to unambiguously refer to objects owned by the database user.
To create a database user account:
On the Database Home Page, click the Administration icon and then Database Users.
If prompted, enter the appropriate administrator username and password and click Login.
The Manage Database Users page appears.
Click Create.
The Create Database User page appears. Fields marked with a red asterisk (*) are required.
Specify user credentials:
Username - Enter a username for database authentication.
Password- Enter a password for database authentication.
Confirm Password - Enter the password again.
Select Expire Password to prompt users to change their password the next time they log in.
Use Account Status to control whether a user can log into the account. Valid options include:
Unlocked. Select this option to enable a user to log in.
Locked. Select this option to prevent a user from logging in.
Next, select a role for this user. A role is a group of related database privileges.
Under Roles, select the appropriate role:
CONNECT - Enables a user to connect to the database and perform basic database actions. Assign this role to any user or application that needs database access. CONNECT includes the system privilege CREATE SESSION.
RESOURCE - Enables a user to create schema objects. Assign this role only to developers and power users. RESOURCE includes the following system privileges: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, and CREATE TYPE.
DBA - Enables a user to access the database objects of all other users and administer the database. Does not include the privileges to start up or shut down the database. DBA includes the system privilege WITH ADMIN OPTION.
To have more control over the privileges assigned to a user, you can create your own roles or augment the selected roles by selecting additional privilege.
Under Directly Grant Privileges, select additional privileges for the user. To select all privileges, click Check All. To deselect all privileges, click Uncheck All.
Note that you can manage additional user attributes using SQL Commands. See "Using SQL Commands".
Click Create.
See Also:
"Changing Your Password" and "Administering User Privileges, Roles, and Profiles" in Oracle Database Security GuideTo edit an existing user account:
On the Database Home Page, click the Administration icon and then Database Users.
If prompted, enter the appropriate administrator username and password and click Login.
The Manage Database Users page appears.
Use the fields and buttons at the top of the page to search for a specific user or alter the page view. See "Viewing Database Users".
To edit account details, select a user.
The User page appears.
To edit a user's password:
Password- Enter a password for database authentication.
Confirm Password - Enter the password again.
Select Expire Password to prompt users to change their password the next time they log in.
Use Account Status to control whether a user can log into the account. Valid options include:
Unlocked. Select this option to enable a user to log in.
Locked. Select this option to prevent a user from logging in.
Use Roles to specify a group of related database privileges for this user.
Under Roles, select the appropriate role:
CONNECT - Enables a user to connect to the database and perform basic database actions. Assign this role to any user or application that needs database access. CONNECT includes the system privilege CREATE SESSION.
RESOURCE - Enables a user to create schema objects. Assign this role only to developers and power users. RESOURCE includes the following system privileges: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, and CREATE TYPE.
DBA - Enables a user to access the database objects of all other users and administer the database. Does not include the privileges to start up or shut down the database. DBA includes the system privilege WITH ADMIN OPTION.
To have more control over the privileges assigned to a user, you can create your own roles or augment the selected roles by selecting additional privilege.
Under Directly Grant Privileges, select additional privileges for the user. To select all privileges, click Check All. To deselect all privileges, click Uncheck All.
Note that you can manage additional user attributes using SQL Commands. See "Using SQL Commands".
To view current system privileges granted to the current user, click All System Privileges Granted to at the bottom of the page.
A report appears.
Click Alter User.
See Also:
"Changing Your Password" and "Administering User Privileges, Roles, and Profiles" in Oracle Database Security GuideWhen you drop a database user, you also drop all objects owned by the user—that is, all objects in the user's schema.
To drip an existing user account:
On the Database Home Page, click the Administration icon and then Database Users.
If prompted, enter the appropriate administrator username and password and click Login.
The Manage Database Users page appears.
You can locate an existing database user as follows:
To locate a specific user, type a username or partial string in the Username field and click Go.
To view all users, leave the Find User field blank and click Go
Select a user.
The User page appears.
Click Drop.
Select Cascade and click Drop User.
A report region appears displaying existing applications and scripts. Applications and scripts associated with the user's account will be deleted when the user account is dropped.
Note:
In order to drop a database user whose schema contains database objects, you must select Cascade. An error will result if you do not select Cascade and the database user's schema contains database objects.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 gain a better understand the workload of the database.
To access any of the icons on the Database Monitor page, you must have a database account that has been granted a DBA role.
This section contains the following topics:
See Also:
"Monitoring the Database" in Oracle Database Express Edition 2 Day DBA GuideA 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.
Tip:
You can also navigate to the Sessions page by clicking the Sessions link on the Usage Monitor. See "About the Usage Monitor"You must have database administrator privileges in order to access the Sessions page.
To access reports on the Sessions page:
On the Database Home Page, click the Administration icon and then Monitor.
If prompted, enter the appropriate administrator username and password and click Login.
Click Sessions.
The Sessions page appears.
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.
See Also:
"About the Usage Monitor"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 item help.
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.
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.
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 item help.
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.
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 item help.
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.
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 item help.
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.
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 item help.
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.
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:
On the Database Home Page, click the Administration icon and then Monitor.
If prompted, enter the appropriate administrator username and password and click Login.
Click the System Statistics icon.
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 GuideThe "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:
On the Database Home Page, click the Administration icon and then Monitor.
If prompted, enter the appropriate administrator username and password and click Login.
Click the Top SQL icon.
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 item help
To access the SQL Plan page, click the View icon.
The SQL Plan page appears.
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.
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:
On the Database Home Page, click the Administration icon and then Monitor.
If prompted, enter the appropriate administrator username and password and click Login.
Click the Long Operations icon.
See Also:
V$SESSION_LONGOPS" in Oracle Database ReferenceYou can view details about your database on the About Database page.
To access details about your database:
Click the Administration icon.
Click About Database.
If prompted, enter the appropriate administrator username and password and click Login.
The About Database page appears. 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, select one of the following check boxes and click Go:
Version
Database
Options
Feature Usage
National Language Support
CGI Environment
If you are using HTTP server, you can control access to the Oracle Database XE user interface on the Manage HTTP Access page. By default, only HTTP requests that originate from the same physical server where the database is installed are able to access the Oracle Database XE user interface. You can change this default behavior on the Manage HTTP Access page.
Note:
The Manage HTTP Access page only controls access to the user interface if you are using HTTP serverTo access the Manage HTTP Access page:
On the Database Home Page, click the Administration icon.
On the Tasks list, click Manage HTTP Access.
If prompted, enter the appropriate administrator username and password and click Login
The Manage Access page appears.
Under Access, select one of the following:
Available only from local server. HTTP access is only allowed for HTTP requests that originate from the same physical server where the database is installed. This is the default setting.
Selecting this option restricts access to the Oracle Database XE user interface as well as any other application using the HTTP server.
Available to remote clients. HTTP access to the user interface is enabled for all clients on your network that have a browser. Note that selecting this option may introduce additional security risks.
Click Apply Changes.
The embedded PL/SQL gateway provides Oracle Database XE with a Web server and also the necessary infrastructure to create dynamic applications. The embedded PL/SQL gateway runs in the XML database HTTP server in the Oracle Database XE database. It includes the core features of mod_plsql, but does not require the Oracle HTTP server powered by Apache.
To establish connectivity from a browser via HTTP, the installation process creates database access descriptor (DAD) that is mapped to a virtual path. A DAD is a set of configuration values used to access the database and the virtual path mapping makes applications accessible under a virtual path of XML DB HTTP Server.
Topics in this section include:
The embedded PL/SQL gateway supports a directive called request-validation-function which enables you to name a PL/SQL function which the embedded PL/SQL gateway will call for each HTTP request. You can use this functionality to restrict the procedures that can be invoked through the embedded PL/SQL gateway. The function returns true if the named procedure in the current request is allowed and false if it is not allowed. You can use this function to enforce access restrictions for Oracle Database XE on a per-database access descriptor (DAD) basis.
Oracle Application Express ships with a request-validation-function named wwv_flow_epg_include_modules.authorize. This function specifies access restrictions appropriate for the standard DAD configured for Oracle Application Express. During installation, scripts also name this function in the request-validation-function directive in the XDB configuration file.
During installation, the installer also creates a PL/SQL function in the Oracle Application Express product schema (FLOWS_xxxxxx). You can change and recompile this function in order to restrict access. The source code for this function is not wrapped and can be found in the database administrators product core directory in the file named wwv_flow_epg_include_local.sql. The source code is as follows:
CREATE OR REPLACE FUNCTION
wwv_flow_epg_include_mod_local(
PROCEDURE_NAME IN VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
RETURN FALSE; -- remove this statement when
you add procedure names to the "IN" list
IF UPPER(procedure_name) IN (
'') THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END wwv_flow_epg_include_mod_local;
/
To add names of procedures that should be allowed:
Remove or comment out the RETURN FALSE statement that immediately follows the BEGIN statement:
...
BEGIN
RETURN FALSE; -- remove this statement when
you add procedure names to the "IN" list
...
Add names to the clause representing procedure names that should be allowed to be invoked in HTTP requests. For example to allow procedures PROC1 and PROC2 the IN list you would write IN ('PROC1', 'PROC2').
After changing the source code of this function, alter the Oracle Application Express product schema (FLOWS_xxxxxx) and compile the function in that schema.
To alter the product schema, FLOWS_xxxxxx :
Log in to SQL Command Line (SQL*Plus) as SYS or SYSTEM.
Alter the product schema (FLOWS_xxxxxx) by entering the following command:
ALTER SESSION SET CURRENT_SCHEMA FLOWS_xxxxxx;
Compile the function wwv_flow_epg_include_local.sql.
See Also:
"Using SQL Command Line" in Oracle Database Express Edition 2 Day Developer GuideThe wwv_flow_epg_include_mod_local function is called by Oracle Application Express's request-validation-function which itself is called by the embedded PL/SQL gateway. The Oracle Application Express function first evaluates the request and based on the procedure name, approves it, rejects it, or passes it to the local function, wwv_flow_epg_include_mod_local, which can evaluate the request using its own rules.
When you create new DADs for use with Oracle Application Express through the embedded PL/SQL gateway, the request-validation-function directive should be specified exactly as it is for the DAD created when you install Oracle Database XE, that is, the function wwv_flow_epg_include_modules.authorize should be named in the directive.
If you have no additional restrictions beyond those implemented in the wwv_flow_epg_include_modules.authorize function, there is no need to take any action with respect to the source code for the wwv_flow_epg_include_mod_local function.
