SQL*PLUS使用技巧

在ORACLE的维护过程中，目前ORACLE有很多易用的客户端工具，如PL/SQL、DEVELOPER、TOAD，但作为数据库管理员也需要经 常和SQLPLUS 打交道，一些常用的操作往往需要在SQLPLUS完成，如果SQLPLUS不熟悉也在很大程度上降低我们的维护效率，本文主要介绍一些在管理数据库过程中比较有用的的SQLPLUS技巧。

在unix系统中，可以通过帮助查看SQLPLUS命令用法：sqlplus -help

一、启动参数设置

SQLPLUS程序在启动情况下可以通过设置相关参数，让其执行login.sql，然后通过修改login.sql脚本可以定义一些语句，达到修改SQLPLUS的提示符的目的，便于识别，login.sql的文件路径需要通过操作系统环境变量SQLPATH进行设置：

WINDOWS环境下，通过修改注册表项目在SQLPATH（位置一般在ORACLE-HOME下）在注册表中，可以设置为D:\oracle\ora92\dbs

UNIX的可以直接在ORACLE用户的profile文件中加入SQLPATH环境变量，可以设置exportSQLPATH=/home/oracle/app/oracle/product/9.2/dbs；具体的login.sql，如下所示，可以根据需要 实际修改：

set serveroutputon size 1000000

column old_namenew_value pname

set termout off

select lower(user)|| '@' ||instance_name old_name from v$INSTANCE;

set sqlprompt'&pname> '

set termout on

通过这样修改每次通过SQLPLUS登陆系统的时候会显示username@instancname>更好地识别所要连接的数据库。注意：连接用户必须要有查看v$INSTANCE的权限。

二、在SQLPLUS下执行操作系统命令

在使用SQLPLUS的过程中，经常要执行一些操作系统方面的命令，可以通过两种方法：

1) 在SQLPLUS命令行下执行host命令，可以切换到操作系统命令行下，执行完后exit可以切换回SQLPLUS命令行下。

2) 直接在SQLPLUS 下执行敲一个”!”后面跟上具体的命令行（但在windows下不支持）。

三、在SQLPLUS修改sql语句

使用SQLPLUS的过程中也经常需要修改sql语句，但是SQLPLUS下的修改sql语句的命令过于烦琐不好记忆，可以通过定义相关变量达到很好的效果。

1)      在UNIX 下可以定义define_editor=vi,如果需要修改sql语句，直接在命令行下用edit就可以进入vi环境，和vi操作功能一样，方便操作，设置办法直接在sqlplus的命令行下敲：

Define_editor=vi；

在windows下可以定义define_editor=notepad，如果需要修改sql语句，直接在命令行下用edit就可以进入notepad编写环境，windows下操作就方便多了；

四、在SQLPLUS下查看sql语句的执行时间情况

1) 可以通过直接在sqlplus命令行下执行命令set time on（打开sql语句的执行时间显示）;

21:06:26oldbill@BILL>set time on
21:06:32 oldbill@BILL>
21:06:34 oldbill@BILL>set time off
oldbill@BILL>
oldbill@BILL>set time on
21:06:43 oldbill@BILL>
21:06:44 oldbill@BILL>

2) 在sqlplus命令行下执行命令 set timing on（统计sql语句的执行时间）；

21:07:13oldbill@BILL>set timing on
21:07:16 oldbill@BILL>
21:07:17 oldbill@BILL>select sysdate from dual;

SYSDATE
--------------
27-9月 -11

已用时间:  00: 00: 00.01
21:07:23 oldbill@BILL>

五、在SQLPLUS下查看SQL语句的执行计划

执行$ORACLE_HOME/rdbms/admin/utlxplan.sql创建计划表

执行$ORACLE_HOME/sqlplus/admin/plustrce.sql创建plustrace角色

如果想计划表让每个用户都能使用，则

SQL>create public synonymplan_table for plan_table;

SQL> grant all onplan_table to public;

如果想让自动跟踪的角色让每个用户都能使用，则

SQL> grant plustrace topublic;

通过如下语句开启/停止跟踪

SET AUTOTRACE ON |OFF

| ON EXPLAIN | ONSTATISTICS |TRACEONLY | TRACEONLY EXPLAIN

六、相关参数设置

show all --查看所有系统变量值

show user --显示当前连接用户

show error

1). 设置当前session是否对修改的数据进行自动提交 
SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n} 
  
2).在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句 
SQL> SET ECHO {ON|OFF} 
  
