kleontev

spool spool_statement_level_rollback.log 

cl scr 
set echo on 
set time off 

drop table slr_test purge;

create table slr_test(id int primary key);

kleontev

-- задачка из чата https://t.me/oracle_dbd
-- https://t.me/c/1195394250/1821

spool spool_non_overlapping_range_constraint.log
drop table client_tariff purge;

create table client_tariff(
  client_id number(38) not null,
  effective_date date not null,
  primary key(client_id, effective_date)  

kleontev

spool spool_snapshot_too_old_demo.log

set echo on time off timi on serverout on

drop table snapshot_too_old_demo purge;

create table snapshot_too_old_demo as select * from dual;

create undo tablespace tiny_undotbs2
    datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/tiny_undotbs2.dbf'

kleontev

import cx_Oracle
from functools import wraps
from typing import Optional, Any


def is_oracle_exception(e: Exception) -> bool:
    return isinstance(e, cx_Oracle.Error)


def get_ora_error_attr(e: Exception, attr: str) -> Any:

kleontev

from typing import (
    Any,
    Dict,
    Optional,
    Union,
    Tuple,
    Hashable,
    List
)

kleontev

from concurrent.futures import Future, wait, FIRST_COMPLETED
from typing import Iterable


def as_completed(fs: Iterable[Future], buffer: int = 5):
    """
    Self-throttling version of concurrent.futures.as_completed.
    Contrary to the standard library version, does not consume
    Future iterable immediately.
    """

kleontev

/*
Задача (из чата https://t.me/oracle_dbd): 
    Есть сложный запрос с большим количеством соединений и условий фильтрации.
    При определенных условиях запрос возвращает существенно меньшее количество строк, 
    чем мы ожидаем, либо не возвращает вообще ни одной. 
    
    Нам нужно понять, какое из условий в запросе приводит к этому.
*/

set echo on lines 200 pages 0 

kleontev

spool spool_arraysize_lio_impact.log 

create table t1 (id, fill) as select rownum, lpad('x', 2000, 'y') from dual connect by rownum <= 1e5;

create table t2 as select * from t1;

exec dbms_stats.gather_table_stats(user, 't1');

exec dbms_stats.gather_table_stats(user, 't2');

kleontev

SQL> 
SQL> create table t(id, value) as select 1, 1 from dual;

Table created.

SQL> 
SQL> update t set value = value + 1 where id = 1;

1 row updated.

kleontev

SQL> 
SQL> create table t (id, value) as select 1, 1 from dual
  2  /

Table created.

Elapsed: 00:00:00.03
SQL> 
SQL> var cur_1 refcursor
SQL> var cur_2 refcursor