Oracle 21C區塊連結串列
何為區塊連結串列
Blockchain Tables
https://blogs.oracle.com/database/post/introducing-oracle-database-21c
區塊鏈作為一種技術，在解決與交易驗證相關的許多問題方面已經做出了很大的承諾。 雖然在將這項技術引入企業方面取得了相當大的進展，但仍存在一些問題。 可以說，最大的問題是構建支援分散式賬本的應用程式的複雜性。 Oracle Database 21c透過引入區塊連結串列解決了這個問題。 這些表的操作與任何普通堆表一樣，但有許多重要的區別。 其中最值得注意的是，行在插入到表中時會進行加密雜湊，以確保以後不再更改該行。
Blockchain as a technology has promised much in terms of solving many of the problems associated with the verification of transactions. While considerable progress has been made in bringing this technology to the enterprise, a number of problems exist. Arguably, the largest being the complex nature of building applications that can support a distributed ledger. Oracle Database 21c addresses this problem with the introduction of Blockchain Tables. These tables operate like any normal heap table, but with a number of important differences. The most notable of these being that rows are cryptographically hashed as they are inserted into the table, ensuring that the row can no longer be changed at a later date.
實驗：
資料庫版本：
Oracle 21C
SQL> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
PDB名稱:CJCPDB1
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 CJCPDB1 MOUNTED
例項狀態：
SQL> select con_id,dbid,name,open_mode from v$pdbs; CON_ID DBID NAME OPEN_MODE ---------- ---------- -------------------- ---------- 2 643081980 PDB$SEED READ ONLY 3 1808035318 CJCPDB1 MOUNTED
啟動PDB:
SQL> alter pluggable database CJCPDB1 open; SQL> select con_id,dbid,name,open_mode from v$pdbs;
進入pdb資料庫
SQL> alter session set container=CJCPDB1; SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 CJCPDB1 READ WRITE NO
檢視資料檔案
SQL> select name from v$dbfile; NAME -------------------------------------------------------------------------------- /oracle/app/oracle/oradata/CJC/cjcpdb1/system01.dbf /oracle/app/oracle/oradata/CJC/cjcpdb1/sysaux01.dbf /oracle/app/oracle/oradata/CJC/cjcpdb1/undotbs01.dbf /oracle/app/oracle/oradata/CJC/cjcpdb1/users01.dbfile
建立測試表空間cjc_tbs和測試使用者cjc、chen。
SQL> create tablespace cjc_tbs datafile '/oracle/app/oracle/oradata/CJC/cjcpdb1/cjc_tbs01a.dbf' size 10M autoextend on; SQL> create user cjc identified by a default tablespace cjc_tbs; SQL> create user chen identified by a default tablespace cjc_tbs; SQL> GRANT connect,resource,dba to cjc; SQL> GRANT unlimited tablespace TO cjc; SQL> GRANT execute ON sys.dbms_blockchain_table TO cjc; SQL> GRANT connect,resource,dba to chen; SQL> GRANT unlimited tablespace TO chen; SQL> GRANT execute ON sys.dbms_blockchain_table TO chen;
連線cjc使用者
SQL> conn cjc/a@192.168.31.100:1521/cjcpdb1 Connected. SQL> show user USER is "CJC"
建立區塊連結串列ledger_emp。
SQL> CREATE BLOCKCHAIN TABLE ledger_emp (employee_id NUMBER, salary NUMBER) NO DROP UNTIL 31 DAYS IDLE NO DELETE LOCKED HASHING USING "SHA2_512" VERSION "v1"; Table created.
透過user_blockchain_tables檢視區塊連結串列的屬性。
Verify the attributes set for the blockchain table in the appropriate data dictionary view.
SQL> SELECT row_retention, row_retention_locked, table_inactivity_retention, hash_algorithm FROM user_blockchain_tables WHERE table_name='LEDGER_EMP'; ROW_RETENTION ROW TABLE_INACTIVITY_RETENTION HASH_ALG ------------- --- -------------------------- -------- 365000 YES 31 SHA2_512
檢視錶結構
Show the description of the table.
SQL> DESC ledger_emp Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NUMBER SALARY NUMBER
DESC僅顯示可見列。
使用USER_TAB_COLS檢視顯示內部隱藏列名。
SET PAGESIZE 100 COL "Data Length" FORMAT 9999 COL "Column Name" FORMAT A24 COL "Data Type" FORMAT A28 SELECT internal_column_id "Col ID", SUBSTR(column_name,1,30) "Column Name", SUBSTR(data_type,1,30) "Data Type", data_length "Data Length" FROM user_tab_cols WHERE table_name = 'LEDGER_EMP' ORDER BY internal_column_id; Col ID Column Name Data Type Data Length ---------- ------------------------ ---------------------------- ----------- 1 EMPLOYEE_ID NUMBER 22 2 SALARY NUMBER 22 3 ORABCTAB_INST_ID$ NUMBER 22 4 ORABCTAB_CHAIN_ID$ NUMBER 22 5 ORABCTAB_SEQ_NUM$ NUMBER 22 6 ORABCTAB_CREATION_TIME$ TIMESTAMP(6) WITH TIME ZONE 13 7 ORABCTAB_USER_NUMBER$ NUMBER 22 8 ORABCTAB_HASH$ RAW 2000 9 ORABCTAB_SIGNATURE$ RAW 2000 10 ORABCTAB_SIGNATURE_ALG$ NUMBER 22 11 ORABCTAB_SIGNATURE_CERT$ RAW 16 12 ORABCTAB_SPARE$ RAW 2000 12 rows selected.
步驟2：插入資料
Step 2 : Insert rows into the blockchain table
插入第一行資料。
Insert a first row into the blockchain table.
SQL> INSERT INTO ledger_emp VALUES (106,12000); 1 row created. SQL> COMMIT; Commit complete.
顯示第一行的內部值。
Display the internal values of the first row of the chain.
SQL> SET LINE 300 COL "Chain HASH" FOR A30 COL "Chain date" FORMAT A17 COL "Chain ID" FORMAT 99999999 COL "Seq Num" FORMAT 99999999 COL "User Num" FORMAT 9999999 COL "Chain HASH" FORMAT 99999999999999 SELECT ORABCTAB_CHAIN_ID$ "Chain ID", ORABCTAB_SEQ_NUM$ "Seq Num", to_char(ORABCTAB_CREATION_TIME$,'dd-Mon-YYYY hh-mi') "Chain date", ORABCTAB_USER_NUMBER$ "User Num", ORABCTAB_HASH$ "Chain HASH" FROM ledger_emp; Chain ID Seq Num Chain date User Num --------- --------- ----------------- -------- Chain HASH --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 24 1 14-Nov-2021 10-11 110 C66C6E3C124BA8B517827F357A86C7941419B0D22B502583BE118AF3D3DBD45EF15B20F446DD76F190691471B37A830CEF2E99D5885B9DEF678E7538C76A918D
以cjc身份連線並在區塊連結串列中插入一行資料。
SQL> conn cjc/a@192.168.31.100:1521/cjcpdb1 SQL> GRANT insert ON ledger_emp TO chen;
插入資料
SQL> conn chen/a@192.168.31.100:1521/cjcpdb1 SQL> INSERT INTO cjc.ledger_emp VALUES (106,24000); 1 row created. SQL> COMMIT; Commit complete.
檢視資料
SQL> conn cjc/a@192.168.31.100:1521/cjcpdb1 SQL> SELECT ORABCTAB_CHAIN_ID$ "Chain ID", ORABCTAB_SEQ_NUM$ "Seq Num", to_char(ORABCTAB_CREATION_TIME$,'dd-Mon-YYYY hh-mi') "Chain date", ORABCTAB_USER_NUMBER$ "User Num", ORABCTAB_HASH$ "Chain HASH", employee_id, salary FROM ledger_emp; Chain ID Seq Num Chain date User Num --------- --------- ----------------- -------- Chain HASH EMPLOYEE_IDSALARY --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ---------- 24 1 14-Nov-2021 10-11 110 C66C6E3C124BA8B517827F357A86C7941419B0D22B502583BE118AF3D3DBD45EF15B20F446DD76F190691471B37A830CEF2E99D5885B9DEF678E7538C76A918D 106 12000 24 2 14-Nov-2021 10-20 111 C172CB59794C574E62B21AC82F054129D94AD45988C90D685EDBA58D293A89D635FB6475FB0E0F781F8A6F566E764315A8F5CA0225072D622E5AABCC258F7810 106 24000
步驟3：刪除表資料
SQL> DELETE FROM ledger_emp WHERE ORABCTAB_USER_NUMBER$ = 119; DELETE FROM ledger_emp WHERE ORABCTAB_USER_NUMBER$ = 119 * ERROR at line 1: ORA-05715: operation not allowed on the blockchain table
不能使用delete命令刪除區塊連結串列中的行。
可以使用DBMS_BLOCKCHAIN_TABLE包刪除區塊鏈中的資料。
SQL>
SET SERVEROUTPUT ON
DECLARE
NUMBER_ROWS NUMBER;
BEGIN
DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS('CJC','LEDGER_EMP', null, NUMBER_ROWS);
DBMS_OUTPUT.PUT_LINE('Number of rows deleted=' || NUMBER_ROWS);
END;
/
Number of rows deleted=0
PL/SQL procedure successfully completed.
使用DBMS_BLOCKCHAIN_TABLE包刪除區塊連結串列中的行，只能刪除保留期之外的行。
這就是為什麼儲存過程執行成功但是沒有刪除任何行的原因。
truncate表
SQL> TRUNCATE TABLE ledger_emp; TRUNCATE TABLE ledger_emp * ERROR at line 1: ORA-05715: operation not allowed on the blockchain table
指定在建立行15天后才能刪除行。
SQL> ALTER TABLE ledger_emp NO DELETE UNTIL 15 DAYS AFTER INSERT; ALTER TABLE ledger_emp NO DELETE UNTIL 15 DAYS AFTER INSERT * ERROR at line 1: ORA-05741: minimum retention time too low, should be at least 16 days
為什麼不能更改此屬性？您建立了具有“NO DELETE LOCKED”屬性的表。
Why can't you change this attribute? You created the table with the NO DELETE LOCKED attribute.
LOCKED子句表示以後不能修改行保留。
The LOCKED clause indicates that you can never subsequently modify the row retention.
步驟4：drop表
Step 4 : Drop the blockchain table
SQL> DROP TABLE ledger_emp; DROP TABLE ledger_emp * ERROR at line 1: ORA-05723: drop blockchain table LEDGER_EMP not allowed
區塊連結串列的建立時限制了在31天內不能刪除。
The blockchain table was created so that it cannot be dropped before 31 days of inactivity.
更改表的行為以允許較低的保留率。
Change the behavior of the table to allow a lower retention.
SQL> ALTER TABLE ledger_emp NO DROP UNTIL 1 DAYS IDLE; ALTER TABLE auditor.ledger_emp NO DROP UNTIL 1 DAYS IDLE * ERROR at line 1: ORA-05732: retention value cannot be lowered SQL> ALTER TABLE ledger_emp NO DROP UNTIL 40 DAYS IDLE; Table altered.
只能增加保留值。
這禁止刪除出於安全目的需要保留的任何歷史資訊。
You can only increase the retention value.
This prohibits dropping and removing any historical information that needs to be kept for security purposes.
#####chenjuchao 20220102 16:10#####
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2850403/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 一語中的：區塊鏈本質是雜湊連結串列區塊鏈
- 連結串列-迴圈連結串列
- 連結串列-雙向連結串列
- 連結串列 - 單向連結串列
- 連結串列4: 迴圈連結串列
- 連結串列-單連結串列實現
- 連結串列-雙向通用連結串列
- 連結串列-雙向非通用連結串列
- 【LeetCode】-＞連結串列-＞通向連結串列自由之路LeetCode
- 連結串列入門與插入連結串列
- Leetcode_86_分割連結串列_連結串列LeetCode
- 資料結構-單連結串列、雙連結串列資料結構
- 連結串列
- LeetCode-Python-86. 分隔連結串列（連結串列）LeetCodePython
- Oracle 21C TriggersOracle
- 建立連結串列兩種方法的區別
- 反轉連結串列（C++簡單區）C++
- 單連結串列建立連結串列出現問題
- 為什麼Oracle要搞出兩個髒連結串列Oracle
- **203.移除連結串列元素****707.設計連結串列****206.反轉連結串列**
- 連結串列逆序
- 2、連結串列
- 連結串列（python）Python
- 重排連結串列
- （一）連結串列
- 分割連結串列
- 單連結串列
- php連結串列PHP
- 資料結構與演算法——連結串列 Linked List（單連結串列、雙向連結串列、單向環形連結串列-Josephu 問題）資料結構演算法
- Oracle 21C Flex ClustersOracleFlex
- Oracle 21C Extended ClustersOracle
- 【LeetCode-連結串列】面試題-反轉連結串列LeetCode面試題
- 面試-陣列和連結串列的區別面試陣列
- 資料結構之連結串列：206. 反轉連結串列資料結構
- 反轉連結串列、合併連結串列、樹的子結構
- 程式碼隨想錄第3天 | 連結串列 203.移除連結串列元素，707.設計連結串列，206.反轉連結串列
- [連結串列】2.輸入一個連結串列，反轉連結串列後，輸出新連結串列的表頭。[多益，位元組考過]
- 【圖解連結串列類面試題】移除連結串列元素圖解面試題