Как перечислить все записи в тегах

#sql #oracle #plsql #plsql_developer

Есть строка  '/home/pc/test' ,
и слова в тегах '[/alseko][/logs][/archive]'
Как получить ожидаемый результат =   

 [/home/pc/test/alseko][/home/pc/test/logs][/home/pc/test/archive]  


Мой код, пытаюсь найти:  

select '[' || '/home/pc/test' ||
       ltrim(substr('[/alseko][/logs][/archive]',
              instr('[/alseko][/logs][/archive]', '['),
              instr('[/alseko][/logs][/archive]', ']')),'[')
from dual

    

Ответы

Ответ 1

Надо распарсить теги в таблицу, и потом заново собрать в строку, например так:

with data as (
    select 
    '[/alseko][/logs][/archive]' tags,
    '\[(\/\w+)' pattern,
    '/home/pc/test' prefix 
    from dual
),
tags as (
    select 
        '['||prefix||regexp_substr (tags, pattern, 1, level, null, 1)||']' tag,
        level sort
    from data connect by level <= regexp_count (tags, pattern) 
)   
select listagg (tag) within group (order by sort)
from tags
; 


Вывод:


  [/home/pc/test/alseko][/home/pc/test/logs][/home/pc/test/archive]


Ответ 2

Получился сделать через цикл, сперва определил количество вхождений

Declare
vn_count_paths Integer;
vc_paths varchar(1024);
vc_FtpDir varchar(1024);
begin
vn_count_paths:= length('[/alseko][/logs][/archive]')-LENGTH(REPLACE('[/alseko][/logs][/archive]','['));
dbms_output.put_line('Size: ' || vn_count_paths);
for i in 1 .. vn_count_paths
loop
  vc_paths:= vc_paths||'['||'/home/pc/test'||ltrim(substr('[/alseko][/logs][/archive]',instr('[/alseko][/logs][/archive]','[',1,i)+1,
                                                                                
     instr('[/alseko][/logs][/archive]',']',1,i)-
                                                                                
     instr('[/alseko][/logs][/archive]','[',1,i)-1),'[')||']';
end loop;
vc_FtpDir := '['||'/home/pc/test'||']'||vc_paths;
dbms_output.put_line('vc_FtpDir: ' || vc_FtpDir);
end;

Отключение конкретной сессии / всех сессий

#sql #база_данных #oracle #plsql #plsql_developer

Добрый день!
Есть темповая таблица, которую необходимо альтернуть (добавить столбец).
Понятное дело, при 300 активных сессиях кто-то эту таблицу использует - из за этого
валится ora-14450.

Могу я как то отследить, какая именно сессия обращается к необходимой мне таблице? 
И
 alter system kill ...?

Если же это отследить невозможно - как отключить все активные сессии?
    

Ответы

Ответ 1

Получить информацию о сессиях, использующих временную таблицу можно следующим запросом:

select s.*
  from v$lock l, dba_objects d, v$session s
 where d.owner='СХЕМА' and d.OBJECT_NAME='ИМЯ-ТАБЛИЦЫ'
   and l.id1=d.object_id and l.type='TO' and s.sid=l.sid


далее сессии можно убить с помощью ALTER SYSTEM KILL SESSION 'sid,serial#, где sid
и serial# взяты из предыдущего запроса. Если сессий много, можно убить их все автоматически,
следующим PL/SQL блоком (предварительно проверив то ли вы получаете, что надо, первым
запросом):

begin
for c1 in(select distinct s.sid, s.serial#
            from v$lock l, dba_objects d, v$session s
           where d.owner='СХЕМА' and d.OBJECT_NAME='ИМЯ-ТАБЛИЦЫ'
             and l.id1=d.object_id and l.type='TO' and s.sid=l.sid) loop
   execute immediate 'ALTER SYSTEM KILL SESSION '''||c1.sid||','||c1.serial#||'''';
end loop;
end;
/


Ответ 2

В таких случаях часто бывает достаточным просто установить DDL_LOCK_TIMEOUT перед
выполнением DDL:

alter session set ddl_lock_timeout=600;


Если в течении 10 минут (600 секунд) объект освободится даже на очень короткий промежуток
времени, то Oracle сможет выполнить DDL

Оптимизация запроса с подзапросом в Oracle

#sql #oracle #plsql_developer

Здравствуйте. Есть запрос. Отрабатывает в sql-developer 60 секунд. Второй раз запускаю
8 секунд, 3 раз 3.5 секунды. Кэшируется вообщем. Минут через 10 та же история. Наверное
за это время кэш падает. Когда этот запрос первый раз выполянется из кода c# на строчке
rdr.Read() вообще выполняется минут 5-10 rdr - OracleDataReader. Не понимаю что ещё
можно упростить в запросе. Всё и так проиндексировано кроме поелей edit_state и primary_date.
Вообщем вот запрос и explain plan к нему.

SELECT r.id
FROM INTERSHOP.JOURNAL_RECORDS r
WHERE r.edit_state = 0
      AND r.journal_id = 515
      AND r.journal_table_id = 1
      AND
(
    SELECT STRING_VALUE
    FROM journal_field_data f
    WHERE r.id = f.journal_record_id
          AND field_name = 'SERTIF_NUMB'
) = :SERTIF_NUMB 
ORDER BY r.PRIMARY_DATE DESC;




Если я правильно понял то там где написано full очень много занимает памяти, но там
всё проиндексировано. И я немного не понимаю но неужели order by весит ещё больше?
Подскажите пожалуйста что тут можно сделать. Я уже даже не знаю что попробовать.  

UPD
Исправленный вариант запроса:

SELECT r.id
FROM INTERSHOP.JOURNAL_RECORDS r
     LEFT JOIN journal_field_data f ON r.id = f.journal_record_id
WHERE r.edit_state = 0
      AND r.journal_id = 515
      AND r.journal_table_id = 1
      AND field_name = 'SERTIF_NUMB'
      AND f.STRING_VALUE = :SERTIF_NUMB
ORDER BY r.PRIMARY_DATE DESC;



    

Ответы

Ответ 1

Можно использовать 2 варианта.

Первый:

SELECT r.id
FROM INTERSHOP.JOURNAL_RECORDS r
     JOIN journal_field_data f ON r.id = f.journal_record_id
WHERE r.edit_state = 0
      AND r.journal_id = 515
      AND r.journal_table_id = 1
      AND field_name = 'SERTIF_NUMB'
      AND f.STRING_VALUE = :SERTIF_NUMB
ORDER BY r.PRIMARY_DATE DESC;


Второй:   

SELECT r.id
FROM INTERSHOP.JOURNAL_RECORDS r
     JOIN journal_field_data f ON r.id = f.journal_record_id
                                  AND f.field_name = 'SERTIF_NUMB'
                                  AND f.STRING_VALUE = :SERTIF_NUMB
WHERE r.edit_state = 0
      AND r.journal_id = 515
      AND r.journal_table_id = 1
ORDER BY r.PRIMARY_DATE DESC;




Получить представление об общих принципах оптимизации запросов можно из статей на
Википедии:   


Семантическая оптимизация запросов СУБД   
Оптимизация запросов СУБД




Почему тут не нужен LEFT JOIN - и вообще, кратко и наглядно о различных вариантах
соединений - на иллюстрации:

Создание строки из значений (процедура)

#sql #oracle #plsql #plsql_developer

Добрый день. Т.к. от программирования далек, но в виду производственной необходимости
в это дело ввязался, прошу помочь с написанием процедуры в oracle plsql developer (буду
признателен за помощь в создании алгоритма так же).

Суть такова - есть первый столбец (условно ID, в моем контексте не уникальный), и
второй столбец, назову его "признаком". В базе это хранится в таком виде

ID  Признак
1   Красный
1   Темно-красный
2   Синий
2   Бирюзовый


Необходимо написать процедуру такого рода - все признаки, соответствующие одинаковому
ID, пишутся в одну ячейку. Т.е. так:

ID   Признак (применение процедуры)
1    Красный, (или ;) Темно-красный
2    Синий, Бирюзовый


Буду крайне признателен за помощь!
В виду необходимости скидываю сам кусок селекта:

(case when (row_number() over(partition by
    (select dd.name from metrology.document dd where dd.id in (select **max(d.document_id)**
from metrology.eqtype_document d where d.EQUIPMENT_TYPE_ID = e.id))
    order by e.name)) = 1 then
    (select dd.name from metrology.document dd where dd.id in (select **max(d.document_id)**
from metrology.eqtype_document d where d.EQUIPMENT_TYPE_ID = e.id)) end) as doc_name_to_form


