Oracle | DBMS

Oracle DBMS_PLUGTS 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 Internal support package for Transportable Tablespaces.

AUTHID DEFINER

Constants

Name	Data Type	Value
General
TS_EXP_BEGIN	BINARY_INTEGER	1
TS_EXP_END	BINARY_INTEGER	2
Function Constants
C_ADD	NUMBER	1
C_TRUNCATE	NUMBER	2
C_USER	NUMBER	1
C_TABLESPACE	NUMBER	2

Dependencies

CLU$	DUAL	SEQ$
COL$	FILE$	TAB$
DBMS_ASSERT	IDGEN1$	TABPART$
DBMS_EXTENDED_TTS_CHECKS	IND$	TABSUBPART$
DBMS_METADATA	INDPART$	TS$
DBMS_PITR	KUPCC	TTS_INFO_T
DBMS_PLUGTSP	KUPF$FILE	TTS_INFO_TAB
DBMS_PLUGTS_LIB	KUPP$PROC	USER$
DBMS_SPACE_ADMIN	KUPW$WORKER	V$DATABASE
DBMS_STREAMS_TABLESPACE_ADM	LOB$	V$DATAFILE
DBMS_SYS_ERROR	OBJ$	V$PARAMETER
DBMS_TDB	PLITBLM	X$KCVFH
DBMS_TTS	SEG$

Documented No

Exceptions

Error Code	Reason
ORA-29304	Tablespace not found
ORA-29335	Tablespace not readonly
ORA-29336	Internal error
ORA-29338	Datafile not ready
ORA-29339	Blocksize mistmatch
ORA-29340	Export file corrupted
ORA-29341	Not self contained
ORA-29342	User not found
ORA-29343	Mapped user not found
ORA-29344	User not in list
ORA-29345	Different character set
ORA-29346	Invalid tablespace list
ORA-29347	Tablespace not in list
ORA-29348	Datafiles missing
ORA-29349	Tablespace name conflict
ORA-29351	System or Temporary tablespace
ORA-29353	Tablespace list overflow
ORA-39185	Tablespace failure list
ORA-39186	Tablespace list empty
ORA-39187	Not self-contained list

First Available 7.3.4

Security Model Owned by SYS with granted to GSMADMIN_INTERNAL and SYSBACKUP and the EXECUTE_CATALOG_ROLE role

Direct access to some objects in this package is prevented by means of an Accessible By clause.

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

Subprograms

BEGINEXPORT	GETDEBUGENABLE	NEWDATAFILE
BEGINEXPTABLESPACE	GETLINE	NEWTABLESPACE
BEGINIMPORT	GET_DB_CHAR_PROPERTIES	PLTS_NEWDATAFILE
BEGINIMPTABLESPACE	INIT	PLUGGABLEUSER
CHECKCOMPTYPE	KCP_CHECK_TTS_CHAR_SET_COMPAT	RECLAIMTEMPSEGMENT
CHECKDATAFILE	KCP_CHKCHAR	SELECTBLOCK
CHECKPLUGGABLE	KCP_GETCOMP	SENDTRACEMSG
CHECKUSER	KCP_SETPIRENA	SETDEBUG
COMMITPLUGGABLE	MANAGEPLSQLTABLE	SETOPTION
ENDIMPORT	MAPTS	TAB_FUNC
ENDIMPTABLESPACE	MAPUSER

BEGINEXPORT

Constructs the call in an anonymous PL/SQL block dbms_plugts.beginexport;

exec dbms_plugts.beginexport;

BEGINEXPTABLESPACE

Verifies tablespaces are read-only during export. It is called for each tablespace specified dbms_plugts.beginexptablespace(tsname IN VARCHAR2);

exec dbms_plugts.beginexptablespace('EXAMPLE');

      

      ALTER TABLESPACE example READ ONLY;

      

      exec dbms_plugts.beginexptablespace('EXAMPLE');

      

      ALTER TABLESPACE example READ WRITE;

BEGINIMPORT

Constructs the beginImport call in an anonymous PL/SQL block dbms_plugts.beginimport;

exec dbms_plugts.beginimport;

BEGINIMPTABLESPACE

