0

Oracle的SQL*Plus工具

eygle 2019-09-09
422

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所示。


企业微信截图_15680207184327.png

图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所示。


企业微信截图_15680207846541.png

图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所示。

 

企业微信截图_15680208602589.png

图2-28  显示的结果


3. 启动iSQL*Plus


通过在浏览器的地址栏中输入http://hostname:port/isqlplus,就可以启动iSQL*Plus,如图2-29所示。


企业微信截图_15680209107946.png

图2-29  启动iSQL*Plus


输入用户名口令等信息就可以登录到数据库,并执行SQL语句及脚本文件,也可以加载脚本来执行,如图2-30所示。


企业微信截图_1568020947184.png

图2-30  脚本执行示例


4.以SYSDBA的身份登录

以SYSDBA的身份登录,需要在浏览器输入http://machine_name.domain:7778/isqlplusdba地址,此时会弹出如图2-31所示的“输入网络密码”对话框,需要输入HTTP Server的口令,这个不同于数据库认证。


企业微信截图_15680209894979.png

图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所示。


企业微信截图_15680210313263.png

图2-32  登录数据库


以SYSDBA身份,甚至可以通过iSQL*Plus启动和关闭数据库,如图2-33所示。


企业微信截图_15680210627197.png

图2-33  启动数据库


2.2.2 Oracle 10g的iSQL*Plus工具


iSQL*Plus工具在安装软件时可以选择是否安装,一般在选择“可用产品组件”时可以选择是否安装它,如图2-34所示。


企业微信截图_15680211022936.png

图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数据库了,如下右图所示。


企业微信截图_15680211664791.png

图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的身份登录数据库了,如下右图所示。

 

企业微信截图_15680214111002.png

图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我们可以看到输出效果:


企业微信截图_15680216374568.png

图2-37 Excel格式输出


把main.sql脚本中的spool tables.xls更改为spool tables.htm,我们可以获得HTM格式输出,效果如图2-38:


企业微信截图_15680216782672.png

图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


「喜欢文章,快来给作者赞赏墨值吧」
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

关注
最新发布
暂无内容,敬请期待...
数据库资讯
最新 热门 更多
本月热门
近期活动
全部
暂无活动,敬请期待...
相关课程
全部