CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_TTS
Version 11.1
 
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;
 
Related Topics
DBMS_EXTENDED_TTS_CHECKS
Export
Import
Packages
Tablespaces
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [198 users online]    © 2010 psoug.org