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;
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;
/
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 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 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;
/
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;
/
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;
/