Sets the target tablespaces and it's owner. Checks to make sure the tablespace name does not conflict with any existing tablespaces already in the database. Verifies the block size is the same as that in the target database. If this succeeds, begins importing metadata for the tablespace. This procedure call appears in the export file. dbms_plugts.beginimptablespace( tsname IN VARCHAR2, -- tablespace name tsID IN NUMBER, -- tablespace ID in original database owner IN VARCHAR2, -- owner of tablespace n_files IN BINARY_INTEGER, -- number of datafiles in the tablespace contents IN BINARY_INTEGER, -- contents column of ts$ (TEMP/PERMANENT) blkSize IN BINARY_INTEGER, -- size of block in bytes inc_num IN BINARY_INTEGER, -- incarnation number of extent clean_SCN IN NUMBER, -- tablespace clean SCN dflminext IN NUMBER, -- default minimum number of extents dflmaxext IN NUMBER, -- default maximum number of extents dflinit IN NUMBER, -- default initial extent size dflincr IN NUMBER, -- default initial extent size dflminlen IN NUMBER, -- default minimum extent size dflextpct IN BINARY_INTEGER, -- default percent extent size increase dflogging IN BINARY_INTEGER, -- default logging attribute affstrength IN NUMBER, -- affinity strength bitmapped IN NUMBER, -- if bitmapped dbID IN NUMBER, -- database ID directallowed IN NUMBER, -- allowed flags IN BINARY_INTEGER, -- flags creation_SCN IN NUMBER, -- tablespace creation SCN groupname IN VARCHAR2, -- group name spare1 IN NUMBER, -- spare1 in ts$ spare2 IN NUMBER, -- spare2 in ts$ spare3 IN VARCHAR2, -- spare3 in ts$ spare4 IN DATE, -- spare4 in ts$ seg_fno IN NUMBER DEFAULT 0, -- file# for space_hdr in seg$ seg_bno IN NUMBER DEFAULT 0, -- block# for space_hdr in seg$ seg_blks IN NUMBER DEFAULT 0); -- blocks, size of space_hdr in seg$

TBD

CHECKCOMPTYPE

Checks and adjusts the version for each compatibility type. This procedure is in the export file dbms_plugts.checkcomptype( compID IN VARCHAR2, -- compatibility type name compRL IN VARCHAR2); -- release level

TBD

CHECKDATAFILE

Calls statically linked C routines to associate the datafile with the tablespace and validates file headers. This procedure appears in the export file. The parameter list includes all columns in file$, except those that will be discarded (status$, ownerinstance). dbms_plugts.checkdatafile( name IN VARCHAR2, -- file name (excluding path) databaseID IN NUMBER, -- database ID absolute_fno IN BINARY_INTEGER, -- absolute file number curFileBlks IN NUMBER, -- size of file in blocks tablespace_ID IN NUMBER, -- tablespace ID in original database relative_fno IN BINARY_INTEGER, -- relative file number maxextend IN NUMBER, -- maximum file size inc IN NUMBER, -- increment amount creation_SCN IN NUMBER, -- file creation SCN checkpoint_SCN IN NUMBER, -- file checkpoint SCN reset_SCN IN NUMBER, -- file reset SCN spare1 IN NUMBER, -- spare1 in file$ spare2 IN NUMBER, -- spare2 in file$ spare3 IN VARCHAR2, -- spare4 in file$ spare4 IN DATE); -- spare4 in file$

TBD

CHECKPLUGGABLE

Verifies that objects are self-contained in the tablespaces specified

The transport set must first be defined dbms_plugts.checkpluggable( incl_constraints IN NUMBER, incl_triggers IN NUMBER, incl_grants IN NUMBER, full_check IN NUMBER, do_check IN NUMBER DEFAULT 1, job_type IN VARCHAR2 DEFAULT NULL, encryption_password IN BOOLEAN DEFAULT FALSE);

exec dbms_plugts.checkpluggable(1, 1, 1, 1, 1);

CHECKUSER

Checks to see that the user name in the pluggable set matches that entered by the DBA via the import USERS command line option. Makes sure that, after the user mappings, the required user is already in the database. This procedure call appears in the export file. dbms_plugts.checkuser(username IN VARCHAR2);

-- test with one real and one fake username

      SELECT username

      FROM dba_users

      ORDER BY1;

      

      exec dbms_plugts.checkuser('UWCLASS');

      

      exec dbms_plugts.checkuser('ZZYZX');

COMMITPLUGGABLE

Calls a statically linked C routine to atomically plug-in the pluggable set. This procedure call appears in the export file dbms_plugts.commitpluggable;

exec dbms_plugts.commitpluggable;

ENDIMPORT

