使用sql生成sql脚本

1，设置环境变量
z7z g&@%y5tO/R0 SQL>set echo off
2O,Vf En1QP0 SQL>set heading off ITPUB个人空间7E|WcPl0k B
SQL>set feedback off

2,把SQL*plus中执行的内容追加到一个文件中： ITPUB个人空间R&p#i ]5I!b\*y
SQl>spool D:\oracle\grant.txt

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

部分输出内容
2^~b eev7`0 grant connect,resource toSYS; ITPUB个人空间v`s;l'{
grant connect,resource toSYSTEM;
a%Az He0M8l3j3j%S0 grant connect,resource toSCOTT;
3jo*^$@7e w y Z0 grant connect,resource toTEST;
f)B+Ch\l A0 grant connect,resource toRMAN; ITPUB个人空间6iR2OX+q
grant connect,resource toOUTLN; ITPUB个人空间6Xm ~ S#FG
grant connect,resource toMGMT_VIEW; ITPUB个人空间f5_3s-^&o
grant connect,resource toMDSYS;
@](seA-BA8lw]0 grant connect,resource toORDSYS;
m$f/\(dC#M R;kP:k0 grant connect,resource toCTXSYS; ITPUB个人空间5aWnIK)n
grant connect,resource toANONYMOUS; ITPUB个人空间"s*DuLu}s:LW
grant connect,resource toEXFSYS;
kq@&I B(kLMo$J0 grant connect,resource toDMSYS;

4,查看grant.txt的内容： ITPUB个人空间0D&]3X8Y B!E&L_HE X
grant connect,resource toSYS; ITPUB个人空间{r"]@;v#S
grant connect,resource toSYSTEM; ITPUB个人空间e)eIf Wr!~^[8A
grant connect,resource toSCOTT; ITPUB个人空间yc#x a3_%z}
grant connect,resource toTEST;
a%RRsj+~S0 grant connect,resource toRMAN; ITPUB个人空间5t0D9v@9R"v[
grant connect,resource toOUTLN;
|%o-d V L5F O0 grant connect,resource toMGMT_VIEW;
u6\"~EKO7u_'X0 grant connect,resource toMDSYS;
L Q K!d5q#[+@0 grant connect,resource toORDSYS;
hQ ZT\rx9w0 grant connect,resource toCTXSYS; ITPUB个人空间 ^R;hBHu!s
grant connect,resource toANONYMOUS; ITPUB个人空间c%EE;CT x
grant connect,resource toEXFSYS; ITPUB个人空间 ay0{$y P
grant connect,resource toDMSYS; ITPUB个人空间 O e!ClML0q
... ... ITPUB个人空间OZ*g.o ld0aPK|4?

5,运行脚本
T LN'} g:uQ2k4_0 SQL>@D:\oracle\grant.txt
p{TM*SSK8m0 Grant succeeded. ITPUB个人空间Nn"rBK _h
Grant succeeded. ITPUB个人空间 }]_X.xv
Grant succeeded.
.p._D8Dkp3YZ:q.w0 Grant succeeded.
1@ t#W'q4M Z7A0 Grant succeeded.
%G][_D-c].}~X0 Grant succeeded.
A3y7l Jp0 Grant succeeded. ITPUB个人空间,p.j$lzNGY
... ...

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