Логирование в таблицу

Для отладки кода часто бывает нужно собирать отладочную информацию.
Этим мы сейчас и займемся.

13 использований конвейерных и параллельных табличных функций

Обзор табличных функций
Табличные функции
Конвейерные табличные функции


Обзор табличных функций

Табличные функции -- это функции, которые создают коллекцию строк (либо вложенную таблицу, или массив) к которой можно обратиться как к обычной физической таблице БД. Вы можете использовать табличную функцию по имени во фразе FROM запроса.

Простейший пример использования табличный функций (pipe row)

Создаем тип для нужной нам табличной функции:

create or replace package My_Types is
  type lookup_row is record ( idx number, text varchar2(20) );
  type lookups_tab is table of lookup_row;
end My_Types;

Советы по работе с SQL*Plus

Здесь представлены советы по работе со средой SQL*Plus самого начального уровня.

Добавление подсветки кода в Blogger (2-ая попытка)

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

Общая информация об электронной подписи

По результатам прочтения статьей (раз и два) на Хабрахабре решил для себя систематизировать полученную информацию и расписать последовательность действий по работе с электронной подписью.

Формат даты в Oracle

Строка с форматом хранится и на клиентском компьютере и на сервере.

Давайте разберемся сначала с настройками клиентского компьютера. В настройках клиентской конфигурации есть два параметра NLS_TERRITORY и NLS_LANGUAGE.

Том Кайт: о суммировании, слиянии и перемещении

Вопросы к Томасу Кайту и его ответы.

Контекст сеанса в Oracle, часть 1: предопределенные контексты

Аннотация

В каждом сеансе работы с СУБД можно использовать так называемые контексты, формально представляющие собой именованный набор пар "параметр/значение". Контексты сеансов обладают рядом интересных свойств, существенно повышающих "внутренние" возможности Oracle по созданию приложений. В статье рассматриваются два предопределенных контекста Oracle: USERENV и CLIENTCONTEXT.

Пустая строка в Oracle. Сравнение с пустой строкой

Oracle9i SQL Reference
   Basic Elements of Oracle SQL
       Nulls
Ссылка на документацию.
В Oracle пустая строка эквивалентна NULL, поэтому сравнение некоего значения с пустой строкой фактически ведет к сравнению его с NULL. В результате получается NULL:

Индекс-таблицы

Индекс-таблицы отличаются от обычных таблиц способом структурирования, хранения и индексирования данных. Чтобы лучше объяснить эти отличии, сравним индекс-таблицы с обычными таблицами, а также перечислим ситуации когда индекс-таблицу использовать предпочтительнее чем обычную таблицу.

Рекомендации по использованию индексов в конкретных приложениях

Для обеспечения быстрого доступа к строкам таблицы СУБД Oracle используются индексы. Индексы обеспечивают быстрый доступ к данным при операциях, когда выбирается относительно небольшое число строк таблицы.

Хотя Oracle позволяет использовать неограниченное число индексов в таблице, индексы приносят пользу только в том случае, когда они применяются для ускорения запросов. В противном случае, они лишь занимают место и уменьшают производительность сервера при обновлении индексированных столбцов. Необходимо использовать возможность EXPLAIN PLAN (план выполнения и статистики), чтобы определить, каким образом индексы используются в ваших запросах. Иногда, если индекс не используется по умолчанию, можно использовать подсказки к запросу по использованию индекса.

Ниже приводятся некоторые простыре рекомендации по управлению индексами.

Обеспечение ссылочной целостности с помощью ограничений

Если две таблицы содержат один или несколько общих стобцов, то их можно связать с помощью ограничения ссылочной целостности. Для столбца родительской таблицы (таблицы, содержащей полный набор значений, на которые может ссылаться другая таблица) должно быть определено ограничение PRIMARY KEY или UNIQUE. Для столбца дочерней таблицы (таблицы, значения которой должны ссылаться на существующие значения в другой таблице) должно быть определено ограничение FOREIGN KEY.

Первичные и уникальные ключи таблицы

В статье описывается смысл первичных и уникальных ключей, а также, даны рекомендацию по выбору полей кандидатов на эти ограничения.

Группы команд SQL

Все команды SQL можно условно разделить на несколько групп по их функциональности. Перечислим эти группы с примерами команд.

Получение плана выполнения

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

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
Connected as SYS
 
SQL> explain plan for select sysdate from dual;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
 
8 rows selected
 
SQL> 

Bulk collect on record

Давайте на примере попробуем поработать с Bulk Collect и record.

Генерация случайных чисел в заданном диапазоне

На практике часто требуется генерировать случайные числа в заданном диапазоне. Рассмотрим наиболее естественный способ сделать это на PL/SQL.

Блокировки Oracle при неиндексированных внешних ключах

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

