分类目录归档:Oracle

JSON in Oracle

JSON(JavaScript Object Notation,JavaScript对象表示法,读作/ˈdʒeɪsən/)是一种由道格拉斯·克罗克福特构想和设计、轻量级的数据交换语言,该语言以易于让人阅读的文字为基础,用来传输由属性值或者序列性的值组成的数据对象。尽管JSON是JavaScript的一个子集,但JSON是独立于语言的文本格式,并且采用了类似于C语言家族的一些习惯。

JSON 数据格式与语言无关。即便它源自JavaScript,但当前很多编程语言都支持 JSON 格式数据的生成和解析。

相对于传统的关系型数据库,一些基于文档存储的NoSQL非关系型数据库选择JSON作为其数据存储格式,比较出名的产品有:MongoDB。

从12.1版本开始,oracle开始支持JSON格式数据,包括事务、索引和视图等。

创建包含JSON数据的表

oracle并没有增加新的字段类型来专门存放JSON数据,而是将其存放在传统的VARCHAR2或CLOB字段中。另外IS JSON约束表明这个字段包含有效的JSON格式数据。

创建一张表,包含判断数据是否为JSON格式的约束

drop table json_test purge;

create table json_test (
    id raw(16) not null,
    document clob
    CONSTRAINT ensure_json CHECK (document IS JSON)
);

这里的IS JSON约束默认情况下都代表弱约束,如果要做严格JSON语法约束,则换成document IS JSON(STRICT)

插入测试的JSON数据

INSERT INTO json_test
  VALUES (SYS_GUID(),
          '{ "PONumber"             : 1600,
          "Reference"            : "ABULL-20140421",
          "Requestor"            : "Alexis Bull",
          "User"                 : "ABULL",
          "CostCenter"           : "A50",
          "ShippingInstructions" : { "name"   : "Alexis Bull",
                                     "Address": { "street"  : "200 Sporting Green",
                                                  "city"    : "South San Francisco",
                                                  "state"   : "CA",
                                                  "zipCode" : 99236,
                                                  "country" : "United States of America" },
                                     "Phone" : [ { "type" : "Office", "number" : "909-555-7307" },
                                                 { "type" : "Mobile", "number" : "415-555-1234" } ] },
          "Special Instructions" : null,
          "AllowPartialShipment" : false,
          "LineItems"            : [ { "ItemNumber" : 1,
                                       "Part"       : { "Description" : "One Magic Christmas",
                                                        "UnitPrice"   : 19.95,
                                                        "UPCCode"     : 13131092899 },
                                       "Quantity"   : 9.0 },
                                     { "ItemNumber" : 2,
                                       "Part"       : { "Description" : "Lethal Weapon",
                                                        "UnitPrice"   : 19.95,
                                                        "UPCCode"     : 85391628927 },
                                       "Quantity"   : 5.0 } ] }');

如果更新数据为无效的JSON数据时,则会出现错误,这里假设去掉一个大括号

update json_test
    set document = '{"User" : "xb"'
where document.User = 'ABULL';

               *
ERROR at line 3:
ORA-01747: invalid user.table.column, table.column, or column specification

查询JSON数据

虽然例子中JSON数据是存放在CLOB字段当中,但是查询的返回值总是VARCAHR2类型

查询PONumber

SQL> col PONumber for a50
SQL> select a.id,a.document.PONumber from json_test a;

ID                               PONUMBER
-------------------------------- --------------------------------------------------
9AAB58F9BDD3DA0AE0536892A8C06893 1600

查询装运须知的Phone

SQL> col ShippingInstructions for a100
SQL> select a.document.ShippingInstructions.Phone from json_test a;

SHIPPINGINSTRUCTIONS
----------------------------------------------------------------------------------------------------
[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}]

查询phone的类型

SQL> select a.document.ShippingInstructions.Phone.type from json_test a;

SHIPPINGINSTRUCTIONS
----------------------------------------------------------------------------------------------------
["Office","Mobile"]

JSON字符集

