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_EXTENDED_TTS_CHECKS

Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmsplts.sql

Dependencies
IND$ OBJ$ TAB$
MLOG$ SECOBJ$ TS$
NTAB$ SNAP$ USER$
 
DBA_INDEXES MLOG$
DBA_IND_PARTITIONS NTAB$
DBA_IND_SUBPARTITIONS OBJ$
DBA_TAB_PARTITIONS PLITBLM
DBA_TAB_SUBPARTITIONS SECOBJ$
DBMS_EXTENDED_TTS_CHECKS_LIB SNAP$
DBMS_PLUGTS TAB$
DBMS_TTS TTS_OBJ_VIEW
DBMS_SYS_ERROR TTS_TBS$
IND$  

Data Types
-- pass information about an object 
TYPE objrec IS RECORD (
v_pobjschema VARCHAR2(30),
v_pobjname   VARCHAR2(30),
v_objid      NUMBER,
v_objname    VARCHAR2(30),
v_objsubname VARCHAR2(30),
v_objowner   VARCHAR2(30),
v_objtype    VARCHAR2(15));

-- List of object records 
TYPE t_objlist IS TABLE OF objrec
INDEX BY BINARY_INTEGER;
Object Privileges execute on dbms_extended_tts_checks
 
CHECK_CSX_CLOSURE (new 11g)

Verifies that all token manager tables for XML tables and columns 
with binary storage (CSX) are also contained in the transported
tablespaces. This is needed so that data at the import site can be
decoded without a full remapping.
dbms_extended_tts_checks.check_csx_closure(
tsnames IN dbms_tts.tablespace_names,
fromExp IN BOOLEAN)
RETURN BOOLEAN;
SELECT DISTINCT table_type
FROM dba_all_tables;

SELECT owner, table_name, tablespace_name
FROM dba_all_tables
WHERE table_type = 'XMLTYPE';

set serveroutput on

DECLARE
 tslist dbms_tts.tablespace_names;
BEGIN
  tsnamelist(1) := 'USERS';  
  IF
dbms_extended_tts_checks.check_csx_closure(tslist, TRUE) THEN
    dbms_output.put_line('Token Manager Table Check Succeeded');
  ELSE
    dbms_output.put_line('Token Manager Check Failed Verification');
  END IF;
END;
/
 
GET_CHILD_NESTED_TABLES

Returns child nested tables associated with a parent nested table object in a list
dbms_extended_tts_checks.get_child_nested_tables(objn NUMBER)
RETURN t_objlist;
conn uwclass/uwclass

CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64);
/

CREATE TABLE department (
name VARCHAR2(20),
director VARCHAR2(20),
office VARCHAR2(20),
courses CourseList)
NESTED TABLE courses STORE AS courses_tab;

col object_name format a40

SELECT object_id
FROM user_objects
WHERE object_name = 'DEPARTMENT';

conn / as sysdba

set serveroutput on

DECLARE
 retlist dbms_extended_tts_checks.t_objlist;
BEGIN
 retlist:=dbms_extended_tts_checks.get_child_nested_tables(65634);

 dbms_output.put_line('*************************');
 FOR i IN 1..retlist.COUNT
 LOOP
   dbms_output.put_line('Obj ID:     ' || retlist(i).v_objid);
   dbms_output.put_line('Obj Name:   ' || retlist(i).v_objname);
   dbms_output.put_line('Owner:      ' || retlist(i).v_objowner);
   dbms_output.put_line('Obj Type:   ' || retlist(i).v_objtype);
   dbms_output.put_line('*************************');
 END LOOP;
END;
/
 
GET_DOMAIN_INDEX_SECOBJ

Returns objects associated with an extensible index in a list
dbms_extended_tts_checks.get_domain_index_secobj(objn IN NUMBER)
RETURN t_objlist;
conn uwclass/uwclass

CREATE TABLE domtab(
did  NUMBER,
docs CLOB);

ALTER TABLE domtab
ADD CONSTRAINT pk_domtab
PRIMARY KEY (did)
USING INDEX;

INSERT INTO domtab
(did, docs)
VALUES
(111555, 'This text will be indexed');

INSERT INTO domtab
(did, docs)
VALUES
(111556,'This is a default datastore example');
COMMIT;

CREATE INDEX dix_domtab
ON domtab(docs) 
INDEXTYPE IS ctxsys.context 
PARAMETERS('DATASTORE CTXSYS.DEFAULT_DATASTORE');

SELECT index_name, index_type
FROM user_indexes
WHERE table_name = 'DOMTAB';

SELECT o.object_id, i.table_name, i.index_name
FROM user_objects o, user_indexes i
WHERE o.object_name = i.index_name
AND i.table_name = 'DOMTAB';

conn / as sysdba

set serveroutput on

DECLARE
 retlist dbms_extended_tts_checks.t_objlist;
BEGIN
 retlist:=dbms_extended_tts_checks.get_domain_index_secobj(65648);

 dbms_output.put_line('*************************');
 FOR i IN 1..retlist.COUNT
 LOOP
   dbms_output.put_line('Obj ID:     ' || retlist(i).v_objid);
   dbms_output.put_line('Obj Name:   ' || retlist(i).v_objname);
   dbms_output.put_line('Owner:      ' || retlist(i).v_objowner);
   dbms_output.put_line('Obj Type:   ' || retlist(i).v_objtype);
   dbms_output.put_line('*************************');
 END LOOP;
END;
/
 
GET_TABLESPACE_IND

If index is partitioned then return the tablespace  associated with the first partition
dbms_extended_tts_checks.get_tablespace_ind(
object_id      IN NUMBER,
object_owner   IN VARCHAR2, 
object_name    IN VARCHAR2, 
object_subname IN VARCHAR2,
object_type    IN VARCHAR2)
RETURN VARCHAR2;
SELECT object_id
FROM dba_objects
WHERE owner = 'UWCLASS'
AND object_name = 'PK_SERVERS';

SELECT dbms_extended_tts_checks.get_tablespace_ind(52787, 'UWCLASS', 'PK_SERVERS', NULL, 'INDEX')
FROM dual;
 
GET_TABLESPACE_INDPART

Get Index Partition Tablespace
dbms_extended_tts_checks.get_tablespace_indpart(
object_id      IN NUMBER,
object_owner   IN VARCHAR2, 
object_name    IN VARCHAR2, 
object_subname IN VARCHAR2,
object_type    IN VARCHAR2)
RETURN VARCHAR2;
See get_tablespace_tabsubpart
 
GET_TABLESPACE_INDSUBPART
Get Index Subpartition Tablespace dbms_extended_tts_checks.get_tablespace_indsubpart(
object_id      IN NUMBER,
object_owner   IN VARCHAR2, 
object_name    IN VARCHAR2, 
object_subname IN VARCHAR2,
object_type    IN VARCHAR2) 
RETURN VARCHAR2;
See get_tablespace_tabsubpart
 
GET_TABLESPACE_TAB

Get Table Tablespace
dbms_extended_tts_checks.get_tablespace_tab(
object_id      IN NUMBER,
object_owner   IN VARCHAR2, 
object_name    IN VARCHAR2, 
object_subname IN VARCHAR2,
object_type    IN VARCHAR2)
RETURN VARCHAR2;
SELECT object_id
FROM dba_objects
WHERE owner = 'UWCLASS'
AND object_name = 'SERVERS';

SELECT dbms_extended_tts_checks.get_tablespace_tab(52786, 'UWCLASS', 'SERVERS', NULL, 'TABLE')
FROM dual;
 
GET_TABLESPACE_TABPART

Get Table Partition Tablespace
dbms_extended_tts_checks.get_tablespace_tabpart(
object_id      IN NUMBER,
object_owner   IN VARCHAR2, 
object_name    IN VARCHAR2, 
object_subname IN VARCHAR2,
object_type    IN VARCHAR2)
RETURN VARCHAR2;
See get_tablespace_tabsubpart
 
GET_TABLESPACE_TABSUBPART

