Oracle Database In-Memory (12.1.0.2) Frequently Asked Question

Oracle Database In-Memory选件于美国时间6月10日已经发布,发布会视频参看:http://www.oracle.com/us/corporate/events/dbim/index.html

什么时候Oracle Database In-Memory选件能够发布?

该选件将包含在Oracle Database 12c的第一个Pacthset(12.1.0.2)中一起发布。

价格如何?

价格会在发布日的时候决定。

新的Oracle Database In-Memory选件是否会替代In-Memory Database Cache(就是TimesTen技术)选件?

当然不,完全不一样的应用场景。In-Memory Database Cache是在应用程序层通过TimesTen来管理的内存性质的数据库,这是为了让OLTP应用享受到对于存储在Oracle数据库中一部分表的超低延迟访问而设计的。

Oracle Database In-Memory选件是否会替代TimesTen?

当然不。像上面所说,TimesTen通常是部署在应用层的,可能是作为一个独立的数据库,也可能是作为在Oracle数据库前面的In-Memory Database Cache。TimesTen提供的是超低延迟数据访问,由此提供了极速的响应时间。由此受益的OLTP系统通常不会创建用于报表应用的索引,也因此通常无法从数据库层的Oracle Database In-Memory选件中获得多少性能提升。

Oracle Database In-Memory选件是否会替代Exalytics?

当然不。并不是所有客户都需要在他们的系统中拥有强力的分析能力,而Exalytics是Oracle的一体化策略分析平台。它提供了丰富的分析工具和可视化工具,以及大量的标准报表、预测分析、数据发现,是为了最优化地运行BI和EPM工作的平台,是对数据库层的补充而不是竞争。 Exalytics也不仅仅是针对Oracle Database 12c的,它可以处理多种数据源,包括Teradata、Oracle database 11g、SAP Netweaver等。

那么在Exalytics中使用的是什么关系型内存数据库引擎呢?

Exalytics使用的是TimesTen In-Memory Database for Exalytics。在TimesTen产品发展路线图中也包含着跟Oracle Database In-Memory选件相类似的列式存储技术,而且是使用的相同的底层列式处理架构。这项技术计划在2014年跟Oracle Database In-Memory选件一起在相同的时间窗口发布。实际上,TimesTen和Oracle Database In-Memory选件在Oracle公司内部是由同一个小组来开发的,共享相同的创新技术以及底层架构。

那么在Exalytics中使用的内存数据库引擎在未来会变成Oracle Database In-Memory选件吗?

没有这个计划。Oracle期望TimesTen会持续研发。

Oracle Database In-Memory选件是否会替代Exadata?

当然不。这个问题太幼稚,就不做解释了。

好吧,那到底什么是Oracle Database In-Memory选件呢?

这是一项新的技术,内置在Oracle数据库中,通过内存中的列式存储数据来获得超快的数据处理能力。

当使用Oracle Database In-Memory时,数据库的大小受限于服务器中的可用内存容量吗?

不。由用户来决定哪些对象会存在In-Memory列式缓存中。如果一张表太大了,无法完全缓存在In-Memory列存中,Oracle会尝试将尽可能多的数据保持在内存中(以列的形式),剩下的仍然存储在闪存或者磁盘中(以原来行的形式),这样的操作非常高效并且是完全透明的。 你可以有选择性的定义一部分数据库,比如将热表、热分区、经常访问的列放入内存中。这就让除了Oracle原有的那些跨越内存、闪存、磁盘的各种优化方式之外,Oracle Database In-Memory选件还能够专门为频繁访问的业务关键数据提供帮助。当然,如果数据库足够小,全部表都能使用Oracle Database In-Memory选件。

Oracle Database In-Memory选件只会对分析/报表类业务有帮助吗?

虽然确实分析/报表类业务能够从列式存储中获得最多的收益,但是其他类型业务也同样能够受益。比如通过减少需要的索引,就能够加速OLTP业务。通常在以前的混合负载业务中需要在表上创建很多的多字段索引来支撑报表类型的查询,如今In-Memory列存则对DML操作造成更少的影响,而又可以提供相同的查询性能。 减少索引还能够带来优化和管理的简便,DBA不再需要去rebuild那些碎片化的索引,也不再需要去研究为什么优化器选择了错误的索引。

从Oracle Database In-Memory功能中能够期望获得多少性能提升?

使用该选项能够为多种查询负载提供显著的性能提升。从一个非常简单的星型查询到一个非常复杂的具有多个子查询的SQL,就算所有的的表都在内存中,In-Memory列式缓存仍然会胜过普通的Buffer Cache。 比如说,In-Memory列存在查询很少列上的很多行时,会提供极高的性能,通常这类查询都是分析类的查询,在这类查询上使用In-Memory列存和使用普通缓存(Buffer Cache)相比,有超过100倍的性能提升。

当使用Oracle Database In-Memory选件的时候,我该对索引做些什么吗?

你可以保留以前的所有索引,就像以前那样运行你的应用。Oracle优化器完全能够感知到内存中的列式数据,只有当确认使用这些列式数据会有性能提升的时候才会使用到,如果优化器认为从索引访问中能够获得更好的性能,那么就会自动去读取普通缓存(Buffer Cache)中的数据,就跟以前一样。 不过,如果将一些多列索引设置为invisible,引导Oracle优化器更多地去选择列式缓存,可能会带来额外的速度提升。然后,你可以删除掉这些无用的索引,这样又能带来进一步的性能提升,因为Oracle数据库需要维护的索引更少了。

如果使用Oracle Database In-Memory选项,数据库现在的功能会受影响吗?

没有任何一项现有的Oracle数据库功能会受到Oracle Database In-Memory选项的限制;很多操作还能在性能上获得显著提升;一些原本为了提高性能所做的操作(比如创建索引)会变得不再重要;安全性、高可用性以及其它的所有功能都跟以前一模一样,完全不受影响。 当然在DML操作上会有一些额外消耗,因为In-Memory列存要时刻跟表数据保持同步,但是这部分列存是完全在内存中的,所以不需要额外的Logging操作。

Oracle Database In-Memory选项在高可用性上跟其它厂商的内存数据库相比如何?

因为In-Memory列存并没有给Oracle数据库施加任何限制,因此在高可用性上可以享受Oracle数据库完整的丰富的高可用解决方案,而其它厂商的高可用性均不太完备。

如何规划In-Memory列存所需要的内存大小?

