ITPub博客

首页 > 数据库 > Oracle > exp\imp 遇到操作系统转移字符问题(比如query参数里的where)

exp\imp 遇到操作系统转移字符问题(比如query参数里的where)

原创 Oracle 作者:perfychi 时间:2014-02-13 11:34:28 0 删除 编辑

from:baidu.com
String cmd="exp 用户名/密码@服务名 file=C:\20111226160758.dmp tables=(表名)  query=\\\"where ADDTIME>=to_date('2011-12-01 15:56:46','YYYY-MM-DD hh24:mi:ss') and ADDTIME<=to_date('2011-12-26 15:56:46','YYYY-MM-DD hh24:mi:ss')\\\"";(win7测试通过)

//如果是windows os?

cmd="cmd /c "+cmd;

//如果是linux os

boolean shouldClose=false;
  Process process=null;
  try {
   process = java.lang.Runtime.getRuntime().exec("cmd /c "+cmd);
   BufferedReader b = new BufferedReader(new InputStreamReader(process.getErrorStream()));  
   String line = null;
   boolean err=false;
   errinfo="";
   while((line=b.readLine())!=null)  
   {  
         System.out.println(line);
         if(err)
         {
          errinfo=line;
          process.destroy();
          break;
         }
         if(line.indexOf("错误")>0)
         {
          err=true;        
         }
      }  
      int value= process.exitValue();  
      if(value==0||(value==3&&!err))  
      {  
       this.setOperateStatus("数据库备份完成");
          System.out.println("数据库备份完成!");  
      } 
      else 
      {  
          System.out.println("exit value =" + value);  
          System.out.println("数据库备份失败!");
          this.setOperateStatus("数据库备份失败");
      }
      shouldClose=true;
      if(shouldClose)
             process.destroy();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   shouldClose=true;
   e.printStackTrace();
  }finally{   
    try {
     if(process!=null)
     process.waitFor();
    } catch (InterruptedException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }
  }

 query 参数说明

其实文档中讲得很清楚:

exp scott/tiger TABLES=emp QUERY=\"WHERE job=\'SALESMAN\' and sal \<1600\"Note:
Because the value of the QUERY parameter contains blanks, most operating systems require that the entire strings WHERE job=\'SALESMAN\' and sal\<1600 be placed in double quotation marks or marked as a literal by some method. Operating system reserved characters also need to be preceded by an escape character. See your Oracle operating system-specific documentation for information about special and reserved characters on your system.

所有操作系统保留字符都要使用转义符号,看来 ( ) 也需要转义。

正确的写法:

 exp scott/tiger tables=skytest file=test.dmp query=\"where dt=to_date\(\'2007-09-22\',\'yyyy-mm-dd\'\)\" (linux)

exp scott/tiger tables=skytest file=test.dmp query=\"where dt=to_date('2007-09-22','yyyy-mm-dd')\" (win7)

任何 os 平台都适用的方法(推荐):
使用 parfile 就不用担心这些格式问题

oracle DBALNP01 > cat > test.par
tables=skytest
file=test.dmp
query="where dt=to_date('2007-09-22','yyyy-mm-dd')"

oracle DBALNP01 > exp / parfile=test.par

Export: Release 8.1.7.4.0 - Production on Wed Sep 12 04:22:27 2007

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                        SKYTEST          1 rows exported
Export terminated successfully without warnings.

不需要任何转义符既简洁又可以在多种操作系统平台上通用,推荐使用这种方式。

?命令行exp 导出时对密码中含的处理方法

用户的密码为Rdy)Jl!S

在windows      D:>exp testuser/Rdy)Jl!S@oralocal owner=testuser

在linux    nunix下,用""
$ exp "testuser/Rdy)Jl!S" owner=testuser


IMPDP FLASHBACK_TIME 中如何使用TO_TIMESTAMP参数,防止LRM-00116

官方文档上查,参数文件中这样写"to_timestamp()"

在命令行中直接这样写

C:\Documents and Settings\yyy>impdp scott/tiger tables=t directory=exp_dir network_link=test flashback_time="to_timestamp('2009-01-07 2
0:01:22','yyyy-mm-dd hh24:MI:SS')"
LRM-00116: ')'后跟 'yyyy-mm-dd hh24:' 时出现语法错误

总提示语法错误

后来N长时间终于找到了解决办法

Alternatively you can used FLASHBACK_TIME parameter like on unix,
flashback_time=\"TO_TIMESTAMP\(\'16-05-2008 11:21:42\', \'DD-MM-YYYY HH24:MI:SS\'\)\"


On windows,
flashback_time=\"TO_TIMESTAMP('16-05-2008 13:24:26', 'DD-MM-YYYY HH24:MI:SS')\"


Use backslash(\) before special character.

所以改成

C:\Documents and Settings\jiachliu>impdp scott/tiger tables=t directory=exp_dir network_link=test flashback_time=\"to_timestamp('2009-01-07
20:01:22','yyyy-mm-dd hh24:MI:SS')\"

执行成功

--END

 
java_exp命令备份数据库_query.docx

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27042095/viewspace-1080708/,如需转载,请注明出处,否则将追究法律责任。

下一篇: oracle profile
请登录后发表评论 登录
全部评论

注册时间:2012-06-06

  • 博文量
    486
  • 访问量
    2695721