Символами ** отмечен участок кода, из за которого возникает весь сыр-бор. 
Max там включен чисто для работоспособности - данный участок селекта возвращает более
одной строки и вызывает ошибку ora, поэтому я его max-ом искуссвенно урезал до одного
возвращаемого результата. Но дело в том, что в 70% случаев данный участок должен возвращать
более одного значения, для чего и необходима конкатенация, и я не знаю, как сюда инъектировать
какой либо код.
    

Ответы

Ответ 1

Как вариант, если не заработают wm_conat или sys_xmlagg, сборка рекурсивным CTE:

with Src(ID, Color, RN) as(
  select ID, Color, row_number() over(partition by ID order by NULL) RN
    from tab
),
Tree(ID,color,RN) as(
  select ID, Color, RN from Src where RN=1
  union all
  select T.ID,T.Color||','||S.Color,S.RN
    from Tree T, Src S
   where S.ID=T.ID and S.RN=T.RN+1
)
select * from Tree


Или такой вариант (при наличии sys_connect_by_path):

select ID, substr(max(sys_connect_by_path(Color,',')),2)
  from (
        select ID, Color, row_number() over(partition by ID order by NULL) RN
          from tab
  )
  start with RN=1
  connect by ID=prior ID and RN=prior RN+1
  group by Id


Или сделать функцию, получающую строку по ID, что возможно удобнее для применения
в выходных формах:

create or replace function get_colors(ID_ number) return varchar2
is
  result varchar2(4000);
begin
  result:=NULL;
  for C in(select accname from v1users where vidid=ID_) loop
    if length(result)+length(C.accname) > 3996 then
      return substr(result||',...',2);
    end if;
    result:=result||','||C.accname;
  end loop;
  return substr(result,2);
end;


Ответ 2

Получить нужный результат можно таким запросом:

select id, 
       to_char(wm_concat("Признак")) as "Признак" 
from   table
group  by id


Следует заметить, что wm_concat функция недокументированная и в проме ее использовать
не всегда разумно. 
Если wm_concat не подходит, можно использовать XML:

select id, 
       CAST(RTRIM(Sys_xmlagg(XMLELEMENT(col, id||', ')).extract('/ROWSET/COL/text()').getclobval(),
', ') AS VARCHAR2(4000)) as "Признак"
FROM   table
group  by id


col - это Ваша колонка Признак. Использовать русское наименование колонки у меня
не получилось. В обоих вариантах могут быть проблемы с сортировкой значений.

Каков синтаксис для функционального аналога SELECT TOP(N) в Oracle?

#sql #sql_server #oracle #plsql_developer

Предположим, есть таблица TABLE

ID | DEF
 1 | Один
 2 | Два
 3 | Три
 4 | Четыре


Тогда запрос в MS SQL Server

SELECT TOP(2) * FROM TABLE ORDER BY ID DESC


Даст нам выдачу:

ID | DEF
 4 | Четыре
 3 | Три


А аналогичный запрос в Oracle (аналог, который мне предложился в интернетах)

SELECT * FROM TABLE WHERE ROWNUM <= 2 ORDER BY ID DESC


Даст нам выдачу:

ID | DEF
 2 | Два
 1 | Один


Как получить действительно аналогичный по функциональности синтаксис для Oracle?
    

Ответы

Ответ 1

В оракле до 11-й версии включительно - через подзапрос с row_number:

select *
  from (select t.*, row_number() over (order by id desc) rn
          from t)
 where rn <= 2


В 12-й версии появился синтаксис для первых N строк с LIMIT и OFFSET, почти как в
постгресе (запрос скопипастил из интернета, 12-го оракла под рукой нет):

 select *
   from t
  order by id desc
 OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;


Ответ 2

Дело в том, что Oracle сначала выполняет условие where и только потом производит
сортировку. То есть в вашем случае сначала остаются строки с id in (1, 2) и потом они
и сортируются.

Для получения аналогичного результата сначала надо отсортировать все данные и потом
обернув запрос получить нужное количество строк:

SELECT * 
FROM (SELECT * 
      FROM   TABLE 
      ORDER  BY ID DESC) 
