分类目录归档:Oracle

expdp导出表ORA-01555错误

有同事碰到一个问题,在导出一个很小的库时有三张表都提示报错,而其他对象都导出正常。

ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number with name " " too small
ORA-22924: snapshot too old

这里的报错有点奇怪,因为库才10个g,undo表空间配置了32g,导出时基本无业务运行,按常理不会出现回滚段不足的情况。检查这三张表都包含有lob字段,怀疑是跟这有关,后来结果确实是lob字段数据有异常,处理完后即可顺利导出,现在重现这个例子。

新建测试表

drop table t_lob purge;
drop tablespace test_lob including contents and datafiles;
create tablespace test_lob datafile size 4m;

create table t_lob(a number,b clob)
    lob(b) store as (
    tablespace test_lob
    chunk 8k
    PCTVERSION 2
    disable storage in row);


begin
    for i in 1..20 loop
        insert into t_lob values(i,'this is '||i);
        commit;
    end loop;
end;
/

ALTER TABLE t_lob MODIFY LOB (b) ( PCTVERSION 1 );

begin
    for i in 1..10 loop
        update t_lob set b = i||b;
        commit;
    end loop;
end ;
/

这时候查询sql会出现案例中类似的报错

SQL> select * from t_lob as of timestamp timestamp'2019-09-19 16:31:00';
ERROR:
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old

这里就提到一个参数PCTVERSION,它表示LOB存储空间需要为旧版本的LOB页预留的空间,主要是用来维护更新前的一致性读,默认值为10%。如果这个lob字段会有大量的高并发更新,则需要设置一个较高的值。

PCTVERSION可以在创建字段的时候指定,也可以通过ALTER TABLE修改,如果设置小了,当一个会话想要去访问lob字段的旧版本但是这个旧版本又被覆盖时,那么就会出现ORA-01555的错误。PCTVERSION可以避免旧页被覆盖而去强制扩展段空间。

碰到lob字段的值显示<Value Error>时,可以通过下面的脚本来定位那些行和页出现了异常

declare
  error_1578 exception;
  error_1555 exception;
  error_22922 exception;
  pragma exception_init(error_1578,-1578);
  pragma exception_init(error_1555,-1555);
  pragma exception_init(error_22922,-22922);
  n number;
begin
  for cursor_lob in (select rowid r, <LOB_COLUMN> from <TABLE_NAME>) loop
  begin
    n:=dbms_lob.instr(cursor_lob.<LOB_COLUMN>,hextoraw('889911'));
  exception
    when error_1578 then
      insert into corrupt_lobs values (cursor_lob.r, 1578);
      commit;
    when error_1555 then
      insert into corrupt_lobs values (cursor_lob.r, 1555);
      commit;
    when error_22922 then
      insert into corrupt_lobs values (cursor_lob.r, 22922);
      commit;
    end;
  end loop;
end;
/

然后通过上面查到的rowid,对这些lob字段数据进行清空,最后成功导出

SQL> update <TABLE_NAME> set <LOB_COLUMN> = empty_blob()
     where rowid in (select corrupt_rowid from corrupt_lobs);

( for BLOB and BFILE columns use EMPTY_BLOB; for CLOB and NCLOB columns use EMPTY_CLOB )

-- Or export the table without the corrupted row, like:

% expdp system/<PASSWORD> DIRECTORY=my_dir DUMPFILE=<dump_name>.dmp LOGFILE=<logfile_name>.log TABLES=<SCHEMA_NAME>.<TABLE_NAME> QUERY=\"WHERE rowid NOT IN \(\'<corrupt_rowid>\'\)\"

oracle buffer cache

Granules

从10g开始引入了ASMM的功能,oracle会自动管理各个模块内存的使用,而granules则是oracle用于使用共享内存区域的最大内存单元。

在oracle的sga中,数据块都会读入到buffer cache这块内存区域当中,如果启用了ASSM,share pool中的部分区域会标记为KGH:NO ALLOC然后重新映射到buffer cache当中去,这是oracle会根据使用情况自动调整内存大小。

与数据缓存相关的主要有三部分:
最大的则是buffer阵列,主要用来存放从磁盘copy的数据块,其次为buffer header阵列,可以通过内存结构x$bh查看,其余则是少量的管理开销所需要的部分。

buffer header与buffer的联系非常紧密。buffer阵列的每一行与buffer header阵列中对应的每一行都有一个永久一对一的对应关系,buffer header与block header并不一样,buffer header包含一些关于block的信息,一些buffer状态的信息,和许多指向其他buffer header的指针。

Buffer池

每个granule都分配一个指定的buffer池,这表示任何一个granule都只包含同一个大小的buffer。

内存通常会被分成较小的一些块,这样会方便管理。将以块为单位的内存从一块区域(eg:data cache)移动到其他区域(eg:shared pool)是也会更容易。而管理这些块的机制最常见的就是LRU列表,但是这里要称之为工作数据集更加合适点。