Использование корзины Oracle

Одно из нововведений Oracle 10g это корзина. Эта возможность работает аналогично корзине в OC Windows или Mac OS. В этой статье будет описано, как работать с корзиной.

Фрагментация таблиц в Oracle

В статье рассказано о фрагментации таблиц. Представлен пример и предложено 4 варианта решения проблемы.

Содержимое необработанного файла расширенной трассировки SQL

Данные о трассировке SQL представляют непревзойденный учебный и диагностический материал, т.к. представляет собой последовательно записанную историю действий ядра Oracle, предпринятых в ответ на запросы приложения к базе данных.

Демонстрация обработки значений NULL групповыми функциями

Для демонстрации создадим и заполним небольшую таблицу. Это всегда удобно, потому что после демонстрации можно безболезненно удалить ее и не засорять схему. Этот пример показывает как работает групповая функция AVG со значением NULL:

Ещё раз COALESCE против NVL

Как часто вы видите запись, вроде этой:

nvl('x',user)

Думаете маленькая стоимость? А если вы выполняете его все время.

Что если мы напишем это так:

coalesce('x',user)

Нет разницы?

Использование функции NULLIF

Существует интересная, но малоизвестная функция NULLIF.

Узнать размер таблиц в схеме

Следующий запрос поможет узнать размер таблиц в схеме:

set pages 999
col segment_name format a40
col  mb   format 999,999,999

select segment_name, ceil(sum(bytes) / 1024 / 1024) "MB"
  from dba_segments
 where owner like 'SCOTT'
   and segment_type = 'TABLE'
 group by segment_name
 order by ceil(sum(bytes) / 1024 / 1024) desc
/

Результат:

SEGMENT_NAME                                     MB
---------------------------------------- ----------
BONUS                                             1
DEPT                                              1
DUMMY                                             1
EMP                                               1
SALGRADE                                          1

Источник.

Google Chrome. Добавление поиска по сайту Oracle

Google Chrome позволяет настроить поиск по сайту Oracle.
Сделать это довольно просто.

COALESCE “умнее” чем NVL

Такие операторы как NVL встречаются очень часто. Ещё в версии 8.1.7 это была единственная доступная функция, которая фильтровала null.

В версии 9i появилась функция coalesce которую называли обобщением NVL функции.

Использование неявных курсоров при DML-операциях

Наверняка у многих возникала необходимость внутри PL\SQL блока проверить, сколько строк было вставлено, удалено или обновлено последней DML-операцией. Все знают, что где-то оно должно быть, но не все представляют где. В SQL*Plus да и в различных оболочках для работы с БД мы видим надписи:

SQL> UPDATE test_tab t SET t.col1=1;

3 rows updated

Но как проверить результат выполнения в коде?

Получение DDL скрипта объекта

Иногда для каких-то целей может потребоваться скрипт создания объекта (например, CONSTRAINT, TABLE, INDEX и т.д).
Вот один из вариантов как это сделать:

declare
   v_text varchar2(32767);
   procedure print(p_msg in varchar2) is
      v_msg varchar2(32767);
   begin
      v_msg := p_msg;
      if length(v_msg) <= 255 then
         dbms_output.put_line(v_msg);
      else
         while length(v_msg) > 0 loop
            dbms_output.put_line(substr(v_msg, 1, 255));
            v_msg := substr(v_msg, 256);
         end loop;
      end if;
   end print;
begin
   v_text := dbms_lob.substr(lob_loc => DBMS_METADATA.GET_DDL('TABLE',
                                                              'EMPLOYEE',
                                                              'SCOTT'),
                             amount  => 32767,
                             offset  => 1);
   print(v_text);
end;

List all objects in a tablespace

set pages 999
col owner format a15
col owner format a7
col segment_name format a40
col segment_type format a13

select d.tablespace_name, d.owner, d.segment_name, d.segment_type
  from dba_segments d
 where tablespace_name = 'USERS'
   and d.owner = 'HR'
 order by d.owner, d.segment_name
/

Результат:

TABLESPACE_NAME      OWNER   SEGMENT_NAME                             SEGMENT_TYPE
-------------------- ------- ---------------------------------------- -------------
USERS                HR      BIG_ORDERS                               TABLE
USERS                HR      BIN$+0AeybK+SRmxaNEjrULGkw==$0           TABLE
USERS                HR      EMPLOYEES_TEMP                           TABLE
USERS                HR      EMP_TEMP                                 TABLE
USERS                HR      LOCK_HOLDERS                             TABLE
USERS                HR      LOGS_TAB                                 TABLE
USERS                HR      PARTS1                                   TABLE
USERS                HR      PARTS2                                   TABLE
USERS                HR      REJECTED_ORDERS                          TABLE
USERS                HR      VALID_ORDERS                             TABLE
 
