Oracle DBMS_TTS
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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
Database Security
DataPump Executable
DBMS_CSX_ADMIN
DBMS_CSX_INT
DBMS_DATAPUMP
DBMS_EXTENDED_TTS_CHECKS
DBMS_PLUGTS
DBMS_PLUGTSP
DBMS_TDB
Export
Import
Tablespaces
Transportable Tablespaces
UTL_RAW
What's New In 21c
What's New In 23c

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