Oracle 23ai: Read-Only Users and Sessions

 

- Overview:

• Oracle 23ai database introduces a new feature to control whether a user or session is enabled for read-write operations, irrespective of the privileges of the user that is connected to the database. 
• The READ_ONLY user only applies on a PDB local database user.
• The READ_ONLY session applies to any type of user for any type of container.
• With the capability to disable and re-enable the read-write capabilities of any user or session without revoking and re-granting privileges provides you with more flexibility to temporarily control the privileges of users or sessions for testing, administration, or application development purposes.
• It also gives you a simple way to control the read-write behavior within different parts of an application that are used by the same user or session.
• You can set the access of a local user to a PDB to READ ONLY or READ WRITE with the ALTER USER or CREATE USER statement.
• After READ ONLY access is enabled for a PDB user, whenever that user connects to the PDB, the session operates as if the database is open in read-only mode and the user cannot perform any write operation.
• A new column (READ_ONLY) has been added to dictionary views *_USERS to show if READ_ONLY is enabled/disabled for a database user.

In this blog, I'll demonstrate enable/disable READ ONLY for a PDB local database user.

Prerequisites:

• Oracle Database 23ai Free Developer Release.
• A pluggable database with HR database schema.

A Demo Steps:

1. Connect to a PDB using HR local database user and run a DML statement.

- Dictionary view *_USERS currently shows that READ_ONLY is disabled for HR user.

2. Connect to a PDB using SYSTEM common user and enable READ ONLY for HR user using below SQL command.

SQL> alter user &USERNAME read only;

3. Reconnect to a PDB using HR local database user and run a DML statement.

- Dictionary view *_USERS currently shows that READ_ONLY is enabled for HR user.

- You will get below error message.

SQL Error: ORA-28194: Can perform read operations only

4. Using SYSTEM database session, disable READ ONLY for HR user using below SQL command.

SQL> alter user &USERNAME read write;

5. Reconnect to a PDB using HR local database user and run a DML statement. DML statement will run without error.

- Dictionary view *_USERS currently shows that READ_ONLY is disabled for HR user.

OCW 2024: What to Expect Around Oracle 23ai

 

I’m so excited to share that I’ll be attending Oracle CloudWorld 2024 this year!. I’m thrilled as It will be my first OCW event to attend in person !!!. 

This event has always been an ignition for innovation and cutting-edge technology, and I can’t wait to dive into what Oracle has to surprise us.

One of my main goals at OCW this year is to learn more about Oracle 23ai, Oracle’s latest and most advanced AI offering. From what I’ve gathered so far, Oracle 23ai promises to bring significant advancements in AI-powered analytics, automation, and decision-making capabilities. I’m particularly interested in how these features can be applied to streamline business operations and enhance data-driven strategies.

Another key area I’m excited to explore is Exascale, Oracle’s high-performance computing solution. The promise of Exascale lies in its ability to process massive datasets at unprecedented speeds, which could be a game-changer for industries that rely heavily on data-intensive operations. Understanding how Exascale integrates with Oracle 23ai and the broader Oracle Cloud ecosystem will be the main interest for me at the event.

Attending Oracle OCW isn’t just about gaining knowledge; it’s also about connecting with industry experts, exchanging ideas with fellow professionals, and staying ahead in the rapidly evolving tech landscape. I’m looking forward to the sessions, hands-on labs, and keynotes that will provide deeper insights into Oracle’s vision for the future of cloud computing and AI.

If you’re also attending, I’d love to connect and share experiences. Let’s make the most of this opportunity to learn, grow, and innovate together!

Oracle 23ai: Managing Flashback Database Logs Outside the Fast Recovery Area

 

- Overview:

• In previous database releases, you could store flashback database logs only in the fast recovery area.
• Oracle 23ai database introduces the option to store flashback database logs in a separate location.
• With this new option, you can choose to write the flashback logs to faster disks.
• Using a separate destination also eliminates the manual administration to manage the free space in the fast recovery area.
• A new database instance initialization parameter (DB_FLASHBACK_LOG_DEST) has been introduced to specify a separate location for the flashback database logs. This parameter is a system modifiable on CDB level.

In this blog, I'll demonstrate the steps to set a separate location for the flashback database logs.

Prerequisites:

• Oracle Database 23ai running in archive log mode.

Setting Steps:

1. Connect to a root container using SYS user and confirm the database is running in archive log mode and flashback is ON. 

- Notice that FRA destination is +DATA, and flashback logs were generated in FRA (+DATA).

- Currently DB_FLASHBACK_LOG_DEST is not configured.

2. Before assigning a value for the initialization parameter (DB_FLASHBACK_LOG_DEST), you must specify the size of flashback area by setting the initialization parameter (DB_FLASHBACK_LOG_DEST_SIZE) to a nonzero value. 

3. When modifying the DB_FLASHBACK_LOG_DEST parameter with the ALTER SYSTEM statement, you must disable and reenable flashback logging.

- Notice that flashback destination is +RECO.

SQL> alter system set db_flashback_log_dest_size=100G scope=both;

SQL> alter system set db_flashback_log_dest='+RECO' scope=both;

- Archive log files are still generated in FRA (+DATA).

Oracle 23ai: Bigfile Tablespace Shrink

 

- Overview:

• If you try to resize/shrink a datafile before Oracle Database 23ai, you'll get "ORA-03297: file contains used data beyond requested RESIZE value", because there are segments' extents (your tables, indexes, partitions, etc.) that are blocking it from shrinking..
• Oracle 23ai database introduces the option to shrink a Bigfile tablespace to reclaim datafile's free space.
• A new procedure (SHRINK_TABLESPACE) has been added to DBMS_SPACE package to shrink tablespace and reclaim datafile's free space.

In this blog, I'll demonstrate the steps to shrink a tablespace.

Prerequisites:

• Oracle Database 23ai.

Preparation Steps:

 1. Create a new Bigfile tablespace.

   - Connect to a pluggable database and create a new tablespace.

2. Create new tables on the new tablespace and populate with data.

  - In my example, I used below commands to create and populate tables with data.

  - Create tables:

create table segments tablespace TEST_SHRINK as select * from dba_segments;

create table objects tablespace TEST_SHRINK as select * from dba_objects;

create table my_tables tablespace TEST_SHRINK as select * from dba_tables;

  - Populate tables by running below inserts multiple times:

insert into segments select * from segments; 

insert into objects select * from objects;

insert into my_tables select * from my_tables;

  - Check datafile's free space and write it down for comparison later.

3. Delete some data or truncate table/s. 

  - In my example, I truncated OBJECTS table.

  - Notice that datafile's free space increased from 316M to 724M.

Shrink Tablespace Steps:

1. Analyze a tablespace shrink by executing below command.

execute dbms_space.shrink_tablespace('TEST_SHRINK',shrink_mode => DBMS_SPACE.TS_MODE_ANALYZE)

  - It is highly recommended to determine the potential of a shrink operation by running it in analyze mode first. 

  - The result of this analysis contains useful information including a list of unsupported objects, a list of movable objects, the total size of movable objects in the tablespace, and the suggested target size for the tablespace. 

  - Analyzing a tablespace will take much less time that actually shrinking it.

2. Execute shrink tablespace by executing below command.

execute dbms_space.shrink_tablespace('TEST_SHRINK')

  - Notice that datafile's size was shrunk from 1024M to 202M and datafile's free space was decreased from 724M to 7M.

Oracle 23ai: Unrestricted Parallel DMLs

 

- Overview:

• Oracle database allows DML statements to be executed in parallel mode by breaking the DML statements into exclusive smaller tasks.
• However in releases prior Oracle database 23ai, parallel DML operations had a limitation. Once an object is modified by a parallel DML statement, that object cannot be read or modified by later statements of the same transaction before ending the transaction by executing commit or rollback.
• Oracle Database 23ai removes that restriction and by introducing "Unrestricted Parallel DMLs or Unrestricted Direct Loads" feature.

- Benefits: 

• In the same transaction session and before ending the transaction, you can:
• Query the same table multiple times
• Perform serial or parallel DML on the same table
• Perform multiple direct loads 
• Overhead Reduced
• Enable parallel DML in a session
• Separate commits are not required after each parallel DML statement 
• Take the full advantage of using parallel DMLs in the same transaction 

- Restrictions: 

• Heap organized table only.
• No ASSM tablespaces

- How to Enable Parallel DML Mode: 

• The parallel DML mode is required because parallel DML and serial DML have different locking, transaction, and disk space requirements and parallel DML is disabled for a session by default.
• When parallel DML is disabled, no DML is executed in parallel even if the PARALLEL hint is used.
• When parallel DML is enabled in a session, all DML statements in this session are considered for parallel execution.
• Run below SQL statement to enable parallel DML mode in a session:

ALTER SESSION ENABLE PARALLEL DML;

• Enable unrestricted parallel DML mode in a specific SQL statement, include the ENABLE_PARALLEL_DML hint.

INSERT /*+ ENABLE_PARALLEL_DML */ …

• However, even if parallel DML is enabled, the DML operation may still execute serially if there are no parallel hints or no tables with a parallel attribute or if restrictions on parallel operations are violated.

- Practice:

• In this practice, I'll enable parallel DML and perform a parallel insert (non-unrestricted) without commit or rollback. After the insert, I'll run a select from the same table and it will cause an error.
• Then I'll perform the parallel insert using unrestricted parallel hint without commit or rollback, After I'll run select and additional insert without errors.

1. Create a new table.

SQL> create table test_dmls as select * from objects_objects;   Table TEST_DMLS created.

2. Alter the session and enable parallel DML.

SQL> alter session enable parallel dml;   Session altered.

3. Insert into the table with PARALLEL hint.

SQL> insert /*+ parallel(test_dmls 4) */ into test_dmls             select /*+ parallel(test_dmls 4) */ * from test_dmls;    76,237 rows inserted.

4. Select from the table without performing a commit or rollback. You will get and error.

SQL> select count(*) from test_dmls;   Error starting at line : 1 in command - select count(*) from test_dmls SQL ORA-12838: cannot read/modify an object after modifying it in parallel   More Details : https://docs.oracle.com/error-help/db/ora-12838/ SQL> !oerr ora 12838 12838, 00000, "cannot read/modify an object after modifying it in parallel" // *Cause: Within the same transaction, an attempt was made to add read or  // modification statements on a table after it had been modified in parallel // or with direct load. This is not permitted. // *Action: Rewrite the transaction, or break it up into two transactions: // one containing the initial modification and the second containing the // parallel modification operation.

5.  Perform a commit.

SQL> commit;   Commit complete.

6. After the commit is done, now you can select again from the table.

SQL> select count(*) from test_dmls;      COUNT(*) ___________      152474

7. Perform a parallel insert using ENABLE_PARALLEL_DML hint.

SQL> insert /*+ enable_parallel_dml(test_dmls 4) */ into test_dmls             select /*+ enable_parallel_dml(test_dmls 4) */ * from test_dmls;   152,474 rows inserted.

8. Select from the table without performing a commit or rollback. Now you will NOT get an error.

SQL> select count(*) from test_dmls;      COUNT(*) ___________      304948

9. Perform another parallel insert without a commit or rollback. No error.

SQL> insert /*+ enable_parallel_dml(test_dmls 4) */ into test_dmls   2* select /*+ enable_parallel_dml(test_dmls 4) */ * from test_dmls;   304,948 rows inserted.   SQL> /   609,896 rows inserted.   SQL> /   1,219,792 rows inserted. SQL> select count(*) from test_dmls;    COUNT(*)  ___________      2439584

Oracle 23ai: Lock Free Reservations Capability

 

- Overview:

• In previous database releases when a column value of a row is updated by adding or subtracting from it, all other updates to that row are blocked until the transaction is committed or rolled back.
• Oracle Database 23ai introduces a new feature called "Lock Free Reservations" that allows multiple concurrent updates on a numeric column value to proceed without being blocked by uncommitted updates when adding or subtracting from the column value.
• Used with applications operate on numeric aggregate data. Such as data involve subtraction or addition of the values rather than assigning a value.
• The Lock Free Reservation feature is enabled by default. A Lock Free Reservation parameter, named "lockfree_reservation", is provided at the PDB level.
• To use Lock Free Reservation, use the RESERVABLE keyword to declare a RESERVABLE numeric column when you CREATE or ALTER a table.

- Benefits: 

• Reserve values without locking.
• Value locked on commit.
• Short held locks.
• Improved concurrency.
• Reduced bottlenecks. 

- Use Cases:

• Bank account balance (debt and credit transactions).
• Inventory and supply chain control.
• Ticketing 

- Restrictions:

• Only numeric data type.
•  Table must have a primary key.
• A Reservable column cannot be part of foreign key constraint. 
• A Reservable column cannot be a primary key, virtual column, or identity column.
• Indexes are not supported on a Reservable column.
• No updates on multiple Reservable columns in a table. 
• No mixing non-Reservable and Reservable columns updates.

- Demo:

In this demo I'll create a table "ACCOUNTS". The table will have a Reservable numeric column "Balance". I'll reduce the balance in one session without committing the transaction, then I'll try to reduce the balance of the same account from another session where I'll get an error notifying there is not enough value to reduce the balance.

