Заметки про Oracle

Содержание

back

1. partitioning

1.1. ctas and partitioning

Создавать партицированные таблицы можно и с помощью оператора create table as select. Пример приведен ниже.

create table ah_table 
parallel 8 
nologging 
partition by range (report_date) interval (numtoyminterval(1, 'MONTH')) 
  (partition p0 values less than (to_date('2005-01-01', 'yyyy-mm-dd'))) 
tablespace yourts as 
(
select  /*+parallel(8)*/  date'2020-01-01' report_date, 42 as kf_value union all
select  /*+parallel(8)*/  date'2020-02-01' report_date, 42 as kf_value 
)

1.2. create partition ddl range day patition and hash subpartition   oracle

Создание партицирования: range по дане и hash для субпартиций

create table tablename
(
  pk_hash        raw(20) not null,
  report_date    date not null,
  id             number  )
tablespace yourts
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  )
compress for all operations
nologging
partition by range (report_date) interval (numtodsinterval(1, 'DAY'))  -- partition by range (report_dt) interval (numtoyminterval(1, 'MONTH'))
subpartition by hash(id) 
  subpartitions 256
(
  partition p0 VALUES LESS THAN (to_DATE('2001-01-01', 'yyyy-mm-dd')),
  partition p1 VALUES LESS THAN (to_DATE('2001-02-01', 'yyyy-mm-dd'))
);

1.3. create partition ddl range number   oracle

Создание партиций: range по числовому значению

drop table tablename;
-- Create table
create table tablename
(
  num     NUMBER,
  secondf NUMBER
)
tablespace yourts
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  )
partition by range (num) interval(1)
(
  partition p0 values less than (1),
  partition p2 values less than (2)    
);

1.4. create partition ddl range by month and semi dynamic range   oracle

Создание партиций range по месяцу и набор фиксированных по number

create table tablename
(
  id              INTEGER,
  report_dt       DATE,
  metric_id       INTEGER,
  val_dc_lcl      NUMBER
)
tablespace yourts
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  )
partition by range (report_dt) interval (numtoyminterval(1, 'MONTH'))
  subpartition by range (metric_id) --interval(1)
  subpartition template

    ( 
     subpartition vld0 VALUES LESS THAN (1),
     subpartition vld5 VALUES LESS THAN (5),
     subpartition vld10 VALUES LESS THAN (10),
     subpartition vldm VALUES LESS THAN (maxvalue) )

  (partition p0 VALUES LESS THAN (to_DATE('2005-02-01', 'yyyy-mm-dd'))   
);

1.5. create partition ddl range day interval week   oracle

создание партиции по неделям

create table tablename
(
  ID              number(4, 0)
, creation_date   date
)
partition by range  (creation_date) interval ( numtodsinterval(7, 'day') )
  (partition TEST_P1 values less than (to_date(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));

1.6. create partition ddl hash   oracle

Создание партиций по hash с фиксированным количеством

create table tablename
(
  id                       NUMBER,
  c_date                   DATE
)
tablespace yourts
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  )
  partition by hash(id) partitions 1024
  ;

1.7. partitioning range date range metric   oracle

create table tablename
(
  id INTEGER,
  report_dt       DATE,
  metric_id       INTEGER,
  val_dc_lcl      NUMBER
)
tablespace yourts
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  )
partition by range (report_dt) interval (numtoyminterval(1, 'MONTH'))
  subpartition by range (metric_id) --interval(1)
  subpartition template 
    (
       subpartition  vld0 VALUES LESS THAN(10000),
       subpartition  vld1 VALUES LESS THAN(10001),
       subpartition  vld2 VALUES LESS THAN(10002),      
       subpartition vldm VALUES LESS THAN (maxvalue) 
     )     
  (partition p0 VALUES LESS THAN (to_DATE('2005-02-01', 'yyyy-mm-dd'))   
);

