Oracle DBMS_EXTENDED_TTS_CHECKS
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Supports checks for the transportable tablespace feature. Adds support to capture any objects that would prevent the transportable feature to be used because of dependencies between objects in the transportable set and those not contained in the transportable set.
AUTHID DEFINER
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;
Dependencies
DBA_INDEXES DBMS_TTS SNAP$
DBA_IND_PARTITIONS IND$ STRADDLING_TS_OBJECTS
DBA_IND_SUBPARTITIONS INDPART$ TAB$
DBA_TAB_PARTITIONS MLOG$ TS$
DBA_TAB_SUBPARTITIONS NTAB$ TTS_OBJ_VIEW
DBMS_EXTENDED_TTS_CHECKS_LIB OBJ$ TTS_TBS$
DBMS_PLUGTS PLITBLM USER$
DBMS_SYS_ERROR SECOBJ$  
Documented No
First Available Not known
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/dbmsplts.sql
Subprograms
 
CHECK_CSX_CLOSURE
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@pdbdev

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 sys@pdbdev 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@pdbdev

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 sys@pdbdev 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_ae
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 Demo Below
 
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 Demo Below
 
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_ae
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 Demo Below
 
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 sys@pdbdev as sysdba

col object_name format a30

SELECT object_id, object_name, object_type
FROM dba_objects_ae
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(fromExp IN BOOLEAN) RETURN BOOLEAN;
set serveroutput on

BEGIN
  IF dbms_extended_tts_checks.verify_extensible(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(fromExp IN BOOLEAN, full_check IN 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

BEGIN
  IF dbms_extended_tts_checks.verify_mv(TRUE,TRUE) THEN
    dbms_output.put_line('Self Contained');
  ELSE
    dbms_output.put_line('Not Self Contained');
  END IF;
END;
/
 
VERIFY_NT
Verifies that all nested tables are fully contained within the set dbms_extended_tts_checks.verify_nt(fromExp IN BOOLEAN) RETURN BOOLEAN;
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 to 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
DataPump Executable
DataPump Package
DBMS_PLUGTS
DBMS_PLUGTSP
DBMS_TDB
DBMS_TTS
Export
Import
Packages
Tablespaces
Transportable Tablespaces

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved