{"id":7149,"date":"2021-11-08T18:09:00","date_gmt":"2021-11-08T18:09:00","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/brief-overview-of-oracle-block-chain-table\/"},"modified":"2026-11-03T19:27:55","modified_gmt":"2026-11-03T19:27:55","slug":"brief-overview-of-oracle-block-chain-table","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/brief-overview-of-oracle-block-chain-table\/","title":{"rendered":"Brief Overview of Oracle Block Chain Table"},"content":{"rendered":"<p class=\"MsoNormal\"><strong>Blockchain<\/strong><\/p>\n<p class=\"MsoNormal\">A&nbsp;blockchain&nbsp;is a tabulated series of <a href=\"https:\/\/en.wikipedia.org\/wiki\/Record_(computer_science)\" title=\"Record (computer science)\">records<\/a>, which are also called&nbsp;blocks, that are linked together.&nbsp;The contents of each block comprise a&nbsp;<a href=\"https:\/\/en.wikipedia.org\/wiki\/Cryptographic_hash_function\" title=\"Cryptographic hash function\">cryptographic hash<\/a>&nbsp;of the previous block, a&nbsp;<a href=\"https:\/\/en.wikipedia.org\/wiki\/Trusted_timestamping\" title=\"Trusted timestamping\">timestamp<\/a>, as well as transaction data.<\/p>\n<p class=\"MsoNormal\">As each block contains information about the immediate block before him, a chain is formed. Hence, blockchains are resistant to changes in their data because once recorded, any change in a block will change all blocks after it.<\/p>\n<p><drupal-entity data-align=\"center\" data-embed-button=\"media_entity_embed\" data-entity-embed-display=\"view_mode:media.full\" data-entity-type=\"media\" data-entity-uuid=\"643dd37b-c5e8-429b-94da-f442f0a69483\" data-langcode=\"en\"><\/drupal-entity><\/p>\n<p><strong>Cryptographic hash function<\/strong><\/p>\n<p class=\"MsoNormal\">A&nbsp;cryptographic&nbsp;<a href=\"https:\/\/en.wikipedia.org\/wiki\/Hash_function\" title=\"Hash function\">hash function<\/a>&nbsp;is a mathematical&nbsp;<a href=\"https:\/\/en.wikipedia.org\/wiki\/Algorithm\" title=\"Algorithm\">algorithm<\/a>&nbsp;that&nbsp;<a href=\"https:\/\/en.wikipedia.org\/wiki\/Map_(mathematics)\" title=\"Map (mathematics)\">takes<\/a> input&nbsp;data of an arbitrary size and converts it into output of a fixed size.&nbsp;<\/p>\n<p class=\"MsoNormal\">A hash function is expected to have the following features :<\/p>\n<ul>\n<li>The same input will give the same output always<\/li>\n<li>No to same inputs have same output<\/li>\n<li>A small change in input will change output drastically<\/li>\n<\/ul>\n<p class=\"MsoNormal\">Some popular hash functions are<\/p>\n<ul>\n<li><a href=\"https:\/\/en.wikipedia.org\/wiki\/Cryptographic_hash_function#MD5\">MD5<\/a><\/li>\n<li><a href=\"https:\/\/en.wikipedia.org\/wiki\/Cryptographic_hash_function#SHA-1\">SHA-1<\/a><\/li>\n<li><a href=\"https:\/\/en.wikipedia.org\/wiki\/Cryptographic_hash_function#SHA-2\">SHA-2<\/a><\/li>\n<li><a href=\"https:\/\/en.wikipedia.org\/wiki\/Cryptographic_hash_function#SHA-3\">SHA-3<\/a><\/li>\n<li><a href=\"https:\/\/en.wikipedia.org\/wiki\/Cryptographic_hash_function#BLAKE2\">BLAKE2<\/a><\/li>\n<\/ul>\n<p><strong>&nbsp;Blockchain tables in Oracle<\/strong><\/p>\n<p class=\"MsoNormal\">Oracle Blockchain tables are add-only tables in which only insert operations are allowed. Deleting rows is either prohibited or restricted based on time which is pre-defined. A hash function is part of row metadata and helps in making it tamper-proof. Blockchain Tables will be available with Oracle Database 21c and will also become available in the 19c database when the 19.10 release is available.<\/p>\n<ul>\n<li>This feature is a component of the Oracle Database so a new setup will be required.<\/li>\n<li>Indexing and partitioning are allowed in the blockchain table.<\/li>\n<li>Blockchain tables can be used along with (regular) tables in transactions and queries.<\/li>\n<\/ul>\n<p><drupal-entity data-align=\"center\" data-embed-button=\"media_entity_embed\" data-entity-embed-display=\"view_mode:media.full\" data-entity-type=\"media\" data-entity-uuid=\"57c004f8-4f45-4ad2-8b71-1b9809f360ff\" data-langcode=\"en\"><\/drupal-entity><\/p>\n<p class=\"MsoNormal\">Blockchain tables are used to implement centralized blockchain applications where the central authority is the Oracle Database. It provides organizations with more customization and control to decide who can work on the chain. Participants must have privilege to insert data into the blockchain table. Blockchain content is defined and managed by the application. Centralized blockchains are provide higher throughput and lower latency in transactions<\/p>\n<p class=\"MsoNormal\">Rows in a blockchain table are corruption-free. Each row has a hash value which is generated using row data and has value of previous row. If any row is changed it will cause all subsequent chains to change since hash values will be updated<\/p>\n<p class=\"MsoNormal\"><strong>Example of Blockchain tables<\/strong><\/p>\n<p>\n<em><strong>Create the blockchain table<\/strong><\/em><\/p>\n<p class=\"MsoNormal\">CREATE BLOCKCHAIN TABLE&nbsp;command requires additional conditions to be stated. The&nbsp;NO DROP,&nbsp;NO DELETE,&nbsp;HASHING USING, and&nbsp;VERSION&nbsp;clauses are mandatory.<\/p>\n<p class=\"MsoNormal\">&nbsp;<\/p>\n<p class=\"MsoNormal\"><em>CREATE BLOCKCHAIN TABLE emp (employee_id NUMBER, department_id NUMBER) NO DROP UNTIL 90 DAYS IDLE NO DELETE LOCKED HASHING USING &#8220;SHA2_512&#8221; VERSION &#8220;v1&#8221;;<\/em><\/p>\n<p class=\"MsoNormal\">We can use user_blockchain_tables view to check the attributes of the blockchain table<\/p>\n<p class=\"MsoNormal\"><em>SELECT row_retention, row_retention_locked, table_inactivity_retention, hash_algorithm FROM user_blockchain_tables WHERE table_name=&#8217;EMP&#8217;;<\/em><\/p>\n<p class=\"MsoNormal\"><em>ROW_RETENTION ROW TABLE_INACTIVITY_RETENTION HASH_ALG <\/em><\/p>\n<p class=\"MsoNormal\"><em>&#8212;&#8212;&#8212;&#8212;- &#8212; &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211; &#8212;&#8212;&#8211; <\/em><\/p>\n<p class=\"MsoNormal\"><em>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; YES&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 90 SHA2_512<\/em><\/p>\n<p class=\"MsoNormal\"><strong>Description of the table.<\/strong><\/p>\n<p class=\"MsoNormal\">The standard describe command will show only visible columns. USER_TAB_COLS&nbsp;view displays all internal column used to store internal information.<\/p>\n<p class=\"MsoNormal\">\n<em>SELECT internal_column_id &#8220;Col ID&#8221;, SUBSTR(column_name,1,30) &#8220;Column Name&#8221;, SUBSTR(data_type,1,30) &#8220;Data Type&#8221;, data_length &#8220;Data Length&#8221; FROM user_tab_cols WHERE table_name = &#8216;LEDGER_EMP&#8217; ORDER BY internal_column_id; <\/em><\/p>\n<p class=\"MsoNormal\"><em>Col ID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Column Name&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Data Type Data Length <\/em><\/p>\n<p class=\"MsoNormal\"><em>&#8212;&#8212;&#8212;- &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212; &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;- &#8212;&#8211; <\/em><\/p>\n<p class=\"MsoNormal\"><em>1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EMPLOYEE_ID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NUMBER 22 <\/em><\/p>\n<p class=\"MsoNormal\"><em>2 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Department_ID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NUMBER 22 <\/em><\/p>\n<p class=\"MsoNormal\"><em>3 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ORABCTAB_INST_ID$ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NUMBER 22<\/em><\/p>\n<p>&nbsp;<\/p>\n<p class=\"MsoNormal\"><strong>Insert row into the blockchain table.<\/strong><\/p>\n<p class=\"MsoNormal\"><em>INSERT INTO emp VALUES (106,12000);<\/em><\/p>\n<p class=\"MsoNormal\">Display the internal values of the row of the chain.<\/p>\n<p><em>SELECT ORABCTAB_CHAIN_ID$, ORABCTAB_SEQ_NUM$, ORABCTAB_CREATION_TIME$,ORABCTAB_USER_NUMBER$, ORABCTAB_HASH$ FROM emp<\/em><\/p>\n<p class=\"MsoNormal\"><strong>Delete, Drop and Truncate<\/strong><\/p>\n<p class=\"MsoNormal\">You can delete rows in a blockchain table only by using the&nbsp;DBMS_BLOCKCHAIN_TABLE&nbsp;package, and only rows that are outside the retention period. The standard delete command will not work for the blockchain table.<\/p>\n<p>&nbsp;<\/p>\n<p>DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS(<\/p>\n<p>&nbsp;&nbsp; schema_name &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;IN VARCHAR2,<\/p>\n<p>table_name &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IN VARCHAR2,<\/p>\n<p>&nbsp;&nbsp; before_timestamp &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,<\/p>\n<p>&nbsp;&nbsp; number_of_rows_deleted&nbsp;&nbsp;&nbsp;&nbsp; OUT NUMBER);<\/p>\n<p class=\"MsoNormal\">If you created the table with the&nbsp;NO DELETE LOCKED&nbsp;attribute then you can never subsequently modify the row retention period for the purpose of deletion.<\/p>\n<p class=\"MsoNormal\">You can only increase the retention value of block chain table and the drop command will work only when the retention period is over.<\/p>\n<p class=\"MsoNormal\"><strong>Truncate command is not allowed for blockchain table.<\/strong><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Brief Overview of Oracle Block Chain Table<\/p>\n","protected":false},"author":2,"featured_media":7151,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26283","content_type":"blog","created_by":"10130","authored_on":"2023-11-22T18:08:59+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Ravi Garkoti, Oracle Applications Database Administrator, Rackspace Technology","seo_description":"Explore centralized blockchain apps with Oracle Database as the central authority and gain control over participant access, with privileged users inserting data.","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26283-insights_image-oracle-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"Oracle","product":"","publisheddate":"2021-11-08T18:09:00","rxt_solutions":"","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Oracle Blockchain Table | Rackspace Technology","field_topics":"","field_slug":"brief-overview-of-oracle-block-chain-table","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7149","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7149","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=7149"}],"version-history":[{"count":1,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7149\/revisions"}],"predecessor-version":[{"id":7150,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7149\/revisions\/7150"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/7151"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=7149"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=7149"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=7149"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}