.Net程序员学用Oracle系列(30):零碎补充、最后总结(The End)
1、同义词
概念
同义词是数据库对象的一个别名,常用于简化对象访问及提高对象访问的安全性。在使用同义词时,Oracle 数据库自动将它翻译成对应模式对象的名字。与视图类似,同义词并不占用实际存储空间,只在数据字典中保存了同义词的定义。同义词可以指向的对象有表、视图、物化视图、序列、函数、存储过程、包、同义词等。Oracle 同义词有公用同义词和专用同义词两种。普通用户如果希望创建同义词,还需要有 CREATE PUBLIC SYNONYM 这个系统权限。
公用同义词:由一个特殊的用户组 PUBLIC 所拥有,顾名思义,数据库中所有的用户都可以使用公用同义词。公用同义词往往用来标示一些比较普通的数据库对象,这些对象往往大家都需要引用。
专用同义词:由创建它的用户专用,当然,这个同义词的创建者,也可以通过授权来让别的用户访问自己的专用同义词。
语法
创建同义词:
CREATE [PUBLIC] SYNONYM [SCHEMA.]synonym_name FOR [SCHEMA.]object_name[@dblink];
说明:加PUBLIC
表示公用,不能同时指定PUBLIC
和synonym_name
的SCHEMA
,@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.ora
、TNS_ADMIN
、Oracle 主目录及 OCI 库路径之后就能根据别名登录了。在 PL/SQL Developer 登录界面上的第 3 排——数据库的备选项就是tnsnames.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
文件了。如果仅从写法上看,普通写法与别名写法唯一的区别就是,将别名所代表的 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 个:
COMPATIBLE
:用于利用新版本的最新改进。DB_BLOCK_SIZE
:对于 OLTP 为 8192,对于 DSS 为更高的值。SGA_TARGET
:自动调整大小的 SGA 组件。PGA_AGGREGATE_TARGET
:自动 PGA 管理。PROCESSES
:指定可以启动的最大进程数。SESSIONS
:指定可以在系统中创建的最大会话数,必须用于共享服务器。UNDO_MANAGEMENT
:建议使用 AUTO 模式。UNDO_TABLESPACE
:指定实例要使用的还原表空间。
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
版权声明:本文为博客园博主 韩宗泽 原创,作者保留署名权!欢迎通过转载、演绎或其它传播方式来使用本文,但必须在明显位置给出作者署名和本文链接!本人初写博客,水平有限,若有不当之处,敬请批评指正,谢谢!