使用sql生成sql指令碼

tianya_2011發表於2012-11-10
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章