CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_CSX_ADMIN
Version 11.1
 
General

Note
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.
Source $ORACLE_HOME/rdbms/admin/dbmsxdba.sql

Constants
Name Data Type Value
DEFAULT_LEVEL BINARY_INTEGER 0
TAB_LEVEL BINARY_INTEGER 1
TBS_LEVEL BINARY_INTEGER 2
NOREG_LEVEL BINARY_INTEGER 3
NO_CREATE BINARY_INTEGER 0
NO_INDEXES BINARY_INTEGER 1
WITH_INDEXES BINARY_INTEGER 2
DEFAULT_TOKS BINARY_INTEGER 0
NO_DEFAULT_TOKS BINARY_INTEGER 1
Dependencies
DBMS_ASSERT KU$_JOBDESC_1020 USER_TABLESPACES
DBMS_DATAPUMP KU$_STATUS USER_USERS
DBMS_XDBUTIL_INT SESSION_ROLES  
Security Model Execute is granted to the DBA role
 
CopyDefault TokenTableSet

Copy a token table set
dbms_csx_admin.copyDefaultTokenTableSet(
tsno       IN  NUMBER,
qnametable OUT VARCHAR2,
nmspctable OUT VARCHAR2,
pttable    OUT VARCHAR2);
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;
/
 
GetTokenTableInfo

Given the table name and the owner, returns the guid of the token table set where token mappings for this table can be found. Returns also the names of the token tables, and whether the token table set is the default one

Overload 1
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);
TBD

Overload 2
dbms_csx_admin.GetTokenTableInfo(tabno IN NUMBER, guid OUT RAW)
RETURN BOOLEAN;
TBD
 
GetTokenTableInfoByTablespace
Given a tablespace number, returns the guid and the token table names for this tablespace. If there is no entry in XDB$TTSET for this tablespace, it assumes the default guid is isued, and returns TRUE in isdefault. ContainTokTabs is set to TRUE if the token tables for guid are actually in this tablespace. (This is needed for procedural actions for TTS.) 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);
TBD
 
GuidFrom32
Undocumented dbms_csx_admin.guidFrom32(guid32 IN VARCHAR2) RETURN VARCHAR2;
SELECT dbms_csx_admin.guidFrom32('Morgan') FROM dual;
 
GuidTo32
Undocumented dbms_csx_admin.guidTo32(guidhex IN VARCHAR2) RETURN VARCHAR2;
SELECT dbms_csx_admin.guidTo32(SYS_GUID()) FROM dual;
 
Instance_Info_Exp
Undocumented dbms_csx_admin.instance_info_exp(
name      IN  VARCHAR2,
schema    IN  VARCHAR2,
prepost   IN  PLS_INTEGER,
isdba     IN  PLS_INTEGER,
version   IN  VARCHAR2,
new_block OUT PLS_INTEGER)
RETURN VARCHAR2;
TBD
 
NamespaceIDTable
Undocumented dbms_csx_admin.namespaceIDTable RETURN VARCHAR2;
TBD
 
PathIDTable
Undocumented dbms_csx_admin.pathIDTable RETURN VARCHAR2;
TBD
 
QNameIDTable
Undocumented dbms_csx_admin.QNameIDTable RETURN VARCHAR2
TBD
 
RegisterTokenTableSet

Registers a token table set: adds an entry in XDB$TTSET corresponding to the new token table set, and creates (if required) the token tables (with the corresponding indexes).
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);
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;
/
 
UpdateMasterTable
Undocumented dbms_csx_admin.updateMasterTable(
name   IN VARCHAR2, 
schema IN VARCHAR2, 
level  IN NUMBER,
guid   IN RAW,
toksuf IN VARCHAR2);
TBD
 
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [310 users online]    © 2010 psoug.org