1.8. partitioning by range interval subpartition by range number   oracle

create table tablename
(
  tableley     NUMBER(20) not null,
  product_type NUMBER(1),
  update_ts          DATE
)
partition by range (update_ts) interval (numtodsinterval(1, 'DAY'))  -- partition by range (report_dt) interval (numtoyminterval(1, 'MONTH'))
  subpartition by range (PRODUCT_TYPE) 
  subpartition template 
  (
    subpartition pt1 values less than (2),
    subpartition pt2 values less than (3),
    subpartition pt3 values less than (4),
    subpartition pt4 values less than (5),
    subpartition ptm values less than (maxvalue)  
  )
  (partition p0 VALUES LESS THAN (to_date('2019-01-01', 'yyyy-mm-dd'))
  );

2. statement_queuing

Если вы не хотите чтобы ваш запрос даунгрейдился, есть хинт STATEMENT_QUEUING. Неверное и необдуманное использование данного хинта, как и многих других черевато нехорошими последствиями

select /*+ parallel(8) statement_queuing */ *
  from dual
  where rownum < 5

3. регулярные выражения в oracle

3.1. regexp

select 1 as z from dual -- (9999999999,8888888888,7777777777)  from dual
where 
   --   regexp_like('9999999999', '[0-9]{10}') -- 10 цифр 
  --and 
--      regexp_like('1142', '(\d{3})')
      regexp_like(11121111555555, '(.).*\1{5}') -- повторение любого символа строго более 5 раз подряд ( 6 и более )

select z.a 
  from (
        select '5-1' as a from dual union all
        select '555' as a from dual union all
        select 'a5a 1349853940589304' as a from dual union all
        select 'asdasd' as a from dual union all
        select '51 12 345678-' as a from dual union all
        select '51 ' as a from dual union all
        select '51 12 345678' as a from dual
  ) z
  where regexp_like(z.a, '[0-9]{6}') -- имеющие 6 повторяющихся цифр
    and regexp_like(z.a, '^[0-9]{2}') -- начинающиеся на как минимум две цифры

3.2. oracle extract from regexp

Если вам понадобилось найти в неструктурированном тексте число между двумя тегами не включая теги, то для этого в регулярных выражениях есть решение. Оно называется lookbehind. В oracle к сожалению данная часть функционала регулярных выражений не поддерживается. К счастью есть несколько решений позволяющих получить результат.

Повторюсь: задача получить число между тегами, не включая сам тег.

Пример: исходная строка: "243 mysupertag->42<-mysupersupertag" требуется получить: 42

Решение правильное: Использовать функционал групп (capture group - т.е. скобки). Работает это через дополнительный параметр в функции regexp_substr, называющийся subexpr, где мы указываем какая именно группа должна быть возвращена в качестве результата. Выдержка из документации: Для следующего выражения "0123(((abc)(de)f)ghi)45(678)" есть 5 subexpressions: This expression has five subexpressions in the following order: "abcdefghi" followed by "abcdef", "abc", "de" and "678". Число переданное в этот параметр означает какое из subexpressions вам нужно вернуть в качестве результата. Т.к. в примере ниже есть только 1 скобки, то передав значение 1 мы получаем в качестве результата число без лишних тегов, но поиск при этом осуществляется только при наличии тегов.

select regexp_substr('243 mysupertag->42<-mysupersupertag', 'mysupertag->(\d+)<-mysupersupertag', 1, 1, NULL, 1) as extracted_value
    from dual;

Решение грубое: Выбрать всё в т.ч. и теги, и заменить их помощью replace на пустоту

select replace(
                replace(
                          regexp_substr('243 mysupertag->42<-mysupersupertag', 'mysupertag->(\d+)<-mysupersupertag', 1, 1, NULL, 1)
                          , 'mysupertag->', ''
                        )
                , '<-mysupersupertag', ''
              ) as extracted_value
    from dual;

На данное решение я натолкнулся благодаря ответу на SO: https://stackoverflow.com/questions/32359433/oracle-look-behind-positive in oracle there is no lookbehind but you can cheat with capture group?

select regexp_substr('TIPTOP4152', 'TOP(\d+)', 1, 1, NULL, 1) nbr
    from dual;

благодаря последнему параметру и скобкам, система возвращает то, что в скобках. Номер это какие скобки по очереди вернуть.

4. sqlloader

4.1. sql loader   sqlloader

тип TIMESTAMP для sqlloader ODS$VALIDFROM TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF6",

Чтобы загрузить в таблицу из csv

CREATE TABLE SCHEMA.TEST_TABLE (
  ID        NUMBER,
  VALUE     NUMBER,
  REPORT_DT DATE
)

Файл EXPORT.csv


542351;123;2019-01-01 123125;456;2019-01-02 346363;789;2019-01-03

ctl файл

Файл EXPORT.ctl
----------------------------------------------------------------------------------------------------------
OPTIONS (SKIP=0)
LOAD DATA
INFILE 'EXPORT.csv'
BADFILE 'EXPORT.bad'
DISCARDFILE 'EXPORT.dsc'
APPEND
INTO TABLE SCHEMA.TEST_TABLE
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
ID,
VALUE,
PEPORT_DT DATE "YYYY-MM-DD"
)

Файл EXPORT.bat (параметр кодировки chcp 1251 (Win) или 65001 (UTF8)…)

@echo off
chcp 65001
set NLS_NUMERIC_CHARACTERS=,.
sqlldr login/"pass"@yourservername:1521/yoursidname control=EXPORT.ctl direct=true parallel=true
pause

4.2. Загрузка блобов в oracle через sqlloader с локальной тачки   sqlloader

@echo off
chcp 65001
set NLS_NUMERIC_CHARACTERS=,.
sqlldr asriyan_av/"passhere!!!"@yourservervname:1521/yoursid control=EXPORT.ctl direct=true parallel=false
pause

csv для загрузки. Во втором поле храним путь к файлу для загрузки в колонку.

1;D:\eav\sqlload\eav.rar;

ctl

OPTIONS (SKIP=0)
LOAD DATA
INFILE 'EXPORT.csv'
BADFILE 'EXPORT.bad'
DISCARDFILE 'EXPORT.dsc'
APPEND
INTO TABLE ASRIYAN_AV.TEST_BLOB
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
ID,
FNM FILLER,
VALUE LOBFILE(FNM) TERMINATED BY EOF
)

table DDL where we want to load data

CREATE TABLE "ASRIYAN_AV"."TEST_BLOB" 
 (      "ID" NUMBER, 
      "FNM" NVARCHAR2(100), 
      "VALUE" BLOB
 ) ;

5. meta query

5.1. get tablespace size

select /*+ parallel(1)*/ 
       a.tablespace_name,
       round(a.used_space*8192/1024/1024/1024/1024,2) as used_space_tb,
       round(a.tablespace_size*8192/1024/1024/1024/1024,2)  tablespace_size_tb,
       round(a.used_percent,2) used_percent,
       round(a.tablespace_size*8192/1024/1024/1024/1024,2) - round(a.used_space*8192/1024/1024/1024/1024,2) as free_size_tb
  from dba_tablespace_usage_metrics a;

5.2. get plan

sql_id and sql_exec_id you can find in v$session

get plan

select dbms_sqltune.report_sql_monitor( sql_id => '0h8fg425sz9n6',
                                        sql_exec_id => 16777216,
                                        type => 'TEXT' )  -- ACTIVE -- TEXT -- HTML
  from dual;

5.3. table statistics

посчитать статистику для таблицы

begin
  dbms_stats.gather_table_stats( 'TABLESCHEMA', 'TABLENAME', degree => 8, cascade => true );
end;

5.4. create table with dates