In-Memory Area(用于保存In-Memory列存的内存区域)是SGA中的一块新的缓存池,该缓存大小由初始化参数INMEMORY_SIZE指定,如果一旦设定,最小是100MB。因为In-Memory Area是SGA的一部分,因此其最大大小受制于分配了其它所有内存池之后的SGA中的剩余空间。INMEMORY_SIZE默认是0,也就是不启用Oracle Database In-Memory选项。 由于In-Memory Area是一块静态区域,所以不会被自动内存管理算法所影响。 如果想要修改In-Memory Area的大小,需要重启数据库实例,在RAC数据库中,可以在各实例的内存中复制In-Memory列存,因此可以通过重启节点的方式来最小化对应用的影响。

在RAC环境中,是否In-Memory列存是在集群的所有节点中完全镜像的?

在RAC环境中,用户可以自行设定使用以下三种选项中的任何一种: 1. 在所有实例的内存中镜像相同的表数据。这样每个实例都会读取自己的本地内存,通常这用于较小的表。 2. 表数据分布在所有实例的内存中。这样每个实例内存中只会保存表中的一部分数据,没有冗余复制。 3. 表数据分布在所有实例的内存中,并且为高可用性保留2份镜像。这种工作机制很像ASM的条带化和冗余性。

【Oracle Database 12c New Feature】ILM – In-Database Archiving

本文介绍Oracle Database 12c中关于数据生命周期管理多个新特性中相对最简单的一个,数据库内归档(In-Database Archiving)。使用的测试表是上一篇介绍数据时间有效期管理中使用的TV表(包括表结构和测试数据),如果你还没有看过上一篇文章,可以先阅读【Oracle Database 12c New Feature】ILM – Temporal Validity

相比起数据时间有效期管理而言,数据库内归档非常简单,只有一个开关,对于一条数据,要不就是活跃的允许显示,要不就是归档掉不显示,这是由数据库管理员来人工操作的。

在设置数据库内归档之前,必须要在表级别启用该特性。如上一篇文章提到的,In-Database Archiving支持多租户架构,可以在PDB中使用。

SQL> alter table TV row archival;

Table altered.

Oracle仍然是使用隐藏列来实现这个功能的,在启用该特性以后,会自动在表上增加ORA_ARCHIVE_STATE字段,这是一个VARCHAR2(4000)的字段。

SQL> select COLUMN_NAME,DATA_TYPE,HIDDEN_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME='TV';

COLUMN_NAME          DATA_TYPE            HID
-------------------- -------------------- ---
ORA_ARCHIVE_STATE    VARCHAR2             YES
SYS_NC00005$         RAW                  YES
VALID_TIME_END       DATE                 YES
VALID_TIME_START     DATE                 YES
INSERT_TIME          DATE                 NO
VALID_TIME           NUMBER               YES

6 rows selected.

先检查一下TV表中的数据分布,一共有9个不同的时间段,前面5个都只有1条记录,后面4个则有大量测试记录。

SQL> select INSERT_TIME,count(*) from TV group by INSERT_TIME order by 1;

INSERT_TIME         COUNT(*)
----------------- ----------
20130811 09:04:30          1
20130811 09:08:27          1
20130811 09:22:30          1
20130811 09:39:40          1
20130811 09:45:22          1
20130811 09:50:44      19368
20130811 09:50:46      19368
20130811 09:50:47      19368
20130811 09:50:48      19368

9 rows selected.

尝试将所有20130811 09:50之后的记录全部设置为归档模式。直接使用UPDATE语句将ORA_ARCHIVE_STATE字段更新为任意非0的字符,0表示该记录是活跃的,任何非0字符都表示该记录被归档。

SQL> UPDATE TV SET ORA_ARCHIVE_STATE = '20' WHERE INSERT_TIME>to_date('20130811 09:50','YYYYMMDD HH24:MI');

77472 rows updated.

再次执行相同的查询语句,可以看到只存在活跃的5条记录了。

SQL> select INSERT_TIME,count(*) from TV group by INSERT_TIME order by 1;

INSERT_TIME         COUNT(*)
----------------- ----------
20130811 09:04:30          1
20130811 09:08:27          1
20130811 09:22:30          1
20130811 09:39:40          1
20130811 09:45:22          1

5 rows selected.

可以在会话级别设置即使是记录被归档,也仍然显示出来。

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

Session altered.

SQL> select INSERT_TIME,count(*) from TV group by INSERT_TIME order by 1;

INSERT_TIME         COUNT(*)
----------------- ----------
20130811 09:04:30          1
20130811 09:08:27          1
20130811 09:22:30          1
20130811 09:39:40          1
20130811 09:45:22          1
20130811 09:50:44      19368
20130811 09:50:46      19368
20130811 09:50:47      19368
20130811 09:50:48      19368

9 rows selected.

检查ORA_ARCHIVE_STATE值,可以看到所有活跃数据的ORA_ARCHIVE_STATE字段值均为0,这也是在表级别启用数据库内归档以后的默认值。

SQL> select ORA_ARCHIVE_STATE,INSERT_TIME,count(*) from TV group by ORA_ARCHIVE_STATE,INSERT_TIME order by 2;

ORA_ARCHIVE_STATE    INSERT_TIME         COUNT(*)
-------------------- ----------------- ----------
0                    20130811 09:04:30          1
0                    20130811 09:08:27          1
0                    20130811 09:22:30          1
0                    20130811 09:39:40          1
0                    20130811 09:45:22          1
20                   20130811 09:50:44      19368
20                   20130811 09:50:46      19368
20                   20130811 09:50:47      19368
20                   20130811 09:50:48      19368

9 rows selected.

将其中的一些记录的ORA_ARCHIVE_STATE字段更新为另外的非0字符。

SQL> update TV set ORA_ARCHIVE_STATE='ARCHIVING' where INSERT_TIME='20130811 09:50:48';

19368 rows updated.

SQL> select ORA_ARCHIVE_STATE,INSERT_TIME,count(*) from TV group by ORA_ARCHIVE_STATE,INSERT_TIME order by 2;

ORA_ARCHIVE_STATE    INSERT_TIME         COUNT(*)
-------------------- ----------------- ----------
0                    20130811 09:04:30          1
0                    20130811 09:08:27          1
0                    20130811 09:22:30          1
0                    20130811 09:39:40          1
0                    20130811 09:45:22          1
20                   20130811 09:50:44      19368
20                   20130811 09:50:46      19368
20                   20130811 09:50:47      19368
ARCHIVING            20130811 09:50:48      19368

在会话级别重新设置不显示归档数据,可以看到只要是ORA_ARCHIVE_STATE字段不为0的记录都不会显示。

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

Session altered.

SQL> select INSERT_TIME,count(*) from TV group by INSERT_TIME order by 1;