每个不同的buffer池都可以分离出多个切片,主要为了减少需要当作一个整体进行维护的buffer数量。这些切片贯穿多个granule,所以如果一个buffer池由4个工作数据集(working data set)组成,每个切片则会包含granules当中1/4数量的buffer。每个buffer池都有相同数量的工作数据集,同时由于有8个buffer池,所以每个实例的工作数据集数量为8的倍数。

PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ---------------------------
db_16k_cache_size                                            big integer 0
db_2k_cache_size                                             big integer 0
db_32k_cache_size                                            big integer 0
db_4k_cache_size                                             big integer 0
db_8k_cache_size                                             big integer 0
db_cache_size                                                big integer 0
db_keep_cache_size                                           big integer 0
db_recycle_cache_size                                        big integer 0

工作数据集

工作数据集是一个非常重要的内存单元,是专门用来支持对buffer进行物理IO操作的部分。

SQL> @desc x$kcbwds
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    7      DBWR_NUM                                 NUMBER
   10      CNUM_SET                                 NUMBER

   14      SET_LATCH                                RAW(8)
   15      NXT_REPL                                 RAW(8)
   16      PRV_REPL                                 RAW(8)
   17      NXT_REPLAX                               RAW(8)
   18      PRV_REPLAX                               RAW(8)
   19      CNUM_REPL                                NUMBER
   20      ANUM_REPL                                NUMBER
-- more

DBWR_NUM 每个工作数据集都有一个相关的dbwr进程,每个dbwr进程都会对应多个工作数据集。cnum_set表示数据集里含有的buffer数量,set_latch表示保护这个数据集的cache buffer lru chain的latch地址。剩下的几个字段其实就代表了replacement列表的地址,这里总共有两条列表,一个叫replacement列表(通常被称为LRU列表但不是完全精确),一个叫辅助replacement列表,从这里也可以看出都是双向列表,因为都是NXT和PRV成对出现。
我们知道一个工作数据集切片是覆盖多个granules,每个granule包含一组buffer headers,但是在x$kcbwds视图里可以看到一对链表的端点,如果检查x$bh可以看到另一对字段(nxt_repl和prv_repl)来知道链表是如何工作的。

上图中上面的buffer header链也就是通常被称为LRU列表数据集,nxt_repl字段指向列表中最多访问的部分,而prv_repl字段则是指向列表中最少访问的部分。
这里cnum_repl是两条链表上所有pin住的buffer header数量总和,anum_repl则单独表示辅助replacement列表上pin住的buffer header数量。

LRU/TCH算法

lru算法解释参考https://en.wikipedia.org/wiki/Cache_replacement_policies

所有的buffer header都是通过一个双向链连接起来,每个buffer header都会指向一个buffer,每个buffer都会持有一个数据块的副本。现在要做的就是如果读取一个新数据块到内存,哪个buffer是可以被覆盖的。而oracle用到的算法并不是LRU算法,而是在LRU算法基础上修改后的TCH算法(touch count)

SQL> @desc x$bh
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ADDR                                     RAW(8)
    2      INDX                                     NUMBER
    3      INST_ID                                  NUMBER
    4      CON_ID                                   NUMBER
    5      HLADDR                                   RAW(8)
    6      BLSIZ                                    NUMBER
    7      NXT_HASH                                 RAW(8)
    8      PRV_HASH                                 RAW(8)
    9      NXT_REPL                                 RAW(8)
   10      PRV_REPL                                 RAW(8)

   48      US_NXT                                   RAW(8)
   49      US_PRV                                   RAW(8)
   50      WA_NXT                                   RAW(8)
   51      WA_PRV                                   RAW(8)
   52      OQ_NXT                                   RAW(8)
   53      OQ_PRV                                   RAW(8)
   54      AQ_NXT                                   RAW(8)
   55      AQ_PRV                                   RAW(8)
   56      OBJ_FLAG                                 NUMBER
   57      TCH                                      NUMBER
   58      TIM                                      NUMBER

通过x$bh结构可以看到包含了大量的PRV NXT字段对,也说明了buffer header包含了大量的指针,并且是双向的

传统的LRU算法在一个对象被访问后,则将其移动到列表的顶端,但是对于像buffer cache这样的部分来说,有大量的对象被同时使用,将这些对象一直移动则会造成大量的资源消耗并同时带来很多latch争用。为了解决这些问题,则引入了touch count的概念,改进了算法,增加了上面标红的字段,TCH计数器和时间标志TIM。每次buffer被访问时,都会更新tch和时间戳——需要从上次更新到现在超过3s。现在不会移动这个buffer header。

读取块到内存

x$bh视图里也有tch字段,x$kcbwds视图有一个中间点指针cold_hd

加载数据块到buffer

如果要读取一个全新的数据块到内存,首先需要找一个可用的buffer然后复制进去。这时要看哪些块可用,则从replacement列表的LRU端开始去查询。假设在列表的末端找到一个tch为1的buffer header,表示这个buffer从加载到内存到现在并没有被其他会话访问过,那么就是属于很少访问的冷块。还需要检查这个buffer是否正被pin住,并且其不需要被写回磁盘。假设所有的检查都通过,则会以排它模式pin住buffer header,读取数据块到buffer,更新buffer header里记录的相关信息,从列表的末端移除buffer header,并将其重链到列表的中点(v$kcbwds.cold_hd),然后unpin buffer header。