1. Create a table (HR.ACCOUNTS) with a Reservable numeric column (BALANCE). The column will have a CHECK constraint to enforce a minimum balance value.

Note: a CHECK constraint is NOT a mandatory with a Reservable column.

create table accounts ( acc_id number primary key, acc_name varchar2(10), balance number reservable constraint accounts_bal_ck check (balance >= 50) );

2. Select from USER_TAB_COLUMNS and USER_TABLES views to see information about the lock free table and reservable column.

select table_name,column_name,reservable_column  from user_tab_columns where table_name='ACCOUNTS';   select table_name,has_reservable_column from user_tables where table_name='ACCOUNTS';

3. Find the name of the journal table created when the accounts tables was created with a reservable column.

select table_name,tablespace_name from user_tables where table_name like 'SYS_RESER%';

4.  Perform a describe and select from the journal table.

desc SYS_RESERVJRNL_77307 select * from SYS_RESERVJRNL_77307;

5. Insert and commit a row into ACCOUNTS table and run select.

insert into accounts values (100,'SCOTT',89); commit; select * from accounts;

6. Update the row without a commit and select from the journal table. The journal table will have a record for the uncommitted updated row executed in that session.

Note: A select from accounts table will show balance value unchanged, because the update has not committed. We are just reserving the balance.

7. Open a new database session and run the same update statement. 

Even we see the balance is 89, the update will get an error because of CHECK constraint violation. The first transaction in the first session reserved 25 making only 64 available until the transaction commit or rollbacks, the transaction in session 2 would violate the check constraint.

8. Go back to the first first session and perform a rollback.

9. Go back to the second session and run the update again and commit.

10. Go back to the first session and select the row from the table. With the update and commit in session 2 the balance is now updated. 

11. Query the journal table contents. With no pending truncations there are no rows in the table.

 

Oracle 23ai: Enumeration domains - List of Values in the Database

 

- Overview:

• Oracle Database 23ai introduces a new way to create lists of values: enumeration (enum) domains.
• Benefits:
• Create lists of name-value pairs in the database.
• Query the enum as a list of values.
• Limit column values to those in the enum list.
• Use the enum names as constants in SQL statements.
• Display the name of enum values.
• To create an enumeration domain, use create domain as enum command and provide a list of names. This assigns the values to each name in the order listed, starting with one. Each name has a value one higher than the previous.
• By default, they are case insensitive. If you want case sensitive names, as with table names place them in double quotes.
• Enums have an implicit check constraint. The database applies this to the column when you associate the domain. This ensures you can only store the enum’s values in the column.

In this blog, I'll demonstrate the steps to create error message severity name-value pair lists of enums and associate enums with incidents table severity column.   

Prerequisites:

• Oracle Database 23ai.

Demo 

1. Create an enumeration domain using below command.

     create domain <Domain Name> as enum (< comma separated list of values >);

    - In this demo, I'll create two an enumeration domains.

    - The first domain will provide a list of error messages severity. It starts with "Emergency" having the value of 1 to "Debug" which has the value of 8. Domain will provide a list of numbers values.

create domain err_msg_severity as enum (

Emergency, Alert, Critical, Error,

Warning, Notice, Informational, Debug);

    - The second domain will provide the same list of error messages severity. It starts with "Emergency" having the value "emerg" to "Debug" which has the value of "debug". Domain will provide a list of characters values.

create domain err_msg_severity_2 as enum (

Emergency = 'emerg', 

Alert = 'alert', 

Critical = 'crit', 

Error = 'error',

Warning = 'warn', 

Notice = 'notice', 

Informational = 'info', 

Debug = 'debug'

);

2. Create incidents table where severity column uses the first domain (list of numbers values).

     Notice that severity column's data type is NUMBER.

3. Insert rows into incidents table. 

    - Remember that severity column value should be between 1 and 8. Assigning a value not between 1 and 8 will raise ORA-11534.

ORA-11534: check constraint (HR.SYS_C0013233) involving column SEVERITY due to domain constraint 

     - You can use <DOMAIN_NAME>.<ENUM_NAME> when providing a value to severity column.

4. Drop and recreate incidents table where severity column uses the second domain (list of characters values).

     Notice that severity column's data type is VARCHAR2.

5. Insert rows into incidents table. 

    - Remember you need to use <DOMAIN_NAME>.<ENUM_VALUE> when assigning a value to severity column or insert/update command will raise ORA-11534.