Oracle | XDB_MIGRATESCHEMA

Oracle XDB_MIGRATESCHEMA Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

Purpose This package defines structures to migrate an XML schema from a user 'a' to a user 'b' without moving the data. For now this is an undocumented package that was requested to enable editioning.

This package is not part of the default DBCA installation.

AUTHID DEFINER

Dependencies

None: Not even STANDARD

Documented No

First Available 19c

Security Model Owned by SYS with no privileges granted

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

@?/rdbms/admin/dbmsxdbschmig.sql

      

      Session altered.

      

      SQL> SET FEEDBACK 1

      SQL> SET NUMWIDTH 10

      SQL> SET LINESIZE 80

      SQL> SET TRIMSPOOL ON

      SQL> SET TAB OFF

      SQL> SET PAGESIZE 100

      SQL>

      SQL> CREATE OR REPLACE PACKAGE sys.xdb_migrateschema IS

      2

      3 -- Procedures to move an xml schema from user A to user B

      4 -- see impl for comments

      5 PROCEDURE moveSchemas;

      6 PROCEDURE cleanup;

      7 end xdb_migrateschema;

      8 /

      

      SP2-0808: Package created with compilation warnings

      

      SQL> show errors;

      Errors for PACKAGE SYS.XDB_MIGRATESCHEMA:

      

      LINE/COL ERROR

      -------- -----------------------------------------------------------------

      1/1 PLW-05018: unit XDB_MIGRATESCHEMA omitted optional AUTHID clause;

      default value DEFINER used

      

      SQL> BEGIN

      2 execute immediate ('DROP TABLE xdb$moveSchemaTab');

      3 EXCEPTION

      4 WHEN OTHERS THEN NULL;

      5 END;

      6 /

      

      PL/SQL procedure successfully completed.

      

      SQL> BEGIN

      2 execute immediate ('CREATE TABLE xdb$moveSchemaTab (schema_url VARCHAR2(4000),

      3 schemaOwnerFrom VARCHAR2(128),

      4 schemaOwnerTo VARCHAR2(128),

      5 schema CLOB,

      6 CONSTRAINT xdb$moveSchemaTabC1 UNIQUE (schema_url, schemaOwnerFrom))

      7 ');

      8 EXCEPTION

      9 WHEN OTHERS THEN NULL;

      10 END;

      11 /

      

      PL/SQL procedure successfully completed.

      

      SQL> show errors;

      Errors for PACKAGE SYS.XDB_MIGRATESCHEMA:

      

      LINE/COL ERROR

      -------- -----------------------------------------------------------------

      1/1 PLW-05018: unit XDB_MIGRATESCHEMA omitted optional AUTHID clause;

      default value DEFINER used

      

      SQL> @?/rdbms/admin/sqlsessend.sql

      SQL> Rem $Header: rdbms/admin/sqlsessend.sql /main/5 2020/07/20 02:36:19 dgoddard Exp $

      SQL> Rem Copyright (c) 2013, 2019, Oracle and/or its affiliates.

      SQL> Rem All rights reserved.

      SQL> alter session set "_ORACLE_SCRIPT" = false;

      

      Session altered.

      

      SQL> sho err

      Errors for PACKAGE SYS.XDB_MIGRATESCHEMA:

      

      LINE/COL ERROR

      -------- -----------------------------------------------------------------

      1/1 PLW-05018: unit XDB_MIGRATESCHEMA omitted optional AUTHID clause;

      default value DEFINER used

Subprograms

CLEANUP

MOVESCHEMAS

CLEANUP

Undocumented xdb_migrateschema.cleanup;

exec xdb_migrateschema.cleanup;

MOVESCHEMAS

Undocumented xdb_migrateschema.moveSchemas;

exec xdb_migrateschema.moveSchemas;