重链buffer

由于需要重新读取新块到内存,所以需要将buffer从原先的cache buffer chain上分离并且连接到新的上,也就表示需要同时获取两个cache buffer chain latch。
大致过程如下:

  • 修改x$kcbwds.prv_repl指向列表中下一个buffer header
  • 修改列表中下一个buffer header指回x$kcbwds
  • 修改当前列表中点的两个buffer header指向我的buffer header而不是它们自己
  • 修改x$kcbwds.cold_hd指向我的buffer header
  • 将原先将buffer从原先的cache buffer chain上分离并且连接到新的上
  • 将buffer与旧的对象分离并关联新的

假设tch=1的buffer已经加载了一个新的数据块,则它会被移动到中点,所以tch=4的buffer就变成了列表的最末端。对于不同的tch值的buffer来说,要读取一个新块到这个buffer时处理逻辑都有所不同。

buffer包含一个活跃块:
buffer自从加载数据块以后被访问了多次,所以不需要急着将其清出内存。oracle将buffer从列表的最LRU(最近最少使用)端分离并将其重新链接到MRU(最近最多使用)端,并将tch值减半,然后继续检查列表中下一个数据块。所以一个活跃块只有在快要被移出缓存的LRU端的情况下才会移动到MRU端,而并不是每次被使用时就会移动。

辅助LRU

当会话想搜寻buffer来读入数据块时,并不是优先从主replacement列表的LRU端去查询的,而是先搜索的辅助replacement列表的LRU端。辅助replacement列表主要存放的是那些几乎可以立即被使用的buffer header,由于这个原因,会使得在搜寻可用buffer的效率提高,它不需要消耗资源来考虑哪些是脏buffer、被pin住的buffer和其他需要考虑的问题。

寻找数据

因为buffer就是用来储存数据块的副本,所以内存里会包含大量的数据块,有些可能是重复的,同时dbwr进程也会定期将这些脏buffer写回磁盘。这时我们访问一个特定的数据块时,要如何知道是否应该重新从磁盘读取到内存,并且要如何快速有效的判断这个块已经不在内存了呢?

其实是采用hash表的方式,将一小段链接起来的buffer header附在每个bucket上,然后将这些bucket分成小组,通过一个cache buffer chains latch来保护这些小组,下面这个图表示了4个cache buffer chains latch,16个hash buckets,和23个buffer headers。

当我们有了一些buffer headers的集合后,可以通过定义合适的指针去链接相关的buffer headers来施加各种模式到这个集合上。cache buffer chains只是通过指针产生的其中一种模式,完全独立于granules,buffer pools,工作数据集,replacement列表等。

下面是需要访问一个buffer的大致过程

  • 先将数据块的地址(DBAs:表空间、file_id、block_id的结合)进行hash算法计算,找到对应的hash bucket
  • 获取保护这个hash bucket的cache buffer chains latch
  • 如果获取latch成功,则读取buffer headers去查看指定版本的block是否已经在链表中,如果找到则直接访问buffer cache里的buffer,通过pin/unpin的动作来访问
    如果没有找到block,则在buffer cache中找一个可用buffer,从当前链移出,重新链到新hash链上,释放latch然后读取数据块到这个可用buffer
  • 如果获取latch失败,则一直spin到spin_count值的次数然后重新尝试获取latch,依旧失败则sleep然后继续尝试。

12c中维护多分区

12c中可以很方便的通过简单的语句同时操作多个分区,其中包括drop和truncate多个分区,多个分区合并成一个,一个拆分成多个。

DROP/TRUNCATE多个分区

从12c开始可以用一个单独的语句就能维护多个分区,比如drop或truncate多个分区,不过如果表含有域索引,则只能一次删除或截取一个分区。执行这些操作时,分区里的数据也会被清除,如果你想保留这些数据,那么需要用MERGE PARTITIONS方式。

除此之外,在这些操作的过程中,全局索引的维护可以延迟进行,这样即使删除了分区,全局索引仍然可用,这样保证了在业务高峰期我们也能进行分区的删除操作,而索引的维护可以放在低峰时期。

删除分区

ALTER TABLE语句中可以通过DROP PARTITIONSDROP SUBPARTITIONS来对range或list的多个分区或子分区进行删除。

drop table t1 purge;
create table t1 (a number,b number,c number)
    partition by range(a)
    (partition p1 values less than (1000),
    partition p2 values less than(2000),
    partition p3 values less than(3000),
    partition p4 values less than(4000));

insert into t1 select q*1000,rownum,rownum from dba_objects, ( select rownum q from dual connect by level <= 2);
commit;

create index idx_local on t1(b) local;
create index idx_global on t1(c);

删除分区

ALTER TABLE t1
DROP PARTITIONS p1,p2;

Table altered.

检查分区和索引

SELECT TABLE_NAME,
       partition_name
FROM user_tab_partitions
WHERE TABLE_NAME='T1';

TABLE_NAME PARTITION_NAME
---------- ---------------------------------------------
T1         P3
T1         P4

SELECT index_name,
       status
FROM user_indexes
WHERE table_name='T1';

INDEX_NAME STATUS
---------- --------
IDX_LOCAL  N/A
IDX_GLOBAL UNUSABLE

这里不能删除表的所有分区,如果只剩一个分区,那么就只能删除表。
当你删除多个分区时,全局和分区索引的维护跟你删除单个分区是一样的。删除分区时分区索引也会同时删除,全局索引需要重建。

截断分区

当使用ALTER TABLE … TRUNCATE PARTITION语句来清除表分区里的数据时,可以同时操作多个分区。
截断分区与删除分区类似

drop table t1 purge;
create table t1 (a number,b number,c number)
    partition by range(a)
    (partition p1 values less than (1000),
    partition p2 values less than(2000),
    partition p3 values less than(3000),
    partition p4 values less than(4000));

insert into t1 select q*1000,rownum,rownum from dba_objects, ( select rownum q from dual connect by level <= 2);
commit;

create index idx_local on t1(b) local;
create index idx_global on t1(c);

截断分区

ALTER TABLE T1 TRUNCATE PARTITIONS P1,P2;

Table truncated.

检查分区和索引

SELECT TABLE_NAME,
       partition_name
FROM user_tab_partitions
WHERE TABLE_NAME='T1';

TABLE_NAME PARTITION_NAME
---------- ----------------------------------------
T1         P1
T1         P2
T1         P3
T1         P4


SELECT index_name,
       status
FROM user_indexes
WHERE table_name='T1';

INDEX_NAME STATUS
---------- --------
IDX_LOCAL  N/A
IDX_GLOBAL UNUSABLE

全局索引必须要进行重建,除非之前指定了UPDATE INDEXESUPDATE GLOBAL INDEXES

索引维护

drop或truncate分区时,默认是采用异步维护全局索引的方式,然而UPDATE INDEXES语句同样需要带上,是为了向下兼容。

全局索引的维护是与DROP或TRUNCATE分区的维护独立开来的,从而不需要使全局索引失效。索引的维护是采用异步的方式,可以推迟到之后的某个时间点。推迟全局索引的维护是为了在不影响索引可用性的前提下来错峰进行,这样就大大加快了drop和truncate分区的速度和减少了操作过程中资源的消耗。

为了加快删除分区的速度,只会先维护索引的字典信息。这个功能在字典中维护数据对象编号的列表,那些被删除或阶段的对象所对应的索引由于失效而被忽略掉不进行维护。

索引的维护操作能自动的通过定时任务SYS.PMO_DEFERRED_GIDX_MAINT_JOB来清理所有的全局索引。默认是在每天凌晨2点进行,当然你也可以手动运行这个job。

DBMS_PART包提供了一个维护和管理分区对象的接口,作为优先使用异步维护全局索引的结果,全局索引可以包含指向那些已经不存在数据段的条目,这些陈旧的索引行在对表或索引上进行查询、DDL、DML操作时都不会导致任何影响正确性的问题。

DBMS_PART.CLEANUP_GIDX过程会确认和清除这些全局索引来保证存储的效率和性能。

局部全局索引优化

视图USER_INDEXESUSER_IND_PARTITIONS中新增了ORPHANED_ENTRIES字段,这个字段用来表示全局索引是否包含因为延迟索引维护产生的陈旧的条目。

字段可以为以下三种值:

  • YES:索引包含单独的条目
  • NO:索引不包含任何单独的条目
  • N/A:该属性不适用-针对分区索引或非分区表上的索引

你可以强制清理那些需要维护的索引:

  • DBMS_PART.CLEANUP_GIDX:收集那些需要被清理的全局索引,用来将这些索引恢复到正常状态
exec DBMS_PART.CLEANUP_GIDX('SCHEMA','INDEXNAME');
  • ALTER INDEX REBUILD [PARTITION]: 重建整个索引或索引分区,重建完之后的索引不会包含任何陈旧的条目
ALTER INDEX INDEXNAME REBUILD;
  • ALTER INDEX COALESCE [PARTITION] CLEANUP: 清理所有索引块中孤立的条目
ALTER INDEX  INDEXNAME COALESCE CLEANUP;

合并多个分区

12c开始允许将多个分区或子分区合并到一个分区或子分区。

range分区/子分区

当合并多个range分区时,分区必须是相邻的,并按分区的边界值的升序指定。

新分区继承了原分区的最高边界

