oracle删除子分区限定值,删除分区测试(Oracle)

--删除分区测试

-- Created on 2014-10-08 by LENOVO

declare

-- Local variables here

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,

'删除 T_UNIT_WORKINFO 分区错误',

SYSDATE);

COMMIT;

NULL;

END;

END;

END LOOP;

end;

注意：

只有使用本地方式创建的分区才可以这样删除，否则会引起索引失败。

建表时按如下方式建立：

create table T_HISTORY_DARK

(

HISTORYID         NUMBER(10) not null,

UNIT_ID           NUMBER(10) 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(10) default 0,

ISHANDLE          NUMBER(10) 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 pctfree 5;