Oracle DBMS_TTS
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. 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.
Purpose Determine whether violations exist that would prevent transporting a tablespace or tablespace set
AUTHID DEFINER
Constants
Name Data Type Value
ENCRYPTED PLS_INTEGER 2
OBFUSCATED PLS_INTEGER 1
v_exclude_xmlverify BOOLEAN FALSE;
Data Types -- also used by dbms_extended_tts_checks
TYPE tablespace_names IS TABLE OF VARCHAR(30)
INDEX BY BINARY_INTEGER;
Dependencies
DBMS_EXTENDED_TTS_CHECKS DUAL STRADDLING_TS_OBJECTS
DBMS_OUTPUT KUPCC TS$
DBMS_PLUGTS KUPP$PROC TTS_ERROR$
DBMS_STREAMS_TABLESPACE_ADM KUPU$UTILITIES_INT TTS_TBS$
DBMS_SYS_ERROR KUPW$WORKER UTL_RAW
DBMS_TTS_LIB PLITBLM  
Documented Only 2 of the contained objects are supported: DOWNGRADE and TRANSPORT_SET_CHECK
Exceptions
Error Code Reason
ORA-29304 Tablespace Not Found
ORA-29335 Tablespace Not Read Only
ORA-29336 Internal DBMS_TTS Error
ORA-29338 Datafile Not Ready
ORA-29339 Blocksizes Do Not Match
ORA-29340  Export File Corrupted
ORA-29341 Tablespace Is Not Self-Contained
ORA-29342 User Not Found
ORA-29343 Mapped User Not Found
ORA-29344 User Not Listed
ORA-29345 Character Set Mismatch
ORA-29346 Invalid Tablespace List
ORA-29347 Tablespace Not Listed
ORA-29348 Missing Datafile
ORA-29349 Tablespace Name Conflict
ORA-29351 System or Temp Tablespace
ORA-29353 Tablespace List Overflow
First Available 8.1.5
Security Model Owned by SYS EXECUTE is granted to SYSBACKUP and the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmsplts.sql
Subprograms
 
ADD_PROTECTED_CE_TAB_KEY (new 12.1)
This trusted callout provides an interface to add the column encryption table key to the TDE dictionary table. The table key is unwrapped with the passphrase setup in a previous call to dbms_tts.set_passphrase, re-wrapped with the Master Key and added to enc$. dbms_tts.add_protected_ce_tab_key(
schemaName   IN VARCHAR2, -- schema name
tableName    IN VARCHAR2, -- table name
protTableKey IN RAW);     -- protected Table Key
TBD
 
CHECKTABLESPACE
Checks if a tablespace is temporary or if it is a tablespace that can not be exported using transportable tablespace mode dbms_tts.checkTablespace(
a_tsname IN     VARCHAR2,
a_ts_num IN OUT NUMBER,
upcase   IN     BOOLEAN DEFAULT FALSE);
SELECT ts#, name
FROM ts$;

set serveroutput on

DECLARE
 tsnum NUMBER;
BEGIN
  dbms_tts.checkTablespace('UWDATA', tsnum);
  dbms_output.put_line(tsnum);
END;
/

DECLARE
 tsnum NUMBER;
BEGIN
  dbms_tts.checkTablespace('TEMP', tsnum);
  dbms_output.put_line(tsnum);
END;
/
 
DH_KEY_EXCHANGE (new 12.1)
Performs a Diffie-Hellman key exchange. Data Pump worker process on target system executes this procedure over a network link on the remote source system. Once this key exchange has taken place, both sides can transmit sensitive data securely. dbms_tts.dh_key_exchange(
source_key IN  VARCHAR2,
target_key OUT VARCHAR2,
digest     OUT VARCHAR2);
TBD
 
DOWNGRADE
Downgrades transportable tablespace related data dbms_tts.downgrade;
exec dbms_tts.downgrade;
 
