14.3.3 Running Partition Maintenance Scripts

As described in Partition Maintenance, when a partitioned OAA database is used, a number of partitions are created by database jobs for some tables.

After completing purging and archiving tasks, administrators can decide which partitions are no longer required. These partitions can then be dropped.

A list of partitions can be found my logging into the database as the OAA schema user and running:

SQL> select * from user_tab_partitions

Partitions that are no longer required can be dropped for those tables using the SP_OAA_DROP_PARTITION procedure. To drop partitions:

1. Login to the database as the OAA schema user.
2. Run the following commant to set the NLS_DATE_FORMAT correctly:

   ALTER Session SET NLS_DATE_FORMAT='YYYY-MM-DD';

3. Execute the procedure as follows:

   SQL> execute SP_OAA_DROP_PARTITION(<table_name>,<p_days>) 

   where:

   • <table_name> specifies for which table the partition needs to be dropped
   • <p_days> specifies the number of days of data you wish to retain. For example, if you specifiy p_days as 180, then all partitions for that table where the data is older than 180 days, will be dropped.