17,377
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure p_partition_truncate
(
table_name in varchar2, --表名称
dtime in varchar2 --起始日期
)
is
v_sql varchar2(1000);
begin
v_sql := 'alter table '||table_name||' truncate partition '||'P'||dtime||' update indexes';
execute immediate v_sql;
exception
when others then
raise;
end;
CREATE OR REPLACE PROCEDURE P_PARTITION_TRUNCATE
(
TABLE_NAME IN VARCHAR2, --表名称
DTIME IN VARCHAR2, --起始日期
TIME_COL_NAME IN VARCHAR2 --日期列名称
) IS
V_SQL VARCHAR2(1000);
V_ROWID ROWID;
V_SUBOBJECT_NAME USER_OBJECTS.SUBOBJECT_NAME%TYPE;
BEGIN
V_SQL := 'select max(rowid) from ' || TABLE_NAME || ' where ' ||
TIME_COL_NAME || '=''' || DTIME || '''';
EXECUTE IMMEDIATE V_SQL
INTO V_ROWID;
SELECT MAX(SUBOBJECT_NAME)
INTO V_SUBOBJECT_NAME
FROM USER_OBJECTS T
WHERE T.DATA_OBJECT_ID IN (SELECT DBMS_ROWID.ROWID_OBJECT(V_ROWID)
FROM DUAL);
V_SQL := 'alter table ' || TABLE_NAME || ' truncate partition ' ||
V_SUBOBJECT_NAME || ' update indexes';
--DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
不知道分区名的情况下,可以通过rowid来确认所属分区的, 代码用的日期是字符串型的, 如果是其他类型,稍微修改一下即可
create or replace procedure p_partition_truncate
(
table_name in varchar2, --表名称
dtime in varchar2 --起始日期
)
is
v_sql varchar2(1000);
begin
v_sql := 'alter table '||table_name||' truncate partition '||'P'||dtime||' update indexes';
exception
when others then
raise;
end;