使用数据插入语句编写 Oracle 表 (DDL) 脚本到单个/多个 sql 文件中
我需要将给定模式的表导出到 DDL 脚本和 Insert 语句中 - 并编写脚本以维护依赖项/约束的顺序。
我发现这篇文章建议如何使用数据归档数据库 - http://www.dba-oracle .com/t_archiving_data_in_file_structs.htm - 不确定该文章是否适用于 Oracle 10g/11g。
我在“Sql Developer”、“Toad for Oracle”、“DreamCoder for Oracle”等中看到了“导出带有数据的表”功能，但我需要一次执行一个表，并且仍然需要弄清楚手动执行脚本的正确顺序。
是否有任何工具/脚本可以利用oracle元数据并用数据生成DDL脚本？
请注意，某些表具有 CLOB 数据类型列 - 因此工具/脚本需要能够处理这些列。
PS我需要类似于SQL Server 2008中的“生成脚本”功能，其中可以指定“脚本数据”选项并返回一个带有DDL和数据的自给自足的脚本，按照表约束的顺序生成。请参阅：http://www. kodyaz.com/articles/sql-server-script-data-with-generate-script-wizard.aspx
感谢您的帮助！
I am needing to export the tables for a given schema, into DDL scripts and Insert statements - and have it scripted such that, the order of dependencies/constraints is maintained.
I came across this article suggesting how to archive the database with data - http://www.dba-oracle.com/t_archiving_data_in_file_structures.htm - not sure if the article is applicable for oracle 10g/11g.
I have seen "export table with data" features in "Sql Developer", "Toad for Oracle", "DreamCoder for Oracle" etc, but i would need to do this one table at a time, and will still need to figure out the right order of script execution manually.
Are there any tools/scripts that can utilize oracle metadata and generate DDL script with data?
Note that some of the tables have CLOB datatype columns - so the tool/script would need to be able to handle these columns.
P.S. I am needing something similar to the "Generate Scripts" feature in SQL Server 2008, where one can specify "script data" option and get back a self-sufficient script with DDL and data, generated in the order of table constraints. Please see: http://www.kodyaz.com/articles/sql-server-script-data-with-generate-script-wizard.aspx
Thanks for your help!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
首先，认识到这不一定是可能的。视图可以使用包中的函数，该函数也从视图中进行选择。另一个问题是，您可能需要将数据加载到表中，然后应用约束，即使这可能比其他方式慢。
简而言之，您需要在这里做一些工作。
找出系统中的依赖关系。 ALL_DEPENDENCIES 是主要机制。
然后使用DBMS_METADATA.GET_DDL提取DDL语句。对于小数据量，我会单独提取约束以在数据加载后应用。
在当前版本中，您可以创建外部表以将数据从常规表卸载到操作系统文件（显然反之亦然）。但如果您有外来数据类型（BLOB、RAW、XMLTYPE、用户定义类型......），那就更具挑战性。
Firstly, recognise that this isn't necessarily possible. A view can use a function in a package that also selects from the view. Another issue is that you might need to load data into tables and then apply constraints, even though this might be slower than the other way round.
In short, you will need to do some work here.
Work out the dependencies in your system. ALL_DEPENDENCIES is the primary mechanism.
Then use DBMS_METADATA.GET_DDL to extract the DDL statements. For small data volumes, I'd extract the constraints separately for applying after the data load.
In current versions you can create external tables to unload data from regular tables into OS files (and obviously go the other way round). But if you've got exotic datatypes (BLOB, RAW, XMLTYPEs, User Defined Types....) it will be more challenging.
我建议你在这里使用Oracle标准导出和导入（exp/imp），你有理由不考虑它吗？另外请注意，您可以在导入时使用“indexfile”选项将 SQL 语句（不幸的是，这不包括插入）输出到文件，而不是实际执行它们。
I suggest that you use Oracle standard export and import (exp/imp) here, is there a reason why you won't consider it? Note in addition you can use the "indexfile" option on the import to output the SQL statements (unfortunately this doesn't include the inserts) to a file instead of actually executing them.