26 Managing Oracle XML DB Using Oracle Enterprise Manager

Getting Started with Oracle Enterprise Manager and Oracle XML DB

Oracle Enterprise Manager is supplied with Oracle Database, both Enterprise and Standard Editions. To run the Enterprise Manager version that supports Oracle XML DB functionality, use Oracle9i release 2 (9.2) or higher.

Configure Oracle XML DB

Oracle XML DB is managed through the configuration file, xdbconfig.xml. Through Enterprise Manager, you can view or configure parameters for this file. To access the Oracle XML DB configuration options, from the Enterprise Manager right hand window, select Configure XML Database. See "Configuring Oracle XML DB with Enterprise Manager" .

Create and Manage Resources

To access the XML resource management options, select the XML Database object in the Navigator and click Create a Resource in the detail view. See "Creating and Managing Oracle XML DB Resources with Enterprise Manager" .

Manage XML Schema and Related Database Objects

To access the XML schema management options, select the XML Database object in the Navigator and click Create Table and Views Based on XML Schema in the detail view. See "Managing XML Schema and Related Database Objects".

You can register, delete, view, generate (that is, reverse engineer) an XML schema and see its dependencies. You can also view an XML schema contents and perform actions on its constituent elements.

• Views. Create or alter an object view of XMLType, and look at the corresponding indexes.

• Tables. Create XML schema-based and non-XML schema-based XMLType tables and columns, and look at the corresponding indexes. You can also specify LOB storage attributes on these columns.

  You can create XMLType tables in CLOB or object-relational form, and specify constraints and LOB storage attributes on hidden XMLType columns.

• Indexes. Create index on the hidden columns of XMLType.

• DML operations. You can also perform other DML operations such as inserting and updating rows using XML instance documents.

• XML Schema Elements. You can view elements in their XML form and the XML instance data stored in the database corresponding to that element. You can also view the XML schema-dependent objects, such as tables, views, indexes, object types, array types, and table types.

Figure 26-2 Enterprise Manager Console: XML Database Management Window

Description of the illustration oem_xdb.jpg

XML Database Management Window: Right-Hand Dialog Windows

From the XML Database Management detail view, you can access the XML DB management functionality. From there you can select which task you must perform.

Hierarchical Navigation Tree: Navigator

Use the left-hand Navigator, to select the Oracle XML DB resources and database objects you must view or modify.

Viewing or Editing Oracle XML DB Configuration Parameters

With Enterprise Manager you can view and edit partial Oracle XML DB configuration parameters, in the following categories:

Administering Individual Resources

Once you select a resource sub-folder under the main Resources folder in the Navigator, you can see the resources General Page in the detail view, as shown in Figure 26-7.

Figure 26-7 Enterprise Manager: Individual Oracle XML DB Resources - General Page

Description of the illustration res_gen_page.gif

Security Page

Oracle XML DB Resources Security page changes the ACL associated with the XML DB resource container or file. Use the ACL files to restrict access to all XML DB resources. When you change the ACL file for the resource, you change the access privileges for the resource file. See Figure 26-8.

To specify a new ACL file:

1. Click Specify a new ACL File and then choose a new ACL file from the drop down list in the File Name field.

2. Click Apply to apply the change.

3. Click Revert to abandon any changes you have made to the File Name field.

Figure 26-8 Enterprise Manager: Individual Oracle XML DB Resource - Security Page

Description of the illustration res_security_page.gif

Individual Resource Content Menu

Figure 26-9 shows the context-sensitive menu displayed when you select and right-click an individual Oracle XML DB resource object in the Navigator.

Figure 26-9 Enterprise Manager: When You Right-Click an Individual Resource...

Description of the illustration res_rmouse_menu.gif

You can perform the following Oracle XML DB tasks from the Enterprise Manager Content Menu:

• Create Resource

• Grant Privileges On...

• Show Contents

• Show Grantee

Create Resource

Figure 26-10 shows how you can use the Create XML DB Resource dialog box to create an XML DB resource container or file. From the XML DB Resource dialog you can name the resource and then either create a new resource container or create a new resource file, designating the location of the file as either a local file, a file at a specified URL, or specifying the contents of the file.

