Oracle DBMS_EXTENDED_TTS_CHECK
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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 SECOBJ$
DBA_IND_PARTITIONS IND$ SNAP$
DBA_IND_SUBPARTITIONS INDPART$ STRADDLING_TS_OBJECTS
DBA_TAB_PARTITIONS KUPCC TAB$
DBA_TAB_SUBPARTITIONS MLOG$ TS$
DBMS_EXTENDED_TTS_CHECKS_LIB NTAB$ TTS_OBJ_VIEW
DBMS_PLUGTS OBJ$ USER$
DBMS_SYS_ERROR PLITBLM  
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_TABLESPACE_TAB
Get Table Tablespace dbms_extended_tts_checks.get_tablespace_tab(
objnum     IN NUMBER,
schemaname IN VARCHAR2,
objname    IN VARCHAR2,
subname    IN VARCHAR2,
objtype    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;
 
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
Built-in Functions
Built-in Packages
Database Security
DataPump Executable
DBMS_DATAPUMP
DBMS_PLUGTS
DBMS_PLUGTSP
DBMS_TDB
DBMS_TTS
Export
Import
Tablespaces
Transportable Tablespaces
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx