Oracle DBMS_TTS Demos

Oracle DBMS_TTS Version 11.2.0.3

General Information

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

-- used by dbms_extended_tts_checks

TYPE tablespace_names IS TABLE OF VARCHAR(30)
INDEX BY BINARY_INTEGER;

Dependencies

DBMS_EXTENDED_TTS_CHECKS	DUAL	PLITBLM
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

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 the EXECUTE_CATALOG_ROLE role only

Source

{ORACLE_HOME}/rdbms/admin/dbmsplts.sql

Subprograms

CHECKTABLESPACE	INSERT_ERROR	TRANSPORT_CHAR_SET_CHECK
DOWNGRADE	ISSELFCONTAINED	TRANSPORT_CHAR_SET_CHECK_MSG
GET_PROTECTED_CE_TAB_KEY	SET_PASSPHRASE	TRANSPORT_SET_CHECK
GET_PROTECTED_TSE_KEY

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

DOWNGRADE

Downgrades transportable tablespace related data

dbms_tts.downgrade;

exec dbms_tts.downgrade;

GET_PROTECTED_CE_TAB_KEY (new parameters 11.2.0.3)

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 (new parameters 11.2.0.3)

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 (new parameters 11.2.0.3)

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

SET_PASSPHRASE (new 11.2.0.3)

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;