Oracle管理ORACLE数据库sqlplus使用技巧.docx
《Oracle管理ORACLE数据库sqlplus使用技巧.docx》由会员分享，可在线阅读，更多相关《Oracle管理ORACLE数据库sqlplus使用技巧.docx（9页珍藏版）》请在冰豆网上搜索。
Oracle管理ORACLE数据库sqlplus使用技巧
（Oracle管理）ORACLE数据库sqlplus使用技巧
Sql*plus中蕴藏着好多技巧，如果掌握这些技巧，对于在oracle数据库下进行快速开发与有效维护数据库都是有益的。
1．使用SQL*PLUS动态生成批量脚本
将spool与select命令结合起来使用，可以生成一个脚本，脚本中包含有可以批量执行某一任务的语句。
例1：
生成一个脚本，删除SCOTT用户下的所有的表：
a.创建gen_drop_table.sql文件，包含如下语句：
SPOOLc:
\drop_table.sql
SELECT'DROPTABLE'||table_name||';'FROMuser_tables;
SPOOLOFF
b.以SCOTT用户登录数据库
SQLPLUS>@…..\gen_dorp_table.sql
c.在c盘根目录下会生成文件drop_table.sql文件，包含删除所有表的语句，如下所示:
SQL>SELECT'DROPTABLE'||table_name||';'FROMuser_tables;
'DROPTABLE'||TABLE_NAME||';'
------------------------------------
DROPTABLEDEPT;
DROPTABLEEMP;
DROPTABLEPARENT;
DROPTABLESTAT_VENDER_TEMP;
DROPTABLETABLE_FORUM;
5rowsselected.
SQL>SPOOLOFF
d.对生成的drop_table.sql文件进行编辑去掉不必要的部分，只留下droptable…..语句
e.在scott用户下运行dorp_table.sql文件，删除scott用户下所有的表。
SQLPLUS>@c:
\dorp_table.sql
在上面的操作中，在生成的脚本文件中会有多余的字符，如运行的sql语句，标题，或返回的行数，需要我们编辑该脚本后再运行，给实际的操作带来诸多不便。
懒惰是人的本性，这促使我们用更简单的办法来实现上面的任务。
a.创建gen_drop_table.sql文件，包含如下语句：
setechooff
setfeedbackoff
setnewpagenone
setpagesize5000
setlinesize500
setverifyoff
setpagesize0
settermoff
settrimson
setlinesize600
setheadingoff
settimingoff
setverifyoff
setnumwidth38
SPOOLc:
\drop_table.sql
SELECT'DROPTABLE'||table_name||';'FROMuser_tables;
SPOOLOFF
b.以SCOTT用户登录数据库
SQLPLUS>@…..\gen_dorp_table.sql
c.在c盘根目录下会生成文件drop_table.sql文件，包含删除所有表的语句，如下所示:
DROPTABLEDEPT;
DROPTABLEEMP;
DROPTABLEPARENT;
DROPTABLESTAT_VENDER_TEMP;
DROPTABLETABLE_FORUM;
d.在scott用户下运行dorp_table.sql文件，删除scott用户下所有的表。
SQLPLUS>@c:
\dorp_table.sql
2．将一个表中的数据导出生成一个文本文件，列与列之间以”,”隔开
setechooff
setfeedbackoff
setnewpagenone
setpagesize5000
setlinesize500
setverifyoff
setpagesize0
settermoff
settrimson
setlinesize600
setheadingoff
settimingoff
setverifyoff
setnumwidth38
SPOOLc:
\drop_table.sql
selectDEPTNO||','||DNAMEFROMDEPT;
SPOOLOFF
将上面的内容保存为一个文本文件后，以scott登录，执行该文件后显示结果：
10,ACCOUNTING
20,RESEARCH
30,SALES
40,OPERATIONS
通过上面的两个例子，我们可以将：
setechooff
setfeedbackoff
setnewpagenone
setpagesize5000
setlinesize500
setverifyoff
setpagesize0
settermoff
settrimson
setlinesize600
setheadingoff
settimingoff
setverifyoff
setnumwidth38
SPOOLc:
\具体的文件名
你要运行的sql语句
SPOOLOFF
作为一个模版，只要将必要的语句加入这个模版就可以了。
在oracle的较新版本中，还可以用setcolsep命令来实现上面的功能：
SQL>setcolsep,
SQL>select*fromdept;
10,ACCOUNTING,NEWYORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
35,aa,bb
3．动态生成spool命令所需的文件名
在我们上面的例子中，spool命令所需要的文件名都是固定的。
有时我们需要每天spool一次，并且每次spool的文件名都不相同，如文件名包含当天的日期，该如何实现呢？
columndat1new_valuefilename;
selectto_char（sysdate,'yyyymmddhh24mi'）dat1fromdual;
spoolc:
\&&filename..txt
select*fromdept;
spooloff;
4．如何从脚本文件中得到WINDOWS环境变量的值：
在windos中：
spoolc:
\temp\%ORACLE_SID%.txt
select*fromdept;
...
spooloff
在上面的例子中，通过%ORACLE_SID%的方式引用环境变量ORACLE_SID的值，如果ORACLE_SID的值为orcl，则生成的spool文件名为：
orcl.txt
在UNIX中：
spoolc:
\temp\$ORACLE_SID.txt
select*fromdept;
...
spooloff
在上面的例子中，通过$ORACLE_SID的方式引用环境变量ORACLE_SID的值，如果ORACLE_SID的值为orcl，则生成的spool文件名为：
orcl.txt
5．如何指定缺省的编辑脚本的目录
在sql*plus中，可以用save命令，将上一条执行的sql语句保存到一个文件中，但是如何设置该文件的缺省目录呢？
通过SQL>seteditfilec:
\temp\file.sql命令，可以设置其缺省目录为c:
\tmpe，缺省文件名为file.sql。
6．如何除去表中相同的行
找到相同的行：
SELECT*FROMdepta
WHEREROWID<>（SELECTMAX（ROWID）
FROMdeptb
WHEREa.deptno=b.deptno
ANDa.dname=b.dname--Makesureallcolumnsarecompared
ANDa.loc=b.loc）;
注释：
如果只找deptno列相同的行，上面的查询可以改为：
SELECT*FROMdepta
WHEREROWID<>（SELECTMAX（ROWID）
FROMdeptb
WHEREa.deptno=b.deptno）
删除相同的行：
DELETEFROMdepta
WHEREROWID<>（SELECTMAX（ROWID
FROMdeptb
WHEREa.deptno=b.deptno
ANDa.dname=b.dname--Makesureallcolumnsarecompared
ANDa.loc=b.loc）;
注意：
上面并不删除列值为null的行。
7．如何向数据库中插入两个单引号（’’）
Insertinotdeptvalues（35,’aa’’’’bb’,’a’’b’）;
在插入时，用两个’表示一个’。
8．如何设置sql*plus的搜寻路径，这样在用@命令时，就不用输入文件的全路径。
设置SQLPATH环境变量。
如：
SQLPATH=C:
\ORANT\DBS;C:
\APPS\SCRIPTS;C:
\MYSCRIPTS
9．@与@@的区别是什么？
@等于start命令，用来运行一个sql脚本文件。
@命令调用当前目录下的，或指定全路径，或可以通过SQLPATH环境变量搜寻到的脚本文件。
@@用在脚本文件中，用来指定用@@执行的文件与@@所在的文件在同一目录，而不用指定全路径，也不从SQLPATH环境变量指定的路径中寻找文件，该命令一般用在嵌套脚本文件中。
10．&与&&的区别
&用来创建一个临时变量，每当遇到这个临时变量时，都会提示你输入一个值。
&&用来创建一个持久变量，就像用用define命令或带new_vlaue字句的column命令创建的持久变量一样。
当用&&命令引用这个变量时，不会每次遇到该变量就提示用户键入值，而只是在第一次遇到时提示一次。
如，将下面三行语句存为一个脚本文件，运行该脚本文件，会提示三次，让输入deptnoval的值：
selectcount（*）fromempwheredeptno=&deptnoval;
selectcount（*）fromempwheredeptno=&deptnoval;
selectcount（*）fromempwheredeptno=&deptnoval;
将下面三行语句存为一个脚本文件，运行该脚本文件，则只会提示一次，让输入deptnoval的值：
selectcount（*）fromempwheredeptno=&deptnoval;
selectcount（*）fromempwheredeptno=&deptnoval;
selectcount（*）fromempwheredeptno=&deptnoval;
11．引入copy的目的
Copy命令在两个数据库之间拷贝数据时特别有用，特别是该命令可以在两个数据库之间传递long型字段的数据。
缺点：
在两个数据库之间传递数据时，有可能丢失精度（loseprecision）。
12．为什么在修改大量的行时，我的脚本会变得很慢？
当通过PL/SQL块修改一个表中的许多行时，你会创建在表上创建一个cursor，但是只有在你关闭cursor时，才会释放ROLLBACKSEGMENT，这样，当cursor仍然打开时，修改过程会变慢，这是因为数据库不得不搜寻大量的rollbacksegment以便于维护读一致性。
为了避免这样情况，试着在表上加一个标志字段来描述该行是否已经被修改，然后关闭该cursor，然后再打开该cursor。
每次可以修改5000行.
升级会员 