当前位置：文江博客话题详情

重新生成db（oracle）中的所有序列

发布于 2024-12-28 15:08:23 字数 73 浏览 0 评论 0原文

再会。我有一个非常具体的任务：重新生成数据库中的所有序列。里面有400多张桌子，所以我不能用手做。有人可以帮我做吗？多谢..

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

眉目亦如画i 2025-01-04 15:08:23

请注意，这是非常危险的。你很可能会犯错误。首先运行 select 以检查您要执行的操作并创建一个 select 表，以便您可以在需要时手动重新创建同义词。

使用 all_synonyms 或 dba_synonyms 代替 user_synonyms 可能会导致删除系统同义词不这样做如果您希望数据库随后正常工作，

我还建议在您创建的 1 个测试同义词上测试代码，以确保它完全执行您想要的操作。

另外我真的不明白这样做有什么意义？如果同义词存在，为什么需要重新生成它们？或者这是在另一台服务器上完成的？如果是这样，请在 user_synonyms 之后添加 @server_name 并删除 drop。

begin

  for xx in ( select * from user_sequences ) loop

    execute immediate 'drop sequence ' || xx.sequence_name;
    execute immediate 'create sequence ' || xx.sequence_name
                      ||  ' start with ' || xx.min_value
                      ||  '  ends with ' || xx.max_value
                      || case when xx.cycle_flag = 'N' then ' nocycle ' 
                              else ' cycle ' end
                      || case when xx.cache_size = 0 then ' nocache ' 
                              else ' cache ' end || xx.cache_size
                      || case when xx.order_flag = 'N' then ' noorder ' 
                              else ' order ' end
                      ;

   end loop;

end;

Please note this is highly dangerous. You may very well make mistakes. Run the select first to check what you're about to do and create a table of the select so you can re-create the synonyms manually later if you need to.

Using all_synonyms or dba_synonyms instead of user_synonyms may result in dropping system synonyms do not do this if you want your database to work afterwards

I'd also recommend testing the code on 1 test synonym you create to ensure that it does exactly what you want.

Plus I don't really see the point of doing this at all? If the synonyms are there why do you need to re-generate them? Or is this being done on another server? If so add @server_name after user_synonyms and remove the drop.

begin

  for xx in ( select * from user_sequences ) loop

    execute immediate 'drop sequence ' || xx.sequence_name;
    execute immediate 'create sequence ' || xx.sequence_name
                      ||  ' start with ' || xx.min_value
                      ||  '  ends with ' || xx.max_value
                      || case when xx.cycle_flag = 'N' then ' nocycle ' 
                              else ' cycle ' end
                      || case when xx.cache_size = 0 then ' nocache ' 
                              else ' cache ' end || xx.cache_size
                      || case when xx.order_flag = 'N' then ' noorder ' 
                              else ' order ' end
                      ;

   end loop;

end;

回复收藏 0 原文

~没有更多了~