3).是否显示当前sql语句查询或修改的行数 
SQL> SET FEED[BACK] {6|n|ON|OFF} 
   默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数 
  
4).是否显示列标题 
SQL> SET HEA[DING] {ON|OFF} 
当set headingoff 时，在每页的上面不显示列标题，而是以空白行代替 
  
5).设置一行可以容纳的字符数 
SQL> SET LIN[ESIZE] {80|n} 
   如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示。 
  
6).设置页与页之间的分隔 
SQL> SET NEWP[AGE] {1|n|NONE} 
当set newpage0 时，会在每页的开头有一个小的黑方框。 
当set newpagen 时，会在页和页之间隔着n个空行。 
当set newpagenone 时，会在页和页之间没有任何间隔。 
  
7).显示时，用text值代替NULL值 
SQL> SET NULL text 
  
8).设置一页有多少行数 
SQL> SET PAGES[IZE] {24|n} 
如果设为0，则所有的输出内容为一页并且不显示列标题 
  
9).是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。 
SQL> SET SERVEROUT[PUT] {ON|OFF}  
在编写存储过程时，我们有时会用dbms_output.put_line将必要的信息输出，以便对存储过程进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。 
  
10).当SQL语句的长度大于LINESIZE时，是否在显示时截取SQL语句。 
SQL> SET WRA[P] {ON|OFF} 
   当输出的行的长度大于设置的行的长度时（用set linesize n命令设置），当setwrap on时，输出行的多于的字符会另起一行显示，否则，会将输出行的多于字符切除，不予显示。 
  
11).是否在屏幕上显示输出的内容，主要用与SPOOL结合使用。 
SQL> SET TERM[OUT] {ON|OFF} 
   在用spool命令将一个大表中的内容输出到一个文件中时，将内容输出在屏幕上会耗费大量的时间，设置set termspool off后，则输出的内容只会保存在输出文件中，不会显示在屏幕上，极大的提高了spool的速度。 
  
12).将SPOOL输出中每行后面多余的空格去掉 
SQL> SET TRIMS[OUT] {ON|OFF}  
    
13)显示每个sql语句花费的执行时间 
set TIMING  {ON|OFF} 
  
14．修改sql buffer中的当前行中，第一个出现的字符串 
C[HANGE] /old_value/new_value 
SQL> l 
   1* select * from dept 
SQL> c/dept/emp 
   1* select * from emp 
  
15．编辑sql buffer中的sql语句 
EDI[T] 
  
16．显示sql buffer中的sql语句，list n显示sql buffer中的第n行，并使第n行成为当前行 
L[IST] [n] 
  
17．在sql buffer的当前行下面加一行或多行 
I[NPUT] 
  
18．将指定的文本加到sql buffer的当前行后面 
A[PPEND] 
SQL> select deptno, 
   2  dname 
   3  from dept; 
     DEPTNO DNAME 
---------- -------------- 
         10ACCOUNTING 
         20RESEARCH 
         30SALES 
         40OPERATIONS 
  
SQL> L 2 
   2* dname 
SQL> a ,loc 
   2* dname,loc 
SQL> L 
   1  select deptno, 
   2  dname,loc 
   3* from dept 
SQL> / 
  
     DEPTNODNAME          LOC 
---------- -------------- ------------- 
         10ACCOUNTING     NEW YORK 
         20RESEARCH       DALLAS 
         30SALES          CHICAGO 
         40OPERATIONS     BOSTON 
  
19．将sql buffer中的sql语句保存到一个文件中 
SAVE file_name 
  
20．将一个文件中的sql语句导入到sqlbuffer中 
GET file_name 
  
21．再次执行刚才已经执行的sql语句 
RUN 
or 
/ 
22．执行一个存储过程 
EXECUTE procedure_name 
  
23．在sql*plus中连接到指定的数据库 
CONNECT user_name/passwd@db_alias 
  
24．设置每个报表的顶部标题 
TTITLE 
  
25．设置每个报表的尾部标题 
BTITLE 
  
26．写一个注释 
REMARK [text] 
  
27．将指定的信息或一个空行输出到屏幕上 
PROMPT [text] 
  
28．将执行的过程暂停，等待用户响应后继续执行 
PAUSE [text] 
  
Sql>PAUSE Adjust paper and press RETURN tocontinue. 
  
29．将一个数据库中的一些数据拷贝到另外一个数据库（如将一个表的数据拷贝到另一个数据库） 
COPY {FROM database | TO database | FROM databaseTO database} 
{APPEND|CREATE|INSERT|REPLACE} destination_table 
[(column, column, column, ...)] USING query