oracle 数据库基本操作

1. oracle 数据库基本操作

目录

• 1. oracle 数据库基本操作
  • 1.1. 表空间操作
    • 1.1.1. 查询所有表空间使用率
    • 1.1.2. 查询表空间文件名
      • 1.1.2.1. 创建表空间：
        • 1.1.2.1.1. 数据表空间：
        • 1.1.2.1.2. 临时表空间：
        • 1.1.2.1.3. 回滚表空间：
      • 1.1.2.2. 增加表空间：
        • 1.1.2.2.1. 数据表空间：
        • 1.1.2.2.2. 临时表空间：
        • 1.1.2.2.3. 回滚表空间：
      • 1.1.2.3. 删除表空间：
        • 1.1.2.3.1. 数据表空间：
        • 1.1.2.3.2. 临时表空间：
        • 1.1.2.3.3. 回滚段表空间：
      • 1.1.2.4. 表空间大小查看：
    • 1.1.3. 裸卷表空间管理
      • 1.1.3.1. 创建表空间：
        • 1.1.3.1.1. 数据表空间：
        • 1.1.3.1.2. 临时表空间：
        • 1.1.3.1.3. 回滚段表空间：
      • 1.1.3.2. 增加表空间：
        • 1.1.3.2.1. 数据表空间：
        • 1.1.3.2.2. 临时表空间：
        • 1.1.3.2.3. 回滚段表空间：
      • 1.1.3.3. 删除表空间：
        • 1.1.3.3.1. 数据表空间：
        • 1.1.3.3.2. 临时表空间：
        • 1.1.3.3.3. 回滚段表空间：
  • 1.2. 用户、角色、权限
    • 1.2.1. 用户创建、修改、删除
      • 1.2.1.1. 用户创建
      • 1.2.1.2. 修改用户密码：
      • 1.2.1.3. 修改用户使用状态
      • 1.2.1.4. 用户赋权限：
      • 1.2.1.5. 给某一个用户赋予某张表的某种权限：
      • 1.2.1.6. 用户权限回收：
      • 1.2.1.7. 用户删除：
      • 1.2.1.8. 查看用户属性和状态：
    • 1.2.2. 权限分类：
    • 1.2.3. 角色创建、修改、删除
      • 1.2.3.1. 角色创建
      • 1.2.3.2. 角色权限修改
      • 1.2.3.3. 角色删除
      • 1.2.3.4. 权限增加和收回
    • 1.2.4. 系统角色分类
  • 1.3. 表操作
    • 1.3.1. 单表创建
      • 1.3.1.1. 直接创建
      • 1.3.1.2. 创建类似表
      • 1.3.1.3. 创建表结构：
      • 1.3.1.4. 表查询：
      • 1.3.1.5. 表插入：
      • 1.3.1.6. 循环插入：
      • 1.3.1.7. 表更新：
      • 1.3.1.8. 表删除：
      • 1.3.1.9. 表记录删除：
      • 1.3.1.10. 表名修改：
      • 1.3.1.11. 表迁移
      • 1.3.1.12. 表压缩：
      • 1.3.1.13. 表统计信息收集：
    • 1.3.2. 分区表创建
      • 1.3.2.1. 范围分区表创建
        • 1.3.2.1.1. 分区表插入
        • 1.3.2.1.2. 表分区删除：
        • 1.3.2.1.3. 分区分割：
        • 1.3.2.1.4. 分区查询：
      • 1.3.2.2. hash 分区表（散列分区）
        • 1.3.2.2.1. Hash 分区表创建：
        • 1.3.2.2.2. 表插入：
        • 1.3.2.2.3. 分区查询：
      • 1.3.2.3. 列表分区表
        • 1.3.2.3.1. 散列分区表创建：
        • 1.3.2.3.2. 数据清理
          • 1.3.2.3.2.1. 创建删除的存储过程
    • 1.3.3. 分区操作
      • 1.3.3.1. 添加分区
      • 1.3.3.2. 删除分区
      • 1.3.3.3. 截断分区
      • 1.3.3.4. 合并分区
      • 1.3.3.5. 拆分分区
      • 1.3.3.6. 接合分区(coalesca)
      • 1.3.3.7. 重命名表分区
      • 1.3.3.8. 相关查询
        • 1.3.3.8.1. 跨分区查询
        • 1.3.3.8.2. 查询表上有多少分区
        • 1.3.3.8.3. 查询索引信息
        • 1.3.3.8.4. --显示数据库所有分区表的信息：
        • 1.3.3.8.5. --显示当前用户可访问的所有分区表信息:
        • 1.3.3.8.6. --显示当前用户所有分区表的信息：
        • 1.3.3.8.7. --显示表分区信息 显示数据库所有分区表的详细分区信息：
        • 1.3.3.8.8. --显示当前用户可访问的所有分区表的详细分区信息：
        • 1.3.3.8.9. --显示当前用户所有分区表的详细分区信息：
        • 1.3.3.8.10. --显示子分区信息 显示数据库所有组合分区表的子分区信息：
        • 1.3.3.8.11. --显示当前用户可访问的所有组合分区表的子分区信息：
        • 1.3.3.8.12. --显示当前用户所有组合分区表的子分区信息：
        • 1.3.3.8.13. --显示分区列 显示数据库所有分区表的分区列信息：
        • 1.3.3.8.14. --显示当前用户可访问的所有分区表的分区列信息：
        • 1.3.3.8.15. --显示子分区列 显示数据库所有分区表的子分区列信息：
        • 1.3.3.8.16. --显示当前用户可访问的所有分区表的子分区列信息：
        • 1.3.3.8.17. --显示当前用户所有分区表的子分区列信息：
        • 1.3.3.8.18. --怎样查询出 oracle 数据库中所有的的分区表
        • 1.3.3.8.19. --删除一个表的数据是
        • 1.3.3.8.20. --删除分区表一个分区的数据是
    • 1.3.4. 表状态查询
      • 1.3.4.1. 查询分区表信息
      • 1.3.4.2. 查询表上的索引信息
    • 1.3.5. DDL 语句操作
      • 1.3.5.1. 创建表
      • 1.3.5.2. 获得有关表的信息可以通过如下操作获取有关表的信息：
      • 1.3.5.3. 获得表中列的信息
      • 1.3.5.4. 修改表
        • 1.3.5.4.1. 添加列
        • 1.3.5.4.2. 修改列
        • 1.3.5.4.3. 修改列的长度
        • 1.3.5.4.4. 修改数字列的精度
        • 1.3.5.4.5. 修改列的数据类型
        • 1.3.5.4.6. 修改列的默认值
        • 1.3.5.4.7. 删除列
        • 1.3.5.4.8. 删除表数据，保留表结构
  • 1.4. 视图操作
    • 1.4.1. 视图创建
      • 1.4.1.1. 创建简单视图
      • 1.4.1.2. 创建复杂视图
    • 1.4.2. 视图操作
      • 1.4.2.1. 查看视图
      • 1.4.2.2. 视图上执行 DML 操作
      • 1.4.2.3. 配置拒绝 DML 操作
      • 1.4.2.4. 视图删除
  • 1.5. 索引、约束操作
    • 1.5.1. 单表索引
      • 1.5.1.1. 索引创建：
      • 1.5.1.2. 索引重建：
        • 1.5.1.2.1. 在线重建索引.
      • 1.5.1.3. 手动拓展索引的空间
      • 1.5.1.4. 收回未用到的空间
      • 1.5.1.5. 索引碎片整理
      • 1.5.1.6. 标识索引是否使用过
      • 1.5.1.7. 取消监控
      • 1.5.1.8. 索引压缩：
      • 1.5.1.9. 索引删除：
      • 1.5.1.10. 索引查看：
        • 1.5.1.10.1. 索引相关信息
        • 1.5.1.10.2. 索引列对照信息
        • 1.5.1.10.3. 索引存储信息
    • 1.5.2. 分区表索引
      • 1.5.2.1. 索引创建：
        • 1.5.2.1.1. 局部索引
        • 1.5.2.1.2. 全局索引
      • 1.5.2.2. 索引重建：
      • 1.5.2.3. 索引删除：
      • 1.5.2.4. 索引查看：
        • 1.5.2.4.1. 索引相关信息
        • 1.5.2.4.2. 索引列对照信息
        • 1.5.2.4.3. 索引存储信息
    • 1.5.3. 主键、约束和唯一索引
      • 1.5.3.1. 基本概念
        • 1.5.3.1.1. 唯一索引
        • 1.5.3.1.2. 主键索引
        • 1.5.3.1.3. 主键和唯一索引的一些比较：
        • 1.5.3.1.4. 约束和唯一索引比较：
      • 1.5.3.2. 唯一索引创建
        • 1.5.3.2.1. 分区表创建唯一索引
        • 1.5.3.2.2.
        • 1.5.3.2.3. 单表创建唯一索引：
        • 1.5.3.2.4. 唯一索引重建：
        • 1.5.3.2.5. 唯一索引删除：
        • 1.5.3.2.6. 索引查看：
          • 1.5.3.2.6.1. DBA 查看索引分区
          • 1.5.3.2.6.2. USER 查看索引分区
          • 1.5.3.2.6.3. DBA 查看索引分区类型
          • 1.5.3.2.6.4. USER 查看索引分区类型
      • 1.5.3.3. 约束创建：
        • 1.5.3.3.1. ORACLE 支持五种类型的完整性约束
        • 1.5.3.3.2. 定义约束
        • 1.5.3.3.3. 创建约束：
          • 1.5.3.3.3.1. 列级的约束定义
          • 1.5.3.3.3.2. 表级约束的定义
          • 1.5.3.3.3.3. NOT NULL 约束
          • 1.5.3.3.3.4. UNIQUE 约束
          • 1.5.3.3.3.5. PRIMARY KEY 约束
          • 1.5.3.3.3.6. foreign key 约束
          • 1.5.3.3.3.7. CHECK 约束
        • 1.5.3.3.4. 添加约束
          • 1.5.3.3.4.1. 添加外键约束
        • 1.5.3.3.5. 删除约束
        • 1.5.3.3.6. 关闭约束
        • 1.5.3.3.7. 打开约束
        • 1.5.3.3.8. 约束信息查看：
  • 1.6. 存储过程/job/函数/触发器操作
    • 1.6.1. 存储过程和函数
      • 1.6.1.1. 存储过程有很多优点
      • 1.6.1.2. 存储过程创建
        • 1.6.1.2.1. 创建权限
        • 1.6.1.2.2. 创建语法
      • 1.6.1.3. 删除存储过程
        • 1.6.1.3.1. 删除权限
        • 1.6.1.3.2. 删除语法
      • 1.6.1.4. 重新编译存储过程
        • 1.6.1.4.1. 编译权限
        • 1.6.1.4.2. 编译语法
      • 1.6.1.5. 执行存储过程
        • 1.6.1.5.1. 执行权限
        • 1.6.1.5.2. 执行语法
      • 1.6.1.6. 案例：
        • 1.6.1.6.1. 【训练 1】创建一个显示雇员总人数的存储过程。
        • 1.6.1.6.2. 【训练 2】在 pl/sql 程序中调用存储过程。
        • 1.6.1.6.3. 【训练 3】 编写显示雇员信息的存储过程 emp_list，并引用 emp_count 存储过程。
        • 1.6.1.6.4. 【练习 1】编写显示部门信息的存储过程 dept_list，要求统计出部门个数。
          • 1.6.1.6.4.1. 参数类型
          • 1.6.1.6.4.2. 参数定义
        • 1.6.1.6.5. 【训练 1】编写给雇员增加工资的存储过程 change_salary，通过 in 类型的参数传递要增加工资的雇员编号和增加的工资额。
        • 1.6.1.6.6. 【练习 2】创建插入雇员的存储过程 insert_emp，并将雇员编号等作为参数。
        • 1.6.1.6.7. 【训练 2】 调用存储过程 change_salary，不传递参数，使用默认参数值。
        • 1.6.1.6.8. 【训练 3】 使用 out 类型的参数返回存储过程的结果。
        • 1.6.1.6.9. 【训练 4】 使用 in out 类型的参数，给电话号码增加区码。
      • 1.6.1.7. 创建和删除存储函数
        • 1.6.1.7.1. 创建权限
        • 1.6.1.7.2. 创建语法
        • 1.6.1.7.3. 删除权限
        • 1.6.1.7.4. 删除语法
        • 1.6.1.7.5. 重新编译权限
        • 1.6.1.7.6. 重新编译语法
      • 1.6.1.8. 函数
        • 1.6.1.8.1. 调用函数存储过程权限
        • 1.6.1.8.2. 【训练 1】 创建一个通过雇员编号返回雇员名称的函数 get_emp_name。
        • 1.6.1.8.3. 【训练 1】 查询过程 emp_count 的脚本。
        • 1.6.1.8.4. 【训练 2】 查询过程 get_emp_name 的参数。
        • 1.6.1.8.5. 【训练 3】 在发生编译错误时，显示错误。
        • 1.6.1.8.6. 【训练 4】查询 emp_list 存储过程是否可用：
        • 1.6.1.8.7. 【训练 5】 查询 emp_list 存储过程的依赖性。
    • 1.6.2. 程序包
      • 1.6.2.1. 包的概念和组成
        • 1.6.2.1.1. 包有以下优点：
        • 1.6.2.1.2. 创建包和包体
        • 1.6.2.1.3. 包的其他操作命令包括：
        • 1.6.2.1.4. 删除包头：
        • 1.6.2.1.5. 删除包体：
        • 1.6.2.1.6. 重新编译包头：
        • 1.6.2.1.7. 重新编译包体：
        • 1.6.2.1.8. 包的应用
          • 1.6.2.1.8.1. 【训练 1】 创建管理雇员信息的包 employe，它具有从 emp 表获得雇员信息，修改雇员名称，修改雇员工资和写回 emp 表的功能。
          • 1.6.2.1.8.2. 【练习 1】为包增加修改雇员职务和部门编号的功能。
          • 1.6.2.1.8.3. 【训练 1】 完整的雇员包 emp_pk 的创建和应用。
          • 1.6.2.1.8.4. 操作练习
    • 1.6.3. 触发器
      • 1.6.3.1. 触发器的组成部分
      • 1.6.3.2. 触发操作
      • 1.6.3.3. 触发
      • 1.6.3.4. 触发器类型：
    • 1.6.4. Job 创建和操作
      • 1.6.4.1. 设置初始化参数 job_queue_processes
      • 1.6.4.2. dbms_job package 用法介绍
      • 1.6.4.3. 查看相关 job 信息
        • 1.6.4.3.1. 相关视图
        • 1.6.4.3.2. 查看相关信息
        • 1.6.4.3.3. 正在运行的 job 相关信息
        • 1.6.4.3.4. job queue lock 相关信息
      • 1.6.4.4. 实例操作
    • 1.6.5. 后台脚本
      • 1.6.5.1. EOF方式
        • 1.6.5.1.1. oracle
        • 1.6.5.1.2. mysql环境
  • 1.7. 同义词操作
    • 1.7.1. 创建同义词
      • 1.7.1.1. 公共同义词
      • 1.7.1.2. 私有同义词
    • 1.7.2. 删除同义词
      • 1.7.2.1. 删除公共同义词
      • 1.7.2.2. 删除私有同义词
  • 1.8. dblink 操作
    • 1.8.1. 概念
    • 1.8.2. 建立dblink之前需要确认的事项：
• 确认从local database到remote database的网络连接是正常的，tnsping要能成功。
• 确认在remote database上面有相应的访问权限。
  • • 1.8.3. dblink 创建
    • 1.8.4. dblink 操作
      • 1.8.4.1. 查看dblink
    • 1.8.5. database link删除
  • 1.9. Sequences 操作
    • 1.9.1. Sequences 创建
    • 1.9.2. 查看sequence当前值
    • 1.9.3. 查看多有序列
    • 1.9.4. 可以使用 sequence 的地方：
      • 1.9.4.1. 实例：
    • 1.9.5. 注意事项：
    • 1.9.6. alter sequence，修改起始值和最大值
    • 1.9.7. Sequences 删除
  • 1.10. 数据库操作必须掌握技巧
    • 1.10.1. 中断正在执行的 session 连接
    • 1.10.2. 查看进程号：
    • 1.10.3. 用户密码修改和用户账号加锁、解锁
      • 1.10.3.1. a) 用户修改密码：
      • 1.10.3.2. b）用户账号锁定：
      • 1.10.3.3. 账号创建
      • 1.10.3.4. 查询用户拥有的角色权限：
      • 1.10.3.5. 查询用户拥有的系统权限：
      • 1.10.3.6. 完全复制一个Oracle用户的三个权限表
      • 1.10.3.7. 查询用户密码：
      • 1.10.3.8. 授予角色权限
      • 1.10.3.9. 以密文的方式创建用户
      • 1.10.3.10. 用户创建
    • 1.10.4. 查看和修改最大会话数
      • 1.10.4.1. 查询数据库允许的最大连接数：
      • 1.10.4.2. 修改最大会话数
        • 1.10.4.2.1. 查看processes和sessions参数
        • 1.10.4.2.2. 修改流程
    • 1.10.5. 核心参数修改，数据库启动（必须 sys 用户权限）
      • 1.10.5.1. db_files 参数
      • 1.10.5.2. process 参数
    • 1.10.6. 字符集修
    • 1.10.7. 日志切换和初始化文件创建
      • 1.10.7.1. 管理联机重做日志文件
      • 1.10.7.2. LGWR 什么时候开始写
      • 1.10.7.3. 手工切换日志
      • 1.10.7.4. 手工启动检查点
      • 1.10.7.5. 查询当前系统中日志的情况
      • 1.10.7.6. 增加一个新组
      • 1.10.7.7. 给日志组增加一个新成员
      • 1.10.7.8. 删除一个成员
      • 1.10.7.9. 删除一个组
      • 1.10.7.10. 重新放置和重命名联机重做日志文件
        • 1.10.7.10.1. 使用 alter database rename file 命令
        • 1.10.7.10.2. 增加一个新成员，删除旧成员
      • 1.10.7.11. 归档日志设置
        • 1.10.7.11.1. 单实例数据库归档模式的修改：
      • 1.10.7.12. 创建参数文件
        • 1.10.7.12.1. 1)查看 spfile location
          • 1.10.7.12.1.1. 2)从 spfile 获取 pfile
          • 1.10.7.12.1.2. 从 pfile 获取 spfile
        • 1.10.7.12.2. 动态修改参数
        • 1.10.7.12.3. 查看初始化参数
        • 1.10.7.12.4. 查看系统全局区大小
        • 1.10.7.12.5. 使用 pfile/spfile 启动数据库
        • 1.10.7.12.6. 查看系统是以 pfile 还是 spfile 启动
        • 1.10.7.12.7. 查询 oracle 游标使用情况的方法
      • 1.10.7.13. 控制文件备份和重建
      • 1.10.7.14. 操作小技巧
        • 1.10.7.14.1. Oracle 安装完成后用户初始口令
        • 1.10.7.14.2. ORACLE9I AS WEB CACHE 的初始默认用户和密码
        • 1.10.7.14.3. 怎么创建数据库
          • 1.10.7.14.3.1. 1.dbca
          • 1.10.7.14.3.2. 脚本方式创建
        • 1.10.7.14.4. oracle 中的裸设备指的是什么
        • 1.10.7.14.5. 如何解锁怎样解除 PROCEDURE 被意外锁定?
        • 1.10.7.14.6. sqlplus 下如何修改编辑器
        • 1.10.7.14.7. oracle 产生随机函数
        • 1.10.7.14.8. 如何才能得知系统当前的 SCN 号（必须要 sys 用户执行）
        • 1.10.7.14.9. 如何在 ORACLE 中取毫秒
        • 1.10.7.14.10. 如何在字符串里加回车？
        • 1.10.7.14.11. 中文是如何排序的？
• 2. 常用查询和操作
  • 2.1. 会话相关

1.1. 表空间操作

1.1.1. 查询所有表空间使用率

SELECT a.tablespace_name,  
         ROUND (a.total_size) "total_size(GB)",  
         ROUND (a.total_size) - ROUND (b.free_size, 2) "used_size(GB)",  
         ROUND (b.free_size, 2) "free_size(GB)",  
         ROUND (b.free_size / total_size * 100, 2) || '%' free_rate  
FROM ( SELECT tablespace_name, SUM (bytes) /1024 /1024/1024total_size  
              FROM dba_data_files  
          GROUP BY tablespace_name) a,  
         ( SELECT tablespace_name, SUM (bytes) /1024/1024/1024 free_size  
              FROM dba_free_space
          GROUP BY tablespace_name) b  
WHERE a.tablespace_name = b.tablespace_name(+);

set linesize 200  pages 999
col TBS_NAME  for a25
set space 2
comp sum of  "TOTAL_GB" "USED_GB" "FREE_GB" on report
break on report
select a.TABLESPACE_NAME tbs_name,
       round(a.BYTES / 1024 / 1024 /1024) Total_GB,
       round((a.BYTES - nvl(b.BYTES, 0)) / 1024  / 1024 /1024) Used_GB,
       round((1 - ((a.BYTES - nvl(b.BYTES, 0)) / a.BYTES)) * 100, 2) Pct_Free,
       round(((a.BYTES - nvl(b.BYTES, 0)) / a.BYTES) * 100, 2) Pct_Used,
       nvl(round(b.BYTES / 1024 /1024 / 1024 ), 0) Free_GB,
       auto
  from (select TABLESPACE_NAME, sum(BYTES) BYTES, max(AUTOEXTENSIBLE) AUTO
          from sys.dba_data_files
         group by TABLESPACE_NAME) a,
       (select TABLESPACE_NAME, sum(BYTES) BYTES
          from sys.dba_free_space
         group by TABLESPACE_NAME) b
where
/*a.TABLESPACE_NAME='please replease tbs_name over here'   and      */
a.TABLESPACE_NAME = b.TABLESPACE_NAME(+)
order by ((a.BYTES - b.BYTES) / a.BYTES) desc
/

1.1.2. 查询表空间文件名

select count(*) from dba_data_files where tablespace_name='MUSICLOGIDX';
select * from dba_data_files where tablespace_name='MUSICLOGIDX';
select * from dba_data_files where tablespace_name='TBS_ZBA_DMA'and file_name like '%2017%

1.1.2.1. 创建表空间：

1.1.2.1.1. 数据表空间：

create tablespace tablespace_name logging datafile '/XXX/xxx/datafile_name1.ora' size 4095M;

1.1.2.1.2. 临时表空间：

create temporay tablespae tmp_tablespace_name tempfile '/XXX/xxx/datafile_name1.ora' size 4095M;

1.1.2.1.3. 回滚表空间：

create undo tablespace undotbs_tablespace datafile '/XXX/xxx/datafile_name1.ora' size 4095M;

1.1.2.2. 增加表空间：

1.1.2.2.1. 数据表空间：

 alter tablespace tablespace_name add datafile '/XXX/xxx/datafile_name1.ora' size 4095M;

1.1.2.2.2. 临时表空间：

alter tablespace temp_tablespace_name add tempfile '/XXX/xxx/datafile_name1.ora' size 4095M;

1.1.2.2.3. 回滚表空间：

alter tablespace undotbs_tablespace_name add datafile '/XXX/xxx/datafile_name1.ora' size 4095M;

1.1.2.3. 删除表空间：

1.1.2.3.1. 数据表空间：

drop tablespace tablespace_name；
alter database datafile '/xxx/xxx/undotbs_datafile_name1.dbf' offline drop

1.1.2.3.2. 临时表空间：

drop tablespace tablespace_name；
alter database datafile '/xxx/xxx/undotbs_datafile_name1.dbf' offline drop

注意，应该先创建好临时表空间，并使之生效，然后才能再删除现有临时表空间

1.1.2.3.3. 回滚段表空间：

drop tablespace tablespace_name；
alter database datafile '/xxx/xxx/undotbs_datafile_name1.dbf' offline drop

注意，应该先创建好回滚段表空间，并使之生效，然后才能再删除现有回滚段表空间

1.1.2.4. 表空间大小查看：

set linesize 300
set pagesize 999
SELECT a.tablespace_name,  
         ROUND (a.total_size) "total_size(GB)",  
         ROUND (a.total_size) - ROUND (b.free_size, 2) "used_size(GB)",  
         ROUND (b.free_size, 2) "free_size(GB)",  
         ROUND (b.free_size / total_size * 100, 2) || '%' free_rate  
FROM ( SELECT tablespace_name, SUM (bytes) /1024 /1024/1024total_size  
              FROM dba_data_files  
          GROUP BY tablespace_name) a,  
         ( SELECT tablespace_name, SUM (bytes) /1024/1024/1024 free_size  
              FROM dba_free_space
          GROUP BY tablespace_name) b  
WHERE a.tablespace_name = b.tablespace_name(+);


set linesize 300
set pagesize 999
select df.tablespace_name "tablespace_name", totalspace "totalspacem", freespace "freespacem",
round((1 - freespace / totalspace) * 100, 2) "usedspace%"
from (select tablespace_name, round(sum(bytes) / 1024 / 1024) totalspace from dba_data_files group by
tablespace_name) df,(select tablespace_name, round(sum(bytes) / 1024 / 1024) freespace from
dba_free_space group by tablespace_name) fs
where df.tablespace_name = fs.tablespace_name
order by 4 asc;

1.1.3. 裸卷表空间管理

1.1.3.1. 创建表空间：

1.1.3.1.1. 数据表空间：

create tablespace tablespace_name datafile '/dev/vgname/rlv_name1.dbf' size 4095M; 

1.1.3.1.2. 临时表空间：

create tablespace temp tablespace_name datafile '/dev/vgname/rlv_name2.dbf' size 4095M; 

1.1.3.1.3. 回滚段表空间：

create undo tablespace undotbs_tablespace_name datafile 'dev/vgname /rlv_name1.dbf' SIZE 
4095M

1.1.3.2. 增加表空间：

1.1.3.2.1. 数据表空间：

alter tablespace tablespace_name add datafile '/dev/vgname/rlv_name1.dbf' size 4095M; 

1.1.3.2.2. 临时表空间：

alter tablespace tmp_tablespace_name add datafile '/dev/vgname/rlv_name1.dbf' size 4095M; 

1.1.3.2.3. 回滚段表空间：

alter tablespace undotbs_tablespace_name add datafile '/dev/vgname/rlv_name1.dbf' size 4095M;

1.1.3.3. 删除表空间：

1.1.3.3.1. 数据表空间：

drop tablespace tablespace_name；
alter database datafile '/xxx/xxx/undotbs_datafile_name1.dbf' offline drop

1.1.3.3.2. 临时表空间：

 drop tablespace tablespace_name；
 alter database datafile '/xxx/xxx/undotbs_datafile_name1.dbf' offline drop

注意，应该先创建好临时表空间，并使之生效，然后才能再删除现有临时表空间

1.1.3.3.3. 回滚段表空间：

drop tablespace tablespace_name；
alter database datafile '/xxx/xxx/undotbs_datafile_name1.dbf' offline drop

注意，应该先创建好回滚段表空间，并使之生效，然后才能再删除现有回滚段表空间

1.2. 用户、角色、权限

1.2.1. 用户创建、修改、删除

1.2.1.1. 用户创建

create user username profile default identified by "user_passwd" default tablespace 
tablespace_name account unlock;

1.2.1.2. 修改用户密码：

alter user username identified by char_name；

1.2.1.3. 修改用户使用状态

alter user username account unlock

1.2.1.4. 用户赋权限：

grant connect,resource,select any table,update any table,delete any table,insert any 
table,select 
 any dictionary,create any procedure,execute any procedure,create any TRIGGER,create 
any view,
 drop any view,create any sequence,select any sequence,drop any sequence,CREATE 
DATABASE LINK,
 CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK,CREATE 
ANY synonym,DROP ANY synonym,
 CREATE PUBLIC synonym,DROP PUBLIC SYNONYM,SELECT_CATALOG_ROLE
 to &u;

1.2.1.5. 给某一个用户赋予某张表的某种权限：

grant privilege_name（select insert update） table_name to username;

1.2.1.6. 用户权限回收：

revoke role_name from username; 
alter user username default role all;

1.2.1.7. 用户删除：

Drop user username cascade （如果有数据，带 cascade 参数）

1.2.1.8. 查看用户属性和状态：

select * from dba_users;
select * from dba_sys_privs
select * from dba_tab_privs

1.2.2. 权限分类：

administer database trigger debug any procedure 
administer resource manager debug connect session 
alter any cluster delete any table 
alter any dimension dequeue any queue 
alter any evaluation context drop any cluster 
alter any index drop any context 
alter any indextype drop any dimension 
alter any library drop any directory 
alter any outline drop any evaluation context 
alter any procedure drop any index 
alter any role drop any indextype 
alter any rule drop any library 
alter any rule set drop any operator 
alter any sequence drop any outline 
alter any snapshot drop any procedure 
alter any table drop any role 
alter any trigger drop any rule 
alter any type drop any rule set 
alter database drop any sequence 
alter profile drop any snapshot 
alter resource cost drop any synonym 
alter rollback segment drop any table 
alter session drop any trigger 
alter system drop any type 
alter tablespace drop any view 
alter user drop profile 
analyze any drop public database link 
audit any drop public synonym 
audit system drop rollback segment 
backup any table drop tablespace 
become user drop user 
comment any table enqueue any queue 
create any cluster execute any evaluation context
create any context execute any indextype 
create any dimension execute any library 
create any directory execute any operator 
create any evaluation context execute any procedure 
create any index execute any rule 
create any indextype execute any rule set 
create any library execute any type 
create any operator exempt access policy 
create any outline flashback any table 
create any procedure force any transaction 
create any rule force transaction 
create any rule set global query rewrite 
create any sequence grant any object privilege 
create any snapshot grant any privilege 
create any synonym grant any role 
create any table insert any table 
create any trigger lock any table 
create any type manage any queue 
create any view manage tablespace 
create cluster on commit refresh 
create database link query rewrite 
create dimension restricted session 
create evaluation context resumable 
create indextype select any dictionary 
create library select any sequence 
create operator select any table 
create procedure under any table 
create profile under any type 
create public database link under any view 
create public synonym unlimited tablespace 
create role update any table 
create rollback segment create snapshot 
create rule create synonym 
create rule set create table 
create sequence create tablespace
create session create trigger 
create user create type 
create view

1.2.3. 角色创建、修改、删除

1.2.3.1. 角色创建

create role role_name;

1.2.3.2. 角色权限修改

grant privilege_name to role_name
revoke privilege_name from role_name

1.2.3.3. 角色删除

drop role role_name

1.2.3.4. 权限增加和收回

grant privilege_name to role_name （增加权限）
revoke privilege_name from role_name （收回权限）

1.2.4. 系统角色分类

AQ_ADMINISTRATOR_ROLE IMP_FULL_DATABASE 
AQ_USER_ROLE JAVADEBUGPRIV 
AUTHENTICATEDUSER JAVAIDPRIV 
CONNECT JAVASYSPRIV 
CTXAPP JAVAUSERPRIV 
DBA JAVA_ADMIN 
DELETE_CATALOG_ROLE JAVA_DEPLOY 
EJBCLIENT LOGSTDBY_ADMINISTRATOR
EXECUTE_CATALOG_ROLE OEM_MONITOR 
EXP_FULL_DATABASE RECOVERY_CATALOG_OWNER
GATHER_SYSTEM_STATISTICS RESOURCE 
HS_ADMIN_ROLE SALES_HISTORY_ROLE 
WKUSER SELECT_CATALOG_ROLE 
WM_ADMIN_ROLE 
XDBADMIN

1.3. 表操作

1.3.1. 单表创建

1.3.1.1. 直接创建

Create table table_name 
(
字段 1 类型 1，
字段 2 类型 2，
、、、、、
字段 n 类型 n
) tablespace_name;

1.3.1.2. 创建类似表

Create table table_name as select * from table_name1;

1.3.1.3. 创建表结构：

Create table table_name as select * from table_name 字段 1=字段 2；

1.3.1.4. 表查询：

Select * from table_name;
Select * from table_name where 条件
Select 字段 1 别名 1，字段 2 别名 2，字段 n 别名 n from table_name
字段中可以进行代数运算，包括：＋ － × ÷，count sun avge trunk round 等函数

1.3.1.5. 表插入：

Insert 字段 1，字段 2、、字段 n into table_name values (字段值 1，字段值 2、、字段值 n )；

1.3.1.6. 循环插入：

begin 
 for i in 1..10 loop 
 insert into table_name values (...); 
 end loop; 
 end;

1.3.1.7. 表更新：

Update table_name set 字段 1 where 条件

1.3.1.8. 表删除：

drop table table_name

1.3.1.9. 表记录删除：

delete table_name where 条件；
truncate table table_name 清空表记录，保留表结构

1.3.1.10. 表名修改：

rename table_name1 to table_name2
注意：表名修改后，索引还在

1.3.1.11. 表迁移

（从一个表空间迁移到另外一个表空间）
alter table table_name move tablespace tablespace_name nologging parallele 4 ;

注意：将表从一个表空间迁移到另外一个表空间，必须进行索引重建，存储过程、触发器、
其他程序包都需要编译，以免执行报错。例如：

1.3.1.12. 表压缩：

不同表空间： alter table table_name move tablespace tablespace_name compress;
相同表空间：alter table table_name move compress;

1.3.1.13. 表统计信息收集：

exec dbms_stats.gather_table_stats(ownname => 'username',tabname 
=>'table_name',degree =>10,cascade =>true,estimate_percent =>25);

例：

exec dbms_stats.gather_table_stats(ownname => 'test',tabname 
=>'data_table1_name1',degree =>4,cascade =>true,estimate_percent =>30);

查看最后一次收集统计信息的时间
select owner,table_name,num_rows,last_analyzed from dba_tables where table_name in ('TEST') order by table_name;

表语句执行很慢，检查执行计划，zk.cm_busi_charge_201001 b 不引用索引，做表分析、重建索引都无效，执行计划中均没有引用索引，最后强制使用索引效果明显，执行计划改变，索引引用。效果明显

select a.serv_id, sum(nvl(b.item_value, 0))
from zk.cm_busi_201001 a, zk.cm_busi_charge_201001 b
where a.so_nbr = b.so_nbr and a.busi_code in (1, 4, 5) and a.serv_id = 2222732774 and
b.book_item_id in (21000013, 21000100, 21000101, 23000003, 27000002, 
24000032)
group by a.serv_id;

优化后
select /*+ index(b pk_cm_busi_charge_201001)*/
a.serv_id, sum(nvl(b.item_value, 0))
from zk.cm_busi_201001 a, zk.cm_busi_charge_201001 b
where a.so_nbr = b.so_nbr and a.busi_code in (1, 4, 5) and a.serv_id = 2222732774 and
book_item_id in (21000013, 21000100, 21000101, 23000003, 27000002, 
24000032)
group by a.serv_id;

1.3.2. 分区表创建

为了使大量的数据在读写操作和查询中速度更快，Oracle 提供了对表和索引进行分区的技术，以改善大型应用系统的性能。使用分区的优点：

A、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
B、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
C、均衡 I/O：可以把不同的分区映射到磁盘以平衡 I/O，改善整个系统性能；
D、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度

Oracle 数据库提供对表或索引的分区方法有三种：

1、范围分区
2、Hash 分区（散列分区）
3、列表分区 
4、范围—散列分区 
5、范围—列表分区

