Five Things you might not know or may have forgotten about the Oracle Database

In this session, we'll look at five things you might not have known about the Oracle Database - or that you might have known about but never realized how they could benefit you. For each topic, we will explain the functionality and demonstrate the benefits using real-world examples. The topics covered apply to anyone running Oracle Database 11g and up, including Standard Edition, with only a few minor exceptions.

Transcript

1. Five Things You Might Not Know About The Oracle Database Maria Colgan Distinguished Product Manager Oracle Database Or may have forgotten
2. Five things you may not know or have forgotten about the Oracle Database 3. Parameterized Views 2. Online Operations 5. Power of Immutability 4. PL/SQL Packages 1. Benefits of Invisibility Copyright © 2023, Oracle and/or its affiliates
3. Benefits of Invisibility Copyright © 2023, Oracle and/or its affiliates
4. Managing Indexes One of the most common approaches for query tuning on OLTP systems is to add an index Copyright © 2023, Oracle and/or its affiliates The more complex the app, the more indexes you are likely to have But over time, how valuable are those indexes? After all, each additional index increases the overhead of each DML operation However, we are all relucent to drop an index just in case it causes a performance regression!
5. Determine which indexes are useful via DBA_INDEX_USAGE view SELECT i.index_name, u.total_access_count tot_access, u.total_exec_count exec_cnt, u.bucket_0_access_count B0, u.bucket_1_access_count B1, u.bucket_2_10_access_count B2_10, u.bucket_11_100_access_count B11_100, u.bucket_101_1000_access_count B101_1K, u.bucket_1000_plus_access_count B1K, u.last_used FROM dba_index_usage u, dba_indexes i WHERE i.owner='MARIA' AND i.index_name = u.name (+) ORDER BY u.total_access_count; Copyright © 2023, Oracle and/or its affiliates Use an outer join with DBA_INDEXES to capture indexes that are never used Note: Index usage info is flushed from memory to disk every 15 minutes Query LAST_FLUSH_TIME in V$INDEX_USAGE_INFO to determine the last time it was updated
6. Determine which indexes are useful via DBA_INDEX_USAGE view SELECT i.index_name, u.total_access_count tot_access, u.total_exec_count exec_cnt, u.bucket_0_access_count B0, u.bucket_1_access_count B1, u.bucket_2_10_access_count B2_10, u.bucket_11_100_access_count B11_100, u.bucket_101_1000_access_count B101_1K, u.bucket_1000_plus_access_count B1K, u.last_used FROM dba_index_usage u, dba_indexes i WHERE i.owner='MARIA' AND i.index_name = u.name (+) ORDER BY u.total_access_count; INDEX_NAME TOT_ACCESS EXEC_CNT B0 B1 B2_10 B11_100 B101_1K B1K LAST_USED ---------------- ---------- ---------- ------- ------- ------- -------- ---------- ---------- --------- PROD_CUST_SALES 1 1 0 0 0 0 0 1 06-JAN-23 INDX_LOC 2 2 0 1 1 0 0 0 12-JAN-23 INDX_DEPTNO 19 19 0 18 1 0 0 0 26-JAN-23 PROD_SUP_INDX 27 27 25 0 0 0 2 0 26-JAN-23 EMPNO_PK_IND 82 82 48 32 0 2 0 0 26-JAN-23 CHAN_SOLD PROD_SUB_IDX Copyright © 2023, Oracle and/or its affiliates The histogram indicates how useful an index is by showing how many accesses fall in each bucket of rows returned
7. Determine which indexes are useful via DBA_INDEX_USAGE view SELECT i.index_name, u.total_access_count tot_access, u.total_exec_count exec_cnt, u.bucket_0_access_count B0, u.bucket_1_access_count B1, u.bucket_2_10_access_count B2_10, u.bucket_11_100_access_count B11_100, u.bucket_101_1000_access_count B101_1K, u.bucket_1000_plus_access_count B1K, u.last_used FROM dba_index_usage u, dba_indexes i WHERE i.owner='MARIA' AND i.index_name = u.name (+) ORDER BY u.total_access_count; INDEX_NAME TOT_ACCESS EXEC_CNT B0 B1 B2_10 B11_100 B101_1K B1K LAST_USED ---------------- ---------- ---------- ------- ------- ------- -------- ---------- ---------- --------- PROD_CUST_SALES 1 1 0 0 0 0 0 1 06-JUL-22 INDX_LOC 2 2 0 1 1 0 0 0 06-JUL-22 INDX_DEPTNO 19 19 0 18 1 0 0 0 06-JUL-22 PROD_SUP_INDX 27 27 25 0 0 0 2 0 06-JUL-22 EMPNO_PK_IND 82 82 48 32 0 2 0 0 06-JUL-22 CHAN_SOLD PROD_SUB_IDX Copyright © 2023, Oracle and/or its affiliates These two indexes have never been used and are candidates to be dropped
8. Leverage Invisible Indexes before dropping indexes for good -- Mark indexes you are looking to remove as invisible, if no one complains after some time; delete them ALTER INDEX prod_sub_idx INVISIBLE; -- New indexes can be marked invisible until you have an opportunity to prove they improve performance CREATE INDEX my_idx ON t(x, object_id) INVISIBLE; -- Test newly created invisible indexes by setting OPTIMIZER_USE_INVISBLE_INDEXES to TRUE ALTER SESSION SET optimizer_use_invisible_indexes = TRUE; Copyright © 2023, Oracle and/or its affiliates
9. Managing shared schema objects across apps One of the difficulties with the relational model today is: • Different applications may need different columns from the same table Copyright © 2023, Oracle and/or its affiliates • However, all apps are impacted by any DDL change on the table • Dropping a column will break queries still referring to it • Adding a column breaks “SELECT *” queries • Dropping a column will delete the data from all the rows, even those that are already archived CUSTOMERS FIRST_NAME LAST_NAME INITIALS : APP A APP B
10. Sharing schema objects across apps -- Code for App A SELECT * FROM customers; FIRST_NAME LAST_NAME IN ADDRESS … ---------- ---------- -- -------- Dan Jones DJ New York Joseph Smith JS Los Angeles -- Code for App B SELECT first_name, last_name, initials FROM customers; FIRST_NAME LAST_NAME IN ---------- ---------- -- Dan Jones DJ Joseph Smith JS Copyright © 2023, Oracle and/or its affiliates CUSTOMERS FIRST_NAME LAST_NAME INITIALS : -- App A and B share a single Customers table
11. Sharing schema objects across apps Copyright © 2023, Oracle and/or its affiliates -- Code for App A SELECT * FROM customers; FIRST_NAME LAST_NAME ADDRESS … ---------- ---------- -------- Dan Jones New York Joseph Smith Los Angeles -- Code for App B SELECT first_name, last_name, initials FROM customers; * ERROR at line 1: ORA-00904: "INITIALS": invalid identifier -- App A no longer needs the initials columns and decides to drop the column ALTER TABLE customers DROP COLUMN initials; CUSTOMERS FIRST_NAME LAST_NAME :
12. Invisible Columns Copyright © 2023, Oracle and/or its affiliates -- Code for App A SELECT * FROM customers; FIRST_NAME LAST_NAME ADDRESS … ---------- ---------- -------- Dan Jones New York Joseph Smith Los Angeles -- Code for App B SELECT first_name, last_name, initials FROM customers; FIRST_NAME LAST_NAME IN ---------- ---------- -- Dan Jones DJ Joseph Smith JS -- But if App A simple marks the column invisible, other Apps that query it directly will still work ALTER TABLE customers MODIFY initials INVISIBLE; CUSTOMERS FIRST_NAME LAST_NAME :
13. Managing Statistics • By default, optimizer statistics are published as soon as they are gathered • But what if you want to experiment with different statistic-gathering options? • How do you protect yourself from the possibility of unpredictable changes in execution plans Copyright © 2023, Oracle and/or its affiliates
14. Using Invisible or Pending statistics allows you to test without worry -- Check the current statistics publishing setting for the database SELECT dbms_stats.get_prefs('PUBLISH', 'MARIA', 'SALES') FROM dual; DBMS_STATS.GET_PREFS('PUBLISH’) ------------------------------------------- TRUE -- Check the current column stats for the SALES table SELECT column_name, histogram FROM user_tab_col_statistics WHERE table_Name ='SALES'; COLUMN_NAME HISTOGRAM ------------------------------ --------------- PROD_ID NONE CUST_ID NONE TIME_ID NONE CHANNEL_ID NONE PROMO_ID NONE QUANTITY_SOLD NONE AMOUNT_SOLD NONE Copyright © 2023, Oracle and/or its affiliates None of the columns have a histogram
15. Using Invisible or Pending statistics allows you to test without worry Copyright © 2023, Oracle and/or its affiliates -- Set new statistics gathered for MARIA.SALES to be invisible to the optimizer by default BEGIN dbms_stats.set_table_prefs('MARIA', 'SALES', 'PUBLISH', 'FALSE'); END; / -- Gather a new set of statistics for MARIA.SALES that included new histograms types introduced in 12c BEGIN dbms_stats.gather_table_stats('MARIA', 'SALES' method_opt=>'FOR ALL COLUMNS SIZE 254'); END; /
16. Using Invisible or Pending statistics allows you to test without worry Copyright © 2023, Oracle and/or its affiliates -- Confirm the pending statistics are what we are expecting SELECT report FROM table(dbms_stats.diff_table_stats_in_pending('MARIA','SALES', systimestamp,0)); The returned REPORT is a CLOB You must use the set long and longchunksize command to define the width of a long so the report can be displayed properly
17. Using Invisible or Pending statistics allows you to test without worry Copyright © 2023, Oracle and/or its affiliates -- Confirm the pending statistics are what we are expecting SELECT report FROM table(dbms_stats.diff_table_stats_in_pending('MARIA','SALES', systimestamp,0)); DBMS_STAT.DIFF_TABLE_STATS functions are table functions, you must use the key word TABLE when you are selecting from them, otherwise you will receive an error saying the object does not exist
18. Using Invisible or Pending statistics allows you to test without worry Copyright © 2023, Oracle and/or its affiliates -- Confirm the pending statistics are what we are expecting SELECT report FROM table(dbms_stats.diff_table_stats_in_pending('MARIA','SALES', sysdate,0)); REPORT -------------------------------------------------------------------------------- : COLUMN STATISTICS DIFFERENCE: ............................. COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZE .................................................................................. AMOUNT_SOLD A 583 .001715265 NO 0 4 C105 C2525 960 B 583 .001715265 NO 0 4 C105 C2525 960 CUST_ID A 630 .001587301 NO 0 5 C202 C3135 960 B 630 .001576 YES 0 5 C202 C3135 960 PROD_ID A 766 .001305483 NO 0 5 C106 C3056 960 B 766 .001298 YES 0 5 C106 C3056 960 PROMO_ID A 116 .008620689 NO 0 4 C102 C2646 960 B 116 .008620689 NO 0 4 C102 C2646 960 QUANTITY_SOLD A 44 .022727272 NO 0 3 C102 C130 960 B 44 .022727272 NO 0 3 C102 C130 960 TIME_ID A 620 .001612903 NO 0 8 77C60 78640 960 B 620 .001608 YES 0 8 77C60 78640 960 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Source A is the current published stats for the SALES table, while source B is the pending stats
19. Using Invisible or Pending statistics allows you to test without worry Copyright © 2023, Oracle and/or its affiliates --Set the initialization parameter optimizer_use_pending_statistics to TRUE to test out the new statistics ALTER SESSION SET optimizer_use_pending_statistics =TRUE; -- After testing the pending statistics with histograms, we can go ahead and publish them BEGIN dbms_stats.publish_pending_stats('MARIA','SALES'); END; /
20. Using Invisible or Pending statistics allows you to test without worry Copyright © 2023, Oracle and/or its affiliates --Set the initialization parameter OPTIMIZER_USE_PENDING_STATISTICS to TRUE to test new statistics ALTER SESSION SET optimizer_use_pending_statistics =TRUE; -- After testing the pending statistics with histograms, we can go ahead and publish them BEGIN dbms_stats.publish_pending_stats('MARIA','SALES'); END; / -- Confirm the histograms that we gather as pending statistics are now visible SELECT column_name, histogram FROM user_tab_col_statistics WHERE table_Name ='SALES'; COLUMN_NAME HISTOGRAM ------------------------------ --------------- PROD_ID HYBRID CUST_ID HYBRID TIME_ID HYBRID CHANNEL_ID NONE PROMO_ID NONE QUANTITY_SOLD NONE AMOUNT_SOLD NONE
21. Useful Online Operations Copyright © 2023, Oracle and/or its affiliates
22. Why Online Operations are so important DBAs often have a long list of maintenance activities & data model changes they need to do • Goal to improve overall system performance In the past, these tasks have required exclusive locks • Forcing DBAs to wait until application downtime to complete them With online operations, these tasks can occur, when necessary, without delay Online operations • Wait for active transactions to end before beginning • Do not block any new DML statements (with the only exception being parallel DML) Copyright © 2023, Oracle and/or its affiliates
23. Adding a column to a table happens online Copyright © 2023, Oracle and/or its affiliates A fast online operation that allocates no space and generates no redo or undo -- Adding a column with or without a default value is a metadata-only operation since 11g SELECT bytes/1024/1024 MB, blocks FROM user_segments WHERE segment_name='SALES'; MB BLOCKS 176 22528 ALTER TABLE sales ADD tax_code varchar2(20) DEFAULT 'xxx-xxx' NOT NULL; Table SALES altered. SELECT bytes/1024/1024 MB, blocks FROM user_segments WHERE segment_name='SALES'; MB BLOCKS 176 22528
24. 09:37:38 SQL> CREATE TABLE sales3 AS SELECT * FROM sales; Table SALES3 created. 09:37:41 SQL> SELECT column_name, num_distinct, num_nulls, histogram FROM user_tab_col_statistics WHERE table_name='SALES3'; COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM COMMENTS 0 10075747 NONE SHIPMODE 7 0 NONE REVENUE 4296902 0 NONE PRICE 2416797 0 NONE AMOUNT_SOLD 50 0 NONE DATE_ID 2395 0 NONE SUPPLIER_ID 1924463 0 NONE PRODUCT_ID 1370318 0 NONE CUST_ID 2021661 0 NONE ORDER_ID 2116177 0 NONE Online statistics gathering Copyright © 2023, Oracle and/or its affiliates Online statistics automatically gathers base statistics as part of any CTAS or IAS operation into an empty table or partition Histograms are not created as they require an additional pass of the data and add an overhead to the initial op
25. 09:47:40 SQL> ALTER SESSION SET "_optimizer_gather_stats_on_load_hist"=TRUE; 09:47:40 SQL> CREATE TABLE sales3 AS SELECT * FROM sales; Table SALES3 created. 09:47:51 SQL> SELECT column_name, num_distinct, num_nulls, histogram FROM user_tab_col_statistics WHERE table_name='SALES3'; COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM COMMENTS 0 10075747 NONE SHIPMODE 7 0 FREQUENCY REVENUE 4296902 0 HYBRID PRICE 2416797 0 HYBRID AMOUNT_SOLD 50 0 FREQUENCY DATE_ID 2395 0 HYBRID SUPPLIER_ID 1924463 0 HYBRID PRODUCT_ID 1370318 0 HYBRID CUST_ID 2021661 0 HYBRID ORDER_ID 2116177 0 HYBRID Online statistics gathering with histograms Copyright © 2023, Oracle and/or its affiliates Be careful utilizing underscore parameters. Only use them within a session, and if you fully understand the behavior, they control
26. -- Ability to convert a non-partitioned table to a partitioned table online ALTER TABLE sales MODIFY PARTITION BY RANGE(date_id) INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')), PARTITION p1 VALUES LESS THAN (TO_DATE('01-FEB-2018', 'DD-MON-YYYY')), PARTITION p2 VALUES LESS THAN (TO_DATE('01-MAR-2018', 'DD-MON-YYYY’)) ) ONLINE UPDATE INDEXES ( idx_dt_rev local, idx_ord_sold GLOBAL PARTITION BY RANGE (order_id) ( PARTITION ip0 VALUES LESS THAN (MAXVALUE)) ); Table SALES altered. Online partitioning of a non-partitioned table Copyright © 2023, Oracle and/or its affiliates SALES SALES The use of the keyword ONLINE enables concurrent DML operations while the conversion is ongoing
27. -- Ability to convert a non-partitioned table to a partitioned table online ALTER TABLE sales MODIFY PARTITION BY RANGE(date_id) INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')), PARTITION p1 VALUES LESS THAN (TO_DATE('01-FEB-2004', 'DD-MON-YYYY')), PARTITION p2 VALUES LESS THAN (TO_DATE('01-MAR-2004', 'DD-MON-YYYY’)) ) ONLINE UPDATE INDEXES ( idx_dt_rev local, idx_ord_sold GLOBAL PARTITION BY RANGE (order_id) ( PARTITION ip0 VALUES LESS THAN (MAXVALUE)) ); Table SALES altered. Online partitioning of a non-partitioned table Copyright © 2023, Oracle and/or its affiliates SALES SALES If you don’t specify what should happen to the indexes, Oracle will create local partitioned index if they contain the partition key or are bitmap indexes Otherwise, indexes become global non-partitioned indexes
28. Parameterized Views Copyright © 2023, Oracle and/or its affiliates
29. Database Views Database views have been used for decades to help simplify both ad-hoc queries and reporting But the problem with defining views is they are either too specific or not specific enough Copyright © 2023, Oracle and/or its affiliates
30. Database Views Database views have been used for decades to help simplify both ad-hoc queries and reporting But the problem with defining views is they are either too specific or not specific enough In this example, we need a view to help quickly find details about orders that are waiting to ship Copyright © 2023, Oracle and/or its affiliates CREATE OR REPLACE VIEW orders_waiting AS SELECT i.* FROM orders o, order_items i WHERE o.order_status > 6 AND o.order_total >= 100 AND o.order_id = i.order_id’; Extremely specific, efficient view includes no unnecessary data but not reusable CREATE OR REPLACE VIEW order_details 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; Nonspecific view that includes lots of unnecessary data , so it can be reusable
31. SQL Macros allow you to create portable, parameterized code Easy to create portable code by factoring out common SQL expressions and statements into reusable, parameterized constructs with SQL Macros A Macro acts like a pre-processor for SQL with no expensive context switching • Enabling faster performance Two types of SQL Macros: • TABLE expressions typically found in FROM-clause that act as a parameterized views • SCALAR expressions to encapsulate calculations and business logic typically found in SELECT list, WHERE/HAVING, GROUP BY/ORDER BY clauses Copyright © 2023, Oracle and/or its affiliates
32. 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; / Parameterized View – AKA SQL Macro Copyright © 2023, Oracle and/or its affiliates
33. SELECT * FROM orders_waiting_to_ship(100) ORDER BY order_id; ORDER_ID PRODUCT_ID PRICE DESCRIPTION ___________ _____________ ________ ______________ 70707 7 17 Lip gloss 70707 8 28 Mascara 70707 9 39 Blusher 70707 10 30 lipstick 80808 8 28 Mascara 80808 8 28 Mascara 80808 8 28 Mascara 80808 8 28 Mascara Parameterized View – AKA SQL Macro Copyright © 2023, Oracle and/or its affiliates
34. Useful PL/SQL Packages Copyright © 2023, Oracle and/or its affiliates
35. DBMS_LOCK.SLEEP() From time to time, all code needs to pause or sleep for a short time Copyright © 2023, Oracle and/or its affiliates SQL> 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 The problem is DBMS_LOCK includes other, more sensitive methods Therefore, not granted to public Requires DBA intervention to get a accessible sleep function in PL/SQL
36. DBMS_SESSION.SLEEP() From time to time, all code needs to pause or sleep for a short time Copyright © 2023, Oracle and/or its affiliates SQL> 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 Sleep function moved to DBMS_SESSION DBMS_SESSION is granted to public Compatible with DBMS_LOCK.SLEEP, simply do a search/replace
37. How do you determine which view is the right view to use? Views help to hide complexity from developers But they can also cause problems They make it incredibly easy to write apparently simple SQL statements that result in a highly complex SQL statement being sent to the database The DBMS_UTILITY.EXPAND_SQL_TEXT procedure expands references to views, turning them into subqueries in the original statement Copyright © 2023, Oracle and/or its affiliates SELECT * FROM sales_v;
38. DBMS_UTILITY.EXPAND_SQL_TEXT 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; / Copyright © 2023, Oracle and/or its affiliates
39. DBMS_UTILITY.EXPAND_SQL_TEXT 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" Copyright © 2023, Oracle and/or its affiliates View definition has alias A1
40. How to determine why production is different to test Trying to figure out why things behave differently in production than they do in test can be time- consuming and painful The DBMS_COMPARISON package allows you to compare objects, schemas, or data between two different databases or schemas Note: for a table comparison you do need a unique index on both tables Copyright © 2023, Oracle and/or its affiliates
41. DBMS_UTILITY.EXPAND_SQL_TEXT -- Begin by creating the comparison BEGIN dbms_comparison.create_comparison( comparison_name => 'COMP_SALES', schema_name => 'SH', object_name => 'SALES', dblink_name => 'orcl2_test' ); END; / Copyright © 2023, Oracle and/or its affiliates
42. DBMS_COMPARISON Execute the COMPARE function to perform the compare operation 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: 1 Copyright © 2023, Oracle and/or its affiliates The scan_id allows you to find out what the differences are between the systems It will return a BOOLEAN indicating if the object is consistent or not
43. DBMS_COMPARISON 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 FROM user_comparison_columns c, user_comparison_row_dif r, user_comparison_scan s WHERE c.comparison_name = 'COMP_SALES‘ AND r.scan_id = s.scan_id AND r.status = 'DIF’ AND c.index_column = 'Y’ AND c.comparison_name = r.comparison_name AND s.scan_id = 1 ORDER BY r.index_value; COLUMN_NAME INDEX_VALUE LOC REM ----------- ----------- --- ---- TAX_CODE 0.05 No Yes Copyright © 2023, Oracle and/or its affiliates The test environment has a different tax code
44. Immutability Copyright © 2023, Oracle and/or its affiliates
45. Protecting data from corrupt or compromised users All information stored in a database is important And this data is often most vulnerable to users with valid credentials Over the years, Oracle has offered several techniques to help protect that data: • Read-only tablespaces • Read-only users • Read-only views • Auditing • Schema-only accounts But up until now, there hasn’t been a way to allow a table to continue to accept new data but prevent anyone, including SYSDBAs, from changing any existing of the data Copyright © 2023, Oracle and/or its affiliates
46. Oracle Immutable Tables Oracle Immutable Tables prevent illicit modifications by database users • Allows relational data, JSON, or LOB documents New data can be added, but existing data cannot be changed or deleted by anyone using the database • Even Database Administrators The table definition can not be changed, and it’s not possible to convert immutable table to updatable or vice-versa • It also not possible to modify table metadata in the database dictionary Side benefit: immutability also prevents accidental changes due to human error Copyright © 2023, Oracle and/or its affiliates Available in Database 19.11, 21.3
47. Prevent illicit changes by Insiders using Immutable Tables -- Oracle Immutable Tables prevent illicit modifications by insiders using SQL but allow new data to be added CREATE IMMUTABLE TABLE trade_ledger (id number, stock_name varchar2(40), value number) NO DROP UNTIL 40 DAYS IDLE NO DELETE UNTIL 100 DAYS AFTER INSERT; -- Anyone with the appropriate privileges can insert into an immutable table INSERT INTO trade_ledger VALUES( 1000, ‘ORCL’, 500); 1 row inserted. -- However, it’s not possible to delete from the table until the specified amount of time has elapsed DELETE FROM trade_ledger WHERE id=1000; SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table Copyright © 2023, Oracle and/or its affiliates
48. Five things you may not know or have forgotten about the Oracle Database 3. Parameterized Views 2. Online Operations 5. Power of Immutability 4. PL/SQL Packages 1. Benefits of Invisibility Copyright © 2023, Oracle and/or its affiliates
49. More Information Visit: SQLMaria.com @SQLMaria youtube.com/c/MariaColgan42 Copyright © 2023, Oracle and/or its affiliates If you have more questions later, feel free to ask Thank you