oracle oracle如何导出空表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18924704/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to export empty tables in oracle
提问by M.SH
I have a big problem When i take a dump of database the empty tables are truncated
我有一个大问题当我转储数据库时，空表被截断
How i can export the all tables in oracle 10g
我如何导出 oracle 10g 中的所有表
exp SYSTEM/password FILE=expdat.dmp
回答by Dba
This might be because of these tables may not be extent allocated. Before taking backup you need to identify all the tables that do not have data. Then alter these tables to allocate extent.
这可能是因为这些表可能未分配区。在进行备份之前，您需要确定所有没有数据的表。然后更改这些表以分配范围。
ALTER TABLE <table_name> ALLOCATE EXTENT;
Use the below script to alter all tables they do not have extent allocated.
使用下面的脚本来改变他们没有分配范围的所有表。
SELECT 'ALTER TABLE '||table_name||' ALLOCATE EXTENT;' FROM user_tables WHERE segment_created = 'NO';
Copy the output and execute it.
复制输出并执行它。
回答by DCookie
You might consider expdp (data pump)instead. There's a parameter CONTENT=METADATA_ONLYthat might get you what you want.
您可以考虑改用expdp（数据泵）。有一个参数CONTENT=METADATA_ONLY可能会让你得到你想要的。
回答by Jakob
I found another solution here.
Oracle has an option called DEFERRED_SEGMENT_CREATION, default true.
我在这里找到了另一个解决方案。
Oracle 有一个名为DEFERRED_SEGMENT_CREATION的选项，默认为 true。
from the docs:
从文档：
If set to true, then segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.
如果设置为 true，则在将第一行插入到表中之前，不会创建表及其相关对象（LOB、索引）的段。
I'll sum up the solution from the above link:
我将从上面的链接总结解决方案：
SQL> alter system set DEFERRED_SEGMENT_CREATION=FALSE scope=both;
Run the output of the following statement:
运行以下语句的输出：
SQL> select 'alter table ' || table_name || ' move;' from user_tables where num_rows=0;
This made my expexport empty tables as well.
这也使我的exp导出空表。
