Oracle Delete分批提交

最新推荐文章于 2021-04-03 03:51:01 发布
最新推荐文章于 2021-04-03 03:51:01 发布
阅读量5.7k 收藏 5
点赞数
CC 4.0 BY-SA版权
分类专栏： Oracle DBA
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/dawn19900907/article/details/50246375
本文介绍了在Oracle数据库中进行大量数据删除时，如何通过分批提交避免undo表空间耗尽。同时讨论了DELETE操作对高水位线、查询效率及空间释放的影响，并分析了DELETE、UPDATE、INSERT三种DML操作对redo和undo日志产生的量。通过实验观察到redo和undo大小的变化规律。

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

1、Oracle Delete操作会产生redo log，undo log，即使delete 语句中加上nologging 参数，还是会记录日志的，这样保证了数据安全性，利于rollback。为了不使undo表空间被撑爆，可能需要分批提交，以下是分批提交语句：

declare
   cnt number(10):=0;
   i number(10):=0;
    begin
   select count(0) from scott.bigtable where id>1000000;
   for i in 1..trunc(cnt/50000)+1 loop
   delete from scott.bigtable where id >1000000 and rownum<=50000;
   commit;
   end loop;
  end;

/

2、Oracle Delete 操作不会降低高水位线，而每次查询还是会扫描高水位线下面所有的数据，这样查询效率不会因为数据delete掉之后，而提高查询效率，除非通过压缩表的空间来降低高水位线。

查询高水位线语句，其中blocks表示高水位线：

最低0.47元/天 解锁文章