Performs final cleanup to end the import job dbms_plugts.endimport;

exec dbms_plugts.endimport;

ENDIMPTABLESPACE

Wraps up the tablespace check. This procedure call appears in the export file dbms_plugts.endImpTablespace;

exec dbms_plugts.endImpTablespace;

GETDEBUGENABLE

Indicates if PLTS tracing is enabled dbms_plugts.getDebugEable RETURN BOOLEAN ACCESSIBLE BY (PACKAGE sys.dbms_plugtsp);

Can not be executed directly due to an ACCESSIBLE BY clause

GETLINE

Returns a string to be appended to the export file dbms_plugts.getline RETURN VARCHAR2;

SELECT dbms_plugts.getline FROM dual;

GET_DB_CHAR_PROPERTIES

Retrieves the tablespace database character set properties from sys.tts_tbs$ dbms_plugts.get_db_char_properties( has_clobs OUT BINARY_INTEGER, has_nchars OUT BINARY_INTEGER, char_semantics OUT BINARY_INTEGER);

set serveroutput on

      

      DECLARE

       has_clobs  PLS_INTEGER;

       has_nchars PLS_INTEGER;

       char_seman PLS_INTEGER;

      BEGIN

        dbms_plugts.get_db_char_properties(has_clobs, has_nchars, char_seman);

        dbms_output.put_line(TO_CHAR(has_clobs));

        dbms_output.put_line(TO_CHAR(has_nchars));

        dbms_output.put_line(TO_CHAR(char_seman));

      END;

      /

INIT

Initialize interface for testing dbms_plugts.init;

exec dbms_plugts.init;

KCP_CHECK_TTS_CHAR_SET_COMPAT

Determines whether a plug into a specified database char and nchar set is compatible with the current database dbms_plugts.kcp_check_tts_char_set_compat( has_clobs IN BINARY_INTEGER, has_nchars IN BINARY_INTEGER, char_semantics_on IN BINARY_INTEGER, target_charset_name IN VARCHAR2, target_ncharset_name IN VARCHAR2);

exec dbms_plugts.kcp_check_tts_char_set_compat(1, 1, 1, 'WE8MSWIN1252', 'AL16UTF16');

      

      exec dbms_plugts.kcp_check_tts_char_set_compat(1, 1, 1, 'ZZYZX', 'AL16UTF16');

KCP_CHKCHAR

Checks if char, nchar set match. Raises an exception if they do not dbms_plugts.kcp_chkchar( cid IN BINARY_INTEGER, -- char ID ncid IN BINARY_INTEGER, -- nchar ID chknc IN BINARY_INTEGER, -- check nchar (1 or 0) has_clobs IN BINARY_INTEGER, has_nchars IN BINARY_INTEGER, char_semantics_on IN BINARY_INTEGER);

conn / as sysdba

      

      DECLARE

       pCID  VARCHAR2(60);

       pNCID NVARCHAR2(60);

      BEGIN

        dbms_plugts.kcp_getchar(pCID, pNCID);

      --  pCID := pCID+100;

        dbms_plugts.kcp_chkchar(pCID, pNCID, 1, 1, 1, 1);

      END;

      /

      

      DECLARE

       pCID  VARCHAR2(60);

       pNCID NVARCHAR2(60);

      BEGIN

        dbms_plugts.kcp_getchar(pCID, pNCID);

        pCID := pCID+100;

        dbms_plugts.kcp_chkchar(pCID, pNCID, 1, 1, 1, 1);

      END;

      /

KCP_GETCOMP

Retrieve the current compatible setting dbms_plugts.kcp_getcomp(szcomp OUT VARCHAR2); -- compatible setting

SELECT value

      FROM v$parameter

      WHERE name = 'compatible';

      

      set serveroutput on

      

      DECLARE

       retval VARCHAR2(60);

      BEGIN

        dbms_plugts.kcp_getcomp(retval);

        dbms_output.put_line(retval);

      END;

      /

KCP_SETPIRENA (new 21c)

Set worker session UGAPIRENA dbms_plugts.setPirena; ACCESSIBLE BY (PACKAGE sys.kupw$worker);

Can not be executed directly due to an ACCESSIBLE BY clause

MANAGEPLSQLTABLE

PL/SQL-Table management dbms_plugts.managePLSQLtable( function IN NUMBER, plsqlTable IN NUMBER, name IN VARCHAR2 DEFAULT NULL, tsnum IN NUMBER DEFAULT NULL);