GET_AFN_DBID (new 12.1)
At least in theory this new, undocumented, procedure is a trusted callout gets the absolute file number and the database id for a given file. It doesn't raise an exception ... but then it doesn't return anything either: Even accepting negative numbers as input which by definition is invalid. dbms_tts.get_afn_dbid(
fn   IN  VARCHAR2, -- filename
afn  OUT NUMBER,   -- absolute file number
dbid OUT NUMBER);  -- database id
SELECT file_id, file_name
FROM dba_data_files
ORDER BY 1;

DECLARE
 afn_out  NUMBER;
 dbid_out NUMBER;
BEGIN
  dbms_tts.get_afn_dbid(-999, afn_out, dbid_out);
  dbms_output.put_line('AFN: ' || TO_CHAR(afn_out));
  dbms_output.put_line('DBID: ' || TO_CHAR(dbid_out));
END;
/
 
GET_PROTECTED_CE_TAB_KEY
This trusted callout provides an interface to get the column encryption table keys in the protected form. The table key is extracted from enc$, unwrapped with the Master Key, re-wrapped with the passphrase setup with dbms_tts.set_passphrase dbms_tts.get_protected_ce_tab_key(
schemaName   IN  VARCHAR2,  -- schema name
tableName    IN  VARCHAR2,  -- table name
protTableKey OUT RAW);      -- protected Table Key
set serveroutput on

DECLARE
 pwd VARCHAR2(30) := 'OurSecret';
 tKey RAW(200);
BEGIN
  dbms_tts.set_passphrase(utl_raw.cast_to_raw(pwd), SYS.DBMS_TTS.ENCRYPTED);
  dbms_tts.get_protected_ce_tab_key('SH', 'SALES', tKey);
  dbms_output.put_line(utl_raw.cast_to_varchar2(tKey));
END;
/
 
GET_PROTECTED_TSE_KEY
This trusted callout provides an interface to get the tablespace encryption keys in the protected form. The TSE key is rewrapped with a call to dbms_tts.set_passphrase dbms_tts.get_protected_tse_key(
tablespaceNumber  IN  NUMBER, -- tablespace number
protTablespaceKey OUT RAW);   -- protected Tablespace Key
SELECT ts#, name
FROM ts$
ORDER BY 1;

set serveroutput on

DECLARE
 pwd VARCHAR2(30) := 'OurSecret';
 tKey RAW(200);
BEGIN
  dbms_tts.set_passphrase(utl_raw.cast_to_raw(pwd), SYS.DBMS_TTS.ENCRYPTED);
  dbms_tts.get_protected_tse_key(7, tKey);
  dbms_output.put_line(utl_raw.cast_to_varchar2(tKey));
END;
/
 
INSERT_ERROR
Adds an error to sys.tts_error$ if the error was not previously added dbms_tts.insert_error(
exp_err_num IN NUMBER,
err_num     IN NUMBER,
err_msg     IN VARCHAR2)
RETURN BOOLEAN;
desc tts_error$

SELECT * FROM tts_error$;

set serveroutput on

DECLARE
 b BOOLEAN;
BEGIN
  b := dbms_tts.insert_error(29335, 29335, 'ORA-39335: Test');

  IF b THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/

SELECT * FROM tts_error$;
 
ISSELFCONTAINED
Returns true if the tablespaces in ts_list are self-contained dbms_tts.isselfcontained(
ts_list             IN CLOB,
incl_constraints    IN BOOLEAN,
full_check          IN BOOLEAN,
job_type            IN VARCHAR2 DEFAULT NULL,
encryption_password IN BOOLEAN  DEFAULT FALSE)
RETURN BOOLEAN;
set serveroutput on

BEGIN
  IF dbms_tts.isselfcontained('uwdata, user_data', FALSE, TRUE) THEN
    dbms_output.put_line('Self Contained');
  ELSE
    dbms_output.put_line('Not Self Contained');
  END IF;
END;
/
 
