Delete partition test (Oracle) ____oracle

--Delete partition test
--Created on 2014-10-08 by LENOVO
Declare
--Local variables
L_begin_date DATE;
L_end_date DATE;
L_begin_time DATE;
L_sql VARCHAR2 (2000);
Begin
--Test statements here
L_begin_time: = l_begin_date;
For L_partname in (SELECT table_name, partition_name
From User_tab_partitions
WHERE to_date (SUBSTR (REPLACE (Partition_name, ' _ ', '),-6), ' Yy-mm-dd ' <
Add_months (TRUNC (sysdate, ' MONTH '),-12)
and table_name = ' t_history_dark ') LOOP
BEGIN
L_sql: = ' ALTER TABLE t_history_dark DROP PARTITION ' | |
L_partname. Partition_name;
EXECUTE IMMEDIATE L_sql;
EXCEPTION
When others THEN
BEGIN
INSERT into T_sql_debug_log
(sql_debug_log_id, Sql_debug_log, remarks, STAMP)
VALUES
(Snt_sql_debug_log. Nextval,
L_sql,
' Delete t_unit_workinfo partition error ',
Sysdate);
COMMIT;
NULL;
End;
End;
End LOOP;

End

Attention:

Only partitions created locally are allowed to be deleted, or they can cause indexing to fail.

The table is built as follows:

CREATE TABLE T_history_dark
(
Historyid number is not NULL,
UNIT_ID number is not NULL,
LON number (28,10),
LAT number (28,10),
COURSE Number (10),
SPEED number (12,6),
DIFF Number (4),
Loc_state Number (4),
RESP_ID Number (4),
Gpstime DATE,
STAMP DATE,
State VARCHAR2 (500),
Gpsmsg VARCHAR2 (126),
DEMO VARCHAR2 (1024),
Isalarm number (a) default 0,
Ishandle number (a) default 0,
Keypointno VARCHAR2 (20),
Referenceposition VARCHAR2 (255),
PhotoID number,
Photourl VARCHAR2 (255),
Smallphotourl VARCHAR2 (255),
LON1 number,
LAT1 number,
DISTANCE number,
CODE number,
TEMPERATURE1 number (5,1),
TEMPERATURE2 number (5,1),
VEHICLEKIND_ID number,
gate_id NVARCHAR2 (16),
TypeCode VARCHAR2 (1),
Postname NVARCHAR2 (20),
Fromplace NVARCHAR2 (50),
Toplace NVARCHAR2 (50),
Goodsname NVARCHAR2 (50),
Taskstamp DATE,
Oilpercent number,
OilChange number,
Oilstatus VARCHAR2 (200),
TEMPERATURE3 number (5,1),
TEMPERATURE4 number (5,1),
TEMPERATURE5 number (5,1),
TEMPERATURE6 number (5,1),
TEMPERATURE7 number (5,1),
TEMPERATURE8 number (5,1),
Elevation number,
Addition_info VARCHAR2 (255)
)
Partition by range (stamp)
(
Partition p_tbl_history_2012_11_20 values less than (To_date (' 2012-11-21 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' Nls_ Calendar=gregorian ')),
Partition p_tbl_history_2012_11_21 values less than (To_date (' 2012-11-22 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' Nls_ Calendar=gregorian ')),
Partition p_tbl_history_2012_11_22 values less than (To_date (' 2012-11-23 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' Nls_ Calendar=gregorian ')),
Partition p_tbl_history_2012_11_23 values less than (To_date (' 2012-11-24 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' Nls_ Calendar=gregorian ')),
Partition p_tbl_history_2012_11_24 values less than (To_date (' 2012-11-25 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' Nls_ Calendar=gregorian ')),
Partition p_tbl_history_2012_11_25 values less than (To_date (' 2012-11-26 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' Nls_ Calendar=gregorian ')),
Partition p_tbl_history_2012_11_26 values less than (To_date (' 2012-11-27 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' Nls_ Calendar=gregorian ')),
Partition p_tbl_history_2012_11_27 values less than (To_date (' 2012-11-28 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' Nls_ Calendar=gregorian ')),
Partition p_tbl_history_2012_11_28 values less than (To_date (' 2012-11-29 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' Nls_ Calendar=gregorian ')),
Partition p_tbl_history_2012_11_29 values less than (To_date (' 2012-11-30 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' Nls_ Calendar=gregorian ')),
Partition p_tbl_history_2012_11_30 values less than (To_date (' 2012-12-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' Nls_ Calendar=gregorian '))
) tablespace Gisap Pctfree 5;
Create INDEX Idx_history_dark on T_history_dark (unit_id) local tablespace gisapindex 5;