Oracle DBMS_EXTENDED_TTS_CHECKS Transportable Tablespace

Search the Reference Library pages:

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

-----