PUT_PROTECTED_TSE_KEY (new 12.1)
Provides an interface to rewrap tablespace key from a passphrase protected key to target DB wallet and write it to the file header. Must be executed within the context of dbms_datapump. dbms_tts.put_protected_tse_key(
filename          IN VARCHAR2, -- data file name
protTablespaceKey IN RAW);     -- protected Tablespace Key
TBD
 
SET_PASSPHRASE
Sets the passphrase in a package state variable. Subsequent calls to get/put protected routines can pass the obfuscated passphrase to their respective C callouts as needed. dbms_tts.set_passphrase(passphrase IN raw, passphraseFmt IN pls_integer DEFAULT SYS.DBMS_TTS.OBFUSCATED);
DECLARE
 pwd VARCHAR2(30) := 'OurSecret';
BEGIN
  dbms_tts.set_passphrase(utl_raw.cast_to_raw(pwd), SYS.DBMS_TTS.ENCRYPTED);
END;
/
 
TRANSPORT_CHAR_SET_CHECK
Returns TRUE if char set is compatible. msg is set to 'Ok' or error message dbms_tts.transport_char_set_check(
ts_list                  IN  CLOB,
target_db_char_set_name  IN  VARCHAR2,
target_db_nchar_set_name IN  VARCHAR2
err_msg                  OUT VARCHAR2)
RETURN BOOLEAN;
set serveroutput on

DECLARE
 c    CLOB := 'uwdata';
 cset VARCHAR2(20) := 'WE8MSWIN1252';
 nset VARCHAR2(20) := 'WE8ISO8859P1';
 emsg VARCHAR2(100);
BEGIN
  IF dbms_tts.transport_char_set_check(c, cset, nset, emsg) THEN
    dbms_output.put_line('Compatible');
  ELSE
    dbms_output.put_line(emsg);
  END IF;
END;
/

DECLARE
 c    CLOB := 'uwdata';
 cset VARCHAR2(20) := 'ZHS16GBK';
 nset VARCHAR2(20) := 'WE8ISO8859P1';
 emsg VARCHAR2(100);
BEGIN
  IF dbms_tts.transport_char_set_check(c, cset, nset, emsg) THEN
    dbms_output.put_line('Compatible');
  ELSE
    dbms_output.put_line(emsg);
  END IF;
END;
/
 
TRANSPORT_CHAR_SET_CHECK_MSG
Check if the transportable set is compatible with the specified char sets dbms_tts.transport_char_set_check_msg(
ts_list                  IN CLOB,
target_db_char_set_name  IN VARCHAR2,
target_db_nchar_set_name IN VARCHAR2);
DECLARE
 c    CLOB := 'uwdata';
 cset VARCHAR2(20) := 'WE8MSWIN1252';
 nset VARCHAR2(20) := 'WE8ISO8859P1';
BEGIN
  dbms_tts.transport_char_set_check_msg(c, cset, nset);
END;
/

DECLARE
 c    CLOB := 'uwdata';
 cset VARCHAR2(20) := 'ZHS16GBK';
 nset VARCHAR2(20) := 'AR8ASMO8X';
BEGIN
  dbms_tts.transport_char_set_check_msg(c, cset, nset);
END;
/
 
TRANSPORT_SET_CHECK
Determine if Tablespace is transportable dbms_tts.transport_set_check(
ts_list          IN VARCHAR2,
incl_constraints IN BOOLEAN DEFAULT FALSE,
full_check       IN BOOLEAN DEFAULT FALSE);
exec dbms_tts.transport_set_check('uwdata, user_data', FALSE, TRUE);

SELECT * FROM TRANSPORT_SET_VIOLATIONS;

Related Topics
DataPump Executable
DBMS_EXTENDED_TTS_CHECKS
DBMS_PLUGTS
DBMS_PLUGTSP
DBMS_TDB
Export
Import
Packages
Tablespaces
Transportable Tablespaces
UTL_RAW

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