Oracle的SQL*Plus工具
Oracle的另外一个传统工具是SQL*Plus，SQL*Plus可能是Oracle应用最广泛的数据库工具，在Linux/UNIX环境下，通常都是通过这个工具来启动、关闭和维护数据库的。SQL*Plus以其小巧、快捷一直为众多DBA们所喜爱；当然为了格式化更友好地输出，在SQL*Plus中我们往往不得不设置大量的格式化代码。
从Oracle 9i开始，Oracle提供了基于Web方式展现的SQL*Plus工具，也就是iSQL*Plus。iSQL*Plus是基于三层的架构，可以被安装在Oracle服务器上，如果服务器上配置了iSQL*Plus Server，那么客户端只需要浏览器就可以连结并管理Oracle数据库，这就省略了以前客户端的安装，大大简化和方便了数据库管理，并且新提供的Web方式展现的iSQL*Plus具有更加友好的用户界面。
2.2.1 Oracle 9i的iSQL*Plus
iSQL*Plus是基于三层结构设计的，在配置应用中，其Client、Middle、Server可以位于同一台机器上也可以位于不同的机器；Oracle试图通过iSQL*Plus的推广，逐渐取代SQL*Plus工具，也许这个Web方式的工具更容易为用户所接受，但是在字符界面，SQL*Plus始终是不可或缺的管理工具。
iSQL*Plus三层模型包括了客户端（iSQL*Plus用户界面，通常是Web浏览器）、中间层（iSQL*Plus Server、Oracle Net和Oracle HTTP Server）和数据库层（Oracle 9i），其架构如图2-26所示。
图2-26 iSQL*Plus三层模型
这三层可位于同一台计算机上，也可位于不同的计算机上。然而iSQL*Plus Server必须与Oracle HTTP Server位于同一台计算机上。中间层负责协调客户端和数据库层之间的交互和资源。数据库层是Oracle 9i，可以通过Oracle Net进行访问。
以下简单介绍一下iSQL*Plus的配置和使用。
1. 启动服务器上的HTTP Server
通过“开始”菜单中的快捷命令“Start HTTP Server powered by Apache”，可以启动HTTP Server，如图2-27所示。
图2-27 启动HTTP Server
2. 通过浏览器访问HTTP服务器
一般缺省设置的端口及协议是（不同版本/不同平台端口可能不同）：HTTP 7778 端口 / HTTPS 4443端口。
对于非默认配置，可以直接到$ORACLE_HOME/Apache/Apache目录下查看ports.ini文件，其中记录了端口配置信息：
[Ports]
s_apachePort = 80
s_apachePortSSL = 443
s_jservPort = 8007
Apache Server的详细配置信息可以在$ORACLE_HOME/Apache/Apache/conf目录下文件httpd.conf中找到：
# Port: The port to which the standalone server listens.
Port 7778
## When we also provide SSL we have to listen to the
## standard HTTP port (see above) and to the HTTPS port
Listen 7778
Listen 4443
本例设置http://ggqiang:7778和https://ggqiang:4443，在浏览器地址栏中输入这两个地址后，显示的结果如图2-28所示。
图2-28 显示的结果
3. 启动iSQL*Plus
通过在浏览器的地址栏中输入http://hostname:port/isqlplus，就可以启动iSQL*Plus，如图2-29所示。
图2-29 启动iSQL*Plus
输入用户名口令等信息就可以登录到数据库，并执行SQL语句及脚本文件，也可以加载脚本来执行，如图2-30所示。
图2-30 脚本执行示例
4.以SYSDBA的身份登录
以SYSDBA的身份登录，需要在浏览器输入http://machine_name.domain:7778/isqlplusdba地址，此时会弹出如图2-31所示的“输入网络密码”对话框，需要输入HTTP Server的口令，这个不同于数据库认证。
图2-31 输入网络密码
需要先创建Apache的认证口令文件，并初始化一个用户：
D:\oracle\ora92\Apache\Apache\bin>htpasswd D:/oracle/ora92/sqlplus/admin/iplusdba.pw admin
Automatically using MD5 format on Windows.
New password: *****
Re-type new password: *****
Adding password for user admin
然后可以用这个用户通过身份认证，通过Apache的系统认证之后就可以以SYSDBA身份登录数据库，如图2-32所示。
图2-32 登录数据库
以SYSDBA身份，甚至可以通过iSQL*Plus启动和关闭数据库，如图2-33所示。
图2-33 启动数据库
2.2.2 Oracle 10g的iSQL*Plus工具
iSQL*Plus工具在安装软件时可以选择是否安装，一般在选择“可用产品组件”时可以选择是否安装它，如图2-34所示。
图2-34 选择是否安装iSQL*Plus工具
在Windows上，Oracle 10g的iSQL*Plus还增加了一个系统服务，启动该服务后就可以登录iSQL*Plus，或者可以通过以下命令启动和停止该服务：
isqlplusctl start
isqlplusctl stop
在命令行启动过程如下：
C:\>isqlplusctl start
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Starting iSQL*Plus ...
iSQL*Plus started.
通过查看$ORACLE_HOME/install/portlist.ini文件，可以找到当前的iSQL*Plus Application Server的监听端口，默认是5560，以下是我的portlist文件示范：
iSQL*Plus HTTP 端口号 =5560
Enterprise Manager Console HTTP 端口 (eygle) = 1158
Enterprise Manager 代理端口 (eygle) = 3938
打开浏览器，在地址栏中输入地址http://hostname:5560/isqlplus就可以访问iSQL*Plus了，如图2-35所示。输入用户名口令及连接标识符就可以连接相应的Oracle数据库了，如下右图所示。
图2-35 访问iSQL*Plus
如果要修改iSQL*Plus的默认端口，可以修改其配置文件。该配置文件为$ORACLE_HOME/oc4j/j2ee/isqlplus/config/http-web-site.xml，相应的配置为：
<web-site port="5560" display-name="OC4J Java HTTP Web Site">
修改这一端口即可变更iSQL*Plus的端口号。
默认情况下，SYSDBA用户无法通过iSQL*Plus登入；如果要以SYSDBA身份登入iSQL*Plus，则需要通过类似下面的网址登录http://hostname:5560/isqlplus/dba。输入该网址后首先会弹出一个登录框，要求先输入iSQL*Plus DBA的用户和密码，同Oracle 9i类似，这里输入的不是数据库用户，而是iSQL*Plus应用服务器的用户和密码。
要以DBA身份登录iSQL*Plus，必须先配置好OC4J用户。OC4J可以使用两种身份认证方式：基于XML配置文件（jazn-data.xml）和基于LDAP（Oracle Internet Directory）。
通常采用第一种方式进行认证，XML配置文件位于：$ORACLE_HOME/oc4j/j2ee/isqlplus/ application-deployments/isqlplus/config。
该文件包含登录用户及口令，密码是加密后存储的，不能手工修改；要想修改该文件内容，需要通过JAZN（Java AuthoriZatioN）工具来进行配置。JAZN是Oracle提供的JAAS（Java Authentication and Authorization Service）工具，JAAS实施了一个Java版的PAM（Pluggable Authentication Module）架构，支持基于用户的认证管理。
通过JAZN，可以完成包括创建用户、授权等工作；这些工作，可以登入JAZN命令环境后执行，也可以通过命令行输入单条完整的命令实现。
启动JAZN命令环境可以参考如下步骤：
1.在命令行进入相关目录
具体路径为$ORACLE_HOME/oc4j/j2ee/isqlplus/application-deployments/isqlplus/
C:\Oracle\10.2.0\oc4j\j2ee\isqlplus\application-deployments\isqlplus>
2.确认JAVA_HOME环境变量指向了正确的JDK
JDK需要1.4版本以上，可以使用Oracle自带的JDK，位于$ORACLE_HOME/jdk。
C:\Oracle\10.2.0\jdk\bin>java -version
java version "1.4.2_08"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_08-b03)
Java HotSpot(TM) Client VM (build 1.4.2_08-b03, mixed mode)
也可以使用系统其他满足版本要求的JDK：
C:\Oracle\10.2.0\oc4j\j2ee\isqlplus\application-deployments\isqlplus>which java
C:\WINDOWS\system32\java.exe
C:\Oracle\10.2.0\oc4j\j2ee\isqlplus\application-deployments\isqlplus>java -version
java version "1.5.0_08"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_08-b03)
Java HotSpot(TM) Client VM (build 1.5.0_08-b03, mixed mode, sharing)
注意：这里的which命令并非Windows系统自带，来自其他工具增强。
3. 执行以下命令：
java -Djava.security.properties=$ORACLE_HOME/sqlplus/admin/iplus/provider -jar $ORACLE_HOME/oc4j/j2ee/home/jazn.jar -user "iSQL*Plus DBA/admin" -password welcome –shell
在Windows上，执行结果如下：
C:\Oracle\10.2.0\oc4j\j2ee\isqlplus\application-deployments\isqlplus>java -Djava.security.properties=c:\oracle\10.2.0\sqlplus\admin\iplus\provider –jar \
c:\oracle\10.2.0\oc4j\j2ee\home\jazn.jar -user "iSQL*Plus DBA/admin" -password welcome -shell
JAZN:>
其中realm=iSQL*Plus DBA，user=admin，这些可以从XML配置文件中看到：
<jazn-realm>
<realm>
<name>iSQL*Plus DBA</name>
<users>
<user>
<name>admin</name>
<display-name>Realm Administrator</display-name>
<description>Administrator for this realm.</description>
<credentials>{903}ubtKEVZPHqBXthQMG2Pi5mnwWzjLlPHX</credentials>
</user>
</users>
<roles>
<role>
<name>admin</name>
<display-name>Realm Admin Role</display-name>
<description>Administrative role for this realm.</description>
<members>
<member>
<type>user</type>
<name>admin</name>
</member>
</members>
</role>
<role>
<name>webDba</name>
<members>
</members>
</role>
</roles>
</realm>
</jazn-realm>
admin用户的默认密码是welcome，但是admin用户默认没有webDba权限，不能直接用于登录iSQL*Plus。
提示：以上命令需要进入第一步的目录后再执行，否则会报错：
oracle.security.jazn.JAZNRuntimeException: Configuration file "configjazn.xml" does not exist. Check your JAAS configuration settings.
或者
Realm [iSQL*Plus DBA] does not exist in system.
进入了JAZN命令环境之后，我们就可以执行一系列的管理任务，如创建用户、授权等。
（1）查看现有用户：
JAZN:> listusers
iSQL*Plus DBA/admin
JAZN:> listusers "iSQL*Plus DBA"
admin
（2）增加新用户：
JAZN:> adduser "iSQL*Plus DBA" eygle eygle
JAZN:> listusers "iSQL*Plus DBA"
admin
eygle
（3）授予webDba角色：
JAZN:> grantrole webDba "iSQL*Plus DBA" eygle
（4）回收权限：
JAZN:> revokerole webDba "iSQL*Plus DBA" eygle
（5）更改口令：
JAZN:> setpasswd "iSQL*Plus DBA" eygle eygle oracle
（6）删除用户：
JAZN:> remuser "iSQL*Plus DBA" eygle
JAZN:> listusers
iSQL*Plus DBA/admin
熟悉了这几个常用命令之后，最后再创建一个用户eygle，设置口令为oracle，并授予webDba角色：
JAZN:> adduser "iSQL*Plus DBA" eygle oracle
JAZN:> grantrole webDba "iSQL*Plus DBA" eygle
JAZN:> listusers "iSQL*Plus DBA"
admin
eygle
接下来需要重新启动iSQL*Plus应用服务器：
C:\Oracle\10.2.0\BIN>isqlplusctl stop
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Stopping iSQL*Plus ...
iSQL*Plus stopped.
C:\Oracle\10.2.0\BIN>isqlplusctl start
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Starting iSQL*Plus ...
iSQL*Plus started.
再打开浏览器，输入网址http://hostname:port/isqlplus/dba ，如图2-36左图所示。此时弹出“连接到gqgai”对话框，提示要求输入用户名及密码，我们输入刚才创建的用户及口令，认证通过之后就可以以SYSDBA的身份登录数据库了，如下右图所示。
图2-36 iSQL*Plus的认证页面
2.2.3 SQL*Plus的使用
虽然图形化的管理工具越来越多，易用性也越来越好，但是SQL*Plus的地位一直不可动摇，至今仍是DBA最为常用的数据库管理工具。本节将介绍一些DBA需要了解的SQL*Plus工具的常用技巧和功能。
2.2.3.1 查看SQL的执行计划
通过SQL*Plus中的Autotrace功能，可以查看SQL的执行计划，这在进行SQL诊断和跟踪是极其方便和有效的。在Oracle 9i之中，启用SQL*Plus的Autotrace功能可以参考如下步骤：
SQL> connect / as sysdba
已连接。
SQL> @?\rdbms\admin\utlxplan --这里创建了plan_table表
表已创建。
SQL> create public synonym plan_table for plan_table; --这里创建公用同义词
同义词已创建。
SQL> grant all on plan_table to public ; --使所有用户可以共享plan_table表
授权成功。
SQL> @?\sqlplus\admin\plustrce --创建查看执行计划必要的plustrace角色
SQL> drop role plustrace;
drop role plustrace
*
ERROR 位于第 1 行:
ORA-01919: 角色'PLUSTRACE'不存在
SQL> create role plustrace;
角色已创建
SQL> grant select on v_$sesstat to plustrace;
授权成功。
SQL> grant select on v_$statname to plustrace;
授权成功。
SQL> grant select on v_$session to plustrace;
授权成功。
SQL> grant plustrace to dba with admin option;
授权成功。
SQL> set echo off
SQL> grant plustrace to public ; -- 将plustrace授给public，以便所有用户可以获得
授权成功。
从Oracle 10g开始，plan_table已经缺省的被创建，实现了全局共享，简化了Autotrace的使用。当用户被授予了plustrace权限之后，即可使用Autotrace的功能。
Autotrace几个常用的选项是：
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN --------- AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS ------ 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ---------- 同SET AUTOTRACE ON，但是不显示查询输出这个功能可以帮助我们获得关于SQL的执行印象：
SQL> create table eygle as select * from dict;
SQL> desc eygle
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)
SQL> set autotrace trace explain
SQL> select count(table_name) from eygle;
Execution Plan
----------------------------------------------------------
Plan hash value: 3602634261
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 4 (0) | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
| 2 | TABLE ACCESS FULL | EYGLE| 660 | 11220 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------
SQL> create index idx_tname on eygle(table_name);
Index created.
SQL> set linesize 120
SQL> select count(table_name) from eygle;
Execution Plan
----------------------------------------------------------
Plan hash value: 3338774945
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0) | 00:00:01|
| 1 | SORT AGGREGATE | | 1 | 17 | | |
| 2 | INDEX FAST FULL SCAN| IDX_TNAME | 660 | 11220 | 3 (0) | 00:00:01|
-----------------------------------------------------------------------------------
2.2.3.1 生成HTML的页面输出
当我们使用OEM或iSQL*Plus进行查询或数据访问时，直观的感受是输出界面友好，其实通过SQL*PLUS完全可以构建友好的输出，满足多样化用户需求。本例通过简单示例，介绍通过SQL*plus输出XLS，HTML两种格式文件。
首先创建两个脚本:
1.main.sql 用以设置环境，调用具体功能脚本
2.get_tables.sql 为实现具体功能之脚本
通过这样两个脚本可以避免spool中的冗余信息，获得友好的输出显示，示例如下:
1.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
@get_tables.sql
spool off
exit
2.get_tables.sql脚本:
select owner,table_name,tablespace_name,blocks,last_analyzed
from all_tables order by 1,2;
3.执行并获得输出:
[oracle@jumper utl_file]$ sqlplus "/ as sysdba" @main
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Apr 25 10:30:11 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
[oracle@jumper utl_file]$ ls -l tables.xls
-rw-r--r-- 1 oracle dba 69539 Apr 25 10:30 tables.xls
此处输出为XLS文件，通过图2-37我们可以看到输出效果:
图2-37 Excel格式输出
把main.sql脚本中的spool tables.xls更改为spool tables.htm,我们可以获得HTM格式输出，效果如图2-38:
图2-38 查询结果的HTM格式输出
通过SQL*Plus的这一特性和功能，我们就能够对数据库报表生成丰富的展现。
2.2.3.1 常用的SET选项
在使用SQL*Plus管理数据库时，我们常常希望更改提示符，增加其他提示信息，以防止不必要的误操作等。在Oracle10g中，这些设置变得简化。
SET命令可以帮助我们轻松完成这些设置，例如用户名及身份权限：
SQL> set sqlprompt "_user _privilege> "
SYS AS SYSDBA> select * from dual;
D
-
X
设置显示登录服务器的信息：
SYS AS SYSDBA> set sqlprompt "_user _privilege @ _connect_identifier>"
SYS AS SYSDBA @ enmo>
增加时间显示：
SYS AS SYSDBA @ enmo>set sqlprompt "_user _privilege on _date @ _connect_identifier>"
SYS AS SYSDBA on 15-SEP-10 @ enmo>
对于本地服务器的登录，可以将需要设定的内容保存在$ORACLE_HOME/sqlplus/ glogin.sql文件中，就可以每次调用相应的设定显示了。
在执行SQL查询输出时，通常缺省的列分隔符是空格，我们可以通过SET命令指定自定义的分隔符：
SQL>set linesize 120
SQL>set colsep |
SQL>select username,password,default_tablespace from dba_users
2 where rownum <10;
USERNAME |PASSWORD |DEFAULT_TABLESPACE
----------------------------|----------------------------|--------------------
SYSTEM |2D594E86F93B17A1 |SYSTEM
SYS |8A8F025737A9097A |SYSTEM
SCOTT |F894844C34402B67 |USERS
EYGLE |B726E09FE21F8E83 |USERS
MGMT_VIEW |8AD629A4412A591E |SYSTEM
OUTLN |4A3BA55E08595C81 |SYSTEM
DBSNMP |E066D214D5421CCC |SYSAUX
OLAPSYS |invalid |SYSAUX
SI_INFORMTN_SCHEMA |84B8CBCA4D477FA3 |SYSAUX
而如果需要查看SQL的执行时间，则可以通过简单的一句set timing on来实现：
SQL>set timing on
SQL>select user,sysdate ,systimestamp from dual;
USER |SYSDATE |SYSTIMESTAMP
---------|-----------|--------------------------------------
SYS |15-SEP-10 |15-SEP-10 10.28.07.621000 PM +08:00
Elapsed: 00:00:00.05
