Anas Darkal

-->

Latest Posts

Aug 22, 2024 4:26:00 PM

Oracle CloudWorld 2024: What to Expect Around Oracle 23ai

Oracle 23ai Oracle database 23ai Oracle cloudworld

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 !!!.

Aug 21, 2024 4:23:00 PM

Oracle 23ai: Lock Free Reservations Capability

Oracle 23ai Oracle database 23ai Oracle cloudworld

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..

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,

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" loading="lazy">

Aug 20, 2024 1:00:00 PM

Oracle 23ai: Unrestricted Parallel DMLs

Oracle database Oracle 23ai

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 to Oracle..

Jul 10, 2024 5:17:00 PM

Oracle Database 23ai: Fetch Top-N Rows Per Group Queries

Oracle Oracle database Oracle 23ai Oracle database 23ai

Overview: Oracle 23ai introduces partition by clause in fetch first clause to get top-N rows per group. A query’s syntax SELECT …….. FROM …….. ORDER BY , FETCH FIRST , ROWS ONLY..

Jun 23, 2024 5:24:00 PM

Oracle 23ai: JSON Schema Support

Oracle Oracle database Oracle 23ai Oracle database 23ai Json

Overview: Oracle first introduced the ability to display a JSON schema when they introduced the JSON Data in Oracle 12.2 but without the option to validate the structure of JSON..

Jun 2, 2024 6:02:00 PM

Oracle 23ai: Enumeration Domains – List of Values in the Database

Oracle Databases Oracle 23ai Oracle database 23ai

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..

May 30, 2024 6:05:00 PM

Oracle 23ai: Quick Overview

Oda Oracle Oracle 23ai Oracle database 23ai

Oracle Database 23ai Oracle Database 23ai is the next long-term support release of Oracle Database It brings AI to your data with the addition of AI Vector search to Oracle’s..

May 20, 2024 6:07:00 PM

Oracle 23ai: Bigfile Tablespace Shrink

Oracle Oracle 23ai Oracle database 23ai Bigfile

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..

May 1, 2024 6:29:00 PM

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

Oracle database Databases Oracle 23ai Oracle database 23ai

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..

Apr 30, 2024 6:31:00 PM

Oracle 23ai: Read-Only Users and Sessions

Oracle Oracle 23ai Oracle database 23ai Developer

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..