第3章 Oracle管理工具

1、第3章 Oracle管理工具【教学目标】【教学目标】 1.掌握SQL Plus的启动和断开连接的方法。 2.掌握SQL Plus的各种编辑编辑命令。 3.掌握变量变量在SQL Plus中的使用。 4.掌握格式化查询格式化查询的结果的设置。 5.熟悉SQL Developer对数据库的操作。 6.熟悉OEM工具的基本使用。 7.了解Oracle Net Configuration Assistant工具 8.了解Oracle Net Manager工具。教学内容 3.1命令行工具SQL Plus 3.2 SQL Plus实用命令 3.3 SQL Plus中变量的使用 3.4 SQL Plus格式

2、化查询结果命令 3.5 实践案例：使用图形管理工具SQL Developer 3.6Web管理工具OEM 3.7实践案例：Oracle Net Configuration Assistant工具 3.8实践案例：Oracle Net Manager工具3.1命令行工具SQL Plus 3.1.1运行SQL Plus 3.1.2实践案例：重启数据库 3.1.3断开连接3.1.1运行SQL Plus 命令行SQLPLUS语法： SQLPLUS user_name/password connect_identifier AS SYSOPER | SYSDBA | SYSASM /NOLOG AS：指

3、定管理权限，可选值SYSDBA、SYSOPER和SYSASM。 SYSOPER：此权限管理员可启动和关闭数据库，执行备份，归档日志文件，连接数据库。 SYSDBA：此权限包含SYSOPER的所有权限，还能创建数据库，并且授权SYSDBA或SYSOPER权限给其他数据库用户。3.1.1运行SQL Plus 例例3-1：在DOS窗口中输入“SQLPLUS SCOTT/tiger”命令可以用SCOTT用户连接数据库。 例例3-2：查看当前数据库实例的名称： SQL SELECT name FROM V$DATABASE;3.1.2实践案例：重启数据库 重启数据库的步骤如下。 （1）使用SQL Plu

4、s以SYSDBA的身份登录到Oracle数据库。 （2）关闭数据库。 （3）重启数据库。3.1.2实践案例：重启数据库 重启重启数据数据库命令库命令（1）SYSDBA登录数据库。 SQLPLUS SYSORCL AS SYSDBA（2）关闭数据库。 SQL SHUTDOWN IMMEDIATE; 过程：关闭数据库卸载数据库实例关闭。（3）重启数据库。 SQL STARTUP;三种模式（状态）启动数据库关闭数据库打开数据库装载数据库启动实例关闭数据库卸载数据库关闭实例启动数据库 STARTUP NOMOUNT | MOUNT | OPEN PFILE=初始化参数文件;启动模式启动模式说明说明NO

5、MOUNT 启动实例但不装载数据库读取参数文件参数文件，为实例创建内存结构和后台服务进程，用户能与库通信，但不能使用任何文件。MOUNT启动实例装载数据库不打开数据库读取控制文件控制文件，从中获取库名、数据文件位置名称等库物理结构信息，为打开库做准备。该模式下，只有库管理员可通过部分命令修改库，用户无法与库建立连接或会话。OPEN启动实例，装载并打开数据库启动后用户可连接到库并可执行库访问操作。更改数据库启动模式（状态） 1.切换到切换到MOUNT模式模式 用户使用NOMOUNT模式启动数据库，执行任务完毕后，执行下列ALTER命令，完成数据库装载。 ALTER DATABASE MOUNT

6、2.切换到切换到OPEN模式模式 用户使用MOUNT模式启动数据库，执行任务完毕后，数据库已被加载，但仍处于关闭状态。执行下列ALTER命令，可以打开数据库。 ALTER DATABASE OPEN 关闭数据库 SHUTDOWN NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT;选项选项说明说明NORMAL(正常)oracle不再接受新的链接，一直等待当前连接到数据库的所有用户都断开连接后，再关闭数据库。TRANSACTOINAL(事务处理)oracle在完成事务处理后断开所有已连接的用户，关闭数据库。不允许建立新的连接，不允许开始新的事务。当所有事务处理完

7、成后，仍然连接到当前数据库实例上的客户端将被断开。可以避免客户端中断工作或丢失数据，也不需要用户退出登录。IMMEDIATE(立即) 不允许建立新的连接，不允许开始新的事务。所有未提交的事务将被执行回滚操作。系统不等待在线用户断开连接，事务回滚完毕立即关闭数据库。ABORT(中止)不允许建立新的连接，不允许开始新的事务。正在执行的SQL语句将被立即中止，没有提交的事务也不被回滚，立即断开所有在线用户的连接。3.1.3断开连接 DISCONNECT 断开数据库连接，并保持SQL Plus运行。 CONNECT连接数据库，语法同SQLPLUS。 EXIT | QUIT 退出数据库。 SQL SEL

8、ECT name FROM V$DATABASE;3.2 SQL Plus实用命令 3.2.1查看表结构 3.2.2编辑SQL语句 3.2.3保存缓存区内容 3.2.4读取内容到缓存区 3.2.5运行外部文件的命令 3.2.6编辑外部文件的命令 3.2.7将执行结果保存到文件3.2 SQL Plus实用命令命令命令说明说明HELP topic查看命令的使用方法，topic为命令名称。例如HELP DESCHOST切换到操作系统环境，以便执行操作系统命令（exit返回）HOST 系统命令系统命令执行系统命令，例如HOST notepad.exe将打开一个事本文件CLEAR SCREEN消除屏幕内

9、容SHOW ALL查看SQL Plus的所有系统变量值的信息SHOW USER查看当前正在使用SQL Plus的用户SHOW SGA显示SGA大小SHOW RELEASE显示数据库版本信息SHOW ERRORS查看详细的错误信息SHOW PARAMETERS查看系统初始化参数信息DESC查看对象的结构，如表、视图、存储过程、函数和包等SQL Plus提供了HELP、DESCRIBE以及SHOW等命令，主要用来查看数据库信息，以及数据库中已经存在的对象信息，但不能对其进行修改等操作。3.2.1查看表结构 DESC返回对象的描述。表和视图等，列出列名及各个列的属性；输出过程、函数和程序包的规范。

10、DESC命令的语法如下： DESC schema. object connect_identifier 例3-3：假设要查看scott用户下emp表的结构。 SQL DESC scott.emp;3.2.2编辑SQL语句命令命令说明说明APPEND text将text附加到当前行之后CHANGE /old/new将当前行中的old替换为newCLEAR BUFFER消除缓存区中的所有行DEL删除当前行DEL n删除第n行（行号从1开始）INPUT text在当前行后面当前行后面添加一新行，内容是textLIST列出缓冲区中所有的行LIST n或或n列出第n行LIST *列出当前行RUN或或/运

11、行缓冲区中保存的语句x将第x行作为当前行表3-2 常用的编辑命令3.2.2编辑SQL语句 例例3-5：查询emp表中job为MANAGER的员工信息，包括empno列、ename列、job列和mgr列。 现在希望sal列和deptno列也在结果中。 例例3-6：查询scott用户表中sal小于1500的员工信息。 使用CHANGE命令将条件修改为查询sal小于1000的员工信息。3.2.3保存缓存区内容 SAVE命令将当前缓冲区内容(SQL语句)保存到文件中。 SAVE file_name CREATE | REPLACE | APPEND CREATE：创建file_name文件。该选项为默

12、认值。 APPEND：如果file_name已存在，则将内容追加到file_name的内容之后；如果文件不存在，则创建。 REPLACE：如果file_name已存在，则覆盖；如果文件不存在，则创建。3.2.3保存缓存区内容 例例3-7：使用SAVE命令将SQL Plus缓冲区中的SQL语句保存到名称为result.sql的文件中。 SQL SAVE result.sql3.2.4读取内容到缓存区 GET命令读取文件内容到缓存区。 GET FILE file_name LIST | NOLIST LIST：列出缓冲区中的语句。 例例3-8：将result.sql文件中的内容读入到缓冲区中，并获

13、取执行结果。 SQL GET result.sql SQL RUN3.2.5运行外部文件的命令 START读取文件内容到缓冲区，并运行运行。 START url | file_name 例例3-9：使用START命令读取并运行result.sql文件。 SQL START result.sql 提示：START命令等同于命令。3.2.6编辑外部文件的命令 EDIT命令将SQLPlus缓冲区内容复制到名为afiedit.buf的文件中。打开此文件，并处于可编辑状态。在退出编辑器时，所编辑的文件内容将被复制到SQL Plus缓冲区中。 EDIT file_name 例3-10：将缓冲区内容复制到a

