Oracle 11g automatically creates a partition (interval patition) after the scheduled partition Deletion

The TRUCK_GPS_TRANS table uses the interval patition function of Oracle11 to automatically create partitions by day. Now, you need to delete the partition data from the past 30 days on a regular basis to keep the business data in the table for only 30 days, because partitions are dynamically created and cannot obtain the partition name, you need to use the dictionary table user_objects. The stored procedure is:

Create or replace procedure PROC_DROP_PATITION_GPS_TRANS (beforeDays NUMBER
)
V_SqlExec VARCHAR2 (2000 );
V_err_num NUMBER;
V_err_msg VARCHAR2 (100 );

Cursor cursor_cpu_info_part is
Select t. SUBOBJECT_NAME partition_name
From user_objects t
Where object_name = upper ('truck _ GPS_TRANS ')
And t. OBJECT_TYPE = 'table partition'
And t. GENERATED = 'y' -- the first partition, that is, the first partition created during table creation, cannot be deleted. oracle Reports an error and automatically GENERATED partitions can be deleted.
And t. CREATED <sysdate-30; -- search for
Record_cpu_info_oldpart cursor_cpu_info_part % rowType;

BEGIN
Open cursor_cpu_info_part;
Loop
Fetch cursor_cpu_info_part
Into record_cpu_info_oldpart;
Exit when cursor_cpu_info_part % notfound;
-- Delete partitions in the TRUCK_GPS_TRANS table
V_SqlExec: = 'alter TABLE TRUCK_GPS_TRANS drop partition '|
Record_cpu_info_oldpart.partition_name;
Dbms_output.put_line ('delete TRUCK_GPS_TRANS table partition = '| v_SqlExec );
DBMS_Utility.Exec_DDL_Statement (v_SqlExec );
End loop;
Close cursor_cpu_info_part;

EXCEPTION
WHEN OTHERS THEN
V_err_num: = SQLCODE;
V_err_msg: = SUBSTR (SQLERRM, 1,100 );
Dbms_output.put_line ('mals _ NM_DROP_PARTITION_PROC execution exception, error code = '|
V_err_num | 'error description =' | v_err_msg );
END PROC_DROP_PATITION_GPS_TRANS;

Recommended reading:

How to install Oracle 11g on Linux

Detailed description of the installation process of Oracle 11g Database in Linux

How to install Oracle 11g R2 single-instance database on CentOS 5.6

To install Oracle Clusterware on an Oracle vmvm

Install Oracle 11 GB single-instance database on Linux under vmvm