常用Oracle管理工具

第2章常用Oracle管理工具 本章内容 SQL Plus工具及其使用OEM的配置和使用SQLDeveloper 本章要求 了解SQL Plus工具的应用掌握SQL PLUS基本命令操作掌握OEM的配置 启动和停止掌握OEM控制台设置 2 1Oracle企业管理器 OEM OracleEnterpriseManager Oracle企业管理器 是Oracle数据库的主要管理工具 它以一个HTTP服务器方式为用户提供基于Web界面的管理工具 OEM有两个版本 管理单实例数据库的DatabaseControl 数据库控制 GridControl 网格控制 2 1 1配置DatabaseControl OEMDatabaseControl的配置有两种方法 自动配置和手工配置 使用DBCA创建数据库时 可选择 配置EnterpriseManager 自动配置OEMDatabaseControl 用EMCA EnterpriseManagerConfigurationAssistant 企业管理器配置助手 手工配置DatabaseControl 先设置环境变量ORACLE SID setORACLE SID orcl创建EM资料档案库emca reposcreate配置DatabaseControlemca configdbcontroldb访问DatabaseControl的URL地址被记录在 install readme txt文件中 2 1 2启动和停止DatabaseControl DatabaseControl配置之后 在操作系统控制面板的服务下添加一项服务 OracleDBConsoleSID 使用控制面板可以启动和停止DatabaseControl使用Oracle实用程序emctlemctl实用程序查看DatabaseControl状态emctlstatusdbconsole停止DatabaseControlemctlstopdbconsole启动DatabaseControlemctlstartdbconsole 连接DatabaseControl确认 正确配置并启动DatabaseControl目标数据库以及监听正在运行在浏览器地址栏内输入访问OEM的URL地址 格式为 http hostname portnumber emhostname为主机名或主机IP地址portnumber为OracleDBConsole服务的端口号详细信息可以参阅 install readme txt中的记录 2 1 3DatabaseControl功能概述 见教材P47 设置OEM控制台管理员 在默认情况下 只有SYS SYSTEM和SYSMAN三个数据库用户才能登录和使用OEM控制台 其中 SYSMAN用户是OEM控制台的超级用户 是在安装OEM的过程中创建的 用于执行系统配置 全局配置等任务 通常 需要创建其他普通的OEM控制台管理员 进行OEM控制台的日常管理与维护工作 注意 1 除SYS以SYSDBA身份登录外 SYSTEM和SYSMAN均以NORMAL身份登录 2 SYSMAN用户信息不能更改 设置封锁期 概念Oracle封锁期允许OEM控制台管理员挂起对一个或多个目标的监控操作 以便在目标上执行定期维护操作 要封锁某个目标 则至少要对此目标具有 操作者 权限 两种方法 在封锁期列表中选择一个封锁期 然后单击 类似创建 按钮 以此为基础 快速创建 在封锁期界面 选择 创建 按钮 创建一个全新的封锁期 设置数据库首选身份证明 概念首选身份证明通过将相应目标的登录信息以加密模式存储到OEM资料档案库中 以简化对被管理目标的访问 如果为目标设置了首选身份证明 那么用户通过OEM控制台访问该目标时 不需要进行显式登录 例如 为执行备份和恢复操作而安排作业和任务时 OEM控制台可以自动从OEM资料档案库中获取主机和数据库的首选身份证明 而不需要进行登录操作 首选身份证明是基于每个用户的 因此可以保证被管理的企业环境的安全性 可以设置首选身份证明的目标包括主机 数据库实例 监听程序 代理等 设置主机首选身份证明 概念在网络环境中 如果要利用OEM控制台启动 关闭数据库 进行数据的导入 导出 备份 恢复等操作 就必须指明数据库所在的主机 并使用该主机操作系统用户登录该主机 即进行主机身份证明 如果设置了主机首选身份证明 那么执行这些数据库操作时就可以简化操作 不需要进行登录主机操作系统的操作 设置主机首选身份证明的步骤 首先进行操作系统用户权限分配 然后设置主机身份证明 2 2SQL Plus 概念SQL Plus工具是随Oracle数据库服务器或客户端的安装而自动进行安装的管理与开发工具 Oracle数据库中所有的管理操作都可以通过SQL Plus工具完成 同时开发人员利用SQL Plus可以测试 运行SQL语句和PL SQL程序 SQL Plus是一个命令行界面的查询工具 拥有自己的命令和环境 可执行以下操作 执行SQL Plus命令 执行SQL语句和PL SQL语句块 格式化和保存查询结果 检查表和对象定义 开发和运行批脚本 管理数据库 执行操作系统命令 2 2 1SQL Plus的启动和关闭 启动SQL Plus在命令行上直接输入sqlplus从Oracle程序组的 应用程序开发 中选择SQLPlus启动语法格式为 sqlplus logon 其中 option部分的主要选项为 Help 显示SQL Plus程序的使用帮助信息 Version 显示SQL Plus版本号 Silent 要求以哑模式启动和运行SQL Plus 适合在其他应用程序中调用SQL Plus的情况 logon参数指出登录相关信息 其格式为 用户名 口令 连接标识符 AS SYSOPER SYSDBA NOLOGstart参数指出SQL Plus启动后立即执行的脚本文件名称及其参数 其语法格式为 url地址 脚本文件名 扩展名 参数 例如 启动SQL Plus时以system用户登录 注意 如果以系统用户名 如sys 登录 则必须输入登录连接的身份 数据库认证 操作系统认证 只输入用户名 并指定要连接的数据库 输入口令 注意 为了保证口令安全 可以先不输入 等提示 输入口令 时 再输入 关闭SQL Plus关闭SQL Plus可以采用以下两种方法 异常关闭 直接关闭SQL Plus窗口 或者由于其他原因导致SQL Plus与Oracle数据库服务器之间的连接异常中断 未完成的事务被Oracle实例回滚正常关闭 执行SQL Plus命令exit关闭 未完成事务的结束方式由SQL Plus环境参数EXITCOMMIT的值决定 值为ON 默认值 提交事务 值为OFF 回滚 2 2 2SQL Plus变量与参数配置 在SQL Plus下可以定义变量 设置参数 参数改变当前会话的环境设置等 变量分为SQL Plus预定义变量和用户变量 定义SQL Plus变量define命令查看预定义变量例如 启动SQL Plus以scott用户连接数据库 查看预定义变量 SCOTT orclSQL define define命令定义用户变量语法 DEFINEvariable name value 例如 SCOTT orclSQL definedept no 10查看变量 SCOTT orclSQL definedept noDEFINEDEPT NO 10 CHAR 注意 使用DEFINE定义替换变量时 1 变量只能是CHAR类型的 2 定义的同时要进行赋值 替换变量 使用undefine命令释放变量例如 释放dept no变量SCOTT orclSQL undefinedept no设置SQL Plus环境参数SQL Plus参数也称作系统变量 它由set命令设置 用于改变SQL Plus当前会话的环境设置 常用的参数如下 说明 SETAUTOTRACE ON OFF TRACEONLY EXPLAIN STATISTICS 例如 修改SQL Plus默认提示符 SQL SETSQLPROMPT USER CONNECT IDENTIFIER SCOTT orcl dbs 使用不同的连接字符串重新连接 SCOTT orcl dbs connscott tiger dbs 1525 已连接 SCOTT dbs 1525 显示环境参数SQL SHOWALLSQL SHOWvariable name 设置SQL Plus配置文件DBA或用户可以使用配置文件设置SQL Plus环境 这样每次建立连接后自动运行配置文件 即可得到相同的环境设置 SQL Plus配置文件包括两类 站点配置文件 由DBA在Oracle数据库服务器上建立 文件名为glogin sql 存储在ORACLE HOME下的sqlplus admin 子目录内 影响连接该数据库 站点 的所有用户 用户配置文件 由用户在客户端创建 文件名为login sql 它存储于当前目录或者SQLPATH注册项所指定的目录 Windows下为 ORACLE HOME dbs 内 只影响当前用户 注意 如果站点配置文件和用户配置文件对同一个参数进行设置 由于用户配置文件执行在后 所以其设置将覆盖站点配置文件中的设置 例如 在glogin sql内添加以下设置 使每个用户在连接后修改SQL提示符和输出的行 页数据长度 SETSQLPROMPT USER CONNECT IDENTIFIER DATE SETLINESIZE120SETPAGESIZE24 2 2 3编辑执行命令 在SQL Plus中可以编辑执行的内容分为3类 SQL Plus命令SQL语句PL SQL语句块SQL语句和PL SQL语句块会暂时存放到SQL缓冲区中 编辑执行SQL语句结束编辑状态 可以执行以下任意操作 在新行的开始直接输入句点 只结束编辑状态 而不执行SQL语句 在空行上直接按回车键 结束编辑 但不执行SQL语句 输入分号 结束编辑状态并执行已输入SQL语句 在一行上输入斜杠 结束编辑并执行已输入的SQL语句 编辑执行PL SQL语句块结束PL SQL块的编辑状态 在新行的开始直接输入句点 只结束编辑状态 但不执行编辑的PL SQL语句块 在新行上输入斜杠 结束编辑并执行已输入的PL SQL语句块 编辑执行SQL Plus命令输入SQL Plus命令后按回车键 即可结束编辑状态 并立即执行 在输入SQL Plus命令时 可以使用缩写 常用SQL Plus命令见教材P38表2 1 连接命令CONNECT 进行用户切换或连接到新的数据库 语法 CONN ECT username password hoststring DISC ONNECT 断开与数据库的连接 注意 DISC命令作用仅仅是断开与数据库的连接 不退出SQL Plus环境 编辑命令 对SQL缓冲区中的语句进行编辑显示缓冲区L IST 编辑缓冲区A PPEND 将指定的文本追加到缓冲区内当前行的末尾 C HANGE 修改缓冲区中当前行的文本 DEL 删除缓冲区中当前行的文本 N 用数值定位缓冲区中的当前行 I NPUT 在缓冲区当前行的后面新增加一行文本 ED IT 以文本编辑器方式打开缓冲区 进行编辑 注意 使用ED IT 命令时 缓冲区中必须存在信息 执行缓冲区R UN 清除缓冲区CL EAR BUFF ER 例 在SQL PLUS中编辑SQL缓冲区中的SQL语句 显示结果 SQL SELECTdeptno dname2FROMdept DEPTNODNAME 10ACCOUNTING20RESEARCH30SALES40OPERATIONS SQL L11 deptno dnameSQL A loc1 deptno dname loc 问题 增加一列查询信息 不重新输入SQL语句 如何实现 显示结果 SQL L1SELECTdeptno dname loc2 FROMdeptSQL DEPTNODNAMELOC 10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON 文件操作命令 对磁盘中的脚本文件进行操作通常 将经常执行的SQL Plus命令 SQL语句和PL SQL程序存储到SQL脚本文件 以 sql为后缀 中 然后执行SQL脚本文件 使用脚本文件的好处 可以降低命令输入量 可以避免输入错误 脚本文件的创建SAV E filename CREATE REPLACE APPEND 将SQL缓冲区内容保存到一个SQL脚本文件中脚本文件的装载与编辑GETfilename LIST NOLIST 将SQL脚本文件调入SQL缓冲区 默认路径是 BIN 脚本文件的执行STA RT filename filename脚本文件的注释REM ARK 单行注释 放在一行语句的头部 表示该行为注释 单行注释 多行注释 例 SQL SELECTempno ename sal2FROMemp3WHEREempno 7844SQL SAVd ora code sqlscript sql已创建filed ora code sqlscript sql SQL CLEARBUFFERbuffer已清除SQL INPUT1COLUMNsalHEADING salary 2SELECTempno ename sal3FROMemp4WHEREempno 78445SQL SAVd ora code iscript sql已创建filed ora code iscript sql 把SQL Plus命令送入缓冲区 SQL GETd ora code sqlscript sqlSQL d ora code sqlscript sql RUNor 注意 包含了SQL Plus命令的缓冲区内容无法执行 只能通过脚本文件的执行命令来执行 例 SPOd ora code outputfile txtSELECTempno ename salFROMempWHEREsal 1500 SPOOFF SPOd ora code outputfile txtAPPENDSELECTempno ename salFROMempWHEREsal 3000 SPOOFF 注意 只有SPOOFF后 才会把屏幕输出内容写入文件 交互式命令替换变量使用替换变量 variable name例 注意 替换变量为数值类型时 可以直接引用 如果为字符型或日期型 则需将替换变量用单引号引起来 不希望每次执行时都为替换变量赋值 可以使用两个 例 SQL SELECTempno ename2FROMemp3WHEREdeptno SQL SELECTempno ename2FROMemp3WHEREdeptno 注意 此时 只需赋值一次 可在当前SQL Plus环境中一直使用 定义替换变量 ACCEPT命令定义替换变量可以自定义提示信息 以提示用户为替换变量输入值 语法 ACCEPTvariable datatype FORMATformat PROMPTtext HIDE 说明 PROMPT选项指定提示值 用HIDE选项隐藏输入 以便于用户输入替换变量的值 例 是否显示用值替换替代变量前后的命令文本 SETVERIFYON OFF SQL ACCEPTdenoNUMBERPROMPT 请输入部门编号 SQL SELECTempno2FROMemp3WHEREdeptno 绑定变量定义 指在SQL Plus中定义 在PL SQL程序中使用的变量 语法 VAR IABLE variable nameTYPE说明 在PL SQL中引用 variable name赋值 EXECUTE variable name value显示 PRINTvariable name例 SQL VARIABLEv salNUMBERSQL EXECUTE v sal 10PL SQL过程已成功完成 SQL BEGIN2 v sal 20 3END 4 PL SQL过程已成功完成 SQL PRINTv salV SAL 20 定义绑定变量v sal 给绑定变量v sal赋值 引用绑定变量v sal 显示绑定变量v sal的值 格式化命令对SQL语句或PL SQL程序执行结果的显式格式进行设置 遵循以下规则 格式化命令设置之后 将会一直起作用 直到会话结束或下一个格式化命令的设置 每一次报表结束时 应该重新设置SQL Plus为默认值 如果为某个列指定了别名 必须使用该别名 不能使用列名 COL UMN 控制列的输出显示格式 其中 option的选项有 CLE AR 清除所有列的显示格式FOR MAT format 使用格式模型改变列的显示HEA DING text 设置列标题JUS TIFY align 调整列标题的位置居左 left 居中 center 还是居右 right NO PRINT 显式 隐藏 列标题NULL 在指定位置将空值位置显示为textTRUNCATED 删除第一行的字符串WRAPPED 换行 COL UMN column alias option 格式模型 结果123401234 1234 12341234 001 234 举例A15999999099999 9999L99999999 999 999 元素An90 L 描述设置显示宽度为n个字符每个9表是一位数字 不显示前导零表示前导零美元符号本地货币符号小数点的位置千位分割符 例 显示某列的当前设置 清除某列的当前设置 TTITLE和BTITLE 设置报表的页眉和页脚 其中 text为标题的内容 用单引号引起来 可以使用Oracle系统变量 COLenameHEADING Employee Name FORMATA15COLsalJUSTIFYLEFTFORMAT 99 999 99COLmgrFORMAT999999999NULL Nomanager COL UMN ename COL UMN enameCLEAR TTI TLE BTI TLE CENTER RIGHT LEFT text OFF ON 分两行显示 SQL LNO当前的行号SQL PNO当前的页号SQL RELEASE当前Oracle的版本号