数据量超大时,如果直接delete,会导致:
1.redo 很快满掉,然后checkpoint not compete, 等等日志切换
2.大事务致使 自动扩展的undo 表空间迅速增大,系统处于等等
固定大小的undo表空间,系统可能就hang注了。
alert 错误 ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
就会出现。
1.普通表
如果删除前需要导出,
create table xxx.xxx_hour160501
organization external
( type oracle_datapump
default directory tdwllog.tomcat_dir
location( 'expdp_xxxx_hour160501.dat' )
)
as
select * from xxxx.xxx_HOUR WHERE s_hour <'2016050100';
删除方法:(rowid方式)
BEGIN
FOR x IN(SELECT ROWID rd,ROWNUM rn FROM xxx_LOG
WHERE logdate<to_date('20161201','yyyymmdd')
--WHERE s_date<'20170301'
)
LOOP
DELETE FROM xxx_LOG WHERE ROWID = x.rd;
IF MOD(x.rn,1000)=0 THEN
COMMIT;
END IF;
END LOOP;
commit;
END;
如果,保留的数据较少,可以反过来做:
CREATE TABLE temp1 AS
SELECT * FROM xxxx_LOG WHERE logdate>=to_date('20161201','yyyymmdd');
TRUNCATE TABLE xxxx_LOG
INSERT INTO xxxx_LOG SELECT * FROM temp1;
DROP TABLE temp1 PURGE;
2.分区表
查询分区数据:
SELECT * FROM xxx_LOG PARTITION (part_201612);
导出:
expdp \'/ as sysdba\' TABLES=xxx.xxx_LOG:PART_201609 dumpfile=expdp_xxx_log_201609.dat DIRECTORY=mtudoulog_dir;
添加分区:
ALTER TABLE xxx.xxxx_LOG
SPLIT PARTITION PART_MAX
AT (TO_DATE('2016-02-01','YYYY-MM-DD'))
INTO (partition PART_201601 tablespace tdywdsdb , partition PART_MAX tablespace tdywdsdb );
删除分区:
ALTER TABLE xxx_LOG DROP PARTITION PART_201504;
</to_date('20161201','yyyymmdd')
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/807718/viewspace-2136438/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/807718/viewspace-2136438/