sqplus用法总结

sqlplus help -
用法: SQLPLUS [ [ ] [ ] [ ] ]
其中 ::= -H | -V | [ [-M ] [-R ] [-S] ]
<登录> ::= <用户名>[/<口令>][@] | / | /NOLOG
<启动> : : = @<文件名>[.] [<参数> ...]
"-H" 显示 SQL*Plus 的版本标帜和使用语法
"-V" 显示 SQL*Plus 的版本标帜
"-M " 使用 HTML 标志选项
"-R " uses restricted mode
"-S" uses silent mode
eg:
 sqlplus username/password@DBname

==================================================================

　SQL*Plus系统环境变量有哪些？如何修改？

show和set命令是两条用于维护SQL*Plus系统变量的命令
SQL> show all --查看所有70个系统变量值
SQL> show user --显示当前连接用户
SQL> show error --显示错误
SQL> show xxx --显示xxx参数信息
SQL> set heading off --禁止输出列标题，默认值为ON
SQL> set feedback off --禁止显示最后一行的计数反馈信息，默认值为"对6个或更多的记录，回送ON"
SQL> set timing on --默认为OFF，设置查询耗时，可用来估计SQL语句的执行时间，测试性能
SQL> set sqlprompt "SQL> " --设置默认提示符，默认值就是"SQL> "
SQL> set linesize 1000 --设置屏幕显示行宽，默认100
SQL> set autocommit ON --设置是否自动提交，默认为OFF
SQL> set pause on --默认为OFF，设置暂停，会使屏幕显示停止，等待按下ENTER键，再显示下一页
SQL> set arraysize 1 --默认为15
SQL> set long 1000 --默认为80，设置1000是为了显示更多的内容，因为很多数据字典视图中用到了long数据类型
SQL> define a ='''20000101 12:01:01''' --定义局部变量，如果想用一个类似在各种显示中所包括的回车那样的常量，可以用define命令来设置
SQL> select &a from dual;
原值 1: select &a from dual
新值 1: select '20000101 12:01:01' from dual
'2000010112:01:01
-----------------
20000101 12:01:01

SQL*Plus中缓冲区命令列表

命令              缩写              作用
----------------  ----------------  -------------------------------------------------------
append 文本       A 文本            --添加文本到当前行尾
change/旧文/新文  C/旧文本/新文本   --在当前行用新的文本替换旧的文本
change/文本       C/文本            --从当前行删除文本
del                                 --删除当前行
del n                               --删除第n行
del n m                             --删除第n到m行
del n *                             --删除第n到当前行
del n last                          --删除第n行到最后一行
del *                               --删除当前行
Del * n                             --删除当前行到第n行
Del * last                          --删除当前行到最后一行
Del last                            --删除最后一行
input             I                 --在当前行之后添加多行，input会提示输入每一行
input 文本        i 文本            --在当前行之后添加一行
List              L                 --显示缓冲区中所有行
List n            L n               --显示缓冲区中第n行
List n m          L n m             --显示缓冲区中第n到第m行
List n *          L n *             --显示缓冲区中第n行到当前行
List n            last  L n last    --显示缓冲区中第n行到最后一行
List *            L *               --显示缓冲区中当前行
List * n          L * n             --显示缓冲区中当前行到第n行
List * last       L * last          --显示缓冲区中当前行到最后一行
List last         L last            --显示缓冲区中最后一行
Run               R                 --显示并执行当前缓冲区的命令，并使缓冲区的最后一行成为当前行
/                                   --执行当前缓冲区的命令，不显示缓冲区命令
行号                           --设置缓冲区的某一行为当前行

sqlplus下得到执行计划和统计数据的两种方法

SQL> connect /as sysdba
SQL> @?/rdbms/admin/utlxplan.sql  --建立plan_table表
SQL> create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;
SQL> @?/sqlplus/admin/plustrce.sql  --建立plustrace角色
SQL> grant plustrace to public;
在有了plan_table表和plustrace角色的情况下：
1、先explain plan for sql_stmt; 然后 @?/RDBMS/ADMIN/utlxpls 看执行计划
2、先set autotrace traceonly 然后直接执行sql_stmt;会自动显示执行计划和统计信息。
set autotrace 选项
on            显示查询结果，执行计划，统计数据
on statistics 显示查询结果，统计数据，不显示执行计划
on explain    显示查询结果，执行计划，不显示统计数据
traceonly     显示执行计划和统计结果，但不包括查询结果
traceonly statistics 仅显示统计数据

有时utlxplan.sql执行成功，但发现plustrce.sql找不到，但此时用SYSDBA是可以打开跟踪的，但用我的用户wwm却无法打开跟踪。提示
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
初步判断，因为SYSDBA可以打开，但其他用户无法打开，是权限问题，于是查找资料。用以下语句解决
SQL> connect sys/pass@xxx as sysdba
SQL> create role wwmtrace;
SQL> grant select on v_$sesstat to wwmtrace;
SQL> grant select on v_$statname to wwmtrace;
SQL> grant select on v_$mystat to wwmtrace;
SQL> grant wwmtrace to wwm;
SQL> connect wwm/pass
SQL> set autotrace on
无错误提示，测试正确
注：8i版本通过如下方式解决:
drop role wwmtrace;
create role wwmtrace;                                                                            
grant select on v_$sesstat to wwmtrace;
grant select on v_$statname to wwmtrace;
grant select on v_$session to wwmtrace;
grant wwmtrace to your_user;

