用游标删除符合条件的表 可以清空该数据库下所有表

本文介绍了一种方法,通过定义游标、查询并删除指定条件下的表,实现批量删除非系统管理表的过程。

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

删除所有表

  以用户test为例

  for example:

  declare

  cursor cur1 is select table_name from dba_tables where owner='TEST';

  begin

  for cur2 in cur1 loop

  execute immediate 'drop table test.'||cur2.table_name;

  end loop;

  end;



-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

在工作中,我遇到这样的一种事情,一个系统做完了,我想用原来的框架重新做另外的一个项目,这就涉及到删除除系统管理外的基他表,我的系统管理前缀是T_Sys,我就把查出要删除的表拼接成删除表字符串,然后用游标去一条条执行这些语句。

--定义游标

declare test_Cursor CURSOR for

select 'drop table '+ [name]+' ' as name from [sysobjects] where [type] = 'u' and name not like 'T_Sys%' order by [name]

--定义一个临时变量

declare @name varchar(80)

--打开游标

open test_Cursor

print 'Start'

--循环执行语句

while @@FETCH_STATUS=0

begin

--把下一条数据  取出来放到@name中

fetch next from test_Cursor into @name

--执行这条语句

EXECUTE( @name)

end

--关悼游标

close test_Cursor

--释放游标

DEALLOCATE test_Cursor
print 'End
<think>嗯,用户问的是Oracle数据库如何一次性删除一千万条数据。首先,我得确认用户的需求。删除大量数据的时候,直接执行DELETE语句可能会有问题,比如回滚段不够,锁时间长,影响性能,甚至可能让数据库挂起。所以得考虑更有效的方法。 首先想到的是分批次删除。一次性删一千万条的话,事务太大,容易导致undo空间不足,而且锁的时间长。用循环分批删除,比如每次删5000条,直到删完。但需要确保有合适的索引,否则每次的DELETE操作会全扫描,效率低下。同时,分批删除的时候记得加WHERE条件,避免全锁。 然后,可能要考虑使用TRUNCATE,但用户是有筛选条件的,TRUNCATE只能清空整个或者分区,所以如果数据符合某个分区,可以删除分区。但前提是已经做了分区,按条件对应的字段分区的话,比如按时间范围,这时候DROP PARTITION会很快。不过如果没分区的话,可能就不适用了。 另外,使用临时的方法。把不需要删除的数据插入到新,然后重命名。这种方法在数据量大的时候比较有效,因为INSERT AS SELECT比逐条删除快,而且产生的redo和undo日志少。但需要考虑空间问题,因为新需要足够的存储空间,并且在切换过程中可能会有短暂的服务不可用。 还有,要注意高水位线的问题。DELETE操作不会释放空间,而TRUNCATE或者MOVE会。所以如果删除大部分数据后,可能需要收缩或者重建索引,来回收空间,提升性能。 此外,NOLOGGING选项可以在插入时减少日志量,加快速度。但要注意备份,因为NOLOGGING操作可能无法用日志恢复。还有并行处理,用并行DML来加速,但需要根据系统资源调整。 最后,提醒用户在操作前备份数据,使用生产环境的测试环境先验证,避免误删。同时,考虑在业务低峰期操作,减少对业务的影响。可能还需要更新统计信息,重建索引,保证后续查询效率。</think>在Oracle数据库中一次性删除千万级数据,需要结合性能、事务管理和资源占用综合考虑。以下是分步骤的解决方案及注意事项: --- ### **一、常规DELETE语句的风险** 直接执行`DELETE FROM table WHERE condition`存在三大问题: 1. **事务回滚段暴增**:千万级删除生成大量undo数据,可能触发`ORA-01555`错误 2. **锁时间长**:长时间持有行锁/锁,阻塞其他DML操作 3. **性能瓶颈**:单次提交消耗大量内存和日志资源 --- ### **二、推荐方案:分批删除** #### **步骤1:创建带ROWID的游标** ```sql DECLARE CURSOR c_del IS SELECT rowid FROM table WHERE condition ORDER BY rowid; -- 确保有序扫描 TYPE del_array IS TABLE OF ROWID INDEX BY PLS_INTEGER; l_rowids del_array; BEGIN OPEN c_del; LOOP FETCH c_del BULK COLLECT INTO l_rowids LIMIT 5000; -- 每批5000条 EXIT WHEN l_rowids.COUNT = 0; FORALL i IN 1..l_rowids.COUNT DELETE FROM table WHERE rowid = l_rowids(i); COMMIT; -- 分批提交 END LOOP; CLOSE c_del; END; / ``` #### **步骤2:优化关键点** - **LIMIT值调整**:根据测试调整批量大小(建议5000-20000) - **索引优化**:确保WHERE条件字段有索引,避免全扫描 - **并行处理**:添加`/*+ PARALLEL(4) */`提示(需评估服务器资源) --- ### **三、高级方案:分区优化** 如果目标是分区删除条件匹配分区键: ```sql ALTER TABLE table DROP PARTITION partition_name UPDATE INDEXES; ``` 特点: - 数据字典级操作(秒级完成) - 不产生undo日志 - 需要预定义分区策略 --- ### **四、替代方案:CTAS(Create Table As Select)** 适用场景:需保留少量数据时 ```sql CREATE TABLE new_table NOLOGGING PARALLEL 8 AS SELECT * FROM old_table WHERE保留条件; -- 重命名(需停机维护) ALTER TABLE old_table RENAME TO old_table_bak; ALTER TABLE new_table RENAME TO old_table; -- 重建约束、索引等 ``` --- ### **五、删除后维护** ```sql -- 回收高水位空间 ALTER TABLE table MOVE; -- 重建索引 ALTER INDEX index_name REBUILD ONLINE; -- 更新统计信息 EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','TABLE'); ``` --- ### **六、性能对比** | 方法 | 时间消耗 | 资源占用 | 锁级别 | 可回滚性 | |---------------|----------|----------|--------------|----------| | 直接DELETE | 极高 | 极高 | 行级锁 | 支持 | | 分批DELETE | 中等 | 可控 | 短暂行级锁 | 支持 | | DROP PARTITION| 秒级 | 最低 | 分区级DDL锁 | 不支持 | | CTAS | 快 | 高 | 级排他锁 | 不支持 | --- ### **七、注意事项** 1. **备份优先**:操作前执行`expdp`或`RMAN`备份 2. **日志模式**:建议开启归档模式 3. **空间监控**:确保空间有20%以上空闲 4. **业务窗口**:选择低峰期操作 5. **外键约束**:检查相关约束关系 实际执行前建议在测试环境验证,可通过`EXPLAIN PLAN`分析执行计划,使用`DBMS_MONITOR`跟踪会话性能。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值