1.3.2.1. 范围分区表创建

分区表表创建：一般都是按照时间来创建

create table table_name
(
 row1 char1 not null,
 row2 char2 not null,
 、、、、、、
 rown number not null
)
partition by range (paration_row)
(
 partition partition_name1 values less than (to_date('value_name', 'yyyy-mm-dd',
'nls_calendar=gregorian')) tablespace tablespace_name1,
 partition partition_name2 values less than (to_date('value_name', 'yyyy-mm-dd', 
'nls_calendar=gregorian')) tablespace tablespace_name2,
 、、、、、、、、、、
 partition partition_namen values less than (to_date('value_name', 'yyyy-mm-dd', 
'nls_calendar=gregorian')) tablespace tablespace_namen,
 partition partmax values less than (maxvalue)
 tablespace ora_data
 pctfree 10
 initrans 16
 maxtrans 255
 storage
 (
 initial 64k
 minextents 1
 maxextents unlimited ))

1.3.2.1.1. 分区表插入

Insert into table_name1 as select * from table_name2;

1.3.2.1.2. 表分区删除：

alter table 表名 truncate partition 分区名称 drop storage;

注意：分区删除以后，需要重新创建索引。

1.3.2.1.3. 分区分割：

alter table table_name split partition partmax at(to_date('values_name', 'yyyy-mm-dd')) into 
(partition prtition_namexx tablespace tablespace_name , partition partmax);

1.3.2.1.4. 分区查询：

select * from table_name partition (partition_name);

1.3.2.2. hash 分区表（散列分区）

1.3.2.2.1. Hash 分区表创建：

hash 分区表的分区数量一般是 2 的 n 次幂，这样记录分布在各个分区上就比较均匀，可以进行 I/O 的均衡。

create table table_name
(
 row1 char1 not null,
 row2 char2 not null,
 、、、、、、
 rown NUMBER not null
)
partition by hash (paration_row)
(
 partition partition_name1 tablespace tablespace_name1,
 partition partition_name2 tablespace tablespace_name2,
 partition partition_name3 tablespace tablespace_name3
 、、、、、、、、、、
 partition partition_nameX tablespace tablespace_nameX
);

1.3.2.2.2. 表插入：

insert into table_name1 as select * from table_name2

1.3.2.2.3. 分区查询：

Select * From table_name Partition (partition_name);

hash 分区表一般由于数据分布均衡性，查询不是通过对分区的操作进行的。update 、insert 操作同普通表。

1.3.2.3. 列表分区表

1.3.2.3.1. 散列分区表创建：

create table table_name
(
 row1 char1 not null,
 row2 char2 not null,
 、、、、、、
 rown number not null
)
partition by list (paration_row)
(
 partition partition_name1 values (row_value1,row_value3,row_value8) tablespace 
tablespace_name1,
 partition partition_name2 values (row_value4,row_value6,row_value7) tablespace 
tablespace_name2,
 partition partition_name3 values 
(row_value9,row_value10,row_value11,row_value12,row_value15) 
tablespace tablespace_name3
 、、、、、、、、、、
 partition partition_nameX values (default) tablespace tablespace_nameX
);

表的插入、更新、删除和普通表相同，

1.3.2.3.2. 数据清理

在 hash 分区和 list 分区中，比较困难的操作是：从一张5 千万以上的表中，要删除一千万条记录比较困，可以通过以下方法进行清理：

第一种方法：
a 创建中间表和表备份，
b 进行数据插入，
c 进行锁表，
d 进行表名修改，
e 进行索引创建和存储过程编译
f 进行表 truncate 操作


第二种方法：
直接创建 job 进行 delete 删除：这样长期做，会降低表的执行效果
a 进行备份数据，
b 进行表记录删除，每次删除 1000——2000 条记录，不然会发生锁表，
c 重建索引，
d 进行表分析。

1.3.2.3.2.1. 创建删除的存储过程

一次删除少量数据，不能一次删除大批量数据，否者会导致长时间锁表

declare
v_lognum number; -- 数据库中拥有的日志文件数
v_needarc number; -- 需要归档的日志文件数
begin
select count(1) into v_lognum from v$log;
loop
loop
select count(1) into v_needarc from v$archive;
if v_needarc < v_lognum - 1 then
exit;
end if;
end loop;
delete from 对象.table_name where 条件 1 and 条件 2 and rownum<1000;
if sql%rowcount = 0 then
exit;
end if;
commit;
end loop;
end;


––––––————— -- - - - - - - - - - - - - - - - - - - - - - -
create or replace procedure procedure_name(exp_date1 varchar2 ,exp_date2 varchar2)
as
 del_sql varchar2(1024);
 v_c integer;
 begin
del_sql:='delete from 对象.表名 a where exists (select 1 from 对象.表名 b where 
条件 1 and
条件 2 and 、、、、
条件 n ）and rownum<1000';
 for i in 1..10000 loop
 execute immediate del_sql using exp_date1,exp_date2;
 if sql%rowcount = 0 then
 exit;
 end if;
 commit;
 end loop;
 end procedure_name;
 /

1.3.3. 分区操作

1.3.3.1. 添加分区

alter table table_partition_name add partition partition_name values less than 
(to_date('row_values','yyyy-mm-dd')); 
注意：以上添加的分区界限应该高于最后一个分区界限。

alter table table_partition_name modify partition partition_name add subpartition 
subpartition_name 
values('row_values');

1.3.3.2. 删除分区

以下代码删除表分区：

alter table table_partition_name drop partition partition_name storage; 

注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删
除表

1.3.3.3. 截断分区

截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当
表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：

alter table table_partition_name truncate partition partition_name;

1.3.3.4. 合并分区

合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不
能将分区合并到界限较低的分区。以下代码实现了 partition_name1 与 partition_name 分区的合
并：

alter table table_partition_name merge partitions partition_name1,partition_name2 into partition 
partition_name2;

1.3.3.5. 拆分分区

拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对 HASH 类型的
分区进行拆分。

alter table table_partition_name sblit partition partition_name1 
at(to_date('row_value','yyyy-mm-dd')) 
into (partition partition_name1,partition partition_name2); 

1.3.3.6. 接合分区(coalesca)

结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增
加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行
接合分区：

alter table table_hash_partition_name coalesca partition;

1.3.3.7. 重命名表分区

以下代码将 partition_name1 更改为 partition_name2

alter table table_partition_name rename partition partition_name1 to partition_name2;

1.3.3.8. 相关查询

1.3.3.8.1. 跨分区查询

select sum( *) from 
(select count(*) cn from table_partition_name partition (partition_name1) 
union all 
select count(*) cn from table_partition_name partition (partition_name2)); 

1.3.3.8.2. 查询表上有多少分区

select * from user_tab_partitions where table_name ='table_partition_name' ;

1.3.3.8.3. 查询索引信息

select object_name,object_type,tablespace_name,sum(value) 
from v$segment_statistics 
where statistic_name in ('physical reads','physical write','logical reads')and object_type='index' 
group by object_name,object_type,tablespace_name order by 4 desc;

1.3.3.8.4. --显示数据库所有分区表的信息：

select * from dba_part_tables;

1.3.3.8.5. --显示当前用户可访问的所有分区表信息:

select * from all_part_tables 

1.3.3.8.6. --显示当前用户所有分区表的信息：

select * from USER_PART_TABLES 

1.3.3.8.7. --显示表分区信息 显示数据库所有分区表的详细分区信息：

select * from dba_tab_partitions

1.3.3.8.8. --显示当前用户可访问的所有分区表的详细分区信息：

select * from all_tab_partitions 

1.3.3.8.9. --显示当前用户所有分区表的详细分区信息：

select * from user_tab_partitions 

1.3.3.8.10. --显示子分区信息 显示数据库所有组合分区表的子分区信息：

select * from dba_tab_subpartitions 

1.3.3.8.11. --显示当前用户可访问的所有组合分区表的子分区信息：

select * from all_tab_subpartitions 

1.3.3.8.12. --显示当前用户所有组合分区表的子分区信息：

select * from user_tab_subpartitions 

1.3.3.8.13. --显示分区列 显示数据库所有分区表的分区列信息：

select * from dba_part_key_columns 

1.3.3.8.14. --显示当前用户可访问的所有分区表的分区列信息：

select * from all_part_key_columns 
##### 1.3.3.8.15. --显示当前用户所有分区表的分区列信息：

select * from user_part_key_columns 

1.3.3.8.15. --显示子分区列 显示数据库所有分区表的子分区列信息：

select * from dba_subpart_key_columns 

1.3.3.8.16. --显示当前用户可访问的所有分区表的子分区列信息：

select * from all_subpart_key_columns 

1.3.3.8.17. --显示当前用户所有分区表的子分区列信息：

select * from user_subpart_key_columns 

1.3.3.8.18. --怎样查询出 oracle 数据库中所有的的分区表

select * from user_tables a where a.partitioned='yes' 

1.3.3.8.19. --删除一个表的数据是

truncate table table_name; 

1.3.3.8.20. --删除分区表一个分区的数据是

alter table table_partition_name truncate partition partition_nameN drop storage;

1.3.4. 表状态查询

1.3.4.1. 查询分区表信息

Select * From Dba_Tab_Partitions Where Table_Name=’%表名%’;

1.3.4.2. 查询表上的索引信息

Select owner,index_name,status,degree,table_name from dba_indexes where 
table_name=’table_name ’;

select owner,bytes/1024/1024,segment_name,segment_type,tablespace_name from 
dba_segments where 
owner='SCOTT' and segment_type='EMP';

1.3.5. DDL 语句操作

1.3.5.1. 创建表

基本语法：

create [global temporary] table table_name(
column_name type [constraint constraint_def default default_exp]
[,column_name type [constraint constraint_def default default_exp] ...])
[on commit {delete | preserve} rows]
tablespace tab_space;

其中：

1)global temporary 说明改表的行都是临时的，这种表就称为临时表。行的有效期由 on commit 字句指定。临时表对于所有的会话都是可见的，但是这些行则是特定于某个会话的。
2)table_name 指定了要分配给该表的名称。
3)column_name 指定了要分配给某个列的名称。
4)type 指定了对某个列的类型。
5)constraint_def 指定了对某个列的约束的定义。
6)default_def 指定了一个表达式，用来为某个列赋予默认值。
7)on commit 控制临时表中行的有效期。delete 说明这些行在事务的末尾要被删除。preserve 说明这些行在会话的末尾要被删除。若对临时表没有指定 on commit 选项，那末默认值是 delete。
8)tab_space 为该表指定表空间。若没有指定表空间，该表就被存储在该用户的默认表空间中。

1.3.5.2. 获得有关表的信息可以通过如下操作获取有关表的信息：

1.对表执行 describe 命令。

desc order_status_temp

--注意 desc 是 SQL*PLUS 命令，在 SQL 中不能执行。
2.查询 user_tables，它是数据字典的一部分。另：查询用户可访问的表的相关信息，可以查询all_tables。

select table_name, tablespace_name, temporary
from user_tables
where table_name in ('EMP', 'DEPT');

1.3.5.3. 获得表中列的信息

从 user_tab_columns 中可以获得有关表中各列的信息,另：通过访问 all_tab_columns,可以获得有
关可以访问的表中所有列的信息。

select column_name, data_type, data_length, data_precision, data_scale
 from user_tab_columns
where table_name = 'table_name';

1.3.5.4. 修改表

alter table 语句可以用于对表进行修改。
alter table 语句可以执行以下任务：
1)添加、修改、删除列；
2)添加或删除约束；
3)启用或禁用约束

1.3.5.4.1. 添加列

alter table table_name add modified_by integer;
alter table table_name add rowname 类型 [date default sysdate not null];

1.3.5.4.2. 修改列

1)修改列的长度，条件是该列的类型的长度可以修改，比如 char 或 varchar2；
2)修改数字列的精度；
3)修改列的数据类型；
4)修改列的默认值。

1.3.5.4.3. 修改列的长度

alter table table_name modify rowname 类型(长度); 

只有在表中还没有任何行或所有列都为空值的情况下才可以减小列的长度。

1.3.5.4.4. 修改数字列的精度

alter table table_name modify column 类型;

只有在表中还没有任何行或列为空值时才可以减小数字列的精度。

1.3.5.4.5. 修改列的数据类型

alter table table_name modify column 类型;

若一个表中还没有任何行或列为空值，就可以将列修改为任何一种数据类型。否则，就只能将列
的数据类型修改为一种兼容的数据类型。
例如，可以将 varchar2 类型修改为 char，条件是没有缩短列的长度；但是不能将 date 修改为
number。

1.3.5.4.6. 修改列的默认值

alter table table_name modify column column_name;

默认值只适用于新插入表中的行。

1.3.5.4.7. 删除列

alter table table_name drop column column_name;
重命名表

rename 语句可以用于对表进行重命名。
rename table_name1 table_name2;

1.3.5.4.8. 删除表数据，保留表结构

truncate table table_name;
DELETE from table_name;(改删除操作会记录操作数据到日志，删除会很慢)

1.4. 视图操作

视图是基于一张表或多张表或另外一个视图的逻辑表。视图不同于表，视图本身不包含任何
数据。表是实际独立存在的实体，是用于存储数据的基本结构。而视图只是一种定义，对应一个
查询语句。视图的数据都来自于某些表，这些表被称为基表。通过视图来查看表，就像是从不同
的角度来观察一个(或多个)表。

**视图有如下一些优点： **
a 可以提高数据访问的安全性，通过视图往往只可以访问数据库中表的特定部分，限制了用户访问表的全部行和列。
b 简化了对数据的查询，隐藏了查询的复杂性。视图的数据来自一个复杂的查询，用户对视图的检索却很简单。
c 一个视图可以检索多张表的数据，因此用户通过访问一个视图，可完成对多个表的访问。
d 视图是相同数据的不同表示，通过为不同的用户创建同一个表的不同视图，使用户可分别访问同一个表的不同部分。 视图可以在表能够使用的任何地方使用，但在对视图的操作上同表相比有些限制，特别是插入和修改操作。对视图的操作将传递到基表，所以在表上定义的约束条件和触发器在视图上将同样起作用。

（比如：如果有一个复杂查询需要定期执行，那么可以将这个复杂查询的部分拆分出来，然后建成视图，然后保存结构即可）

1.4.1. 视图创建

create [or replace] [force|noforce] view view_name
 [(alias[,alias]...)]
 as subquery
[with check option [constraint constraint]]
 [with read only]

1.4.1.1. 创建简单视图

例如：
创建视图 empv，该属兔仅包含 10 部门雇员的细节信息。

 create view empv
 as select empno,ename,job
 from emp
 where deptno=10;

别名的使用
例 1：

 create or replace view salv
 as select empno employee_id,ename name,sal salary
 from emp where deptno=30;

例 2：

 create or replace view salv
 (employee_id,name,salary)
 as select empno,ename,sal
 from emp
 where deptno=30;

1.4.1.2. 创建复杂视图

例：创建一个基于两个表并且含有组函数的复杂视图

 create or replace view dept_sum_v(name,minsal,maxsal,avgsal)
 as select d.dname,min(e.sal),max(e.sal),avg(e.sal)
 from emp e,dept d 
 where e.deptno=d.deptno group by d.dname;

1.4.2. 视图操作

1.4.2.1. 查看视图

数据字典 USER_VIEWS

 select view_name,text from user_views;

从视图检索数据

 select * from salv;

1.4.2.2. 视图上执行 DML 操作

a) 如果视图包含以下内容，则不能实现对数据的修改:
-GROUP 函数、GROUP BY 子句、DISTINCT 关键字
-使用表达式定义的列
-ROWNUM 伪列
b) 如果视图中包含以下内容则不能删除数据行
-GROUP 函数
-GROUP BY 子句
-DISTINCT 关键字
-ROWNUM 伪列

WITH CHECK OPTION 子句
-如果要确保在视图上执行的插入、更新操作仅限于一定的范围，便可使用 WITH CHECK OPTION 子句

例：

 create or replace view empv
 as select * from emp where deptno=20
 with check option constraint empv_ck;

测试一：

 update empv set deptno=10 where empno=7369

结果：
ERROR 位于第 1 行:
ORA-01402: 视图 WITH CHECK OPTIDN 违反 where 子句

测试二：

 update empv set sal=2000 where empno=7369

结果：已更新 1 行。

1.4.2.3. 配置拒绝 DML 操作

-在视图定义时使用 WITH READ ONLY 选项可以确保不能对视图执行 DML 操作
例：

 create or replace view empv(employee_id,employ_name,job_title)
 as select empno,ename,job from emp where deptno=10
 with read only;

1.4.2.4. 视图删除

删除视图并不会删除数据，因为视图是基于数据库中的基表

DROP VIEW view_name;
例：DROP VIEW empv;

1.5. 索引、约束操作

类似书的目录结构;

• Oracle 的“索引”对象，与表关联的可选对象，提高 SQL 查询语句的速度;
• 索引直接指向包含所查询值的行的位置，减少磁盘 I/O;
• 与所索引的表是相互独立的物理结构;
• Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引;
  语法：

CREATE INDEX index ON table (column[, column]...); B-tree 结构(非 bitmap)

1.5.1. 单表索引

1.5.1.1. 索引创建：

create [unique | bitmap] index index_name on table_name(col_name) pctfree 20 
storage(initial 100k next 100k pctincrease 0 maxextents 100) tablespace 
tablespace_name;

1.5.1.2. 索引重建：

重建索引(可以同时使用存储子句和参数,不重建时也可直接使用)

alter index index_name rebuild tablespace tablespace_name nologging parallel 4; 
alter index index_name noparallel;

1.5.1.2.1. 在线重建索引.

可以减少加锁时间,从而开放使用 DML 类型操作

alter index index_name rebuild tablespace tablespace_name nologging online; 

