| 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 |
| 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 |
OBJ$ |
| DBMS_OUTPUT |
PLITBLM |
| DBMS_PLUGTS |
SYS |
| DBMS_PLUGTS_LIB |
TRANSPORT_SET_VIOLATIONS |
| DBMS_SQL |
TS$ |
| DBMS_STREAMS_TABLESPACE_ADM |
USER$ |
| DBMS_SYS_ERROR |
|
|
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 the
execute_catalog_role role |
| |
| CHECKTABLESPACE
(new 11g) |
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;
/ |
| |
| DOWNGRADE |
| Downgrades transportable tablespace related data |
dbms_tts.downgrade; |
| exec dbms_tts.downgrade; |
| |
| INSERT_ERROR (new
11g) |
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 CLOB,
incl_constraints BOOLEAN,
full_check BOOLEAN)
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;
/ |
| |
| 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; |