| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsplts.sql |
| First Available |
7.3.4 |
| Constants |
| Name |
Data Type |
Value |
| TS_EXP_BEGIN |
BINARY_INTEGER |
1 |
| TS_EXP_END |
BINARY_INTEGER |
2 |
|
| Dependencies |
| CLU$ |
FILE$ |
TAB$ |
| COL$ |
IDGEN1$ |
TABPART$ |
| DBMS_EXTENDED_TTS_CHECKS |
IND$ |
TABSUBPART$ |
| DBMS_METADATA |
INDPART$ |
TS$ |
| DBMS_PITR |
KUPCC |
TTS_TBS$ |
| DBMS_PLUGTSP |
KUPF$FILE |
TTS_USR$ |
| DBMS_PLUGTS_LIB |
KUPP$PROC |
USER$ |
| DBMS_STREAMS_TABLESPACE_ADM |
KUPW$WORKER |
V$DATABASE |
| DBMS_SYS_ERROR |
LOB$ |
V$DATAFILE |
| DBMS_TDB |
OBJ$ |
V$PARAMETER |
| DBMS_TTS |
PLITBLM |
X$KCVFH |
| DUAL |
SEG$ |
|
|
| 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 |
|
| Security Model |
Execute is granted to the EXECUTE_CATALOG_ROLE |
| |
| 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 (new parameters 11.2.0.3)  |
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_ALDFTS |
| Allocate datafile and tablespace structures |
dbms_plugts.kcp_adfts(
n_dfiles IN BINARY_INTEGER, -- number of datafiles
n_ts IN BINARY_INTEGER); -- number of tablespaces |
SELECT file_name
FROM dba_data_files;
SELECT tablespace_name
FROM dba_tablespaces;
-- do not count system, sysaux, temp and undo
exec dbms_plugts.kcp_adfts(23,9); |
| |
| 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 (32-bit)', 10, 'Linux IA (32-bit)','11.1.0.0.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); |
| 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); |
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); |
| exec dbms_plugts.newtablespace('UWDATA'); |
| |
| PLUGGABLEUSER |
| Adds a user to the import job |
dbms_plugts.pluggableuser(usrname IN VARCHAR2); |
| 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); |
