Oracle DBMS_PLUGTS
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Internal support package for Transportable Tablespaces.
AUTHID DEFINER
Constants
Name Data Type Value
TS_EXP_BEGIN BINARY_INTEGER 1
TS_EXP_END BINARY_INTEGER 2
Data Types  
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
Source {ORACLE_HOME}/rdbms/admin/dbmsplts.sql
Subprograms
BEGINEXPORT INIT KCP_PLG_RECLAIM_SEGMENT
BEGINEXPTABLESPACE KCP_ACOMP KCP_RDFH
BEGINIMPORT KCP_BEXP MAPTS
BEGINIMPTABLESPACE KCP_CHECK_TTS_CHAR_SET_COMPAT MAPUSER
CHECKCOMPTYPE KCP_CHKCHAR NEWDATAFILE
CHECKDATAFILE KCP_CHKXPLATFORM NEWTABLESPACE
CHECKPLUGGABLE KCP_CMT PLUGGABLEUSER
CHECKUSER KCP_GETCHAR RECLAIMTEMPSEGMENT
COMMITPLUGGABLE KCP_GETCOMP SB4_TO_UB4
ENDIMPORT KCP_GETFH SELECTBLOCK
ENDIMPTABLESPACE KCP_INIT SENDTRACEMSG
GETLINE KCP_NEWTS SETDEBUG
GET_DB_CHAR_PROPERTIES KCP_PLGDF  
 
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;
 
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_ACOMP
Adjusts the compatibility level dbms_plugts.kcp_acomp(
compID IN VARCHAR2,  -- compatibility type
compRL IN VARCHAR2); -- release level
TBD
 
KCP_BEXP
Begin export dbms_plugts.kcp_bexp(
vsn       OUT VARCHAR2,        -- Oracle server version
dobj_half OUT BINARY_INTEGER,  -- half of data obj#
dobj_odd  OUT BINARY_INTEGER); -- lowest bit of data obj#
TBD
 
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_CHKXPLATFORM
Verification checks needed for cross platform transport dbms_plugts.kcp_chkxplatform(
srcplatformID   IN BINARY_INTEGER,
srcplatformName IN VARCHAR2,
tgtplatformID   IN BINARY_INTEGER,
tgtplatformName IN VARCHAR2,
src_rls_version IN VARCHAR2);
col platform_name format a40

SELECT *
FROM gv$db_transportable_platform;

exec dbms_plugts.kcp_chkxplatform(7, 'Microsoft Windows IA (64-bit)', 21, 'Linux IA (32-bit)','12.1.0.1.0');
 
KCP_CMT
Commit pluggable dbms_plugts.kcp_cmt(data_objn IN BINARY_INTEGER);  -- data object number
exec dbms_plugts.kcp_cmt(84736);
 
KCP_GETCHAR
Retrieve char and nchar ID and name dbms_plugts.kcp_getchar(
cid  OUT BINARY_INTEGER,  -- char ID
ncid OUT BINARY_INTEGER); -- nchar ID
set serveroutput on

DECLARE
 pCID  VARCHAR2(60);
 pNCID NVARCHAR2(60);
BEGIN
  dbms_plugts.kcp_getchar(pCID, pNCID);
  dbms_output.put_line(pCID);
  dbms_output.put_line(pNCID);
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_GETFH
Retrieve file header information by file number dbms_plugts.kcp_getfh(
afn       IN  BINARY_INTEGER,  -- absolute file number
dbID      OUT BINARY_INTEGER,  -- database ID
ckpt_SCN  OUT VARCHAR2,        -- checkpoint SCN
reset_SCN OUT VARCHAR2,        -- reset log SCN
hdr_afn   OUT BINARY_INTEGER); -- file# from header
TBD
 
KCP_INIT
Initializes kernel data structures for transportable tablespaces dbms_plugts.kcp_init;
exec dbms_plugts.kcp_init;
 
KCP_NEWTS
New tablespace dbms_plugts.kcp_newts(
tsname    IN VARCHAR2,        -- tablespace name
tsid      IN BINARY_INTEGER,  -- ts ID
n_files   IN BINARY_INTEGER,  -- # of datafiles in ts
blksz     IN BINARY_INTEGER,  -- block size
inc_num   IN BINARY_INTEGER,  -- inc #
cleanSCN  IN NUMBER,          -- cleanSCN
dflminext IN BINARY_INTEGER,  -- dflminext in ts$
dflmaxext IN BINARY_INTEGER,  -- dflmaxext in ts$
dflinit   IN BINARY_INTEGER,  -- dflinit in ts$
dflincr   IN BINARY_INTEGER,  -- dflincr in ts$
dflminlen IN BINARY_INTEGER,  -- dflminlen in ts$
dflextpct IN BINARY_INTEGER,  -- dflextpct in ts$
dflogging IN BINARY_INTEGER,  -- dflogging in ts$
bitmapped IN BINARY_INTEGER,  -- bitmapped in ts$
dbID      IN BINARY_INTEGER,  -- db ID
crtSCN    IN NUMBER,          -- creation SCN
contents  IN BINARY_INTEGER,  -- contents$ in ts$
flags     IN BINARY_INTEGER,  -- flags in ts$
seg_fno   IN BINARY_INTEGER,  -- file# in seg$
seg_bno   IN BINARY_INTEGER,  -- block# in seg$
seg_blks  IN BINARY_INTEGER); -- blocks in seg$
TBD
 
KCP_PLGDF
Plug in a datafile dbms_plugts.kcp_plgdf(
dbID      IN BINARY_INTEGER,  -- database ID
afn       IN BINARY_INTEGER,  -- absolute file #
fileBlks  IN BINARY_INTEGER,  -- size of file in blocks
tsID      IN BINARY_INTEGER,  -- tablespace ID
rfn       IN BINARY_INTEGER,  -- relative file #
maxextend IN BINARY_INTEGER,
inc       IN BINARY_INTEGER,
crtSCN    IN NUMBER,          -- creation SCN
cptSCN    IN NUMBER,          -- checkpoint SCN
rstSCN    IN NUMBER,          -- reset SCN
spare1    IN BINARY_INTEGER); -- spare1 in file$
TBD
 
KCP_PLG_RECLAIM_SEGMENT
Fixes seg$ to reclaim a temp segment dbms_plugts.kcp_plg_reclaim_segment(
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,
extpct       IN BINARY_INTEGER,
user_no      IN BINARY_INTEGER,
lists        IN BINARY_INTEGER,
groups       IN BINARY_INTEGER,
bitmapranges IN BINARY_INTEGER,
cachehint    IN BINARY_INTEGER,
scanhint     IN BINARY_INTEGER,
hwmincr      IN BINARY_INTEGER,
spare1       IN BINARY_INTEGER,
spare2       IN BINARY_INTEGER);
TBD
 
KCP_RDFH
Read export file's file header dbms_plugts.kcp_rdfh(fname IN VARCHAR2);
exec dbms_plugts.kcp_rdfh('/stage/tts.dmp');
 
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');
 
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
 
SB4_TO_UB4
Converts sb4 to ub4

Note: sb4 and ub4 are external data types both supported by PLS_INTEGER
dbms_plugts.sb4_to_ub4(b IN BINARY_INTEGER) RETURN NUMBER;
SELECT dbms_plugts.sb4_to_ub4(42) FROM dual;
 
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);

Related Topics
DBMS_EXTENDED_TTS_CHECK
DBMS_PLUGTSP
DBMS_TDB
DBMS_TTS
Packages

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-2014 Daniel A. Morgan All Rights Reserved