40 DBMS_CSX_ADMIN

Using DBMS_CSX_ADMIN

• Overview

• Constants

---

Overview

This package can be used by DBAs to customize the setup when transporting a tablespace containing binary XML data. The use of the package is not required in order for a transportable tablespace job to run.

By default, all binary XML tables will use the default token table set, which will be replicated during transport on the target database. To avoid the cost of transporting a potentially large token table set, the DBA may opt for registering a new set of token tables for a given tablespace. The package provides routines for token table set registration and lookup.

---

Constants

The DBMS_CSX_ADMIN package uses the constants shown in Table 40-1:

---

Examples

Example 1: Register a New Set of Token Tables for the Tablespace

To prepare a tablespace CSXTS for export, the DBA can register a new set of token tables for the tablespace with the following PL/SQL:

DECLARE
  tsno   number;
  stmt   varchar2(2000);
BEGIN
  stmt := 'SELECT ts# FROM ts$ WHERE (name = ''' || 'CSXTS' || ''')';
  EXECUTE IMMEDIATE stmt into tsno;
  DBMS_CSX_ADMIN.REGISTERTOKENTABLESET(
    tstabno     =>  tsno, 
    guid        =>  NULL,
    flags       =>  DBMS_CSX_ADMIN.TBS_LEVEL, 
    tocreate    =>  DBMS_CSX_ADMIN.WITH_INDEXES,
    defaulttoks =>  DBMS_CSX_ADMIN.DEFAULT_TOKS);
  COMMIT;
END;
/

In this example, the new token tables are indexed, populated with default token mappings, and associated with all tables in the CSXTS tablespace. All subsequent loads of binary XML data in CSXTS will make use of the new set of token tables. The advantage is that no loading of binary XML data in other tablespaces will affect the size of the token tables used by CSXTS and exported during a tablespace export of CSXTS. This setup is suited to the case in which the tablespace does not yet contain binary XML data.

Example 2: Copying the Default Token Tables in a New Set

If binary XML data already exists in the tablespace to be exported, the DBA has the option of copying the default token tables in a new set.

DECLARE
  tsno   number;
  stmt   varchar2(2000);
  qntab  varchar2(34);
  nmtab  varchar2(34);
  pttab  varchar2(34);
BEGIN
  stmt := 'select ts# from ts$ where (name = ''' || 'CSXTS' || ''')';
  EXECUTE IMMEDIATE stmt INTO tsno;
  DBMS_CSX_ADMIN.COPYDEFAULTTOKENTABLESET(
    tstabno => tsno, qntab, nmtab, pttatb);
  COMMIT;
END;
/

This setup is suited to the case in which the DBA wishes to optimize the export of a tablespace that already contains binary XML data, and does not have associated a token table set. After cloning the default token table set, all subsequent loads of binary XML data in CSXTS will make use of the new set of token tables.

Summary of DBMS_CSX_ADMIN

---

COPYDEFAULTTOKENTABLESET Procedure

This procedure creates a new token table set associated with a given tablespace, and populates the token tables with the token mappings from the default token tables.

Syntax

DBMS_CSX_ADMIN.COPYDEFAULTTOKENTABLESET  (
   tsno         IN    NUMBER,
   qnametable   OUT   VARCHAR2,
   nmspctable   OUT   VARCHAR2,
   pttable      OUT   VARCHAR2);

Parameters

---

GETTOKENTABLEINFO Procedure & Function

Given the table name and the owner, the first overload of the procedure returns the globally unique identifier (GUID) of the token table set where token mappings for this table can be found. The procedure returns also the names of the token tables, and whether the token table set is the default one.

Given the object number of a table, the second overload of the procedure returns the GUID of the token table set used by the table, and whether this is the default token table set.

Syntax

DBMS_CSX_ADMIN.GETTOKENTABLEINFO  (
   ownername      IN   VARCHAR2,
   tablename      IN   VARCHAR2,
   guid           OUT  RAW,
   qnametable     OUT  VARCHAR2,
   nmspctable     OUT  VARCHAR2,
   level          OUT  NUMBER,
   tabno          OUT  NUMBER);

DBMS_CSX_ADMIN.GETTOKENTABLEINFO  (
   tabno          IN   NUMBER,
   guid           OUT  RAW);
 RETURN BOOLEAN;

Parameters

---

GETTOKENTABLEINFOBYTABLESPACE Procedure

Given a tablespace number, this procedure returns the GUID and the token table names for this tablespace.

Syntax

DBMS_CSX_ADMIN.GETTOKENTABLEINFOBYTABLESPACE  (
   tsname          IN   VARCHAR2,
   tablespaceno    IN   NUMBER,
   guid            OUT  RAW,
   qnametable      OUT  VARCHAR2,
   nmspctable      OUT  VARCHAR2,
   isdefault       OUT  BOOLEAN,
   containTokTab   OUT  BOOLEAN);

Parameters

---

NAMESPACEIDTABLE Function

This procedure returns default namespace-ID token table.

Syntax

DBMS_CSX_ADMIN.NAMESPACEIDTABLE 
  RETURN VARCHAR2;

---

PATHIDTABLE Function

This procedure returns the default path-ID token table. This is used for granting permissions on the default path-ID token table for a user before executing EXPLAIN PLAN for a query on an XML table with an XML index.

Syntax

DBMS_CSX_ADMIN.PATHIDTABLE 
  RETURN VARCHAR2;

---

QNAMEIDTABLE Function

This procedure returns the default qname-ID token table.

Syntax

DBMS_CSX_ADMIN.QNAMEIDTABLE 
  RETURN VARCHAR2;

---

REGISTERTOKENTABLESET Procedure

This procedure registers a new token table set, creates (if required) the token tables (with the corresponding indexes).

Syntax

DBMS_CSX_ADMIN.REGISTERTOKENTABLESET  (
   tstabno      IN  NUMBER  DEFAULT NULL,
   guid         IN  RAW     DEFAULT NULL, 
   flags        IN  NUMBER  DEFAULT TBS_LEVEL,
   tocreate     IN  NUMBER  DEFAULT WITH_INDEXES,
   defaulttoks  IN  NUMBER  DEFAULT DEFAULT_TOKS);

Parameters