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

1、同义词

概念

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

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

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

语法

创建同义词：

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

PUBLICPUBLICsynonym_nameSCHEMA@dblink

示例：

CREATE PUBLIC SYNONYM staff FOR demo.t_staff;

删除同义词：

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

示例：

DROP PUBLIC SYNONYM staff;

用途

schema.object_name

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)

t_course

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

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; -- 关闭行转移功能

demo.t_course

FLASHBACK TABLE demo.t_course TO SCN 2869642;

demo.t_course

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 指令清除回收站中的记录。只要被删除表的信息仍在回收站中，就可以通过闪回技术将这些表恢复。

USER_RECYCLEBINDBA_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（简易写法）。

tnsnames.ora

tnsnames.oraTNS_ADMINtnsnames.ora

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

示例：

"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 语句	特殊符号

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

'_%''/_%' 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

&SELECT 'AB&CD' res FROM DUAL;CD&

如果是在命令窗口，如下执行即可：

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 中的几个特殊运算符解析

:v_name := 'scott'v_name:NEW.staff_namestaff_name

=>=>

||

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

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

5、文件类型

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

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

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

.dbf

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

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

.dmp

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

6、查看参数 & 修改参数

语法：

SHOW PARAMETER parameter_name;                             -- 查看参数

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

ALTER SESSIONALTER SYSTEM

NLS_LANGUAGEOPTIMIZER_MODESORT_AREA_SIZE

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

7、AWR 工具

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

DBA_HIST_

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

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 了。当然，如果你不能接受这一点，那么你也可以通过必应或谷歌来查，一般也能搜到。百度的话查中文资料往往比必应和谷歌的效果好，但要查国外的资料，还是必应和谷歌的效果要好一些。