Organizations are adopting the cloud as a standard and actively evaluating their database needs. Amazon Relational Database Service (Amazon RDS) for Oracle is a managed service that makes it easy to quickly create Oracle database instances, enabling you to migrate existing on-premises workloads to the cloud. Migration from on-premises Oracle Database to Amazon RDS for Oracle is quick because it doesn’t involve code conversion.
Oracle Enterprise Edition (Oracle EE) has become the standard adopted by many organizations. However, if you do a more in-depth database assessment, you may find that not every application needs all the features of Oracle EE, and you may be overpaying.
You can significantly reduce your Oracle commercial license usage by switching to Oracle Database Standard Edition (Oracle SE), which is even easier with the Amazon RDS for Oracle License Included (LI) option. Applications with no or minimum Oracle EE features usage are excellent candidates for migrating to Oracle SE.
Oracle table partitioning is a commonly used feature to manage large tables and improve query performance. The Oracle partitioning option is an extra cost option only available in the Enterprise Edition.
This post is a continuation Implementing table partitioning in Oracle Standard Edition: Part 1, which explains how to configure partitioning in Oracle SE. In this post, we demonstrate techniques of automating date range partition management in Oracle SE. The code and use cases related to this post are available in the “automate-table-partitioning-in-oracle-standard-edition” GitHub repository.
The following architecture diagram details the solution. It explains the relationship between the PARTITION_META_DATA, PARTITION_TABLE_DATA, and PARTITION_LOG metadata tables and provides details about the MAIN_PROCESS package and its sub-components MANAGE_PARTITON and SETUP_PARTITON.
The solution uses three metadata control tables to manage table partitioning. In the following sections, we explain the purpose of each table and column.
The PARTITION_META_DATA table contains information about the main partition table, view, partitioning type (daily, weekly, monthly, and yearly) along with flags to archive the table and log. The following table describes each column.
Auto-generated unique identifier and primary key of the table.
Schema owner where the partition main table exists and partitioned tables are created.
Name of the main partition table.
Shorted name of the main partition table. This short name should be used in all constraints and indexes created on the main partition table.
Name of the view created to create logical partitioning.
Column that we use for partitioning in the partition table, for example, REGISTRATION_DATE.
Primary key of the partition table.
Type of partition: Y = yearly, M = monthly, W = weekly, D = daily.
How many future partition tables to create (default three).
How many old partition tables to retain (default three)
Flag to run automation if N automation doesn’t process this record (default Y).
Flag indicates whether to drop old partitions that are older than partitions to be retained. The default is N. If Y, the old partition tables are dropped; if N, the tables are removed from the view.
Flag indicates to log all steps of automation process into PARTITION_LOG. The default is Y; if N, only errors are logged.
The PARTITION_TABLE_DATA table stores the information of all partition tables managed by the automation process. The following table describes each column.
Partition table identifier
Name of the partition table
Date corresponding to partition table
Date when the partition table is created (running partition automation)
Indicates the state of partition table (A = active partition, D = deleted partition)
The PARTITION_LOG table is for logging errors. If a flag is enabled in the metadata table, all activities are logged into this table, which is required for debugging in case of any issues. The following table summarizes the column details.
Auto-generated unique identifier
DEBUG indicates debug log type; ERROR indicates error log type
Partition table identifier
Name of the object (such as partition table name or view name)
Log detail information
Date when log entry is created
Along with metadata tables, you need to use the MANAGE_PARTITON package, which includes the SETUP_PARTITIONS and MAIN_PROCESS procedures to automate the partition management.
The SETUP_PARTITIONS procedure performs the initial setup activities required for automating the partitioning. This should be called only once for a partition table. The procedure performs the following tasks:
Check if a given table is already part of partition management. If yes, it raises the error.
Create an entry in the partition metadata table.
Create a default table <main_table>_DEFAULT. This table is used as an overflow table to store records that don’t fit into any partitions.
Create partition tables from the given start date (parameter p_start_date)
Create a view with the given name (parameter p_view_name) based on all required partition tables.
Create INSERT, UPDATE, and DELETE triggers.
Grant all privileges similar to the main partition table.
The MAIN_PROCESS procedure is for managing partitions, namely to create future partitions and delete older partitions. This procedure must be run through a scheduled or on-demand job during a maintenance window or low-activity time. However, to maintain full control, you can run the procedure manually during the maintenance window or low-activity time. The following activities are performed:
Get information of all partition tables for which the AUTO_MANAGE_PARTITIONS flag is set to Y.
For each of these partitions, get the recent partition table date.
Based on the partition type (Y, M, W, or D) and NUM_PRECREATE_PARTITIONS, identify new partition tables to be created.
Get all the privileges on the partition main table.
Create new partition tables.
Disable old partitions that are older than partitions to be retained.
Recreate the view with new partition tables.
Drop old partition tables if DROP_OLD_PARTITIONS is set to Y.
The MANAGE_PARTITION package also includes the following private procedures:
WRITE_TO_LOG – Logs errors during the process. Also logs the events during the process as debug if ENABLE_LOGGING is set to Y.
DISABLE_OLD_PARTITIONS – Updates the status to D to all old partition tables that are older than partitions to be retained.
DROP_OLD_PARTITIONS – Drops old partition tables with status D and DROP_OLD_PARTITIONS flag is set to Y. The status is updated with D by the function DISABLE_OLD_PARTITIONS for partitions that are older than partitions to be retained.
RECREATE_VIEW – Recreates the view to include new partition tables and exclude old partition tables. It performs the following tasks:
Get all privileges on the view.
Get synonyms on the view.
Recreate the view to include new partition tables and exclude old partition tables.
Grant all privileges on the view.
Recreate INSERT, UPDATE, and DELETE triggers on the view.
Recreate synonyms on the view.
CREATE_INS_TRG – Creates INSTEAD OF INSERT trigger on the view.
CREATE_UPD_TRG – Creates INSTEAD OF UPDATE trigger on the view.
CREATE_DEL_TRG – Creates INSTEAD OF DELETE trigger on the view.
GRANT_OBJECT_PRIVILEGES – Grants all the privileges to the given object.
CREATE_SYNONYMS – Recreates all the synonyms using the data in the p_object_synonyms collection.
The MANAGE_PARTITION package also includes the following functions:
GET_SYNONYMS – Retrieves all the synonyms on the given object and returns the same as the collection.
GET_OBJECT_PRIVILEGES – Retrieves all the privileges on the given object and returns the same as the collection.
This post assumes that:
You have an AWS account to test the use case.
You have Amazon RDS for Oracle Standard Edition Two provisioned in your AWS account.
You have Oracle client with SQL*Plus installed and have connectivity between client and RDS instance.
You have hands-on experience with SQL and PL/SQL programming language.
Partition implementation steps
The following diagram shows the flow of the events to implement automation of table partitioning:
The flow includes the following steps:
Create the partition table object schema.
Install the partition management automation objects.
Create the partition table.
Add an entry for automation.
Run the automation packages, which create the view, partition tables, indexes, and constraints. It uses DML INSTEAD OF triggers to update the metadata information. We must also schedule the automation to ensure we keep adding new partitions as needed.
Create the partition service account user
To demonstrate the end-to-end capability of the partition automation, we create a user with the required permissions. We use PARTSRVC (partition service account) as the schema in which we create a main table called STUDENT_YEAR and its dependent objects; metadata tables PARTITION_META_DATA, PARTITION_TABLE_DATA and PARTITION_LOG; triggers to manage the DML transactions; and maintenance code packages.
Log in to the database as the admin account and use the following code to create the PARTSRVC user and grant the required permissions:
Create the main partition table, primary key, and index
Next, we create the table to partition. This table is referred as the main partition table, and doesn’t contain any data. We use it as reference to create the partition tables. This table should have all the necessary constraints, indexes, and privileges that are replicated to all partition tables, and also the view that is created for managing the partitions.
Log in as the PARTSRVC user and use following code to create the STUDENT_YEAR main partition table, which we use to simulate the solution by creating yearly partitions:
Create the primary key, which ensures the uniqueness of the records:
Create an index:
Create the metadata tables
Use the following code to create the PARTITION_META_DATA, PARTITION_TABLE_DATA, and PARTITION_LOG metadata tables (alternatively, you can use partition_mgmt_tables.sql to create all the metadata tables):
The script runs the following commands.
Create the automation package
Run the automated procedure
Now we run the manage_partition.setup_partition procedure. The procedure uses the main table and input values and creates the partition tables, a view by combining all the partition tables, and the DML triggers to manage data. We pass the following values in the syntax:
PARTSRVC – Owner of the partition table
STUDENT_YEAR – Main partition table
STDTY – Unique partition name for metadata information
REGISTRATION_DATE – Name of the partition column
STUDENT_ID – Name of the primary key column
STUDENT_YEAR_VW – Name of the view created by combining all the partition tables
Y –The yearly partition (the types of partition supported are Y, M, W, and D)
2020-01-01 – Date from which partitions are created
3 – Number of future partition tables to create
3 – How many old partitions we can retain
Y – Flag to automatically manage partitions (if you want to disable partition management on the table, set this to N)
N – Flag to indicate if old partitions are to be dropped
Y – Flag for logging
Run the following code:
The preceding command calls the controlling function MANAGE_PARTITIONS.main_process, which uses the main table STUDENT_YEAR and creates YEARLY partition tables called STUDENT_YEAR_2021, STUDENT_YEAR_2022, STUDENT_YEAR_2023, and STUDENT_YEAR_2024 for data from years 2021–2024, respectively, and STUDENT_YEAR_DEFAULT for the data for any other year data and its dependent objects. It creates STUDENT_YEAR_VW and also creates DML management triggers TRG_STUDENT_YEAR_VW_INS, TRG_STUDENT_YEAR_VW_UPD, and TRG_STUDENT_YEAR_VW_DEL to manage the data route to correct partition table. The procedure also creates an entry into the partition metadata tables.
The command is an example for YEARLY partition management. We can also create daily, weekly, and monthly partitions. The partition tables are created in following format based on the type of partition.
Daily – <partition_main_table>_YYYYMMDD
Weekly – <partition_main_table>_YYYYMMDD, where DD refers to Monday
Monthly – <partition_main_table>_YYYYMM
Yearly – <partition_main_table>_YYYY
The automation tracks the partition tables created using the PARTITION_TABLE_DATA table.
For the first run of the automation, no records exist in PARTITION_TABLE_DATA, so it creates the partition table with reference to the current date (SYSDATE).
The following code confirms that the partition tables are empty:
The DML example in the next section shows how the INSTEAD OF triggers store the data in appropriate partition tables.
Validate the triggers
Now we can validate that the triggers are working as expected.
The following code inserts the records in each partition table and also one 2025 record in the default partition (because we don’t have a 2025 partition table):
Query the view and partition tables to confirm that the data is inserted into the correct partition tables and the view can select data from all the tables:
Use following code to delete a record to confirm the delete trigger functionality. For this post, we delete the 2023101 student_id record:
Use the following code to update a record to confirm the update trigger functionality. We update the dataset for student_id 2023102:
After the partition logic is implemented and in use, we can use the following command to manage the partitioning process. The procedure uses the PARTITION_META_DATA table to add new and delete old partition tables:
A unique index or primary key isn’t possible at the global table level, but we can implement them at the local table level.
If you want to implement an auditing trigger at the table level, this feature isn’t handled by the automation. We recommend studying the source code and customizing your implementation.
In the preceding example, we used REGISTRATION_DATE as our key column to define the YEARLY partitions. You can’t update the key column that is REGISTRATION_DATE data. For example, in the following code, we try to modify the REGISTRATION_DATE for STUDENT_ID 2024101, which resides in the default partition table.
In this post, we described how we can use an automated process to manage a table as partitions in Oracle SE. The process improves partition management efficiency and helps avoid human error. The process provides the automation required for functionality to create and manage partitions in Oracle SE.
We’re looking forward to you trying this approach in your environment. Please reach out with questions or feature requests via the comments.
About the Authors
Srinivas Potlachervoois a Lead Database Consultant with the Professional Services team at Amazon Web Services. He works as database migration specialist to help AWS customers to move their on-premises database environment to AWS cloud database solutions.
Bhavesh Rathod is a Senior Database Consultant with the Professional Services team at Amazon Web Services. He works as database migration specialist to help Amazon customers to move their on-premises database environment to AWS cloud database solutions.
Samujjwal Roy is a Principal DB Consultant with the Professional Services team at Amazon Web Services. He has been with Amazon for 17+ years and has led migration projects for internal and external Amazon customers to move their on-premises database environment to AWS Cloud database solutions.
Read MoreAWS Database Blog