用exp导出数据库时带query参数时必须有tables参数,否则就报错:“EXP-00035: QUERY 参数仅在表模式导出中有效”
我想把每个表只导出前100行怎么办?
我想把每个表只导出前100行怎么办?
解决方案 »
- SUSE 下安装oracle10,建立oracle用户后,必须让它在oinstall组下吗。
- order by语句怎么优化
- oracle提示12560.12541
- 50分!oracle 非常简单的update用法
- oci 执行sql问题。
- 关于varchar2类型的字段排序错误的问题
- 通用查询窗口,如何避免用户设置的查询条件返回过多记录(oracle数据库)?
- 取系统时间问题
- 搞不懂耶,为什么用 ResultSet 对象的.next()可以取得值但用.first()就出错了?
- ORACLE存储过程怪问题,100分解决再加100分
- 问个菜鸟问题
- oracle查询语句里能用if else这类的控制语句吗
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.
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 exportedAbout 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]$
如果表太多,则必须要使用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'"
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 2010Copyright (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 exportedAbout 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]$
首先查询select table||’,’_name from user_tables ,从user_tables表中可以查到本用户下的所有表,如果有不想要的用也可以利用where条件过滤一下将不要的表过滤除去,这条语句的最终作用就是将想要导出的表名都用逗号连接起来,不过导出的所有表名粘到记事本中以后有换行,还需要将所有的东西从记事本中粘贴到word中,然后选中所有内容,然后ctrl+H,点击 高级 按钮,点击特殊字符,然后选段落标记,后在查找内容中就会出现^p,在替换为中什么也不写,点击全部替换,这样就将所有的换行都消除了,然后所有的表名都以逗号间隔连接起来了
有了连接的表名以后,在控制台中输入exp file=c:\expdat.emp tables(表名1,表名2,……)