.Net程序员学用Oracle系列(30):零碎补充、最后总结(The End)

1、同义词

概念

同义词是数据库对象的一个别名,常用于简化对象访问及提高对象访问的安全性。在使用同义词时,Oracle 数据库自动将它翻译成对应模式对象的名字。与视图类似,同义词并不占用实际存储空间,只在数据字典中保存了同义词的定义。同义词可以指向的对象有表、视图、物化视图、序列、函数、存储过程、包、同义词等。Oracle 同义词有公用同义词和专用同义词两种。普通用户如果希望创建同义词,还需要有 CREATE PUBLIC SYNONYM 这个系统权限。

公用同义词:由一个特殊的用户组 PUBLIC 所拥有,顾名思义,数据库中所有的用户都可以使用公用同义词。公用同义词往往用来标示一些比较普通的数据库对象,这些对象往往大家都需要引用。

专用同义词:由创建它的用户专用,当然,这个同义词的创建者,也可以通过授权来让别的用户访问自己的专用同义词。

语法

创建同义词:

CREATE [PUBLIC] SYNONYM [SCHEMA.]synonym_name FOR [SCHEMA.]object_name[@dblink];

说明:加PUBLIC表示公用,不能同时指定PUBLICsynonym_nameSCHEMA@dblink表示远程数据库连接。

示例:

CREATE PUBLIC SYNONYM staff FOR demo.t_staff;

删除同义词:

DROP [PUBLIC] SYNONYM [SCHEMA.]synonym_name; -- 也不能同时指定`PUBLIC`和`synonym_name`的`SCHEMA`

示例:

DROP PUBLIC SYNONYM staff;

用途

  • 1、多用户协同开发中,可以屏蔽对象的名字及其持有者。如果没有同义词,当操作其他用户的表时,必须通过schema.object_name的形式,采用了 Oracle 同义词之后就可以屏蔽掉 schema,当然这里要注意的是:PUBLIC 同义词只是为数据库对象定义了一个公共的别名,其他用户能否通过这个别名访问这个数据库对象,还要看是否已经为这个用户授权。

  • 2、为用户简化 SQ了语句,屏蔽 schema 也是一种简化 SQL 的体现。同时,如果自己建的表的名字很长,也可以为这个表创建一个 Oracle 同义词来简化 SQL 开发。

  • 3、为分布式数据库的远程对象提供位置透明性。在分布式数据库环境中,为了识别一个数据库对象,必须规定主机名、服务器名、对象的拥有者和对象名,这无疑增加了访问者的访问难度。为了给不同的用户使用数据库对象时提供一个简单的、唯一标识数据库对象的名称,可以为数据库对象创建同义词。

2、Flashback 技术

当某个错误的 UPDATE 或 DELETE 语句被提交之后,如果想要把数据恢复到提交之前的某个时间点,通过逻辑备份或日志理论上也是可以做到的,但过程往往比较繁琐,而且需要花费较长时间才能恢复过来。

为了让数据库能从任何逻辑误的操作中迅速地恢复,Oracle 推出了 Flashback(闪回)技术。Flashback 技术是以 undo segment 中的内容为基础的,因此受限于 UNDO_RETENTON 参数。要使用 Flashback 的特性,就必须启用自动撤销管理表空间(默认即开启)。

Oracle 10g 中的闪回技术主要包含闪回数据库、闪回删除、闪回表、闪回事务及闪回查询等一系列功能,本节将简要介绍其中比较实用的 3 个功能——闪回查询、闪回表及闪回删除。

注意:SCN(System Change Number)是在 Oracle 数据库更新后,用于定义数据库事务提交版本的一个数字,由 DBMS 自动维护(自动递增)。

SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL; -- 查询当前的 SCN
SELECT SCN_TO_TIMESTAMP(2817281) FROM DUAL;               -- SCN 转换成时间点
SELECT TIMESTAMP_TO_SCN('2017-05-20 13:14:51') FROM DUAL; -- 时间点转换成 SCN

当时间点转换成 SCN 时可能会失败,因为那个时间点也许并没有有效的事务操作。

闪回查询(Flashback Query)

利用 SCN 号来进行闪回查询,查询最近一个事务操作之前t_course表中的数据:

SELECT * FROM demo.t_course AS OF SCN DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;

