Category: Oracle Database 19c

How to find differences between Oracle production and test envs

Determining why things behave differently in production versus a test environment can be time-consuming and painful.

That is until you realize there is a convenient package that allows you to compare the two environments quickly.

Introduced in Oracle Database 11g, the DBMS_COMPARISON package allows you to compare objects, schemas, or data between two databases or schemas (within the same database) to find where they differ.

Let’s take a closer look at how this works.

Create the Comparison

First, you must create a comparison using the DBMS_COMPARISON.CREATE_COMPARISON procedure. You will need to supply a unique name for the Comparison, the schema name, and, optionally, the object name in the current database you wish to compare. You must also supply the location of the other schema you want to compare. The comparison schema can be in the same database (specify NULL for DBLINK_NAME). Still, in my case, below, I’m comparing our product system to our test system, which is reachable from production via a database link called ‘orcl2_test’.

BEGIN
     dbms_comparison.create_comparison(
      					  comparison_name => 'COMP_SALES',
      					  schema_name     => 'SH',
      					  object_name     => 'SALES',
      					  dblink_name     => 'orcl2_test'
      					  );
END;
/  
PL/SQL PROCEDURE successfully completed.

Note: You need a unique index on both tables to compare tables, as shown above.

Run the Comparison

Now we have created the Comparison; we can execute it using the DBMS_COMPARISON.COMPARE function. The COMPARE function returns a BOOLEAN, indicating whether the objects are consistent. It returns TRUE when no differences are found and FALSE when discrepancies are found.

However, the details of the differences are not returned by the function. Instead, the differences are stored in dictionary tables; user_comparison_columns, user_comparison_row_dif, and user_comparison_scan. If a discrepancy is found, you can find details on the differences by querying these tables using the scan ID, which is returned in the variable scan_info.

How you call the COMPARE function is a little more complex than a standard PL/SQL function call. I’ve wrapped the function call in an IF NOT statement in the example below. If the DBMS_COMPARISON.COMPARE function returns FALSE (IF NOT FALSE = TRUE), the function will print out the SCAN ID, so we can use it to query the dictionary table. If, on the other hand, the function returns TRUE (IF NOT TRUE = FALSE), it will return nothing.

DECLARE
        scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
         IF NOT DBMS_COMPARISON.COMPARE
       		     ( comparison_name => 'COMP_SALES'
       		     , scan_info       => scan_info
       		     , perform_row_dif => TRUE
       		     ) THEN
           DBMS_OUTPUT.PUT_LINE('Scan ID:'||scan_info.scan_id);
        END IF;
END;
 /
 
Scan ID:3

Find the details on the Comparison

As you can see, there must be a discrepancy between our production and test environments, as the COMPARE function returned a SCAN ID of 3. We need to query the dictionary tables using the comparison name and the scan id to determine the difference. Below is the query we need to use. You will notice I’ve included two case statements to indicate if the difference was found on the local database (in this case, production) or on the Remote database (in this case, our test environment).

SELECT c.column_name, r.index_value, 
       CASE WHEN r.local_rowid IS NULL  THEN 'No' ELSE 'Yes' END  LOC, 
       CASE WHEN r.remote_rowid IS NULL THEN 'No' ELSE 'Yes' END REM 
  2  FROM   user_comparison_columns c, user_comparison_row_dif r, user_comparison_scan s
  3  WHERE  c.comparison_name = 'COMP_SALES'
  4  AND    r.scan_id         = s.scan_id
  5  AND    r.status	      = 'DIF'
  6  AND    c.index_column    = 'Y'
  7  AND    c.comparison_name = r.comparison_name
  8  AND    s.scan_id         = 3  
  9  ORDER BY r.index_value;
 
COLUMN_NAME    INDEX_VALUE    LOC    REM    
______________ ______________ ______ ______ 
ID             5000           Yes    Yes

The result of our query shows a difference in the data that occurs in the row with ID 5000. However, it doesn’t show us which column has the problem. I must extract the row with ID=5000 from both systems and manually compare them.

SELECT * FROM sales WHERE id=5000;
 
ORDER_ID        CUST_ID    PRODUCT_ID    SUPPLIER_ID     DATE_ID     AMOUNT_SOLD     PRICE     TAX_CODE         ID                                                                                     
____________ ___________ _____________ ______________ ____________ ______________ ___________ __________ ___________  
   248173057    25162649        610090        1229054  18-JAN-23                10    140          .5          5000 
 
