删除分区测试(Oracle)

来源：互联网发布：java项目有意思的案例编辑：程序博客网时间：2024/04/26 16:37

--删除分区测试
-- 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;

0 0