CREATE TABLE prod_list
(Prod_name VARCHAR2(30),
Prod_id   NUMBER(4))
PARTITION BY RANGE(prod_id)
(
PARTITION prod_1 VALUES LESS THAN (100),
PARTITION prod_2 VALUES LESS THAN (200),
PARTITION prod_3 VALUES LESS THAN (300),
PARTITION prod_4 VALUES LESS THAN (400),
PARTITION prod_5 VALUES LESS THAN (500),
PARTITION prod_6 VALUES LESS THAN (600),
PARTITION prod_other VALUES LESS THAN (MAXVALUE)
);

ALTER TABLE prod_list MERGE PARTITIONS prod_1,prod_2,prod_3 INTO partition old;

将prod_list分区表中的三个分区合并成了一个,新分区中包含了之前三个分区代表的最早的商品ID,表示那些还未售出的商品。

你可以不用将每个分区都一一列出来,而是直接指定最小和最大的分区,中间通过TO关键字来表示范围

ALTER TABLE prod_list MERGE PARTITIONS prod_1 TO prod_3 INTO partition old;

LIST或系统分区/子分区

对于LIST分区来说,合并的时候就不需要相邻了,因为不需要分区是有序。另外不能将range表分区使用的TO语法来指定LIST分区。

当合并多个LIST分区,生成的分区包含的数据为原分区所有值的集合的合并。
DEFAULT的list分区与其他list分区合并产生的结果依然是DEFAULT分区。

CREATE TABLE sales_list
(salesman_name VARCHAR2(30),
sales_state   VARCHAR2(20))
PARTITION BY LIST(sales_state)
(
PARTITION sales_CA VALUES('California'),
PARTITION sales_NY VALUES ('New York'),
PARTITION sales_NJ VALUES ('New Jersey'),
PARTITION sales_CT VALUES ('Connecticut'),
PARTITION sales_PA VALUES ('Pennsylvania'),
PARTITION sales_IL VALUES('Illinois'),
PARTITION sales_other VALUES(DEFAULT) 
);

ALTER TABLE sales_list MERGE PARTITIONS sales_NY, sales_NJ, sales_CT INTO PARTITION TRI_STATE;

单分区分离成多个

跟前面的合并类似,只不过操作是刚好相反

Range分区

将一个range分区prod100分离成多个分区,用于表示prod25,prod50,prod75和prod100,prod100分区源自原来的prod100分区的最高边界

drop table prod_list
CREATE TABLE prod_list
(Prod_name VARCHAR2(30),
Prod_id   NUMBER(4))
PARTITION BY RANGE(prod_id)
(
PARTITION prod100 VALUES LESS THAN (100),
PARTITION prod200 VALUES LESS THAN (200),
PARTITION prod300 VALUES LESS THAN (300),
PARTITION prodother VALUES LESS THAN (MAXVALUE)
);

alter table prod_list split partition prod100 into 
  (partition prod25 values less than (25),
   partition prod50 values less than (50),
   partition prod75 values less than (75),
   partition prod100);

换一个日期的例子,将表range_sales的sales_q1_2012分区分离成3个分区,用于表示第一季度的三个月份。

CREATE TABLE range_sales
    ( prod_id        NUMBER(6)
    , cust_id        NUMBER
    , time_id        DATE
    , quantity_sold  NUMBER(3)
    , amount_sold    NUMBER(10,2)
    ) 
PARTITION BY RANGE (time_id)
  (PARTITION SALES_Q1_2012 VALUES LESS THAN (TO_DATE('01-APR-2012','DD-MON-YYYY')),
   PARTITION SALES_Q2_2012 VALUES LESS THAN (TO_DATE('01-JUL-2012','DD-MON-YYYY')),
   PARTITION SALES_Q3_2012 VALUES LESS THAN (TO_DATE('01-OCT-2012','DD-MON-YYYY')),
   PARTITION SALES_Q4_2012 VALUES LESS THAN (MAXVALUE));

   ALTER TABLE range_sales SPLIT PARTITION sales_q1_2012 INTO
   (PARTITION sales_jan_2012 values less than (TO_DATE('01-FEB-2012','dd-MON-yyyy')),
   PARTITION sales_feb_2012 values less than (TO_DATE('01-MAR-2012','dd-MON-yyyy')),
   PARTITION sales_mar_2012);

要将range分区表拆分成N个分区,需要在要分割分区的分区范围内指定(N-1)个分区键值。新分区的上界值需要按升序排序,第N个新分区的上界为被分割的分区的上界,第N个新分区的名称和物理属性可以在拆分时指定。

List分区

customer分区表被分割成3个

CREATE TABLE sales_list
(salesman_name VARCHAR2(30),
sales_state   VARCHAR2(20))
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT) 
);

ALTER TABLE sales_list SPLIT PARTITION sales_east INTO
   (PARTITION NY values ('New York'),
    PARTITION FL values ('Florida'),
    PARTITION rest_sales_east);

将LIST分区拆分成N个分区时,需要指定(N-1)个分区键值,每个分区键值代表了(N-1)个分区需要插入的值。源分区余下的值则都被插入了第N个分区当中。前(N-1)个列值不能包含当前所有分区的值,因为这样的话第N个分区就为空了。同样新的(N-1)个分区不能包含任何当前分区不存在的值。

拆分多个分区的限制:

  • 不适用hash分区
  • 不能对索引组织表使用并行
  • 如果表是索引组织表或则表上有本地域索引,那么你只能将分区拆分成2个新分区

12c rman从备份集中active duplicate

12c以前的版本中,ACTIVE DUPLICATE进程使用生产库的进程通过网络将镜像副本发送到辅助库,这会是一个很消耗时间的任务,因为复制进程是直接按照源库大小相同比例进行复制。而在现在的12c中,数据库复制进程可以使用备份集来代替镜像副本。那么需要传输的数据库大小就会大幅度减小,因为RMAN备份的时候会跳过没有使用的块和已经提交了的回滚块等。同时也可以使用压缩或者切片的方式来提高复制传输的速度。另外新的从辅助库发起的辅助通道会用来从源库进行拉取备份集,而不是以前的从源库将备份集推送到辅助库,从而降低了源库的负载压力。

12c中,一个pull进程由备份集来决定,连接最初从源库建立,辅助实例则会通过网络接收到所需的数据库文件。恢复进程则是在辅助实例上发起,因此对源库的资源消耗更少了。

基于DUPLICATE语句,RMAN动态决定使用哪种进程,pull或者push。这样保证了现有已存在的脚本仍然有效。

  • 当你指定USING BACKUPSET,RMAN使用pull方法
  • 当你在DUPLICATE之前指定SET ENCRYPTION,RMAN自动使用pull方法并且创建备份集。这些要传输到目标端的备份集是被加密的
  • SECTION SIZE语句将数据文件切分多个小块,这样在辅助实例上并行恢复的时候可以利用多个通道的优势。为了更有效的使用并发,可以配置多个辅助通道
  • 通过USING COMPRESSED BACKUPSET语句,文件会通过压缩备份集的格式进行传输。RMAN创建备份时采取对未使用的块压缩方式,因此缩小了需要通过网络传输的备份集大小

示例

sys@ORA12C> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME       OPEN_MODE
---------- ---------- ----------
         2 PDB$SEED   READ ONLY
         3 PDB12C     READ WRITE
         4 PDB3       READ WRITE

登录两边实例

[oracle@testyum ~]$ rman target sys/oracle@ora12c auxiliary sys/oracle@ora12c_dg

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Aug 27 13:18:24 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA12C (DBID=383814323)
connected to auxiliary database: ORA12C (not mounted)

开始执行复制脚本

run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
allocate auxiliary channel aux3 type disk;
allocate auxiliary channel aux4 type disk;
DUPLICATE TARGET DATABASE TO 'ora12c'
FROM ACTIVE DATABASE
USING BACKUPSET;
}

完整日志:rman_duplicate_backupset.log

通过日志可以看出一些端倪

contents of Memory Script:
{
   sql clone "alter system set  control_files = 
  ''/u01/app/oracle/oradata/ORA12C/controlfile/o1_mf_gcm27myg_.ctl'', ''/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/controlfile/o1_mf_gcm27ndj_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name = 
 ''ORA12C'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone from service  'ora12c' primary controlfile;
   alter clone database mount;
}

这里辅助库是直接通过restore from service 的方式恢复了主库的控制文件

contents of Memory Script:
{
   sql clone 'alter database flashback off';
   set newname for clone datafile  1 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   set newname for clone datafile  7 to new;
   set newname for clone datafile  8 to new;
   set newname for clone datafile  9 to new;
   set newname for clone datafile  10 to new;
   set newname for clone datafile  11 to new;
   set newname for clone datafile  12 to new;
   set newname for clone datafile  134 to new;
   set newname for clone datafile  135 to new;
   set newname for clone datafile  136 to new;
   set newname for clone datafile  137 to new;
   restore
   from  nonsparse   from service 
 'ora12c'   clone database
   ;
   sql 'alter system archive log current';
}

这里是通restore from service 恢复了数据文件。

看到这里很容易就联想到之前说过的12c新特性12c新特性:Rman中通过网络恢复文件

所以从备份集中的在线复制其实就是采用了新特性,然后从辅助库发起连接,使用RESTORE FROM SERVICE的方式将数据文件恢复到本地。

12c不可见列

在12c中,Oracle允许隐藏某些列。这个操作可以由用户自己来控制,通过CREATE TABLE或者ALTER TABLE等方式。被隐藏的列之后也可以通过ALTER TABLE还原。一般访问表时都是无法显示出隐藏字段的,比如select * from table或者DESC TABLE

隐藏列的基本特性:

  • 一般对表的访问都不会显示隐藏列(SELECT * FROM TABLE, DESC TABLE)
  • 数据库通常会按表创建的顺序来存放字段,如果你将这张表新增了一个字段,这个新字段默认会排在表的最后。当表含有隐藏字段时,则表字段的顺序不会包含这些隐藏字段。
  • SELECT语句中只有显式的查询不可见列才能返回结果

示例

创建表

DROP TABLE t2 purge;

CREATE TABLE t2 (a int,b int invisible, c int NOT NULL);

xb@PDB12C> @desc t2;
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      A                                        NUMBER(38)
    2      C                               NOT NULL NUMBER(38)

SELECT COLUMN_NAME,
       COLUMN_ID,
       HIDDEN_COLUMN
FROM USER_TAB_COLS
WHERE TABLE_NAME ='T2';

COLUMN_NAME           COLUMN_ID HID
-------------------- ---------- ---
A                             1 NO
B                               YES
C                             2 NO

可以看到通过desc查询表结构是无法看到字段b的,不可见列B的COLUMN_ID变为空,而第三个字段C的COLUMN_ID为2。

不过这里可以通过设置选项COLINVISIBLE来让DESC命令看到不可见列

SET COLINVISIBLE ON
xb@PDB12C> @desc t2
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      A                                        NUMBER(38)
    2      C                               NOT NULL NUMBER(38)
    3      B (INVISIBLE)                            NUMBER(38)

插入部分数据

xb@PDB12C> INSERT INTO T2 values (1,2,3);
INSERT INTO T2 values (1,2,3)
            *
ERROR at line 1:
ORA-00913: too many values

xb@PDB12C> INSERT INTO T2 values (1,2);

1 row created.

xb@PDB12C> INSERT INTO T2(a,b,c) values(3,4,5);

1 row created.

xb@PDB12C> SELECT * FROM t2;

         A          C
---------- ----------
         1          2
         3          5

xb@PDB12C> SELECT A,B,C FROM t2;

         A          B          C
---------- ---------- ----------
         1                     2
         3          4          5

恢复不可见列

ALTER TABLE t2 modify(b visible);

xb@PDB12C> @desc t2;
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      A                                        NUMBER(38)
    2      C                               NOT NULL NUMBER(38)
    3      B                                        NUMBER(38)

SELECT COLUMN_NAME,
       COLUMN_ID,
       HIDDEN_COLUMN
FROM USER_TAB_COLS
WHERE TABLE_NAME ='T2';

COLUMN_NAME           COLUMN_ID HID
-------------------- ---------- ---
A                             1 NO
B                             3 NO
C                             2 NO

可以看到将字段B重新可见后,它的COLUMN_ID发生了改变,所以要注意的是如果这时你再通过INSERT INTO T2插入数据时,第二个值不是插到B,而是C,所以在写sql的时候一定注意规范,将插入的字段名称都写在语句里。

INSERT INTO t2
VALUES(6,
       7,
       8);


SELECT *
FROM t2;

         A          C          B
---------- ---------- ----------
         1          2
         3          5          4
         6          7          8

将NOT NULL字段不可见

ALTER TABLE t2 modify(c invisible);

INSERT INTO t2
VALUES(5,
       6);
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("XB"."T2"."C")

所以即使字段不可见,但是因为有非空约束,所以还是要插入非空值到列

INSERT INTO t2 (a,b,c)
VALUES(5,
       6,
       7);

1 row created.

当非空字段有默认值的时候,那插入就不会有问题了。

不可见列上的约束键

不可见列上也时可以创建主键和外键约束的,与其他字段没什么区别

CREATE TABLE test1(a number,b number invisible PRIMARY KEY);

CREATE TABLE test2(c number, d number invisible REFERENCES test1(b));

限制

  • 有些类型的表不能包含不可见列
    • 外部表
    • 聚簇表
    • 临时表
  • 用户自定义类型的属性不能是不可见

12c返回指定行数

如果对返回结果进行行数的过滤时,通常都是通过order by + rownum <的方式,对于返回中间行数的情况就需要通过嵌套分页的写法,而在其他类型的数据库比如Mysql中可以比较方便的直接通过limit的方式

SELECT *
FROM t1
ORDER BY 1 LIMIT 0,
                 10;

row limiting概念

从12c开始,oracle对这方面的功能大大的增强,引入了Row_limiting_clause语句格式,允许查询sql能限制返回的行数以及返回结果集开始的行位置。许多查询sql需要限制返回的行数和修改返回行数的起始位置,比如在返回结果集中只需要过滤top-N的数据,这个时候就只需要FETCH FIRSTOFFSET选项就能简单完成。

Row_limiting_clause语句通过指定起始偏移量,返回行数n或者返回记录数的百分比,将这些选项都接在常规sql的order by语句后面。

它主要通过以下选项来实现:

  • OFFSET
    用来指定限制返回行数开始前需要跳过的行数的整数值,如果offset为负数或未指定,则默认为0并且限制返回行数时从第一行开始。如果值为NULL或者大于等于返回行的总数,那么最终返回0行,等于是进行了截断。

  • FETCH
    指定返回多少行或者多少百分比的记录,如果不指定,则默认返回所有行,第一行从offset+1开始

  • FIRST | NEXT
    这些关键字可以互换使用,根据场景来判断

  • Rowcount | percent PERCENT
    使用Rowcount来指定返回的行数,负值或者NULL则被当做0。如果指定的值大于最大返回行数,则只返回可返回的最大行数。percent PERCENT类似,表示返回的百分比。
    如果这两个值都没指定,则默认返回一行。

  • ONLY | WITH TIES
    ONLY只返回指定行数的精确值,而WITH TIES则在除了指定行数以外,如果还有与最后一行数据同样的order by值的时候会将其他满足条件的记录都返回,如果使用WITH TIES则必须配合order by语句,否则额外的行都不会返回。

