Oracle DBMS_PLUGTS
Version 19.2.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 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 the EXECUTE_CATALOG_ROLE role and SYSBACKUP.

Selected objects in the package are protected through use of the ACCESSIBLE BY clause.
Source {ORACLE_HOME}/rdbms/admin/dbmsplts.sql
Subprograms
 
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 (new 19c)
Indicates if PLTS tracing is enabled dbms_plugts.getDebugEable RETURN BOOLEAN
ACCESSIBLE BY (PACKAGE sys.dbms_plugtsp);
Can not be executed directly due to the 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;
/
 
MANAGEPLSQLTABLE (new 19c)
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 (new 19c)
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 (new 19c)
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 the ACCESSIBLE BY clause
 
TAB_FUNC (new 19c)
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

Related Topics
Built-in Functions
Built-in Packages
DBMS_EXTENDED_TTS_CHECK
DBMS_PLUGTSP
DBMS_TDB
DBMS_TTS
What's New In 18cR3
What's New In 19cR2

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