Sachin Ramesh Oracle DBA Blog

Monday, December 11, 2023

Oracle Database 23c–Fine Grained Locking–Lock Free Reservations - Demonstration

December 11, 2023

Oracle Database 23c–Fine Grained Locking–Lock Free Reservations:

Session 1 inserts a record and commits it. Session 1 performs an update on this record. And does not yet commit its transaction. Session 2 tries to update that same record …. and is able to! It does not run into a lock. It can perform the update, almost as if session 1 does not exist.

The traditional behavior of the Oracle Database is to lock a row as soon as it is updated. Some databases lock a table or a page (set of records) but from its incarnation, Oracle Database has always been very fine grained by only locking the row in question. However, this example shows that in release 23c it can lock even less than a single record. It does lock – make no mistake

When I invoked a third session and perform two more updates, you will see that the these transactions are not isolated from each other. It is not wild west, free for all. There are still rules enforced.

Step 4 in session B selects the current capacity and finds that the value in column capacity is still at the original 2000 despite the update in session B just prior to this query that took 800 from that capacity.

Step 5 in session C reduces the capacity by another 400 – although a query for the current capacity in that session would also return the original 2000. This update is accepted – again while both sessions A and B have made their own claims on that ticket capacity. Still no row lock preventing session C from making the update. However in step 6, when session C tries to get hold of another 700 (tickets), we finally run into a limitation: a Check Constraint violation. Not shown yet: there is a Check Constraint defined against table ticketsales that prevents the value of capacity from getting below 10.

Despite the fact that the value of column capacity for this record is still at 2000, there are claims (“reservations”) from session A (200), session B (300) and session C (400) that have virtually drained 900 from that original 2000, meaning that the 600 that was attempted to take out in step 7 were no longer available.

Let’s now commit session A (step 8) – definitively getting hold of the 200- , rollback session B (step 9) – releasing the claim of 300 – and trying to claim those 600 tickets again in session C (step 10).

What we have seen in action here is Lock-Free Reservations. A feature that allows us to have the Oracle Database manage pools of resources – tickets, seats, account balance, lottery tickets – without locking database records. To have multiple transactions each take out a claim on a slice of the resource pool, they do not have to lock the record that describes that particular pool – thereby excluding other transactions until the transaction is either committed or rolled back. They can instead create a reservation, that guarantees that the slice they requested is available by the time they commit. Oracle Database makes sure that not more reservations can be created than the pool can sustain. Many transactions can hold reservations on the same pool [record] at the same time: they do not interfere. The allows for a much higher degree of concurrency than is possible with table, page or even record locking.

Working with a resource pool with lock-free reservations is quite straightforward:

define a column as reservable. This must be a numeric column define check constraint(s) to control the allowable values for the column (usually limiting the lower or upper capacity limit; note: check constraints can compare non reservable (regular) columns with reservable columns access the record to be updated using its primary / unique key – make sure the update is a single row statement do not use for update of when updating the reservable column’s value (as that would defeat the purpose) only use set column = column + claim or set column = column – claim to claim part of the capacity; do not use set column = value the pool can be replenished; capacity can be added for example. However, the transaction that adds capacity needs to be committed before it will have an effect on additional reservations that can be made by other transactions.

Oracle Database creates a “Reservation Journal Table” – SYS_RESERVJRNL_<object_number_of_base_table> – that records the claims made against a resource pool. This table behaves like a global temporary table: each session only sees its own claims. When the session commits (or rolls back) the table is cleared. Flashback query does not return values from this table and even SYS cannot look across sessions to find all currently held reservations

In very brief the definition of the table used in this example:

SQL> CREATE TABLE TICKETSALES(ID NUMBER PRIMARY KEY, NAME VARCHAR2(100),CAPACITY NUMBER RESERVABLE CONSTRAINT MINIMUM_CAPACITY CHECK (CAPACITY >= 10));

Column CAPACITY is defined as reservable and it appear in the check constraint minimum_capacity that enforces the rule that the capacity should never be lower than 10.

The Reservation Journal Table created in this case:

(where 78947 is the OBJECT_ID found for table TICKETSALES in a query against USER_OBJECTS WHERE OBJECT_NAME=’TICKETSALES’).

Here is an example of the information available to the current transaction regarding its reservations:

SQL> UPDATE DEMO.TICKETSALES SET CAPACITY=CAPACITY - 300 WHERE ID=1;

SQL> SELECT * FROM SYS_RESERVJRNL_OBJ_ID;

SQL> UPDATE DEMO.TICKETSALES SET CAPACITY=CAPACITY - 150 WHERE ID=1;

SQL> SELECT * FROM SYS_RESERVJRNL_OBJ_ID;

SQL> SELECT * FROM TICKETSALES;

These Lock-Free Reservations are among the most intriguing application development oriented features in the latest Oracle Database release. Transactions vying for the resources from the same collection – without heavy handedly locking each other out on a record lock. It seems quite elegant and useful. There is of course much more to be looked into. (When) do row level triggers fire for example. Can we define triggers on the journal table – and would that ever make sense? How can we find out what the maximum claim is a transaction can make? Just trying until we hit a limit? Can we intercept ‘release of a claim’ or ‘replenishment of a resource’?

DEMONSTRATION STEPS:

SQL> create user demo identified by demo12345;

User created.

SQL> grant connect, create session to demo;

Grant succeeded.

SQL> alter user demo default tablespace users;

User altered.

SQL> alter user demo temporary tablespace temp;

User altered.

SQL> grant sysdba, dba to demo;

Grant succeeded.

SQL> create user test1 identified by test12345;

User created.

SQL> grant connect, create session to test1;

Grant succeeded.

SQL> grant select, insert, update, delete on DEMO.TICKETSALES to test1;

Grant succeeded.

SQL> create user test2 identified by test12345;

User created.

SQL> grant connect, create session to test2;

Grant succeeded.

SQL> grant select, insert, update, delete on DEMO.TICKETSALES to test2;

Grant succeeded.

SQL> create user test3 identified by test12345;

User created.

SQL> grant connect, create session to test3;

Grant succeeded.

SQL> grant select, insert, update, delete on DEMO.TICKETSALES to test3;

Grant succeeded.

SQL> col username for a10

SQL> col account_status for a10

SQL> select username,account_status from dba_users where username in ('TEST1','TEST2','TEST3');

USERNAME ACCOUNT_ST

---------- ----------

TEST1 OPEN

TEST2 OPEN

TEST3 OPEN

SQL> grant update any table to TEST1,TEST2,TEST3;

Grant succeeded.

SQL> grant select any table to TEST1,TEST2,TEST3;

Grant succeeded.

SQL> grant select, insert, update, delete on "DEMO"."SYS_RESERVJRNL_78959" to TEST1,TEST2,TEST3;

Grant succeeded.

SESSION - A:

STEP1:

SQL> INSERT INTO DEMO.TICKETSALES VALUES (1, 'Instagram Live', 2000);

STEP2:

SQL> COMMIT;

STEP3:

SQL> UPDATE DEMO.TICKETSALES SET CAPACITY=CAPACITY - 200 WHERE ID=1;

STEP8:

SQL> COMMIT;

SQL> SELECT CAPACITY FROM DEMO.TICKETSALES WHERE ID=1;

SESSION - B:

STEP4:

SQL> UPDATE DEMO.TICKETSALES SET CAPACITY=CAPACITY - 800 WHERE ID=1;

STEP5:

SQL> SELECT CAPACITY FROM DEMO.TICKETSALES WHERE ID=1;

STEP9:

SQL> ROLLBACK;

SQL> SELECT CAPACITY FROM DEMO.TICKETSALES WHERE ID=1;

SESSION - C:

STEP6:

SQL> UPDATE DEMO.TICKETSALES SET CAPACITY=CAPACITY - 500 WHERE ID=1;

STEP7:

SQL> UPDATE DEMO.TICKETSALES SET CAPACITY=CAPACITY - 700 WHERE ID=1;

STEP10:

SQL> UPDATE DEMO.TICKETSALES SET CAPACITY=CAPACITY - 700 WHERE ID=1;

Main Session Log:

oracle@192.168.56.5's password:

Web console: https://srlab.localdomain:9090/

Last login: Sat Nov 11 12:24:09 2023 from 192.168.56.1

[oracle@srlab ~]$ ps -ef |grep pmon

oracle 4632 1 0 10:37 ? 00:00:06 db_pmon_FREE

oracle 25930 25638 0 17:12 pts/1 00:00:00 grep --color=auto pmon

[oracle@srlab ~]$ ps -ef |grep tns

root 21 2 0 10:36 ? 00:00:00 [netns]

oracle 2153 1 0 10:37 ? 00:00:01 /opt/oracle/product/23c/dbhomeFree/bin/tnslsnr LISTENER -inherit

oracle 25942 25638 0 17:12 pts/1 00:00:00 grep --color=auto tns

[oracle@srlab ~]$

[oracle@srlab ~]$ . oraenv

ORACLE_SID = [oracle] ? FREE

The Oracle base has been set to /opt/oracle

[oracle@srlab ~]$

[oracle@srlab ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Sat Nov 11 17:13:32 2023

Version 23.2.0.0.0

Connected to:

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

Version 23.2.0.0.0

SQL> select * from global_name;

GLOBAL_NAME

--------------------------

SRLAB23C

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 SRLABPLUG READ WRITE NO

SQL> alter session set container=SRLABPLUG;

Session altered.

SQL>

SQL> set lines 300 pages 2000

SQL>

SQL> create user demo identified by demo12345 default tablespace users temporary tablespace temp;

User created.

SQL> grant connect, create sessions, sysdba, dba to demo;

grant connect, create sessions, sysdba, dba to demo

ERROR at line 1:

ORA-00990: missing or invalid privilege

SQL> grant connect, create session, sysdba, dba to demo;

Grant succeeded.

SQL>

SQL> col username for a10

SQL> col account_status for a10

SQL>

SQL> select username,account_status from dba_users where username='DEMO';

USERNAME ACCOUNT_ST

---------- ----------

DEMO OPEN

SQL> exit

Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

Version 23.2.0.0.0

[oracle@srlab ~]$ sqlplus demo@srlabplug

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Sat Nov 11 17:17:01 2023

Version 23.2.0.0.0

Enter password:

Connected to:

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

Version 23.2.0.0.0

SQL>

SQL> select * from global_name;

GLOBAL_NAME

-----------------------------------------------

SRLABPLUG

SQL> sho user

USER is "DEMO"

SQL>

SQL> create table ticketsales(id number primary key, name varchar2(100), capacity number reservable constraint minimum_capacity check (capacity>=10));

Table created.

SQL> col owner for a10

SQL> col object_name for a15

SQL> select owner,object_name,object_type,last_ddl_time,status from dba_objects where owner='DEMO';

OWNER OBJECT_NAME OBJECT_TYPE LAST_DDL_ STATUS

---------- --------------- ----------------------- --------- -------

DEMO TICKETSALES TABLE 11-NOV-23 VALID

DEMO SYS_RESERVJRNL_ TABLE 11-NOV-23 VALID

78965

DEMO SYS_C008232 INDEX 11-NOV-23 VALID

SQL> col object_name for a40

SQL> /

OWNER OBJECT_NAME OBJECT_TYPE

---------- ---------------------------------------- -----------------------

LAST_DDL_ STATUS

--------- -------

DEMO TICKETSALES TABLE

11-NOV-23 VALID

DEMO SYS_RESERVJRNL_78965 TABLE

11-NOV-23 VALID

DEMO SYS_C008232 INDEX

11-NOV-23 VALID

SQL> set lines 300 pages 200

SQL> /

OWNER OBJECT_NAME OBJECT_TYPE LAST_DDL_ STATUS

---------- ---------------------------------------- ----------------------- --------- -------

DEMO TICKETSALES TABLE 11-NOV-23 VALID

DEMO SYS_RESERVJRNL_78965 TABLE 11-NOV-23 VALID

DEMO SYS_C008232 INDEX 11-NOV-23 VALID

SQL> set lines 100

SQL> desc DEMO.TICKETSALES

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

ID NOT NULL NUMBER

NAME VARCHAR2(100)

CAPACITY NOT NULL NUMBER

SQL> select owner,object_type,status,count(*) from dba_objects where owner='DEMO' group by owner,object_type,status;

OWNER OBJECT_TYPE STATUS COUNT(*)

---------- ----------------------- ------- ----------

DEMO TABLE VALID 2

DEMO INDEX VALID 1

SQL>

SQL> conn / as sysdba

Connected.

SQL>

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 SRLABPLUG READ WRITE NO

SQL>

SQL> alter session set container=SRLABPLUG;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

3 SRLABPLUG READ WRITE NO

SQL>

SQL> create user test1 identified by test12345;

create user test1 identified by test12345

ERROR at line 1:

ORA-01920: user name 'TEST1' conflicts with another user or role name

SQL> drop user test1 cascade;

User dropped.

SQL> drop user test2 cascade;

User dropped.

SQL> drop user test3 cascade;

User dropped.

SQL> create user test1 identified by test12345;

User created.

SQL> create user test2 identified by test12345;

User created.

SQL> create user test3 identified by test12345;

User created.

SQL> grant connect, create session to test1, test2, test3;

Grant succeeded.

SQL> grant select,insert,update,delete on demo.ticketsales to test1,test2,test3;

Grant succeeded.

SQL> grant select,insert,update,delete on demo.SYS_RESERVJRNL_78965 to test1,test2,test3;

Grant succeeded.

SQL> col username for a10

SQL> col acount_status for a10

SQL>

SQL> select username,account_status from dba_users where username in ('TEST1','TEST2','TEST3');

USERNAME ACCOUNT_STATUS

---------- --------------------------------

TEST1 OPEN

TEST2 OPEN

TEST3 OPEN

SQL> col grantee for a10

SQL> col grantor for a10

SQL> col privilege for a10

SQL> col owner for a10

SQL> col table_name for a40

SQL> select * from dba_tab_privs where grantee in ('TEST1','TEST2','TEST3');

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE COM

---------- ---------- ---------------------------------------- ---------- ---------- --- --- ---

TYPE INH

------------------------ ---

TEST1 DEMO TICKETSALES DEMO DELETE NO NO NO

TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO DELETE NO NO NO

TABLE NO

TEST2 DEMO TICKETSALES DEMO DELETE NO NO NO

TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO DELETE NO NO NO

TABLE NO

TEST3 DEMO TICKETSALES DEMO DELETE NO NO NO

TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO DELETE NO NO NO

TABLE NO

TEST1 DEMO TICKETSALES DEMO INSERT NO NO NO

TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO INSERT NO NO NO

TABLE NO

TEST2 DEMO TICKETSALES DEMO INSERT NO NO NO

TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO INSERT NO NO NO

TABLE NO

TEST3 DEMO TICKETSALES DEMO INSERT NO NO NO

TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO INSERT NO NO NO

TABLE NO

TEST1 DEMO TICKETSALES DEMO SELECT NO NO NO

TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO SELECT NO NO NO

TABLE NO

TEST2 DEMO TICKETSALES DEMO SELECT NO NO NO

TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO SELECT NO NO NO

TABLE NO

TEST3 DEMO TICKETSALES DEMO SELECT NO NO NO

TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO SELECT NO NO NO

TABLE NO

TEST1 DEMO TICKETSALES DEMO UPDATE NO NO NO

TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO UPDATE NO NO NO

TABLE NO

TEST2 DEMO TICKETSALES DEMO UPDATE NO NO NO

TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO UPDATE NO NO NO

TABLE NO

TEST3 DEMO TICKETSALES DEMO UPDATE NO NO NO

TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO UPDATE NO NO NO

TABLE NO

24 rows selected.

SQL> col tabel_name for a30

SQL> /

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE COM

---------- ---------- ---------------------------------------- ---------- ---------- --- --- ---

TYPE INH

------------------------ ---

TEST1 DEMO TICKETSALES DEMO DELETE NO NO NO

TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO DELETE NO NO NO

TABLE NO

TEST2 DEMO TICKETSALES DEMO DELETE NO NO NO

TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO DELETE NO NO NO

TABLE NO

TEST3 DEMO TICKETSALES DEMO DELETE NO NO NO

TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO DELETE NO NO NO

TABLE NO

TEST1 DEMO TICKETSALES DEMO INSERT NO NO NO

TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO INSERT NO NO NO

TABLE NO

TEST2 DEMO TICKETSALES DEMO INSERT NO NO NO

TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO INSERT NO NO NO

TABLE NO

TEST3 DEMO TICKETSALES DEMO INSERT NO NO NO

TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO INSERT NO NO NO

TABLE NO

TEST1 DEMO TICKETSALES DEMO SELECT NO NO NO

TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO SELECT NO NO NO

TABLE NO

TEST2 DEMO TICKETSALES DEMO SELECT NO NO NO

TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO SELECT NO NO NO

TABLE NO

TEST3 DEMO TICKETSALES DEMO SELECT NO NO NO

TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO SELECT NO NO NO

TABLE NO

TEST1 DEMO TICKETSALES DEMO UPDATE NO NO NO

TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO UPDATE NO NO NO

TABLE NO

TEST2 DEMO TICKETSALES DEMO UPDATE NO NO NO

TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO UPDATE NO NO NO

TABLE NO

TEST3 DEMO TICKETSALES DEMO UPDATE NO NO NO

TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO UPDATE NO NO NO

TABLE NO

24 rows selected.

SQL> set lines 300 pages 200

SQL> /

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE COM TYPE INH

---------- ---------- ---------------------------------------- ---------- ---------- --- --- --- ------------------------ ---

TEST1 DEMO TICKETSALES DEMO DELETE NO NO NO TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO DELETE NO NO NO TABLE NO

TEST2 DEMO TICKETSALES DEMO DELETE NO NO NO TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO DELETE NO NO NO TABLE NO

TEST3 DEMO TICKETSALES DEMO DELETE NO NO NO TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO DELETE NO NO NO TABLE NO

TEST1 DEMO TICKETSALES DEMO INSERT NO NO NO TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO INSERT NO NO NO TABLE NO

TEST2 DEMO TICKETSALES DEMO INSERT NO NO NO TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO INSERT NO NO NO TABLE NO

TEST3 DEMO TICKETSALES DEMO INSERT NO NO NO TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO INSERT NO NO NO TABLE NO

TEST1 DEMO TICKETSALES DEMO SELECT NO NO NO TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO SELECT NO NO NO TABLE NO

TEST2 DEMO TICKETSALES DEMO SELECT NO NO NO TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO SELECT NO NO NO TABLE NO

TEST3 DEMO TICKETSALES DEMO SELECT NO NO NO TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO SELECT NO NO NO TABLE NO

TEST1 DEMO TICKETSALES DEMO UPDATE NO NO NO TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO UPDATE NO NO NO TABLE NO

TEST2 DEMO TICKETSALES DEMO UPDATE NO NO NO TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO UPDATE NO NO NO TABLE NO

TEST3 DEMO TICKETSALES DEMO UPDATE NO NO NO TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO UPDATE NO NO NO TABLE NO

24 rows selected.

SQL> col TABLE_NAME for a20

SQL> /

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE COM TYPE INH

---------- ---------- -------------------- ---------- ---------- --- --- --- ------------------------ ---

TEST1 DEMO TICKETSALES DEMO DELETE NO NO NO TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO DELETE NO NO NO TABLE NO

TEST2 DEMO TICKETSALES DEMO DELETE NO NO NO TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO DELETE NO NO NO TABLE NO

TEST3 DEMO TICKETSALES DEMO DELETE NO NO NO TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO DELETE NO NO NO TABLE NO

TEST1 DEMO TICKETSALES DEMO INSERT NO NO NO TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO INSERT NO NO NO TABLE NO

TEST2 DEMO TICKETSALES DEMO INSERT NO NO NO TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO INSERT NO NO NO TABLE NO

TEST3 DEMO TICKETSALES DEMO INSERT NO NO NO TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO INSERT NO NO NO TABLE NO

TEST1 DEMO TICKETSALES DEMO SELECT NO NO NO TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO SELECT NO NO NO TABLE NO

TEST2 DEMO TICKETSALES DEMO SELECT NO NO NO TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO SELECT NO NO NO TABLE NO

TEST3 DEMO TICKETSALES DEMO SELECT NO NO NO TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO SELECT NO NO NO TABLE NO

TEST1 DEMO TICKETSALES DEMO UPDATE NO NO NO TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO UPDATE NO NO NO TABLE NO

TEST2 DEMO TICKETSALES DEMO UPDATE NO NO NO TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO UPDATE NO NO NO TABLE NO

TEST3 DEMO TICKETSALES DEMO UPDATE NO NO NO TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO UPDATE NO NO NO TABLE NO

24 rows selected.

SQL> col TYPE for a10

SQL> /

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE COM TYPE INH

---------- ---------- -------------------- ---------- ---------- --- --- --- ---------- ---

TEST1 DEMO TICKETSALES DEMO DELETE NO NO NO TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO DELETE NO NO NO TABLE NO

TEST2 DEMO TICKETSALES DEMO DELETE NO NO NO TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO DELETE NO NO NO TABLE NO

TEST3 DEMO TICKETSALES DEMO DELETE NO NO NO TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO DELETE NO NO NO TABLE NO

TEST1 DEMO TICKETSALES DEMO INSERT NO NO NO TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO INSERT NO NO NO TABLE NO

TEST2 DEMO TICKETSALES DEMO INSERT NO NO NO TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO INSERT NO NO NO TABLE NO

TEST3 DEMO TICKETSALES DEMO INSERT NO NO NO TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO INSERT NO NO NO TABLE NO

TEST1 DEMO TICKETSALES DEMO SELECT NO NO NO TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO SELECT NO NO NO TABLE NO

TEST2 DEMO TICKETSALES DEMO SELECT NO NO NO TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO SELECT NO NO NO TABLE NO

TEST3 DEMO TICKETSALES DEMO SELECT NO NO NO TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO SELECT NO NO NO TABLE NO

TEST1 DEMO TICKETSALES DEMO UPDATE NO NO NO TABLE NO

TEST1 DEMO SYS_RESERVJRNL_78965 DEMO UPDATE NO NO NO TABLE NO

TEST2 DEMO TICKETSALES DEMO UPDATE NO NO NO TABLE NO

TEST2 DEMO SYS_RESERVJRNL_78965 DEMO UPDATE NO NO NO TABLE NO

TEST3 DEMO TICKETSALES DEMO UPDATE NO NO NO TABLE NO

TEST3 DEMO SYS_RESERVJRNL_78965 DEMO UPDATE NO NO NO TABLE NO

24 rows selected.

SQL> l

1* select * from dba_tab_privs where grantee in ('TEST1','TEST2','TEST3')

SQL> select * from demo.SYS_RESERVJRNL_78965;

no rows selected

SQL>

Test1 session log:

oracle@192.168.56.5's password:

Web console: https://srlab.localdomain:9090/

Last login: Sat Nov 11 17:02:44 2023 from 192.168.56.1

[oracle@srlab ~]$

[oracle@srlab ~]$ . oraenv

ORACLE_SID = [oracle] ? FREE

The Oracle base has been set to /opt/oracle

[oracle@srlab ~]$

[oracle@srlab ~]$ sqlplus test1@srlabplug

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Sat Nov 11 17:30:12 2023

Version 23.2.0.0.0

Enter password:

Connected to:

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

Version 23.2.0.0.0

SQL> show user

USER is "TEST1"

SQL> select * from global_name;

GLOBAL_NAME

--------------------------------------------------------------------

SRLABPLUG

SQL> insert into demo.ticketsales values (1, 'ARR Concert', 2000);

1 row created.

SQL> commit;

Commit complete.

SQL> update demo.ticketsales set capacity=capacity - 200 where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL>

Test2 session log:

oracle@192.168.56.5's password:

Web console: https://srlab.localdomain:9090/

Last login: Sat Nov 11 17:29:56 2023 from 192.168.56.1

[oracle@srlab ~]$

[oracle@srlab ~]$ . oraenv

ORACLE_SID = [oracle] ? FREE

The Oracle base has been set to /opt/oracle

[oracle@srlab ~]$

[oracle@srlab ~]$ sqlplus test2@srlabplug

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Sat Nov 11 17:33:16 2023

Version 23.2.0.0.0

Enter password:

Connected to:

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

Version 23.2.0.0.0

SQL> show user

USER is "TEST2"

SQL> select * from global_name;

GLOBAL_NAME

--------------------------------------------------------------------------------

SRLABPLUG

SQL> update demo.ticketsales set capacity=capacity - 800 where id=1;

1 row updated.

SQL> select capacity from demo.ticketsales where id=1;

CAPACITY

----------

2000

SQL> rollback;

Rollback complete.

SQL> select capacity from demo.ticketsales where id=1;

CAPACITY

----------

1800

SQL> select capacity from demo.ticketsales where id=1;

CAPACITY

----------

600

SQL>

Test3 Session Log:

oracle@192.168.56.5's password:

Web console: https://srlab.localdomain:9090/

Last login: Sat Nov 11 17:33:02 2023 from 192.168.56.1

[oracle@srlab ~]$ . oraenv

ORACLE_SID = [oracle] ? FREE

The Oracle base has been set to /opt/oracle

[oracle@srlab ~]$

[oracle@srlab ~]$ sqlplus test3@srlabplug

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Sat Nov 11 17:37:09 2023

Version 23.2.0.0.0

Enter password:

Connected to:

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

Version 23.2.0.0.0

SQL> show user

USER is "TEST3"

SQL> select * from global_name;

GLOBAL_NAME

-------------------------------------------------------------

SRLABPLUG

SQL>

SQL> update demo.ticketsales set capacity=capacity - 500 where id=1;

1 row updated.

SQL> update demo.ticketsales set capaoity=capacity - 700 where id=1;

update demo.ticketsales set capaoity=capacity - 700 where id=1

ERROR at line 1:

ORA-00904: "CAPAOITY": invalid identifier

SQL> update demo.ticketsales set capacity=capacity - 700 where id=1;

update demo.ticketsales set capacity=capacity - 700 where id=1

ERROR at line 1:

ORA-02290: check constraint (DEMO.MINIMUM_CAPACITY) violated

SQL> update demo.ticketsales set capacity=capacity - 700 where id=1;

1 row updated.

SQL> select * from demo.SYS_RESERVJRNL_78965;

ORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_STMT_TYPE$

-------------------------------- ---------------- ------------ ----------------

ID CAPACIT CAPACITY_RESERVED

---------- ------- -----------------

0800210059020000 ACTIVE UPDATE

1 - 500

0800210059020000 ACTIVE UPDATE

1 - 700

SQL> set lines 100

SQL> desc demo.ticketsales

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

ID NOT NULL NUMBER

NAME VARCHAR2(100)

CAPACITY NOT NULL NUMBER

SQL> commit;

Commit complete.

SQL> select * from demo.SYS_RESERVJRNL_78965;

no rows selected

SQL>

Saturday, October 28, 2023

Oracle 23c — Installing Oracle Database 23c Free through RPM package on Oracle Linux 8.8

October 28, 2023

I am using VirtualBox 6.1 for my database installations, each running on an Oracle Linux VM. Here is the step by step demonstration to install Oracle Database 23c Free on Oracle Linux 8.

Here we go..

Download and Install Oracle Linux 8 from https://yum.oracle.com/oracle-linux-isos.html
Download the database pre-install package: https://yum.oracle.com/repo/OracleLinux/OL8/developer/x86_64/getPackage/oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm
and install it as root in your OL8 machine:
[root@srlab sf_Oracle]# cd 23c
[root@srlab 23c]# ls -lrt
total 1717588
-rwxrwx — -. 1 root vboxsf 1758776440 Apr 4 2023 oracle-database-free-23c-1.0–1.el8.x86_64.rpm
-rwxrwx — -. 1 root vboxsf 30688 Oct 27 23:19 oracle-database-preinstall-23c-1.0–0.5.el8.x86_64.rpm

[root@srlab 23c]# yum install oracle-database-preinstall-23c-1.0–0.5.el8.x86_64.rpm
Last metadata expiration check: 15:20:02 ago on Fri 27 Oct 2023 09:09:24 AM IST.
Dependencies resolved.
======================================================================================================================================================
Package Architecture Version Repository Size
======================================================================================================================================================
Installing:
oracle-database-preinstall-23c x86_64 1.0–0.5.el8 @commandline 30 k
Installing dependencies:
compat-openssl10 x86_64 1:1.0.2o-4.el8_6 ol8_appstream 1.1 M
ksh x86_64 20120801–257.0.1.el8 ol8_appstream 929 k
libnsl x86_64 2.28–225.0.4.el8_8.6 ol8_baseos_latest 108 k

Transaction Summary
======================================================================================================================================================
Install 4 Packages

Total size: 2.2 M
Total download size: 2.1 M
Installed size: 6.3 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): libnsl-2.28–225.0.4.el8_8.6.x86_64.rpm 159 kB/s | 108 kB 00:00
(2/3): ksh-20120801–257.0.1.el8.x86_64.rpm 693 kB/s | 929 kB 00:01
(3/3): compat-openssl10–1.0.2o-4.el8_6.x86_64.rpm 685 kB/s | 1.1 MB 00:01
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
Total 1.2 MB/s | 2.1 MB 00:01
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : ksh-20120801–257.0.1.el8.x86_64 1/4
Running scriptlet: ksh-20120801–257.0.1.el8.x86_64 1/4
Installing : compat-openssl10–1:1.0.2o-4.el8_6.x86_64 2/4
Running scriptlet: compat-openssl10–1:1.0.2o-4.el8_6.x86_64 2/4
/sbin/ldconfig: /etc/ld.so.conf.d/kernel-5.4.17–2011.1.2.el8uek.x86_64.conf:6: hwcap directive ignored

Installing : libnsl-2.28–225.0.4.el8_8.6.x86_64 3/4
Installing : oracle-database-preinstall-23c-1.0–0.5.el8.x86_64 4/4
Running scriptlet: oracle-database-preinstall-23c-1.0–0.5.el8.x86_64 4/4
/sbin/ldconfig: /etc/ld.so.conf.d/kernel-5.4.17–2011.1.2.el8uek.x86_64.conf:6: hwcap directive ignored

Verifying : libnsl-2.28–225.0.4.el8_8.6.x86_64 1/4
Verifying : compat-openssl10–1:1.0.2o-4.el8_6.x86_64 2/4
Verifying : ksh-20120801–257.0.1.el8.x86_64 3/4
Verifying : oracle-database-preinstall-23c-1.0–0.5.el8.x86_64 4/4

Installed:
compat-openssl10–1:1.0.2o-4.el8_6.x86_64 ksh-20120801–257.0.1.el8.x86_64 libnsl-2.28–225.0.4.el8_8.6.x86_64
oracle-database-preinstall-23c-1.0–0.5.el8.x86_64

Complete!

3. Download the Oracle Database 23c Free RPM:
https://download.oracle.com/otn-pub/otn_software/db-free/oracle-database-free-23c-1.0-1.el8.x86_64.rpm
and install it as root:

[root@srlab 23c]# yum install -y oracle-database-free-23c-1.0–1.el8.x86_64.rpm
Last metadata expiration check: 15:24:45 ago on Fri 27 Oct 2023 09:09:24 AM IST.
Dependencies resolved.
======================================================================================================================================================
Package Architecture Version Repository Size
======================================================================================================================================================
Installing:
oracle-database-free-23c x86_64 1.0–1 @commandline 1.6 G

Transaction Summary
======================================================================================================================================================
Install 1 Package

Total size: 1.6 G
Installed size: 5.2 G
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Running scriptlet: oracle-database-free-23c-1.0–1.x86_64 1/1
Installing : oracle-database-free-23c-1.0–1.x86_64 1/1
Running scriptlet: oracle-database-free-23c-1.0–1.x86_64 1/1
[INFO] Executing post installation scripts…
[INFO] Oracle home installed successfully and ready to be configured.
To configure Oracle Database Free, optionally modify the parameters in ‘/etc/sysconfig/oracle-free-23c.conf’ and then run ‘/etc/init.d/oracle-free-23c configure’ as root.

/sbin/ldconfig: /etc/ld.so.conf.d/kernel-5.4.17–2011.1.2.el8uek.x86_64.conf:6: hwcap directive ignored

Verifying : oracle-database-free-23c-1.0–1.x86_64 1/1

Installed:
oracle-database-free-23c-1.0–1.x86_64

Complete!

4. Run the initialization script as directed, entering the privileged user password when prompted:
[root@srlab ~]# /etc/init.d/oracle-free-23c configure
Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0–9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:
Confirm the password:
Configuring Oracle Listener.
ORACLEListener configuration succeeded.
Configuring Oracle Database FREE.
_Enter SYS user password:
**********
Enter SYSTEM user password:
********
Enter PDBADMIN User Password:
*********
Prepare for db operation
7% complete
Copying database files
29% complete
Creating and starting Oracle instance
30% complete
33% complete
36% complete
39% complete
43% complete
Completing Database Creation
47% complete
49% complete
50% complete
Creating Pluggable Databases
54% complete
71% complete
Executing Post Configuration Actions
93% complete
Running Custom Scripts
100% complete
Database creation complete. For details check the logfiles at:
/opt/oracle/cfgtoollogs/dbca/FREE.
Database Information:
Global Database Name:FREE
System Identifier(SID):FREE
Look at the log file “/opt/oracle/cfgtoollogs/dbca/FREE/FREE.log” for further details.

Connect to Oracle Database using one of the connect strings:
Pluggable database: srlab.localdomain/FREEPDB1
Multitenant container database: srlab.localdomain
[root@srlab ~]# ps -ef |grep pmon
oracle 9147 1 0 13:58 ? 00:00:00 db_pmon_FREE
root 9987 6062 0 14:14 pts/1 00:00:00 grep — color=auto pmon
[root@srlab ~]# ps -ef |grep tns
root 21 2 0 13:26 ? 00:00:00 [netns]
oracle 6172 1 0 13:42 ? 00:00:00 /opt/oracle/product/23c/dbhomeFree/bin/tnslsnr LISTENER -inherit
root 9997 6062 0 14:14 pts/1 00:00:00 grep — color=auto tns

5. Make sure the DB is auto-started when the machine boots:
[root@srlab ~]# systemctl enable oracle-free-23c
oracle-free-23c.service is not a native service, redirecting to systemd-sysv-install.
Executing: /usr/lib/systemd/systemd-sysv-install enable oracle-free-23c

6. Edit /etc/oratab and change N to Y in the FREE line:

[root@srlab ~]# cat /etc/oratab

FREE:/opt/oracle/product/23c/dbhomeFree:Y

7. Open up a firewall port so the DB can be accessed from other machines:
[root@srlab ~]# firewall-cmd — permanent — add-port=1521/tcp
success
[root@srlab ~]# firewall-cmd — reload
success

8. Reboot the VM (in my case). I can then connect to the DB from my host machine like sqlplus system@srlab.localhost/freepdb1

Monday, August 28, 2023

What’s New in Oracle Database 23c - Group By Alias - Demonstration

August 28, 2023

What’s New in Oracle Database 23c - Group By Alias - Demonstration!

Oracle Database 23c delivers the most complete and simple converged database for developers looking to build new microservice, graph, document, and relational applications.
Since Oracle's 23C is a long term release, the database has already been released for developers on April 6th, 2023. Oracle Cloud's non-production environments are updated on the first Friday of your quarterly update month.
Whether you are a Developer, a Data Scientist, a DBA, an Educator, or just interested in databases, Oracle Database 23c Free—Developer Release is the ideal way to get started.
Oracle will share new features updates about Oracle Database 23c with examples so you can get up and running quickly.

Okay, Let’s See the Feature Highlights…

GROUP BY column alias:

You can now use column alias or SELECT item position in GROUP BY, GROUP BY CUBE, GROUP BY ROLLUP, and GROUP BY GROUPING SETS clauses. Additionally, the HAVING clause supports column aliases. These new Database 23c enhancements make it easier to write GROUP BY and HAVING clauses, making SQL queries much more readable and maintainable while providing better SQL code portability.

In previous versions of Oracle, I could never understand why a GROUP BY clause in a SELECT statement does not allow a column alias or a position number. For ORDER BY this is allowed, but not for GROUP BY. For example, if you run the following query on an Oracle 19c database, it will cause an error message:

Demo:

In Oracle 19c Database:

SQL> create table pepsi_delivery (year number, pepsi varchar2(10), pepsi_type varchar2(10));

Table created.

SQL> insert into pepsi_delivery values (2020, '500 ml', 'Cane');

1 row created.

SQL> insert into pepsi_delivery values (2021, '300 ml', 'Cane');

1 row created.

SQL> insert into pepsi_delivery values (2022, '200 ml', 'Bottle');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT TO_CHAR(bottling_date, ‘YYYY’) year, pepsi, pepsi_type FROM pepsi_delivery GROUP BY year, pepsi, pepsi_type ORDER BY 1, 2, 3;

SELECT TO_CHAR(bottling_date, ‘YYYY’) year, pepsi, pepsi_type FROM pepsi_delivery GROUP BY year, pepsi, pepsi_type ORDER BY 1, 2, 3

ERROR at line 1:

ORA-00904: "???YYYY???": invalid identifier

The problem is the column alias year that is used in the SELECT clause. An alias is allowed in the ORDER BY clause, but not in the GROUP BY. There is no logical restriction for this – other relational databases support this syntax since years. In Oracle, we had to copy the expression of the SELECT clause into the GROUP BY clause to run the SQL statement:

SELECT year, pepsi, pepsi_type FROM pepsi_delivery GROUP BY 1, 2, 3 ORDER BY 1, 2, 3;

GROUP BY Column Alias or Position in Oracle 23c:

With Oracle 23c, this restriction does not exist anymore. Now, it is possible to use a column alias or the column position in a GROUP BY – in the same way as we know it from the ORDER BY clause. This was one of the first features I tested on the new 23c release, and it worked as expected for the column alias.

But for the positional notation, I got another error message:

SQL> SELECT year, pepsi, pepsi_type FROM pepsi_delivery GROUP BY 1, 2, 3 ORDER BY 1, 2, 3;

Error starting at line : 1 in command -

SELECT year, pepsi, pepsi_type FROM pepsi_delivery GROUP BY 1, 2, 3 ORDER BY 1, 2, 3

Error at Command Line : 1 Column : 8

Error report -

SQL Error: ORA-00979: "YEAR": must appear in the GROUP BY clause or be used in an aggregate

function

00979. 00000 - "not a GROUP BY expression"

*Cause:

*Action:

SQL>

To avoid this error, the parameter group_by_position_enabled must be set to TRUE (it is FALSE by default).

SQL> show parameter group_by

NAME TYPE VALUE

------------------------- ------- -----

group_by_position_enabled boolean FALSE

SQL>

SQL> ALTER SESSION SET group_by_position_enabled=TRUE;

Session altered.

SQL> show parameter group_by

NAME TYPE VALUE

------------------------- ------- -----

group_by_position_enabled boolean TRUE

SQL> SELECT year, pepsi, pepsi_type FROM pepsi_delivery GROUP BY 1, 2, 3 ORDER BY 1, 2, 3;

YEAR PEPSI PEPSI_TYPE

_______ _________ _____________

2020 500 ml Cane

2021 300 ml Cane

2022 200 ml Bottle

After that, you can use column names, column aliases or positions in the GROUP BY part of your queries. This is a nice little extension, but it can be useful to reduce the complexity of complex queries.

This is just one of many new features that were introduced with Oracle 23c. If you didn’t do yet, download and install the Oracle Database 23c Free – Developer Release. There you can play around with many other extensions of the new release. An overview of all new features you can find in the Oracle Database New Features manual of the Oracle 23c documentation. Also I am going to explain each and every new features in upcoming videos.

References:

https://docs.oracle.com/en/database/oracle/oracle-database/23/nfcoa/introduction.html#GUID-C852CD78-8F16-4449-8BE0-D85C80D38E3C

https://www.oracle.com/database/23c

Cheers!

Ramesh

Connect me on:

Blog: https://www.oralgwr.com/

LinkedIn: https://www.linkedin.com/in/ramesh-kumar-krishnamoorthy-3a67ba69/

Twitter: https://twitter.com/sachinrameshdba

Facebook: https://www.facebook.com/rameshkumar.krishnamoorthy.9/

Facebook Page: https://www.facebook.com/oraclef1

Instagram: https://www.instagram.com/oraclef1_1

YouTube: https://www.youtube.com/oraclef1

YouTube: https://www.youtube.com/oracledbatutorialtamil

Sachin Ramesh Oracle DBA Blog

Stat Counter

Blog Visit Count

AdSense

Popular Posts

Labels

Social Widget

Report Abuse

Total Pageviews

Blog Archive

Tags

Social Plugin

Subscribe Us

Popular Posts

Menu Footer Widget

About Me

Monday, December 11, 2023

Oracle Database 23c–Fine Grained Locking–Lock Free Reservations - Demonstration

Saturday, October 28, 2023

Oracle 23c — Installing Oracle Database 23c Free through RPM package on Oracle Linux 8.8

Monday, August 28, 2023

What’s New in Oracle Database 23c - Group By Alias - Demonstration