Oracle | DBMS_XMLSTORAGE

Oracle DBMS_XMLSTORAGE_MANAGE 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 Undocumented

AUTHID CURRENT_USER

Dependencies

ALL_ALL_TABLES	ALL_XML_TABLES	PLITBLM
ALL_CONSTRAINTS	ALL_XML_TAB_COLS	PRVT_DBMS_MANAGE_XMLSTORAGE
ALL_INDEXES	ATTRCOL$	USER_ALL_TABLES
ALL_IND_COLUMNS	COL$	USER_CONSTRAINTS
ALL_NESTED_TABLES	COLTYPE$	USER_CONS_COLUMNS
ALL_NESTED_TABLE_COLS	DBMS_ASSERT	USER_NESTED_TABLES
ALL_TABLES	DBMS_LOB	USER_NESTED_TABLE_COLS
ALL_TAB_COLS	DBMS_METADATA	USER_REFS
ALL_TAB_COLUMNS	DBMS_OUTPUT	USER_TAB_COLS
ALL_TAB_PARTITIONS	DBMS_STANDARD	USER_TAB_COLUMNS
ALL_USERS	DBMS_XMLDOM	USER_UPDATABLE_COLUMNS
ALL_VIEWS	DBMS_XMLPARSER	USER_XML_TABLES
ALL_XMLTYPE_COLS	DUAL	XDB$ELEMENT
ALL_XML_INDEXES	NTAB$	XDB_INDEX_DDL_CACHE
ALL_XML_NESTED_TABLES	OBJ$	XMLTYPE
ALL_XML_OUT_OF_LINE_TABLES	PDB_INV_TYPE$	XQSEQUENCE
ALL_XML_SCHEMAS

Documented No

First Available 12.1

Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(DEFAULT, AUTO);

Security Model Owned by XDB with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsxutil.sql

Subprograms

DISABLEINDEXESANDCONSTRAINTS	GETSIDXDEFFROMVIEW	REFPARTITIONEXCHANGEOUT
ENABLEINDEXESANDCONSTRAINTS	GETTYPEDDL	RENAMECOLLECTIONTABLE
EXCHANGEPOSTPROC	INDEXXMLREFERENCES	SCOPEXMLREFERENCES
EXCHANGEPREPROC	REFPARTITIONEXCHANGEIN	XPATH2TABCOLMAPPING

DISABLEINDEXESANDCONSTRAINTS

Drops the indexes and disable the constraints for both xmltype table (no P_COL_NAME) and xmltype columns dbms_xmlstorage_manage.disableIndexesAndConstraintS( owner_name IN VARCHAR2 := sys_context('USERENV','CURRENT_USER'), table_name IN VARCHAR2, column_name IN VARCHAR2 DEFAULT NULL, clear IN BOOLEAN DEFAULT FALSE);

TBD

ENABLEINDEXESANDCONSTRAINTS

Rebuilds all indexes and enable the constraints on the P_TABLE_NAME including its child and out of line tables dbms_xmlstorage_manage.enableIndexesAndConstraints( owner_name IN VARCHAR2 := sys_context('USERENV','CURRENT_USER'), table_name IN VARCHAR2, column_name IN VARCHAR2 DEFAULT NULL);

TBD

EXCHANGEPOSTPROC

Enables constraints after exchange partition dbms_xmlstorage_manage.exchangePostProc( owner_name IN VARCHAR2 := sys_context('USERENV','CURRENT_USER'), table_name IN VARCHAR2);

TBD

EXCHANGEPREPROC

Disables constraints before exchange partition dbms_xmlstorage_manage.exchangePreProc( owner_name IN VARCHAR2 := sys_context('USERENV','CURRENT_USER'), table_name IN VARCHAR2);

TBD

GETSIDXDEFFROMVIEW

Undocumented dbms_xmlstorage_manage.getSIDXDefFromView(viewName IN VARCHAR2) RETURN CLOB; PRAGMA SUPPLEMENTAL_LOG_DATA(getSIDXDefFromView, READ_ONLY);

TBD

GETTYPEDDL

Routine to get type creation DDLs for all CDB invalid types after PDB plugin dbms_xmlstorage_manage.getTypeDDL;

exec dbms_xmlstorage_manage.getTypeDDL;

      

      --Please run the following script on your original source 12.1.0.1 CDB

      set serveroutput on

      set define off

      declare stmt clob;

      begin

      dbms_output.put_line('--Please run the following script on your target CDB');

      dbms_output.put_line('define sysuser=&1');

      dbms_output.put_line('alter session set container=cdb$root; ');

      dbms_output.put_line('alter pluggable database CDB$ROOT close; ');

      dbms_output.put_line('alter pluggable database CDB$ROOT open; ');

      dbms_output.put_line('alter session set container=CDB$ROOT ; ');

      dbms_output.put_line('alter session set events ''64478 trace name context forever''; ');

      dbms_output.put_line('alter session set "_oracle_script"=true; ');

      dbms_output.put_line('alter session set events ''64478 level 0''; ');

      dbms_output.put_line('update sys.obj$ set status = 6 where obj# in (select o.obj# from sys.obj$ o, sys.dba_users u, sys.pdb_inv_type$ p where o.type#=13 and o.owner#=u.user_id and

      u.username=p.owner and o.name=p.type_name);');

      dbms_output.put_line('commit;');

      dbms_output.put_line('alter system flush shared_pool;');

      dbms_output.put_line('alter system flush shared_pool;');

      dbms_output.put_line('alter system flush shared_pool;');

      dbms_output.put_line('alter system flush shared_pool;');

      dbms_output.put_line('alter system flush shared_pool;');

      dbms_output.put_line('conn &sysuser as sysdba');

      dbms_output.put_line('alter session set container=CDB$ROOT ; ');

      dbms_output.put_line('@@?/rdbms/admin/utlrp.sql');

      dbms_output.put_line('conn &sysuser as sysdba');

      dbms_output.put_line('alter session set container=CDB$ROOT ; ');

      dbms_output.put_line('@@?/rdbms/admin/utlrp.sql');

      dbms_output.put_line('conn &sysuser as sysdba');

      dbms_output.put_line('alter session set container=CDB$ROOT ; ');

      dbms_output.put_line('@@?/rdbms/admin/utlrp.sql');

      dbms_output.put_line('disconnect');

      end;

      /

      

      PL/SQL procedure successfully completed.

      

      --Please run the following script on your target CDB

      define sysuser=&1

      alter session set container=cdb$root;

      alter pluggable database CDB$ROOT close;

      alter pluggable database CDB$ROOT open;

      alter session set container=CDB$ROOT ;

      alter session set events '64478 trace name context forever';

      alter session set "_oracle_script"=true;

      alter session set events '64478 level 0';

      update sys.obj$ set status = 6 where obj# in (select o.obj# from sys.obj$ o, sys.dba_users u, sys.pdb_inv_type$ p where o.type#=13 and o.owner#=u.user_id and

      u.username=p.owner and

      o.name=p.type_name);

      commit;

      alter system flush shared_pool;

      alter system flush shared_pool;

      alter system flush shared_pool;

      alter system flush shared_pool;

      alter system flush shared_pool;

      conn &sysuser as sysdba

      alter session set container=CDB$ROOT ;

      @@?/rdbms/admin/utlrp.sql

      conn &sysuser as sysdba

      alter session set container=CDB$ROOT ;

      @@?/rdbms/admin/utlrp.sql

      conn &sysuser as sysdba

      alter session set container=CDB$ROOT ;

      @@?/rdbms/admin/utlrp.sql

      disconnect

      

      PL/SQL procedure successfully completed.

INDEXXMLREFERENCES

Creates unique indexes on the ref columns of the given XML type tables or XML type column of a given table dbms_xmlstorage_manage.indexXMLReferences( owner_name IN VARCHAR2 := sys_context('USERENV','CURRENT_USER'), table_name IN VARCHAR2, column_name IN VARCHAR2 DEFAULT NULL, index_name IN VARCHAR2);

TBD

REFPARTITIONEXCHANGEIN

Exchange reference partition in dbms_xmlstorage_manage.RefPartitionExchangeIn( owner_name IN VARCHAR2 := sys_context('USERENV','CURRENT_USER'), parent_table_name IN VARCHAR2, child_table_name IN VARCHAR2, parent_exchange_table_name IN VARCHAR2, child_exchange_table_name IN VARCHAR2, parent_exchange_stmt IN CLOB, child_exchange_stmt IN CLOB);

TBD

REFPARTITIONEXCHANGEOUT

Exchange reference partition out dbms_xmlstorage_manage.refPartitionExchangeOut( owner_name IN VARCHAR2 := sys_context('USERENV','CURRENT_USER'), parent_table_name IN VARCHAR2, child_table_name IN VARCHAR2, parent_exchange_table_name IN VARCHAR2, child_exchange_table_name IN VARCHAR2, parent_exchange_stmt IN CLOB, child_exchange_stmt IN CLOB);

TBD

RENAMECOLLECTIONTABLE

Renames a collection table from the system generated name to the given table name dbms_xmlstorage_manage.renameCollectionTable( owner_name IN VARCHAR2 := sys_context('USERENV','CURRENT_USER'), tab_name IN VARCHAR2, col_name IN VARCHAR2 DEFAULT NULL, xpath IN VARCHAR2, collection_table_name IN VARCHAR2, namespaces IN VARCHAR2 DEFAULT NULL);

TBD

SCOPEXMLREFERENCES

Will scope all XML references. Scoped REF types require less storage space and allow more efficient access than unscoped REF types dbms_xmlstorage_manage.scopeXMLReferences;

exec xdb.dbms_xmlstorage_manage.scopeXMLReferences;

      

      PL/SQL procedure successfully completed.

XPATH2TABCOLMAPPING

Undocumented dbms_xmlstorage_manage.xpath2TabColMapping( owner_name IN VARCHAR2 := sys_context('USERENV','CURRENT_USER'), table_name IN VARCHAR2, column_name IN VARCHAR2 DEFAULT NULL, xpath IN VARCHAR2, namespaces IN VARCHAR2 DEFAULT NULL) RETURN XMLTYPE; RAGMA SUPPLEMENTAL_LOG_DATA(xpath2TabColMapping, READ_ONLY);

TBD