oralce 超过1亿条数据的数据库表清理实践

一时失言乱红尘 2023-10-10 09:19 48阅读 0赞

# #

2018-08-18 16:58 [无腿鸟][Link 1] 阅读(331) 评论(0) [ 编辑][Link 2] 收藏

问题：**当一个表的数据量超过一亿条，要删除其中的5000w条，如何处理**。

如果直接使用delete语句，会涉及到到大量的磁盘IO，并产生大量的数据库日志，效率很低，删除速度慢，可能导致事务中断，甚至有服务器硬盘空间撑爆的可能。

本文提供的思路是先将数据表需要保留的数据不带索引导出，然后导入一个新表中 ，对新表重建索引后将老表、新表进行重命名，这样就完成了删除操作，效率有了很大提升。

**主要分为三步，1.数据导出2. 数据导入 3.表以及索引重命名 。**下面脚本是运行在oracle数据库上。

具体步骤如下：

**1.数据导出：**

进入数据库：sqlplus ，输入用户名与密码。进入sql运行界面。

创建虚拟目录：create or replace directory exp\_dir as '/home/oracle/';

给目录授权，若失败继续：grant read,write on directory exp\_dir to oracle;

提交：commit;

退出数据库：exit;

导出全量数据： expdp user/passwd@sername  dumpfile=exp\_table\_eg\_his\_bak.dmp directory=exp\_dir  tables= exp\_table\_eg\_his  compression=all

dumpfile是指明dump文件名，directory是上面建立的虚拟目录，tables指明要导出的表。compression说明要对数据进行压缩，减少磁盘占用。数据量比较大，导出时间长，建议后台运行。

如果没有数据库用户密码，可使用操作系统oracle的权限以管理员身份进行导出。脚本如下：

expdp \\'/ as sysdba \\'   dumpfile= exp\_table\_eg\_his.bak .dmp directory=exp\_dir tables= user.exp\_table\_eg\_his compression=all

注意，exp\_table\_eg\_his前要加user.，用以说明是哪个用户的表。导出全量的数据只是为了备份而已。

导出保留数据 :

expdp user/passwd123@sername  dumpfile=exp\_table\_eg\_his\_new.dmp directory=exp\_dir tables= exp\_table\_eg\_his Query=\\"where plat\_number like \\'2018%\\'\\"  exclude=index

这里需要注意，**query查询条件中的“”，‘’都要用\\进行转义，否则会报错，**exclude参数用来排除索引，oracle的索引是全局的，相同的索引名只能存在一个，为了后续方便导入，需要导出时把索引排除。

**2. 数据导入：**

impdp user/passwd123@sername  directory=exp\_dir dumpfile=exp\_table\_eg\_his\_new.dmp remap\_table= exp\_table\_eg\_his: exp\_table\_eg\_his \_new

remap\_table用来将导入时将dump的表映射为新的表名。

进入数据库：进入toad

重建索引：

create unique  INDEX exp\_table\_eg\_his\_ix\_new ON exp\_table\_eg\_his\_new    (plat\_number)  。

关于建索引，最好将原表的的索引创建脚本导出，修改下索引名字与表名，这样可以保证索引的参数一致。

**3. 重命名表与索引：**

表重命名：alter table exp\_table\_eg\_his rename to exp\_table\_eg\_his \_bak

alter table exp\_table\_eg\_his \_new rename to exp\_table\_eg\_his

索引重命名：

alter index exp\_table\_eg\_his\_ix rename to exp\_table\_eg\_his\_ix\_bak

alter index exp\_table\_eg\_his\_ix\_new rename to exp\_table\_eg\_his\_ix

如果代码中没有显式用到索引名字，在数据库表重命名后索引也可以不用重命名。

删除原his表：drop table exp\_table\_eg\_his\_bak。备份表已无用，可以删掉了。

转载于:https://www.cnblogs.com/lcword/p/11553117.html

[Link 1]: https://www.cnblogs.com/dengjk/
[Link 2]: https://i.cnblogs.com/EditPosts.aspx?postid=9497787

oralce 超过1亿条数据的数据库表清理实践

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle数据库空间不足：清理无效数据的实践

相关 1亿条数据如何分表100张到Mysql数据库中（PHP）

相关记录数过亿条的表数据维护-数据删除

相关 oralce 超过1亿条数据的数据库表清理实践

相关清理oracle数据库中间表

相关 mysql 3亿条数据库_如何在mysql 造1亿条记录的大容量数据表？

相关 Oralce清理多余的session

相关 oralce删除表重复数据

相关 mongodb每天上亿数据量定期清理

相关 oralce 表分析