一个符合个人使用习惯的sqlplus登陆环境:

cd $ORACLE_HOME/sqlplus/admin
vi glogin.sql
增加以下内容
-----------------------------------------------------------
define_editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
select lower(user)||'@'||global_name global_name from global_name;
set sqlprompt '&gname>'
set termout on
-----------------------------------------------------------

scott用户下emp/dept/salgrade/bonus/dummy五张表建立脚本:

SQL> @?/sqlplus/demo/demobld.sql 重建脚本
SQL> @?/sqlplus/demo/demodrop.sql 删除脚本

如果通过回车控制逐页查看数据(假如数据太多)
set pause on
set pagesize 100
每显示100-3=97行数据暂停一下!

sqlplus里出报表:
compute sum of bytes on pool
break on pool skip 1
select pool,name,bytes from v$sgastat order by 1,2;

通过sqlplus向shell传递变量

$ cat a.sh
sqlplus -S "/ as sysdba" << !
set heading off
col today noprint
column today new_val dat
select to_char( sysdate, 'yyyy-mm-dd') today from dual;
host echo 'today is ' &dat
exit;
exit;
!
$ ./a.sh
today is  2005-04-11

sqlplus里出html和excel报表

vi gettables.sql
--------------------------------------------------
select owner,table_name,tablespace_name,blocks,last_analyzed
from all_tables order by 1,2;
--------------------------------------------------
vi main.sql
--------------------------------------------------
set linesize 200
set term off verify off feedback off pagesize 999
set markup html on entmap ON spool on preformat off
spool tables.xls
@gettables.sql
spool off
exit
--------------------------------------------------
执行:
--------------------------------------------------
$ sqlplus "/ as sysdba" @main
--------------------------------------------------
把main.sql脚本中的，spool tables.xls更改为spool tables.htm,我们可以获得htm格式输出

一个sqlplus的小技巧

在sql*plus中敲命令的过程中, 想不起某个列的名字, 如果取消当前的命令,待查询后再重敲, 那太痛苦了. 当然你可以另开一个sql*plus窗口进行查询, 但下面提供的方法更简单.比如说, 你想查名称带"xxx"的业务信息, 输入了下面的语句:
SQL> select icpcode,icpservid
from service
where
这时, 你发现你想不起来业务名称的列名是什么了.这种情况下, 只要在下一行以#开头, 就可以执行一条sql*plus命令, 执行完后, 刚才的语句可以继续输入
SQL> select icpcode,icpservid
from service
where
#desc service
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ICPCODE                                   NOT NULL VARCHAR2(12)
ICPSERVID                                 NOT NULL VARCHAR2(10)
SERVNAME                                  NOT NULL VARCHAR2(64)
3 servname like '%xxx%';

用sqlplus只输出数据到文本文件的实例

$ vi call.sql
Prompt ***********************************
Prompt sql_file = &1
Prompt spool_file = &2
Prompt "Exit:Ctrl+c; Continue:Enter."
ACCEPT choice CHAR PROMPT 'Please confirm your input:'
set feedback off pages 0 lines 30000 trims on trim on ver off hea off time off timing off autot off echo off show off term off
spo &2
@@&1
spo off
set feedback 6 pages 14 lines 80 trims off ver on hea on term on
Prompt Spool to &2 successful!
Prompt ***********************************
$ vi exec.sql
SELECT * FROM tab where rownum < 11;
$ sqlplus wacos/oss@orcl
SQL> @call exec.sql output.lst
SQL> !more output.lst
注意:两个@@表示执行的sql文件和当前文件在同一个目录中

自定义一个符合习惯的基于DOS的sqlplus命令行

由于工作原因,经常要登陆到各个oracle环境解决问题,设置一个实用的sqlplus命令行环境是必要的:
一.首先设置dos窗口最大化,并且能显示尽量多的信息
--cmd窗口布局layout设置
windows任务栏为两栏情况下
Screen Buffer Size:
Width:1250
Height:420
Window Size:
Width:125
Height:42
Window position:
Left:-4
Top:-4
Let System position window:为空
二.在C:/Documents and Settings/yourWindowLoginUserName下新建set.sql:
set serveroutput on size 1000000
set trimspool on
set long 5000
--linesize过大了,使用desc命令显示结果格式很差,set linesize 1后再用desc.
set linesize 10000
set pagesize 42
column plan_plus_exp format a80
column global_name new_value gname
set termout off
select lower(user)||'@'||substr(global_name,1,instr(global_name,'.')-1) global_name from global_name;
set sqlprompt '&gname>'
set termout on
set pause on
三.在C:/Documents and Settings/yourWindowLoginUserName下新建sql33.bat:
sqlplus wacos/oss@orcl.33 @set
四.以后进入dos窗口后,直接敲sql33就进入sqlplus命令行窗口了.
这样以后就单击快捷方式就进入sqlplus命令行界面,该界面的特点是:
窗口最大化,显示尽量多信息
每行记录在一行显示
每显示一屏信息即暂停,按Enter继续显示下一屏
还有其它set.sql里设置好的各种属性