1.5.1.3. 手动拓展索引的空间

alter index index_name allocate extent; 

1.5.1.4. 收回未用到的空间

alter index index_name deallocate unused; 

1.5.1.5. 索引碎片整理

alter index index_name coalesce; 

1.5.1.6. 标识索引是否使用过

alter index index_name monitoring usage; 

查询标识过的索引:
select * from v$object_usage;

1.5.1.7. 取消监控

alter index index_name nomonitoring usage

1.5.1.8. 索引压缩：

alter index index_name rebuild nologging online tablespace tablespace_name compress;

1.5.1.9. 索引删除：

drop index index_name;

1.5.1.10. 索引查看：

1.5.1.10.1. 索引相关信息

select owner,index_name,table_name,tablespace_name,index_type,degree,status 
from dba_indexes; 

1.5.1.10.2. 索引列对照信息

select index_name,table_name,column_name,index_owner,table_owner 
from dba_ind_columns where index_owner='SCOTT';

1.5.1.10.3. 索引存储信息

select index_name,pct_free,pct_increase,initial_extent,next_extent,min_extents,
max_extents from dba_indexes;

1.5.2. 分区表索引

1.5.2.1. 索引创建：

1.5.2.1.1. 局部索引

create index index_name on table_name (column)
local
(
partition partition_name1 tablespace index_tablespace_name1,
partition partition_name2 tablespace index_tablespace_name2,
partition partition_name3 tablespace index_tablespace_name3 );

1.5.2.1.2. 全局索引

create [unique] index index_name on table_name(column)
global partition by range(column)
(
partition partition_name1 value less than(first range value) tablespace
index_tablespace_name1,
partition partition_name2 value less than(second range value) tablespace
index_tablespace_name 2,
、、、、、、、
partition partition_nameN value less than(maxvalue) tablespace index_tablespace_nameN
)
create [unique] index index_name on table_name(column,[column2])
global partition by hash(column,[column2])
(
partition partition_name1 tablespace index_tablespace_name1,
partition partition_name2 tablespace index_tablespace_name 2,
、、、、、、、、
partition partition_nameN tablespace index_tablespace_nameN
);

1.5.2.2. 索引重建：

alter index index_name rebuild tablespace tablespace_name nologging online parallel 4;
alter index index_name noparallel;

1.5.2.3. 索引删除：

drop index index_name;

1.5.2.4. 索引查看：

1.5.2.4.1. 索引相关信息

select owner,index_name,table_name,tablespace_name,index_type,degree,status from 
dba_indexes; 

1.5.2.4.2. 索引列对照信息

select index_name,table_name,column_name,index_owner,table_owner from 
dba_ind_columns; 

1.5.2.4.3. 索引存储信息

select index_name,pct_free,pct_increase,initial_extent,next_extent,min_extents,max_extents 
from dba_indexes;

1.5.3. 主键、约束和唯一索引

1.5.3.1. 基本概念

1.5.3.1.1. 唯一索引

唯一索引不允许两行具有相同的索引值。如果现有数据中存在重复的键值，则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时，数据库也拒绝接受此数据。
例如，如果在 books_table 表中的书名 (book_name) 列上,创建了唯一索引，则所有书不能同名。

1.5.3.1.2. 主键索引

主键索引是唯一索引的特殊类型,数据库表通常有一列或列组合，其值用来唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。

1.5.3.1.3. 主键和唯一索引的一些比较：

(1)对于主健 unique/constraint oracle 自动建立唯一索引
(2)主键不一定只包含一个字段，所以如果你在主键的其中一个字段建唯一索引还是必要的
(3)主健可作外健，唯一索引不可，
(4)主健不可为空，唯一索引可，
(5)主健也可是多个字段的组合.
(6)主键不同的是
    a.有 not null 属性 
    b.每个表只能有一个

1.5.3.1.4. 约束和唯一索引比较：

主键索引和主键约束的创建与删除顺序
创建主键索引 --> 创建主键约束 删除主键约束 --> 删除主键索引

A 分区字段不是主键的情况下,只可以创建全局分区索引,不可以创建本地主键分区索引.只有分区字段为主键时才可以创建本地主键分区索引.
B 如果创建本地唯一分区索引,除指定索引字段外还要加上表分区字段.这种索引意义不大:因为这样成复合索引,索引改变,约束也改变了.
C 如果创建非唯一索引则不需要表分区字段.
D 创建全局分区索引后可以创建约束.

1.5.3.2. 唯一索引创建

1.5.3.2.1. 分区表创建唯一索引

1.5.3.2.2.

create unique index index_name on table_name (column)
local
(
partition partition_name1 tablespace index_tablespace1,
partition partition_name2 tablespace index_tablespace2,
、、、、、、
partition partition_nameN tablespace index_tablespaceN )

1.5.3.2.3. 单表创建唯一索引：

create unique index index_name on table_name (column);

1.5.3.2.4. 唯一索引重建：

alter index index_name rebuild tablespace tablespace_name nologging parallel 8 online
alter index index_name noparallel;

1.5.3.2.5. 唯一索引删除：

如果有约束：先关闭约束 alter table table_name1 disable constraint constraint_name cascade;
再 drop 索引：drop index index_name

1.5.3.2.6. 索引查看：

1.5.3.2.6.1. DBA 查看索引分区

select * from dba_ind_partitions;

1.5.3.2.6.2. USER 查看索引分区

select * from user_ind_partitions;

1.5.3.2.6.3. DBA 查看索引分区类型

select * from dba_part_indexes

1.5.3.2.6.4. USER 查看索引分区类型

select * from user_part_indexes;

1.5.3.3. 约束创建：

在数据库中使用约束(constraints)是为了在该数据库中实施所谓的"业务规则"其实就是防止非法信息进入数据库,满足管理员和应用开发人员所定义的规则集.ORACLE 使用完整性约束(integrity constraints)防止不合法的数据写入数据库,管理员和开发人员可以定义完整性规则,增强商业规则,限制数据表中的数据.如果一个 DML 语句执行的任何结果破坏了完整性约束,ORACLE 就会回滚语句,返回错误信息.

约束是通过使用 create table 或 alter table语句生成的.(建立表时或者表建立后修改都可)如果相关的约束定义在单列上,可以在列这一级指定约束的定义;多列约束必须定义在数据表级,相关的列要在括号中指定,用逗号分隔.如果没有为约束提供一个名字,那么 ORACLE 会分配一个系统生成的唯一名字,以 SYS_开头,你可以使用关键字 CONSTRAINTS 后面跟随相关的约束名字来为约束指定名字.

1.5.3.3.1. ORACLE 支持五种类型的完整性约束

• NOT NULL (非空)--防止 NULL 值进入指定的列,在单列基础上定义,默认情况下,ORACLE 允许在任何列中有 NULL值.
• CHECK (检查)--检查在约束中指定的条件是否得到了满足.
• UNIQUE (唯一)--保证在指定的列中没有重复值.在该表中每一个值或者每一组值都将是唯一的.
• PRIMARY KEY (主键)--用来唯一的标识出表的每一行,并且防止出现 NULL 值,一个表只能有一个主键约束.
• POREIGN KEY (外部键)--通过使用公共列在表之间建立一种父子(parent-child)关系,在表上定义的外部键可以指向主键或者其他表的唯一键.

约束定义存储在数据字典中,查询 user_constraints 可以获得相关信息.

1.5.3.3.2. 定义约束

create table [schema.]table
(column datatype [default expr]
[column_constraint],
...
[table_constraint][,...]);

1.5.3.3.3. 创建约束：

create table employees
(employee_id number(6),
first_name varchar2(20),
...
job_id varchar2(10) not null,
constraints emp_emp_id_pk primary key (employee_id));

1.5.3.3.3.1. 列级的约束定义

column [CONSTRAINT constraint_name] constraint_type,

1.5.3.3.3.2. 表级约束的定义

column,..
[constraint constraint_name] constraint_type (column,...)

1.5.3.3.3.3. NOT NULL 约束

只能定义在列级,不能定义在表级：

create table table_name1
(row_id number(6),
row_time varchar2(25) not null,
row_salary number(8,2),
row_pct number(2,2),
row_date date constraint constraint_name1 not null); 

1.5.3.3.3.4. UNIQUE 约束

用来保护一个表中的一个或者多个列没有任何两行在收到保护的列中具有重复的数据.ORACLE
在唯一键列上自动生成一个唯一索引以实现唯一性：

create table table_name1
(row_id number(6),
row_time varchar2(25) not null,
row_salary number(8,2),
row_pct number(2,2),
row_date date constraint constraint_name1 UNIQUE(row_id)); 

1.5.3.3.3.5. PRIMARY KEY 约束

唯一键的所有特征都适用于主键约束,只是在主键列中不允许有 NULL 值.一个表只能有一个主键：

create table table_name1
(row_id number(6),
row_time varchar2(25) not null,
row_salary number(8,2),
row_pct number(2,2),
row_date date constraint constraint_name1 PRIMARY KEY(row_id));

1.5.3.3.3.6. foreign key 约束

用来保护一个表中的一个或者多个列,它会通过一个主键或者唯一键保证对于每个非NULL值在数据库的其他地方都有一个数据可用.这个外部键就是在生成此约束的表(子表)中的一个或多个列,在父级表和子表中,相关列的数据类型必须匹配.外部键列和引用键(reference key)列可以位于相同的表中(自引用完整性约束)

create table table_name1
(row_id number(6),
row_time varchar2(25) not null, 
row_salary number(8,2),
row_pct number(2,2),
row_date date not null,
dep_id number(6),
constraint constraint_name1 foreign key(row_id) references table_name2(dep_id),
constraint constraint_name2 unique(dep_id));

上例中是在表级定义外部键约束,如果在列级定义,不同的是:

create table table_name1
(...,
dep_id number(4) constraint constraint_name1 references table_name2(dep_id),
...);

//没有关键字 FOREIGN KEY
FOREIGN KEY 约束还有两个关键字是
ON DELETE CASCADE --当删除所引用的父表记录时,删除子表中相关的记录
ON DELETE SET NULL--与上面不同的是删除时,转换子表中相关记录为 NULL 值默认情况下,如果没有指定以上两个中任一,则父表中被引用的记录将不能被删除

1.5.3.3.3.7. CHECK 约束

[CONSTRAINT <constraint name>] CHECK (<condition> )

这里 CHECK 子句中的 CONDITION 应该求值为一个布尔值结果,并且可以引用相同行中其他列的值;不能包含子查询,序列,环境函数(SYSDATE,UID,USER,USERENV)和伪列(ROWNUM,LEVEL,CURRVAL,NEXTVAL),一个列上可以定义多个 CHECK 约束,如果所定义的条件为FALSE,则语句将回滚.

CREATE TABLE table_name1
(...,
row_sal NUMBER(8,2) CONSTRAINT constraint_name1 CHECK (row_sal>0),
...);

1.5.3.3.4. 添加约束

1.5.3.3.4.1. 添加外键约束

alter table table_name1
ADD CONSTRAINT constraint_name FOREIGN KEY(rowmagr_id) REFERENCES 
table_name2(name_id)

1.5.3.3.5. 删除约束

alter table table_name1 drop constraint constraint_name;

alter table table_name1 drop primary key cascade;

对于 NOT NULL 约束,用 ALTER TABLE MODIFY 子句来删除

alter table table_name1 modify row_name null;

1.5.3.3.6. 关闭约束

alter table table_name1 disable constraint constraint_name cascade; 
//如果没有被引用则不需 CASCADE 关键字

当你生成一个约束时,约束自动打开(除非你指定了 DISABLE 子句,当用 DISABLE 关闭 UNIQUE或者 PRIMARY KEY 约束时,ORACLE 会自动删除相关的唯一索引,再次打开时,ORACLE 又会自动建立.

1.5.3.3.7. 打开约束

alter table table_name enable constraint constraint_name;
//注意,打开一个先前关闭的被引用的主键约束,并不能自动打开相关的外部键约束

1.5.3.3.8. 约束信息查看：

可以从 USER_CONSTRAINTS 表和 USER_CONS_COLUMNS 视图中查询约束的信息

查看约束类型：
select constraint_name,constraint_type,search_condition
from user_constraints
where table_name='table_name1';

查看约束对应的列：
select constraint_name,column_name
from user_cons_columns
where table_name='table_name1';

注意：
约束类型
C--CHECK 和 NOT NULL 都算为 C TYPE
P--PRIMARY KEY
R--REFERENTIAL INTEGRITY 就是外部键约束
U--UNIQUE

1.6. 存储过程/job/函数/触发器操作

1.6.1. 存储过程和函数

存储过程和函数也是一种 pl/sql 块，是存入数据库的 pl/sql 块。但存储过程和函数不同于已经介绍过的 pl/sql 程序，我们通常把 pl/sql 程序称为无名块，而存储过程和函数是以命名的方式存储于数据库中的。和 pl/sql 程序相比，

1.6.1.1. 存储过程有很多优点

具体归纳如下：

• a）存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的，因为代码不保存在本地，用户可以在任何客户机上登录到数据库，并调用或修改代码。

• b）存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储过程和函数的所有者的授权，只有被授权的用户或创建者本身才能执行存储过程或调用函数。

• c）存储过程和函数的信息是写入数据字典的，所以存储过程可以看作是一个公用模块，用户编写的 pl/sql 程序或其他存储过程都可以调用它(但存储过程和函数不能调用pl/sql 程序)。一个重复使用的功能，可以设计成为存储过程，比如：显示一张工资统计表，可以设计成为存储过程；一个经常调用的计算，可以设计成为存储函数；根据雇员编号返回雇员的姓名，可以设计成存储函数。

• d）像其他高级语言的过程和函数一样，可以传递参数给存储过程或函数，参数的传递也有多种方式。存储过程可以有返回值，也可以没有返回值，存储过程的返回值必须通过参数带回；函数有一定的数据类型，像其他的标准函数一样，我们可以通过对函数名的调用返回函数值。

存储过程和函数需要进行编译，以排除语法错误，只有编译通过才能调用。

1.6.1.2. 存储过程创建

1.6.1.2.1. 创建权限

创建存储过程，需要有 create procedure 或 create any procedure 的系统权限。该权限可由系统管理员授予。

1.6.1.2.2. 创建语法

创建一个存储过程的基本语句如下：

create [or replace] procedure 存储过程名[(参数[in|out|in out] 数据类型...)] 
{as|is} 
[声明部分] 
begin 
可执行部分
[exception 
错误处理部分] 
end [过程名];

其中：
1.可选关键字 or replace 表示如果存储过程已经存在，则用新的存储过程覆盖，通常用于存储过程的重建。
2.参数部分用于定义多个参数(如果没有参数，就可以省略)。参数有三种形式：in、out 和 in out。如果没有指明参数的形式，则默认为 in。
3.关键字 as 也可以写成 is，后跟过程的说明部分，可以在此定义过程的局部变量。
4.编写存储过程可以使用任何文本编辑器或直接在 sqlplus 环境下进行，编写好的存储过程必须要在 sqlplus 环境下进行编译，生成编译代码，原代码和编译代码在编译过程中都会被存入数据库。
5。编译成功的存储过程就可以在 oracle 环境下进行调用了。 一个存储过程在不需要时可以删除。

1.6.1.3. 删除存储过程

1.6.1.3.1. 删除权限

删除存储过程的创建者或者拥有 drop any procedure系统权限的人。

1.6.1.3.2. 删除语法

删除存储过程的语法如下：

语法格式：drop procedure 存储过程名；

1.6.1.4. 重新编译存储过程

1.6.1.4.1. 编译权限

如果要重新编译一个存储过程，则只能是过程的创建者或者拥有 alter any procedure 系统权限的人。

1.6.1.4.2. 编译语法

语法如下：

alter procedure 存储过程名 compile；

1.6.1.5. 执行存储过程

1.6.1.5.1. 执行权限

执行(或调用)存储过程的人是过程的创建者或是拥有 execute any procedure 系统权限的人或是被拥有者授予 execute 权限的人。

1.6.1.5.2. 执行语法

执行的方法如下：
方法 1：、

execute 模式名.存储过程名[(参数...)]; 

方法 2：

begin 
模式名.存储过程名[(参数...)]; 
end;
/

1.6.1.6. 案例：

1.6.1.6.1. 【训练 1】创建一个显示雇员总人数的存储过程。

步骤 1：登录 scott 账户(或学生个人账户)。
步骤 2：在 sql*plus 输入区中，输入以下存储过程：

create or replace procedure dyk_count 
as 
 v_total1 number(10);
 v_total2 number(10);
 v_total3 number(10); 
begin 
 select count(*) into v_total1 from dyk_table1;
 dbms_output.put_line('雇员总人数为：'||v_total1);
 delete dyk_table1 where owner In ('XDB','CTSSYS','OUTLN','WMSYS');
 select count(*) into v_total2 from dyk_table1;
 dbms_output.put_line('剩余总人数为：'||v_total2);
 insert into dyk_table1 nologging select * from dba_objects;
 select count(*) into v_total3 from dyk_table1; 
 dbms_output.put_line('增加后的总人数为：'||v_total3); 
end;
/


查看编译错误：
show errors

步骤 3：按“执行”按钮进行编译。
如果存在错误，就会显示: 警告: 创建的过程带有编译错误。
如果存在错误，对脚本进行修改，直到没有错误产生。
如果编译结果正确，将显示：（sql 代码）
过程已创建。

步骤 4：调用存储过程，在输入区中输入以下语句并执行：

set serverout on /从 sql 提示符下输出存储过程变量值
execute dyk_count; 

显示结果为：
雇员总人数为：337386
剩余总人数为：336456
增加后的总人数为：386254

说明：在该训练中，v_total 变量是存储过程定义的局部变量，用于接收查询到的雇员总人数。注意：在 sql*plus 中输入存储过程，按“执行”按钮是进行编译，不是执行存储过程。 如果在存储过程中引用了其他用户的对象，比如表，则必须有其他用户授予的对象访问权限。一个存储过程一旦编译成功，就可以由其他用户或程序来引用。但存储过程或函数的所有者必须授予其他用户执行该过程的权限。

存储过程没有参数，在调用时，直接写过程名即可。

1.6.1.6.2. 【训练 2】在 pl/sql 程序中调用存储过程。

步骤 1：登录 scott 账户。
步骤 2：授权 student 账户使用该存储过程，即在 sql*plus 输入区中，输入以下的命令：

grant execute on emp_count to student 

授权成功。
步骤 3：登录 student 账户，在 sql*plus 输入区中输入以下程序：

set serveroutput on 
 begin 
 scott.emp_count; 
 end; 
 /

步骤 4：执行以上程序，结果为：
雇员总人数为：14
pl/sql 过程已成功完成。

说明：在本例中，存储过程是由 scott 账户创建的，studen 账户获得 scott 账户的授权后，才能调用该存储过程。
注意：在程序中调用存储过程，使用了第二种语法。

1.6.1.6.3. 【训练 3】 编写显示雇员信息的存储过程 emp_list，并引用 emp_count 存储过程。

步骤 1：在 sql*plus 输入区中输入并编译以下存储过程：

create or replace procedure emp_list 
 as 
 cursor emp_cursor is 
 select empno,ename from emp; 
 begin 
for emp_record in emp_cursor loop 
 dbms_output.put_line(emp_record.empno||emp_record.ename); 
 end loop; 
 emp_count; 
 end;
 /

过程已创建。
步骤 2：调用存储过程，在输入区中输入以下语句并执行：

execute emp_list 
execute emp_list

显示结果为：
执行结果： 雇员总人数为：14
pl/sql 过程已成功完成。

说明：以上的 emp_list 存储过程中定义并使用了游标，用来循环显示所有雇员的信息。然后调用已经成功编译的存储过程 emp_count，用来附加显示雇员总人数。通过 execute 命令来执行emp_list 存储过程。

1.6.1.6.4. 【练习 1】编写显示部门信息的存储过程 dept_list，要求统计出部门个数。

参数传递：参数的作用是向存储过程传递数据，或从存储过程获得返回结果。正确的使用参数可以大大增加存储过程的灵活性和通用性，

1.6.1.6.4.1. 参数类型

参数的类型有三种，如下所示。

in 定义一个输入参数变量，用于传递参数给存储过程 
out 定义一个输出参数变量，用于从存储过程获取数据 
in out 定义一个输入、输出参数变量，兼有以上两者的功能 

1.6.1.6.4.2. 参数定义

参数的定义形式和作用如下：
参数名 in 数据类型 default 值
定义一个输入参数变量，用于传递参数给存储过程。在调用存储过程时，主程序的实际参数可以是常量、有值变量或表达式等。default 关键字为可选项，用来设定参数的默认值。如果在调用存储过程时不指明参数，则参数变量取默认值。在存储过程中，输入变量接收主程序传递的值，但不能对其进行赋值。
参数名 out 数据类型；
定义一个输出参数变量，用于从存储过程获取数据，即变量从存储过程中返回值给主程序。在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。在存储过程中，参数变量只能被赋值而不能将其用于赋值，在存储过程中必须给输出变量至少赋值一次。
参数名 in out 数据类型 default 值；
定义一个输入、输出参数变量，兼有以上两者的功能。在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。default 关键字为可选项，用来设定参数的默认值。在存储过程中，变量接收主程序传递的值，同时可以参加赋值运算，也可以对其进行赋值。
在存储过程中必须给变量至少赋值一次。如果省略 in、out 或 in out，则默认模式是 in

1.6.1.6.5. 【训练 1】编写给雇员增加工资的存储过程 change_salary，通过 in 类型的参数传递要增加工资的雇员编号和增加的工资额。

步骤 1：登录 scott 账户。
步骤 2：在 sql*plus 输入区中输入以下存储过程并执行：

create or replace procedure change_salary(p_empno in number default 7788,p_raise number 
default 10) 
 as 
 v_ename varchar2(10); 
v_sal number(5); 
 begin 
 select ename,sal into v_ename,v_sal from emp where empno=p_empno; 
 update emp set sal=sal+p_raise where empno=p_empno; 
 dbms_output.put_line('雇员'||v_ename||'的工资被改为'||to_char(v_sal+p_raise)); 
commit; 
 exception 
 when others then 
 dbms_output.put_line('发生错误，修改失败！'); 
 rollback; 
 end; 
 /

过程已创建。
步骤 3：调用存储过程，在输入区中输入以下语句并执行：
sql 代码

execute change_salary(7788,80) 

显示结果为：
雇员 scott 的工资被改为 3080

说明：从执行结果可以看到，雇员 scott 的工资已由原来的 3000 改为 3080。
参数的值由调用者传递，传递的参数的个数、类型和顺序应该和定义的一致。如果顺序不一致，可以采用以下调用方法。如上例，执行语句可以改为：

execute change_salary(p_raise=>80,p_empno=>7788); 

可以看出传递参数的顺序发生了变化，并且明确指出了参数名和要传递的值，=>运算符左侧是
参数名，右侧是参数表达式，这种赋值方法的意义较清楚。

1.6.1.6.6. 【练习 2】创建插入雇员的存储过程 insert_emp，并将雇员编号等作为参数。

在设计存储过程的时候，也可以为参数设定默认值，这样调用者就可以不传递或少传递参数了。

1.6.1.6.7. 【训练 2】 调用存储过程 change_salary，不传递参数，使用默认参数值。

在 sql*plus 输入区中输入以下命令并执行：

execute change_salary 

显示结果为：
雇员 scott 的工资被改为 3090
说明：在存储过程的调用中没有传递参数，而是采用了默认值 7788 和 10，即默认雇员号为 7788，增加的工资为 10。

1.6.1.6.8. 【训练 3】 使用 out 类型的参数返回存储过程的结果。

步骤 1：登录 scott 账户。
步骤 2：在 sql*plus 输入区中输入并编译以下存储过程：

create or replace procedure emp_count(p_total out number) 
 as 
 begin 
 select count(*) into p_total from emp; 
 end;
 /

执行结果为：
sql 代码
过程已创建。
步骤 3：输入以下程序并执行：
sql 代码

declare 
 v_empcount number; 
 begin 
 emp_count(v_empcount); 
 dbms_output.put_line('雇员总人数为：'||v_empcount); 
 end; 
 /

显示结果为：
sql 代码
雇员总人数为：14
pl/sql 过程已成功完成。

说明：在存储过程中定义了 out 类型的参数 p_total，在主程序调用该存储过程时，传递了参数v_empcount。在存储过程中的 select...into...语句中对 p_total 进行赋值，赋值结果由 v_empcount变量带回给主程序并显示。
以上程序要覆盖同名的 emp_count 存储过程，如果不使用 or replace 选项，
就会出现以下错误：sql 代码error 位于第 1 行: ora-00955: 名称已由现有对象使用。

【练习 2】创建存储过程，使用 out 类型参数获得雇员经理名。

1.6.1.6.9. 【训练 4】 使用 in out 类型的参数，给电话号码增加区码。

步骤 1：登录 scott 账户。
步骤 2：在 sql*plus 输入区中输入并编译以下存储过程：

create or replace procedure add_region(p_hpone_num in out varchar2) 
 as 
 begin 
 p_hpone_num:='0755-'||p_hpone_num; 
 end;
 /

执行结果为：
过程已创建。
步骤 3：输入以下程序并执行：

set serveroutput on 
declare 
 v_phone_num varchar2(15); 
begin 
 v_phone_num:='26731092'; 
 add_region(v_phone_num); 
 dbms_output.put_line('新的电话号码：'||v_phone_num); 
end;
/

显示结果为：
sql 代码
新的电话号码：0755-26731092
pl/sql 过程已成功完成。

说明：变量 v_hpone_num 既用来向存储过程传递旧电话号码，也用来向主程序返回新号码。新的号码在原来基础上增加了区号 0755 和-。

1.6.1.7. 创建和删除存储函数

1.6.1.7.1. 创建权限

创建函数，需要有 create procedure 或 create any procedure 的系统权限。该权限可由系统管理员
授予。创建存储函数的语法和创建存储过程的类似，即

1.6.1.7.2. 创建语法

create [or replace] function 函数名[(参数[in] 数据类型...)] 
return 数据类型
{as|is} 
[说明部分] 
begin 
可执行部分
return (表达式) 
[exception 
 错误处理部分] 
end [函数名];

其中，参数是可选的，但只能是 in 类型(in 关键字可以省略)。
在定义部分的 return 数据类型，用来表示函数的数据类型，也就是返回值的类型，此部分不可省略。
在可执行部分的 return(表达式)，用来生成函数的返回值，其表达式的类型应该和定义部分说明的函数返回值的数据类型一致。
在函数的执行部分可以有多个 return 语句，但只有一个return 语句会被执行，一旦执行了 return 语句，则函数结束并返回调用环境。

1.6.1.7.3. 删除权限

一个存储函数在不需要时可以删除，但删除的人应是函数的创建者或者是拥有 drop any procedure 系统权限的人。

1.6.1.7.4. 删除语法

其语法如下：

drop function 函数名；

1.6.1.7.5. 重新编译权限

重新编译一个存储函数时，编译的人应是函数的创建者或者拥有 alter any procedure 系统权限的人。

1.6.1.7.6. 重新编译语法

重新编译一个存储函数的语法如下：

alter procedure 函数名 compile；

1.6.1.8. 函数

1.6.1.8.1. 调用函数存储过程权限

函数的调用者应是函数的创建者或拥有 execute any procedure 系统权限的人，或是被函数的拥有者授予了函数执行权限的账户。

函数的引用和存储过程不同，函数要出现在程序体中，可以参加表达式的运算或单独出现在表达式中，其形式如下：

变量名:=函数名(...) 

1.6.1.8.2. 【训练 1】 创建一个通过雇员编号返回雇员名称的函数 get_emp_name。

步骤 1：登录 scott 账户。
步骤 2：在 sql*plus 输入区中输入以下存储函数并编译：

create or replace function get_emp_name(p_empno number default 7788) 
 return varchar2 
 as 
 v_ename varchar2(10); 
 begin 
 elect ename into v_ename from emp where empno=p_empno; 
return(v_ename); 
exception 
when no_data_found then 
 dbms_output.put_line('没有该编号雇员'); 
 return (null); 
when too_many_rows then 
 dbms_output.put_line('有重复雇员编号！'); 
 return (null); 
when others then 
 dbms_output.put_line('发生其他错误！'); 
 return (null); 
end;
/

步骤 3：调用该存储函数，输入并执行以下程序：

begin 
 dbms_output.put_line('雇员 7369 的名称是：'|| get_emp_name(7369)); 
 dbms_output.put_line('雇员 7839 的名称是：'|| get_emp_name(7839)); 
end; 

显示结果为：
sql 代码
雇员 7369 的名称是：smith
雇员 7839 的名称是：king
pl/sql 过程已成功完成。

说明：函数的调用直接出现在程序的 dbms_output.put_line 语句中，作为字符串表达式的一部分。如果输入了错误的雇员编号，就会在函数的错误处理部分输出错误信息。试修改雇员编号，重新运行调用部分。

【练习 1】创建一个通过部门编号返回部门名称的存储函数 get_dept_name。
【练习 2】将函数的执行权限授予 student 账户，然后登录 student 账户调用。
存储过程和函数的查看可以通过对数据字典的访问来查询存储过程或函数的有关信息，如果要查
询当前用户的存储过程或函数的源代码，可以通过对
user_source 数据字典视图的查询得到。user_source 的结构如下：

describe user_source 

结果为：

名称 是否为空? 类型 
------------------------------------------------------------- ------------- ----------------------- 
name varchar2(30) 
type varchar2(12) 
line number 
text varchar2(4000) 

说明：里面按行存放着过程或函数的脚本，name 是过程或函数名，type 代表类型(procedure或 function)，line 是行号，text 为脚本。

1.6.1.8.3. 【训练 1】 查询过程 emp_count 的脚本。

在 sql*plus 中输入并执行如下查询：

select text from user_source where name='emp_count'; 

结果为：

text 
-------------------------------------------------------------------------------- 
procedure emp_count(p_total out number) 
as 
begin 
select count(*) into p_total from emp; 
end;
/

1.6.1.8.4. 【训练 2】 查询过程 get_emp_name 的参数。

在 sql*plus 中输入并执行如下查询：

describe get_emp_name 

结果为：

function get_emp_name returns varchar2 
 参数名称 类型 输入/输出默认值？ 
 -------- ------- --------------------
 p_empno number(4) in default

1.6.1.8.5. 【训练 3】 在发生编译错误时，显示错误。

show errors 

以下是一段编译错误显示：

line/col error 
---------- ------------------- -------------- 
 4/2 pl/sql: sql statement ignored 
 4/36 pls-00201: 必须说明标识符 'empp' 

说明：查询一个存储过程或函数是否是有效状态(即编译成功)，可以使用数据字典 user_objects的 status 列。

1.6.1.8.6. 【训练 4】查询 emp_list 存储过程是否可用：

select status from user_objects where object_name='emp_list'; 
结果为：
sql 代码
 status 
------------ 
 valid

说明：valid 表示该存储过程有效(即通过编译)，invalid 表示存储过程无效或需要重新编译。当oracle 调用一个无效的存储过程或函数时，首先试图对其进行编译，如果编译成功则将状态置成valid 并执行，否则给出错误信息。 当一个存储过程编译成功，状态变为 valid，会不会在某些情况下变成 invalid。结论是完全可能的。比如一个存储过程中包含对表的查询，如果表被修改或删除，存储过程就会变成无效 invalid。所以要注意存储过程和函数对其他对象的依赖关系。

如果要检查存储过程或函数的依赖性，可以通过查询数据字典 user_denpendencies 来确定，
该表结构如下：

describe user_dependencies;

结果：

名称 是否为空 类型 
----- ---------- ------------
name not null varchar2(30) 
type varchar2(12) 
referenced_owner varchar2(30) 
referenced_name varchar2(64) 
referenced_type varchar2(12) 
referenced_link_name varchar2(128) 
schemaid number 
dependency_type varchar2(4) 

说明：name 为实体名，type 为实体类型，referenced_owner 为涉及到的实体拥有者账户，referenced_name 为涉及到的实体名，referenced_type 为涉及到的实体类型。

1.6.1.8.7. 【训练 5】 查询 emp_list 存储过程的依赖性。

select referenced_name,referenced_type from user_dependencies where name='emp_list'; 

执行结果：

referenced_name referenced_type 
------------------------------ --------------------------- 
standard package 
 sys_stub_for_purity_analysis package 
 dbms_output package 
 dbms_output synonym 
dbms_output non-existent 
 emp table 
 emp_count procedure

说明：可以看出存储过程 emp_list 依赖一些系统包、emp 表和 emp_count 存储过程。如果删除了 emp 表或 emp_count 存储过程，emp_list 将变成无效。

还有一种情况需要我们注意：如果一个用户 a 被授予执行属于用户 b 的一个存储过程的权限，在用户 b 的存储过程中，访问到用户
c 的表，用户 b 被授予访问用户 c 的表的权限，但用户 a 没有被授予访问用户 c 表的权限，那么用户 a 调用用户 b 的存储过程是失败的还是成功的呢？答案是成功的。如果读者有兴趣，不妨进行一下实际测试。

1.6.2. 程序包

1.6.2.1. 包的概念和组成

包是用来存储相关程序结构的对象，它存储于数据字典中。包由两个分离的部分组成：包头(package)和包体(package body)。

• 包头是包的说明部分，是对外的操作接口，对应用是可见的；
• 包体是包的代码和实现部分，对应用来说是不可见的黑盒。

包中可以包含的程序结构如下所示。

过程(procudure) 带参数的命名的程序模块 
函数(function) 带参数、具有返回值的命名的程序模块 
变量(variable) 存储变化的量的存储单元 
常量(constant) 存储不变的量的存储单元 
游标(cursor) 用户定义的数据操作缓存区，在可执行部分使用 
类型(type) 用户定义的新的结构类型 
异常(exception) 在标准包中定义或由用户自定义，用于处理程序错误

说明部分可以出现在包的三个不同的部分：出现在包头中的称为公有元素，出现在包体中的称为私有元素，出现在包体的过程(或函数)中的称为局部变量。它们的性质有所不同，如下所示。

• 公有元素(public) 在包头中说明，在包体中具体定义 在包外可见并可以访问，对整个应用的全过程有效
• 私有元素(private) 在包体的说明部分说明 只能被包内部的其他部分访问
• 局部变量(local) 在过程或函数的说明部分说明 只能在定义变量的过程或函数中使用

在包体中出现的过程或函数，如果需要对外公用，就必须在包头中说明，包头中的说明应该和包体中的说明一致。

1.6.2.1.1. 包有以下优点：

* 包可以方便地将存储过程和函数组织到一起，每个包又是相互独立的。在不同的包中，过程、函数都可以重名，这解决了在同一个用户环境中命名的冲突问题。
* 包增强了对存储过程和函数的安全管理，对整个包的访问权只需一次授予。
* 在同一个会话中，公用变量的值将被保留，直到会话结束。
* 区分了公有过程和私有过程，包体的私有过程增加了过程和函数的保密性。
* 包在被首次调用时，就作为一个整体被全部调入内存，减少了多次访问过程或函数的 i/o 次数。

1.6.2.1.2. 创建包和包体

包由包头和包体两部分组成，包的创建应该先创建包头部分，然后创建包体部分。创建、删除和编译包的权限同创建、删除和编译存储过程的权限相同。

创建包头的简要语句如下：

create [or replace] package 包名
{is|as} 
公有变量定义
公有类型定义
公有游标定义
公有异常定义
函数说明
过程说明
end;

创建包体的简要语法如下：

create [or replace] package body 包名
{is|as} 
私有变量定义
私有类型定义
私有游标定义
私有异常定义
函数定义
过程定义
end;

1.6.2.1.3. 包的其他操作命令包括：

1.6.2.1.4. 删除包头：

drop package 包头名

1.6.2.1.5. 删除包体：

drop package body 包体名

1.6.2.1.6. 重新编译包头：

alter package 包名 compile package 

1.6.2.1.7. 重新编译包体：

alter package 包名 compile package body

在包头中说明的对象可以在包外调用，调用的方法和调用单独的过程或函数的方法基本相同，惟一的区别就是要在调用的过程或函数名前加上包的名字(中间用“.”分隔)。但要注意，不同的会话将单独对包的公用变量进行初始化，所以不同的会话对包的调用属于不同的应用。
系统包 oracle 预定义了很多标准的系统包，这些包可以在应用中直接使用，比如在训练中我们使用的 dbms_output 包，就是系统包。put_line 是该包的一个函数。
常用系统包下所示。

dbms_output 在 sql*plus 环境下输出信息 
dbms_ddl 编译过程函数和包 
dbms_session 改变用户的会话，初始化包等 
dbms_transaction 控制数据库事务 
dbms_mail 连接 oracle*mail 
dbms_lock 进行复杂的锁机制管理 
dbms_alert 识别数据库事件告警 
dbms_pipe 通过管道在会话间传递信息 
dbms_job 管理 oracle 的作业 
dbms_lob 操纵大对象 
dbms_sql 执行动态 sql 语句 
dbms_output 在 sql*plus 环境下输出信息
dbms_ddl 编译过程函数和包
dbms_session 改变用户的会话，初始化包等
dbms_transaction 控制数据库事务
dbms_mail 连接 oracle*mail
dbms_lock 进行复杂的锁机制管理
dbms_alert 识别数据库事件告警
dbms_pipe 通过管道在会话间传递信息
dbms_job 管理 oracle 的作业
dbms_lob 操纵大对象
dbms_sql 执行动态 sql 语句

1.6.2.1.8. 包的应用

在 sql*plus 环境下，包和包体可以分别编译，也可以一起编译。如果分别编译，则要先编译包头，后编译包体。如果在一起编译，则包头写在前，包体在后，中间用“/”分隔。

可以将已经存在的存储过程或函数添加到包中，方法是去掉过程或函数创建语句的 create or replace 部分，将存储过程或函数复制到包体中 ，然后重新编译即可。

如果需要将私有过程或函数变成共有过程或函数的话，将过程或函数说明部分复制到包头说明部分，然后重新编译就可以了。

1.6.2.1.8.1. 【训练 1】 创建管理雇员信息的包 employe，它具有从 emp 表获得雇员信息，修改雇员名称，修改雇员工资和写回 emp 表的功能。

步骤 1：登录 scott 账户，输入以下代码并编译：

create or replace package employe --包头部分 
 is 
procedure show_detail; 
procedure get_employe(p_empno number); 
procedure save_employe; 
procedure change_name(p_newname varchar2); 
procedure change_sal(p_newsal number); 
 end employe; 
 / 

 create or replace package body employe --包体部分 
 is 
employe emp%rowtype; 
 -------------- 显示雇员信息 --------------- 
 procedure show_detail 
 as 
 begin 
dbms_output.put_line(‘----- 雇员信息 -----’); 
 dbms_output.put_line('雇员编号：'||employe.empno); 
 dbms_output.put_line('雇员名称：'||employe.ename); 
 dbms_output.put_line('雇员职务：'||employe.job); 
 dbms_output.put_line('雇员工资：'||employe.sal); 
 dbms_output.put_line('部门编号：'||employe.deptno); 
 end show_detail; 
----------------- 从 emp 表取得一个雇员 -------------------- 
 procedure get_employe(p_empno number) 
 as 
 begin 
 select * into employe from emp where empno=p_empno; 
 dbms_output.put_line('获取雇员'||employe.ename||'信息成功'); 
 exception 
 when others then 
 dbms_output.put_line('获取雇员信息发生错误！'); 
 end get_employe; 
---------------------- 保存雇员到 emp 表 -------------------------- 
 procedure save_employe 
 as 
 begin 
 update emp set ename=employe.ename, sal=employe.sal where empno= 
 employe.empno; 
 dbms_output.put_line('雇员信息保存完成！'); 
 end save_employe; 
---------------------------- 修改雇员名称 ------------------------------ 
 procedure change_name(p_newname varchar2) 
 as 
 begin 
 employe.ename:=p_newname; 
 dbms_output.put_line('修改名称完成！'); 
 end change_name; 
---------------------------- 修改雇员工资 -------------------------- 
 procedure change_sal(p_newsal number) 
 as 
 begin 
 employe.sal:=p_newsal; 
 dbms_output.put_line('修改工资完成！'); 
 end change_sal; 
 end employe; 
create or replace package employe --包头部分
is
procedure show_detail; 
procedure get_employe(p_empno number); 
procedure save_employe; 
procedure change_name(p_newname varchar2); 
procedure change_sal(p_newsal number); 
end employe;
/
create or replace package body employe --包体部分
is
employe emp%rowtype;
-------------- 显示雇员信息 ---------------
procedure show_detail
as
begin
dbms_output.put_line(‘----- 雇员信息 -----’);
 dbms_output.put_line('雇员编号：'||employe.empno);
 dbms_output.put_line('雇员名称：'||employe.ename);
 dbms_output.put_line('雇员职务：'||employe.job);
dbms_output.put_line('雇员工资：'||employe.sal);
dbms_output.put_line('部门编号：'||employe.deptno);
end show_detail;
----------------- 从 emp 表取得一个雇员 --------------------
procedure get_employe(p_empno number)
as
begin
 select * into employe from emp where empno=p_empno;
 dbms_output.put_line('获取雇员'||employe.ename||'信息成功');
exception
when others then
 dbms_output.put_line('获取雇员信息发生错误！');
end get_employe;
---------------------- 保存雇员到 emp 表 --------------------------
procedure save_employe
as
begin
 update emp set ename=employe.ename, sal=employe.sal where empno=
employe.empno;
dbms_output.put_line('雇员信息保存完成！');
end save_employe;
---------------------------- 修改雇员名称 ------------------------------
procedure change_name(p_newname varchar2)
as
begin
employe.ename:=p_newname;
dbms_output.put_line('修改名称完成！');
end change_name;
---------------------------- 修改雇员工资 --------------------------
procedure change_sal(p_newsal number)
as
begin
employe.sal:=p_newsal;
dbms_output.put_line('修改工资完成！');
end change_sal;
end employe;

步骤 2：获取雇员 7788 的信息:

set serveroutput on 
 execute employe.get_employe(7788); 

结果为：
获取雇员 scott 信息成功
pl/sql 过程已成功完成。
步骤 3：显示雇员信息：

execute employe.show_detail; 

结果为：
sql 代码
------------------ 雇员信息 ------------------
雇员编号：7788
雇员名称：scott
雇员职务：analyst
雇员工资：3000
部门编号：20
pl/sql 过程已成功完成。
步骤 4：修改雇员工资：

execute employe.change_sal(3800); 

结果为：
修改工资完成！
pl/sql 过程已成功完成。
步骤 5：将修改的雇员信息存入 emp 表

execute employe.save_employe; 

结果为：
雇员信息保存完成！
pl/sql 过程已成功完成。

说明：该包完成将 emp 表中的某个雇员的信息取入内存记录变量，在记录变量中进行修改编辑，在确认显示信息正确后写回 emp 表的功能。记录变量 employe 用来存储取得的雇员信息，定义为私有变量，只能被包的内部模块访问。

1.6.2.1.8.2. 【练习 1】为包增加修改雇员职务和部门编号的功能。

下面的训练通过定义和创建完整的包 emp_pk 并综合运用本章的知识，完成对雇员表的插入、删
除等功能，包中的主要元素解释如下所示。

程序结构 类型说明
v_emp_count 公有变量 跟踪雇员的总人数变化，插入、删除雇员的同时修改该变量的值 
init 公有过程 对包进行初始化，初始化雇员人数和工资修改的上、下限 
list_emp 公有过程 显示雇员列表 
insert_emp 公有过程 通过编号插入新雇员 
delete_emp 公有过程 通过编号删除雇员 
change_emp_sal 公有过程 通过编号修改雇员工资 
v_message 私有变量 存放准备输出的信息 
c_max_sal 私有变量 对工资修改的上限 
c_min_sal 私有变量 对工资修改的下限 
show_message 私有过程 显示私有变量 v_message 中的信息 
exist_emp 私有函数 判断某个编号的雇员是否存在，该函数被 insert_emp、delete_emp 和
change_emp_sal 等过程调用

1.6.2.1.8.3. 【训练 1】 完整的雇员包 emp_pk 的创建和应用。

步骤 1：在 sql*plus 中登录 scott 账户，输入以下包头和包体部分，按“执行”按钮编译：

create or replace package emp_pk 
 --包头部分 
 is 
 v_emp_count number(5); 
 --雇员人数 
 procedure init(p_max number,p_min number); --初始化 
 procedure list_emp; 
 --显示雇员列表 
procedure insert_emp(p_empno number,p_enamevarchar2,p_job varchar2, 
 p_sal number); 
 --插入雇员 
 procedure delete_emp(p_empno number); --删除雇员 
 procedure change_emp_sal(p_empno number,p_sal number); 
 --修改雇员工资 
 end emp_pk; 
 /create or replace package body emp_pk 
 --包体部分 
 is 
 v_message varchar2(50); --显示信息 
v_max_sal number(7); --工资上限 
 v_min_sal number(7); --工资下限 
 function exist_emp(p_empno number) return boolean; --判断雇员是否存在函数 
 procedure show_message; --显示信息过程 
 ------------------------------- 初始化过程 ---------------------------- 
 procedure init(p_max number,p_min number) 
 is 
 begin 
 select count(*) into v_emp_count from emp; 
v_max_sal:=p_max; 
 v_min_sal:=p_min; 
 v_message:='初始化过程已经完成！'; 
 show_message; 
 end init; 
---------------------------- 显示雇员列表过程 --------------------- 
 procedure list_emp 
 is 
 begin 
dbms_output.put_line('姓名 职务 工资'); 
 for emp_rec in (select * from emp) 
 loop 
 dbms_output.put_line(rpad(emp_rec.ename,10,'')||rpad(emp_rec.job,10,' 
')||to_char(emp_rec.sal)); 
 end loop; 
 dbms_output.put_line('雇员总人数'||v_emp_count); 
 end list_emp; 
----------------------------- 插入雇员过程 ----------------------------- 
procedureinsert_emp(p_empno number,p_enamevarchar2,p_job varchar2,p_sal number) 
 is 
 begin 
 if not exist_emp(p_empno) then 
 insert into emp(empno,ename,job,sal) values(p_empno,p_ename,p_job,p_sal); 
 commit; 
 v_emp_count:=v_emp_count+1; 
 v_message:='雇员'||p_empno||'已插入!'; 
 else 
v_message:='雇员'||p_empno||'已存在,不能插入!'; 
 end if; 
 show_message; 
 exception 
 when others then 
 v_message:='雇员'||p_empno||'插入失败!'; 
 show_message; 
 end insert_emp; 
--------------------------- 删除雇员过程 -------------------- 
 procedure delete_emp(p_empno number) 
 is 
 begin 
 if exist_emp(p_empno) then 
 delete from emp where empno=p_empno; 
 commit; 
 v_emp_count:=v_emp_count-1; 
 v_message:='雇员'||p_empno||'已删除!'; 
 else 
v_message:='雇员'||p_empno||'不存在，不能删除!'; 
 end if; 
 show_message; 
 exception 
 when others then 
 v_message:='雇员'||p_empno||'删除失败!'; 
 show_message; 
 end delete_emp; 
--------------------------------------- 修改雇员工资过程 ------------------------------------ 
 procedure change_emp_sal(p_empno number,p_sal number) 
 is 
 begin 
 if (p_sal>v_max_sal or p_sal<v_min_sal) then 
 v_message:='工资超出修改范围!'; 
 elsif not exist_emp(p_empno) then 
 v_message:='雇员'||p_empno||'不存在，不能修改工资!'; 
else 
 update emp set sal=p_sal where empno=p_empno; 
 commit; 
 v_message:='雇员'||p_empno||'工资已经修改!'; 
 end if; 
 show_message; 
 exception 
 when others then 
 v_message:='雇员'||p_empno||'工资修改失败!'; 
 show_message; 
 end change_emp_sal; 
---------------------------- 显示信息过程 ---------------------------- 
 procedure show_message 
 is 
 begin 
 dbms_output.put_line('提示信息：'||v_message); 
 end show_message; 
------------------------ 判断雇员是否存在函数 ------------------- 
 function exist_emp(p_empno number) 
 return boolean 
 is 
 v_num number; --局部变量 
 begin 
 select count(*) into v_num from emp where empno=p_empno; 
if v_num=1 then 
 return true; 
 else 
 return false; 
 end if; 
 end exist_emp; 
 ----------------------------- 
 end emp_pk; 

结果为：
程序包已创建。
程序包主体已创建。
步骤 2：初始化包：

set serveroutput on 
execute emp_pk.init(6000,600); 

显示为：
提示信息：初始化过程已经完成！
步骤 3：显示雇员列表：

execute emp_pk.list_emp; 

显示为：

 姓名 职务 工资 
 smith clerk 1560 
 allen salesman 1936 
 ward salesman 1830 
 jones manager 2975 
 ... 

雇员总人数：14
步骤 4：插入一个新记录：
sql 代码

execute emp_pk.insert_emp(8001,'小王','clerk',1000); 

显示结果为：

提示信息：雇员 8001 已插入!
pl/sql 过程已成功完成。
步骤 5：通过全局变量 v_emp_count 查看雇员人数：

begin 
dbms_output.put_line(emp_pk.v_emp_count); 
end; 

显示结果为：

15
pl/sql 过程已成功完成。
步骤 6：删除新插入记录：

execute emp_pk.delete_emp(8001); 

显示结果为：

提示信息：雇员 8001 已删除!
pl/sql 过程已成功完成。
再次删除该雇员：

execute emp_pk.delete_emp(8001); 
结果为：

提示信息：雇员 8001 不存在，不能删除!
步骤 7：修改雇员工资：

execute emp_pk.change_emp_sal(7788,8000); 

显示结果为：
提示信息：工资超出修改范围!
pl/sql 过程已成功完成。
步骤 8：授权其他用户调用包：
如果是另外一个用户要使用该包，必须由包的所有者授权，下面授予 studen 账户对该包的使
用权：
grant execute on emp_pk to student;
每一个新的会话要为包中的公用变量开辟新的存储空间,所以需要重新执行初始化过程。两个
会话的进程互不影响。
步骤 9：其他用户调用包。
启动另外一个 sql*plus，登录 student 账户，执行以下过程：

set serveroutput on 
 execute scott.emp_pk. emp_pk.init(5000,700); 

结果为：
提示信息：初始化过程已经完成！
pl/sql 过程已成功完成。

说明：在初始化中设置雇员的总人数和修改工资的上、下限，初始化后 v_emp_count 为 14 人，
插入雇员后 v_emp_count 为 15 人。v_emp_count 为公有变量，所以可以在外部程序中使用
dbms_output.put_line 输出，引用时用 emp_pk.v_emp_count 的形式，说明所属的包。
而私有变量 v_max_sal 和 v_min_sal 不能被外部访问，只能通过内部过程来修改。
同样，exist_emp 和 show_message 也是私有过程，也只能在过程体内被其他模块引用。
注意：在最后一个步骤中，因为 student 模式调用了 scott 模式的包，所以包名前要增加
模式名 scott。不同的会话对包的调用属于不同的应用，所以需要重新进行初始化。

1.6.2.1.8.4. 操作练习

1)如果存储过程的参数类型为 out，那么调用时传递的参数应该为：
a．常量 b．表达式 c．变量 d．都可以
2)下列有关存储过程的特点说法错误的是：
a．存储过程不能将值传回调用的主程序
b．存储过程是一个命名的模块
c．编译的存储过程存放在数据库中
d．一个存储过程可以调用另一个存储过程
3)下列有关函数的特点说法错误的是：
a．函数必须定义返回类型
b．函数参数的类型只能是 in
c．在函数体内可以多次使用 return 语句
d．函数的调用应使用 execute 命令
4)包中不能包含的元素为:
a．存储过程 b．存储函数
c．游标 d．表
5)下列有关包的使用说法错误的是：
a．在不同的包内模块可以重名
b．包的私有过程不能被外部程序调用
c．包体中的过程和函数必须在包头部分说明
d．必须先创建包头，然后创建包体

1.6.3. 触发器

触发器:是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们，其功能如下：
1）允许/限制对表的修改
2）自动生成派生列，比如自增字段
3）强制数据一致性
4）提供审计和日志记录
5）防止无效的事务处理
6）启用复杂的业务逻辑

开始

create trigger biufer_employees_department_id 
 before insert or update 
 of department_id 
 on employees 
 referencing old as old_value 
 new as new_value 
 for each row 
 when (new_value.department_id<>80 ) 
begin 
 :new_value.commission_pct :=0; 
end;
/

1.6.3.1. 触发器的组成部分

1）触发器名称
2）触发语句
3）触发器限制
4）触发操作

触发器名称：

create trigger biufer_employees_department_id 

命名习惯：

biufer（before insert update for each row）
employees 表名
department_id 列名

触发语句
比如：
表或视图上的 dml 语句
ddl 语句
数据库关闭或启动,startup shutdown 等等

before insert or update 
 of department_id 
 on employees 
 referencing old as old_value 
 new as new_value
 for each row

说明：
1）无论是否规定了 department_id ，对 employees 表进行 insert 的时候
2）对 employees 表的 department_id 列进行 update 的时候
3）触发器限制

when (new_value.department_id<>80 )
限制不是必须的。此例表示如果列 department_id 不等于 80 的时候，触发器就会执行。
其中的 new_value 是代表更新之后的值

