oracle exp如何导出每个表的前100行

[wangguanguo]

用exp导出数据库时带query参数时必须有tables参数，否则就报错：“EXP-00035: QUERY 参数仅在表模式导出中有效”
我想把每个表只导出前100行怎么办？

[luoyoumou]

[oracle@sztyora exp]$ exp "scott/bee56915 TABLES=(emp,bonus) QUERY='WHERE rownum<=100'"

Export: Release 10.2.0.4.0 - Production on Fri Jun 11 20:38:38 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.

[luoyoumou]

[oracle@sztyora exp]$ exp "scott/bee56915 file=/u02/exp/scott.dmp indexes=n grants=n constraints=n direct=n TABLES=(emp,bonus) QUERY='WHERE rownum<=100'"

Export: Release 10.2.0.4.0 - Production on Fri Jun 11 20:44:29 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...
. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                          BONUS          0 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@sztyora exp]$ 

[tangren]

1、如果使用exp，必须要在参数tables中逐一指定表，才能使用query参数，没有办法一次性指定所有表
如果表太多，则必须要使用parfile参数，而将exp的参数放到参数文件中，如：
c:\>exp test/test@tnsname file=t.dmp tables=(emp,emp1) query="'where rownum<=100'"

2、如果使用10g，可以使用expdp来导出，并且可以使用include参数来指定所有的表，当然，在使用expdp前，必须要创建directory并授权，如：
c:\>expdp test/test directory=my_dir dumpfile=t.dmp include=table query="'where rownum<=100'"

[luoyoumou]

-- 用这个语句查询你某一模式下的所有表：

SQL> SELECT object_name||',' as user_object
  2  from user_objects where object_type='TABLE';

USER_OBJECT
--------------------------------------------------------------------------------
DEPT,
EMP,
BONUS,
SALGRADE,
T1,
T2,
T3,
T4,
T,

-- 然后将生成的结果放在 tables=() 的括号里！(当然：别忘记将最后一个逗号去掉！)

[oracle@sztyora exp]$ exp "scott/bee56915 file=/u02/exp/scott.dmp indexes=n grants=n constraints=n direct=n tables=(DEPT,EMP,BONUS,SALGRADE,T1,T2,T3,T4,T) query='WHERE rownum<=100'"

Export: Release 10.2.0.4.0 - Production on Fri Jun 11 20:56:32 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...
. . exporting table DEPT 4 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table EMP 14 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table BONUS 0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table SALGRADE 5 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table T1 1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table T2 1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table T3 1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table T4 1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table T 3 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@sztyora exp]$

[zzyzgydotnet]

学习了

[wangguanguo]

谢谢几位的回答，数据库的表太多，不可能一个一个的把表名写上去，tangren的回答对我有帮助。

[hfCoder]

不懂，帮顶

[GDY__GDY]

楼主您好，小弟有一个笨招，您可以试一下，你不是不能制定所有的表名么，您看下面的方法可以不：
首先查询select table||’,’_name from user_tables ，从user_tables表中可以查到本用户下的所有表，如果有不想要的用也可以利用where条件过滤一下将不要的表过滤除去，这条语句的最终作用就是将想要导出的表名都用逗号连接起来，不过导出的所有表名粘到记事本中以后有换行，还需要将所有的东西从记事本中粘贴到word中，然后选中所有内容，然后ctrl+H，点击 高级 按钮，点击特殊字符，然后选段落标记，后在查找内容中就会出现^p,在替换为中什么也不写，点击全部替换，这样就将所有的换行都消除了，然后所有的表名都以逗号间隔连接起来了
有了连接的表名以后，在控制台中输入exp file=c:\expdat.emp tables(表名1,表名2，……)

[GDY__GDY]

select table_name||’,' from user_tables 应该是这样，上面写错了