我装了Oracle 10,有一张数据表,基本每秒钟都朝里面塞数据。现在没用几天就把硬盘给塞满了。我用truncate命令也不能减少ora数据文件大小。应如何能自动减少数据,比如:超过100万条就自动删除10万条数据。自动减少ora数据文件大小。请详细指出。谢谢!
前段时间考虑过用存储程写数据分区。以下显示。按时间来删,好像没用。大家有没有更好的办法!
CREATE OR REPLACE PROCEDURE p_watch_timerauto_partitions AS
/*
本存储过程将处理当前用户的所有分区表
当且仅当满足下列条件,本存储过程才可以执行
1.所有分区表的分区命名规则 p200701 p+yyyymm
2,分区按日期大小分区,规格为一月 eg p200701 的条件为 :
less to_date('2007-02-01 00:00:00','YYYY-MM-DD hh24:mm:ss')
3,本存储过程依托JOB执行,执行周期为每天一次,最好是每天零点
*/
CURSOR max_parts IS --查询每个分区表的当前最大的分区名称
SELECT table_name, MAX((substr(partition_name, 2))) AS part
FROM user_tab_partitions
GROUP BY table_name ;
CURSOR old_parts IS --查询分区名称(yyyymm)早于当前月份3月的所有分区 并且分区名称like T_REAL_WATCH(本系统表空间名称)
SELECT table_name, substr(partition_name, 2) AS part_month
FROM user_tab_partitions
WHERE substr(partition_name, 2) <=
to_char(add_months(SYSDATE, -2), 'yyyymm');
v_tbname VARCHAR2(32); -- 分区表名称
v_ptname VARCHAR2(32); -- 分区名称 eg p200705 p+YYYYmm
v_sql VARCHAR2(3999);
v_today DATE;
BEGIN
/*
step 1 新建分区
*/
--1 判断当前所有分区表的最晚分区是否晚于当前月,
--1.0若小于当前月(问题严重咯 :( ,添加3个月的分区)
--1.1若等于当前月,添加下月,下下月的分区
--1.2若晚一个月,添加下下月的分区
--1.3晚两月说明已经建好了下月,下下月的分区咯,不用建分区了
FOR i IN max_parts LOOP
v_tbname := i.table_name;
v_ptname := i.part;
IF (to_char(SYSDATE, 'yyyymm') > v_ptname)
THEN
--1.0
v_today := SYSDATE;
v_sql := ' alter table ' || v_tbname || ' add partition ' || 'P' ||
to_char(v_today, 'yyyymm') ||
' values less than (to_date(' || '''' ||
to_char(add_months(v_today, 1), 'yyyy-mm') ||
'-01 00:00:01' || '''' || ',''' || 'yyyy-mm-dd hh24:mi:ss' ||
''')' || ')';
--dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql; --添加本月的分区
v_today := add_months(SYSDATE, 1);
v_sql := ' alter table ' || v_tbname || ' add partition ' || 'P' ||
to_char(v_today, 'yyyymm') ||
' values less than (to_date(' || '''' ||
to_char(add_months(v_today, 1), 'yyyy-mm') ||
'-01 00:00:01' || '''' || ',''' || 'yyyy-mm-dd hh24:mi:ss' ||
''')' || ')';
--dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql; --添加下月的分区
v_today := add_months(SYSDATE, 2);
v_sql := ' alter table ' || v_tbname || ' add partition ' || 'P' ||
to_char(v_today, 'yyyymm') ||
' values less than (to_date(' || '''' ||
to_char(add_months(v_today, 1), 'yyyy-mm') ||
'-01 00:00:01' || '''' || ',''' || 'yyyy-mm-dd hh24:mi:ss' ||
''')' || ')';
--dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql; --添加下下月的分区
ELSIF (to_char(SYSDATE, 'yyyymm') = v_ptname)
THEN
--有当前月
v_today := add_months(SYSDATE, 1);
v_sql := ' alter table ' || v_tbname || ' add partition ' || 'P' ||
to_char(v_today, 'yyyymm') ||
' values less than (to_date(' || '''' ||
to_char(add_months(v_today, 1), 'yyyy-mm') ||
'-01 00:00:01' || '''' || ',''' || 'yyyy-mm-dd hh24:mi:ss' ||
''')' || ')';
dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql; --添加下月的分区
v_today := add_months(SYSDATE, 2);
v_sql := ' alter table ' || v_tbname || ' add partition ' || 'P' ||
to_char(v_today, 'yyyymm') ||
' values less than (to_date(' || '''' ||
to_char(add_months(v_today, 1), 'yyyy-mm') ||
'-01 00:00:01' || '''' || ',''' || 'yyyy-mm-dd hh24:mi:ss' ||
''')' || ')';
--dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql; --添加下下月的分区
ELSIF (to_char(add_months(SYSDATE, 1), 'yyyymm') = v_ptname)
THEN
--有下月
v_today := add_months(SYSDATE, 2);
v_sql := ' alter table ' || v_tbname || ' add partition ' || 'P' ||
to_char(v_today, 'yyyymm') ||
' values less than (to_date(' || '''' ||
to_char(add_months(v_today, 1), 'yyyy-mm') ||
'-01 00:00:01' || '''' || ',''' || 'yyyy-mm-dd hh24:mi:ss' ||
''')' || ')';
dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql; --添加下下月的分区
ELSIF (to_char(add_months(SYSDATE, 2), 'yyyymm') = v_ptname)
THEN
--有下下月
dbms_output.put_line('已经加够了哦');
ELSE
dbms_output.put_line('这个程序出问题咯');
END IF;
END LOOP;
/*
step 2 删除早于当前月3个月的所有分区数据
*/
FOR i IN old_parts LOOP
v_tbname := i.table_name;
v_ptname := i.part_month;
v_sql := ' alter table ' || v_tbname || ' drop partition ' || 'P' ||
v_ptname || ' update indexes';
--dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql;
END LOOP;
/*解决删除分区表后回收站中产生的异常表问题*/
v_sql := 'PURGE recyclebin';
EXECUTE IMMEDIATE v_sql; v_sql := 'ALTER TRIGGER DTV.TRI_TS_290ERROR_DETAIL COMPILE';
--dbms_output.put_line('aa');
EXECUTE IMMEDIATE v_sql;
v_sql := 'ALTER TRIGGER DTV.TRI_T_REAL_CHANNELDATA COMPILE';
--dbms_output.put_line('bb');
EXECUTE IMMEDIATE v_sql;
v_sql := 'ALTER TRIGGER DTV.TRI_T_REAL_DMI_INFO COMPILE';
--dbms_output.put_line('cc');
EXECUTE IMMEDIATE v_sql;
END;
前段时间考虑过用存储程写数据分区。以下显示。按时间来删,好像没用。大家有没有更好的办法!
CREATE OR REPLACE PROCEDURE p_watch_timerauto_partitions AS
/*
本存储过程将处理当前用户的所有分区表
当且仅当满足下列条件,本存储过程才可以执行
1.所有分区表的分区命名规则 p200701 p+yyyymm
2,分区按日期大小分区,规格为一月 eg p200701 的条件为 :
less to_date('2007-02-01 00:00:00','YYYY-MM-DD hh24:mm:ss')
3,本存储过程依托JOB执行,执行周期为每天一次,最好是每天零点
*/
CURSOR max_parts IS --查询每个分区表的当前最大的分区名称
SELECT table_name, MAX((substr(partition_name, 2))) AS part
FROM user_tab_partitions
GROUP BY table_name ;
CURSOR old_parts IS --查询分区名称(yyyymm)早于当前月份3月的所有分区 并且分区名称like T_REAL_WATCH(本系统表空间名称)
SELECT table_name, substr(partition_name, 2) AS part_month
FROM user_tab_partitions
WHERE substr(partition_name, 2) <=
to_char(add_months(SYSDATE, -2), 'yyyymm');
v_tbname VARCHAR2(32); -- 分区表名称
v_ptname VARCHAR2(32); -- 分区名称 eg p200705 p+YYYYmm
v_sql VARCHAR2(3999);
v_today DATE;
BEGIN
/*
step 1 新建分区
*/
--1 判断当前所有分区表的最晚分区是否晚于当前月,
--1.0若小于当前月(问题严重咯 :( ,添加3个月的分区)
--1.1若等于当前月,添加下月,下下月的分区
--1.2若晚一个月,添加下下月的分区
--1.3晚两月说明已经建好了下月,下下月的分区咯,不用建分区了
FOR i IN max_parts LOOP
v_tbname := i.table_name;
v_ptname := i.part;
IF (to_char(SYSDATE, 'yyyymm') > v_ptname)
THEN
--1.0
v_today := SYSDATE;
v_sql := ' alter table ' || v_tbname || ' add partition ' || 'P' ||
to_char(v_today, 'yyyymm') ||
' values less than (to_date(' || '''' ||
to_char(add_months(v_today, 1), 'yyyy-mm') ||
'-01 00:00:01' || '''' || ',''' || 'yyyy-mm-dd hh24:mi:ss' ||
''')' || ')';
--dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql; --添加本月的分区
v_today := add_months(SYSDATE, 1);
v_sql := ' alter table ' || v_tbname || ' add partition ' || 'P' ||
to_char(v_today, 'yyyymm') ||
' values less than (to_date(' || '''' ||
to_char(add_months(v_today, 1), 'yyyy-mm') ||
'-01 00:00:01' || '''' || ',''' || 'yyyy-mm-dd hh24:mi:ss' ||
''')' || ')';
--dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql; --添加下月的分区
v_today := add_months(SYSDATE, 2);
v_sql := ' alter table ' || v_tbname || ' add partition ' || 'P' ||
to_char(v_today, 'yyyymm') ||
' values less than (to_date(' || '''' ||
to_char(add_months(v_today, 1), 'yyyy-mm') ||
'-01 00:00:01' || '''' || ',''' || 'yyyy-mm-dd hh24:mi:ss' ||
''')' || ')';
--dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql; --添加下下月的分区
ELSIF (to_char(SYSDATE, 'yyyymm') = v_ptname)
THEN
--有当前月
v_today := add_months(SYSDATE, 1);
v_sql := ' alter table ' || v_tbname || ' add partition ' || 'P' ||
to_char(v_today, 'yyyymm') ||
' values less than (to_date(' || '''' ||
to_char(add_months(v_today, 1), 'yyyy-mm') ||
'-01 00:00:01' || '''' || ',''' || 'yyyy-mm-dd hh24:mi:ss' ||
''')' || ')';
dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql; --添加下月的分区
v_today := add_months(SYSDATE, 2);
v_sql := ' alter table ' || v_tbname || ' add partition ' || 'P' ||
to_char(v_today, 'yyyymm') ||
' values less than (to_date(' || '''' ||
to_char(add_months(v_today, 1), 'yyyy-mm') ||
'-01 00:00:01' || '''' || ',''' || 'yyyy-mm-dd hh24:mi:ss' ||
''')' || ')';
--dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql; --添加下下月的分区
ELSIF (to_char(add_months(SYSDATE, 1), 'yyyymm') = v_ptname)
THEN
--有下月
v_today := add_months(SYSDATE, 2);
v_sql := ' alter table ' || v_tbname || ' add partition ' || 'P' ||
to_char(v_today, 'yyyymm') ||
' values less than (to_date(' || '''' ||
to_char(add_months(v_today, 1), 'yyyy-mm') ||
'-01 00:00:01' || '''' || ',''' || 'yyyy-mm-dd hh24:mi:ss' ||
''')' || ')';
dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql; --添加下下月的分区
ELSIF (to_char(add_months(SYSDATE, 2), 'yyyymm') = v_ptname)
THEN
--有下下月
dbms_output.put_line('已经加够了哦');
ELSE
dbms_output.put_line('这个程序出问题咯');
END IF;
END LOOP;
/*
step 2 删除早于当前月3个月的所有分区数据
*/
FOR i IN old_parts LOOP
v_tbname := i.table_name;
v_ptname := i.part_month;
v_sql := ' alter table ' || v_tbname || ' drop partition ' || 'P' ||
v_ptname || ' update indexes';
--dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql;
END LOOP;
/*解决删除分区表后回收站中产生的异常表问题*/
v_sql := 'PURGE recyclebin';
EXECUTE IMMEDIATE v_sql; v_sql := 'ALTER TRIGGER DTV.TRI_TS_290ERROR_DETAIL COMPILE';
--dbms_output.put_line('aa');
EXECUTE IMMEDIATE v_sql;
v_sql := 'ALTER TRIGGER DTV.TRI_T_REAL_CHANNELDATA COMPILE';
--dbms_output.put_line('bb');
EXECUTE IMMEDIATE v_sql;
v_sql := 'ALTER TRIGGER DTV.TRI_T_REAL_DMI_INFO COMPILE';
--dbms_output.put_line('cc');
EXECUTE IMMEDIATE v_sql;
END;
create or replace trigger tg_a
after insert
on tt
declare
v_count number;
begin
select count(1) into v_count from tt;
if v_count>1000000 then
delete from tt where rownum<=100000;
end if;
end tg_a;
如果这样的话,设置行级触发器,当新增的id为10W的整数倍时触发,删除10W条记录
这样效率应该更高
alter database datafile 'yourname' resize [100m];
收缩后的大小需要查询出来,如果设置过小,执行也会不成功.