删除分区测试(Oracle)

最新推荐文章于 2025-04-07 00:00:00 发布

冷月宫主

最新推荐文章于 2025-04-07 00:00:00 发布

阅读量659

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/e_wsq/article/details/39957855

Oracle 专栏收录该内容

538 篇文章

订阅专栏

本文介绍了一种批量删除Oracle数据库中历史分区数据的方法，适用于本地方式创建的分区，避免索引失效的问题。通过示例代码演示了如何实现这一操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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