Rem drv: <create type="tables"/>
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/masking/dm_tables.sql /st_emdbsa_11.2/1 2009/01/29 14:28:58 kmckeen Exp $
Rem
Rem dm_tables.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem dm_tables.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem <short description of component this file declares/defines>
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem vgoli 11/05/08 - add parallel_degree column to mgmt_dm_scopespecs
Rem kmckeen 09/04/08 - Add sequences for rule_name and column_group
Rem rpattabh 08/26/08 -
Rem vgoli 06/18/08 - add columns for advanced options
Rem rpattabh 06/12/08 - Compound Masking Changes
Rem rpattabh 05/16/08 - Project 25894: Conditional Masking
Rem xshen 02/05/08 -
Rem rpattabh 01/23/08 - remove basicfile
Rem rpattabh 01/01/08 - Add post mask script
Rem rpattabh 11/29/07 - Project 25671: Masking integration with Clone
Rem xshen 11/27/07 - conditional masking
Rem xshen 08/17/07 - drop fk constraint
Rem xshen 12/03/06 - add masking job table
Rem xshen 09/20/06 - adding arraylist item table
Rem xshen 07/17/06 - add library flag
Rem rpattabh 06/19/06 - add drop statements.
Rem lhan 05/24/06 - add sequence
Rem xshen 05/10/06 -
Rem rpattabh 04/20/06 - modify schema
Rem lhan 04/02/06 - Created
Rem
--
-- ER diagram for 10.2.0.5 GC and 11GC
--
-- MGMT_DM_SCOPESPECS(SS_GUID) <---* MGMT_DM_SS_COLUMNS
-- MGMT_DM_SS_COLUMNS(SS_GUID,SCHEMA,TABLE,COLUMN)) <---* MGMT_DM_COLUMN_RULES *---> MGMT_DM_RULETEMPLATES(RULE_GUID)
-- MGMT_DM_RULETEMPLATES(RULE_GUID) <---* MGMT_DM_RULEENTRY(RULE_GUID)
-- MGMT_DM_SS_COLUMNS <---* MGMT_DM_INFCONS_COLUMNS
-- MGMT_DM_RULEENTRY <---* MGMT_DM_ALITEMS
-- MGMT_DM_SCOPESPECS(SS_GUID) <---* MGMT_DM_JOB_EXECUTIONS
--
/*************************************
drop sequence dm_maptbl_seq;
drop sequence dm_rule_name_seq;
drop sequence dm_column_group_seq;
drop table MGMT_DM_COLUMN_RULES;
drop table MGMT_DM_INFCONS_COLUMNS;
drop table MGMT_DM_SS_COLUMNS;
drop table MGMT_DM_ALITEMS;
drop table MGMT_DM_RULEENTRY;
drop table MGMT_DM_RULETEMPLATES;
drop table MGMT_DM_JOB_EXECUTIONS;
drop table MGMT_DM_SCOPESPECS;
**************************************/
CREATE TABLE MGMT_DM_SCOPESPECS
(
SS_GUID RAW(16) DEFAULT SYS_GUID(),
SS_OWNER VARCHAR2(30) NOT NULL,
SS_NAME VARCHAR2(40) NOT NULL,
SOURCE_ID RAW(16) NOT NULL, -- Target GUID of database
SOURCE_NAME VARCHAR2(256) NOT NULL, -- Name of database
DESCRIPTION VARCHAR2(2000), -- description of DM scope
MODIFY_DATE DATE DEFAULT SYSDATE, -- mask defn last modified date
SCRIPT_DATE DATE DEFAULT SYSDATE, -- script generation date
DISABLE_LOGS VARCHAR2(1) DEFAULT 'Y' NOT NULL, -- disable logging during masking ('Y' or 'N')
REFRESH_STATS VARCHAR2(1) DEFAULT 'Y' NOT NULL, -- refresh stats after masing ('Y' or 'N')
DROP_TEMP_TABLES VARCHAR2(1) DEFAULT 'Y' NOT NULL, -- drop temp tables ('Y' or 'N')
PARALLEL_DEGREE VARCHAR2(10), -- parallel execution (DEFAULT, -1=>disabled, nn)
DM_FLAGS NUMBER DEFAULT 0, -- reserved for future use
POST_MASK_SCRIPT CLOB,
FULL_SCRIPT CLOB,
CONSTRAINT MGMT_DM_SCOPESPECS_PK PRIMARY KEY (SS_GUID),
CONSTRAINT MGMT_DM_SCOPESPECS_UC
UNIQUE (SS_OWNER,SS_NAME)
) MONITORING
LOB (POST_MASK_SCRIPT) STORE AS ( DISABLE STORAGE IN ROW CHUNK 8192 )
LOB (FULL_SCRIPT) STORE AS ( DISABLE STORAGE IN ROW CHUNK 8192 );
CREATE TABLE MGMT_DM_RULETEMPLATES
(
RULE_GUID RAW(16) DEFAULT SYS_GUID(),
RULE_OWNER VARCHAR2(30) NOT NULL,
RULE_NAME VARCHAR2(40) NOT NULL,
DESCRIPTION VARCHAR2(2000), -- description of DM rule template
OUTPUT_TYPE NUMBER DEFAULT 0 NOT NULL, -- column data type of masked col
IS_LIBRARY NUMBER DEFAULT 0 NOT NULL, -- if the rule is a library template
RULE_ORDER NUMBER, -- Support conditional masking
RULE_CONDITION VARCHAR2(2000), -- Support conditional masking
CONSTRAINT MGMT_DM_RULETEMPLATES_PK PRIMARY KEY (RULE_GUID),
CONSTRAINT MGMT_DM_RULETEMPLATES_UC
UNIQUE (RULE_OWNER, RULE_NAME)
) MONITORING;
-- In 11g the rules are seperately stored hence two parent tables for FK.
CREATE TABLE MGMT_DM_RULEENTRY
(
RULE_GUID RAW(16) NOT NULL,
ENTRY_ORDER NUMBER NOT NULL,
RULE_TYPE VARCHAR2(30),
RULE_OPTION VARCHAR2(2),
RULE_LOW NUMBER,
RULE_HIGH NUMBER,
START_DATE DATE,
END_DATE DATE,
FIXED_STRING VARCHAR2(30),
FIXED_NUMBER NUMBER,
TABLE_SCHEMA VARCHAR2(30),
TABLE_NAME VARCHAR2(30),
COLUMN_NAME VARCHAR2(30),
UDF_NAME VARCHAR2(512), -- user defined func name
CONSTRAINT MGMT_DM_RULEENTRY_PK PRIMARY KEY (RULE_GUID, ENTRY_ORDER),
CONSTRAINT MGMT_DM_RULEENTRY_FK
FOREIGN KEY (RULE_GUID)
REFERENCES MGMT_DM_RULETEMPLATES (RULE_GUID)
ON DELETE CASCADE
) MONITORING;
CREATE TABLE MGMT_DM_SS_COLUMNS
(
SS_GUID RAW(16) NOT NULL,
RULE_GUID RAW(16), -- Deprecated in 11g. Use MGMT_DM_COLUMN_RULES.RULE_GUID
TABLE_SCHEMA VARCHAR2(30) NOT NULL,
TABLE_NAME VARCHAR2(128) NOT NULL, -- Do we handle long name???
COLUMN_NAME VARCHAR2(128) NOT NULL,
COLUMN_GROUP VARCHAR2(30),
CONSTRAINT MGMT_DM_SS_COLUMNS_FK1
FOREIGN KEY (SS_GUID)
REFERENCES MGMT_DM_SCOPESPECS (SS_GUID)
ON DELETE CASCADE,
CONSTRAINT MGMT_DM_SS_COLUMNS_UC
UNIQUE (SS_GUID, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
) MONITORING;
-- This table is for rules that used for columns, not library
CREATE TABLE MGMT_DM_COLUMN_RULES
(
SS_GUID RAW(16) NOT NULL,
TABLE_SCHEMA VARCHAR2(30) NOT NULL,
TABLE_NAME VARCHAR2(128) NOT NULL, -- Do we handle long name???
COLUMN_NAME VARCHAR2(128) NOT NULL,
RULE_GUID RAW(16) NOT NULL,
CONSTRAINT MGMT_DM_COLUMN_RULES_FK
FOREIGN KEY (SS_GUID,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
REFERENCES MGMT_DM_SS_COLUMNS (SS_GUID,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
ON DELETE CASCADE,
CONSTRAINT MGMT_DM_COLUMN_RULES_FK2
FOREIGN KEY (RULE_GUID)
REFERENCES MGMT_DM_RULETEMPLATES (RULE_GUID)
ON DELETE CASCADE
) MONITORING;
CREATE INDEX MGMT_DM_COLUMN_RULES_IX1 on MGMT_DM_COLUMN_RULES(SS_GUID);
CREATE INDEX MGMT_DM_COLUMN_RULES_IX2 on MGMT_DM_COLUMN_RULES(RULE_GUID);
CREATE TABLE MGMT_DM_INFCONS_COLUMNS
(
SS_GUID RAW(16) NOT NULL,
TABLE_SCHEMA VARCHAR2(30) NOT NULL,
TABLE_NAME VARCHAR2(128) NOT NULL,
COLUMN_NAME VARCHAR2(128) NOT NULL,
PARENT_SCHEMA VARCHAR2(30) NOT NULL,
PARENT_TABLE VARCHAR2(128) NOT NULL,
PARENT_COLUMN VARCHAR2(128) NOT NULL,
CONSTRAINT MGMT_DM_INFCONS_COLUMNS_FK1
FOREIGN KEY (SS_GUID,PARENT_SCHEMA,PARENT_TABLE,PARENT_COLUMN)
REFERENCES MGMT_DM_SS_COLUMNS (SS_GUID,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
ON DELETE CASCADE,
CONSTRAINT MGMT_DM_INFCONS_COLUMNS_UC
UNIQUE (SS_GUID,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
) MONITORING;
CREATE TABLE MGMT_DM_ALITEMS
(
RULE_GUID RAW(16) NOT NULL,
ENTRY_ORDER NUMBER NOT NULL,
ARRAYLIST_ITEM VARCHAR2(128), -- do we store array list item order?
CONSTRAINT MGMT_DM_ALITEMS_PK PRIMARY KEY (RULE_GUID, ENTRY_ORDER, ARRAYLIST_ITEM),
CONSTRAINT MGMT_DM_ALITEMS_FK
FOREIGN KEY (RULE_GUID, ENTRY_ORDER)
REFERENCES MGMT_DM_RULEENTRY (RULE_GUID, ENTRY_ORDER)
ON DELETE CASCADE
) MONITORING;
CREATE TABLE MGMT_DM_JOB_EXECUTIONS
(
SS_GUID RAW(16),
EXECUTION_ID RAW(16),
SUBMISSION_TS DATE DEFAULT SYSDATE,
CONSTRAINT MGMT_DM_JOB_EXEC_PK PRIMARY KEY (SS_GUID,EXECUTION_ID),
CONSTRAINT MGMT_DM_JOB_EXEC_FK1
FOREIGN KEY (SS_GUID)
REFERENCES MGMT_DM_SCOPESPECS (SS_GUID)
ON DELETE CASCADE
) MONITORING;
CREATE SEQUENCE dm_maptbl_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
CREATE SEQUENCE dm_rule_name_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
CREATE SEQUENCE dm_column_group_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;