INSERT_TIME         COUNT(*)
----------------- ----------
20130811 09:04:30          1
20130811 09:08:27          1
20130811 09:22:30          1
20130811 09:39:40          1
20130811 09:45:22          1

性能考虑,这一点数据库内归档与时间有效性是相同的,都只是对隐藏字段进行了filter操作。即使是只显示活跃数据,也仍然需要扫描全表。这一点在真实应用中可以通过创建索引来避免全表扫描,可以参看MOS Note: Potential SQL Performance Degradation When In Database Row Archiving (Doc ID 1579790.1),也就是数据库内归档只应该在一个具备良好性能的SQL基础上对返回结果进行过滤,而不要期望归档的记录不参与扫描

SQL> select * from TV;

INSERT_TIME
-----------------
20130811 09:04:30
20130811 09:08:27
20130811 09:22:30
20130811 09:39:40
20130811 09:45:22


Execution Plan
----------------------------------------------------------
Plan hash value: 1723968289

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |  8044 |   102   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TV   |     4 |  8044 |   102   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TV"."ORA_ARCHIVE_STATE"='0')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        375  consistent gets
          0  physical reads
          0  redo size
        648  bytes sent via SQL*Net to client
        543  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

数据库内归档可以跟时间有效性管理一起配合使用。在会话级别激活时间有效性,可以看到检索不再返回任何数据。执行计划中显示filter条件融合了数据库内归档跟时间有效性两层过滤。

SQL> exec dbms_flashback_archive.enable_at_valid_time('CURRENT');

PL/SQL procedure successfully completed.

SQL> select * from tv;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1723968289

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |  6087 |   102   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TV   |     3 |  6087 |   102   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T"."ORA_ARCHIVE_STATE"='0' AND ("T"."VALID_TIME_START"
              IS NULL OR SYS_EXTRACT_UTC(INTERNAL_FUNCTION("T"."VALID_TIME_START"))<=S
              YS_EXTRACT_UTC(SYSTIMESTAMP(6))) AND ("T"."VALID_TIME_END" IS NULL OR
              SYS_EXTRACT_UTC(INTERNAL_FUNCTION("T"."VALID_TIME_END"))>SYS_EXTRACT_UTC
              (SYSTIMESTAMP(6))))


Statistics
----------------------------------------------------------
         34  recursive calls
          8  db block gets
        397  consistent gets
          0  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        532  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

将时间有效期设置为20130811 09:39:50,根据上一篇文章我们设置的1分钟有效期,只有在20130811 09:39:40插入的这条活跃记录可以被显示出来。

SQL> exec dbms_flashback_archive.enable_at_valid_time('ASOF',to_date('20130811 09:39:50','YYYYMMDD HH24:MI:SS'));

PL/SQL procedure successfully completed.

SQL> select * from TV;

INSERT_TIME
-----------------
20130811 09:39:40


Execution Plan
----------------------------------------------------------
Plan hash value: 1723968289

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |  6087 |   102   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TV   |     3 |  6087 |   102   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T"."ORA_ARCHIVE_STATE"='0' AND ("T"."VALID_TIME_START"
              IS NULL OR INTERNAL_FUNCTION("T"."VALID_TIME_START")<=TIMESTAMP'
              2013-08-11 09:39:50.000000000') AND ("T"."VALID_TIME_END" IS NULL OR
              INTERNAL_FUNCTION("T"."VALID_TIME_END")>TIMESTAMP' 2013-08-11
              09:39:50.000000000'))


Statistics
----------------------------------------------------------
         35  recursive calls
          6  db block gets
        398  consistent gets
          0  physical reads
          0  redo size
        550  bytes sent via SQL*Net to client
        543  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

结论:数据库内归档是一个Oracle利用隐藏字段实现的非常简单的功能,但是数据架构人员在规划的时候一定要考虑性能因素。

【Oracle Database 12c New Feature】ILM – Temporal Validity

ILM全称是Information Lifecycle Management,意思是信息生命周期管理,听上去很高端洋气的一个词,但是实际上几乎每个稍微大些的系统都已经在做ILM了,比如说将生产表中的数据定期插入到历史表中,并把生产表中的这些数据删除,这就是数据生命周期管理;又比如使用了分区,定期将过期的数据分区删除掉,或者置为READONLY,让RMAN不再备份,这也是数据生命周期管理。

因此ILM由来已久,只要数据存在活跃-不活跃-静止这样的周期变化,那么ILM就必不可少,Oracle Database 12c中提供了很多新功能用来方便地进行数据生命周期管理,有些功能甚至是我们期盼已久的。

本文先介绍时间有效期管理(Temporal Validity),下两篇文章会介绍数据库内归档(In-Database Archiving)以及数据热度图(Heat Map)。注意:Temporal Validity和Heat Map目前还不支持多租户架构的数据库,因此想要使用,必须是一个NON-CDB,In-Database Archiving则支持多租户架构,可以在PDB中使用。

一. 时间有效期管理(Temporal Validity)
以下简称TV,TV的功能大致上可以这样描述:在表中手动或者自动建两个时间类型的字段,一个表示有效期的开始时间,一个表示有效期的结束时间,就可以通过设置让只有在有效期内的记录才会被选择出来。

以下这个场景是我构想出来的,一张表里不断地INSERT数据,但是每条数据有效期只有1分钟,过了1分钟再查就看不见了,如果加以仔细策划,应该会是很有趣的功能。直接进入测试。

设置TV,需要使用dbms_flashback_archive包,需要该包的执行权限。

SQL> grant execute on dbms_flashback_archive to kamus;

创建测试表,period for关键字是TV新功能的关键字,valid_time是TV策略的名字,可以随便写。valid_time_start和valid_time_end字段可以不手工定义,只要指定了period for关键字,Oracle会自动创建两个不可见字段。我这里之所以手工定义开始和结束时间字段,是为了能够指定DEFAULT值。有效期开始时间valid_time_start是记录插入的当前时间,有效期结束时间valid_time_end是当前时间的后一分钟。由此定义出了一个跨度1分钟的有效期。

SQL> conn kamus/oracle
SQL> create table TV (insert_time date, 
valid_time_start date invisible default sysdate, 
valid_time_end date invisible default sysdate+1/1440, 
period for valid_time(valid_time_start,valid_time_end)
);

可以看到明确定义的INSERT_TIME字段用于演示,VALID_TIME_START和VALID_TIME_END是明确定义的不可见字段。之外,Oracle还自动创建了VALID_TIME字段,也是隐藏字段。

SQL> select COLUMN_NAME,DATA_TYPE,HIDDEN_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME='TV';

COLUMN_NAME          DATA_TYPE            HID
-------------------- -------------------- ---
VALID_TIME_END       DATE                 YES
VALID_TIME_START     DATE                 YES
INSERT_TIME          DATE                 NO
VALID_TIME           NUMBER               YES

插入一行当前时间

SQL> insert into TV values (sysdate);

1 row created.

正常情况选择,这行记录总是存在的

SQL> select * from TV;

INSERT_TIME
-----------------
20130811 09:04:30

为了应对这个新功能,在Flashback Query中新增了as of period for关键字。as of period for valid_time sysdate+1表示我们想查询在明天都还有效的数据,因为根据我们的设定,所有数据都只在插入以后1分钟内有效,因此自然无法找到在明天还有效的数据,返回零条记录。

SQL> select * from TV as of period for valid_time sysdate+1;

no rows selected

再插入一条测试数据。

SQL> insert into TV values (sysdate);

1 row created.

SQL> select * from TV;

INSERT_TIME
-----------------
20130811 09:04:30
20130811 09:08:27

我们想查询昨天就有效的数据,但是所有的数据有效期开始都是插入数据的那个时间点,自然无法找到昨天就有效的数据,返回零条记录。

SQL> select * from TV as of period for valid_time sysdate-1;

no rows selected

除了使用as of这种闪回查询的语法,还可以直接在会话级别设置有效时间点。CURRENT表示设置为当前时间点。

SQL> exec dbms_flashback_archive.enable_at_valid_time('CURRENT');

PL/SQL procedure successfully completed.

SQL> select * from TV;

no rows selected

在我的测试过程中,TV并不稳定,有时候即使设置了as of,也仍然会返回所有记录,但是过一会儿再次执行完全相同的语句,又能够返回符合条件的记录。没有详细跟踪不稳定的原因,但是猜测与cursor执行计划重用有关,毕竟Oracle的实现只是增加了一个filter条件,如果由于某种原因,之前cursor的执行计划被重用,那么很可能这个filter条件就没有加上,随之而来的也就会返回所有记录。

接下来,我们通过显示执行计划,看看Oracle是如何增加这个filter条件的。
首先禁用TV。执行计划是很正常的全表扫描。

SQL> exec dbms_flashback_archive.DISABLE_ASOF_VALID_TIME;

PL/SQL procedure successfully completed.

SQL> select count(*) from tv;

  COUNT(*)
----------
     77477


Execution Plan
----------------------------------------------------------
Plan hash value: 4129329588

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   102   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TV   | 76349 |   102   (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        333  consistent gets
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        543  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

重新在会话级别启用TV,可以看到在第二步,也就是全表扫描之后增加了一个filter,由于指定的有效期是CURRENT,因此filter条件是VALID_TIME_START 小于等于 当前时间 小于 VALID_TIME_END,也就是只要指定的有效期落在VALID_TIME_START和VALID_TIME_END之间,这条记录就可以被显示出来。同时也可以看到如果这两个限制条件为空,也都作为开放区间,也就是为空就表示不做限制。
由于测试的记录都只有1分钟有效期,因此此时已经没有一条记录可以显示了。

SQL> exec dbms_flashback_archive.enable_at_valid_time('CURRENT');

PL/SQL procedure successfully completed.

SQL> select count(*) from tv;

  COUNT(*)
----------
         0


Execution Plan
----------------------------------------------------------
Plan hash value: 4129329588

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    18 |   103   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    18 |            |          |
|*  2 |   TABLE ACCESS FULL| TV   |   287 |  5166 |   103   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("T"."VALID_TIME_START" IS NULL OR
              SYS_EXTRACT_UTC(INTERNAL_FUNCTION("T"."VALID_TIME_START"))<=SYS_EXTRACT_
              UTC(SYSTIMESTAMP(6))) AND ("T"."VALID_TIME_END" IS NULL OR
              SYS_EXTRACT_UTC(INTERNAL_FUNCTION("T"."VALID_TIME_END"))>SYS_EXTRACT_UTC
              (SYSTIMESTAMP(6))))


Statistics
----------------------------------------------------------
         33  recursive calls
          4  db block gets
        354  consistent gets
          0  physical reads
          0  redo size
        541  bytes sent via SQL*Net to client
        543  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

通过执行计划显示后台机制是一方面,另一方面我们也可以看到实际上TV是会有性能问题的,如果WHERE条件中无法使用到索引而执行了全表扫描(我这里因为没有WHERE条件所以只能是全表扫描),那么无论最终符合有效期的记录是多少,总要先进行所有记录的扫描,我们可以通过前后两次的consistent gets基本相同来获得这个结论。

更直白的说,如果作为系统设计人员不去考虑索引的构建,而仅仅是启用了TV,那么哪怕根据有效期限制,有10万记录的表只有1条会被显示出来,也仍然需要先扫描10万记录,然后再filter掉99999条,这对于程序员来说,如果不仔细阅读执行计划,就可能会造成很大的困扰,程序员会很奇怪,为什么这张表里面看上去只有1条记录,但是却要扫描那么长时间呢?

结论:数据有效期是Oracle利用隐藏字段和Flashback Query技术作的一个有趣的功能,但是数据架构人员在规划的时候一定要考虑性能因素。

【Oracle Database 12c New Feature】Advanced Security – Oracle Data Redaction

1. 什么是Oracle Data Redaction
不确认正式的中文翻译是什么,我翻译为数据改写。这是一项在12c中出现的Oracle高级安全新组件,其作用是限制SQL语句的返回结果样式,对于特定的用户可以限制某些字段显示被自动改写过的值。这是一项非常有用的安全功能,而在12c之前如果要实现相同的功能,可能需要创建特定的视图,或者在存储到数据库的时候就用加密算法进行加密。而12c的数据改写功能则在最后数据返回到客户端的前一刻将数据改写,这并不会影响到数据真实的存储。看一个简单的例子就明白了。

原本存储的数据记录如下:

SQL> select * from EMPLOYEES;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY
----------- -------------------- ------------------------- ----------- ----------
        100 Steven               King                      247-85-9056       7000
        101 Neena                Kochhar                   334-08-6578       5000

在设置完Data Redaction之后,再次执行相同的语句,可以看到有隐私性质的列-社会保障号被遮盖了,只显示最后4位,这就实现了安全的目的。

SQL> select * from kamus.employees;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY
----------- -------------------- ------------------------- ----------- ----------
        100 Steven               King                      ***-**-9056       7000
        101 Neena                Kochhar                   ***-**-6578       5000

2. 如何设置Oracle Data Redaction
设置用户的合适权限,由于Oracle Redaction对于DBA用户不起效果,因此需要将DBA角色收回。

revoke dba from kamus;
grant connect, resource, unlimited tablespace to kamus;
grant select on sys.redaction_policies to kamus;
grant select on sys.redaction_columns to kamus;
grant execute on dbms_redact to kamus;

创建测试环境,包括测试表和测试数据。

CREATE TABLE "EMPLOYEES" (
"EMPLOYEE_ID" NUMBER(6,0), 
"FIRST_NAME" VARCHAR2(20), 
"LAST_NAME" VARCHAR2(25), 
"SOCIAL_SECURITY" VARCHAR2(11), 
"SALARY" NUMBER(4,0)
)
/

Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SOCIAL_SECURITY,SALARY) values (100,'Steven','King','247-85-9056',7000);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SOCIAL_SECURITY,SALARY) values (101,'Neena','Kochhar','334-08-6578',5000);
commit;

SQL> select * from EMPLOYEES;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY
----------- -------------------- ------------------------- ----------- ----------
        100 Steven               King                      247-85-9056       7000
        101 Neena                Kochhar                   334-08-6578       5000

Oracle Redaction针对单张表的某个字段进行设置,分别通过ADD_POLICY存储过程的object_name和column_name来控制;Redaction有多种类型,第一种为FULL,也就是完全改写,对于字符类型的字段将改写为一个空格,对于数字类型的字段改写为0,对于日期类型的字段改写为2001-01-01,类型通过function_type参数来控制。

BEGIN
DBMS_REDACT.ADD_POLICY (
   object_schema          => 'KAMUS',
   object_name            => 'EMPLOYEES',
   policy_name            => 'REDACT_EMP',
   column_name            => 'SOCIAL_SECURITY',
   function_type          => DBMS_REDACT.FULL,
   expression             => '1=1',
   enable                 => TRUE
   );
END;
/

SQL> select * from employees;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY
----------- -------------------- ------------------------- ----------- ----------
        100 Steven               King                                        7000
        101 Neena                Kochhar                                     5000

Redaction的第二种类型是RANDOM,对于字符类型的字段将改写为随机字符,对于数字类型的字段改写为具有相同长度的随机数字,对于日期类型的字段改写为随机日期(永远不会跟真实日期相同)。

BEGIN
DBMS_REDACT.ALTER_POLICY (
   object_schema          => 'KAMUS',
   object_name            => 'EMPLOYEES',
   policy_name            => 'REDACT_EMP',
   column_name            => 'SOCIAL_SECURITY',
   action                 => DBMS_REDACT.MODIFY_COLUMN,
   function_type          => DBMS_REDACT.RANDOM
);
END;
/

SQL> select * from kamus.employees;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY
----------- -------------------- ------------------------- ----------- ----------
        100 Steven               King                      wa};w~ZC i&       7000
        101 Neena                Kochhar                   Q9N]##T/YAV       5000

Redaction的第三种类型是PARTIAL,将改写为按照function_parameters参数指定的格式。

BEGIN
DBMS_REDACT.ALTER_POLICY (
   object_schema          => 'KAMUS',
   object_name            => 'EMPLOYEES',
   policy_name            => 'REDACT_EMP',
   column_name            => 'SOCIAL_SECURITY',
   action                 => DBMS_REDACT.MODIFY_COLUMN,
   function_type          => DBMS_REDACT.PARTIAL,
   function_parameters    => 'VVVFVVFVVVV,VVV-VV-VVVV,*,1,5'
);
END;
/

SQL> select * from kamus.employees;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY
----------- -------------------- ------------------------- ----------- ----------
        100 Steven               King                      ***-**-9056       7000
        101 Neena                Kochhar                   ***-**-6578       5000

3. 如何精细化设置哪些用户才启用数据改写
通过expression参数设置,比如如下所示,所有不是KAMUS的用户都启用在EMPLOYEES.SOCIAL_SECURITY字段上的数据改写,而如果是KAMUS用户进行检索,则还是返回真实的数据。

BEGIN
DBMS_REDACT.ALTER_POLICY (
   object_schema          => 'KAMUS',
   object_name            => 'EMPLOYEES',
   policy_name            => 'REDACT_EMP',
   column_name            => 'SOCIAL_SECURITY',
   action                 => DBMS_REDACT.MODIFY_EXPRESSION,
   expression             => 'SYS_CONTEXT ( ''USERENV'',''SESSION_USER'' ) !=''KAMUS'''
);
END;
/

expression参数非常灵活,通过此参数可以使Data Redaction发挥巨大的作用。expression参数可选值如下图所示。
Screen Shot 2013-07-31 at 6.27.12 PM

4. 如何增加数据改写策略
通过DBMS_REDACT.ALTER_POLICY存储过程可以增加多个字段的数据改写策略,比如下例,增加在SALARY字段上的全数据改写。

BEGIN
DBMS_REDACT.ALTER_POLICY (
   object_schema          => 'KAMUS',
   object_name            => 'EMPLOYEES',
   policy_name            => 'REDACT_EMP',
   column_name            => 'SALARY',
   action                 => DBMS_REDACT.ADD_COLUMN,
   function_type          => DBMS_REDACT.FULL,
   expression             => 'SYS_CONTEXT ( ''USERENV'',''SESSION_USER'' ) !=''KAMUS'''
);
END;
/

SQL> select * from kamus.employees;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY
----------- -------------------- ------------------------- ----------- ----------
        100 Steven               King                      ***-**-9056          0
        101 Neena                Kochhar                   ***-**-6578          0

5. 数据改写到底是发生在哪一阶段,有哪些限制

--where条件中的字段并不会受数据改写影响,可以看到即使最后都是显示SALARY=0,但是where条件还是正常执行并筛选出正确结果的。
SQL> select * from kamus.employees where SALARY>5000;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY
----------- -------------------- ------------------------- ----------- ----------
        100 Steven               King                      ***-**-9056          0

SQL> select * from kamus.employees where SALARY<5000;

no rows selected

SQL> select * from kamus.employees where SALARY=5000;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY
----------- -------------------- ------------------------- ----------- ----------
        101 Neena                Kochhar                   ***-**-6578          0

--在启用了数据改写策略的表上无法进行全表的CTAS操作。
SQL> create table t as select * from kamus.employees;
create table t as select * from kamus.employees
                         *
ERROR at line 1:
ORA-28081: Insufficient privileges - the command references a redacted object.

