1Z0-047 и новые вопросы по Oracle 12c

Oracle 12c еще не получил достаточно широкого распостранения, а в экзамен 1Z0-047 Oracle Database SQL Expert уже включили вопросы по новому функционалу.

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

• Identity Columns
• DEFAULT Values for Table Columns
• Invisible Columns
• LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins
• Row Limiting Clause for Top-N Queries
• Pattern Matching (MATCH_RECOGNIZE)
• PIVOT and UNPIVOT Operators (11g)
• Virtual Columns (11g)
• Grant WITH DELEGATE OPTION
• PERCENTILE_CONT and PERCENTILE_DISC
• Multiple Indexes on the Same Set of Columns
• WITH Clause Enhancements

Ну и в дополнение: 1Z0-047 Oracle Database SQL Expert. Новые вопросы 15 сентября 2014.

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

Создание репозитория и Notepad++

Возникла задача создания SVN-репозитория.

Объекты выгружались из девелоперской базы sql-скриптами TOAD (по моему мнению, он это умеет делать лучше всех).
Далее нужно было привести скрипты в нормальный вид и красиво отформатировать.
Для этих целей использовался Notepad++

Пока редактировал файлы сделал на память несколько скриншотов с регулярными выражениями.

Итак,
1. Удалить из всех файлов в папке оператор CREATE TABLE:

Шпаргалка по перемещению партиций таблицы в read-only tablespace

1. Создаем tablespace:

CREATE TABLESPACE readonly_tbs DATAFILE 
  '/u01/app/oracle/oradata/test/readonly01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON
/

2. Включаем на таблице row movement (если нужно):

ALTER TABLE test_table ENABLE ROW MOVEMENT
/

3. Перемещаем партиции таблицы в новый tablespace:

ALTER TABLE test_table MOVE PARTITION p2007_09 TABLESPACE readonly_tbs
/
ALTER TABLE test_table MOVE PARTITION p2007_10 TABLESPACE readonly_tbs
/
ALTER TABLE test_table MOVE PARTITION p2007_11 TABLESPACE readonly_tbs
/
ALTER TABLE test_table MOVE PARTITION p2007_12 TABLESPACE readonly_tbs
/

4. Переводим наш tablespace в режим "только для чтения":

ALTER TABLESPACE readonly_tbs READ ONLY
/

Готово.

Увеличение диска фиксированного размера в VirtualBox

Если при создании диска в VirtualBox выбран динамический диск, то увеличить его размер можно командой 

VBoxManage modifyhd [файл образа] --resize [размер в Мб]

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

Необходимо:

Как в Oracle получить все значения поля в одной строке

Возможны следующие следующие варианты решения:

1. С помощью xmlagg:

with t as (
    select 1 as id, 'aaa' as str from dual union all
    select 2 as id, 'bbb' as str from dual union all
    select 3 as id, 'ccc' as str from dual
)
SELECT xmlagg(xmlelement("node", t.str, ', ')).extract('//text()').getStringVal() as result1
     , rtrim(xmlagg(xmlelement("node", t.str, ', ').extract('//text()')),', ') as result2
FROM t;


RESULT1              RESULT2
------------------   ------------------
aaa, bbb, ccc,       aaa, bbb, ccc

Пересечение периодов в Oracle

Первая ситуация - проверка перекрытия и вывод ошибки

Исходные данные: таблица some_table с ключевым полем key_field и датой действия периода (поля date_from и date_till)
Требования: при вставке записи в таблицу проверять пересечение нового периода с уже существующими в таблице, и в случае перекрытия выводить ошибку.
Реализация: создается триггер на таблицу

CREATE OR REPLACE TRIGGER some_table_aiu_trg
 AFTER
  INSERT OR UPDATE
 ON some_table
REFERENCING NEW AS NEW OLD AS OLD
DECLARE
   l_dummy varchar2(1);
   CURSOR  c_overlap
   IS
     SELECT 'x'
       FROM some_table t1, some_table t2
      WHERE t1.key_field = t2.key_field
        AND t1.date_from <= t2.date_till
        AND t1.date_till >= t2.date_from
        AND t1.date_till <> t2.date_till;
BEGIN

   -- Проверяем, нет ли перекрытия
   OPEN c_overlap;
   FETCH c_overlap INTO l_dummy;
      IF c_overlap%found THEN
         CLOSE c_overlap;
         raise_application_error(-20050,'Period overlap another period');
      END IF;
   CLOSE c_overlap;

END;
/

Анализ блокировок Oracle

Продолжая серию шпаргалок по Oracle - сегодня речь пойдет о блокировках.

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

Вариант 1

SELECT
    h.sid as Holder,
    w.sid as Waiter,
    h.id1, h.id2, h.lmode, h.request, h.type, h.ctime, w.ctime as wtime
FROM V$LOCK h, V$LOCK w
WHERE (h.id1, h.id2, h.type) IN ((w.id1, w.id2, w.type))
AND h.request=0
AND w.request>0