Oracle DBMS_CSX_ADMIN
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
Purpose 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.
AUTHID CURRENT_USER
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 DBMS_STATS KU$_PARMAMVALUES1010
DBMS_CSX_INT DBMS_XDBUTIL_INT KU$_STATUS
DBMS_DATAPUMP KU$_JOBDESC1210 PLITBLM
DBMS_PRIV_CAPTURE KU$_PARAMVALUE1010  
DBMS_STANDARD    
Documented Yes
First Available 11.1.0.6
Security Model Owned by XDB with EXECUTE granted to the DBA role
Source $ORACLE_HOME/rdbms/admin/dbmsxdba.sql
Subprograms
 
CopyDefaultTokenTableSet
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;
/
 
GatherTokenTableStats
Gather stats on default token tables dbms_csx_admin.GatherTokenTableStats;
exec dbms_csx_admin.GatherTokenTableStats;
 
GetSequenceInfo
Returns the sequence information about a 12.2 style token table. Oracle need's this to construct a SQL statement which will run on the import side dbms_csx_admin.GetSequenceInfo(
guid     IN  RAW,
seqowner OUT VARCHAR2,
seqname  OUT VARCHAR2,
seqstart OUT NUMBER);
TBD
 
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 the tablespace. If there is no entry in XDB$TTSET for the tablespace, it assumes the default guid is issued, and returns TRUE in isdefault. ContainTokTabs is set to TRUE if the token tables for guid are in the tablespace. 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
 
GetTokenTableNames
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. dbms_csx_admin.getTokenTableNames(
ownername  IN  VARCHAR2,
tablename  IN  VARCHAR2,
qnametable OUT VARCHAR2,
nmspctable OUT VARCHAR2,
pttable    OUT VARCHAR2);
DECLARE
 gname VARCHAR2(30);
 nmspctab VARCHAR2(30);
 pttable VARCHAR2(30);
BEGIN
  dbms_csx_admin.getTokenTableNames('XDB', 'XDB$ACL', gname, nmspctab, pttable);
  dbms_output.put_line(gname);
  dbms_output.put_line(nmspctab);
  dbms_output.put_line(pttable);
END;
/
 
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
 
MoveTokenTables
Given the table name, the owner and tablespace name, move the xml token set tables to different tablespace dbms_csx_admin.moveTokenTables(
ownername IN VARCHAR2,
tablename IN VARCHAR2,
tsname    IN VARCHAR2);
BEGIN
  dbms_csx_admin.moveTokenTables('XDB', 'XDB$ACL', 'UWDATA');
END;
/
 
NamespaceIDTable
Returns default namespace-id token table dbms_csx_admin.namespaceIDTable RETURN VARCHAR2;
SELECT dbms_csx_admin.namespaceIDTable FROM dual;
 
PathIDTable
Returns default path-id token table dbms_csx_admin.pathIDTable RETURN VARCHAR2;
SELECT dbms_csx_admin.pathIDTable FROM dual;
 
Post_Import_DDL_DML
Returns calls the right dml generator based on the type of CSX tables on the export side (Central/Table/Tablespace level) dbms_csx_admin.post_import_ddl_dml(
name   IN VARCHAR2,
schema IN VARCHAR2)
RETURN VARCHAR2;
SQL> SELECT dbms_csx_admin.post_import_ddl_dml('XDB$ACL', 'XDB') FROM dual;

DBMS_CSX_ADMIN.POST_IMPORT_DDL_DML('XDB$ACL','XDB')
-------------------------------------------------------------------------------------------------------------------------
BEGIN
xdb.dbms_csx_int.genTTMapping('4E6D13C2039E0EEBE0530100007FE62D','2EDK9T40TV1SLV0KO10007WPHD', 'XDB$ACL','XDB','XDB$IMP
ORT_QN_INFO','XDB$IMPORT_NM_INFO','XDB$IMPORT_PT_INFO','XDB','IMPORT_SEQ_INFO' ,2 );
END;

COMMIT;
 
QNameIDTable
Returns default qname-id token table dbms_csx_admin.QNameIDTable RETURN VARCHAR2;
SELECT dbms_csx_admin.QNameIDTable FROM dual;
 
RebuildTokenTablesIndexes
Given the table name and the owner, rebuild the indexes of the xml token set tables dbms_csx_admin.rebuildTokenTablesIndexes(
ownername IN VARCHAR2,
tablename IN VARCHAR2);
exec dbms_csx_admin.rebuildTokenTablesIndexes('XDB', 'XDB$ACL');
 
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;
/
 
TBS_TTSET_DML
Returns the dml to be run on the import side for xdb$ttset and xdb$tsetmap dbms_csx_admin.tbs_ttset_dml(
name   IN VARCHAR2,
schema IN VARCHAR2)
RETURN VARCHAR2;
SELECT dbms_csx_admin.tbs_ttset_dml('XDB$TTSET', 'XDB')
FROM dual;

y
Related Topics
Built-in Functions
Built-in Packages
DataPump Executable
DBMS_CSX_INT
DBMS_CSX_INT2
DBMS_DATAPUMP
DBMS_EXTENDED_TTS_CHECKS
DBMS_PLUGTS
DBMS_PLUGTSP
DBMS_TDB
DBMS_TTS
Transportable Tablespaces
What's New In 12cR2
What's New In 18cR3

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved