| General Information |
| Purpose |
Determine whether violations exist that would prevent transporting a tablespace or tablespace set |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsplts.sql |
| First Available |
8.1.5 |
| 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 |
KUPU$UTILITIES_INT |
| DBMS_OUTPUT |
KUPW$WORKER |
| DBMS_PLUGTS |
PLITBLM |
| DBMS_STREAMS_TABLESPACE_ADM |
TRANSPORT_SET_VIOLATIONS |
| DBMS_SYS_ERROR |
TS$ |
| DBMS_TTS_LIB |
TTS_ERROR$ |
| DUAL |
TTS_TBS$ |
| KUPCC |
UTL_RAW |
| KUPP$PROC |
|
|
| Exceptions |
| Error Code |
Name |
Description |
| ORA-29304 |
ts_not_found |
Tablespace Not Found |
| ORA-29335 |
ts_not_read_only |
Tablespace not read only |
| ORA-29336 |
internal_error |
Internal DBMS_TTS error |
| ORA-29338 |
datafile_not_ready |
Datafile Not Ready |
| ORA-29339 |
blocksize_mismatch |
Blocksizes Do Not Match |
| ORA-29340 |
exportfile_corrupted |
Export File Corrupted |
| ORA-29341 |
not_self_contained |
Tablespace Is Not Self-Contained |
| ORA-29342 |
user_not_found |
User Not Found |
| ORA-29343 |
mapped_user_not_found |
Mapped User Not Found |
| ORA-29344 |
user_not_in_list |
User Not Listed |
| ORA-29345 |
different_char_set |
Character Set Mismatch |
| ORA-29346 |
invalid_ts_list |
Invalid Tablespace List |
| ORA-29347 |
ts_not_in_list |
Tablespace Not Listed |
| ORA-29348 |
datafiles_missing |
Missing Datafile |
| ORA-29349 |
ts_name_conflict |
Tablespace Name Conflict |
| ORA-29351 |
sys_or_tmp_ts |
System or Temp Tablespace |
| ORA-29353 |
ts_list_overflow |
Tablespace List Overflow |
|
| Security Model |
Execute is granted to EXECUTE_CATALOG_ROLE role |
| |
| 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; |
