第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

3、AS：指定管理权限，可选值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实践案例：重启数据库 重启数据库的步骤如下。 （

4、1）使用SQL Plus以SYSDBA的身份登录到 Oracle数据库。 （2）关闭数据库。 （3）重启数据库。 3.1.2实践案例：重启数据库 重启重启数据数据库命令库命令 （1）SYSDBA登录数据库。 SQLPLUS SYSORCL AS SYSDBA （2）关闭数据库。 SQL SHUTDOWN IMMEDIATE; 过程：关闭数据库卸载数据库实例关闭。 （3）重启数据库。 SQL STARTUP; 三种模式（状态） 启动数据库 关闭数据库 打开数据库 装载数据库 启动实例 关闭数据库 卸载数据库 关闭实例 启动数据库 STARTUP NOMOUNT | MOUNT | OPEN PF

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

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

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

8、QL Plus运行。 CONNECT连接数据库，语法同SQLPLUS。 EXIT | QUIT 退出数据库。 SQL SELECT 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 DESC HOST切换到操作系统环境，以便执行操作系统命令（exit

9、返回） HOST 系统命令系统命令执行系统命令，例如HOST notepad.exe将打开一个事本文件 CLEAR SCREEN消除屏幕内容 SHOW ALL查看SQL Plus的所有系统变量值的信息 SHOW USER查看当前正在使用SQL Plus的用户 SHOW SGA显示SGA大小 SHOW RELEASE显示数据库版本信息 SHOW ERRORS查看详细的错误信息 SHOW PARAMETERS查看系统初始化参数信息 DESC查看对象的结构，如表、视图、存储过程、函数和包等 SQL Plus提供了HELP、DESCRIBE以及SHOW等命令，主要用 来查看数据库信息，以及数据库中已经

10、存在的对象信息， 但不能对其进行修改等操作。 3.2.1查看表结构 DESC返回对象的描述。表和视图等，列出列名 及各个列的属性；输出过程、函数和程序包的 规范。 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替换为new CLEAR BUFFER消除缓存区中的所有行 DEL删除当前行 DEL n删除第n

11、行（行号从1开始） INPUT text在当前行后面当前行后面添加一新行，内容是text LIST列出缓冲区中所有的行 LIST n或或n列出第n行 LIST *列出当前行 RUN或或/运行缓冲区中保存的语句 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保存缓存

12、区内容 SAVE命令将当前缓冲区内容(SQL语句)保存到文件中。 SAVE file_name CREATE | REPLACE | APPEND CREATE：创建file_name文件。该选项为默认值。 APPEND：如果file_name已存在，则将内容追加到 file_name的内容之后；如果文件不存在，则创建。 REPLACE：如果file_name已存在，则覆盖；如果文 件不存在，则创建。 3.2.3保存缓存区内容 例例3-7：使用SAVE命令将SQL Plus缓冲区中的 SQL语句保存到名称为result.sql的文件中。 SQL SAVE result.sql 3.2.4读取内

13、容到缓存区 GET命令读取文件内容到缓存区。 GET FILE file_name LIST | NOLIST LIST：列出缓冲区中的语句。 例例3-8：将result.sql文件中的内容读入到缓 冲区中，并获取执行结果。 SQL GET result.sql SQL RUN 3.2.5运行外部文件的命令 START读取文件内容到缓冲区，并运行运行。 START url | file_name 例例3-9：使用START命令读取并运行result.sql文件。 SQL START result.sql 提示：START命令等同于命令。 3.2.6编辑外部文件的命令 EDIT命令将SQLPlu

14、s缓冲区内容复制到名为afiedit.buf的文件 中。打开此文件，并处于可编辑状态。在退出编辑器时， 所编辑的文件内容将被复制到SQL Plus缓冲区中。 EDIT file_name 例3-10：将缓冲区内容复制到afiedit.buf文件中。 SQL EDIT afiedit.buf文件的内容以斜杠(/)结束。 3.2.7将执行结果保存到文件 使用SPOOL命令实现将SQL Plus中的输出结果复制到指定的 文件中，直到使用SPOOL OFF命令为止。 SPOOL file_name CREATE | REPLACE | APPEND OFF | OUT 例例3-11：使用SPOOL命令

15、将SQL Plus中的输出结果复制到 result.txt文件中。 SQL SPOOL result.txt 执行SQL命令。 SQL SPOOL OFF 查看result.txt文件。 3.3 SQL Plus中变量的使用 3.1.1临时变量 3.3.2已定义变量 3.3.3实践案例：带提示的变量 3.1.1临时变量 前“ 3.3.2已定义变量 在使用变量之前可以对变量进行定义，可多次使用。 已定义变量的值会一直保留到被显式地删除、重定 义或退出SQL Plus为止。 DEFINE创建类型为CHAR变量；或查看已定义变量。 DEFINE：显示所有的已定义变量。 DEFINE variable