1.6.3.2. 触发操作

是触发器的主体

begin 
 :new_value.commission_pct :=0; 
end; 

主体很简单，就是将更新后的 commission_pct 列置为 0

1.6.3.3. 触发

insert into employees(employee_id, 
last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct ) 
values( 12345,’chen’,’donny’, sysdate, 12, ‘donny@hotmail.com’,60,10000,.25); 
select commission_pct from employees where employee_id=12345; 

触发器不会通知用户，便改变了用户的输入值。

1.6.3.4. 触发器类型：

1） 语句触发器
2） 行触发器
3） instead of 触发器
4） 系统条件触发器
5） 用户事件触发器

1.6.4. Job 创建和操作

1.6.4.1. 设置初始化参数 job_queue_processes

 alter system set job_queue_processes=n;（n>0）job_queue_processes 最大值为 1000

查看 job queue 后台进程

 select name,description from v$bgprocess;

1.6.4.2. dbms_job package 用法介绍

包含以下子过程：

 broken()过程;
 change()过程;
 interval()过程;
 isubmit()过程;
 next_date()过程;
 remove()过程;
 run()过程;
 submit()过程;
 user_export()过程;
 what()过程;

(1)broken()过程更新一个已提交的工作的状态，典型地是用来把一个已破工作标记为未破工作。这个过程有三个参数：job 、broken 与 next_date。
procedure broken (job in binary_integer,broken in boolean,next_date in date :=sysdate) - - job 参数是工作号，它在问题中唯一标识工作。

• broken 参数指示此工作是否将标记为破——true 说明此工作将标记为破，而 flase 说明此工作将标记为未破。
• next_date 参数指示在什么时候此工作将再次运行。此参数缺省值为当前日期和时间。
  job 如果由于某种原因未能成功之行，oracle 将重试 16 次后，还未能成功执行，将被标记为
  broken 重新启动状态为 broken 的 job，有如下两种方式;

a、利用 dbms_job.run()立即执行该 job

 begin
 dbms_job.run(:jobno) 该 jobno 为 submit 过程提交时返回的 job number
 end;
 /

b、利用 dbms_job.broken()重新将 broken 标记为 false

 begin
 dbms_job.broken (:job,false,next_date)
  end;
 /

(2) change()过程用来改变指定工作的设置。
这个过程有四个参数：job、what 、next_date 与 interval。

 procedure change (job in binary_integer,
 what in varchar2,
 next_date in date,
 interval in varchar2)

此 job 参数是一个整数值，它唯一标识此工作。
what 参数是由此工作运行的一块 pl/sql 代码块。
next_date 参数指示何时此工作将被执行。
interval 参数指示一个工作重执行的频度.

(3)interval()过程用来显式地设置重执行一个工作之间的时间间隔数。这个过程有两个参数：job
与 interval。
procedure interval (job in binary_integer,interval in varchar2)
job 参数标识一个特定的工作。interval 参数指示一个工作重执行的频度。

(4) isubmit()过程用来用特定的工作号提交一个工作。这个过程有五个参数：job、what、next_date、
interval 与 no_parse。

 procedure isubmit (job in binary_ineger,
 what in varchar2,
 next_date in date,
 interval in varchar2,
 no_parse in booean:=false)

这个过程与 submit()过程的唯一区别在于此 job 参数作为 in 型参数传递且包括一个由开发者
提供的工作号。如果提供的工作号已被使用，将产生一个错误。

(5)next_date()过程用来显式地设定一个工作的执行时间。这个过程接收两个参数：job 与
next_date。
procedure next_date(job in binary_ineger,next_date in date)
job 标识一个已存在的工作。next_date 参数指示了此工作应被执行的日期与时间。

(6)remove()过程来删除一个已计划运行的工作。这个过程接收一个参数：
procedure remove(job in binary_ineger);
job 参数唯一地标识一个工作。这个参数的值是由为此工作调用 submit()过程返回的 job 参
数的值。已正在运行的工作不能由调用过程序删除。

(7)run()过程用来立即执行一个指定的工作。这个过程只接收一个参数：
procedure run(job in binary_ineger)
job 参数标识将被立即执行的工作。

(8)使用 submit()过程，工作被正常地计划好。
这个过程有五个参数：job、what、next_date、interval 与 no_parse。

 procedure submit ( job out binary_ineger,
 what in varchar2,
 next_date in date,
 interval in varchar2,
 no_parse in booean:=false)

job 参数是由 submit()过程返回的 binary_ineger。这个值用来唯一标识一个工作。
what 参数是将被执行的 pl/sql 代码块。
next_date 参数指识何时将运行这个工作。
interval 参数何时这个工作将被重执行。
no_parse 参数指示此工作在提交时或执行时是否应进行语法分析——true 指示此 pl/sql 代码
在它第一次执行时应进行语法分析，而 false 指示本 pl/sql 代码应立即进行语法分析。

(9)user_export()过程返回一个命令，此命令用来安排一个存在的工作以便此工作能重新提交,此程
序有两个参数：job 与 my_call。
procedure user_export(job in binary_ineger,my_call in out varchar2)
job 参数标识一个安排了的工作。my_call 参数包含在它的当前状态重新提交此工作所需要的
正文。

(10)what()过程应许在工作执行时重新设置此正在运行的命令。这个过程接收两个参数：job 与
what procedure what (job in binary_ineger, what in out varchar2)
---job 参数标识一个存在的工作。what 参数指示将被执行的新的 pl/sql 代码。

1.6.4.3. 查看相关 job 信息

1.6.4.3.1. 相关视图

dba_jobs
all_jobs
user_jobs
dba_jobs_running 包含正在运行 job 相关信息

1.6.4.3.2. 查看相关信息

select job, next_date, next_sec, failures, broken
 from dba_jobs;
 
 job next_date next_sec failures b
 ------- --------- -------- -------- -
 9125 01-jun-01 00:00:00 4 n
 14144 24-oct-01 16:35:35 0 n
 9127 01-jun-01 00:00:00 16 y
 3 rows selected

1.6.4.3.3. 正在运行的 job 相关信息

 select sid, r.job, log_user, r.this_date, r.this_sec
 from dba_jobs_running r, dba_jobs j
 where r.job = j.job;
 
 sid job log_user this_date this_sec
 ----- ---------- ------------- --------- --------
 12 14144 hr 24-oct-94 17:21:24
 25 8536 qs 24-oct-94 16:45:12
 2 rows selected.

1.6.4.3.4. job queue lock 相关信息

 select sid, type, id1, id2
 from v$lock
 where type = 'jq';
 
 sid ty id1 id2
 --------- -- --------- ---------
 12 jq 0 14144
 1 row selected

1.6.4.4. 实例操作

创建测试表

 create table test(a date);
 ------create table 

创建一个自定义过程

 create or replace procedure myproc as
 begin
 insert into test values(sysdate);
 end;
 /

--------------过程已创建。
创建 job

 variable job1 number;
 begin
 dbms_job.submit(:job1,'myproc;',sysdate,'sysdate+1/1440'); --每天 1440 分钟，即一分钟运行 test 过程一次
 end;
 / 

运行 job

 begin
 dbms_job.run(:job1);
 end;
 /

 sql> select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间 from test;
  时间
 -------------------
 2001/01/07 23:51:21
 2001/01/07 23:52:22
 2001/01/07 23:53:24

删除 job

 begin
 dbms_job.remove(:job1);
 end;
 /

1.6.5. 后台脚本

编辑文件：dyk_name_mtable1.sql 写入以下内容：

alter table owner.table_name1 move tablespace tablespace_name1;
alter table owner.table_name2 move tablespace tablespace_name2; 

编辑文件 dyk_name_mtable1.sh

sqlplus kt/kt_hitlr7@gstj1<<!>>dyk_name_mtable2.out
@ dyk_name_mtable1.sql
!

执行命令：

Nohup dyk_name_mtable1.sh &

1.6.5.1. EOF方式

1.6.5.1.1. oracle

source /home/oracle/.profile
. ${curdir}/db_monitor.conf
sqlplus -S / as sysdba <<EOF
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
col PDB_NAME for a35
col username for a35
SET line 999 PAGES 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
spool account_expiry.log;
select *
  from (select c.PDB_NAME, u.USERNAME, trunc(u.EXPIRY_DATE - sysdate) days
          from sys.cdb_users u, sys.cdb_pdbs c
         where c.con_id = u.con_id
             and u.username not in ('SYS', 'SYSTEM')
             and u.username not like 'XJ_%'
             and u.username not like 'HW_%'
             and u.username not like 'SZSM_%'
             and u.username not like 'STQ_%'
             and u.username not like 'C##%'
             and u.EXPIRY_DATE is not null) u
 where u.days < ${EXPIRY_DAY} and u.days >-1;
spool off;
exit
EOF

1.6.5.1.2. mysql环境

dbus=root
dbpw='Admin@123'
port=3306
ip=10.25.21.216
socket='/data/mysql/data/mysql3306.sock'
mysqlcmd="/usr/local/mysql/bin/mysql  -u${dbus}    -p${dbpw}  -S${socket}"

${mysqlcmd} <<EOF
或者
mysql -uroot -proot -S /data/mysql/data3307/mysql3307.sock <<EOF
use test;
select * from test;
#exit
EOF

1.7. 同义词操作

1.7.1. 创建同义词

1.7.1.1. 公共同义词

create public synonym table_name for owner.synonym_name;

1.7.1.2. 私有同义词

create synonym table_name for owner.synonym_name;

1.7.2. 删除同义词

1.7.2.1. 删除公共同义词

drop synonym synonym_name public;

1.7.2.2. 删除私有同义词

drop synonym synonym_name;

1.8. dblink 操作

1.8.1. 概念

database link（简称DBLINK）是定义一个数据库到另一个数据库的路径的对象，database link允许查询其他数据库的远程表及执行远程程序。

database link是单向的连接。在创建database link的时候，Oracle在数据字典中保存相关的database link的信息。
在使用database link的时候，Oracle通过预先定义好的连接信息，访问相应的远程数据库以完成相应的工作。

dblink(Database Link)就像电话线一样是一个通道，如果要访问另外一个数据库表中的数据时，本地数据库中就必须要创建远程数据库的dblink，通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。

1.8.2. 建立dblink之前需要确认的事项：

确认从local database到remote database的网络连接是正常的，tnsping要能成功。

确认在remote database上面有相应的访问权限。

1.8.3. dblink 创建

创建dblink的用户有对应的数据库权限，创建dblink命令为：
--public表示所创建的dblink所有用户都可以使用

create public database link 

或者
--所创建的dblink只能是创建者能使用，别的用户使用不了

create database link 

如果用户没有权限创建dblink，则可以通过管理员身份并使用 ：

grant create public database link, create database link to myAccount；

实例：

create [public] database link dblink_name connect to username identified by password using ‘connectstring’ 
例如：create public database link db_file_name1 connect to oracle identified by oracle_123 using CC

create database  link blink1 connect to dbName identified by dbPassword using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.1.106)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';
--blink1 ： 表示dblink名字
--dbName ：表示 远程数据库的用户
--dbPassword：表示 远程数据库的密码
--HOST ： 表示远程数据库IP
--PORT ： 表示远程数据库端口
--SERVICE_NAME ： 远程数据库的实例名


如：A库需要访问B库的表数据，需要在A库建立连接到B库的dblink，在A账户下执行（推荐）：
create database link LIS_LINK01 connect to bUser identified by bPasword using '10.22.xx.xx:1521/orcl'

bUser：B库的数据库账户
bPassword：B库的数据密码
10.22.xx.xx：B库的ip地址

1.8.4. dblink 操作

1.8.4.1. 查看dblink

set linesize 300
set pagesize 999
col DB_LINK format a30
col USERNAME format a30
col HOST format a30
col CREATED format a10
col OWNER format a10
col object_name format a20
select owner,object_name from dba_objects where object_type='DATABASE LINK';

查看dblink的link信息：
select * from dba_db_links;

1.8.5. database link删除

-- 删除public类型的database link

DROP PUBLIC database link link_name;

-- 删除非public类型的database link
-- 注意：只有owner自己能删除自己的非public类型database link

DROP database link link_name;

通过dblink应用例子
当需要跨库拷贝数据而且数据量很大的时候，使用dblink速度很快。
如：通过dblink跨库复制数据量非常多的表T_WEBSERVICE

create table test as select * from T_WEBSERVICE@db3

1.9. Sequences 操作

1.9.1. Sequences 创建

创建 sequence：
先要有 create sequence 或者 create any sequence 权限，执行以下命令就行：

语法
CREATE SEQUENCE sequence //创建序列名称
[INCREMENT BY n] //递增的序列值是 n 如果 n 是正数就递增,如果是负数就递减 默认是 1
[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue
[{MAXVALUE n | NOMAXVALUE}] //最大值  
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环
[{CACHE n | NOCACHE}];//分配并存入到内存中

INCREMENT BY用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表Oracle序列的值是按照此步长递减的。

START WITH 定义序列的初始值(即产生的第一个值)，默认为1。

MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增Oracle序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。

MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是?10的26次方;对于递增序列，最小值是1。

CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。

CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。


eg
create sequence sequence_name 
 increment by name_values1 -- 每次加几个 
 start with name_values1 -- 从 1 开始计数 
 nomaxvalue -- 不设置最大值 
 nocycle -- 一直累加，不循环 
 cache 10;

create sequence seq_test
 increment by 2 -- 每次加几个 
 start with 1 -- 从 1 开始计数 
 nomaxvalue -- 不设置最大值 
 nocycle -- 一直累加，不循环 
 cache 10;

cache的作用
大量语句发生请求，申请序列时，为了避免序列在运用层实现序列而引起的性能瓶颈。Oracle序列允许将序列提前生成 cache x个先存入内存，在发生大量申请序列语句时，可直接到运行最快的内存中去得到序列。但cache个数也不能设置太大，因为在数据库重启时，会清空内存信息，预存在内存中的序列会丢失，当数据库再次启动后，序列从上次内存中最大的序列号+1 开始存入cache x个。这种情况也能会在数据库关闭时也会导致序号不连续。

1.9.2. 查看sequence当前值

一旦定义了 sequence_name，你就可以用 currval，nextval
currval=返回 sequence 的当前值
nextval=增加 sequence 的值，然后返回 sequence 值

比如： （未使用之前必须先查询seq_test.nextval生成第一个值）

select seq_test.nextval from dual;
select seq_test.currval  from dual;

NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用。

CURRVAL 中存放序列的当前值,NEXTVAL 应在 CURRVAL 之前指定 ，二者应同时有效。

1.9.3. 查看多有序列

查看当前用户下的所有序列：
select * from user_sequences;
查看所有用户下的所有序列：
select * from all_sequences;
还有一个
select * from dba_sequences;

1.9.4. 可以使用 sequence 的地方：

• 不包含子查询、snapshot、view 的 select 语句
• insert 语句的子查询中
• nsert 语句的 values 中
• update 的 set 中

insert into 表名 value（seq_name.currval）

1.9.4.1. 实例：

insert into table_name values 
(empseq.nextval, 'row1', 'row2',7902, sysdate, 1200, null, 20); 
select empseq.currval from dual;

1.9.5. 注意事项：

• 第一次 nextval 返回的是初始值；随后的 nextval 会自动增加你定义的 increment by 值，然后返回增加后的值。currval 总是返回当前 sequence 的值，但是在第一次 nextval 初始化之后才能使用 currval，否则会出错。

一次 nextval 会增加一次 sequence 的值，所以如果你在同一个语句里面使用多个 nextval，其值就是不一样的. - 如果指定 cache 值，oracle 就可以预先在内存里面放置一些 sequence，这样存取的快些。cache里面的取完后，oracle 自动再取一组到 cache。 使用 cache 或许会跳号， 比如数据库突然不正常 down 掉 （shutdown abort),cache 中的 sequence 就会丢失.
所以可以在 create sequence 的时候用 nocache 防止这种情况。

1.9.6. alter sequence，修改起始值和最大值

你或者是该 sequence 的 owner，或者有 alter any sequence 权限才能改动 sequence. 可以 alter 除
start 至以外的所有 sequence 参数.如果想要改变 start 值，必须 drop sequence 再 re-create .
alter sequence 的实例

alter sequence sequence_name 
 increment by 10 
 maxvalue 10000 
 cycle -- 到 10000 后从头开始 
 nocache; 

影响 sequence 的初始化参数：
sequence_cache_entries =设置能同时被 cache 的 sequence 数目。

1.9.7. Sequences 删除

drop sequence sequence_name;

1.10. 数据库操作必须掌握技巧

1.10.1. 中断正在执行的 session 连接

在 Oracle 数据库中,可以通过 kill session 的方式来终止一个进程，语法结构如下：

alter system kill session 'sid,serial#' ;

被 kill 掉的 session,状态会被标记为 killed,Oracle 会在该用户下一次 touch 时清除该进程. 当一个 session 被 kill 掉以后，该 session 的 paddr 被修改，如果有多个 session 被 kill，那么多个 session的 paddr 都被更改为相同的进程地址:

select s.pid, s.addr, a.sid, a.serial#, a.program, a.status, a.username, a.machine, 
a.command, a.sql_hash_value from v$process s, v$session a
where s.addr = a.paddr and a.username is not null and a.username <> 'SYS';

