一次讲透Oracle复制表数据的多种姿势从INSERT到CREATE TABLE AS总有一款适合你干Oracle的兄弟应该都有过这种经历上线前要同步一份数据、开发环境要拉一条生产数据、或者只是想把一张历史表归档到另一张表里。每到这种时候复制表数据就成了绕不开的操作。这个需求看起来简单网上搜教程也是一大堆但真正落到自己手里总会碰到各种意想不到的情况字段对不上、主键冲突、CLOB字段丢了、跑到一半报ORA-01555甚至数据量大到直接撑爆临时表空间。我最初接触这个问题的时候也是从一条INSERT INTO ... SELECT开始的用着用着才发现这套东西细分下来门道不少。今天干脆把我这些年整理出来的经验一次讲透从最简单的单表复制到跨用户、跨库、大表加速再到容易翻车的坑全都给你捋一遍。如果你也经常和Oracle打交道这篇内容建议收藏起来慢慢看。1. 复制前一定要想清楚的3个问题1.1 你要复制的是数据还是结构加数据很多刚入门的朋友上来就搜“oracle复制一张表的数据到另一张表”结果搜到一堆INSERT INTO ... SELECT拿着就往自己业务上套。但在动手之前你得先分清一个核心问题目标表到底存不存在。目标表已存在那就用INSERT INTO ... SELECT只灌数据不管结构。目标表不存在要么提前用CREATE TABLE ... AS SELECT一步到位要么先CREATE TABLE建表再INSERT。这两种方式不仅仅是“能不能跑通”的区别还牵扯到约束、默认值、索引、触发器怎么处理。我见过不少开发同事图省事直接CREATE TABLE t2 AS SELECT * FROM t1结果目标表上的主键、非空约束、默认值全丢了后期发现问题再回头补反而更麻烦。1.2 数据量级决定了你能不能任性如果你的表只有几百行随便怎么写都行。但如果是一张几千万行、甚至上亿行的生产大表复制方式不对轻则跑十几分钟重则把生产库的undo表空间撑爆直接影响线上业务。小数据量INSERT INTO ... SELECT就够了不需要额外优化。大数据量要考虑APPEND提示、PARALLEL并行、NOLOGGING减少redo生成甚至考虑用逻辑备份expdp加TABLE_EXISTS_ACTIONAPPEND的方式来做。超大表 需要过滤条件分区裁剪、分批提交往往是更好的选择。这也是为什么我一直强调复制表数据没有“银弹”你得根据实际场景去选方案。1.3 复制后是否需要保留约束、索引和权限这是最容易被忽略的点。CREATE TABLE t2 AS SELECT * FROM t1这种方式默认情况下表结构都复制过去了但索引、约束、触发器、权限、同义词这些统统不会复制。而INSERT INTO ... SELECT如果目标表是预先建好的那约束和索引则取决于你建表时的定义。所以动手之前把需求问清楚目标表要不要和源表一样有主键要不要带索引要不要保留触发器和权限分区表要复制成分区表吗这些问题没想清楚就动手后面返工的成本可比写那几条SQL高多了。2. 最常用的3种复制方式用法和坑一次说清2.1 INSERT INTO ... SELECT最灵活但细节多这是出现频率最高的写法适用场景是目标表已经存在只需要把源表数据灌进去。INSERT INTO target_table (col1, col2, col3) SELECT col1, col2, col3 FROM source_table WHERE condition;如果两边的字段完全一致也可以简写成INSERT INTO target_table SELECT * FROM source_table;但这里有个值得注意的地方如果使用SELECT *Oracle是按位置匹配字段的不是按字段名匹配。也就是说只要两边字段顺序一致就行字段名不同也没关系。这既是优点也是隐患万一源表某天加了个字段顺序变了INSERT就会报错或数据错位。关于INSERT INTO ... SELECT有三个点是我吃过亏后总结出来的锁问题这条语句隐式开启一个事务执行期间会对目标表加锁。如果表很大其他会话的DML会被阻塞所以在生产环境执行前要评估好业务空窗期。事务太大默认情况下如果数据量很大一条语句跑完才提交一旦失败全部回滚。碰到这种场景考虑分批循环提交既避免undo膨胀也让进度可控。默认值处理INSERT INTO target_table (col1, col2) SELECT col1, col2 FROM source_table这种方式目标表中有默认值的字段如果没有出现在列清单里就会用默认值填充这个特性有时候能帮上忙但有时候会引发数据不一致得根据需求确认清楚。2.2 CREATE TABLE ... AS SELECT一步到位建表灌数这个语法是我个人很喜欢用的一种因为它干净利落CREATE TABLE target_table AS SELECT * FROM source_table WHERE 11;优点很明显目标表不存在一句SQL搞定建表和灌数。缺点也明显不会复制索引、约束、触发器、默认值。而且WHERE 11这个条件看起来没用实际上如果你加一个恒为假的WHERE 10就可以只建表结构不灌数据算是一个常用技巧。CREATE TABLE ... AS SELECT还有个比较隐蔽的坑就是字段类型和长度会被Oracle重新推导。举个例子如果源表字段类型是VARCHAR2(100)但实际数据最长只用了20个字符创建出来的新表字段长度可能会被压缩成VARCHAR2(20)。这在特定版本下更明显一旦目标表后续要容纳更长的数据就会报ORA-12899。如果要对目标表进行额外设置比如指定表空间可以这样写CREATE TABLE target_table TABLESPACE users AS SELECT * FROM source_table;需要注意的是在Oracle中这种方式迁移数据时如果源表是分区表复制出来的目标表默认是非分区表。要保留分区结构得开启PARTITION_OPTIONS相关操作或用dbms_metadata.get_ddl先把建表脚本抽出来。2.3 MERGE INTO既要插入又要更新怎么办有时候我们不只是单纯复制数据而是想把源表的数据“同步”到目标表存在就更新不存在就插入。这种场景下用MERGE会比先UPDATE再INSERT高效得多。MERGE INTO target_table t USING source_table s ON (t.id s.id) WHEN MATCHED THEN UPDATE SET t.col1 s.col1, t.col2 s.col2 WHEN NOT MATCHED THEN INSERT (id, col1, col2) VALUES (s.id, s.col1, s.col2);这个语法在做增量同步时非常实用。比如你要把一张历史表的数据同步到另一张汇总表用MERGE一次搞定。不过需要注意ON条件的字段要能唯一标识记录否则匹配到多行时Oracle会报ORA-30926“无法在源表中获得一组稳定的行”。三种方式适合不同场景我平时判断的优先级是仅灌新数据INSERT INTO ... SELECT建表灌数据CREATE TABLE AS SELECT同步更新插入MERGE INTO3. 实战5种业务场景的复制方案3.1 同一用户下复制整张表含数据假设你要在同一个Schema下把employees表复制成employees_bak-- 方式一直接建表复制 CREATE TABLE employees_bak AS SELECT * FROM employees; -- 方式二如果表已经建好了只灌数据 INSERT INTO employees_bak SELECT * FROM employees; COMMIT;如果你希望复制后的表带上主键、索引建议先把employees_bak按源表的DDL建好再执行INSERT。或者直接用PL/SQL配合动态SQL生成DDL这是比较进阶的做法后面我会详细讲。3.2 跨用户复制表数据从SCOTT复制到HR这是我在处理多Schema环境时经常会碰到的场景。跨用户复制数据时源表和目标表属于不同用户需要先确认目标表的用户有没有访问源表的权限。-- 确保HR用户有SCOTT.EMPLOYEES的SELECT权限 GRANT SELECT ON scott.employees TO hr; -- HR用户登录后执行 INSERT INTO hr.employees_bak SELECT * FROM scott.employees; COMMIT;如果需求是直接把表结构和数据都复制过来也可以在目标用户下执行CREATE TABLE hr.employees_bak AS SELECT * FROM scott.employees;这个做法有一个隐藏点CREATE TABLE ... AS SELECT不要求目标用户对源表有单独的SELECT权限之外的特殊权限但要注意SELECT权限要提前授权好。如果报ORA-01031权限不足多半就是这一步没做。跨用户使用时还要留意同义词的问题。如果业务代码里是通过同义词访问这张表的复制到新用户后同义词不会跟着过来需要额外创建否则应用端会报“表或视图不存在”。3.3 跨数据库复制通过dblink有时候表不在同一个实例里比如生产库往报表库同步数据。这种情况下就得用数据库链接dblink了。先创建dblinkCREATE DATABASE LINK dblink_to_report CONNECT TO report_user IDENTIFIED BY password USING report_host:1521/REPORTPDB;然后就能像访问本地表一样访问远程表INSERT INTO local_target_table SELECT * FROM report_user.remote_source_tabledblink_to_report;如果目标表不存在也可以一行搞定CREATE TABLE local_target_table AS SELECT * FROM report_user.remote_source_tabledblink_to_report;这种方式确实方便但有几个先天问题要注意性能瓶颈在网络层大数据量走dblink网络带宽会成为瓶颈几千万行数据传起来非常耗时。字符集差异源库和目标库如果字符集不一致可能出现乱码。查询远程表的主键和索引统计信息不准优化器可能选出很差的执行计划。基于这些考虑跨库大批量同步我更倾向于用expdp/impdp或者OGG这种专业工具dblink更适合小批量的临时同步。3.4 只复制表结构不复制数据有时我们只是需要一个空的表结构用来做下一阶段的初始化。两种常见姿势-- 姿势一WHERE恒为假 CREATE TABLE target_table AS SELECT * FROM source_table WHERE 10; -- 姿势二用ROWNUM限制0行效果一样 CREATE TABLE target_table AS SELECT * FROM source_table WHERE ROWNUM 1;有朋友会问这种方式把NOT NULL约束复制过来了吗答案是要区分版本的。从Oracle 11g开始CREATE TABLE AS SELECT会复制NOT NULL约束但主键、外键、检查约束、默认值不会复制。所以如果你需要完整结构还是建议用DBMS_METADATA.GET_DDL把建表脚本拉出来执行更妥当。3.5 只要部分字段、部分数据这个场景在测试环境造数时非常常见。比如我只想要employees表中department_id50的所有员工且只要employee_id、first_name、last_name、salary这几个字段CREATE TABLE dept_50_employees AS SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id 50;注意如果目标表是已经存在的那需要写成INSERTINSERT INTO dept_50_employees (employee_id, first_name, last_name, salary) SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id 50;关于部分字段复制我最常踩的坑是字段类型不一致。比如源表的salary是NUMBER(10,2)目标表建成了NUMBER(8,2)数据一插入就报ORA-01438值过大。所以批量复制前最好先看下目标表的字段定义尤其是数值精度和字符串长度。4. 大数据量复制怎么提速并行APPENDNOLOGGING4.1 三个让复制“飞起来”的提示如果你要复制的表超过千万行常规的INSERT INTO ... SELECT就会相当吃力。这时候一般会加三个常用提示INSERT /* APPEND PARALLEL(4) */ INTO target_table SELECT /* PARALLEL(4) */ * FROM source_table;拆开解释一下APPEND直接在高水位线以上追加数据不走常规的查找空闲块过程产生的redo大幅减少。PARALLEL并行执行多个进程同时干活充分利用CPU和IO。NOLOGGING需要在目标表或表空间级别设置减少日志生成让插入更快。如果是CREATE TABLE AS SELECT默认就是NOLOGGING的在部分版本中但INSERT需要配合APPEND提示或设置表属性。APPEND模式有个容易被忽略的副作用在追加期间其他会话不能对目标表做DML操作否则会冲突。而且APPEND之后如果数据库异常宕机这部分数据可能会丢失因为它产生的redo少恢复时有一定风险。所以在生产环境大表复制要权衡好数据安全性和速度。4.2 并行度怎么选并行度不是越大越好。我看到很多新手直接写PARALLEL(32)结果系统瞬间被拖垮。这里有个基础判断方法先看CPU核数并行度一般不超过CPU核心数的2倍。再看IO能力如果是机械硬盘并行太高会在磁盘层面排队反而更慢。最后看数据量几百万行的小表并行度2就到顶了上亿行的大表并行度8到16可能比较合适。稳妥的做法是先测试小并行度观察等待事件和响应时间再逐步往上调。4.3 分批提交才是王道对于特别大的表即使加了并行和APPEND一条巨长的SQL一旦失败全部回滚时间成本太高。更稳妥的做法是用PL/SQL循环分批复制DECLARE v_batch_size NUMBER : 10000; v_last_id NUMBER : 0; BEGIN LOOP INSERT INTO target_table SELECT * FROM source_table WHERE id v_last_id AND ROWNUM v_batch_size ORDER BY id; EXIT WHEN SQL%ROWCOUNT 0; v_last_id : v_last_id v_batch_size; COMMIT; END LOOP; END; /这个方法的优点是每批提交一次单条事务小失败后重跑的代价低。缺点是多了循环和提交整体速度可能比单条大SQL稍慢。但如果你的表真的很大慢一点没关系稳才是第一位。提示上面这个例子用的是ROWNUM和自增ID如果你有更好的有序字段比如时间戳可以把WHERE和ORDER BY替换掉逻辑更清晰。但要注意ORDER BY在大表上会额外消耗排序空间如果源表有索引可以借助索引避免排序。5. 复制完成后这些后遗症必须处理5.1 约束和索引丢失前面反复提过CREATE TABLE AS SELECT不会复制主键、外键、唯一约束和索引。如果复制的数据要被业务系统真实使用这一步不能省。最靠谱的方法是用DBMS_METADATA.GET_DDL把源表的建表脚本抽出来改个表名重新执行SELECT DBMS_METADATA.GET_DDL(TABLE, EMPLOYEES) FROM DUAL;然后用这个脚本重建目标表的约束和索引。如果是跨用户复制还要注意把表空间的引用改成目标用户有权限的表空间。5.2 权限和同义词复制表之后如果其他用户或应用需要通过原表名访问你需要在新表上重新授权并创建同义词GRANT SELECT ON target_table TO another_user; CREATE PUBLIC SYNONYM employees_alias FOR target_table;这个步骤经常被忽略导致应用一上线就报ORA-00942表或视图不存在。5.3 统计信息刷新复制完大表之后Oracle的优化器可能还停留在“这张表是空的”或者“数据量很小”的认知上这会导致后续查询执行计划很差。解决办法是立刻收集统计信息EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname SCOTT, tabname EMPLOYEES_BAK, cascade TRUE );如果是分区表还可以加上GRANULARITY ALL来收集所有分区的统计信息。这一步听起来很基础但真的是很多线上慢查询的根源。5.4 验证数据一致性复制完了并不意味着可以高枕无忧。接下来要做的是数据比对尤其是大表复制后非常有必要确认行数和关键字段有没有差异。-- 对比行数 SELECT COUNT(*) FROM source_table; SELECT COUNT(*) FROM target_table; -- 对比关键字段的汇总值比如金额总和 SELECT SUM(amount) FROM source_table; SELECT SUM(amount) FROM target_table; -- 更严谨的可以求差集 SELECT * FROM source_table MINUS SELECT * FROM target_table;用MINUS做全量对比很严谨但数据量大时性能比较差实践中常用分组汇总值对比来替代速度快且能发现绝大多数漏数据或重复数据。6. 高频报错与避坑指南现场实录6.1 ORA-00001违反唯一约束这个报错太常见了。复制数据时目标表本身有唯一约束或主键源表数据里又有重复记录一插入就报错。解决办法分两种情况如果目标表允许去重用DISTINCT或ROW_NUMBER()去重后再插入。如果目标表需要保留所有数据那就要检查源表数据本身的质量问题或者调整目标表的约束设计。去重的写法示例INSERT INTO target_table SELECT * FROM source_table WHERE ROWID IN ( SELECT MAX(ROWID) FROM source_table GROUP BY id );6.2 ORA-01555快照过旧这是个经典问题通常发生在复制大表时SQL跑的时间太长查询需要用到的UNDO被覆盖了。解决方案把SQL拆小分批提交。调整UNDO表空间的UNDO_RETENTION参数。尽量缩短单条SQL的执行时间加索引、加并行。对于这种问题我会用PL/SQL分批处理既避免ORA-01555也让整个过程可监控、可恢复。6.3 ORA-00947没有足够的值如果你在INSERT INTO target_table (col1, col2)里面只写了两个字段但SELECT里查出来三个字段就会报这个错误。务必将字段列表和查询结果的列顺序对应上一个常见做法是编写SQL前先DESC source_table和DESC target_table确保两边字段数量、顺序都清晰。6.4 ORA-12899列值对于列来说太大通常是目标表的某个字段长度比源表小。比如源表VARCHAR2(100)你在建目标表时定义成了VARCHAR2(50)包含超长字符的数据插入时就报这个错。排查方式SELECT column_name, char_length, char_used FROM all_tab_columns WHERE table_name TARGET_TABLE;根据报错信息里的列名去源表查一下这一列的最长数据然后修改目标表列长度ALTER TABLE target_table MODIFY (col_name VARCHAR2(200));6.5 中文乱码跨库复制时如果源库字符集是AL32UTF8目标库是ZHS16GBK中文内容就可能出现乱码。最稳妥的方式是确保两个库字符集兼容。如果是dblink临时同步可以考虑在查询时做字符集转换但更推荐的还是用expdp工具它在导出/导入时会做字符集转换。6.6 复制过程中业务还在写源表如果你复制的数据源表是生产在线表复制期间业务还在持续更新可能导致复制出来的数据不一致。这种情况通常要控制一致性读或者在业务低峰期操作。如果业务不能停可以考虑闪回查询Flashback Query到一个固定的SCN再复制INSERT INTO target_table SELECT * FROM source_table AS OF SCN 123456789;这个做法的前提是UNDO保留时间足够长SCN对应的数据还在UNDO里。这种方式能拿到某个时间点的一致性快照避免复制期间数据变动带来的混乱。7. 如何选择一句话版本复制表数据这个操作真的不难难的是在给定的环境和需求里选对方法。我把自己习惯的判断逻辑梳理了一下目标表不存在默认选CREATE TABLE AS SELECT追求完整结构就再手动补索引约束。目标表已存在只灌数据用INSERT INTO ... SELECT数据量大就APPENDPARALLEL再大就分批循环。目标表已存在并且需要更新插入用MERGE能省一次查询。跨库同步dblink适合小数据expdp适合大数据专业同步工具看预算和团队能力。只取部分数据CREATE TABLE AS SELECT加过滤条件最省事。复制完成后千万别忘了统计信息、约束索引、权限同义词和数据校验。我自己入行时曾经因为一张表的复制方式没选对把生产库的undo表空间撑爆了那条SQL跑了快半小时最后回滚又花了十几分钟整个业务凌晨两点都在抖。从那以后凡是涉及大表复制我都会先在测试环境跑一遍估算执行时间和redo生成量再决定用什么姿势。这不是过度谨慎这是吃过亏之后的本能。另外还有个建议复制窗口如果允许尽量用DBMS_SCHEDULER把这步操作放在定时任务里并加上日志记录。这样出了问题至少能知道是哪个步骤、哪个时间点挂掉的。复制表数据这个技术点虽然基础但每次遇到都有一些小变数。希望这篇文章能帮你少踩几个坑下次再遇到类似需求时心里能更有底。