使用sql生成sql脚本

1，设置环境变量
  SQL>set echo off
  SQL>set heading off
  SQL>set feedback off

2,把SQL*plus中执行的内容追加到一个文件中：
  SQl>spool D:\oracle\grant.txt

3,执行SQL，查询结果会写到D:\oracle\grant.txt文件中
  SQL> select 'grant connect,resource to'||username||';' from dba_users;

  部分输出内容
  grant connect,resource toSYS;                                                  
  grant connect,resource toSYSTEM;                                               
  grant connect,resource toSCOTT;                                                
  grant connect,resource toTEST;                                                 
  grant connect,resource toRMAN;                                                 
  grant connect,resource toOUTLN;                                                
  grant connect,resource toMGMT_VIEW;                                            
  grant connect,resource toMDSYS;                                                
  grant connect,resource toORDSYS;                                               
  grant connect,resource toCTXSYS;                                               
  grant connect,resource toANONYMOUS;                                            
  grant connect,resource toEXFSYS;                                               
  grant connect,resource toDMSYS;

4,查看grant.txt的内容：
  grant connect,resource toSYS;                                                  
  grant connect,resource toSYSTEM;                                               
  grant connect,resource toSCOTT;                                                
  grant connect,resource toTEST;                                                 
  grant connect,resource toRMAN;                                                 
  grant connect,resource toOUTLN;                                                
  grant connect,resource toMGMT_VIEW;                                            
  grant connect,resource toMDSYS;                                                
  grant connect,resource toORDSYS;                                               
  grant connect,resource toCTXSYS;                                               
  grant connect,resource toANONYMOUS;                                            
  grant connect,resource toEXFSYS;                                               
  grant connect,resource toDMSYS;                                                
  ... ...

5,运行脚本
 SQL>@D:\oracle\grant.txt
 Grant succeeded.
 Grant succeeded.
 Grant succeeded.
 Grant succeeded.
 Grant succeeded.
 Grant succeeded.
 Grant succeeded.
 ... ...

哦了，大功告成，简单的一个方法，解决了大批量执行某项操作时的繁琐。

 

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

转载于:http://blog.itpub.net/25373498/viewspace-748913/