SelectFromDual: марта 2010

вторник, 23 марта 2010 г.

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

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

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.

Дальше »

воскресенье, 21 марта 2010 г.

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

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

Дальше »

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

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

Дальше »

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

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

Дальше »

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

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

Дальше »

пятница, 19 марта 2010 г.

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

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

Дальше »

четверг, 18 марта 2010 г.

Демонстрация обработки значений 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.
Сделать это довольно просто.

Дальше »

среда, 17 марта 2010 г.

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;

Дальше »

вторник, 16 марта 2010 г.

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

Источник.

понедельник, 15 марта 2010 г.

Подсветка синтаксиса на 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

вторник, 23 марта 2010 г.

воскресенье, 21 марта 2010 г.

пятница, 19 марта 2010 г.

четверг, 18 марта 2010 г.

среда, 17 марта 2010 г.

вторник, 16 марта 2010 г.

понедельник, 15 марта 2010 г.

вторник, 23 марта 2010 г.

воскресенье, 21 марта 2010 г.

пятница, 19 марта 2010 г.

четверг, 18 марта 2010 г.

среда, 17 марта 2010 г.

вторник, 16 марта 2010 г.

понедельник, 15 марта 2010 г.