在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