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
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
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);
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);
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;
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
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
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;
/
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
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;
/
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;
/