select add_months(DATE'2018-01-31',rownum) as crazy_month 
  from dual 
  connect by rownum < 100;
select last_day(to_date('2018-01-01', 'yyyy-mm-dd') + rownum - 1) as crazyday
  from all_objects 
 where rownum <= to_date('2018-12-01','yyyy-mm-dd')- to_date('2018-01-01', 'yyyy-mm-dd') + 1
 group by last_day(to_date('2018-01-01', 'yyyy-mm-dd') + rownum - 1)

5.5. delta oracle SCN

select current_scn from v$database; -- get current scn number
select a.ora_rowscn, a.* from schemaname.tablename a; -- from table
select timestamp_to_scn(sysdate) from dual;
select dbms_flashback.get_system_change_number from dual;
select scn_to_timestamp(ora_rowscn) from schemaname.tablename

5.6. oracle generate random number in range

select round(dbms_random.value(1,10000000),0) as zzz 
from dual

5.7. create table into dblink

declare 
 --vv varchar2(32000);
 --zz clob;
begin  
  dbms_utility.exec_ddl_statement@dbnlinkname('CREATE TABLE ZZZ1 (id number)');
  -- dbms_utility.exec_ddl_statement@score_rp('CREATE TABLE ZZZ1 AS SELECT 1 AS VV FROM DUAL');
  dbms_utility.exec_ddl_statement@dblinkname('DROP TABLE ZZZ1');
end;
/*
   select dbms_metadata.get_ddl('TABLE','TEST_SCH','ASRIYAN_AV')
      into zz
    from dual;
  vv := dbms_lob.substr(zz,32000);      
  dbms_output.put_line(vv); 
*/  

5.8. create error table

BEGIN
  dbms_errlog.create_error_log('TABLENAME', 'ERR_TABLENAME'); -- ERR_TABLENAME will be created
END;
/
MERGE tablename
LOG ERRORS INTO schemaname.err_tablename('L_TABLENAME') REJECT LIMIT 10000; -- additional label for error        

5.9. regexp_substr connect by

select  regexp_substr('Петров,1111111,Москва','[^,]+',1,level) ll
       from dual
     connect by level <=  REGEXP_COUNT('Петров,1111111,Москва',',')+1
select
    standard_hash(1, 'SHA1') as PK -- sys_guid() 
   , regexp_replace('12312  3A Я z', '[A-Za-zА-Яа-я\ ]','')         
   , regexp_replace('12312  3A Я z', '^[0-9]','')         
   , regexp_replace('12312  3A Я z', '[^0-9]','')         
   , regexp_replace('12312  3A Я z', '[^[:digit:]]','')         
   , sysdate as create_date 
 from dual a

5.10. Зависимости от объекта oracle

SELECT     TO_CHAR (d.object_id) object_id,
           TO_CHAR (referenced_object_id) referenced_object_id,
           TO_CHAR (LEVEL) "LEVEL",
           o.*           
      FROM public_dependency d
        LEFT JOIN dba_objects o on o.object_id = d.object_id
CONNECT BY PRIOR d.object_id = referenced_object_id
START WITH referenced_object_id =
              (
               SELECT object_id --* --object_id
                 FROM SYS.dba_objects
                WHERE owner = 'SCHEMA_NAME'
                  AND object_name = 'OBJECT_NAME'
                  AND object_type = 'PROCEDURE' -- TABLE
               )

5.11. Undo space usage by transactions

–Undo space usage by transactions

/*set lines 200
col username format a10 wrapped heading "User"
col name format a22 wrapped heading "Undo Segment Name"
col xidusn heading "Undo|Seg #"
col xidslot heading "Undo|Slot #"
col xidsqn heading "Undo|Seq #"
col ubafil heading "File #"
col ubablk heading "Block #"
col start_time format a10 word_wrapped heading "Started"
col status format a8 heading "Status"
col blk format 999,999,999 heading "KBytes"
col used_urec heading "Rows"
alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss'; */
SELECT S.SID, t.START_TIME, s.USERNAME, R.NAME "Rollback segment name",  
ubafil, ubablk, t.status "Transaction status", s.status "Session status", (used_ublk*p.value)/1024/1024 "Undo, Mbytes", used_urec, s.sql_id, p1.spid "OS pid"
FROM V$TRANSACTION T, V$ROLLNAME R, V$SESSION S, V$PARAMETER P, v$process p1
where t.xidusn=r.usn
and s.saddr=t.ses_addr
and s.paddr=p1.addr(+)
AND P.NAME='db_block_size'
order by "Undo, Mbytes" desc;

5.12. parallel dml enable hint

enable_parallel_dml parallel(8)

5.13. Oracle get plan 2 and other good tables

select * from v$sql_monitor –v$sql_plan_monitor select * from gv$sql select * from gv$sql_monitor select * from v$sql_plan_monitor select * from gv$active_session_history

select * from v$sql_monitor –v$sql_plan_monitor –select * from gv$sql select * from gv$active_session_history

select dbms_sqltune.report_sql_detail(sql_id => 'btsnqn6jh6gm5', type => 'TEXT', report_level => 'ALL') from dual;
select dbms_sqltune.report_sql_monitor_list( type => 'HTML', report_level => 'ALL' ) as report from dual;
select dbms_sqltune.report_sql_monitor_list(type => 'TEXT', report_level => 'ALL') from dual;

5.14. oracle sql get hour from timestamp

select extract(hour from systimestamp)+3 from dual;

5.15. SQL plan monitor oracle

yet another sql to find history of queries

select /*+ parallel(1) */
    s.sql_id 
   ,s.sql_text 
   ,s.parsing_schema_name as account -- account 
   ,round(s.rows_processed / 1000000, 2) as rows_m -- rows processed millions
   ,s.executions as execs 
   ,round(s.elapsed_time / 1000000 / 60, 0) as time -- elapsed_time_sec - min
   ,round(s.cpu_time / 1000000 / 60 ,0) as cpu_time -- cpu_time_sec - min
   ,round(s.concurrency_wait_time / 1000000,0)  as conc -- concurrency 
   ,round(s.disk_reads/1000000,2) as disk_r -- hz why / 1000000
   ,round(s.direct_writes/1000000,2) as disk_w -- hz why / 1000000
   ,s.last_active_time  
  from v$sql s 
 where s.last_active_time >= date'2019-07-12'
   and s.rows_processed > 0
   and round(s.elapsed_time / 1000000, 0) > 1
   --and s.sql_id = 'cz7gbur20btuc'   
   order by s.elapsed_time desc
--SQL Plan Monitor
select status,
       output_rows,
       plan_line_id,
       lpad('  ', 2*plan_depth-1) || plan_operation operation,
       plan_options,
       plan_object_name,
       plan_object_type,
       plan_cost,
       plan_cardinality
       plan_bytes,
       plan_time,
       plan_partition_start,
       plan_partition_stop,
       plan_cpu_cost,
       plan_io_cost,
       plan_temp_space,
       starts,
       io_interconnect_bytes,
       physical_read_requests,
       physical_read_bytes,
       physical_write_requests,
       physical_write_bytes,
       workarea_mem,
       workarea_max_mem,
       workarea_tempseg,
       workarea_max_tempseg,
       numtodsinterval(last_refresh_time - first_refresh_time, 'day') as elaps
from   (
        select v$sql_plan_monitor.*
        from   v$sql_plan_monitor, v$sql_plan
        where  v$sql_plan.address = hextoraw(:sql_address) and
               v$sql_plan.hash_value = :sql_hash_value and
               v$sql_plan.sql_id = v$sql_plan_monitor.sql_id and
               v$sql_plan_monitor.sql_exec_id = :sql_exec_id and
               v$sql_plan.child_address = v$sql_plan_monitor.sql_child_address and
               v$sql_plan.plan_hash_value = v$sql_plan_monitor.sql_plan_hash_value and
               v$sql_plan.id = v$sql_plan_monitor.plan_line_id
       )

order by plan_line_id, plan_position

5.16. oracle paralell hint

alter session force parallel query parallel 1;

5.17. hier query to get dates

WITH gen_dates ( dt ) AS -- generate dates
  ( 
    SELECT date'2018-01-01' AS dt     
      FROM dual
    UNION ALL
    SELECT   r.dt + 1 AS i           
      FROM gen_dates r 
     WHERE dt < sysdate     
  )
SELECT * FROM gen_dates;  
SELECT date'2018-01-01' + rownum AS vv 
  FROM dual 
  CONNECT BY date'2018-01-01' + rownum < sysdate

5.18. get oracle database name

select name from v$database

5.19. get non auditable tables

-- create table asriyan_av.non_audit_tables as
 select a.owner, a.table_name
   FROM all_tables a -- table where you have grant. also you can see USER_TABLES or DBA_TABLES
     left join DBA_OBJ_AUDIT_OPTS b on a.owner = b.owner AND a.table_name = b.object_name 
  where a.tablespace_name = 'SCHEMANAME'
    and b.object_name is null

5.20. get job ddl

Получить DDL создания джоба из базы запросом

SELECT /*+ parallel(1)*/ dbms_metadata.get_ddl(object_type => 'PROCOBJ', name => 'PROCNAME', SCHEMA => 'SCHEMANAME') FROM dual;
SELECT /*+ parallel(1)*/ dbms_metadata.get_ddl(object_type => 'TABLE', name => 'TABLENAME', SCHEMA => 'SCHEMANAME') FROM dual;
select dbms_metadata.get_dependent_ddl('OBJECT_GRANT', 'TABLENAME', 'SCHEMANAME') from dual;

5.21. dbms_rls get fields

get list of fields with RLS!

SELECT * FROM ALL_SEC_RELEVANT_COLS

5.22. dyn_exec и кавычки.

С этим кодом вам не придется кавычки предварять escape символом

begin
  yourschema.yourprocname(iv_query=> q'[  
  select count('1') from dual 
  ]');
end;

5.23. select from list oracle

SELECT * FROM table(sys.odcinumberlist(
1,2,3
))

5.24. regexp and with function

WITH tr_datas AS ( SELECT '12-Mar-20' val FROM dual UNION all
                   SELECT '11-Jul-20' FROM dual UNION ALL
                   SELECT '11-Jul-bad20' FROM dual UNION ALL
                   SELECT '11-Jul-20 0000 AM' FROM dual UNION ALL
                   SELECT 'bad11-Jul-bad20' FROM dual 
                 )
SELECT val, 
       regexp_substr(val,'^([0-9]){2,2}-([a-zA-Z]){3,3}-([0-9]){2}'),
       to_char(to_date(upper(regexp_substr(val,'^([0-9]){2,2}-([a-zA-Z]){3,3}-([0-9]){2}')), 'DD-MON-YY', 'NLS_DATE_LANGUAGE=AMERICAN'),'dd.mm.yyyy') AS converted_date
  FROM tr_datas
 WHERE regexp_like(val, '^([0-9]){2,2}-([a-zA-Z]){3,3}-([0-9]){2}')

with function 
QQ(dd varchar2,ff varchar2) return  varchar2 deterministic
as
  v_date date;
begin
  select to_date(dd, ff) into v_date from dual;
  return  'Y' ;
  exception when others then return 'N';
end ;
SELECT 
 qq('01-01-2019','DD-MM-YYYY') F_CHECK1
,qq('91-01-2019','DD-MM-YYYY') F_CHECK2
FROM DUAL

Автор: Асриян Артур

Created: 2021-07-13 Вт 15:37

Validate