10 rows selected

Источник.

Tablespaces that are >=80% full, and how much to add to make them 80% again

set pages 999 lines 100
col "Tablespace" for a20
col "Size MB"  for 999999999
col "%Used"  for 999
col "Add (80%)"  for 999999999
select tsu.tablespace_name "Tablespace",
       ceil(tsu.used_mb) "Size MB",
       100 - floor(tsf.free_mb / tsu.used_mb * 100) "%Used",
       ceil((tsu.used_mb - tsf.free_mb) / .8) - tsu.used_mb "Add (80%)"
  from (select tablespace_name, sum(bytes) / 1024 / 1024 used_mb
          from dba_data_files
         group by tablespace_name) tsu,
       (select ts.tablespace_name, nvl(sum(bytes) / 1024 / 1024, 0) free_mb
          from dba_tablespaces ts, dba_free_space fs
         where ts.tablespace_name = fs.tablespace_name(+)
         group by ts.tablespace_name) tsf
 where tsu.tablespace_name = tsf.tablespace_name(+)
   and 100 - floor(tsf.free_mb / tsu.used_mb * 100) >= 80
 order by 3, 4
/

Результат:

Tablespace             Size MB %Us Add (80%)
-------------------- --------- --- ---------
SYSAUX                     657  95  122,9375
SYSTEM                     700 100       169

Источник.

Show the files that comprise a tablespace

set lines 100
col tbn format a10
col file_name format a50
select d.tablespace_name tbn,
       d.file_name,
       ceil(d.bytes / 1024 / 1024) "size MB"
  from dba_data_files d
 order by 3 desc
/

Результат:

TBN        FILE_NAME                                             size MB
---------- -------------------------------------------------- ----------
SYSTEM     C:\ORA11G\ORADATA\ORCL\SYSTEM01.DBF                       700
SYSAUX     C:\ORA11G\ORADATA\ORCL\SYSAUX01.DBF                       657
EXAMPLE    C:\ORA11G\ORADATA\ORCL\EXAMPLE01.DBF                      100
UNDOTBS1   C:\ORA11G\ORADATA\ORCL\UNDOTBS01.DBF                       45
USERS      C:\ORA11G\ORADATA\ORCL\USERS01.DBF                          5

Источник.

Подсветка синтаксиса на Blogger

Здесь описан способ, по которому вы можете добавить подсветку кода в Blogger (сайт автора).

Tablespace usage

set pages 999
col tablespace_name format a30
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name,
       ceil(tsu.used_mb) "size MB",
       decode(ceil(tsf.free_mb), NULL, 0, ceil(tsf.free_mb)) "free MB",
       decode(100 - ceil(tsf.free_mb / tsu.used_mb * 100),
              NULL,
              100,
              100 - ceil(tsf.free_mb / tsu.used_mb * 100)) "% used"
  from (select tablespace_name, sum(bytes) / 1024 / 1024 used_mb
          from dba_data_files
         group by tablespace_name
        union all
        select tablespace_name || '  **TEMP**',
               sum(bytes) / 1024 / 1024 used_mb
          from dba_temp_files
         group by tablespace_name) tsu,
       (select tablespace_name, sum(bytes) / 1024 / 1024 free_mb
          from dba_free_space
         group by tablespace_name) tsf
 where tsu.tablespace_name = tsf.tablespace_name(+)
 order by 4 
/

Результат:

TABLESPACE_NAME         size MB    free MB % u
-------------------- ---------- ---------- ---
UNDOTBS1                     45         20  56
USERS                         5          2  73
EXAMPLE                     100         23  77
SYSAUX                      657         34  94
SYSTEM                      700          6  99
TEMP  **TEMP**               20          0 100
 
6 rows selected

Источник

Узнать размер extent'a

set pages 999
col segment_name format a20
col "MBytes" format 999,999,999
select de.segment_name, sum(de.bytes) / 1024 / 1024 MBytes
  from dba_extents de
 where de.owner = 'HR'
   and de.segment_name = 'EMPLOYEES'
 group by de.segment_name
 order by 2 desc;

Результат:

SEGMENT_NAME             MBYTES
-------------------- ----------
EMPLOYEES                0,0625

Узнать размер tablespacе'ов

set pages 999
col tbs format a20
col Mbytes format 999,999,999
select t.name tbs, ((sum(f.blocks) * t.blocksize) / 1024) / 1024 Mbytes
  from sys.ts$ t, sys.file$ f
 where f.ts# = t.ts#
 group by t.name, t.blocksize
 order by 2 desc
/

Результат:

TBS                      MBYTES
-------------------- ----------
SYSTEM                      300
SYSAUX                      120
EXAMPLE                     100
UNDOTBS1                     25
USERS                         5