TBD

MAPTS

Remaps an exported tablespace name to its new name following import dbms_plugts.mapts( from_ts IN VARCHAR2, to_ts IN VARCHAR2);

conn sys@pdbdev as sysdba

      

      exec dbms_plugts.mapts('UCDATA', 'UWDATA');

MAPUSER

Positionally maps transformation of the from_user schema name to the to_user schema name dbms_plugts.mapuser( from_user IN VARCHAR2, to_user IN VARCHAR2);

conn sys@pdbdev as sysdba

      

      exec dbms_plugts.mapuser('ABC', 'XYZ');

      

      exec dbms_plugts.mapuser('ABC,DEF', 'UVW,XYZ');

NEWDATAFILE

Informs the dbms_plugts package of the location of the new datafiles. If the file can not be found, an error will be signaled: possibly at a later point dbms_plugts.newdatafile(filename IN VARCHAR2);

exec dbms_plugts.newdatafile('/app/oracle/product/oradata/orabase/uwdata03.dbf');

NEWTABLESPACE

Iinforms the dbms_plugts package about tablespace name to be included in the job dbms_plugts.newtablespace(tsname IN VARCHAR2);

conn sys@pdbdev as sysdba

      

      exec dbms_plugts.newtablespace('UWDATA');

PLUGGABLEUSER

Adds a user to the import job dbms_plugts.pluggableuser(usrname IN VARCHAR2);

conn sys@pdbdev as sysdba

      

      exec dbms_plugts.pluggableuser('UWCLASS');

PLTS_NEWDATAFILE

Informs the dbms_Plugts package about the location of a new datafile. This is a whitelist version of newDatafile. Only accessible from the sys.kupw$worker package. dbms_plugts.plts_newDatafile( filename IN VARCHAR2) ACCESSIBLE BY (PACKAGE sys.kupw$worker, PACKAGE gsmadmin_internal.exchange);

Can not be executed directly due to the ACCESSIBLE BY clause

RECLAIMTEMPSEGMENT

Reclaims a segment by calling the statically linked C routine kcp_plg_reclaim_segment. This procedure call appears in the export file. dbms_plugts.reclaimtempsegment( file_no IN BINARY_INTEGER, block_no IN BINARY_INTEGER, type_no IN BINARY_INTEGER, ts_no IN BINARY_INTEGER, blocks IN BINARY_INTEGER, extents IN BINARY_INTEGER, iniexts IN BINARY_INTEGER, minexts IN BINARY_INTEGER, maxexts IN BINARY_INTEGER, extsize IN BINARY_INTEGER, extpct IN BINARY_INTEGER, user_no IN BINARY_INTEGER, lists IN BINARY_INTEGER, groups IN BINARY_INTEGER, bitmapranges IN NUMBER, cachehint IN BINARY_INTEGER, scanhint IN BINARY_INTEGER, hwmincr IN BINARY_INTEGER, spare1 IN BINARY_INTEGER, spare2 IN BINARY_INTEGER);

TBD

SELECTBLOCK

Selects an anonymous block for retrieval. blockID is the ID to pick an anonymous block. Use ts_exp_begin at the beginning of of export and ts_exp_end at the end. dbms_plugts.selectBlock(blockID IN BINARY_INTEGER);

TBD

SENDTRACEMSG

send a message to the trace file using KUPF$FILE.TRACE dbms_plugts.sendtracemsg(msg IN VARCHAR2);

exec dbms_plugts.sendtracemsg('Test Msg');

SETDEBUG

Initializes global variables used for debugging trace messages. Trace/debug flags from /TRACE param or trace/debug event, possibly including global trace/debug flags. dbms_plugts.setdebug(debug_flags IN BINARY_INTEGER);

exec dbms_plugts.setdebug(1);

SETOPTION

Initialize global variables to communicate options settings from the Data Pump Worker to the PLTS code. Only a DataPump worker is allowed to call this routine. dbms_plugts.setOption( option_name IN VARCHAR2 DEFAULT NULL, option_value IN VARCHAR2 DEFAULT NULL) ACCESSIBLE BY (PACKAGE sys.kupw$worker);

Can not be executed directly due to an ACCESSIBLE BY clause

TAB_FUNC

Queries the PL/SQL table and pipelines it to look like a SQL table dbms_plugts.tab_func RETURN sys.tts_info_tab_t pipelined;

TBD