利用时间点来进行闪回查询,查询'2017-05-20 13:14:51'这一秒t_course表中的数据:

SELECT * FROM demo.t_course AS OF TIMESTAMP TO_TIMESTAMP('2017-05-20 13:14:51','yyyy-mm-dd hh24:mi:ss');

闪回表(Flashback Table)

开启行转移功能(必须要开启行转移功能,否则将无法闪回表)。

ALTER TABLE demo.t_course ENABLE ROW MOVEMENT;  -- 开启行转移功能
ALTER TABLE demo.t_course DISABLE ROW MOVEMENT; -- 关闭行转移功能

利用 SCN 号来进行闪回表操作,将demo.t_course表的数据恢复到 2869642:

FLASHBACK TABLE demo.t_course TO SCN 2869642;

利用时间点来进行闪回表操作,将demo.t_course表的数据恢复到'2017-05-22 18:00:00':

FLASHBACK TABLE demo.t_course TO TIMESTAMP TO_TIMESTAMP('2017-05-22 18:00:00','yyyy-mm-dd hh24:mi:ss');

闪回删除(Flashback Drop)

普通的 DROP TABLE 操作并不会彻底的删除表,而是把表放入了回收站(RecycleBin)。放入回收站实际上就是保存原表的位置,然后将要删除的表重新命名为前缀是“BIN$”的表,并将删除的表信息(包括被删除表的新名字和原名字)存储在回收站中。

回收站是一个逻辑结构,不具有物理数据结构。记录在回收站中信息会保留一段时间,直到回收站空间不足(会自动删除一些表信息)或者使用 PURGE 指令清除回收站中的记录。只要被删除表的信息仍在回收站中,就可以通过闪回技术将这些表恢复。

闪回删除类似于操作系统的垃圾回收站功能,可以从中恢复被 DROP 的表或者索引。无论是将回收站里的表恢复为原名称或指定新名称,表中的数据都不会丢失。可以通过USER_RECYCLEBIN视图或DBA_RECYCLEBIN来查询回收站中有那些被删除的表信息。

语法:

FLASHBACK TABLE user_recyclebin.object_name TO BEFORE DROP[RENAME TO new_table_name];

示例:

FLASHBACK TABLE "BIN$/AwWTga9PSHgQKjACgoQJA==$0" TO BEFORE DROP;
FLASHBACK TABLE "BIN$Vm9deG4nRjatKqnWpQ+RBA==$0" TO BEFORE DROP RENAME TO tt;

补充示例:

PURGE DBA_RECYCLEBIN;           -- 清空当前数据库的回收站
PURGE RECYCLEBIN;               -- 清空当前用户的回收站
PURGE TABLE demo.t_course;      -- 清空 demo.t_course 表在回收站中的信息
DROP TABLE demo.t_course PURGE; -- 彻底删除 demo.t_course 表,不进入回收站

3、连接字符串的写法

在应用程序中连接 Oracle 数据库的写法有三种,分别是:TNS Alias(别名写法)、Connect Descriptor(普通写法)和 Easy Connect Naming Method(简易写法)。

别名写法:基本上用过 Oracle 的人都知道这种写法,同时这也是最简单、最繁琐的写法。具体来说就是先在配置文件tnsnames.ora中配置 TNS Connect String 并为其取一个别名,然后再到应用程序中通过别名来连接数据库。

一般(Oracle)数据库管理工具都支持别名写法,如被广泛使用的 PL/SQL Developer,配置好tnsnames.oraTNS_ADMIN、Oracle 主目录及 OCI 库路径之后就能根据别名登录了。在 PL/SQL Developer 登录界面上的第 3 排——数据库的备选项就是tnsnames.ora中配置的别名。如下图:

Login Oracle by PL/SQL Developer

TNS Connect String 写法一:

ORCL_127.0.0.1=(
    DESCRIPTION = (
        ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )(
        CONNECT_DATA = (SERVICE_NAME = orcl)
    )
)

TNS Connect String 写法二:

ORCL_127.0.0.1=(
    DESCRIPTION = (
        ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)
    )(
        CONNECT_DATA = (SERVICE_NAME = orcl)
    )
)

最终的应用程序连接字符串是:

"Data Source=tns_name;User Id=uid;Password=pwd;"         -- 语法
"Data Source=ORCL_127.0.0.1;User Id=demo;Password=test;" -- 实例

普通写法:在实际开发中,估计还是这种写法用的比较多,因为方便且可控制,不必用再多余去配置tnsnames.ora文件了。如果仅从写法上看,普通写法与别名写法唯一的区别就是,将别名所代表的 TNS Connect String 直接赋值给连接字符串中的 Data Source 参数了。

示例:

"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)));User Id=demo;Password=test;"

简易写法:我个人比较喜欢这种写法,因为它最简洁!

语法:

"Data Source=host:port/instance_name;User Id=uid;Password=pwd;"

示例:

"Data Source=127.0.0.1:1521/orcl;User Id=demo;Password=test;"

4、转义字符 & 特殊运算符

常见 Oracle 转义字符一览表

序号 字符 名称 功能 类型
1 % 百分号 在 WHERE 子句中表示 0 或多个任意字符 通配符
2 _ 下划线 在 WHERE 子句中表示一个任意字符 通配符
3 ' 单引号 表示文本、字符和日期 定界符
4 " 双引号 包裹非法字符串或让列名严格区分大小写 定界符
5 & And 用来指出一个替代变量 特殊符号
6 * 星号 在 SELECT 语句中表示 0 或多个列 特殊符号
7 双竖线 连接字符串 特殊符号
8 / 正斜线 终止 SQL 语句 特殊符号

部分转义字符的特殊用法解析

下划线:如果 LIKE 后的模式字符串是'_%',那么查询结果将会是所有非空的数据,只匹配下划线开头的数据的正确写法是'/_%' ESCAPE '/'。示例(查询以A_开头的数据):

WITH t AS(
  SELECT 'AB' f FROM DUAL UNION ALL
  SELECT 'A_' FROM DUAL UNION ALL
  SELECT 'A_B' FROM DUAL
)
SELECT t.f res FROM t WHERE t.f LIKE 'A/_%' ESCAPE '/';

单引号:要在字符串中包含单引号是比较繁琐的,基本思路就是拼接。示例:

SELECT '''' res FROM DUAL;            -- res: '
SELECT '''''' res FROM DUAL;          -- res: ''
SELECT '''1''' res FROM DUAL;         -- res: '1'
SELECT ''''||'1'||'''' res FROM DUAL; -- res: '1'

双引号:在 Oracle 中双引号主要用于处理非法格式和强制大小写。

示例一(处理非法日期格式):

SELECT TO_CHAR(fn_now,'yyyy年mm月dd日') res FROM DUAL;       -- ORA-01821: 日期格式无法识别
SELECT TO_CHAR(fn_now,'yyyy"年"mm"月"dd"日"') res FROM DUAL; -- res: 2017年01月10日

示例二(处理非法列名):

SQL> SELECT 3*7 "RES VAL" FROM DUAL;

RES VAL(注意这个列名包含空格)
----------
        21

示例三(强制列名小写):

SQL> SELECT 3*7 "res" FROM DUAL;

       res(注意这个列名是小写的)
----------
        21

And:默认情况下,如果字段中包含&符号就无法正常执行了。例如在 PL/SQL Developer 中执行SELECT 'AB&CD' res FROM DUAL;时就会被要求提供CD变量的值,假如你提供一个 1,那查询结果就会是 AB1。如果想要正常查询出含&符号的数据该怎么办呢?

如果是在命令窗口,如下执行即可:

SQL> SET DEFINE OFF;
SQL> SELECT 'AB&CD' res FROM DUAL;

RES
-----
AB&CD

如果是 PL/SQL 中,就得写成:

SELECT 'AB'||'&'||'CD' res FROM DUAL; -- 大概单个 & 符号的时候 Oracle 就不再认为它是转义字符了吧
-- 或
SELECT 'AB'||CHR(38)||'CD' res FROM DUAL; -- 38 是 & 的 ASCII 码

Oracle 中的几个特殊运算符解析

冒号(:):在 PL/SQL 程序中,冒号是很常见的,冒号主要有两个作用,分别用于给变量赋值和引用(变量或字段)。如v_name := 'scott'表示给v_name变量赋值为'scott',:NEW.staff_name表示引用表中的staff_name字段。

等于号大于号(=>):在 PL/SQL 程序中调用存储过程的时候,可通过=>符号来指定要给存储过程中的那些参数赋值。

双竖线(||):可能你会说这个符号我太熟了,不就是用来拼接字符串嘛!但在通过双竖线进行连接运算的时候,往往还伴随着隐式数据类型转换,你是否关注到了呢?

以下是进行 SQL 运算时隐式类型转换的一般规则:

  • 连接运算(||、CONCAT):数值类型和日期类型自动转换为字符类型。
  • 算数运算(+、-、*、/、TRUNC 等):字符类型自动转换为数值类型或日期类型。
  • 比较运算(>、<、=、IF、WHEN 等):字符类型自动转换为数值类型或日期类型。

5、文件类型

Oracle 对数据库文件做了详细的划分,细究起来大概有十几二十种。本人觉得没有必要全都了解,但简要了解下常见的几种文件对于 Oracle 的学习和使用还是有一定帮助的。下面将逐一介绍下 Oracle 中比较常见的 7 种文件。

参数文件(parameter file):主要用于设置数据库、用户、进程或资源的限制及调整系统性能。如记录了控制文件的位置。

控制文件(control file):数据库的核心文件,每个 Oracle 数据库最少也会有一个控制文件,且一般情况下都会有副本。控制文件是在创建数据库时自动创建的二进制文件,主要存放数据库的状态信息。其中包含数据库的名称和编号,数据文件和重做日志文件的地址等。

数据文件(data file):数据库中的数据在物理上是保存在操作系统的文件中,这些文件被称之为数据文件,其后缀名通常是.dbf。创建表空间时会自动创建新的数据文件;一个数据文件只能属于一个表空间;一个数据文件一旦被加入到某个表空间中之后,就不能再从这个表空间中移走,也不能再加入到其他表空间中。

重做日志文件(redo log file):存放重做日志信息的文件就叫做重做日志文件。用于记录数据库中发生的所有变更(如增删改),变更的每一条信息都叫做重做日志信息,以便在系统发生故障时,用它对数据库进行恢复。重做日志组中包含一个或多个日志文件,一个数据库至少有两个组,一个组至少有一个成员。重做日志的大小是固定的,写入方式是按时间顺序写入。切换(一个写满往下一个写),循环(都写满了就重新回到头组写)。

闪回日志文件(flashback log file):用于存储数据块被修改前的映像。

转存文件(dump file):供 Oracle 传统逻辑备份工具 export 和 import 使用的文件,也常译作转储文件,后缀名是.dmp

数据泵文件(data pump file):供 Oracle 新的逻辑备份工具数据泵使用的文件。

6、查看参数 & 修改参数

语法:

SHOW PARAMETER parameter_name;                             -- 查看参数
ALTER [SESSION|SYSTEM] SET parameter_name=parameter_value; -- 修改参数

说明:ALTER SESSION表示修改当前会话的参数值,会话结束,修改即失效。而ALTER SYSTEM表示修改当前实例的参数值,会对当前数据库所有用户永久生效。

常见的参数有:用来指定数据库默认语言的NLS_LANGUAGE,用来为实例选择默认优化行为的OPTIMIZER_MODE,用来设置排序的最大内存的SORT_AREA_SIZE等等。

对 Oracle 数据库性能有影响的重要初始化参数主要是以下 8 个:

7、AWR 工具

Oracle 从 10g 开始提供了一个性能分析工具——自动工作负载信息库(AWR,Automatic Workload Repository),用来取代早期的 Statspack。AWR 是 Oracle 的内置工具(需要购买许可),在安装数据库时已自动安装完毕,不用像 Statspack 那样独立安装。

AWR 报告的原理是基于 Oracle 数据库的定时镜像功能,所有的数据来源于 AWR 视图,即以DBA_HIST_开头的所有系统视图。AWR 默认采集最近 7 天的统计信息,每 1 个小时采集一次(即每隔 1 小时收集一次系统当前状态镜像,并且保存到数据库中),更早统计信息会被自动清理,这些默认选项都是可以修改的。生成 AWR 报告时,只需要指定进行分析的时间段(开始镜像编号和结束镜像编号),就可以生成该时间段的性能分析情况。

查询到当前采集配置情况:

SELECT * FROM SYS.DBA_HIST_WR_CONTROL;

查询结果:

      DBID SNAP_INTERVAL               RETENTION                    TOPNSQL
---------- --------------------------- ---------------------------- ----------
1468800140 +00000 01:00:00.0           +00007 00:00:00.0            DEFAULT

修改示例(修改为 30 分钟采集一次,保留 14 天):

BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>30,retention=>14*24*60);
END;

通过 AWR 和 AWR 报告,DBA 可以容易地获知最近数据库的活动状态,数据库的各种性能指标的变化趋势曲线,最近数据库可能存在的异常,分析数据库可能存在的性能瓶颈从而对数据库进行优化。通常可以通过 OEM(Oracle Enterprise Manager Console)平台来生成查看 AWR 报告。

8、学习方法 & 学习资料

考虑到我个人精力有限,《.Net程序员学用Oracle系列》博客写到本篇就告一段落了。接下来我将把我个人学习 Oracle 的方法和资料分享出来,以供那些有兴趣或有需要的朋友参考和进一步学习。

学习方法:简单来说,我的学习方法就是看书+看手册+看帖子+练习。

  • 为什么要看书:好的书往往讲得比较系统,能让你在短时间对某一领域的技术有个整体的把握。
  • 为什么要看手册:尤其是官方手册,往往会涉及到整个技术的方方面面,最主要的还是手册方便查询。毕竟书的纸张有限,一些你有兴趣但书上却没讲的东西可以再看看手册。
  • 为什么要看帖子:好的帖子往往会深入总结一到多个问题,毕竟书能写的有限,手册主要还是全(某些具体的问题并不一定会深入讲述)。所以如果遇到书上没讲,而手册又过于轻描淡写的问题,可以再上网查查,看看相关深入讲解的帖子。
  • 为什么要练习:光看不练假把式!所以说,如果不做练习,看的再多也白看!

好书:Oracle 是著名的老牌关系型数据库,相关的书籍也非常多。想看书的朋友可以结合自己的兴趣点,到主流的售书网站(如当当网和亚马逊)上去搜一下,看看哪些书的销量大且评价高,符合这两点的书一般都不错!

手册:Oracle 官方文档的种类繁多,对于初学者或对 Oracle 文档系统不熟悉的人来说,选择那些文档来学习是个难题。

如果要了解 Oracle 庞大的知识体系,可以参考一下《Oracle Database Readme》,这个文档详细介绍了 Oracle 10g 的知识体系所囊括的全部内容。

  • 对开发人员比较有用的官方文档:

《Oracle Database Concepts》:经典中的经典。这个文档包含了 Oracle 10g 数据库的基本概念、体系结构、事务处理、并行、Oracle 进程等所有重要知识点,最重要的是这个文档说到某个知识点的时候,在结尾处,一般会提到需要详细了解此知识点的其它文档名字和链接,可做为其它文档的索引使用。

《Oracle Database SQL Reference》:SQL,数据库开发之本,所有开发人员都应该要看的文档。

《Oracle Database Performance Tuning Guide》:这个文档,对于开发人员,建议看看前面一些与开发相关的章节,如果掌握了开发调优的知识,基本可以解决大部分开发中的调优问题了。

  • 非官方手册:

《TechOnTheNet: Oracle Tutorial》这是一个非官方的 Oracle 技术手册,它不像官方手册那么大而全,但在某些具体的 SQL 或 PL/SQL 方面,比官方手册讲得要好得多。

其它参考链接

Oracle Help Center - Search:绝大多数 Oracle 10g 的问题在这个上面都能查到相关的文档,唯一的缺点就是服务不稳定,譬如我经常会遇到查到相关文档,但点开却报 404,很多时候来回多点两次就又 OK 了。当然,如果你不能接受这一点,那么你也可以通过必应或谷歌来查,一般也能搜到。百度的话查中文资料往往比必应和谷歌的效果好,但要查国外的资料,还是必应和谷歌的效果要好一些。

本文链接http://www.cnblogs.com/hanzongze/p/oracle-ending.html
版权声明:本文为博客园博主 韩宗泽 原创,作者保留署名权!欢迎通过转载、演绎或其它传播方式来使用本文,但必须在明显位置给出作者署名和本文链接!本人初写博客,水平有限,若有不当之处,敬请批评指正,谢谢!

posted @ 2017-05-24 09:00  韩宗泽  阅读(981)  评论(0编辑  收藏  举报
回到顶部