Get Table Subpartition Tablespace
dbms_extended_tts_checks.get_tablespace_tabsubpart(
object_id      IN NUMBER,
object_owner   IN VARCHAR2, 
object_name    IN VARCHAR2, 
object_subname IN VARCHAR2,
object_type    IN VARCHAR2)
RETURN VARCHAR2;
CREATE TABLE subparttab (
salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
SUBPARTITION BY HASH(salesman_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE uwdata,
SUBPARTITION sp2 TABLESPACE uwdata)
(PARTITION last_years
VALUES LESS THAN(TO_DATE('01/01/2004','DD/MM/YYYY')),
PARTITION sales_q1
VALUES LESS THAN(TO_DATE('04/01/2004','DD/MM/YYYY')),
PARTITION sales_q2
VALUES LESS THAN(TO_DATE('07/01/2004','DD/MM/YYYY')),
PARTITION future_sales
VALUES LESS THAN(MAXVALUE));

conn / as sysdba

col object_name format a30

SELECT object_id, object_name, object_type
FROM dba_objects
WHERE owner = 'UWCLASS'
AND object_name = 'SUBPARTTAB';

SELECT dbms_extended_tts_checks.get_tablespace_tabsubpart(65794, 'UWCLASS', 'SUBPARTTAB', NULL, 'TABLE') AS RESULT
FROM dual;

ALTER TABLE subparttab
MOVE SUBPARTITION future_sales_sp1
TABLESPACE user_data;

SELECT dbms_extended_tts_checks.get_tablespace_tabsubpart(65794, 'UWCLASS', 'SUBPARTTAB', NULL, 'TABLE') AS RESULT
FROM dual;
 
OBJECTLIST_CONTAINED

Ensures that the group of objects that are passed in either are fully IN or OUT of the tslist (set of tablespaces to be transported)
dbms_extended_tts_checks.objectlist_Contained(
vobjlist IN t_objlist)
RETURN NUMBER;

-- RETURN CODES
-- straddling objects across transportable set - 0
-- all objects in list are fully contained - 1
-- all objects in list are fully outside - 2
set serveroutput on

DECLARE
 objlist dbms_extended_tts_checks.t_objlist;
 RetVal  PLS_INTEGER;
BEGIN
  objlist(1).v_objname := 'SERVERS';
  objlist(2).v_objname := 'PK_SERVERS';
 
  RetVal := dbms_extended_tts_checks.objectlist_contained(objlist);
  dbms_output.put_line(RetVal);
END;
/
 
VERIFY_EXTENSIBLE

Verify that any secondary objects associated with an extensible index are contained in the list
dbms_extended_tts_checks.verify_extensible(
tsnames IN dbms_tts.tablespace_names, fromExp IN BOOLEAN)
RETURN BOOLEAN;
set serveroutput on

DECLARE
 tslist  dbms_tts.tablespace_names;
BEGIN
  tslist(1) := 'UWDATA';
  tslist(2) := 'USER_DATA';

  IF dbms_extended_tts_checks.verify_extensible(tslist,TRUE) THEN
    dbms_output.put_line('Self Contained');
  ELSE
    dbms_output.put_line('Not Self Contained');
  END IF;
END;
/
 
VERIFY_MV

Verify that materialized view logs stored as tables and corresponding master tables are self contained and that updateable materialized view tables  and their logs are fully contained in the set
dbms_extended_tts_checks.verify_mv(
tsnames dbms_tts.tablespace_names, fromExp BOOLEAN, full_check BOOLEAN) RETURN BOOLEAN;
CREATE MATERIALIZED VIEW LOG ON servers
PCTFREE 5
PCTUSED 90
TABLESPACE uwdata
WITH PRIMARY KEY, ROWID;

CREATE MATERIALIZED VIEW mv_simple
TABLESPACE uwdata
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
SELECT *
FROM servers;

set serveroutput on

DECLARE
 tslist  dbms_tts.tablespace_names;
BEGIN
  tslist(1) := 'UWDATA';
  tslist(2) := 'USER_DATA';

  IF dbms_extended_tts_checks.verify_mv(tslist,TRUE,TRUE) THEN
    dbms_output.put_line('Self Contained');
  ELSE
    dbms_output.put_line('Not Self Contained');
  END IF;
END;
/
 
VERIFY_NT

Verify that all nested tables are fully in or out of the set

Note: The tsnames parameter has been dropped in 11g which makes no sense and I am verifying whether it is correct right now. (12/6/07).

-- 10g
dbms_extended_tts_checks.verify_nt(
tsnames IN dbms_tts.tablespace_names, fromExp IN BOOLEAN)
RETURN BOOLEAN;

-- 11g
dbms_extended_tts_checks.verify_nt(fromExp IN BOOLEAN) RETURN BOOLEAN;
-- 10g version
DECLARE
 tslist  dbms_tts.tablespace_names;
BEGIN
  tslist(1) := 'UWDATA';
  tslist(2) := 'USER_DATA';

  IF dbms_extended_tts_checks.verify_nt(tslist,TRUE) THEN
    dbms_output.put_line('Self Contained');
  ELSE
    dbms_output.put_line('Not Self Contained');
  END IF;
END;
/

-- 11g version
BEGIN
  IF dbms_extended_tts_checks.verify_nt(TRUE) THEN
    dbms_output.put_line('Self Contained');
  ELSE
    dbms_output.put_line('Not Self Contained');
  END IF;
END;
/
 
VERIFY_XMLSCHEMA

Verifies schema based XMLType tables that are part of the transport set are self contained. i.e. the out of
line pieces that the table points to are also part of the transport set. Tol ensure that the SB XMLType table is self contained.
dbms_extended_tts_checks.verify_xmlschema(
tsnames IN dbms_tts.tablespace_names,
fromExp IN BOOLEAN)
RETURN BOOLEAN;
SELECT DISTINCT table_type
FROM dba_all_tables;


SELECT owner, table_name, tablespace_name
FROM dba_all_tables
WHERE table_type = 'XMLTYPE';

set serveroutput on

DECLARE
 tslist dbms_tts.tablespace_names;
BEGIN
  tsnamelist(1) := 'USERS';  
  IF
dbms_extended_tts_checks.verify_xmlschema(tslist, TRUE) THEN
    dbms_output.put_line('TTS Check Succeeded');
  ELSE
    dbms_output.put_line('TTS Check Failed Verification');
  END IF;
END;
/
 
Related Topics
DBMS_TTS
Transportable Tablespaces
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [149 users online]    © 2010 psoug.org