WHERE ROWNUM <= 2


Ответ 3

Вам придётся воспользоваться подзапросом. Т.е. ваш запрос с основными условиями и
сортировкой перенесите в подзапрос, а ограничение на выбор поставьте в секцию WHERE
внешнего запроса.

SELECT * 
FROM
  (
  SELECT * 
  FROM TABLE 
  ORDER BY ID DESC 
  )
WHERE 
ROWNUM <= 2

Использовать содержание столбца таблицы в качестве имен таблиц для формирования запроса

В столбце таблицы содержатся имена других таблиц:
name ======= table_1 table_pineapple table_3
Нужно через цикл удалить все строки из этих таблиц:
delete from table_N;
Не понятно, как использовать поле типа varchar2 в качестве имени для обращения к таблице.

Ответ

Тут поможет динамический SQL - Native Dynamic SQL
Тест данные:
create table tables as select level id, 'table_'||level name from dual connect by level <= 4;
select 'drop table '||name||';'||chr (10)|| 'create table '||name||' as select '||id||' id '|| 'from dual connect by level<='||(id*10)||';' from tables where id < 4;
Выполните результат последнего запроса методом копи+паста, и затем следующее:
declare rows number; function deleteTableIfExists (name varchar2) return number is tableNotExist exception; pragma exception_init (tableNotExist, -942); begin execute immediate 'delete from '||name; return sql%rowcount; exception when tableNotExist then return -1; end deleteTableIfExists; begin for r in (select * from tables) loop rows := deleteTableIfExists (r.name); if rows >= 0 then dbms_output.put_line (r.name||' '||rows|| ' rows deleted'); else dbms_output.put_line (r.name||' does not exists'); end if; end loop; end; /
table_1 10 rows deleted table_2 20 rows deleted table_3 30 rows deleted table_4 does not exists
PL/SQL procedure successfully completed.
Обычно подобные функциии собираются в несколько пакетов по категориям один раз для всего проекта, например - dmlUtils и ddlUtils.

Отключение конкретной сессии / всех сессий

Добрый день! Есть темповая таблица, которую необходимо альтернуть (добавить столбец). Понятное дело, при 300 активных сессиях кто-то эту таблицу использует - из за этого валится ora-14450.
Могу я как то отследить, какая именно сессия обращается к необходимой мне таблице? И alter system kill ...?
Если же это отследить невозможно - как отключить все активные сессии?

Ответ

Получить информацию о сессиях, использующих временную таблицу можно следующим запросом:
select s.* from v$lock l, dba_objects d, v$session s where d.owner='СХЕМА' and d.OBJECT_NAME='ИМЯ-ТАБЛИЦЫ' and l.id1=d.object_id and l.type='TO' and s.sid=l.sid
далее сессии можно убить с помощью ALTER SYSTEM KILL SESSION 'sid,serial#, где sid и serial# взяты из предыдущего запроса. Если сессий много, можно убить их все автоматически, следующим PL/SQL блоком (предварительно проверив то ли вы получаете, что надо, первым запросом):
begin for c1 in(select distinct s.sid, s.serial# from v$lock l, dba_objects d, v$session s where d.owner='СХЕМА' and d.OBJECT_NAME='ИМЯ-ТАБЛИЦЫ' and l.id1=d.object_id and l.type='TO' and s.sid=l.sid) loop execute immediate 'ALTER SYSTEM KILL SESSION '''||c1.sid||','||c1.serial#||''''; end loop; end; /

Оптимизация запроса с подзапросом в Oracle

Здравствуйте. Есть запрос. Отрабатывает в sql-developer 60 секунд. Второй раз запускаю 8 секунд, 3 раз 3.5 секунды. Кэшируется вообщем. Минут через 10 та же история. Наверное за это время кэш падает. Когда этот запрос первый раз выполянется из кода c# на строчке rdr.Read() вообще выполняется минут 5-10 rdr - OracleDataReader. Не понимаю что ещё можно упростить в запросе. Всё и так проиндексировано кроме поелей edit_state и primary_date. Вообщем вот запрос и explain plan к нему.
SELECT r.id FROM INTERSHOP.JOURNAL_RECORDS r WHERE r.edit_state = 0 AND r.journal_id = 515 AND r.journal_table_id = 1 AND ( SELECT STRING_VALUE FROM journal_field_data f WHERE r.id = f.journal_record_id AND field_name = 'SERTIF_NUMB' ) = :SERTIF_NUMB ORDER BY r.PRIMARY_DATE DESC;

Если я правильно понял то там где написано full очень много занимает памяти, но там всё проиндексировано. И я немного не понимаю но неужели order by весит ещё больше? Подскажите пожалуйста что тут можно сделать. Я уже даже не знаю что попробовать.
UPD Исправленный вариант запроса:
SELECT r.id FROM INTERSHOP.JOURNAL_RECORDS r LEFT JOIN journal_field_data f ON r.id = f.journal_record_id WHERE r.edit_state = 0 AND r.journal_id = 515 AND r.journal_table_id = 1 AND field_name = 'SERTIF_NUMB' AND f.STRING_VALUE = :SERTIF_NUMB ORDER BY r.PRIMARY_DATE DESC;

Ответ

Можно использовать 2 варианта.
Первый
SELECT r.id FROM INTERSHOP.JOURNAL_RECORDS r JOIN journal_field_data f ON r.id = f.journal_record_id WHERE r.edit_state = 0 AND r.journal_id = 515 AND r.journal_table_id = 1 AND field_name = 'SERTIF_NUMB' AND f.STRING_VALUE = :SERTIF_NUMB ORDER BY r.PRIMARY_DATE DESC;
Второй:
SELECT r.id FROM INTERSHOP.JOURNAL_RECORDS r JOIN journal_field_data f ON r.id = f.journal_record_id AND f.field_name = 'SERTIF_NUMB' AND f.STRING_VALUE = :SERTIF_NUMB WHERE r.edit_state = 0 AND r.journal_id = 515 AND r.journal_table_id = 1 ORDER BY r.PRIMARY_DATE DESC;

Получить представление об общих принципах оптимизации запросов можно из статей на Википедии:
Семантическая оптимизация запросов СУБД Оптимизация запросов СУБД

Почему тут не нужен LEFT JOIN - и вообще, кратко и наглядно о различных вариантах соединений - на иллюстрации:

Как перечислить все записи в тегах

Есть строка '/home/pc/test' , и слова в тегах '[/alseko][/logs][/archive]' Как получить ожидаемый результат =
[/home/pc/test/alseko][/home/pc/test/logs][/home/pc/test/archive]
Мой код, пытаюсь найти:
select '[' || '/home/pc/test' || ltrim(substr('[/alseko][/logs][/archive]', instr('[/alseko][/logs][/archive]', '['), instr('[/alseko][/logs][/archive]', ']')),'[') from dual

Ответ

Надо распарсить теги в таблицу, и потом заново собрать в строку, например так:
with data as ( select '[/alseko][/logs][/archive]' tags, '\[(\/\w+)' pattern, '/home/pc/test' prefix from dual ), tags as ( select '['||prefix||regexp_substr (tags, pattern, 1, level, null, 1)||']' tag, level sort from data connect by level <= regexp_count (tags, pattern) ) select listagg (tag) within group (order by sort) from tags ;
Вывод:
[/home/pc/test/alseko][/home/pc/test/logs][/home/pc/test/archive]

Каков синтаксис для функционального аналога SELECT TOP(N) в Oracle?

Предположим, есть таблица TABLE
ID | DEF 1 | Один 2 | Два 3 | Три 4 | Четыре
Тогда запрос в MS SQL Server
SELECT TOP(2) * FROM TABLE ORDER BY ID DESC
Даст нам выдачу:
ID | DEF 4 | Четыре 3 | Три
А аналогичный запрос в Oracle (аналог, который мне предложился в интернетах)
SELECT * FROM TABLE WHERE ROWNUM <= 2 ORDER BY ID DESC
Даст нам выдачу:
ID | DEF 2 | Два 1 | Один
Как получить действительно аналогичный по функциональности синтаксис для Oracle?

Ответ

В оракле до 11-й версии включительно - через подзапрос с row_number
select * from (select t.*, row_number() over (order by id desc) rn from t) where rn <= 2
В 12-й версии появился синтаксис для первых N строк с LIMIT и OFFSET, почти как в постгресе (запрос скопипастил из интернета, 12-го оракла под рукой нет):
select * from t order by id desc OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;