oracle数据库常用命令 mysql数据迁移到oracle( 二 )

等待表全部创建成功，如下所示：
⑤ 导入备份数据数据泵 -> 数据泵导入
⑥ 最后查看数据导入成功！这时候，数据有了，自增主键也有了，但是存在一个问题就是插入数据的时候主键自增ID都是从1开始自增，如果表中没有数据都还ok，问题是如果表有数据，就会出现主键ID重复的问题！！！
2、解决自增主键ID无法从表数据ID最大值开始增值思路：拼接出修改表自增ID从几开始的sql即可！SELECT'SELECT ''ALTER TABLE SEWAGE_GY.' || t1.table_name || ' MODIFY(' || t1.Column_Name || ' Generated as Identity (START WITH '' || MAX( ' || t1.Column_Name || '+1 ) || ''));'' FROM ' || t1.table_name || ' UNION ALL' AS FINAL_SQLFROM cols t1LEFT JOIN user_col_comments t2 ON t1.Table_name = t2.Table_name AND t1.Column_Name = t2.Column_NameLEFT JOIN user_tab_comments t3 ON t1.Table_name = t3.Table_nameWHERENOT EXISTS (SELECT t4.Object_NameFROM User_objects t4WHEREt4.Object_Type = 'TABLE'AND t4.TEMPORARY = 'Y'AND t4.Object_Name = t1.Table_Name)AND t1.IDENTITY_COLUMN = 'YES'ORDER BY t1.Table_Name, t1.Column_ID命令解析：
# 设置表主键ID从多少开始自增ex:下面标识从10000开始自增ALTER TABLE 数据库名.表名 MODIFY(主键ID Generated as Identity (START WITH 10000));# 查询该库下所有表名SELECT table_name FROM user_tables;# 查询出指定表的主键ID字段名SELECT t1.table_name,t1.Column_NameFROM cols t1LEFT JOIN user_col_comments t2 ON t1.Table_name = t2.Table_name AND t1.Column_Name = t2.Column_NameLEFT JOIN user_tab_comments t3 ON t1.Table_name = t3.Table_name WHERE NOT EXISTS (SELECT t4.Object_NameFROM User_objects t4WHERE t4.Object_Type = 'TABLE'AND t4.TEMPORARY = 'Y'AND t4.Object_Name = t1.Table_Name)AND t1.table_name = '表名'AND t1.IDENTITY_COLUMN = 'YES' ORDER BY t1.Table_Name, t1.Column_ID# 查询该库下所有表名+表主键字段名SELECT t1.table_name,t1.Column_NameFROM cols t1LEFT JOIN user_col_comments t2 ON t1.Table_name = t2.Table_name AND t1.Column_Name = t2.Column_NameLEFT JOIN user_tab_comments t3 ON t1.Table_name = t3.Table_name WHERE NOT EXISTS (SELECT t4.Object_NameFROM User_objects t4WHERE t4.Object_Type = 'TABLE'AND t4.TEMPORARY = 'Y'AND t4.Object_Name = t1.Table_Name)AND t1.IDENTITY_COLUMN = 'YES' ORDER BY t1.Table_Name, t1.Column_ID拷贝到新的控制台后注意删除最后一个 UNION ALL 再运行哦！！！
最终完成自增主键ID从表数据最大值开始自增！
3、程序中的sql语句转换这里结合个人语言实际操作…

以上关于本文的内容，仅作参考！温馨提示：如遇健康、疾病相关的问题，请您及时就医或请专业人士给予相关指导!

「四川龙网」www.sichuanlong.com小编还为您精选了以下内容，希望对您有所帮助：