SELECT * FROM  sales@orcl2_test WHERE id=5000;
 
ORDER_ID        CUST_ID    PRODUCT_ID    SUPPLIER_ID     DATE_ID     AMOUNT_SOLD     PRICE     TAX_CODE         ID                                                                                       
____________ ___________ _____________ ______________ ____________ ______________ ___________ __________ ___________  
   248173057    25162649        610090        1229054  18-JAN-23                10    140        xx.xx        5000

As you can see from the output above, the difference is in the TAX_CODE column. The TAX_CODE has been masked in the test environment for this particular row.

You may be wondering why Oracle couldn’t show me the exact difference between the two tables. If I had a unique index on the TAX_CODE column, Oracle could have told me the value that differed. But since this is not a unique column, Oracle can only pinpoint the row for me using the unique index on the ID column.

This blog post is part of a series on useful PL/SQL packages. Other blogs in this series include:

How to determine which view is the right view to use in Oracle?

Database views are a handy tool to help obfuscate complex joins from developers and analysts. However, knowing which view to use can be tricky, especially when faced with multiple views with similar-sounding names. After all, you don’t want to join various tables only to discover that all the information you needed was in just a single table. But how do you know which view has the data you are after and will allow you to get it with the minimum number of joins?

The DBMS_UTILITY.EXPAND_SQL_TEXT procedure expands any references to a view within a query, turning it into a subquery in the original statement that displays the full query text behind that view. This trick lets you see where and how the needed data will be retrieved.

Let’s look at an elementary example of this procedure in action, using a SELECT * statement on a view called SALES_V.

SET serveroutput ON 
DECLARE 
    l_clob CLOB; 
BEGIN 
    dbms_utility.Expand_sql_text(input_sql_text => 'SELECT * FROM sales_v', output_sql_text => l_clob); 
    dbms_output.Put_line(l_clob); 
END; 
/

The result of this procedure call is the following output.

SELECT "A1"."order_id" "ORDER_ID", 
       "A1"."time_id"  "TIME_ID", 
       "A1"."cust_id"  "CUST_ID", 
       "A1"."prod_id"  "PROD_ID" 
FROM   (SELECT "A3"."order_id" "ORDER_ID", 
               "A3"."time_id"  "TIME_ID", 
               "A3"."cust_id"  "CUST_ID", 
               "A3"."prod_id"  "PROD_ID" 
        FROM   "SH"."sales" "A3", 
               "SH"."products" "A2" 
        WHERE  "A3"."prod_id" = "A2"."prod_id") "A1"

The subquery with the alias A1 above is the view definition for SALES_V.

It’s a simple two-table join between SALES (alias A3) and PRODUCTS (alias A2). Although the view only returns columns from the SALES table (A3), it does come with the overhead of a join. The execution plan for our simple SELECT * query below shows that.

PLAN_TABLE_OUTPUT                                                                          
__________________________________________________________________________________________ 
Plan hash VALUE: 2857462611                                                                
 
---------------------------------------------------------------------------------------    
| Id  | Operation                  | Name     | ROWS  | Bytes | Cost (%CPU)| TIME     |    
---------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT           |          |  9773 |   314K|     7   (0)| 00:00:01 |    
|*  1 |  HASH JOIN                 |          |  9773 |   314K|     7   (0)| 00:00:01 |    
|   2 |   TABLE ACCESS STORAGE FULL| PRODUCTS |  4999 | 29994 |     2   (0)| 00:00:01 |    
|   3 |   TABLE ACCESS STORAGE FULL| SALES    | 10000 |   263K|     5   (0)| 00:00:01 |    
---------------------------------------------------------------------------------------    
 
Predicate Information (IDENTIFIED BY operation id):                                        
---------------------------------------------------                                        
 
   1 - access("S"."PROD_ID"="P"."PROD_ID")

In this case, it would be best to find an alternative view that only accesses the SALES table or access the SALES table directly. Below is the plan for a direct select statement from the SALES table, and as you can see, the cost of this plan is lower.

EXPLAIN PLAN FOR
SELECT s.order_id, s.date_id, s.cust_id, s.product_id 
FROM sales s;
 
Explained.
 
SELECT * FROM TABLE(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT                                                                      
______________________________________________________________________________________ 
Plan hash VALUE: 781590677                                                             
 
-----------------------------------------------------------------------------------    
| Id  | Operation                 | Name  | ROWS  | Bytes | Cost (%CPU)| TIME     |    
-----------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT          |       | 10000 |   263K|     5   (0)| 00:00:01 |    
|   1 |  TABLE ACCESS STORAGE FULL| SALES | 10000 |   263K|     5   (0)| 00:00:01 |    
-----------------------------------------------------------------------------------

Over the last couple of releases, Oracle has added several handy PL/SQL packages and procedures you might not know about. So, I put together a short blog series highlighting some of my favorites. This blog post is part of that series. Other blogs in this series include How to add a SLEEP COMMAND to your Oracle PL/SQL code and How diff optimizer statistics.

How do I add a SLEEP to a PL/SQL Stored Procedure?

Over the last couple of releases, Oracle has added several handy PL/SQL packages and procedures you might not know about. So, I put together a short blog series highlighting some of my favorites. First up, DBMS_SESSION.SLEEP().

Oracle has always enabled you to add a sleep command to your stored procedures to suspend a session for a specified number of seconds, as shown in the code below.

    DECLARE
       v_start TIMESTAMP;
       v_end   TIMESTAMP; 
    BEGIN
       v_start := SYSTIMESTAMP;
       -- Sleep for 10 seconds
       DBMS_LOCK.SLEEP(10);
       v_end   := SYSTIMESTAMP;
       DBMS_OUTPUT.PUT_LINE('This procedure started at ' ||v_start);
       DBMS_OUTPUT.PUT_LINE('This procedure ended   at ' ||v_end);
    END;
   /
 
This PROCEDURE started AT 10-SEP-22 12.39.40.587041 AM
This PROCEDURE ended   AT 10-SEP-22 12.39.50.637738 AM
 
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:10.02

However, the sleep function was part of the DBMS_LOCK package, which is not granted to PUBLIC, by default, due to the other more powerful functions inside that package. That means you had to beg the DBA or the security team to give you access to this package just to put your session to sleep for a few minutes.

DBMS_SESSION.SLEEP()

Things got a lot easier starting in Oracle Database 18c, as the sleep function is now available in the DBMS_SESSION package, which is granted to PUBLIC by default. That means you can call the function without any additional privileges. Plus, the function code in DBMS_SESSION.SLEEP is identical to DBMS_LOCK.SLEEP, so you can do a simple find and replace in your code!

DECLARE
       v_start TIMESTAMP;
       v_end   TIMESTAMP; 
    BEGIN
       v_start := SYSTIMESTAMP;
       -- Sleep for 10 seconds
       DBMS_SESSION.SLEEP(10);
       v_end   := SYSTIMESTAMP;
       DBMS_OUTPUT.PUT_LINE('This procedure started at ' ||v_start);
       DBMS_OUTPUT.PUT_LINE('This procedure ended   at ' ||v_end);
   END;
   /
This PROCEDURE started AT 10-SEP-22 12.39.40.587041 AM
This PROCEDURE ended   AT 10-SEP-22 12.39.50.637738 AM
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:10.02

Jonathan Lewis made a great point in the comments below about the granularity of the DBMS_SESSION.SLEEP is 1/100th of a second. If you want to introduce a sleep in milliseconds (or less), you can call Java from PL/SQL, as he demonstrated in his blog posts, little-sleeps.

How to create a parameterized view in Oracle

Database views have been used for decades to help simplify both ad-hoc queries and reporting for developers, analysts, and end-users. But the problem with defining views is they tend to be either too specific (not easily reused) or not specific enough (too generic to be performant).

Imagine we need a view to help developers quickly find the details about orders waiting to ship, which have a total value of $100 or more. How would you create such a view?

Would you create a particular view that returns only the columns needed for this scenario with restrictive where clause predicates that limit the orders to only those that have not shipped and have a value greater than $100?

 CREATE OR REPLACE VIEW orders_waiting 
AS 
SELECT i.order_id, i.product_id, i.price, i.description 
FROM   orders o, order_items i
WHERE  o.order_status > 6   -- orders waiting to ship
AND    o.order_total >= 100
AND    o.order_id = i.order_id;

Or do you create a more generic view that could be used for other reports and queries, even though it won’t be as performant as the dedicated view above?

 CREATE OR REPLACE VIEW orders_waiting 
AS 
SELECT o.order_status, o.order_total,
       o.cust_id, o.order_date, o.rep,       
       o.order_mode, o.promotion_id, 
       i.* 
FROM   orders o, order_items i
WHERE  o.order_id = i.order_id;

Regardless of your approach, you will compromise either reusability or performance.

Starting in Oracle Database 19c (19.7), you no longer have to compromise, as Oracle introduced parameterized views or SQL Table Macros.

SQL table macros are expressions found in a FROM clause that acts as a polymorphic or parameterized view. Let’s look at how this can help us with the view we need to create to find the details about orders waiting to ship, which have a total value of $100 or more.

Creating a SQL Macro is similar to creating a PL/SQL function with an additional SQL_MACRO clause. The SQL MACRO clause can take an argument SCALAR (available from 21c onwards) or TABLE (19c onwards), but if you leave it blank, it defaults to a TABLE macro.

CREATE OR REPLACE FUNCTION orders_waiting_to_ship
RETURN CLOB sql_macro AS
 stmt CLOB;
BEGIN
 ...
 RETURN stmt;
END orders_waiting_to_ship;
/

A SQL Macro always returns the view you define as text (VARCHAR, CHAR, or CLOB). The database will resolve that view definition and makes it part of the SQL statement that calls the SQL Macro. I’m capturing the view definition in a CLOB using the variable stmt above.

Let’s add the text for the view and the parameters we want to pass.

CREATE OR REPLACE FUNCTION orders_waiting_to_ship(order_value INTEGER)
RETURN CLOB sql_macro AS
 stmt CLOB;
BEGIN
 stmt := '
  SELECT i.*
  FROM   orders o, order_items i
  WHERE  o.order_status > 6
  AND    o.order_total >= order_value
  AND    o.order_id = i.order_id';
 
 RETURN stmt;
END orders_waiting_to_ship;
/
 
FUNCTION ORDERS_WAITING_TO_SHIP compiled

In the code example above, I’m using the more selectivity view definition, but instead of specifying the order_total to be $100 or higher, I’m going to parameterize the value of the order. That way, we can reuse this SQL Table Macro regardless of what order_value is needed. You will notice I have also added a parameter to the function called order_value, which the user will pass in when they call the function.

You will also notice that my function ORDERS_WAITING_TO_SHIP compiled without errors. This doesn’t mean my view definition or syntax is correct. You will only see an error at runtime. Remember, a SQL TABLE Macro produces your view definition as text that the optimizer will insert into the SQL statement that calls it.

It’s straightforward to call a SQL Table Macro. The Table Macro goes in the FROM clause of the SQL statement. In the example below, I’m calling our orders_waiting_to_Ship SQL Macro and passing it the value 100.

 SELECT *
FROM orders_waiting_to_ship(100) 
ORDER BY order_id;
 
   ORDER_ID    PRODUCT_ID    PRICE DESCRIPTION    
___________ _____________ ________ ______________ 
      10110            10       23 lipstick       
      10110             7       17 Lip gloss      
      10110             8       30 Mascarra
      10110             1       35 Foundation
      20209             9       15 Blusher        
      20209            10       23 lipstick  
      20209             1       35 Foundation
      20209             2       32 Coverup
      30307             7       17 Lip gloss      
      30307             8       30 Mascarra       
      30307             1       35 Foundation       
      30307             2       32 Coverup

We could take it a step further and generalize our SQL Table Macro a little more by parameterizing the order_status and the order_value; that way, our developers can use it to check the status of shipped orders and orders waiting to ship.

 CREATE OR REPLACE FUNCTION orders_chk(order_value INTEGER, stat INTEGER)
RETURN CLOB sql_macro AS
 stmt CLOB;
BEGIN
 stmt := '
 SELECT i.*
 FROM   orders o, order_items i
 WHERE  o.order_status = orders_chk.stat
 AND    o.order_total >= orders_chk.order_value
 AND    o.order_id = i.order_id';
 
 RETURN stmt;
END orders_chk;
/
 
FUNCTION ORDERS_CHK compiled
 
SELECT *
FROM orders_chk(10,6)
ORDER BY order_id;
 
   ORDER_ID    PRODUCT_ID    PRICE DESCRIPTION     ......
___________ _____________ ________ ______________ __________ 
      10110            10       23 lipstick        ......
      10110             7       17 Lip gloss       ......
      10110             8       30 Mascarra        ......
      10110             1       35 Foundation      ......
      10111             9       15 Blusher         ......         
      10112             1       35 Foundation      ......
      10113             1       17 Lip gloss       ......

There are several restrictions on SQL TABLE Macros you should be aware of:

    • When used directly in a SQL statement, the SQL_MACRO annotation is disallowed with RESULT_CACHE, PARALLEL_ENABLE, and PIPELINE.
    • A SQL macro always runs with invoker rights.
    • SQL macros in views are always executed with the privileges of the view owner.
    • SQL macros can’t be used in virtual column expression, function-based indexes, editioning views, or materialized views.
    • SQL macros can’t be used in type methods.

How to use DBMS_STATS DIFF_TABLE_STATS functions

In 11g, Oracle introduced the DBMS_STAT.DIFF_TABLE_STATS functions to help you compare two sets of statistics for a table along with all its dependent objects (indexes, columns, partitions).

There are three versions of this function depending on where the statistics being compared are located:

  • DBMS_STAT.DIFF_TABLE_STATS_IN_HISTORY (compares statistics for a table from two timestamps in the past)
  • DBMS_STAT.DIFF_TABLE_STATS_IN_PENDING (compares pending statistics and statistics as of a timestamp or statistics from the data dictionary)
  • DBMS_STAT.DIFF_TABLE_STATS_IN_STATTAB (compares statistics from a user statistics table and the data dictionary, from two different user statistics tables, or a single user statistics table using two different STATSIDs)

The functions return a report that has three sections:

  1. Basic table statistics
    The report compares the basic table statistics (number of rows, blocks, etc.).
  2. Column statistics
    The second section of the report examines column statistics, including histograms.
  3. Index Statistics
    The final section of the report covers differences in index statistics.

Statistics will only be displayed in the report if the difference in the statistics exceeds a certain threshold (%). The threshold can be specified as an argument to the functions (PCTTHRESHOLD); the default value is 10%. The statistics corresponding to the first source, typically the current table stats in the data dictionary, will be used to compute the differential percentage.

The functions also return the MAXDIFFPCT (a number) along with the report. This is the maximum percentage difference between the statistics. These differences can come from the table, column, or index statistics.

Let’s look at an example.
Continue reading “How to use DBMS_STATS DIFF_TABLE_STATS functions”

How to do a Fuzzy Text Search on JSON #JoelKallmanDay

More and more apps I deal with store data as JSON documents in the Oracle Database. It is exceptionally convenient for the developers but doesn’t always make it easy to know exactly what data we have stored.

The good news is that Oracle offers multiple ways to help you understand precisely what data you have stored in those JSON documents. For example, you can use the built-in JSON Data Guide, which will trawl through all your documents and return a list of all the attributes you have stored.

But suppose you are interested in searching through your documents and only returning those that contain a particular word, value, or a variation thereof. In that case, you will want to take advantage of Oracle’s fuzzy text search or approximate string matching.

Imagine we have a table that stores movie reviews as JSON documents. I’m doing this demo in 19c, so I’m using a VARCHAR2 column, but from 21c onwards, you can use a JSON column.

CREATE TABLE movie_reviews( 
                     title varchar2(200),
                     cust_id NUMBER(26), 
                     cust_reviews varchar2(32000)
                     CONSTRAINT cr_is_json CHECK (cust_reviews IS JSON));

If you really want a JSON column in 19c, you can take advantage of this trick I learned from @Connor_mc_d.

 CREATE TABLE movie_rewiews( title varchar2(200), 
                             cust_id NUMBER(26), 
                             cust_reviews BLOB,
                             CHECK (cust_reviews IS json format oson));

Each review document contains details on the movie id, the star rating, and the review.

{“movie_id”: 5641,
“star_rating”: 1,
"Feedback":" Loved the tv show, but hated the movie. I am so disappointed."
}

We have been asked to find all the reviews that contain the word disappoint or variations of it. To facilitate a fuzzy text search, we need to create a text-based index on the feedback column inside the review documents.

CREATE SEARCH INDEX review_search_ind ON movie_reviews(cust_reviews) FOR JSON;

Once the index is created, we can run a fuzzy text search using the following query:

SELECT m.title, m.cust_reviews.feedback AS customer_review
FROM   movie_reviews m
WHERE  JSON_TEXTCONTAINS(m.cust_reviews, '$.feedback', 'fuzzy((disappoint))');

This results in the following entries being returned.

TITLE                    COMMENTS
-------------------- ----------------------------------------------------------------
Can You Ever Forgive Me? This movie was so disappointing
Top Gun                  Tom Cruise never disappoints. Definitely worth a watch.
Vice                     Perry’s performance in this movie is just so disappointing
Baywatch                 Loved the tv show but hated the movie. I’m so disappointed.
La La Land               Rent this movie you won’t be disappointed!
Batman                   Complete Disappointment

Alternatively, you can use the abbreviated syntax, which will return the same results as above:

SELECT m.cust_reviews.feedback AS comments
FROM movie_reviews m
WHERE JSON_TEXTCONTAINS(cust_reviews, '$.feedback', '?disapoint');

You can also use the stem search operator $. That will match verb forms sharing the same stem, so $disappoint will match “disappointing,” “disappointed,” and “disappoints,” but not “disappointment.”

SELECT m.cust_reviews.feedback AS comments
FROM movie_reviews m
WHERE JSON_TEXTCONTAINS(cust_reviews, '$.feedback', '$disapoint');

This blog was made possible by the lovely Roger Ford, the product manager for Oracle Text and JSON, who has taught me everything I know about text searches in the Oracle Database.

SQL Tuning: How to fix a SQL statement that picks a Nested Loop instead of a Hash Join

At one point or another during your career, you will face a situation where the optimizer picks the wrong join method. As tempting as it is to brute-force the plan you want via a hint, it’s always better to understand why the Optimizer made a decision and provide the necessary information to allow the Optimizer to select the plan you want by default.

In this short video below, I explain how the Optimizer determines the join method based on the cardinality of the table on the left-hand side of the join. I also provide a simple set of steps to help you identify the common problems that can cause an incorrect join method to be used and guidance on how to supply the necessary information, so the Optimizer will automatically select the appropriate join method.

Adaptive joins, introduced in 12c, can help address some cases where the Optimizer selects the wrong join method but not in all cases. It’s always better to know how to save yourself rather than relying on the built-in safety net.

Happy Tuning!

Why Oracle Implement Blockchain in the Database

The primary focus of conventional data security technologies like passwords, firewalls, and data encryption is to keep criminals out of your company and your data stores.

But what protects your data, especially your essential asset (contracts, property titles, account statements, etc.), from being modified or even deleted by folks who gain access to your systems legitimately or illegitimately (hackers)?

Crypto-secure Data Management

This is where Blockchain can help. Layering Blockchain technologies on top of conventional data security features provide an extra level of protection that prevents illicit modifications or deletes of data.

What is Blockchain?

When we think of Blockchain, many of us instantly think of decentralized peer to peer apps that only permit consensus-based data changes. However, adopting these apps requires new development methodologies, speciality data stores and potentially new business practices, which is complicated and expensive!

But if we take a closer look at Blockchain technologies, we see four critical components; immutability, cryptographic digests, cryptographic signatures, and distributed systems. Each part works to protect against a different aspect of illicit data changes performed using legitimate user credentials or by hackers.

Integrating these Blockchain technologies into the Oracle Database brings the critical security benefits of Blockchain to mainstream applications with minimal or no changes required. Providing the full functionality of the world’s leading database on crypto-protected data.

In the video below, Juan Loaiza explains how Oracle implemented Blockchain technologies in the Oracle Database and how they can be used to protect your essential business data. I’ve also included a brief description of these features under the video.

How do Blockchain technologies work in the Oracle Database?

To protect against illicit data changes made by rogue insiders or malicious actors using insiders’ credentials, Oracle has introduced Immutable tables (insert-only tables) in Oracle Database 21c (21.3).

Immutable Tables

With an Immutable table, it is possible to insert new data, but existing data cannot be changed or deleted by anyone using the database, even the database administrators (SYSDBA). It is also impossible to change an immutable table’s definition or convert it to an updatable table. However, an Immutable table appears like any other table in the database from an application’s point of view. It can store both relational data and JSON documents, and it can be indexed and partitioned or used as the basis of a view.

Blockchain Tables

To protect against illicit changes made by hackers, Oracle has introduced Blockchain tables. Blockchain tables are immutable tables that organize rows into several chains. Each row, except the first row in the chain, is chained to the previous row via a cryptographic digest or hash. The hash is automatically calculated on insert based on that row’s data and the hash value of the previous row in the chain. Timestamps are also recorded for each row on insertion.

Any modification to data in a Blockchain table breaks the cryptographic chain because the hash value of the row will change. You can verify the contents of a blockchain table have not been modified since they were inserted using the DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS procedure.

DECLARE
actual_rows NUMBER;
verified_rows NUMBER;
 
BEGIN
 
SELECT COUNT(*)
INTO actual_rows
FROM admin.my_bc_tab;
 
dbms_blockchain_table.verify_rows(
schema_name => 'admin',
table_name => 'MY_BC_TAB',
number_of_rows_verified => verified_rows);
 
DBMS_OUTPUT.put_line('Actual_rows='||actual_rows|| ' Verified Rows=' || verified_rows);
END;
/

End-User Data Signing

Even with Immutable or Blockchain tables, data can be falsely inserted in an end user’s name by someone using stolen credentials. To address this vulnerability, Oracle allows end-users to cryptographically sign the data they insert using their private key that is never passed to the database.

Each end-user registers a digital certificate containing their public key with this database. This digital certificate allows the database to validate the end-users signature when new data is inserted. Even if a hacker manages to steal a valid set of credentials without the private key, the data insert signature won’t match and will therefore not be accepted.

It’s also possible for end-users to ensure the database has received their changes by requesting Oracle countersign the newly inserted data. Oracle returns a crypto-receipt to the user, ensuring nothing on the mid-tier can filter specific data to prevent it from being recorded.

Distributing Cryptographic Digest

Even with cryptographically chained rows, sophisticated cyber-criminals or authorities could illicitly change data via a large-scale cover-up, where the entire database is replaced. To detect such a cover-up, Oracle enables schema owners to sign and distribute the cryptographic digest for a blockchain table periodically. Remember, the digest can’t be used to infer the data in the table, but authorized users can use it to validate the chain and confirm their newly inserted data is present. The crypto-digest can be posted to an independent public store or blockchain, like Ethereum or sent out by email or made available via a REST API.

A cover-up can easily be detected by comparing the previously published digests to the current table content. Also, distributing the publicly across multiple independent services prevents an authority or cyber-attacker from deleting all the separate copies.

Getting Started With Blockchain

Both Immutable and Blockchain tables are free features of the Oracle  Database. No additional licenses or software is needed to take advantage of these new table types, which are completely transparent to all new and existing applications.

Also, note Oracle has backported Immutable tables and Blockchain tables to Oracle Database 19c (19.11 and 19.10, respectively). Please check My Oracle Support for more details before attempting to use Blockchain tables in 19.10.

For more information on Blockchain check out the Oracle Blockchain blog, Oracle Blockchain LiveLabs or the Oracle Blockchain documentation.

How to use Oracle Database In-Memory for free!

Oracle recently announced a new free base level for the Oracle Database In-Memory Option, their real-time analytics capability. With the base level, you can allocate up to 16GB of memory to the In-Memory column store, per instance without having to have an addition In-Memory license. Larger column stores will still need the additional license. Continue reading “How to use Oracle Database In-Memory for free!”

Oracle Database 19c Upgrade Virtual Classroom

Over the last couple of months, the wonderful folks from Oracle Australia have been running a FREE virtual training event on what to expect from Oracle Database 19c and how to prepare to upgrade to the latest long-term support release of the Oracle Database.

The first 5 sessions covered everything you would want to know about how to upgrade and what you can expect from Oracle Database 19c including an in-depth look at the new Multitenant architecture. The sessions so far have been presented by some of the best Oracle speakers around including Mike Dietrich, Julian Dontcheff, Martin Bach, Richard Agnew, and Alex Blyth. All of the sessions so far have been recorded and are available for replay.

Under the Hood of an Autonomous Database

The final session in series is scheduled for August 11th at 10am AEST ( August 10th at 5 pm PST) and will be presented by Alex Blyth and myself. In our session, we plan to give you a peek under the hood of the Oracle Autonomous Database and provide you with a clear understanding of how this unique autonomous database works. We’ll share details on the exclusive combination of Oracle Database 19c features, best practices, and machine-learning algorithms used to deliver this family of cloud services. We’ll use live demos to show you how it can simplify your approach to data management with Oracle Database 19c and accelerate your transition to the cloud.

You can registers for this session here. Hope you can join us!

self_driving

%d bloggers like this: