Oracle 区块链表创建过程详解

NO DELETE { [ LOCKED ] | (UNTIL number DAYS AFTER INSERT [ LOCKED ]) }

HASHING USING sha2_512 VERSioN v1

--drop table bct_t1 purge;

create blockchain table bct_t1 (
  id            number,
  fruit         varchar2(20),
  quantity      number,
  created_date  date,
  constraint bct_t1_pk primary key (id)
)
no drop until 0 days idle
no delete until 16 days after insert
hashing using "SHA2_512" version "v1";

set linesize 120 pagesize 50
column column_name fORMat a30
column data_type format a27
column hidden_column format a13

select internal_column_id,
       column_name,
       data_type,
       data_length,
       hidden_column
FROM   user_tab_cols       
WHERE  table_name = 'BCT_T1'
ORDER BY internal_column_id;

INTERNAL_COLUMN_ID COLUMN_NAME                    DATA_TYPE                   DATA_LENGTH HIDDEN_COLUMN
------------------ ------------------------------ --------------------------- ----------- -------------
                 1 ID                             NUMBER                               22 NO
                 2 FRUIT                          VARCHAR2                             25 NO
                 3 QUANTITY                       NUMBER                               22 NO
                 4 CREATED_DATE                   DATE                                  7 NO
                 5 ORABCTAB_INST_ID$              NUMBER                               22 YES
                 6 ORABCTAB_CHAIN_ID$             NUMBER                               22 YES
                 7 ORABCTAB_SEQ_NUM$              NUMBER                               22 YES
                 8 ORABCTAB_CREATION_TIME$        TIMESTAMP(6) WITH TIME ZONE          13 YES
                 9 ORABCTAB_USER_NUMBER$          NUMBER                               22 YES
                10 ORABCTAB_HASH$                 RAW                                2000 YES
                11 ORABCTAB_SIGNATURE$            RAW                                2000 YES
                12 ORABCTAB_SIGNATURE_ALG$        NUMBER                               22 YES
                13 ORABCTAB_SIGNATURE_CERT$       RAW                                  16 YES
                14 ORABCTAB_SPARE$                RAW                                2000 YES

14 rows selected.

column row_retention format a13
column row_retention_locked format a20
column table_inactivity_retention format a26
column hash_alGorithm format a14

SELECT row_retention,
       row_retention_locked, 
       table_inactivity_retention,
       hash_algorithm  
FROM   user_blockchain_tables 
WHERE  table_name = 'BCT_T1';

ROW_RETENTION ROW_RETENTION_LOCKED TABLE_INACTIVITY_RETENTION HASH_ALGORITHM
------------- -------------------- -------------------------- --------------
           16 NO                                            0 SHA2_512

alter table bct_t1 no drop until 100 days idle;

Error report -
ORA-05732: retention value cannot be lowered

alter table bct_t1 no drop;

Error starting at line : 1 in command -
alter table bct_t1 no drop
Error report -
ORA-00600: internal error code, arguments: [atbbctable_1], [0], [], [], [], [], [], [], [], [], [], []

-- 增加到 32 天
alter table bct_t1 no delete until 32 days after insert;

Table BCT_T1 altered.


-- 减少到 16 天时返回错误
alter table bct_t1 no delete until 16 days after insert;

Error report -
ORA-05732: retention value cannot be lowered

alter table bct_t1 no delete;

Error report -
ORA-00600: internal error code, arguments: [atbbctable_1], [0], [], [], [], [], [], [], [], [], [], []

-- INSERT
insert into bct_t1 (id, fruit, quantity, created_date ) values (1, 'apple', 20, sysdate);

1 row inserted.

sql> commit;

Commit complete.


-- UPDATE
update bct_t1 set quantity = 10 where id = 1;

Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain table


-- DELETE
delete from bct_t1 where id = 1;

Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain table

truncate table bct_t1;

Error report -
ORA-05715: operation not allowed on the blockchain table

-- 修改字段长度
alter table bct_t1 modify (fruit varchar2(25));

Table BCT_T1 altered.


-- 增加字段
alter table bct_t1 add (additional_info varchar2(50));

Error report -
ORA-05715: operation not allowed on the blockchain table


-- 删除字段
alter table bct_t1 drop column quantity;

Error report -
ORA-05715: operation not allowed on the blockchain table

set serveroutput on
declare
  l_rows  number;
begin
  dbms_blockchain_table.delete_expired_rows(
    schema_name            => 'admin',
    table_name             => 'bct_t1',
    before_timestamp       => null,
    number_of_rows_deleted => l_rows);

  dbms_output.put_line('Rows Deleted=' || l_rows);
end;
/
Rows Deleted=0

PL/SQL procedure successfully completed.

set serveroutput on
declare
  l_rows  number;
begin
  dbms_blockchain_table.delete_expired_rows(
    schema_name            => 'testuser1',
    table_name             => 'it_t1',
    before_timestamp       => systimestamp - 60,
    number_of_rows_deleted => l_rows);

  dbms_output.put_line('Rows Deleted=' || l_rows);
end;
/
Rows Deleted=0

PL/SQL procedure successfully completed.

set serveroutput on
declare
  l_rows      number;
  l_verified  number;
begin
  select count(*)
  into   l_rows
  from   admin.bct_t1;

  dbms_blockchain_table.verify_rows(
    schema_name             => 'admin',
    table_name              => 'bct_t1',
    number_of_rows_verified => l_verified);

  dbms_output.put_line('Rows=' || l_rows || '  Verified Rows=' || l_verified);
end;
/
Rows=1  Verified Rows=1

PL/SQL procedure successfully completed.