Oracle DBMS_TTS
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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
Data Types -- also used by dbms_extended_tts_checks

TYPE ts_names_tab_t IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER;
Dependencies
DBMS_BACKUP_RESTORE KUPCC TRANSPORT_SET_VIOLATIONS
DBMS_EXTENDED_TTS_CHECKS KUPF$FILE_INT TS$
DBMS_OUTPUT KUPP$PROC TTS_ERROR$
DBMS_PLUGTS KUPU$UTILITIES_INT TTS_ERROR_T
DBMS_REGISTRY_SYS KUPW$WORKER TTS_ERROR_TAB_T
DBMS_STREAMS_TABLESPACE_ADM PLITBLM UTL_RAW
DBMS_SYS_ERROR STRADDLING_TS_OBJECTS X$DUAL
DBMS_TTS_LIB    
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
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 GET_AFN_DBIDXENDIAN SET_PASSPHRASE
CHECKTABLESPACE GET_PROTECTED_CE_TAB_KEY TAB_FUNC_ERROR
CONVERTENCRYPTEDDATAFILECOPY GET_PROTECTED_TSE_KEY TRANSPORT_CHAR_SET_CHECK
DH_KEY_EXCHANGE INSERT_ERROR TRANSPORT_CHAR_SET_CHECK_MSG
DOWNGRADE ISSELFCONTAINED TRANSPORT_SET_CHECK
GET_AFN_DBID PUT_PROTECTED_TSE_KEY  
 
ADD_PROTECTED_CE_TAB_KEY
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;
/
 
CONVERTENCRYPTEDDATAFILECOPY
Converts an encrypted datafile between different endian values and then re-encrypt it dbms_tts.convertEncryptedDataFileCopy(
fname         IN  VARCHAR2,
outputdir     IN  VARCHAR2,
l_afn         IN  BINARY_INTEGER,
l_file_dbid   IN  BINARY_INTEGER,
endian_change IN  BOOLEAN,
encrypted     IN  BOOLEAN,
rewrapped_key IN  RAW,
outfile       OUT VARCHAR2);
TBD
 
DH_KEY_EXCHANGE
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
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_AFN_DBIDXEWNDIAN
This trusted callout gets the absolute file number and the database id for a given file x-plat dbms_tts.get_afn_dbidxendian(
filename IN  VARCHAR2,
afn      OUT NUMBER,  -- absolute file number
dbid     OUT NUMBER); -- database id
DECLARE
 afn_out  NUMBER;
 dbid_out NUMBER;
BEGIN
  dbms_tts.get_afn_dbidxendian('C:\APP\ORACLE\ORADATA\ORABASE\USERS01.DBF', afn_out, dbid_out);
  dbms_output.put_line(afn_out);
  dbms_output.put_line(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
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;
/
 
TAB_FUNC_ERROR
Queries the pl/sql error table and pipelines it to look like a SQL table dbms_tts.tab_func_error RETURN sys.tts_error_tab_t pipelined;
SELECT * FROM TABLE(dbms_tts.tab_func_error);
 
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
Built-in Functions
Built-in Packages
DataPump Executable
DBMS_CSX_ADMIN
DBMS_CSX_INT
DBMS_DATAPUMP
DBMS_EXTENDED_TTS_CHECKS
DBMS_PLUGTS
DBMS_PLUGTSP
DBMS_TDB
Export
Import
Packages
Tablespaces
Transportable Tablespaces
UTL_RAW
What's New In 19c
What's New In 20c-21c

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