文本化的JSON数据通常采用UNICODE字符集,从这个方面来说JSON数据比XML数据使用起来更简单,这是JSON数据内部交换格式的重要组成部分。对于Oracle中管理的JSON数据而言,所有需要进行字符集转换的操作都是自动进行。

Oracle内部使用UTF-8来操作JSON数据,包括解析、查询等。如果这些数据被传入或者被输出,必须要以非UTF-8的字符集时,则会自动做字符集之间的转换。

字符集自动转换会影响性能。并且在某些情况下会有信息丢失,将传入的数据转换成UTF-8这段过程是没有丢失的,但是转换成输出时如果在输出的字符集中无法显示字符的话则会丢失数据。

当数据库的字符集为Oracle建议的AL32UTF8时,如果文本化的JSON数据以UNICODE格式储存在数据库当中时则不需要做字符集的转换。

不以文本格式存储的JSON数据,永远不需要字符集转换:因为没有字符用来转换,这表示使用BLOB类型存放的JSON数据不需要经历字符集转换。

如果JSON数据是以非Unicode格式存放,使用非Unicode的VARCHAR2或CLOB字段,则需要考虑下面的方法去避免字符集的转换

  • 对于Oracle SQL函数返回的值使用NUMBER而不是VARCHAR2,例如json_value
  • 在输入时转义特定的Unicode字符

使用JSON的SQL函数和条件

Oracle提供了SQL函数和条件去创建、查询、操作JSON数据,其中一些将JSON路径表达式中作为参数,后面跟着RETURNING子句、WRAPPER子句或者error子句。

  • Oracle SQL Conditions IS JSON and IS NOT JSON 测试数据是否是符合语法的JSON数据,通常作为约束检查
  • Oracle SQL Condition JSON_EXISTS 测试JSON数据里是否存在特定的值
  • Oracle SQL Function JSON_VALUE 从JSON数据中选择一个标量值作为SQL值
  • Oracle SQL Function JSON_QUERY 从JSON数据中选择一个或多个值作为SQL串来表示JSON值,通常用来获取JSON文本的片段,特别是JSON对象或数组
  • Oracle SQL Function JSON_TABLE 将JSON数据映射成关系型的虚拟表,也可以认为是内联的关系型视图

JSON函数和条件中的子句

子句包含returningwrapper、和错误处理。每个子句都可以使用在一个或多个SQL函数和条件json_value, json_query, json_table, is json, is not json, and json_exists.

json_value和json_query允许可选的RETURNING子句,表示通过函数返回数据的类型。
对于json_value来说,可以允许VARCAHR2或者NUMBER类型返回值,而对于json_query,只能使用VARCHAR2。

json_value和json_query允许可选的WRAPPER子句,表示通过json_query返回值或json_table相关列的格式

  • WITH WRAPPER-用一个字符值表示包含所有JSON数据的数组,数组元素顺序不要求。
  • WITHOUT WRAPPER– 用一个字符表示单个JSON对象或者数组,如果路径表达式匹配上一个标量值或者多个值则会报错。
  • WITH CONDITIONAL WRAPPER-对于0个值、单个标量值或多个值与WITH WRAPPER一样,对于单个的JSON对象或数组值,则与WITHOUT WRAPPER一样。

默认是WITHOUT WRAPPER

JSON_QUERY Wrapper子句

JSON Values WITH WRAPPER WITHOUT WRAPPER WITH CONDITIONAL WRAPPER
{“id”: 38327} (single object) [{“id”: 38327}] {“id”: 38327} {“id”: 38327}
[42, “a”, true] (single array) [[42, “a”, true]] [42, “a”, true] [42, “a”, true]
42 [42] Error (scalar) [42]
42, “a”, true [42, “a”, true] Error (multiple values) [42, “a”, true]
none [] Error (no values) []

IS JSON and IS NOT JSON

之前的例子中是将IS JSON写在了约束中,但同时也可以写在查询条件里

drop table json_test purge;

 create table json_test 
 (
 id raw(16) not null,
 data clob,
constraint pk_json primary key(id));

insert into json_test values(sys_guid(), '{"name": "xb"}');
insert into json_test values(sys_guid(), 'dba');
commit;

# JSON_VALUE 使用NULL ON ERROR,对于非JSON数据返回NULL
col name for a10
select id,json_value(a.data, '$.name') as name from json_test a;

ID                               NAME
-------------------------------- ----------
9AFA0E321B112246E0536892A8C0BFD5 xb
9AFA0E321B122246E0536892A8C0BFD5

# 只返回是否包含JSON数据的行
select id,json_value(a.data, '$.name') as name 
from json_test a
where data is json;

ID                               NAME
-------------------------------- ----------
9AFA0E321B112246E0536892A8C0BFD5 xb

select id,json_value(a.data, '$.name') as name 
from json_test a
where data is not json;

ID                               NAME
-------------------------------- ----------
9AFA0E321B122246E0536892A8C0BFD5

JSON_EXISTS

当通过点记法去查询JSON时,不可能分辨出缺失的元素和已有的元素之间的区别,除了一个NULL值以外。以前面第一个例子为例,用户ABULL有一个为NULL的 “Special Instructions”元素,并且没有ADDRESS元素,当通过点记法去查询json_test表时都是返回NULL,而无法判断ADDRESS是不存在的,语句并不会报错。

col user for a10
col PONumber for a20
col Sins for a20
col address for a20
SELECT a.document."User",
       a.document.PONumber,
       a.document."Special Instructions" AS Sins,
       a.document.address AS address
FROM   json_test a
WHERE  a.document."Special Instructions" IS NULL
AND    a.document.address IS NULL;

User       PONUMBER             SINS                 ADDRESS
---------- -------------------- -------------------- --------------------
ABULL      1600

JSON_EXISTS可以分辨出空值和缺失对象的区别

# 查询"Special Instructions"元素存在,但是值为null

SELECT a.document."User",
       a.document.PONumber,
       a.document."Special Instructions" AS Sins
FROM   json_test a
WHERE  a.document."Special Instructions" IS NULL
  and json_exists(a.document, '$."Special Instructions"' FALSE on error);

User       PONUMBER             SINS
---------- -------------------- --------------------
ABULL      1600

# 查询address元素不存在的数据

SELECT a.document."User",
       a.document.PONumber,
       a.document."Special Instructions" AS Sins
FROM   json_test a
WHERE  a.document."Special Instructions" IS NULL
  and not json_exists(a.document, '$.address' FALSE on error)

User       PONUMBER             SINS
---------- -------------------- --------------------
ABULL      1600

默认错误控制为FALSE ON ERROR,另外包含TRUE ON ERROR和ERROR ON ERROR,后者顾名思义表示不会获取任何通过JSON_EXISTS函数生成的错误。

JSON_VALUE

JSON_VALUE函数返回基于JSON路径查询JSON文档的元素

col user for a30
col costcenter for a30

select json_value(a.document, '$."User"') as "user",json_value(a.document, '$.CostCenter') as costcenter from json_test a;

user                           COSTCENTER
------------------------------ ------------------------------
ABULL                          A50

json_value只会返回标量的值,并不会返回嵌套值或数组。默认的错误处理为NULL或ERROR,所以尝试返回非标量值时则返回NULL

col ShippingInstructions for a100
select json_value(a.document, '$.ShippingInstructions') as ShippingInstructions  from json_test a;

SHIPPINGINSTRUCTIONS
----------------------------------------------------------------------------------------------------


# 自定义错误控制
SQL> select json_value(a.document, '$.ShippingInstructions' ERROR ON ERROR) as ShippingInstructions  from json_test a;
select json_value(a.document, '$.ShippingInstructions' ERROR ON ERROR) as ShippingInstructions  from json_test a
                                                                                                     *
ERROR at line 1:
ORA-40456: JSON_VALUE evaluated to non-scalar value


SQL> select a.document.ShippingInstructions from json_test a;

SHIPPINGINSTRUCTIONS
----------------------------------------------------------------------------------------------------
{"name":"Alexis Bull","Address":{"street":"200 Sporting Green","city":"South San Francisco","state":
"CA","zipCode":99236,"country":"United States of America"},"Phone":[{"type":"Office","number":"909-5
55-7307"},{"type":"Mobile","number":"415-555-1234"}]}

JSON支持布尔值,而SQL通常不支持。JSON_VALUE函数转换布尔值为字符串true、false

insert into json_test 
values(sys_guid(), '{"active": false}');
insert into json_test 
values(sys_guid(), '{"active": true}');
commit;

col active for a20
select id,json_value(a.document,'$.active') as active,json_value(a.document,'$.active' returning number) as activenum from json_test a;

ID                               ACTIVE                ACTIVENUM
-------------------------------- -------------------- ----------
9B225A54605370F8E0536892A8C0616F false                         0
9B225A54605470F8E0536892A8C0616F true                          1
9AFA0E321B132246E0536892A8C0BFD5

正如前面所讲,返回值类型可以通过RETURNING来指定,如果没有指定返回或者只指定了VARCHAR2,则默认返回VARCHAR2(4000)

col REFERENCE for a20
col REQUESTOR for a50
select json_value(a.document,'$.Reference' returning varchar2) as Reference,
json_value(a.document,'$.Requestor' returning varchar2(50)) as Requestor from json_test a;

REFERENCE            REQUESTOR
-------------------- --------------------------------------------------


ABULL-20140421       Alexis Bull

JSON_QUERY

JSON_QUERY用来返回一个JSON片段代表一个或多个值

col phone for a200
select json_query(a.document.ShippingInstructions,'$.Phone' WITH WRAPPER) as phone from json_test a; 

PHONE
-------------------------------------------------------------------------------------------------


[[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}]]

这里的WITH WRAPPER表示用中括号将片段括起来。

JSON_TABLE

json_table函数合并了JSON_VALUE, JSON_EXISTS和JSON_QUERY的功能,相对来说json_table语法更加复杂些,但是也更加有效率些。

json_table能让json数据看起来更像关系型的数据,这对于基于JSON数据创建view来说更有帮助

create or replace view v_json as
select jt.phone,jt.refrence,jt.name,jt.city,jt.quantity
from json_test,json_table(document,'$'
    COLUMNS (phone varchar2(50 CHAR) PATH '$.PONumber',
            refrence varchar2(50 CHAR) PATH '$.Reference',
            name varchar2(50 CHAR) PATH  '$.ShippingInstructions.name',
            city varchar2(50 CHAR) PATH '$.ShippingInstructions.Address.city',
            quantity varchar2(50 CHAR) PATH '$.LineItems.Quantity'
    )) jt
where id='9AFA0E321B132246E0536892A8C0BFD5';

视图已创建。

col phone for a20
col refrence for a20
col name for a20
col city for a20
col quantity for a20

select * from v_json;

PHONE                REFRENCE             NAME                 CITY                 QUANTITY
-------------------- -------------------- -------------------- -------------------- --------------------
1600                 ABULL-20140421       Alexis Bull          South San Francisco

COLUMNS子句定义了每列数据的定义和展示。也可以在字段中显示JSON格式

col phone for a20
col refrence for a20
col name for a20
col city for a20
col items for a50

select jt.*
from json_test,json_table(document,'$'
    COLUMNS (phone varchar2(50 CHAR) PATH '$.PONumber',
            refrence varchar2(50 CHAR) PATH '$.Reference',
            name varchar2(50 CHAR) PATH  '$.ShippingInstructions.name',
            city varchar2(50 CHAR) PATH '$.ShippingInstructions.Address.city',
            items varchar2(2000 CHAR) format json with wrapper PATH '$.LineItems'
    )) jt
where id='9AFA0E321B132246E0536892A8C0BFD5';

PHONE                REFRENCE             NAME                 CITY                 ITEMS
-------------------- -------------------- -------------------- -------------------- --------------------------------------------------
1600                 ABULL-20140421       Alexis Bull          South San Francisco  [[{"ItemNumber":1,"Part":{"Description":"One Magic
                                                                                     Christmas","UnitPrice":19.95,"UPCCode":1313109289
                                                                                    9},"Quantity":9.0},{"ItemNumber":2,"Part":{"Descri
                                                                                    ption":"Lethal Weapon","UnitPrice":19.95,"UPCCode"
                                                                                    :85391628927},"Quantity":5.0}]]

insufficient privileges in procedure

ORA-01031: insufficient privileges是一个非常常见的错误,就是表示没有权限。但是在存储过程中oracle处理直接赋予的权限和间接通过roles来赋予的权限时会引起误解。通过以下例子来比较两者之间的区别。

下面这个例子以execute immediate来做示范,用于在存储过程中执行动态语句。

首先创建一个用户,赋予基本的角色权限。

create user test identified by test;

grant connect,resource to test;

此时用户test包含了连接数据库、创建表和其他connect、resource角色所赋予其的权限。

在普通会话中,test能正常连接数据库和创建表

SQL> conn test/test@pdb18c
Connected.

SQL> create table test(id number);

Table created.

创建一个简单的存储过程,通过EXECUTE IMMEDAITE来动态创建表

create or replace procedure proc_test
as 
begin
    EXECUTE IMMEDIATE 'create table test1(id number)';
end;
/

执行这个存储过程

SQL> exec proc_test;
BEGIN proc_test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST.PROC_TEST", line 4
ORA-06512: at line 1

现在得到了一个ORA-01031错误,但是之前确实是有创建表的权限。这里有一个原因:在存储过程中角色是被禁用的,因此任何角色赋予用户的权限都不会生效,对应的权限必须要直接赋予用户。

直接赋予test创建表的权限,然后重新执行存储过程成功

SQL> grant create table to test;

Grant succeeded.

SQL> conn test/test@pdb18c
Connected.

SQL> exec proc_test;

PL/SQL procedure successfully completed.

18c private temporary table

oracle中临时表很常见,它是用来只在一个事务或者会话时间段内存放临时数据的表。临时表中的数据对于会话来说是私有的,每个会话只能看到和处理它自己的临时数据。

临时表

在18c以前,我们所碰到的临时表一般都成为全局临时表,都是通过CREATE TEMPORARY TABLE的方式新建,所有的会话都能看到这个临时表的字典信息。而从18c开始引入了一种新的临时表,成为私有临时表,只有会话自己能看到它的私有临时表的字典信息。

对于这两种临时表的主要区别,可以概况如下:

特性 全局临时表 私有临时表
命名规则 与永久表一样 必须以ORA$PTT_开头
表定义可见 所有会话 只有创建临时表的会话
表定义的存放 磁盘 内存
种类 基于事务(ON COMMIT DELETE ROWS)
基于会话(ON COMMIT PRESERVE ROWS)
基于事务(ON COMMIT DELETE ROWS)
基于会话(ON COMMIT PRESERVE ROWS)

这里其实还有第三种临时表,基于游标周期的临时表,主要应用于oracle为了优化性能自动创建储存在内存中的临时表,比如with as转换过程中生成的。

私有临时表特性

由于私有临时表的字典和数据只能被创建它的会话所看到,基于这个特性,在下面的场景中就很适合建私有临时表

  • 当一个应用需要临时存放数据,只插入一次读取多次,最后事务或会话结束后就删除表
  • 当一个会话处于不确定状态并且必须要给不同的事务创建不同的临时表
  • 当临时表的创建不能重新开启新的事务或者不能提交已有事务
  • 当相同用户的不同会话必须使用同一个临时表的名称
  • 当只读数据库需要临时表

例如在某个报表应用中只只用一个用户,但是应用会使用这个用户建立多个连接去生成不同的报表。每个会话都使用私有临时表来计算不同的事务,每个会话创建的临时表名称都一样。当每个事务提交时,它的临时数据就不再需要了。

私有临时表有两种不同的类型,决定了数据和表定义什么时候会被删除。这里根据提交时的处理的参数不同,可以做如下区分

ON COMMIT设置 含义
DROP DEFINITION 创建基于事务的私有临时表,在事务结束的时候表定义和数据都会被删除
PRESERVE DEFINITION 创建基于会话的私有临时表,在创建这个表的会话结束时表定义和数据才会被删除

创建私有临时表

必须要以ora$ptt_作为开头,否则会报错

SQL> create private temporary table tmp_xb (id number,name varchar2(10));
create private temporary table tmp_xb (id number,name varchar2(10))
*
ERROR at line 1:
ORA-00903: invalid table name

创建基于事务的临时表,会在事务结束的时候删除表定义

create private temporary table ora$ptt_tmp_xb (id number,name varchar2(10))
on commit drop definition;

插入数据,不中断会话

SQL> insert into ora$ptt_tmp_xb values(1,'xb');

1 row created.

SQL> select * from ora$ptt_tmp_xb;

        ID NAME
---------- ----------
         1 xb

SQL> commit;

Commit complete.

SQL> select * from ora$ptt_tmp_xb;
select * from ora$ptt_tmp_xb
              *
ERROR at line 1:
ORA-00942: table or view does not exist

而换成基于会话的时候,就仍然可以查到。只有等到重连的时候表才会被删除

create private temporary table ora$ptt_tmp_xb (id number,name varchar2(10))
on commit preserve definition;

Table created.

SQL> insert into ora$ptt_tmp_xb values(1,'xb');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from ora$ptt_tmp_xb;

        ID NAME
---------- ----------
         1 xb


# 重连
SQL> conn xb/xb@pdb18c

Connected.

SQL> select * from ora$ptt_tmp_xb;
select * from ora$ptt_tmp_xb
              *
ERROR at line 1:
ORA-00942: table or view does not exist

在默认情况下,私有临时表是存放在创建表用户的默认临时表空间下。但是在创建表的时候也可以通过指定TABLESPACE关键字来替代。

同样私有临时表也支持CTAS的创建方式

create private temporary table ora$ptt_tmp_xb 
as 
select * from user_tables;

PL/SQL中的私有临时表

由于私有临时表的定义是无法持久存在的,当在存储过程或函数等中进行编译的时候,表是不存在的。所以如果要在存储过程中使用私有临时表的话,只能通过动态sql的方式。

create or replace function p_test(id in number) return varchar2
as
 v_sql varchar2(3000);
 v_return varchar2(50);
begin
  v_sql :='create private temporary table ora$ptt_tmp_xb (id number,name varchar2(10)) on commit drop definition';
  execute immediate v_sql;
  execute immediate q'{insert into ora$ptt_tmp_xb values (1,'xb')}';
  execute immediate 'select name into :v_return from ora$ptt_tmp_xb where id =:id' into v_return using id;
  return v_return;
 end;
 /

调用这个函数,可以正常返回

set serveroutput on
begin
  dbms_output.put_line('p_test(1) = ' || p_test(1));
end;
/

p_test(1) = xb

PL/SQL procedure successfully completed.

查询视图

因为私有临时表都是存放在内存当中的,所以在数据字典里是无法查到的。但是你可以通过USER_TABLES视图来查询当前会话中的所有私有临时表。

  • DBA_PRIVATE_TEMP_TABLES : 数据库中所有私有临时表
  • USER_PRIVATE_TEMP_TABLES: 当前会话中所有私有临时表

限制

私有临时表除了有全局临时表所有的限制外,还有一些其他的:

  • 必须以PRIVATE_TEMP_TABLE_PREFIX参数设置的前缀作为临时表的开头,默认是ORA$PTT_
  • 持久对象不能直接引用私有临时表
  • 私有临时表不能包含索引、物化视图等
  • 私有临时表不能有主键或者其他索引相关的约束
  • 字段不能有默认值
  • 私有临时表不能通过DBLINK访问

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个新分区