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博客

使用sql生成sql脚本

最新文章