ROW limiting语句的限制:

  1. 不能用于for update语句
  2. select后面不能包含序列的CURRVAL或NEXTVAL
  3. 物化视图如果包含row limiting,则无法刷新

示例

创建一张测试表,插入部分重复数据

DROP TABLE t1 purge;

CREATE TABLE t1 (id number);

INSERT INTO t1
SELECT LEVEL
FROM dual CONNECT BY LEVEL <=5;

INSERT INTO t1
SELECT *
FROM t1;

 COMMIT;

现在t1表则包含10条数据,其中一半是重复的

xb@PDB12C> select * from t1 order by 1; 

        ID
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5

按id排序只取前5行数据

SELECT *
FROM t1
ORDER BY 1 FETCH FIRST 5 ROWS ONLY;

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

因为ID=3的记录有两条,通过with ties将与第5行id值一样的记录也展示出来

SELECT *
FROM t1
ORDER BY 1 FETCH FIRST 5 ROWS WITH ties;

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

用percent取id前30%的数据

SELECT *
FROM t1
ORDER BY 1 FETCH FIRST 30 percent ROWS ONLY;

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

percent配合with ties

SELECT *
FROM t1
ORDER BY 1 FETCH FIRST 30 percent ROWS WITH ties;

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

以前的版本如果要取中间的数据只能用嵌套分页的办法,比如取根据ID排序第3到5行的数据

# before 12c
SELECT id
FROM
  (SELECT rownum rn,
          id
   FROM
     (SELECT id
      FROM t1
      ORDER BY 1)
   WHERE rownum <=5)
WHERE rn >=3;

# after 12c
SELECT id
FROM t1
ORDER BY 1
OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY;

不根据id排序,而是根据rownum取5-7行数据

SELECT rownum,
       id
FROM t1
OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY;

    ROWNUM         ID
---------- ----------
         5          5
         6          1
         7          2

效率问题

之前做分页的时候都会碰到id between 5 and 7的写法,我们知道这是很低效的,所有都是通过rownum的嵌套方式来书写,那新的写法执行效率又如何?
对此做一个10053 trace,看下执行计划是如何

oradebug setmypid
oradebug event 10053 trace name context forever,level 12;

SELECT id
FROM xb.t1
ORDER BY 1
OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY;

oradebug event 10053 trace name context off;

sys@ORA12C> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_112949.trc

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_002"."ID" "ID"
FROM
  (SELECT "T1"."ID" "ID",
          "T1"."ID" "rowlimit_$_0",
          ROW_NUMBER() OVER (
                             ORDER BY "T1"."ID") "rowlimit_$$_rownumber"
   FROM "XB"."T1" "T1"
   WHERE 2<CASE WHEN (2>=0) THEN 2 ELSE 0 END +3) "from$_subquery$_002"
WHERE "from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE
                                                         WHEN (2>=0) THEN 2
                                                         ELSE 0
                                                     END +3
  AND "from$_subquery$_002"."rowlimit_$$_rownumber">2
ORDER BY "from$_subquery$_002"."rowlimit_$_0"

----- Plan Table -----

============
Plan Table
============
--------------------------------------------+-----------------------------------+
| Id  | Operation                 | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT          |         |       |       |     4 |           |
| 1   |  VIEW                     |         |    10 |   390 |     4 |  00:00:01 |
| 2   |   WINDOW SORT PUSHED RANK |         |    10 |   130 |     4 |  00:00:01 |
| 3   |    TABLE ACCESS FULL      | T1      |    10 |   130 |     3 |  00:00:01 |
--------------------------------------------+-----------------------------------+
Query Block Name / Object Alias(identified by operation id):
------------------------------------------------------------
 1 - SEL$1                / from$_subquery$_002@SEL$2
 2 - SEL$1
 3 - SEL$1                / T1@SEL$1
------------------------------------------------------------
Predicate Information:
----------------------

*** 2019-08-21T13:50:43.663264+08:00 (PDB12C(3))
1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN (2>=0) THEN 2 ELSE 0 END +3 AND "from$_subquery$_002"."rowlimit_$$_rownumber">2))
2 - filter(ROW_NUMBER() OVER ( ORDER BY "ID")<=CASE  WHEN (2>=0) THEN 2 ELSE 0 END +3)

可以看到实际上是对sql做了转换,先通过分析函数将伪列的值查出,然后判断OFFSET值是否符合要求,如果大于0则为2,然后返回后面3行。

通过这个大概能猜到如果是with ties的写法,那么可能就是把ROW_NUMBER分析函数换成了RANK。