1. Access the Create XML DB Resource dialog box by right clicking on the Resources folder or any individual resource node and selecting Create from the context menu. When you name the resource in the Name field, you can change the location by clicking on the Change Location button to the right of the Location field.

2. Specify whether the resource you are creating is a container or a file. If you create a file by choosing Create a new resource file, you can select from one of three file type location options:

   • Local File -- Select Use a file in the local file system to browse for a file location on your network.

   • File at URL -- Select Use a file at the URL to enter the location of the file on the Internet or intranet.

   • File Contents -- Select Specify the resource contents here to enter the contents of a file in the edit box located at the bottom of the Create XML DB Resource dialog box.

Figure 26-10 Enterprise Manager: Create Oracle XML DB Resource Dialog Box

Description of the illustration res_create.gif

Grant Privileges On...

Figure 26-11 shows the Grant Privileges On dialog box which assigns privileges on an Oracle XML DB resource to users or roles. You can grant multiple privileges to multiple users or roles. Grant Privileges On dialog box lists the available Oracle XML DB resource privileges in the Grant section at the top of the panel.

1. To grant privileges, select the privileges you want to grant by clicking on a privilege. You can select multiple privileges by holding down the Ctrl key while selecting more than one privilege. You can select consecutive privileges by clicking the first privilege in a sequence and holding down the Shift key while clicking the last privilege in the sequence.

2. Select the user or group in the To: box at the bottom of the dialog page. Use the same procedure to select multiple users or roles to which to grant privileges.

   
   

   See Also: "Enterprise Manager and Oracle XML DB: ACL Security".

   
   

Figure 26-11 Enterprise Manager: Granting Privileges to Oracle XML DB Resources

Description of the illustration res_grant_dlg.gif

Show Contents

Figure 26-12 is an example of a Show Contents dialog box. It displays the contents of the selected resource file.

Figure 26-12 Enterprise Manager: Show Contents Menus of Individual Oracle XML DB Resource

Description of the illustration show_contents.gif

Show Grantee

Figure 26-11 shows the Show Grantee of XML DB Resource dialog box. It displays a list of all granted privileges on a specified XML DB resource for the connected Enterprise Manager user. Show Grantee dialog box lists the connected Enterprise Manager user, privilege, and granted status of the privilege in tabular format.

Figure 26-13 Enterprise Manager: Show Grantee of Oracle XML DB Resources

Description of the illustration show_grantee.gif

Enterprise Manager and Oracle XML DB: ACL Security

From Enterprise Manager you can restrict access to all XML DB resources by means of ACLs. You can grant XML DB resource privileges to database user and database role separately using the existing Security/Users/user and Security/Roles/role interface, respectively.

You can access the Enterprise Manager security options in two main ways:

• To view or modify user (or role) security: In the Navigator, under the Oracle XML DB database in question Security >Users >user (or > Security > Roles > role). In the detail view, select the XML tag. See Figure 26-14. This user security option is described in more detail in "Granting and Revoking User Privileges with User > XML Tab ".

• To view or modify a resource security: Select the individual resource node under the Resources folder in the left navigation panel. Select the Security tag in the detail view. See Figure 26-15.

Granting and Revoking User Privileges with User > XML Tab

This section describes how to grant and revoke privileges for a user. The same procedure applies when granting and revoking privileges for a role. To grant privileges to a user follow these steps:

1. Select a particular user from the Enterprise Manager Navigator. The detail view displays an additional tab, XML, in the existing property sheet.

2. To view and select resources on which you want to grant privileges to users or roles, select the XML tab. Once you select a resource, all available privileges for that resource are displayed in the Available Privileges list to the right of the Resources list.

3. Select the privileges required from the Available Privileges list and move them down to the Granted list that appears at the bottom of the window by clicking on the down arrow.

   Conversely, you can revoke privileges by selecting them in the Granted list and clicking on the up arrow.

4. After setting the appropriate privileges, click the Apply button to save your changes. Before you save your changes, you can click the Revert button to abandon your changes.

Figure 26-14 Adding or Revoking Privileges with Users > user> XML

Description of the illustration security_user.gif

XML Database Resource Privileges

Privilege can be aggregate (contain other privileges) or atomic (cannot be subdivided). The following system privileges are supported:

• Atomic Privileges

  • all

  • read-properties

  • read-contents

  • update

  • link (applies only to containers)

  • unlink (applies only to containers)

  • read-acl

  • write-acl-ref

  • update-acl

  • link-to

  • unlink-from

  • resolve

  • dav:lock

  • dav:unlock

• Aggregate Privileges

  • dav:read (read-properties, read-contents, resolve)

  • dav:write (update, link, unlink, unlink-from)

  • dav:read-acl (read-acl)

  • dav:write-acl (write-acl-ref, update-acl)

  • dav:all (dav:read, dav:write, dav:read-acl, dav:write-acl, dav:look, dav:unlock)

Figure 26-15 Granting Privileges on a Resource

Description of the illustration res_grant_dlg.gif

Navigating XML Schema in Enterprise Manager

Under the XML Schema folder, the tree lists all the XML schema owners. The example here is owner XDB. See Figure 26-16:

• Schema Owners. Under the individual XML schema owners, the tree lists the XML schemas owned by the owner(s). Here you can see:

  http://xmlns.oracle.com/xdb/XDBResource.xsd
  http://xmlns.oracle.com/xdb/XDBSchema.xsd
  http://xmlns.oracle.com/xdb/XDBStandard.xsd

• Top level elements. Under each XML schema, the tree lists the top level elements. These elements are used to create the XMLType tables, tables with XMLType columns, and views. For example, Figure 26-16 shows top level elements servlet and LINK. The number and names of these elements are dictated by the relevant XML schema definition, which in this case is: http://xmlns.oracle.com/XDBStandard.xsd.

• Dependent objects. Under each element, the tree lists the created dependent objects, Tables, Views, and User Types respectively. In this example, you can see that top level element servlet has dependent XMLType Tables, Views, and User types.

• Dependent object owners. Under each dependent object type, the tree lists the owner.

  • Tables. For example, under Tables, XDB is an owner, and XDB owns a table called SERVLET.

    • Table characteristics. Under each table name the tree lists any created Indexes, Materialized View Logs (Snapshots), Partitions, and Triggers.

  • Views. Not shown here but under Views you would see any view owners and the name of views owned by these owners:

    • View characteristics. These are not listed here.

  • User Types. The tree lists any user types associated with the top level element servlet. These are listed according to the type:

    • Object types. Under Object types the tree lists the object type owners.

    • Array types. Under Array types the tree lists the array type owners.

    • Table types. Under table types the tree lists the table type owners.

Figure 26-16 Enterprise Manager Console: Navigating XML Schema

Description of the illustration tree.gif

Figure 26-17 Enterprise Manager: Creating XML Schema-Based Objects

Description of the illustration html2.gif

Registering an XML Schema

Registering an XML schema is one of the central, and often first, tasks before you use Oracle XML DB. XML schema are registered using DBMS_XMLSCHEMA.registerSchema().

Figure 26-18 shows you how to register an XML schema.

Options Page

From the Options page you can select the following options:

• Generate the object types based on this XML schema

• Generate tables based on this XML schema

• Generate Java beans based on this XML schema

• Register this XML schema regardless of any errors

See Figure 26-19. Press Create from either the General Tab or Options Tab to register this XML schema.

Figure 26-18 Enterprise Manager: Registering an XML Schema - General Page

Description of the illustration xschema1.gif

Figure 26-19 Enterprise Manager: Creating XML Schema - Options Page

Description of the illustration xschema2.gif

Creating Tables

You have two main options when creating tables:

• Creating an XMLType Table

• Creating Tables with XMLType Columns

Creating Views

To create an XMLType view, see "Creating a View Based on XML Schema".

Creating Function-Based Indexes

To create function-based indexes see "Creating a Function-Based Index Based on XPath Expressions".

Creating an XMLType Table

From the Create Table property sheet, enter the desired name of the table you are creating on the General page. Select the table owner from the drop-down list Schema. Leave Tablespace at the default setting. Select XMLType table.

Under the lower Schema option, select the XML schema owner, from the drop-down list.

Under XML Schema, select the actual XML schema from the drop-down list.