16、：显示指定变量的名称、值和其数 据类型。 DEFINE variable = value：创建一个CHAR类型的用 户变量，并且为该变量赋初始值。 3.3.2已定义变量 SQL DEFINE MIN_SAL=1500 SQL DEFINE MIN_SAL 提示：使用UNDEFINE命令删除一个变量。 3.3.2已定义变量 例例3-14：使用DEFINE定义3变量，分别表示查询的列 名、表名和条件。然后将其组成查询语句并执行。 SQL DEFINE cols=”empno,ename,job,mgr,sal,deptno” SQL DEFINE tablename=”scott.emp” SQL

17、 DEFINE condition=”sal SQL SELECT 3.3.3实践案例：带提示的变量 ACCEPT允许定义用户提示，提示用户输入指定变量的数据。 可为现有变量设置新值，也可定义新变量并初始化。 ACCEPT variable data_type FORMAT format DEFAULT default PROMPT text | NOPROMPT HIDE data_type：可使用类型有CHAR、NUMBER、DATE、 BINARY_FLOAT和BINARY_DOUBLE。 FORMAT：指定变量的格式，包括A15(15个字符)、9999(一 个4位数)和DD-MON-Y

18、YYY(日期)。 PROMPT：用于表示在用户输入数据之前显示的文本消息。 HIDE：表示隐藏用户为变量输入的值。 3.3.3实践案例：带提示的变量 从scott.emp表查询工资工资在某个范围范围的员工信息，包括 empno、ename、job、mgr、sal和deptno列。要求使用 ACCEPT命令提示用户输入查询范围的最小值和最大值。 SQL ACCEPT minSal NUMBER FORMAT 9999 PROMPT 请 输入最小工资： SQL ACCEPT maxSal NUMBER FORMAT 9999 PROMPT 请 输入最大工资： SQL SELECT empno,en

19、ame,job,mgr,sal,deptno FROM scott.emp WHERE sal SQL show linesize SQL set linesize 160 SQL select * from scott.emp; 3.4.3页面设置命令 pagesize设置每一页显示的数据量(行数)。默 认情况下为14（其中包括了ttitle、btitle、 column标题以及显示的空行）。 例例3-16：显示当前pagesize大小，并修改其值， 然后查看设置后显示的结果。 SQL select * from scott.emp; SQL show pagesize SQL set pa

20、gesize 18 SQL select * from scott.emp; 3.4.4列设置命令 column命令用来控制某列的显示格式。 column column_name | column_alias option option：格式设置选项。 选项选项描述描述 clear清除列的格式 format改变列数据的显示格式，format属性具体见表3-4 heading text设置列标题 justifyalign设置列标题的对齐方式，可选left、center、right null text指定为空值显示的内容 表3-3 column属性选项 3.4.4列设置命令 选项选项描述描述示例示

21、例 An设置列显示宽度A5 9数字999999 0强制列标题为0099999 $浮动的货币符号$9999 L本地货币符号L9999 .小数点位置9999.99 ,千位分割符9,999 表3-4 format属性列格式描述 3.4.4列设置命令 例例3-17：为ename列起别名“雇员姓名”并 设置ename列显示宽度15字符。 例例3-18：为列sal起别名“薪水”，使该列 居中并设置此列为货币格式。 例例3-19：消除sal列的属性设置。 3.4.5分屏显示控制命令pause pause命令用来对查询结果进行分页显示， 使SQL Plus在一页之后暂停滚屏。 set pause on：启动分

22、页显示。 set pause off：关闭分页显示。 3.4.6报表页眉页脚设置命令ttitle和btitle ttitle和btitle命令分别用来设置报表页眉和页脚。 ttitle | btitle printspectext | variable off | on printspec：格式设置，可选值有left、center、right、bold、 format text等。 on：表示启用设置；off：表示取消设置。 例例3-20：设置scott用户下的emp表的页眉和页脚，让页眉 和页脚居中，页眉为“Tom公司雇员”，页脚为“-Tom 信息公司-”。 SQL ttitle cente

23、r “Tom公司雇员公司雇员” 3.4.7统计计算：break和compute 使用break和compute对查询操作的结果进行统计计算。 break on column_name skip n compute function LABEL label of column_name on break_column_name column_name：指示要计算的列名。 skip n：表示在指定列的值变化之前插入n个空行。 function：指定计算类型，SUM() 、MAX() 、MIN() 、AVG() 、 COUNT() 等。 label：指定显示结果时的列标题信息。 3.4.7统计计算：

24、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 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

