Oracle Database 23c–Fine Grained Locking–Lock Free Reservations - Demonstration
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:
login as: oracle
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
Copyright (c) 1982, 2023, Oracle. All rights reserved.
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
Copyright (c) 1982, 2023, Oracle. All rights reserved.
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:
login as: oracle
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 ~]$
[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
Copyright (c) 1982, 2023, Oracle. All rights reserved.
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:
login as: oracle
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
Copyright (c) 1982, 2023, Oracle. All rights reserved.
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:
login as: oracle
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
Copyright (c) 1982, 2023, Oracle. All rights reserved.
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>
Post a Comment: