Saturday, January 30, 2021

Blockchain table works for 19.10 RU with patch 32431413 and compatible 19.10.0

[oracle@ol8 32431413]$ $ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed |grep 32431413
32431413   32431413  Sat Jan 30 12:17:07 SGT 2021   19.10 RU FOR ORACLE IS MISSING QCPLK.O WHICH GETS
[oracle@ol8 32431413]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 30 12:19:49 2021
Version 19.10.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> CREATE BLOCKCHAIN TABLE ledger_tab1 (bank VARCHAR2(128), d_date DATE, d_amount NUMBER)
  2  NO DROP UNTIL 25 DAYS IDLE
  3  NO DELETE UNTIL 31 DAYS AFTER INSERT
  4  HASHING USING "SHA2_512" VERSION v1;
CREATE BLOCKCHAIN TABLE ledger_tab1 (bank VARCHAR2(128), d_date DATE, d_amount NUMBER)
*
ERROR at line 1:
ORA-05728: COMPATIBLE needs to be 19.10.0.0.0 or higher to use blockchain table
ORA-00722: Feature "Blockchain table"

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      19.0.0
noncdb_compatible                    boolean     FALSE

SQL> alter system set compatible='19.10.0' scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 5368705904 bytes
Fixed Size                  9192304 bytes
Variable Size            3036676096 bytes
Database Buffers          167772160 bytes
Redo Buffers                7581696 bytes
In-Memory Area           2147483648 bytes
Database mounted.
Database opened.
SQL> CREATE BLOCKCHAIN TABLE ledger_tab1 (bank VARCHAR2(128), d_date DATE, d_amount NUMBER)
  2  NO DROP UNTIL 25 DAYS IDLE
  3  NO DELETE UNTIL 31 DAYS AFTER INSERT
  4  HASHING USING "SHA2_512" VERSION v1;
CREATE BLOCKCHAIN TABLE ledger_tab1 (bank VARCHAR2(128), d_date DATE, d_amount NUMBER)
*
ERROR at line 1:
ORA-05729: blockchain table cannot be created in root container
SQL> conn donghua@pdb1
Enter password:
Connected.
SQL> CREATE BLOCKCHAIN TABLE ledger_tab1 (bank VARCHAR2(128), d_date DATE, d_amount NUMBER)
  2  NO DROP UNTIL 25 DAYS IDLE
  3  NO DELETE UNTIL 31 DAYS AFTER INSERT
  4  HASHING USING "SHA2_512" VERSION v1;

Table created.
SQL> insert into ledger_tab1 values('dbs',sysdate,100);

1 row created.

SQL> update ledger_tab1 set d_amout=200 where bank='dbs';
update ledger_tab1 set d_amout=200 where bank='dbs'
                       *
ERROR at line 1:
ORA-00904: "D_AMOUT": invalid identifier


SQL> update ledger_tab1 set d_amount=200 where bank='dbs';
update ledger_tab1 set d_amount=200 where bank='dbs'
       *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table


SQL> delete from ledger_tab1;
delete from ledger_tab1
            *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table


SQL> truncate table ledger_tab1;
truncate table ledger_tab1
               *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table


SQL> drop table ledger_tab1;
drop table ledger_tab1
           *
ERROR at line 1:
ORA-05723: drop blockchain table LEDGER_TAB1 not allowed

SQL> set long 2000 longc 2000
SQL> select dbms_metadata.get_ddl('TABLE','LEDGER_TAB1') from dual;

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

  CREATE TABLE "DONGHUA"."LEDGER_TAB1"
   (    "BANK" VARCHAR2(128),
        "D_DATE" DATE,
        "D_AMOUNT" NUMBER
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

No comments:

Post a Comment