200万优质内容无限畅学
【ORACLE】大数据快速更新（游标分批提交)
Ask Self，Ask Tomorrow
08-30 1965
--【避免回滚段过大】 --删除索引，最后恢复索引 --分批插入 DECLARE CURSOR c1 IS SELECT ROWID row_id FROM ts_batchcode WHERE dr=0 order by rowid; c_commit CONSTANT PLS_INTEGER := 10000; --CONSTANT
oracle delete 分批删除
06-30 1033
在使用delete语句删除数据时，数据库是要做日志记录的，以便将来可以恢复数据，可是我在删除上百万条数据时，十分缓慢甚至死机，请问有没有什么好方法? 这个是我平常用来批量删除数据，每500条数据提交一次。 DECL...
oracle分批删除
imlpf的专栏
04-25 1432
CREATE OR REPLACE procedure delBigTab --分批提交删除 (p_TableName in varchar2, --表名 p_Condition in varchar2, --条件 p_Count in varchar2 --每批提交的条数 ) as pragma autonomous_transaction; n_delete numbe
oracle 大批量删除数据分批commit
wrh_csdn的博客
06-13 8612
一切以代码说话，CREATE OR REPLACE PROCEDURE prc_del_big_rec(i_tablename IN VARCHAR2,                                            i_condition IN VARCHAR2,                                            i_count     ...
Oracle DML NOLOGGING
David Dai -- Focus on Oracle
06-29 8704
一． NOLOGGING 说明在对大表插入数据的时候，经常会用到nologging 选项。Nologging 并不是不产生redo，nologging  +  direct 只是不会对数据产生 redo（但依然有其他的redo）。同理 logging +  direct 下 undo 也是大大地减少， 减少的是 数据的undo ，这里强调的是 数据本身的undo ，就如同 redo的减少也一样，是 数据本身的 redo ，这和数据库是否产生  redo 和 undo 是不同的概念，比如空间分配的 redo
如何在Oracle中批量删除数据
辰风的专栏--Oracle/ClearCase管理员日记
10-07 9525
     在一个ORACLE数据库运行过程中，有时候会遇到要批量删除数据的情况，如一个保存历史数据的表中有大量的数据已经不需要保留，要将这部分数据删除。以下就这种情况可以采取的方法进行讨论。 1、使用TRUNCATE命令进行删除。 如果是整个表的数据都要删除的话，使用TRUNCATE TABLE命令是理想的选择。它删除了表中的所有数据，并且因为不写REDO LOG FILE，所以速度很快。删除
Oracle中大批量删除数据的方法
09-11
Oracle中大批量删除数据的方法 Oracle中大批量删除数据的方法是数据库管理员和开发者们经常面临的挑战之一。当数据库中的数据量非常大时，删除数据的操作可能会变得非常慢，甚至影响到整个数据库的性能。因此，...
ORACLE SQL-UPDATE、DELETE、INSERT优化和使用技巧分享
12-16
UPDATE 1、先备份数据（安全、提高性能）。2、分批更新，小批量提交，...DELETE 1、分批提交、减少锁表时间、减少回滚段压力。2、大批量数据删除加上rownum<1000> nologging;）2、使用/*+ append */ 暗示。3、绑定变
oracle批量删除的方法
jlds123的专栏
06-05 948
oracle删除大数据时会对undo造成很大压力，死锁也会经常产生，所以经常采取分批提交的方式。 下面的存储过程转自：http://www.eygle.com/archives/2005/02/ecioioaeoeeeioe.html create or replace procedure deleteTab (
【Oracle】分批删除大量数据
weixin_38538285的博客
11-21 1536
平时我们要删除某个表的数据时 -- 删除同一个证件号，状态为0和1的重复的记录，只保留其中一条 DECLARE CURSOR MYCURSOR IS SELECT * FROM (SELECT COUNT(1) CNT, MIN(ID) ID, A.CERT_ID FROM UNION_LOAN_USER_INFO A WHERE A.CHANNEL_CODE = '10001' AND
oracle删除表redo,oracle 的redo和undo 收藏
weixin_28875235的博客
04-03 461
在这里会介绍UNDO，REDO是如何产生的，对TRANSACTIONS的影响，以及他们之间如何协同工作的。 什么是REDO REDO记录transaction logs，分为online和archived。以恢复为目的。 比如，机器停电，那么在重起之后需要online redo logs去恢复系统到失败点。比如，磁盘坏了，需要用archived redo logs和on...
oracle undo redo的理解
xiongch_xionger的博客
01-09 762
一、oracle delete分批删除大量数据 1、delete 操作会产生redo log、undo log，这样是为了保证数据安全性，利于rollback。为防止undo表空间被撑爆，可以采用分批提交，代码如下： declare    v_cnt number :=0; begin   loop    delete from scoot.emp  where id>100   
关于oracle的NOLOGGING
wuhongze的专栏
04-09 718
可以采用NOLOGGING模式执行以下操作： 1 索引的创建和ALTER（重建）。 2 表的批量INSERT（通过/*+APPEND */提示使用“直接路径插入“。或采用SQL*Loader直接路径加载）。表数据不生成redo，但是 所有索引修改会生成redo，但是所有索引修改会生成redo（尽管表不生成日志，但这个表上的索引却会生成redo！）。 3 LOB操作（对大对象的更新不必生成日...
oracle千万级数据删除优化
qq_41662784的博客
09-03 8877
删除千万百万级别表 delete from 表名nologging where Score_Date = to_date('2018-09-03','yyyy-mm-dd') nologging 不生成日志 删除此表的所有索引，主外键。（注意：将生成主外键，索引的sql语句备份 删除数据，然后恢复主外键及索引。(备份的语句运行即可，美滋滋)      ...
nologging 对于delete update insert 是无效的
热门推荐
wll的专栏
12-16 1万+
最近发现系统的redo log 日志的压力还是比较大的，虽然已经把redo log 增加到了1.5G，但是在某些时候切换频率还是30s就切换了，日志的压力比较大，最后定为到系统中的一些全表delete，产生的日志比较大，有些delete是针对天来删除，针对天删除的只能delete，对全表的delete可以truncate，虽然对于某些中间的里临时表nologging，但是效果不大，最后发现，原来n
nologging对delete以及update效果不大
haiyang_tian20140616的专栏
11-12 5467
SQL> create table a (id int); Table created. SQL> begin for i in 1..10000 loop   2  insert into a select i from dual;   3  end loop;   4  commit;   5  end;   6  / PL/SQL procedure successfully
oracle delete 如何不产生redo日志
最新发布
03-26
<think>嗯，用户问的是Oracle的DELETE操作如何不产生redo日志。这个问题看起来有点技术性，可能用户是DBA或者开发人员，想要优化数据库性能，或者在进行数据清理时希望减少日志生成，比如测试环境的数据处理。首先，我需要回忆一下Oracle中redo日志的机制。 Oracle默认情况下，所有的数据修改操作（包括DELETE）都会生成redo日志，这是为了保证事务的持久性和恢复能力。不过，有些情况下用户可能希望减少redo日志的产生，比如大批量删除数据时，减少日志可以提升性能并节省存储空间。这时候，可以考虑一些特定的方法。 首先想到的是使用NOLOGGING选项。但NOLOGGING通常用于DDL操作，比如创建表或索引时，对于DML操作如DELETE是否有效呢？可能不是直接适用，但或许有其他方式。比如，使用直接路径插入或者特定的提示，但DELETE本身可能不支持这些。 然后，临时表可能是一个选择。临时表在会话或事务中产生的数据变更不会生成redo日志。用户可以将要删除的数据移动到临时表，再进行删除，或者利用临时表的特性来处理。不过，这可能涉及到数据迁移的步骤，步骤可能繁琐。 另外，使用自治事务可能也是一个思路，但自治事务本身并不会关闭redo日志，只是独立于主事务提交，所以可能不适用。 还有，禁用数据库的归档模式或者调整日志生成参数，但这样会影响整个数据库的恢复能力，显然不推荐，尤其是在生产环境中。 突然想到，Oracle的Direct Path操作可以减少redo，但DELETE通常走的是常规路径，所以可能不行。不过，如果使用某种替代方法，比如CTAS（Create Table As Select）来重建表，排除需要删除的数据，这样在NOLOGGING模式下可能减少日志。但这种方法需要重建表，可能不适合在线操作，并且需要停机时间。 另外，分区表可能有用，比如删除整个分区，使用ALTER TABLE ... DROP PARTITION，这种DDL操作在NOLOGGING设置下可能减少日志，但需要表是分区的，并且删除的是整个分区而不是部分数据。 还有一个方法是设置表为NOLOGGING，然后执行DELETE，但根据Oracle文档，NOLOGGING对DML的影响有限，可能只对某些特定操作有效，比如直接路径插入。DELETE可能仍然生成redo。 可能需要更深入的分析。例如，使用批量删除的方式，比如每次删除一定量的数据并定期提交，虽然不能避免redo，但可以分散日志生成的压力，但这并没有直接解决问题。 或者，使用PL/SQL的FORALL语句结合批处理，可能减少一些开销，但redo仍然会产生。 可能用户真正需要的是最小化redo日志，而不是完全避免。因为完全避免可能不可行，除非使用非常规手段，比如关闭数据库的日志功能，这显然不推荐，因为会导致数据无法恢复。 总结一下，可能的解决方案包括： 1. 使用临时表：将数据移到临时表，利用临时表不生成redo的特性进行处理。 2. 使用NOLOGGING模式：虽然对DML可能有限，但结合特定的操作如CTAS可能有效。 3. 分区表删除分区：DDL操作结合NOLOGGING可能减少日志。 4. 设置DISABLE LOGGING：但需要重启数据库，且影响所有操作，风险高。 5. 使用外部表或非日志记录方式导入导出数据。 需要验证这些方法的可行性，并指出潜在的风险，比如数据恢复问题，事务完整性等。另外，提醒用户在生产环境谨慎操作，避免数据丢失。</think>以下是如何在Oracle中执行DELETE操作时不生成Redo日志的几种方法及注意事项： --- ### 1. **使用`NOLOGGING`模式** - **原理**：将表设置为`NOLOGGING`模式，减少某些操作的日志记录。 - **步骤**： 1. 修改表属性： ```sql ALTER TABLE 表名 NOLOGGING; ``` 2. 执行`DELETE`： ```sql DELETE FROM 表名 WHERE 条件; ``` 3. 恢复表属性： ```sql ALTER TABLE 表名 LOGGING; ``` - **限制**： - `NOLOGGING`对常规DML（如`DELETE`）的Redo抑制有限，主要用于DDL（如`CREATE TABLE AS SELECT`）。 --- ### 2. **临时表（Temporary Table）** - **原理**：临时表的数据变更仅写入临时段，不生成Redo日志。 - **步骤**： 1. 创建临时表： ```sql CREATE GLOBAL TEMPORARY TABLE 临时表名 AS SELECT * FROM 原始表 WHERE 条件; ``` 2. 从原始表删除数据： ```sql DELETE FROM 原始表 WHERE 条件; ``` 3. 提交或会话结束后，临时表自动清空。 --- ### 3. **分区表删除分区** - **原理**：删除整个分区的操作属于DDL，结合`NOLOGGING`可大幅减少Redo。 - **步骤**： 1. 设计表为分区表。 2. 删除分区： ```sql ALTER TABLE 表名 DROP PARTITION 分区名 NOLOGGING; ``` --- ### 4. **直接路径操作（如`CTAS`重建表）** - **原理**：通过重建表跳过需删除的数据，结合`NOLOGGING`抑制Redo。 - **步骤**： 1. 创建新表（保留需保留的数据）： ```sql CREATE TABLE 新表 NOLOGGING AS SELECT * FROM 原表 WHERE 需保留的条件; ``` 2. 重命名或替换原表。 --- ### 5. **禁用数据库归档模式（高风险！）** - **原理**：在非归档模式下，Redo日志可被覆盖，但不推荐生产环境使用。 - **步骤**： ```sql SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE NOARCHIVELOG; ALTER DATABASE OPEN; ``` --- ### **注意事项** 1. **数据安全风险**： - 不生成Redo日志可能导致数据无法恢复，仅适用于临时数据或可容忍丢失的场景。 2. **性能与合规性**： - 生产环境中需评估业务连续性和合规要求。 3. **测试验证**： - 在测试环境验证方法可行性，避免影响生产。 --- ### 总结 - **推荐方案**：优先使用临时表或分区表删除。 - **谨慎操作**：`NOLOGGING`和`NOARCHIVELOG`需严格评估风险。 - **替代思路**：若允许少量Redo，可尝试批量提交（`COMMIT`分批）减少日志压力。 如需进一步优化，请提供具体场景（如数据量、表结构、环境类型）。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值