Oracle | DBMS_CSX

Oracle DBMS_CSX_ADMIN Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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_STANDARD	KU$_PARAMVALUE1010
DBMS_CSX_INT	DBMS_STATS	KU$_PARMAMVALUES1010
DBMS_DATAPUMP	DBMS_XDBUTIL_INT	KU$_STATUS
DBMS_PRIV_CAPTURE	KU$_JOBDESC1220	PLITBLM

Documented Yes: Packages and Types Reference

First Available 11.1

Security Model Owned by XDB with EXECUTE granted to the DBA role

Source $ORACLE_HOME/rdbms/admin/dbmsxdba.sql

Subprograms

CopyDefaultTokenTableSet	GetTokenTableNames	Post_Import_DDL_DML
GatherTokenTableStats	Instance_Info_Exp	QNameIDTable
GetSequenceInfo	MoveTokenTables	RebuildTokenTablesIndexes
GetTokenTableInfo	NamespaceIDTable	RegisterTokenTableSet
GetTokenTableInfoByTablespace	PathIDTable	TBS_TTSET_DML

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;