Posts

Oracle 26ai(23.26.2): WAIT and NOWAIT Support for DELETE, UPDATE, INSERT, and MERGE Statements

By -
  Rows modified by DML statements are locked and, if another session attempts to modify the same row, that session must wait until the row becomes available. In previous Oracle Database releases, the WAIT clause was supported only by the SELECT FOR UPDATE statement, enabling users to specify how long to wait for a row lock before the statement is rolled back. For example: Connected to Oracle Database 19 c Enterprise Edition Release 19.0 .0 .0 .0 SQL > select * from tbl for update wait 10 ; ORA -30006 : resource busy; acquire with WAIT timeout expired Similarly, the NOWAIT clause can be used to instruct the database to return immediately if the requested row is locked by another transaction: SQL > select * from tbl for update nowait; ORA -00054 : resource busy and acquire with NOWAIT specified or timeout expired If neither WAIT nor NOWAIT is specified, Oracle retains its default behavior and waits indefinitely for the row lock to become available: SQL > s...
Post a Comment
Read more

Oracle 26ai — Enhancements to Data Dictionary Views for Partitioning

By -
  In data dictionary views related to partitioning, the HIGH_VALUE column is one of the most frequently used attributes. However, its datatype is LONG , which comes with several limitations and restrictions. To address these limitations, Oracle AI Database 26ai introduces two additional columns, HIGH_VALUE_CLOB and HIGH_VALUE_JSON , allowing partition high values to be represented in more convenient formats. The following example creates an interval-partitioned table: SQL > create table tb( 2 id number, 3 date_ date 4 ) 5 partition by range ( date_ ) 6 interval ( numtoyminterval( 1 , 'YEAR' )) 7 ( 8 PARTITION p2022 VALUES LESS THAN (TO_DATE( '2023/01/01' , 'YYYY/MM/DD' )), 9 PARTITION p2023 VALUES LESS THAN (TO_DATE( '2024/01/01' , 'YYYY/MM/DD' )), 10 PARTITION p2024 VALUES LESS THAN (TO_DATE( '2025/01/01' , 'YYYY/MM/DD' )) 11 ); Table created To display partition ...
Post a Comment
Read more

Oracle 26ai(23.26.2): WAIT and NOWAIT Support for DELETE, UPDATE, INSERT, and MERGE Statements

By -
  Rows modified by DML statements are locked and, if another session attempts to modify the same row, that session must wait until the row becomes available. In previous Oracle Database releases, the WAIT clause was supported only by the SELECT FOR UPDATE statement, enabling users to specify how long to wait for a row lock before the statement is rolled back. For example: Connected to Oracle Database 19 c Enterprise Edition Release 19.0 .0 .0 .0 SQL > select * from tbl for update wait 10 ; ORA -30006 : resource busy; acquire with WAIT timeout expired Similarly, the NOWAIT clause can be used to instruct the database to return immediately if the requested row is locked by another transaction: SQL > select * from tbl for update nowait; ORA -00054 : resource busy and acquire with NOWAIT specified or timeout expired If neither WAIT nor NOWAIT is specified, Oracle retains its default behavior and waits indefinitely for the row lock to become available: SQL > s...
Post a Comment
Read more