14、fiedit.buf文件中。 SQL EDIT afiedit.buf文件的内容以斜杠(/)结束。3.2.7将执行结果保存到文件 使用SPOOL命令实现将SQL Plus中的输出结果复制到指定的文件中，直到使用SPOOL OFF命令为止。 SPOOL file_name CREATE | REPLACE | APPEND OFF | OUT 例例3-11：使用SPOOL命令将SQL Plus中的输出结果复制到result.txt文件中。 SQL SPOOL result.txt 执行SQL命令。 SQL SPOOL OFF 查看result.txt文件。3.3 SQL Plus中变量的使用 3

15、.1.1临时变量 3.3.2已定义变量 3.3.3实践案例：带提示的变量3.1.1临时变量 前“&”符号，执行时为该变量提供具体数据。 例3-12：scott用户，查询工资小于某金额的员工信息。该金额具体值由&salary决定。 SQL SELECT empno,ename,job,sal FROM scott.emp WHERE sal SELECT empno,ename,job,&cname FROM scott.emp WHERE &cname&salary;3.3.2已定义变量 在使用变量之前可以对变量进行定义，可多次使用。已定义变量的值会一直

16、保留到被显式地删除、重定义或退出SQL Plus为止。 DEFINE创建类型为CHAR变量；或查看已定义变量。 DEFINE：显示所有的已定义变量。 DEFINE variable：显示指定变量的名称、值和其数据类型。 DEFINE variable = value：创建一个CHAR类型的用户变量，并且为该变量赋初始值。3.3.2已定义变量 SQL DEFINE MIN_SAL=1500 SQL DEFINE MIN_SAL 提示：使用UNDEFINE命令删除一个变量。3.3.2已定义变量 例例3-14：使用DEFINE定义3变量，分别表示查询的列名、表名和条件。然后将其组成查询语句并执行。

17、SQL DEFINE cols=”empno,ename,job,mgr,sal,deptno” SQL DEFINE tablename=”scott.emp” SQL DEFINE condition=”sal SQL SELECT &cols FROM &tablename WHERE &condition;3.3.3实践案例：带提示的变量 ACCEPT允许定义用户提示，提示用户输入指定变量的数据。可为现有变量设置新值，也可定义新变量并初始化。 ACCEPT variable data_type FORMAT format DEFAULT default PROM

18、PT text | NOPROMPT HIDE data_type：可使用类型有CHAR、NUMBER、DATE、BINARY_FLOAT和BINARY_DOUBLE。 FORMAT：指定变量的格式，包括A15(15个字符)、9999(一个4位数)和DD-MON-YYYY(日期)。 PROMPT：用于表示在用户输入数据之前显示的文本消息。 HIDE：表示隐藏用户为变量输入的值。3.3.3实践案例：带提示的变量 从scott.emp表查询工资工资在某个范围范围的员工信息，包括empno、ename、job、mgr、sal和deptno列。要求使用ACCEPT命令提示用户输入查询范围的最小值和最大

19、值。 SQL ACCEPT minSal NUMBER FORMAT 9999 PROMPT 请输入最小工资： SQL ACCEPT maxSal NUMBER FORMAT 9999 PROMPT 请输入最大工资： SQL SELECT empno,ename,job,mgr,sal,deptno FROM scott.emp WHERE sal&minSal and sal select * from scott.emp; SQL show linesize SQL set linesize 160 SQL select * from scott.emp;3.4.3页面设置命令 p

20、agesize设置每一页显示的数据量(行数)。默认情况下为14（其中包括了ttitle、btitle、column标题以及显示的空行）。 例例3-16：显示当前pagesize大小，并修改其值，然后查看设置后显示的结果。 SQL select * from scott.emp; SQL show pagesize SQL set pagesize 18 SQL select * from scott.emp;3.4.4列设置命令 column命令用来控制某列的显示格式。 column column_name | column_alias option option：格式设置选项。选项选项描述描

21、述clear清除列的格式format改变列数据的显示格式，format属性具体见表3-4heading text设置列标题justifyalign设置列标题的对齐方式，可选left、center、rightnull text指定为空值显示的内容表3-3 column属性选项3.4.4列设置命令选项选项描述描述示例示例An设置列显示宽度A59数字9999990强制列标题为0099999$浮动的货币符号$9999L本地货币符号L9999.小数点位置9999.99,千位分割符9,999表3-4 format属性列格式描述3.4.4列设置命令 例例3-17：为ename列起别名“雇员姓名”并设置ena

22、me列显示宽度15字符。 例例3-18：为列sal起别名“薪水”，使该列居中并设置此列为货币格式。 例例3-19：消除sal列的属性设置。3.4.5分屏显示控制命令pause pause命令用来对查询结果进行分页显示，使SQL Plus在一页之后暂停滚屏。 set pause on：启动分页显示。 set pause off：关闭分页显示。3.4.6报表页眉页脚设置命令ttitle和btitle ttitle和btitle命令分别用来设置报表页眉和页脚。 ttitle | btitle printspectext | variable off | on printspec：格式设置，可选值有l

23、eft、center、right、bold、format text等。 on：表示启用设置；off：表示取消设置。 例例3-20：设置scott用户下的emp表的页眉和页脚，让页眉和页脚居中，页眉为“Tom公司雇员”，页脚为“-Tom信息公司-”。 SQL ttitle center “Tom公司雇员公司雇员”3.4.7统计计算：break和compute 使用break和compute对查询操作的结果进行统计计算。 break on column_name skip n compute function LABEL label of column_name on break_column_n

24、ame column_name：指示要计算的列名。 skip n：表示在指定列的值变化之前插入n个空行。 function：指定计算类型，SUM() 、MAX() 、MIN() 、AVG() 、COUNT() 等。 label：指定显示结果时的列标题信息。3.4.7统计计算：break和compute 例例3-21：计算scott.emp表中根据deptno列进行分组后，每组sal列值总和。 SQL break on deptno SQL compute sum of sal on deptno SQL SELECT empno,ename,sal,deptno FROM scott.emp

25、ORDER BY deptno; 提示：clear break和clear compute消除分组和统计设置。3.5 实践案例：使用图形管理工具SQL Developer 3.5.1打开SQL Developer 3.5.2连接Oracle 3.5.3创建表 3.5.4修改列 3.5.5添加数据 3.5.6导出数据 3.5.7执行存储过程3.5.1打开SQL Developer SQL Developer随Oracle安装程序安装。 “开始”“程序”“Oracle-OraDb11g_home1” “应用程序开应用程序开发发”“SQL Developer”。 第一次打开时还需要指定随Oracle

26、一起安装的JDK java.exe文件的位置。3.5.2连接Oracle使用SQL Developer管理Oracle数据库，首先需要连接到Oracle，连接时需要指定登录帐户、登录密码、端口和实例名称等信息。（1）Connections窗格下右击Connections节点，选择New Connection命令，创建一个新连接。（2）Connection Name为连接指定别名，并在Username和Password中指定该连接使用的登录名和密码。（3）Role指定连接时的身份为default或者sysdba。（4）Hostname指定Oracle数据库所在计算机名称，本机可以输入localh

27、ost；Port指定Oracle数据库的端口，默认为1521。（5）选择SID，在文本框中输入Oracle的SID名称，例如“ORCL”。（6）单击Test进行连接测试，如通过将显示Success。（7）单击Save保存连接，再单击Connect连接到Oracle。此时Connections窗格中多出刚才创建的连接名称，展开该连接，可以查看Oracle中各种数据库对象。在右侧可以编辑SQL语句。3.5.3创建表 下面使用SQL Developer工具向scott用户的表空间创建一个名为Departments的表，该表包含一个带有emp表的外键。 在SQL Developer中使用scott用户

28、连接到Oracle。在Connections窗格中展开连接，并右击Tables表选择New Table命令。 (1) DID，NUMBER，Precision 6，NOT NULL。 (2) DNAME，VARCHAR2，SIZE 20，NOT NULL。 (3) EID，NUMBER，NOT NULL。 (4) Foreign Key，EID，REFERENCE emp empno。3.5.4修改列 以Departments表为例。 （1）在Tables节点下选择要更改的表，例如Departments表 （2）从右侧Columns选项卡下单击Action按钮，弹出的菜单中选择ColumnAd

29、d命令。 技巧：直接右击表名也可以打开相同的管理菜单。 （3）在对话框中可以设置列名、数据类型和精度等信息，设置完成后单击“应用”按钮确认添加。最后单击“刷新”按钮即可看到新添加的列。 （4）选择Drop命令，可以删除列。3.5.5添加数据SQL Developer提供了多种添加数据的方法，可以一次添加一行、多行，或者批量添加。（1）从Tables节点下单击Departments表名，在右侧打开Data选项卡。（2）单击“插入行”按钮，下方将会出现一个空白的行。（3）在空白行中依次为DID列、DNAME列和EID列指定值，再单击“提交修改”按钮进行保存。SQL Developer会将用户的输入

30、转换为对应的INSERT语句，并显示执行。（4）SQL Developer同样支持使用SQL脚本形式添加数据。方法是在连接的SQL编辑器中输入添加数据的语句，再单击“执行”按钮。提示：在SQL编辑器中右击，用Open File命令执行外部文件来指添加数据。3.5.6导出数据 SQL Developer能够将用户数据导出为各种格式，包括CSV、XML、HTML以及TEXT等。 假设要将Dpartments表中的数据导出为INSERT语句，可使用如下步骤。 （1）打开查看Dpartments表数据的界面，在空白处右击，选择Export DataINSERT命令。 （2）在弹出的Export Dat

31、a对话框中指定Format为INSERT，单击Browse按钮可以更改导出文件的位置和文件名称。 （3）在Columns选项卡下可以指定要导出的列，这里为全部列；在Where选项卡下可以指定导出数据的条件，这里使用默认值。最后单击“应用”按钮开始导出，完成后打开生成的文件，会看到很多INSERT语句。 （4）如果选择CSV命令，可以将数据导出到CSV文件中。3.5.6导出数据 上面的方法仅能够导出表中的数据，假设要导出Dpartments表的定义以及其他对象，可以通过如下方法。 （1）打开SQL Developer，从主菜单中选择ToolsExport DDL命令，打开Export对话框。 （

32、2）在默认的Export选项卡下设置导出的文件名称、导出使用的连接、导出对象的类型，以及设置选项。 （3）在Filter Object选项卡中可以设置不希望导出的对象。 （4）最后的Filter Data选项卡用于对数据的导出范围进行限制。 （5）全部设置完成后，单击“应用”按钮开始导出。3.5.7执行存储过程创建一个存储过程，可以查询员工编号、姓名、职位和工资，要求可以指定返回结果的行数。（1）在Connections窗格中右击Procedures节点，从弹出的快捷菜单中选择New Procedure命令。（2）在弹出的对话框中指定存储过程的名称为procGetEmp。（3）单击“添加”按钮

33、创建一个名为param1的参数，类型为NUMBER，单击“确定” ，进入存储过程的创建模板，此时会看到创建的一些代码。（4）使用如下代码替换模板中AS关键字后的内容。（5）需要对其进行编译并检测语法错误。单击工具栏上的“编译”按钮进行编译，当检测到无效的PL SQL语句时，会在底部的日志窗格中显示错误列表。（6）经过检查，在本示例中WHILE后多出了一个左小括号，删除后再次编译，将不再有错误出现。（7）运行procGetEmp存储过程。方法是展开Procedures节点，右击procGetEmp并从弹出的快捷菜单中选择Run命令。由于该存储过程有一个参数，会打开参数指定对话框，在这里设置PARAM1参数的值为5。（8）单击“确定”按钮开始执行，然后会在Running窗格中看到输出结果。3.6Web管理工具OEM Oracle Enterprise Manager(Oracle企业管理器) 基于Web的管理界面，管理单个Oracle数据库实例。在安全方面，采用HTTPS协议。 3.6.1运行OEM 3.6.2使用OEM管理Oracle3.6.1运行OEM 启动OEM时，除了需要启动Oracle监听和Oracle服务外，还必须启