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