摘要：关于 Oracle 20c 区块链表（Blockchain Table），很多朋友表达了强烈的关注，通过一些基本测试，我们能够揭示关于区块链表的工作原理。

关于 Oracle 20c 区块链表（Blockchain Table），很多朋友表达了强烈的关注，通过一些基本测试，我们能够揭示关于区块链表的工作原理。

以下是一些基本的规则：

1.空的区块链表，可以删除；

2.区块链表不能建立在Root容器中；

3.NO DROP/DELETE 选项定义了区块链表的删除特性和保留期；

4.在保留期内，有数据的区块链表不能被删除；

5.包含保护期内都区块链表的用户不能递归删除；

6.可以通过删除数据库，清除区块链表；

7.INSERT操作不会彼此阻塞，HASH 值是提交时计算的；

以下是简单的测试输出：

[oracle@enmotech ~]$ sqlplus / as sysdba

SQL*Plus: Release 20.0.0.0.0 - Production on Tue Feb 18 08:47:20 2020

Version 20.2.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  788529032 bytes

Fixed Size     9572232 bytes

Variable Size   603979776 bytes

Database Buffers   171966464 bytes

Redo Buffers     3010560 bytes

Database mounted.

Database opened.

SQL> show pdbs;

    CON_ID CON_NAME   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED   READ ONLY  NO

首先 区块链表 不允许建立在 root 容器中：

SQL> create blockchain table enmotech( 

2    id number primary key,

3    name varchar2(20),

4    join_date date)

5    NO DROP UNTIL 365 DAYS IDLE

6    NO DELETE LOCKED

7  HASHING USING "SHA2_512" VERSION "v1";

create blockchain table enmotech(

*

ERROR at line 1:

ORA-05729: blockchain table cannot be created in root container

创建一个独立的PDB，用于测试：

SQL> SELECT NAME FROM V$DATAFILE;

NAME

-------------------------------------------------------------

/oradata/ENMOTECH/system01.dbf

/oradata/ENMOTECH/pdbseed/system01.dbf

/oradata/ENMOTECH/sysaux01.dbf

/oradata/ENMOTECH/pdbseed/sysaux01.dbf

/oradata/ENMOTECH/undotbs01.dbf

/oradata/ENMOTECH/pdbseed/undotbs01.dbf

/oradata/ENMOTECH/users01.dbf

7 rows selected.

SQL> create pluggable database enmo admin user "ENMOTECH" identified by "ENMOTECH"

file_name_convert = ('/oradata/ENMOTECH/pdbseed/','/oradata/ENMOTECH/pdbseed/enmo/');  2  

Pluggable database created.

打开PDB，创建一个独立的测试用户：

SQL> alter pluggable database enmo open;

Pluggable database altered.

SQL> alter session set container=ENMO;

Session altered.

SQL> create user eygle identified by eygle;

User created.

SQL> grant connect,resource,dba to eygle;

Grant succeeded.

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/oradata/ENMOTECH/pdbseed/enmo/system01.dbf

/oradata/ENMOTECH/pdbseed/enmo/sysaux01.dbf

/oradata/ENMOTECH/pdbseed/enmo/undotbs01.dbf

在这个用户下建立区块链表，注意，空表是可以随意删除的：

SQL> alter session set current_schema=EYGLE;

Session altered.

SQL> create blockchain table enmotech( 

2    id number primary key,

3    name varchar2(20),

4    join_date date)

5    NO DROP UNTIL 365 DAYS IDLE

6    NO DELETE LOCKED

7  HASHING USING "SHA2_512" VERSION "v1";  

Table created.

SQL> drop table enmotech;

Table dropped.

但是一旦表中创建了数据，则不能再删除表：

SQL> create blockchain table enmotech(

  2  id      number primary key,

  3  name      varchar2(20),

  4  join_date      date)

  5  NO DROP UNTIL 365 DAYS IDLE

  6  NO DELETE LOCKED

  7  HASHING USING "SHA2_512" VERSION "v1";

Table created.

SQL> insert into enmotech values(1,'EYGLE',to_date('2010-08-08','yyyy-mm-dd'));

1 row created.

SQL> drop table enmotech;

drop table enmotech

           *

ERROR at line 1:

ORA-05723: drop blockchain table ENMOTECH not allowed

在插入数据时，HASH值是空的，提交时才会计算、链接，所以INSERT操作不会彼此阻塞：

SQL> insert into enmotech values(5,'ROLLINGPIG',sysdate);

1 row created.

SQL> select name,substr(ORABCTAB_HASH$,1,10) from enmotech;



NAME                 SUBSTR(ORABCTAB_HASH$,1,10)

-------------------- ----------------------------------------

EYGLE                6D22ABDFB8

KAMUS                02DFFBE75F

ORA-600              9DAB8D56BF

YANGTINGKUN          080BF7EEF3

ROLLINGPIG

同样，删除用户也不被允许：

ERROR at line 1:SQL> alter session set current_schema=SYS;

Session altered.

SQL> drop user eygle;

drop user eygle

*

ORA-01922: CASCADE must be specified to drop 'EYGLE'

SQL> drop user eygle cascade;

drop user eygle cascade

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-05723: drop blockchain table ENMOTECH not allowed

为了删除这个区块链表，一个选择是删掉这个PDB：

SQL> connect / as sysdba

Connected.

SQL> alter pluggable database enmo close;

Pluggable database altered.

SQL> drop pluggable database enmo;

drop pluggable database enmo

*

ERROR at line 1:

ORA-65179: cannot keep datafiles for a pluggable database that is not unplugged

SQL> drop pluggable database enmo including datafiles;

Pluggable database dropped.

这是关于区块链表的一些基本常识。