--如果CTAS操作中的字段上没有启用数据改写策略,CTAS可以正常进行。
SQL> create table t as select EMPLOYEE_ID,FIRST_NAME from kamus.employees;

Table created.

SQL> drop table t;

Table dropped.

--如果包含了启用数据改写策略的列,则会报ORA-28081错误。
SQL> create table t as select EMPLOYEE_ID,FIRST_NAME,SALARY from kamus.employees;
create table t as select EMPLOYEE_ID,FIRST_NAME,SALARY from kamus.employees
                                                *
ERROR at line 1:
ORA-28081: Insufficient privileges - the command references a redacted object.

【Oracle Database 12c New Feature】How to Learn Oracle (12c New Feature) from Error

这篇文章也许并不太牵涉什么技术点,只是描述一下我自己在学习的时候大概是什么状态,什么思路,因为自认自己的学习能力还不错,因此也期望这样的学习方法对其他人会有帮助。看这篇文章的时候,你可以同步地想一想如果是你遇到这样的错误,你会怎么处理,怎么发散,怎么研究?

Oracle Database 12c前几天正式发布了,如果学习一个新版本的数据库?我通常是从New Features Guide文档看起,先通览文档的目录,遇到感兴趣的新功能点,就开始做实验来验证这个新功能。当然,这之前需要先把新版本的数据库安装好,先把新版本的全部文档下载到本地,这样即使你坐在飞机上也可以有文档可查。

这次我的计划是实验一下Identity类型的字段,这个字段可以用来作主键,会自动递增,这种类型的字段在SQL Server中早就存在,但是Oracle直到12c才推出这个功能。

通常我不会用sys用户进行任何实验(除非是验证sysdba的新功能),因此总是会先创建一个我自己的dba用户。
在12c中创建这个用户首先就遇到了错误。

SQL> create user kamus identified by oracle default tablespace users;
create user kamus identified by oracle default tablespace users
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

对于一个不熟悉的错误,第一件事情不是去Google(如果你说Baidu那就更幼稚了),而是用oerr实用程序来看看Oracle自己对这个错误是怎么解释的。为什么我喜欢非Windows环境中的Oracle?oerr的存在也是很大一个原因。

[oracle@dbserver-oel ~]$ oerr ora 65096
65096, 00000, "invalid common user or role name"
// *Cause:  An attempt was made to create a common user or role with a name
//          that wass not valid for common users or roles.  In addition to
//          the usual rules for user and role names, common user and role
//          names must start with C## or c## and consist only of ASCII
//          characters.
// *Action: Specify a valid common user or role name.
//

错误信息的解析非常明确地告知“试图创建一个通用用户,必需要用C##或者c##开头”,这时候心里会有疑问,什么是common user?但是我通常不会先急着去翻文档,而是先把手头的事情做完,也就是先把用户创建上。

SQL> create user c##kamus identified by oracle default tablespace users;

User created.

SQL> grant dba to c##kamus;

Grant succeeded.

SQL> select USERNAME,COMMON,ORACLE_MAINTAINED from dba_users;

USERNAME                       COM O
------------------------------ --- -
AUDSYS                         YES Y
GSMUSER                        YES Y
SPATIAL_WFS_ADMIN_USR          YES Y
SPATIAL_CSW_ADMIN_USR          YES Y
APEX_PUBLIC_USER               YES Y
SYSDG                          YES Y
DIP                            YES Y
SYSBACKUP                      YES Y
MDDATA                         YES Y
GSMCATUSER                     YES Y
C##KAMUS                       YES N
SYSKM                          YES Y
XS$NULL                        YES Y
OJVMSYS                        YES Y
ORACLE_OCM                     YES Y
OLAPSYS                        YES Y
SI_INFORMTN_SCHEMA             YES Y
DVSYS                          YES Y
ORDPLUGINS                     YES Y
XDB                            YES Y
ANONYMOUS                      YES Y
CTXSYS                         YES Y
ORDDATA                        YES Y
GSMADMIN_INTERNAL              YES Y
APPQOSSYS                      YES Y
APEX_040200                    YES Y
WMSYS                          YES Y
DBSNMP                         YES Y
ORDSYS                         YES Y
MDSYS                          YES Y
DVF                            YES Y
FLOWS_FILES                    YES Y
SYS                            YES Y
SYSTEM                         YES Y
OUTLN                          YES Y
LBACSYS                        YES Y

36 rows selected.

创建C##KAMUS用户成功之后,再返回去解决心中的疑问,什么是common user?在联机文档的左上角搜索关键字common user,会得到如下的结果。
Oracle Online Doc Search Result

通常我会先浏览Concept,如果看完觉得心中疑问已经解决,就会返回继续作之前的实验,不会再浏览其他的链接;如果想要查询怎么做,比如说如何创建Common User,才会继续去看Task部分。这样的好处是可以保持专注而不至于被过多文档分心

但是由于Common User这个概念几乎是崭新的,所以我很有兴趣继续探索一下,跟Common User相对的Local User该如何创建。继续去看Task当然是个方法,但是这里我选择的是直接去看SQL Language Reference,因为我们知道一定是在Create User语法里面会有不同的定义,进入Create User语法页面,直接搜索common user,就可以看到如下这段话。

CONTAINER Clause

To create a local user in a pluggable database (PDB), ensure that the current container is that PDB and specify CONTAINER = CURRENT. To create a common user, ensure that the current container is the root and specify CONTAINER = ALL. The name of the common user must begin with C## or c##. If you omit this clause and the current container is a PDB, then CONTAINER = CURRENT is the default. If you omit this clause and the current container is the root, then CONTAINER = ALL is the default.

也就是说我们一定要先登录进一个PDB,才可以创建本地用户,那么如何知道现在的sqlplus是登录进了哪个DB呢?这个疑问其实是一个很简单的联想,既然需要去一个地方,那么一定有方法知道我现在在什么地方,通过简单地查询文档,可以得知以下的方法。现在确实在CDB中。

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')
-----------------------------------------------
CDB$ROOT

dbca建库的时候,有一个新选项是同时创建PDB,我勾选过(对于dbca中出现的新选项,如果不是条件不允许,我都会选中进行测试),创建了名字为pdbtest的PDB,那么现在我想尝试登录这个PDB,去创建一个Local User。如何登录PDB?Administrator’s Guide中有专门新的一个章节“Part VI Managing a Multitenant Environment”来描述如何管理多租户环境,浏览目录就可以直接找到“Connecting to a PDB with SQL*Plus”这部分,如下所示。

You can use the following techniques to connect to a PDB with the SQL*Plus CONNECT command:

Database connection using easy connect
Database connection using a net service name

那尝试直接使用easy connect来登录PDB。

$ sqlplus sys/oracle@127.0.0.1:15210/pdbtest as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 6 21:44:42 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>  SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')
------------------------------------------
PDBTEST

Elapsed: 00:00:00.01

SQL>  select NAME,PDB from dba_services;
 select NAME,PDB from dba_services
                      *
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only

PDB没有Open?尝试打开。无法使用startup命令。这是显而易见的。 原因是我使用了旧版本的SQL*Plus(如上所示是10.2.0.4.0)连接到12c数据库的PDB中,某些新特性不被支持。

SQL> startup
ORA-24543: instance startup or shutdown not allowed in pluggable database

使用12c自带的SQL*Plus登录,就可以使用startup命令将PDB打开,使用SQL*Plus管理PDB的详细命令可以参看文档描述

SQL> show user
USER is "SYS"
SQL> startup
Pluggable Database opened.
SQL>
SQL> show con_name

CON_NAME
------------------------------
PDBTEST

或者可以使用如下语句打开PDB。

SQL> ALTER PLUGGABLE DATABASE OPEN;

Operation 227 succeeded.

Elapsed: 00:00:02.44

SQL> SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;

NAME			       OPEN_MODE  RES OPEN_TIME
------------------------------ ---------- --- ------------------------------------------------
PDBTEST 		       READ WRITE NO  06-JUL-13 09.48.57.260 PM

到此,可以创建Local User了。

SQL> create user kamus identified by oracle;

User created.

Elapsed: 00:00:00.10
SQL> grant dba to kamus;

Grant succeeded.

Elapsed: 00:00:00.03

那么在一个PDB中可以看到多少用户呢?可以看到CDB中的用户吗?这又是一个简单的联想,学习的过程其实是一个发散再收缩的循环。看来不可以,只能看到自己的用户,当然这里有很多Common User。可以看到即使是在PDB中,cdb_视图也是可以使用的。

SQL> select CON_ID,count(*) from cdb_users group by con_id;

    CON_ID   COUNT(*)
---------- ----------
	 3	   38

Elapsed: 00:00:00.03

那么再回到CDB中看一下,会是什么情况?可以看到所有Container中的用户都可以查询到。

SQL> select CON_ID,count(*) from cdb_users group by con_id;

    CON_ID   COUNT(*)
---------- ----------
         1         36
         2         35
         3         38

Elapsed: 00:00:00.08

终于,我可以回到最开始的实验目标上去了,在PDB中创建了T1表,id列为Identity类型。

SQL> CREATE TABLE t1 (id NUMBER GENERATED AS IDENTITY);

Table created.

Elapsed: 00:00:00.22

根据文档描述,Identity类型仍然是通过Sequence来实现的,那么应该是自动创建了一个Sequence,果然如此。在你学习的过程中会多此一步来查询一下Sequence视图吗?

SQL> select SEQUENCE_NAME from user_sequences;

SEQUENCE_NAME
----------------------------------------------------------------------------------------------
ISEQ$$_91620

Elapsed: 00:00:00.00

默认创建的Sequence,CACHE_SIZE是20,开始值是1,这都跟单独创建的Sequence默认值一样。

SQL> select * from user_sequences;

SEQUENCE_NAME															  MIN_VALUE  MAX_VALUE
-------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
INCREMENT_BY C O CACHE_SIZE LAST_NUMBER PARTITION_COUNT S K
------------ - - ---------- ----------- --------------- - -
ISEQ$$_91620																  1 1.0000E+28
	   1 N N	 20	      1 		N N


Elapsed: 00:00:00.00

插入一条数据试一下,报错报错还是报错。所以如果是generated always的identity列,如果只有这一列,没法插入数据。

SQL> insert into t1 values('');
insert into t1 values('')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


Elapsed: 00:00:00.01

SQL> insert into t1 values(ISEQ$$_91620.nextval);
insert into t1 values(ISEQ$$_91620.nextval)
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


Elapsed: 00:00:00.00

SQL> insert into t1 values(null);
insert into t1 values(null)
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


Elapsed: 00:00:00.00

换GENERATED BY DEFAULT ON NULL 类型试一下,Wait,如果删除了表,对应的序列会自动删除吗?理论上应该会,当然还是要测试一下。

SQL> drop table t1;

Table dropped.

Elapsed: 00:00:00.19

序列还在?

SQL> select * from user_sequences;

SEQUENCE_NAME															  MIN_VALUE  MAX_VALUE
-------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
INCREMENT_BY C O CACHE_SIZE LAST_NUMBER PARTITION_COUNT S K
------------ - - ---------- ----------- --------------- - -
ISEQ$$_91620																  1 1.0000E+28
	   1 N N	 20	      1 		N N


Elapsed: 00:00:00.00

再建一张表。

SQL> CREATE TABLE t2 (id NUMBER GENERATED BY DEFAULT AS IDENTITY);

Table created.

Elapsed: 00:00:00.01

现在是2个序列了。

SQL> select * from user_sequences;

SEQUENCE_NAME															  MIN_VALUE  MAX_VALUE
-------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
INCREMENT_BY C O CACHE_SIZE LAST_NUMBER PARTITION_COUNT S K
------------ - - ---------- ----------- --------------- - -
ISEQ$$_91620																  1 1.0000E+28
	   1 N N	 20	      1 		N N

ISEQ$$_91622																  1 1.0000E+28
	   1 N N	 20	      1 		N N


Elapsed: 00:00:00.00

写完整的Drop语句试一下。

SQL> drop table t2 cascade constraint purge;

Table dropped.

Elapsed: 00:00:00.12

后创建的序列已经被自动删除了,之前创建的还在。

SQL> select * from user_sequences;

SEQUENCE_NAME															  MIN_VALUE  MAX_VALUE
-------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
INCREMENT_BY C O CACHE_SIZE LAST_NUMBER PARTITION_COUNT S K
------------ - - ---------- ----------- --------------- - -
ISEQ$$_91620																  1 1.0000E+28
	   1 N N	 20	      1 		N N


Elapsed: 00:00:00.00

两者的不同应该是purge,如果被删除的表还在回收站中,序列是会保留的,Make sense,因为表还可能从回收站里面再restore回来,需要保证序列仍然有效。那么清空回收站实验一下。

SQL> purge recyclebin;

Recyclebin purged.

Elapsed: 00:00:00.04

果然,序列也相应地被删除了。

SQL> select * from user_sequences;

no rows selected

Elapsed: 00:00:00.00

再回到正题,创建T3表,插入一条数据。

SQL> CREATE TABLE t3 (id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY);

Table created.

Elapsed: 00:00:00.01
SQL> insert into t3 values(null);

1 row created.

Elapsed: 00:00:00.01

序列的LAST_NUMBER已经增加为21。

SQL> select * from user_sequences;

SEQUENCE_NAME															  MIN_VALUE  MAX_VALUE
-------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
INCREMENT_BY C O CACHE_SIZE LAST_NUMBER PARTITION_COUNT S K
------------ - - ---------- ----------- --------------- - -
ISEQ$$_91624																  1 1.0000E+28
	   1 N N	 20	     21 		N N

后台如何操作的?使用10046 trace,再插入几条数据。

SQL> insert into t3 values(null);

1 row created.

SQL> insert into t3 values(null);

1 row created.

SQL> select * from t3;

	ID
----------
	 1
	 2
	 3

查看10046 trace的结果。可以看到执行计划中直接调用了SEQUENCE,就跟之前插入记录的时候明确指定SEQ.NEXTVAL一样。其实Oracle的实现方法非常简单,这一列其实就是Number类型,然后将这一列的Default值设置为”KAMUS”.”ISEQ$$_91624″.nextval,仅此而已。

--tkprof 10046 trace
insert into t3
values
(null)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          1          3           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 104
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=90 us)
         1          1          1   SEQUENCE  ISEQ$$_91624 (cr=0 pr=0 pw=0 time=14 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        5.28          5.28
********************************************************************************

到此为止,可以休息一下了,从ORA-65096开始大概花费了1个多小时的时间,我学习到了:
1. 什么是Common User,什么是Local User?
2. 如何查询现在的环境是CDB还是某个PDB?
3. 如何登录PDB?
4. 如何启动PDB?
5. PDB和CDB中视图看到的内容有怎样的不同?
6. 如何创建Identity类型的列?
7. 删除表以后,对应的Sequence如何处理?
8. Oracle后台对于Identity列是如何处理的?

你是不是也是这样学习的呢?

Update@2013-07-15
谢谢留言中Asher的建议,增加如下测试。
使用DBMS_METADATA.GET_DDL获取到的DDL信息,已经是符合12c语法的样式了,显示出了Sequence的具体信息。

SQL> select dbms_metadata.GET_DDL('TABLE','T3') from dual;

DBMS_METADATA.GET_DDL('TABLE','T3')
--------------------------------------------------------------------------------

  CREATE TABLE "KAMUS"."T3"
   (	"ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 99
99999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE
  NOT NULL ENABLE,
	"COMMENTS" VARCHAR2(100)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

系统自动产生的序列无法手工修改属性。

SQL> alter sequence "ISEQ$$_91624" INCREMENT BY 10;
alter sequence "ISEQ$$_91624" INCREMENT BY 10
*
ERROR at line 1:
ORA-32793: cannot alter a system-generated sequence

SQL> host oerr ora 32793
32793,0000, "cannot alter a system-generated sequence"
// *Cause:  An attempt was made to alter a system-generated sequence.
// *Action: A system-generated sequence, such as one created for an
//          identity column, cannot be altered.

系统自动产生的序列也不允许删除。

SQL> drop sequence "ISEQ$$_91624";
drop sequence "ISEQ$$_91624"
              *
ERROR at line 1:
ORA-32794: cannot drop a system-generated sequence

SQL> host oerr ora 32794
32794,0000, "cannot drop a system-generated sequence"
// *Cause:  An attempt was made to drop a system-generated sequence.
// *Action: A system-generated sequence, such as one created for an
//          identity column, cannot be dropped.

在11gR2中错误信息编号在ORA-32790和ORA-32800之间是空白,而12c使用了这其间的8个错误号作为新特性的报错。

ORA-32791: prebuilt table managed column cannot have a default on null
Cause: An attempt was made to create a materialized view on a prebuilt table that has a managed column with a default on null expression.
Action: Either remove the default on null property, or do not include the column in the materialized view definition.

ORA-32792: prebuilt table managed column cannot be an identity column
Cause: An attempt was made to create a materialized view on a prebuilt table that has a managed column that is an identity column.
Action: Either remove the identity property, or do not include the column in the materialized view definition.

ORA-32793: cannot alter a system-generated sequence
Cause: An attempt was made to alter a system-generated sequence.
Action: A system-generated sequence, such as one created for an identity column, cannot be altered.

ORA-32794: cannot drop a system-generated sequence
Cause: An attempt was made to drop a system-generated sequence.
Action: A system-generated sequence, such as one created for an identity column, cannot be dropped.

ORA-32795: cannot insert into a generated always identity column
Cause: An attempt was made to insert a value into an identity column created with GENERATED ALWAYS keywords.
Action: A generated always identity column cannot be directly inserted. Instead, the associated sequence generator must provide the value.

ORA-32796: cannot update a generated always identity column
Cause: An attempt was made to update an identity column created with GENERATED ALWAYS keywords.
Action: A generated always identity column cannot be directly updated.

ORA-32797: identity column sequence mismatch in ALTER TABLE EXCHANGE PARTITION
Cause: The two tables specified in the EXCHANGE have identity columns with sequences that are neither both increasing nor decreasing.
Action: Ensure that the identity columns have sequences with INCREMENT BY having the same sign.

ORA-32798: cannot use ANSI RIGHT or FULL outer join with a left correlation
Cause: An attempt was made to use a lateral view with a left correlation to the first operand of an ANSI RIGHT or FULL outer join.
Action: Rewrite the query without the left correlation.

【Oracle Database 12c New Feature】What’s New in DBCA of Oracle Database 12c

Oracle Database 12c已经正式发布,在创建数据库的dbca程序界面上与以往的版本有哪些不同?

1. 出现了Manage Pluggable Databases的选项。 PDB是Oracle12c大力宣传的一个新特性,参看文档
Step 1

2. 如果安装的机器上已经有11g GI,那么会出现警告,提升要么升级到12.1,要么新创建的数据库将无法假如到GI的管理中。

Step 2

3. 创建数据库的时候,可以直接选择是CDB还是Non-CDB,也可以同时创建多个PDB。
Step 3

4. 更多的权限细分group,但是在我遇见的生产环境中,很少有使用到这样细分的权限。通常都是一个dba组搞定全部。
Step 4

5. 以往的版本在最后一步真正开始创建数据库的时候是弹出一个新窗口,12c嵌入到dbca的整体界面中。最人性化的改变是可以直接查看alert日志,并且内容是自动滚动的。不再需要登录到服务器上tail -f了。
Step 5