Database Guide › Database Storage and Partitioning › Set Up Partitions and Purging › Retrospective Partitioning › Partitioning an Existing Data Warehouse (Oracle)
Partitioning an Existing Data Warehouse (Oracle)
The following command partitions the data warehouse with partition dates that match the CMS.
Run this command as the schema owner, using the SQL*Plus utility:
SQL> EXEC Wgn_Partition_Util.wgn_partition_tables(
partition_dates => Wgn_Partition_Util.get_partition_dates(Wgn_Partition_Util.wgn_db_type_cms)
, db_type => Wgn_Partition_Util.wgn_db_type_DW
, Partition_Options => Wgn_Partition_Util.wgn_opt_Convert_to_Partitioned
, new_tablespace => 'WGNDATADW'
, new_indextablespace => 'WGNDATADWIDX'
, pdebug => True
);
The command converts the following tables to partitioned tables and migrates the data:
- Fact Tables
-
- WGNDW_EVENT_FACT
- WGNDW_EVENT_PARTICIPANT_FACT
- WGNDW_TRIGGER_FACT
- WGNDW_AUDIT_CURR_FACT
- WGNDW_AUDIT_TRAN_FACT
- WGNDW_EVENT_AUDIT_STATUS_FACT
- WGNDW_ISSUE_PARTICIPANT_BRG
- WGNDW_ISSUE_TRIGGER_BRG
- WGNDW_AUDIT_REVIEWER_BRG
- WGNDW_DETAILED_EV_ATTR_BRG
- WGNDW_AUDIT_ISSUE_REF
This command also moves the data warehouse fact tables to the WGNDATADW and WGNDATADWIDX partitions.
- Other Data Warehouse Tables
-
The following data warehouse tables and indexes are placed in the default tablespace. The database administrator can move these tables and indexes to a more suitable location.
Dimension Tables
- RUT_DLP_AGGREGATION_VALUES
- RUT_DLP_CURR_AGG_SUMMARY
- RUT_DLP_SNAPSHOT_DATA
- WGNDW_AUDIT_APPLICATION_DIN
- WGNDW_AUDIT_APPLICATION_REF
- WGNDW_AUDIT_COMMENTS_DIM
- WGNDW_AUDIT_DETAIL_TYPE_DIN
- WGNDW_AUDIT_DETAIL_TYPE_REF
- WGNDW_AUDIT_FIELD3_EXTRA_BRG
- WGNDW_AUDIT_FLAGS_DIM
- WGNDW_AUDIT_STATUS_DIM
- WGNDW_AUDIT_STATUS_DIN
- WGNDW_AUDIT_TEXT_DIM
- WGNDW_AUDIT_TEXT_DIN
- WGNDW_DATA_LOAD_DIM
- WGNDW_DATA_LOAD_SOURCE_DIM
- WGNDW_DATE_DIM
- WGNDW_DATE_DIN
- WGNDW_EVENT_ATTRIBUTES_DIM
- WGNDW_EVENT_ATTRIBUTES_DIN
- WGNDW_EVENT_TEXT_DIM
- WGNDW_EVENT_TYPE_DIM
- WGNDW_EVENT_TYPE_DIN
- WGNDW_EXPORT_FORMAT_DIN
- WGNDW_EXPORT_FORMAT_REF
- WGNDW_GROUP_MEMBERSHIP_BRG
- WGNDW_ISSUE_NAME_DIM
- WGNDW_LANGUAGES_INSTALLED
- WGNDW_MANAGEMENT_GROUP_BRG
- WGNDW_OVERALL_RISK_AGGS
- WGNDW_PARTICIPATION_TYPE_DIM
- WGNDW_PARTICIPATION_TYPE_DIN
- WGNDW_QUARANTINE_STATE_DIN
- WGNDW_QUARANTINE_STATE_REF
- WGNDW_SEVERITY_DIM
- WGNDW_SEVERITY_DIN
- WGNDW_SYSTEM_CONFIG
- WGNDW_TIME_DIM
- WGNDW_TRIGGER_ACTION_DIM
- WGNDW_TRIGGER_ACTION_DIN
- WGNDW_TRIGGER_TYPE_DIM
- WGNDW_TRIGGER_TYPE_DIN
- WGNDW_USER_ADDRESS_BRG
- WGNDW_USER_DIM
- WGNDW_USER_GROUP_DIM
- WGNDW_USER_HISTORY_DIM
- WGNDW_USER_PROPERTIES_BRG
- WGNDW_VERSION
- WGNDW_ADDRESS_DIM
- WGN_RPT_PROCESSLOG
Staging Tables
- WGNDW_STG_AUDIT_CURR_FACT
- WGNDW_STG_AUDIT_TRAN_FACT
- WGNDW_STG_EP_FACT
- WGNDW_STG_EVENT_FACT
- WGNDW_STG_EXTRACT_LIST
- WGNDW_STG_GROUP_MEMBERSHIP
- WGNDW_STG_MANAGEMENT_GROUP
- WGNDW_STG_POLICY_REF
- WGNDW_STG_TRIGGER_FACT
- WGNDW_STG_USER
- WGNDW_STG_USER_ADDRESS
- WGNDW_STG_USER_GROUP
- WGNDW_STG_USER_PROPERTIES
Dimension Indexes
- PK_WGNDW_VERSION
- PK_WGNDW_USER_PROPERTIES_BRG
- IX_WGNDW_USER_PROP_BRG_ID_USER
- IX_WGNDW_USER_PROP_BRG_LOADKEY
- PK_WGNDW_USER_HISTORY_DIM
- PK_WGNDW_USER_GROUP_DIM
- IX_WGNDW_USER_GROUP_DIM_BK
- IX_WGNDW_USER_GROUP_DIM_NAME
- IX_WGNDW_USER_GROUP_DIM_NESTS
- PK_WGNDW_USER_DIM
- IX_WGNDW_USER_DIM_BK
- IX_WGNDW_USER_DIM_NAME
- PK_WGNDW_USER_ADDRESS_BRG
- IX_WGNDW_USER_ADDRESS_BRG
- PK_WGNDW_TRIGGER_TYPE_DIN
- PK_WGNDW_TRIGGER_TYPE_DIM
- IX_WGNDW_TRIGGER_TYPE_DIM_POL
- IX_WGNDW_TRIGGER_TYPE_DIM_CLS
- IX_WGNDW_TRIGGER_TYPE_DIM_AK
- PK_WGNDW_DATE_DIN
- PK_WGNDW_DATE_DIM
- IX_WGNDW_DATE_DIM_FULL_DATE
- PK_WGNDW_DATA_LOAD_SOURCE_DIM
- PK_WGNDW_DATA_LOAD_DIM
- PK_WGNDW_EXPORT_FORMAT_REF
- PK_WGNDW_EXPORT_FORMAT_DIN
- PK_WGNDW_EVENT_TYPE_DIN
- PK_WGNDW_EVENT_TYPE_DIM
- IX_WGNDWEVENTTYPEDIM_AK
- PK_WGNDW_EVENT_TEXT_DIM
- IX_WGNDW_EVENT_TEXT_DIM_HASH
- PK_WGNDW_QUARANTINE_STATE_REF
- PK_WGNDW_QUARANTINE_STATE_DIN
- PK_WGNDW_PARTICIPATION_TYP_DIN
- PK_WGNDW_PARTICPTN_TYPE_DIM
- IX_WGNDW_PARTICPTN_TYPE_DIM_AK
- PK_WGNDW_OVERALL_RISK_AGGS
- PK_WGNDW_MANAGEMENT_GROUP_BRG
- IX_WGNDW_MANAGEMENT_GROUP_BRG
- PK_WGNDW_LANGUAGES_INSTALLED
- PK_WGNDW_AUDIT_TEXT_DIN
- PK_WGNDW_AUDIT_TEXT_DIM
- IX_WGNDW_AUDIT_TEXT_DIM
- PK_WGNDW_AUDIT_STATUS_DIN
- PK_WGNDW_AUDIT_STATUS_DIM
- PK_WGNDW_AUDIT_FLAGS_DIM
- PK_WGNDW_AUDIT_FLD3_EXTRA_BRG
- PK_WGNDW_AUDIT_DETAIL_TYPE_REF
- PK_WGNDW_AUDIT_DETAIL_TYPE_DIN
- PK_WGNDW_EVENT_ATTRIBUTES_DIN
- PK_WGNDW_EVENT_ATTRIBUTES_DIM
- IX_WGNDW_EVENT_ATTR_DIM_ATTR
- PK_WGNDW_SEVERITY_DIN
- IX_WGNDW_SEVERITY_DIM_SEV
- PK_WGNDW_SEVERITY_DIM
- PK_WGNDW_AUDIT_COMMENTS_DIM
- IX_WGNDW_AUDIT_COMMENTS_DIM
- PK_WGNDW_AUDIT_APPLICATION_REF
- PK_WGNDW_AUDIT_APPLICATION_DIN
- IX_WGNDWADDRDIM_SOURCEHASHKEY
- IX_WGNDWADDRDIM_USERKEY
- PK_WGNDW_ADDRESS_DIM
- IX_WGNDWADDRDIM_ADDRNMHASHKEY
- PK_WGNDW_TRIGGER_ACTION_DIN
- IX_WGNDW_TRG_ACTN_DIM_BK
- PK_WGNDW_TRIGGER_ACTION_DIM
- PK_WGNDW_TIME_DIM
- UK_WGNDW_TIME_DIM_HOUR_MINUTE
- PK_WGNDW_ISSUE_NAME_DIM
- IX_WGNDW_ISSUE_NAME_DIM
- PK_WGNDW_GROUP_MEMBERSHIP_BRG
- IX_WGNDW_GROUP_MEMBERSHIP_BRG
Staging Indexes
- PK_WGNDW_STG_SYSTEM_CONFIG
- PK_WGNDW_STG_USER
- IX_WGNDW_STG_POLICY_REF_AK
- PK_WGNDW_STG_EXTRACT_LIST
Sample scripts to convert and move the data warehouse are available from CA Support.
Copyright © 2015 CA Technologies.
All rights reserved.
|
|