25、修改列 3.5.5添加数据 3.5.6导出数据 3.5.7执行存储过程 3.5.1打开SQL Developer SQL Developer随Oracle安装程序安装。 “开始”“程序”“Oracle- OraDb11g_home1” “应用程序开应用程序开 发发”“SQL Developer”。 第一次打开时还需要指定随Oracle一起安装 的JDK java.exe文件的位置。 3.5.2连接Oracle 使用SQL Developer管理Oracle数据库，首先需要连接到Oracle，连接时 需要指定登录帐户、登录密码、端口和实例名称等信息。 （1）Connections窗格下右击Con

26、nections节点，选择New Connection命 令，创建一个新连接。 （2）Connection Name为连接指定别名，并在Username和Password中 指定该连接使用的登录名和密码。 （3）Role指定连接时的身份为default或者sysdba。 （4）Hostname指定Oracle数据库所在计算机名称，本机可以输入 localhost；Port指定Oracle数据库的端口，默认为1521。 （5）选择SID，在文本框中输入Oracle的SID名称，例如“ORCL”。 （6）单击Test进行连接测试，如通过将显示Success。 （7）单击Save保存连接，再单击Co

27、nnect连接到Oracle。此时 Connections窗格中多出刚才创建的连接名称，展开该连接，可以查看 Oracle中各种数据库对象。在右侧可以编辑SQL语句。 3.5.3创建表 下面使用SQL Developer工具向scott用户的表空间创建 一个名为Departments的表，该表包含一个带有emp表 的外键。 在SQL Developer中使用scott用户连接到Oracle。在 Connections窗格中展开连接，并右击Tables表选择 New Table命令。 (1) DID，NUMBER，Precision 6，NOT NULL。 (2) DNAME，VARCHAR2，

28、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按钮，弹 出的菜单中选择ColumnAdd命令。 技巧：直接右击表名也可以打开相同的管理菜单。 （3）在对话框中可以设置列名、数据类型和精度 等信息，设置完成后单击“应用”按钮确认添加。 最后单击“刷新”按钮即可看到新添加的列。 （4）选择Drop命令，可

29、以删除列。 3.5.5添加数据 SQL Developer提供了多种添加数据的方法，可以一次添加一行、 多行，或者批量添加。 （1）从Tables节点下单击Departments表名，在右侧打开Data选 项卡。 （2）单击“插入行”按钮，下方将会出现一个空白的行。 （3）在空白行中依次为DID列、DNAME列和EID列指定值，再单 击“提交修改”按钮进行保存。SQL Developer会将用户的输入 转换为对应的INSERT语句，并显示执行。 （4）SQL Developer同样支持使用SQL脚本形式添加数据。方法 是在连接的SQL编辑器中输入添加数据的语句，再单击“执行” 按钮。 提示：在

30、SQL编辑器中右击，用Open File命令执行外部文件来指 添加数据。 3.5.6导出数据 SQL Developer能够将用户数据导出为各种格式，包括 CSV、XML、HTML以及TEXT等。 假设要将Dpartments表中的数据导出为INSERT语句，可 使用如下步骤。 （1）打开查看Dpartments表数据的界面，在空白处右 击，选择Export DataINSERT命令。 （2）在弹出的Export Data对话框中指定Format为INSERT， 单击Browse按钮可以更改导出文件的位置和文件名称。 （3）在Columns选项卡下可以指定要导出的列，这里为 全部列；在Wher

31、e选项卡下可以指定导出数据的条件， 这里使用默认值。最后单击“应用”按钮开始导出，完 成后打开生成的文件，会看到很多INSERT语句。 （4）如果选择CSV命令，可以将数据导出到CSV文件中。 3.5.6导出数据 上面的方法仅能够导出表中的数据，假设要导出 Dpartments表的定义以及其他对象，可以通过如下方法。 （1）打开SQL Developer，从主菜单中选择ToolsExport DDL命令，打开Export对话框。 （2）在默认的Export选项卡下设置导出的文件名称、导 出使用的连接、导出对象的类型，以及设置选项。 （3）在Filter Object选项卡中可以设置不希望导出的

32、对 象。 （4）最后的Filter Data选项卡用于对数据的导出范围进 行限制。 （5）全部设置完成后，单击“应用”按钮开始导出。 3.5.7执行存储过程 创建一个存储过程，可以查询员工编号、姓名、职位和工资，要求可以指定 返回结果的行数。 （1）在Connections窗格中右击Procedures节点，从弹出的快捷菜单中选择 New Procedure命令。 （2）在弹出的对话框中指定存储过程的名称为procGetEmp。 （3）单击“添加”按钮创建一个名为param1的参数，类型为NUMBER，单击 “确定” ，进入存储过程的创建模板，此时会看到创建的一些代码。 （4）使用如下代码替换

33、模板中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管理Oracle 3.6.1运行OEM 启动OEM时，除了需要启动Oracle监听和Oracle服 务外，还必须启动本地OracleDBConsoleorcl。 例例3-22：在浏览器地址栏中