E OAAM Partition Schema Reference

This appendix provides information about tables and stored procedures used with Oracle Adaptive Access Manager (OAAM) with Partition support.

It contains the following topics:

Overview
Partition Add Maintenance
Partition Maintenance Scripts

E.1 Overview

Database tables in the Oracle Adaptive Access Manager database are divided into the following categories:

Static partition tables
Transactional partition tables
Non-partitioned tables

Note:

All the tables contain the composite partition (RANGE, HASH). The Range partition is created using CREATE_TIME while the HASH key is defined based on application logic.

lists the Oracle Adaptive Access Manager (OAAM) partition tables. All the other tables are non-partitioned.

Table E-1 OAAM Database Partition Tables

Table Type	Frequency	Table Name
Static Partition	Monthly	V_USER_QA V_USER_QA_HIST
Transactional Partition	Monthly	VCRYPT_TRACKER_NODE_HISTORY VCRYPT_TRACKER_USERNODE_LOGS VCRYPT_TRACKER_NODE VT_USER_DEVICE_MAP V_MONITOR_DATA VT_SESSION_ACTION_MAP VT_ENTITY_ONE VT_ENTITY_ONE_PROFILE VT_USER_ENTITY1_MAP VT_ENT_TRX_MAP VT_TRX_DATA VT_TRX_LOGS
Transactional Partition	Weekly	VR_POLICYSET_LOGS VR_POLICY_LOGS VR_RULE_LOGS VR_MODULE_LOGS

Table Type

Frequency

Table Name

Static Partition

Monthly

V_USER_QA

V_USER_QA_HIST

Transactional Partition

Monthly

VCRYPT_TRACKER_NODE_HISTORY

VCRYPT_TRACKER_USERNODE_LOGS

VCRYPT_TRACKER_NODE

VT_USER_DEVICE_MAP

V_MONITOR_DATA

VT_SESSION_ACTION_MAP

VT_ENTITY_ONE

VT_ENTITY_ONE_PROFILE

VT_USER_ENTITY1_MAP

VT_ENT_TRX_MAP

VT_TRX_DATA

VT_TRX_LOGS

Transactional Partition

Weekly

VR_POLICYSET_LOGS

VR_POLICY_LOGS

VR_RULE_LOGS

VR_MODULE_LOGS

E.2 Partition Add Maintenance

After the initial Oracle Adaptive Access Manager repository setup, the following stored procedures are set up as dbms_jobs to maintain the partitions on a regular basis:

Sp_Oaam_Add_Monthly_Partition
Sp_Oaam_Add_Weekly_Partition

E.2.1 Sp_Oaam_Add_Monthly_Partition

This stored procedure adds partitions for tables with the monthly frequency.

The script runs at the end of each month to create partitions for the following month. To simultaneously add partitions for subsequent months, the partitions are added based on the partition of the previous month.

If this stored procedure fails to exceute (if your monthly partition is missing), you may see database errors, "ORA-14400 and ORA-14401," forcing the Oracle Adaptive Access Manager application to stop.

E.2.2 Sp_Oaam_Add_Weekly_Partition

This stored procedure adds partitions for tables with the weekly frequency.

The script runs at the end of each week to create partitions for the following week. To simultaneously add partitions for subsequent weeks, the partitions are added based on the partition of the previous week.

If this stored procedure fails to exceute (if your weekly partition is missing), you may see database errors, "ORA-14400 and ORA-14401," forcing the Oracle Adaptive Access Manager application to stop.

E.3 Partition Maintenance Scripts

After the initial Oracle Adaptive Access Manager repository setup, use the following scripts with purging or archiving maintenance scripts to maintain the partitions on a regular basis:

drop_monthly_partition_tables.sql
drop_weekly_partition_tables.sql
add_monthly_partition_tables.sql
add_weekly_partition_tables.sql

The above mentioned scripts are located at <IDM_ORACLE_HOME>\oaam\oaam_db_maint_scripts\oaam_db_partition_maint_scripts

Note:

You do not have to execute partition add scripts. You should only use them to create partitions manually because other automated dbms_jobs create partitions at regular intervals.

E.3.1 drop_monthly_partition_tables.sql

You can use this script to drop partitions for tables with the monthly frequency. You should run this script at the end of each month to drop partitions older than sixth months, based on the requirements of the Oracle Adaptive Access Manager application. Note that these tables will have six partitions at a given time.

E.3.2 drop_weekly_partition_tables.sql

You can use this script to drop partitions for tables with the weekly frequency. You should run this script either at the end of every fourteenth day or at the end of third week from the day the Oracle database was created to the dropping of partitions older than two weeks, based on the requirements of the Oracle Adaptive Access Manager application.

E.3.3 add_monthly_partition_tables.sql

You can use this script to add partitions for tables with the monthly frequency. You should run this script at the end of each month to create partitions for the following month. To add partitions for subsequent months at the same time, run this script multiple times. When you run the script multiple times, partitions are added based on the previous month's partition.

E.3.4 add_weekly_partition_tables.sql

You can use this script to add partitions for tables with the weekly frequency. You should run this script at the end of each month to create partitions for the following week. To add partitions for subsequent weeks at the same time, run this script multiple times. When you run the script multiple times, partitions are added based on the previous week's partition.