很多时候，status 状态是 killed，这种情况下资源是无法释放的,要查询对应的 spid，在操作系统级来 kill 这些进程，但是由于此时 v$session.paddr 已经改变，我们无法通过 v$session 和v$process 关联来获得 spid。这样，需要知道当前进程的 addr 值，通过以下语句可以进行：（注意，查询 x$ksupr 视图需要 sys 用户才可以查看）

select s.username, s.status, x.addr, x.ksllapsc, x.ksllapsn, x.ksllaspo, x.ksllid1r, x.ksllrtyp,
decode(bitand(x.ksuprflg, 2), 0, null, 1) from x$ksupr x, v$session s
where s.paddr(+) = x.addr and bitand(ksspaflg, 1) != 0 and s.username <> 'SYS';

通过以上语句，找到进程的 addr 值，通过 addr 值就可以在 v$process 中找到 spid，然后可以使用 Kill 或者 orakill 在系统级来杀掉这些进程。
当在 Oracle 中 kill session 以后, Oracle 只是简单的把相关 session 的 paddr 指向同一个虚拟地址.
此时 v$process 和 v$session 失去关联,进程就此中断。然后 Oracle 就等待 PMON 去清除这些

Session.所以通常等待一个被标记为 Killed 的 Session 退出需要花费很长的时间.如果此时被 Kill的 process，重新尝试执行任务，那么马上会收到进程中断的提示，process 退出，此时 Oracle 会立即启动 PMON 来清除该 session.这被作为一次异常中断处理：

1.10.2. 查看进程号：

select s.pid, s.addr from v$process s where s.addr=’ADDR_VALUES’ 

b）kill 操作系统上进程，在 oracle 用户下执行如下命令：

 kill -9 进程号

1.10.3. 用户密码修改和用户账号加锁、解锁

1.10.3.1. a) 用户修改密码：

select username,password from dba_users where username='USER_NAME';
alter user user_name identified by passwd_char;
conn user_name/passwd_char

1.10.3.2. b）用户账号锁定：

oracle 用户账号加锁：alter user user_name account lock;
oracle 用户账号解锁：alter user user_name account unlock;

1.10.3.3. 账号创建

查询用户拥有的对象权限：

SELECT count(*) FROM dba_tab_privs a WHERE a.grantee = 'XULIN';

1.10.3.4. 查询用户拥有的角色权限：

SELECT * FROM dba_role_privs a WHERE a.GRANTEE = 'XULIN';

1.10.3.5. 查询用户拥有的系统权限：

SELECT * FROM dba_sys_privs a WHERE a.GRANTEE = 'XULIN';

1.10.3.6. 完全复制一个Oracle用户的三个权限表

select 'grant '||privilege||' on '||owner||'.'||table_name||' to XULIN'||';' from dba_tab_privs where grantee='XULIN' ;

1.10.3.7. 查询用户密码：

select user#,name,password from user$ where name='SCOTT';

1.10.3.8. 授予角色权限

grant connect, resource,dba to dbuser1 identified by values 'CDC57F9E62A38D03';

1.10.3.9. 以密文的方式创建用户

create user dbuser1 identified by values 'CDC57F9E62A38D03';

alter user dbuser1 account unlock; 

select distinct owner from all_objects; 

1.10.3.10. 用户创建

create user 用户名 identified by 密码；//注意这是oracle 12c之前的版本的要求
create user c##用户名 identified by 密码; //oracle 12c这个版本要加上c##这个符号才能创建

grant create any table,select any table,connect,resource to xxx;

如果创建的用户没法连接 出现ORA-01045: user lacks CREATE SESSION privilege; logon denied’字样
则给他授予
解决办法：grant create session,resource to
如：

grant create session,resource to c##lin

或在pl/sql developer里，创建user时选择system privileges->create session

1.10.4. 查看和修改最大会话数

1.10.4.1. 查询数据库允许的最大连接数：

select value from v$parameter where name = 'processes';
或者：show parameter processes;

1.10.4.2. 修改最大会话数

1.10.4.2.1. 查看processes和sessions参数

注意：修改会话数只需要修改process的值就可以了，session的值会自动对应到相关的值

SQL> show parameter processes
SQL> show parameter sessions

修改processes和sessions值

SQL>alter system set processes=300 scope=spfile;
SQL>alter system set sessions=335 scope=spfile;

修改processes和sessions值必须重启oracle服务器才能生效(都是静态数据库参数)
ORACLE的连接数(sessions)与其参数文件中的进程数(process)有关，它们的关系如下：
sessions=(1.1*process+5)

1.10.4.2.2. 修改流程

查询数据库当前进程的连接数：

select count(*) from v$process;

b.查看数据库当前会话的连接数：

select count(*) from v$session;

c.查看数据库的并发连接数：

select count(*) from v$session where status='ACTIVE';

d.查看当前数据库建立的会话情况：

select sid,serial#,username,program,machine,status from v$session;

4、查询数据库允许的最大连接数：

select value from v$parameter where name = 'processes';
或者：show parameter processes;

修改数据库允许的最大连接数：

alter system set processes = 300 scope = spfile;
(需要重启数据库才能实现连接数的修改)
重启数据库：

shutdown immediate;
startup;

1.10.5. 核心参数修改，数据库启动（必须 sys 用户权限）

1.10.5.1. db_files 参数

在 UNIX 上存储方式是裸卷时，该参数值如果太小，如果当前裸设备文件的数量超过默认 db_files 默认值，在当前表空间上添加空间，会报错 ORA-00059: maximum number of DB_FILES exceeded，只有修改当前的 db_files 值才可以解决（一般修改到 4000 以上），执行
如下步骤：
1）修改参数

 show parameter db_files;
alter system set db_files=values_number scope=spfile ;

2）重启数据库

shutdown immediate/startup 
show parameter db_files;

1.10.5.2. process 参数

如果太小，则终端不能连接（一般修改到 4000 左右）,执行如下命令：
1）参数修改

show parameter processes;
alter system set processes=values_number scope=spfile ;

2）重启数据库

shutdown immediate/startup 
show parameter processes;

1.10.6. 字符集修

改修改 server 端字符集(不建议使用)
在 oracle 8 之前，可以用直接修改数据字典表 props$来改变数据库的字符集。
但 oracle8 之后，至少有三张系统表记录了数据库字符集的信息，只改 props$表并不完全，
可能引起严重的后果。正确的修改方法如下:
1)连接数据库：

sqlplus /nolog
conn / as sysdba;

2)字符集修改（若此时数据库服务器已启动，则先执行 shutdown immediate 命令关闭数据库服务
器，然后执行以下命令）:

startup mount;
alter system enable restricted session;
alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
alter database open;
alter database character set zhs16gbk;
alter database national character set zhs16gbk;
shutdown immediate;
startup

3）查看字符集

select userenv('language') from dual
select nls_charset_name(to_number('0354','xxxx')) from dual;

1.10.7. 日志切换和初始化文件创建

1.10.7.1. 管理联机重做日志文件

联机重做日志文件主要特点：
a 对数据库做的任何改变都会被记录在联机重做日志中
b 提供了一种恢复机制
c 必须组成组
d 至少需要两组

一个 oracle 数据库至少有两个重做日志文件，一个组一个。如果同一个组有两个文件，LGWR会向两个文件写入相同的信息,同一组的每个成员都有一个相同的日志序列号。Log switch：将一个日志组切换到另一个日志组,Log switch 引发了 checkpoint,Checkpoint：就修改过的数据回写到磁盘的数据文件中。

1.10.7.2. LGWR 什么时候开始写

a 当一个事务 commit 的时候（一个事务 commit 并不是把数据写到数据文件中，而是写到联机重做日志文件中）
b 每 3 秒钟写一次
c 当 Redo Log Buffer 达到 1/3 满的时候
d Redo Log Buffer 中有超过 1M 内容的时候
e 在一个 check point 执行之前，LGWR 先把信息写到联机重做日志文件中。

1.10.7.3. 手工切换日志

alter system switch logfile;

1.10.7.4. 手工启动检查点

alter system checkpoint;

1.10.7.5. 查询当前系统中日志的情况

select * from v$logfile;

1.10.7.6. 增加一个新组

文件系统：alter database add logfile group 4 ('/directory_name/redo_name.log') size 
xxxxM;
裸卷：alter database add logfile group 4 ('/dev/vgX/rredo_xx_NN') size 1023M;

1.10.7.7. 给日志组增加一个新成员

文件系统：alter database add logfile member '/directory_name/redo_name.log' to 
group 4;
裸卷：alter database add logfile member '/dev/vgX/rredo_xx_NN' to group 2；

1.10.7.8. 删除一个成员

（只是在控制文件中删除相关信息，在 OS 中还是真实存在）
alter database drop logfile member '/directory_name/redo_name.log';
alter database drop loggile member '/dev/vgX/rredo_xx_NN';

active 或 current 状态的组不能够被drop

1.10.7.9. 删除一个组

alter database drop logfile group 2;

1.10.7.10. 重新放置和重命名联机重做日志文件

（在做此操作前，必须要对数据库进行全备份）有两种方法：

1.10.7.10.1. 使用 alter database rename file 命令

1) 关闭数据库
2) 拷贝联机重做日志文件到新目录下
3) 将数据库置于 MOUNT 状态
4) 执行命令 alter database rename file '/directory_name/redo_name1.log ' to '/directory_name/redo_name2.log ';
5) 将数据库置于 OPEN 状态

1.10.7.10.2. 增加一个新成员，删除旧成员

alter database add logfile member '/directory_name/redo_name.log' to group 2;
alter database drop logfile member '/directory_name/redo_name.log';
alter system switch logfile;

1.10.7.11. 归档日志设置

获取归档日志信息，主要在如下视图：

v$archvied_log、v$archvie_dest、v$log_history、v$database、v$archive_processes
archive log list;

1.10.7.11.1. 单实例数据库归档模式的修改：

(1) archive log list; /察看归档模式
(2) shutdown immediate; 
(3) startup mount /重起数据库到 mount 状态
(4) alter database archivelog/noarchivelog; /修改数据库到归档模式
(5) alter system set log_archive_dest_1 = 'location=/archlog'; /修改归档日志存放目录
(6) show parameter log_archive_start; /查看数据库是否是自动归档
(7) alter system set log_archive_start=true scope=spfile; /修改数据库是自动归档
alter system set log_archive_format=’XXX_ %t_%s.dbf’scope=spfile; /修改数据库归档文件名
(8) alter database open; /启动数据库 
(9) show parameter log_archive_start; /检查修改自动归档是否成功 
(10) alter system switch logfile; alter system switch logfile; 
(11) select name, value from v$parameter where name like 'log_archive_dest%';

注意: alter system archive log start 如果是非自动启动

1.10.7.12. 创建参数文件

pfile(initialization parameter file)用于 oracle8i，在 oracle9i 中也可以用，以文本形式存在，可以用文本编辑器对其中参数进行修改;spfile(server-side initialization parameter file)用于oracle9i，以二进制文本形式存在，不能用文本编辑器对其中参数进行修改。

spfile 改正了 pfile管理混乱的问题，在多结点的环境里，pfile 会有多个 image，启动时候需要跟踪最新的 image。这是个烦琐的过程。用 spfile 以后，所有参数改变都写到 spfile 里面（只要定义 scope=spfile 或
both）

执行 startup 时，按如下顺序寻找初始化参数文件：
(1)spfile.ora
(2)pfile.ora
(3)都没找到，则在默认位置寻找默认名称的服务器端初始化参数文件。
(4)还没找到，则在默认位置寻找默认名称的文本初始化参数文件。

1.10.7.12.1. 1)查看 spfile location

(show parameter pfile/spfile;) 
show parameter spfile 

1.10.7.12.1.1. 2)从 spfile 获取 pfile

sqlplus /nolog 
connect / as sysdba
create pfile='/directory_name1/pfilesid.ora' from '/directory_name2/spfile'; 
create pfile='/directory_name1/pfilesid.ora' from spfile='/directory_name2/spfile'; 
sqlplus /nolog
connect / as sysdba
create pfile='/directory_name1/pfilesid.ora' from spfile;
create pfile='/directory_name1/pfilesid.ora' from spfile='/directory_name2/spfile';

1.10.7.12.1.2. 从 pfile 获取 spfile

shutdown immediate 
create '/directory_name1/spfile' from pfile='/directory_name2/pfile' 
create spfile='/directory_name1/spfile' from pfile='/directory_name2/pfile' 

shutdown immediate
create '/directory_name1/spfile' from pfile='/directory_name2/pfile' 
create spfile='/directory_name1/spfile' from pfile='/directory_name2/pfile'

1.10.7.12.2. 动态修改参数

alter system set parameter=value scope=spfile|both|memory 
alter system set parameter=value scope=spfile|both|memory

说明：
(1)scope=spfile 
对参数的修改仅记录在服务器初始化参数文件中。该选项同时适用于动态与静态的初始化参数。
修改后的参数只有下一次启动数据库时更改才会生效。
(2)scope=memory 
对参数的修改记录在内存中，对于动态初始化参数，更改立即生效。修改并不会被记录在服务器
端的初始化参数中。下一次启动数据库时更改失效，不能用于静态初始化参数。
(3)scope=both 
对参数的修改同时记录在内存中和服务器端的初始化参数文件中。为默认使用值。

1.10.7.12.3. 查看初始化参数

show parameters

1.10.7.12.4. 查看系统全局区大小

show sga; 
select * from v$sga;

1.10.7.12.5. 使用 pfile/spfile 启动数据库

如果你想使用 pfile 启动数据库，你可以在启动时指定 pfile 或者删除 spfile.

startup pfile='/directory_name/pfile/init.ora'; 

不能以同样的方式指定 spfile，但是可以创建一个包含 spfile 参数的 pfile 文件，指向 spfile.
spfile 是一个自 oracle9i 引入的初始化参数，类似于 ifile 参数。spfile 参数用于定义非缺省路径的spfile 文件。可以在 pfile 链接到 spfile 文件,同时在 pfile 中定义其他参数，如果参数重复设置，后读取的参数将取代先前的设置。

1.10.7.12.6. 查看系统是以 pfile 还是 spfile 启动

(1)查询 v$parameter 动态视图，如果以下查询返回空值，那么你在使用 pfile.

select name,value from v$parameter where name='spfile'; 

(2)可以使用 show 命令来显示参数设置，如果以下结果 value 列返回空值，那么说明你在使用 pfile:

show parameter spfile 

(3)查询 v$spparameter 视图
如果以下查询返回 0 值，表示你在使用 pfile,否则表明你使用的是 spfile:

select count(*) from v$spparameter where value is not null;

或者使用以下查询，如果 true 值返回非 0 值，那么说明我们使用的是 spfile.

select isspecified, count(*) from v$spparameter group by isspecified;

1.10.7.12.7. 查询 oracle 游标使用情况的方法

select * from v$open_cursor where user_name = 'TRAFFIC';

1.10.7.13. 控制文件备份和重建

1）备份数据库。
修改控制文件过程中，出现的任何一个错误都会破坏用户的数据库，所以操作以前做好完善的数据备份。
2）发出 alter database backup control file to trace 命令，建立了一个用户追踪文件，文件位置
（user_dump_dest)该文件带有重建当前控制文件所需要的命令。
3）编辑在前面步骤中所产生的追踪文件，除 createcontrol file 语句外，删除追踪文件中的所有行，设置新的参数值。
4）正常关库，(shutdown normal)。
将旧的控制文件移除到备份目录，确保 ORACLE 在数据库启动时，不能在目录中找到控制文件的任何的副本，否则下一步操作会出现失败。
5）启动数据库。
执行 startup nomount 启库，运行 create control file 追踪文件，这将重建新参数值的控制文件。
6）执行 alter database open ，命令执行成功后，至此完成。

1.10.7.14. 操作小技巧

1.10.7.14.1. Oracle 安装完成后用户初始口令

internal/oracle
sys/change_on_install
system/manager
scott/tiger
sysman/oem_temp

1.10.7.14.2. ORACLE9I AS WEB CACHE 的初始默认用户和密码

administrator/administrator

1.10.7.14.3. 怎么创建数据库

1.10.7.14.3.1. 1.dbca

1.10.7.14.3.2. 脚本方式创建

1.10.7.14.4. oracle 中的裸设备指的是什么

裸设备就是绕过文件系统直接访问的储存空间，一般称之为裸卷

1.10.7.14.5. 如何解锁怎样解除 PROCEDURE 被意外锁定?

alter system kill session ‘sid,serir#’; （不过先要查出链接的 session sid）
or
把该过程重新命名即可

1.10.7.14.6. sqlplus 下如何修改编辑器

DEFINE _EDITOR=“<编辑器的完整路经>” -- 必须加上双引号来定义新的
编辑器，也可以把这个写在$ORACLE_HOME/sqlplus/admin/glogin.sql 里面使它永久有效。

1.10.7.14.7. oracle 产生随机函数

dbms_random.random

1.10.7.14.8. 如何才能得知系统当前的 SCN 号（必须要 sys 用户执行）

select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

1.10.7.14.9. 如何在 ORACLE 中取毫秒

9i 之前不支持,9i 开始有 timestamp.
9i 可以用 select systimestamp from dual;

1.10.7.14.10. 如何在字符串里加回车？

select 'Welcome to visit'||chr(10)||'www.CSDN.NET' from dual ;

1.10.7.14.11. 中文是如何排序的？

oracle9i 之前，中文是按照二进制编码进行排序的。在 oracle9i 中新增了按照拼音、部首、
笔画排序功能。设置 nls_sort 值
schinese_radical_m 按照部首（第一顺序）、笔划（第二顺序）排序
schinese_stroke_m 按照笔划（第一顺序）、部首（第二顺序）排序
schinese_pinyin_m 按照拼音排序

2. 常用查询和操作

2.1. 会话相关