Under Element, select the required element to from the XMLType table, from the drop-down list.

Specify the storage:

• Store as defined by the XML schema. When you select this option, hidden columns are created based on the XML schema definition annotations. Any SELECTs or UPDATEs on these columns are optimized.

• Store as CLOB. When you select CLOB the LOB Storage tab dialog appears. Here you can customize the CLOB storage. See Figure 26-21.

Figure 26-20 Enterprise Manager: Creating XMLType Tables-General Page

Description of the illustration xtab1.gif

Figure 26-21 Enterprise Manager: Creating XMLType Tables - Specifying LOB Storage

Description of the illustration xtab2.gif

Creating Tables with XMLType Columns

See Figure 26-22 shows the Create Table General page. To create a table with XMLType columns follow these steps:

1. From the Create Table property sheet, enter the desired name of the table you are creating on the General page.

2. Select the table owner from the drop-down list Schema. Leave Tablespace at the default setting.

3. Select Define Columns.

4. Enter the Name. Enter the Datatype; select XMLType from the drop-down list. The XMLType Options dialog window appears. See Figure 26-23.

Figure 26-22 Enterprise Manager: Creating Tables With XMLType Column - General Page

Description of the illustration xtcol1.gif

From this screen you can specify for a particular XMLType column, whether it is XML schema-based or non-schema-based.

If it is XML schema-based:

1. Under the "Schema" option, select the XML schema owner, from the drop-down list.

2. Under "XML Schema", select the actual XML schema from the drop-down list.

3. Under "Element", select the required element to form the XMLType column, from the drop-down list.

4. Specify the storage:

5. Store as defined by the XML schema.

6. Store as CLOB. When you select CLOB the LOB Storage tab dialog appears. Here you can customize the CLOB storage.

If it is non-schema-based then you are not required to change the default settings.

Figure 26-23 Enterprise Manager: Creating Tables With XMLType Column - XMLType Options

Description of the illustration xtcol2.gif

Creating a View Based on XML Schema

Figure 26-24 shows the Create View General page. To create a view based on an XML schema, follow these steps:

1. Enter the desired view name. Under Schema, select the view owner from the drop-down list.

2. Enter the SQL statement text in the Query Text window to create the view. Select the Advanced tab. See Figure 26-25.

   From here you can select Force mode

3. Select the As Object option. The view can be set to Read Only or With Check Option.

4. Because this is an XMLType view, select the As Object option. Select XMLType not Object Type.

5. Under the Schema option, select the XML schema owner, from the drop-down list.

6. Under XML Schema, select the actual XML schema from the drop-down list.

7. Under Element, select the required element to form the XMLType column, from the drop-down list.

8. Specify the Object Identifier (OID):

   • If your SQL statement to create the view is based on an object type table, then select the Use default if your query is based on...

   • Otherwise, select Specify based on XPath expression on the structure of the element. Enter your XPath expression. See Chapter 16, " XMLType Views".

Figure 26-24 Enterprise Manager: Creating an XMLType View - General Page

Description of the illustration xview1.gif

Figure 26-25 Enterprise Manager: Creating XMLType Views - Advanced Page

Description of the illustration xview2.gif

Creating a Function-Based Index Based on XPath Expressions

See Figure 26-26 shows the Create Index General page. To create a function-based index based on an XPath expression follow these steps:

1. Provide the owner and name of the index. Under Name enter the name of the required index. Under Schema select the owner of the index from the drop-down list.

2. Under Index On, select Table.

3. Under the lower Schema select the table owner from the drop-down list.

4. Under Table, select either the XMLType table or table with XMLType column from the drop-down list.

   You can also enter an alias for a column expression. You can specify this alias inside your function-based index statement.

5. For tables with XMLTypecolumns, first click the lower left-hand + icon. This creates a new row for you to enter your extract XPath expression under Table Columns.

6. For XMLType tables, a new empty row is automatically created for you to create your extract XPath expression under Table Columns.

7. Datatype defaults to Column Expression. You are not required to change this.

Figure 26-26 Enterprise Manager: Creating a Function-Based Index Based on XPath Expressions

Description of the illustration xindex1.gif