Clear empty partitions

Clear empty partitions

DECLARE
    g_owner      VARCHAR2(30) := USER;
    g_table_name VARCHAR2(30) := 'PART_TEST';

    PROCEDURE sp_execute(p_str VARCHAR2) IS
    BEGIN
        dbms_output.put_line(p_str);
        EXECUTE IMMEDIATE p_str;
    END sp_execute;

    FUNCTION is_segment_empty(p_table_owner    VARCHAR2,
                              p_table_name     VARCHAR2,
                              p_partition_name VARCHAR2) RETURN BOOLEAN IS
        l_tmp  NUMBER;
        l_stmt VARCHAR2(32767);
    BEGIN
        l_stmt := 'SELECT count(*) from ' || p_table_owner || '.' || p_table_name || CASE
                      WHEN p_partition_name IS NOT NULL THEN
                       ' partition (' || p_partition_name || ') '
                  END || ' where rownum = 1';
        EXECUTE IMMEDIATE l_stmt
            INTO l_tmp;
        IF l_tmp <> 0
        THEN
            RETURN FALSE;
        ELSE 
            RETURN TRUE;
        END IF;

    END is_segment_empty;

BEGIN
    FOR rec IN (SELECT * FROM all_tab_partitions p WHERE p.table_owner = g_owner AND table_name = g_table_name)
    LOOP
        IF is_segment_empty(rec.table_owner,
                            rec.table_name,
                            rec.partition_name)
        THEN
            sp_execute('ALTER TABLE ' || rec.table_owner || '.' || rec.table_name || ' TRUNCATE PARTITION ' ||
                       rec.partition_name || ' DROP ALL STORAGE UPDATE INDEXES');
        END IF;
    END LOOP;
END;
/

Get index ddl

Извлечение кода создания индексов без мусора.Не подойдет для global партиционированных индексов

BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); 
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PARTITIONING',FALSE); 
END;
/  

with ind AS (
SELECT owner owner, index_name name FROM all_indexes WHERE table_name = '[table_name]'
UNION ALL
SELECT '[index_owner]' owner, '[index_name]' NAME FROM dual
)
SELECT to_char(
  dbms_metadata.get_ddl(object_type => 'INDEX', name => ind.NAME, schema => ind.owner)
  || CASE WHEN i.partitioned = 'YES' THEN ' LOCAL' END
  )
  , ind.*
FROM ind, all_indexes i
WHERE ind.owner = i.owner AND ind.name = i.index_name
;

ORA-01031: insufficient privileges

Приключилась тут у меня на одной из баз ошибка ORA-01031: insufficient privileges
Произошла она в ходе экспериментов с клонированием баз с одного сервера на другой (печальный опыт показал, что в Oracle 11.1 SE не работает Active Database Duplication и что в статье Тима Холла кучка ошибок, а индусы на металинке дают воркэраунды с опечатками в командах). Для Active Database Duplication нужно явно прописывать экземпляр в listener.ll

Я мог подключится к базе через os authentication (т.е. при помощи sqlplus / as sysdba), но никак не мог при помощи LISTENER (т.е. sqlplus sys/password@db1 as sysdba). Поэтому варианты с
невключением в группу dba и SQLNET.AUTHENTICATION_SERVICES были отброшены сразу.

Проверив параметры REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE, наличие самого password file в $ORACLE_HOME/dbs

ll $ORACLE_HOME/dbs/ora*
-rw-r----- 1 oracle oinstall 2560 2015-06-15 20:58 /opt/oracle/product/11.1/db/dbs/orapwdb1

и даже на всякий случай его пересоздав password file я уже было совсем расстроился, пока не сравнил имя password file с тем, что прописано в listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB1)
      (ORACLE_HOME = /opt/oracle/product/11.1/db)
      (SID_NAME = DB1)
    )
  )

Передирая у Тима Холла, я не исправил регистр символов!!! При этом в переменной ORACLE_SID у меня имя в нижнем регистре

echo $ORACLE_SID
db1

Исправив listener.ora на нижний регистр

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = db1)
      (ORACLE_HOME = /opt/oracle/product/11.1/db)
      (SID_NAME = db1)
    )
  )

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

dba_tab_modifications + merge

Небольшой тест для определения, как в dba_tab_modifications отображается результат оператора MERGE
1. Для того, что бы результаты оказались в dba_tab_modifications нужно вызвать DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
2. Сбор статистики по таблице убирает (удаляет) строчку из dba_tab_modifications.
3. MERGE учитывается по отдельности, как будто это 2 оператора
Пример

DROP TABLE a PURGE;
CREATE TABLE a(a NUMBER, val VARCHAR2(10));

INSERT INTO a 
SELECT LEVEL, LPAD('x', 10, 'x')
FROM dual
CONNECT BY LEVEL <= 100;

BEGIN
  DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
END;
/
SELECT table_name, inserts, updates, deletes FROM dba_tab_modifications t WHERE table_name = 'A';

MERGE INTO a
USING (
SELECT LEVEL + 75 a,  LPAD('Z', 10, 'Z') val
FROM dual
CONNECT BY LEVEL <= 75
) t
ON (a.a = t.a)
WHEN MATCHED THEN UPDATE SET a.val = t.val
WHEN NOT MATCHED THEN INSERT VALUES (t.a, t.val);

BEGIN
  DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
END;
/
SELECT table_name, inserts, updates, deletes FROM dba_tab_modifications t WHERE table_name = 'A';

UPDATE a SET val =  LPAD('x', 10, 'x') WHERE ROWNUM <=22;
BEGIN
  DBMS_STATS.gather_table_stats(NULL, 'A');
END;
/
SELECT table_name, inserts, updates, deletes FROM dba_tab_modifications t WHERE table_name = 'A';

Результаты

SQL> 
Table dropped
Table created
100 rows inserted
PL/SQL procedure successfully completed
TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ---------- ---------- ----------
A                                     100          0          0
75 rows merged
PL/SQL procedure successfully completed
TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ---------- ---------- ----------
A                                     150         25          0
22 rows updated
PL/SQL procedure successfully completed
TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ---------- ---------- ----------

SQL> 

Partition pruning monitoring

Зачастую partition pruning не так просто увидеть в плане запроса: если используются bind переменные или подзапрос, то в плане будет стоять pstart и pstop KEY. Для этих случев Oracle сделал отдельный эвент 10128. Для его использования необходимо создать таблицу kkpap_pruning. Результаты можно просматривать в файле, а можно и в таблице. К сожалению внятной информации, как интерпретировать трейсы найти не удалось, так что ниже небольшое исследование:

Ниже приведены примеры для 4 запросов:
* без PP
* PP для одной партиции по равенству
* PP для 2 партиций для случая >=
* отсутствие PP для !=
* пример с bind-переменными, для которого по event мы увидим, какой PP имел место.
* пример с join таблиц
Инициализируем окружение и создаем объекты при помощи скрипта

SET SERVEROUTPUT OFF
SET PAGESIZE 0 FEEDBACK OFF
SET LINESIZE 32000
COL plan_table_output FORMAT a300


drop table kkpap_pruning;
DROP TABLE part_test;

create table kkpap_pruning 
(partition_count  NUMBER
,iterator         VARCHAR2(32)
,partition_level  VARCHAR2(32)
,order_pt         VARCHAR2(12)
,call_time        VARCHAR2(12)
,part#            NUMBER
,subp#            NUMBER
,abs#             NUMBER
);


CREATE TABLE part_test (
  id1 NUMBER NOT NULL,
  pad VARCHAR2(1000),
  val NUMBER)
PARTITION BY RANGE (id1) (
  PARTITION p1 VALUES LESS THAN (1),
  PARTITION p2 VALUES LESS THAN (2),
  PARTITION p3 VALUES LESS THAN (3),
  PARTITION p4 VALUES LESS THAN (4)
);

INSERT INTO part_test
SELECT MOD(ROWNUM, 4) , LPAD('x', 1000, 'x'), ROWNUM
FROM dual
CONNECT BY LEVEL <= 1000;  

BEGIN dbms_stats.gather_table_stats(USER, 'PART_TEST'); END;
/

NB: SET SERVEROUTPUT OFF необходим для того, что бы dbms_xplan.display_cursor работал правильно.

Пример 1. Нет PP, т.к. нет фильтрации

-- Без предикатов
alter session set events '10128 trace name context forever, level 2';
SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
);

SELECT * FROM TABLE(dbms_xplan.display_cursor());
------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |       |    32 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |           |     1 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|           |  1000 |    32   (0)| 00:00:01 |     1 |     4 |
|   3 |    TABLE ACCESS FULL | PART_TEST |  1000 |    32   (0)| 00:00:01 |     1 |     4 |
------------------------------------------------------------------------------------------
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [0, 3]
   index = 0
  current partition: part# = 0, subp# = 1048576, abs# = 0
  current partition: part# = 1, subp# = 1048576, abs# = 1
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3

здесь и далее приведены части трейса, которые относятся к стадии выполнения call time = RUN. Как видно, в первом случае мы посещаем все 4 партиции нашей таблицы, что видно и из плана выполнения и из трейса

Пример 2. Равенство

SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1=1
);
SELECT * FROM TABLE(dbms_xplan.display_cursor());
-----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |       |       |     9 (100)|          |       |       |
|   1 |  SORT AGGREGATE         |           |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|           |   250 |   750 |     9   (0)| 00:00:01 |     2 |     2 |
|*  3 |    TABLE ACCESS FULL    | PART_TEST |   250 |   750 |     9   (0)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ID1"=1)
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [1, 1]
   index = 1
  current partition: part# = 1, subp# = 1048576, abs# = 1   

Пример 3. Неравенство с 2 партициями

SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1>=2
);
SELECT * FROM TABLE(dbms_xplan.display_cursor());

-------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |       |       |    17 (100)|          |       |       |
|   1 |  SORT AGGREGATE           |           |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|           |   583 |  1749 |    17   (0)| 00:00:01 |     3 |     4 |
|   3 |    TABLE ACCESS FULL      | PART_TEST |   583 |  1749 |    17   (0)| 00:00:01 |     3 |     4 |
-------------------------------------------------------------------------------------------------------
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [2, 3]
   index = 2
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3

Пример 4. Отсутвие PP для !

SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1!=2
);
SELECT * FROM TABLE(dbms_xplan.display_cursor());
--------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |       |       |    32 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |           |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|           |   750 |  2250 |    32   (0)| 00:00:01 |     1 |     4 |
|*  3 |    TABLE ACCESS FULL | PART_TEST |   750 |  2250 |    32   (0)| 00:00:01 |     1 |     4 |
--------------------------------------------------------------------------------------------------
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [0, 3]
   index = 0
  current partition: part# = 0, subp# = 1048576, abs# = 0
  current partition: part# = 1, subp# = 1048576, abs# = 1
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3

Пример 6. PP с bind-переменными для 2 партиций

VAR pstart NUMBER
VAR pstop NUMBER
EXEC :pstart := 2;
EXEC :pstop := 3;
SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1 BETWEEN :pstart AND :pstop
);
SELECT * FROM TABLE(dbms_xplan.display_cursor());
--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |       |       |    32 (100)|          |       |       |
|   1 |  SORT AGGREGATE            |           |     1 |     3 |            |          |       |       |
|*  2 |   FILTER                   |           |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|           |   583 |  1749 |    32   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL      | PART_TEST |   583 |  1749 |    32   (0)| 00:00:01 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:PSTOP>=:PSTART)
   4 - filter(("ID1">=:PSTART AND "ID1"<=:PSTOP))

Partition Iterator Information:
  partition level = PARTITION
  call time = COMPILE
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [0, 3]
   index = 0
  current partition: part# = 0, subp# = 1048576, abs# = 0
  current partition: part# = 1, subp# = 1048576, abs# = 1
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [2, 3]
   index = 2
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3   

Для этого случая я оставил в трейсе часть от call time = COMPILE. Как видно, из нее вообще ничего не понятно.

Результаты запроса

select * from KKPAP_PRUNING t;

PARTITION_COUNT ITERATOR                         PARTITION_LEVEL                  ORDER_PT     CALL_TIME         PART#      SUBP#       ABS#
--------------- -------------------------------- -------------------------------- ------------ ------------ ---------- ---------- ----------
              2 RANGE                            PARTITION                        ASCENDING    RUN               0        1048576          0
              2 RANGE                            PARTITION                        ASCENDING    RUN               1        1048576          1
              2 RANGE                            PARTITION                        ASCENDING    RUN               2        1048576          2
              2 RANGE                            PARTITION                        ASCENDING    RUN               3        1048576          3
              2 RANGE                            PARTITION                        ASCENDING    RUN               1        1048576          1
              2 RANGE                            PARTITION                        ASCENDING    RUN               2        1048576          2
              2 RANGE                            PARTITION                        ASCENDING    RUN               3        1048576          3
              2 RANGE                            PARTITION                        ASCENDING    RUN               0        1048576          0
              2 RANGE                            PARTITION                        ASCENDING    RUN               1        1048576          1
              2 RANGE                            PARTITION                        ASCENDING    RUN               2        1048576          2
              2 RANGE                            PARTITION                        ASCENDING    RUN               3        1048576          3
              3 RANGE                            PARTITION                        ASCENDING    RUN               2        1048576          2
              3 RANGE                            PARTITION                        ASCENDING    RUN               3        1048576          3

Пример 5. join

SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1 IN (
      SELECT ROWNUM FROM dual CONNECT BY LEVEL <=2)
);

SELECT * FROM TABLE(dbms_xplan.display_cursor());

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |           |       |       |    11 (100)|          |       |       |
|   1 |  SORT AGGREGATE                   |           |     1 |    16 |            |          |       |       |
|   2 |   NESTED LOOPS                    |           |   250 |  4000 |    11  (10)| 00:00:01 |       |       |
|   3 |    VIEW                           | VW_NSO_1  |     1 |    13 |     3  (34)| 00:00:01 |       |       |
|   4 |     HASH UNIQUE                   |           |     1 |       |     3  (34)| 00:00:01 |       |       |
|   5 |      COUNT                        |           |       |       |            |          |       |       |
|   6 |       CONNECT BY WITHOUT FILTERING|           |       |       |            |          |       |       |
|   7 |        FAST DUAL                  |           |     1 |       |     2   (0)| 00:00:01 |       |       |
|   8 |    PARTITION RANGE ITERATOR       |           |   250 |   750 |     8   (0)| 00:00:01 |   KEY |   KEY |
|*  9 |     TABLE ACCESS FULL             | PART_TEST |   250 |   750 |     8   (0)| 00:00:01 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - filter("ID1"="ROWNUM")

Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [1, 1]
   index = 1
  current partition: part# = 1, subp# = 1048576, abs# = 1
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [2, 2]
   index = 2
  current partition: part# = 2, subp# = 1048576, abs# = 2

Выводы

Кратко можно заключить следующее:
1. В трейсе смотреть на блоки, относящиеся к call time = RUN
2. Какие партиции посещались видно с строке iterator = RANGE [2, 3] и в строках current partition: part# = 2, subp# = 1048576, abs# = 2.
3. Если нет доступа к файловой системе сервера, то результаты можно посмотреть и в таблице KKPAP_PRUNING.
4. К сожалению, ни названий таблиц, ни номеров объектов в файле и таблице нет, поэтому разобраться со сложным запросом будет очень тяжело. Наверное придется ориентироваться на количество партиций и на порядок операций

Flashback features mindmap

Подборка материалов по управлению планами запросов

Подборка статей про управление планами запросов: SQL profile, SQL Plan Baseline, SQL Patch, Stored Outlines:

1. https://iusoltsev.wordpress.com/2014/12/14/hardcoded-hints-query-plan-spm/
2. http://www.fors.ru/upload/magazine/05/http_texts/russia_ruoug_deev_sql_plans.html
3. https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=108825519711641&id=1931944.1&_afrWindowMode=0&_adf.ctrl-state=y0xdrlgpx_4

dbms_xplan.display

Для тех у кого нет прав вызывать паклеты на prod базе, но есть права на чтение таблиц ниже приведен способ, как с использованием вспомогательной базы данных вывести план запроса в привычном виде.
Основные мысли навеяны статьей http://douggault.blogspot.ru/2009/04/trouble-with-dbmsxplan.html

Итак, план запроса мы будем строть при помощи функции dbms_xplan.display. Описание параметров этой функции такое

DBMS_XPLAN.DISPLAY(
   table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',
   statement_id  IN  VARCHAR2  DEFAULT  NULL, 
   format        IN  VARCHAR2  DEFAULT  'TYPICAL',
   filter_preds  IN  VARCHAR2 DEFAULT NULL);

Теперь вместо первого параметра мы передадим имя представления/материализованного представления/таблицы. Представление строим над копией таблицы v$sql_plan с продакшена. Это позволяет обойти следующие неприятности:

• в dba_hist_sql_plan не копируются колонки filter_predicates и access_predicates
• ошибку ORA-22992: cannot use LOB locators selected from remote tables (немного нечестную, т.к. я не тяну LOB колонок в запросах. Второй вариант ее обхода – с использованием материализованного представления см. ниже).

Текст представления:

CREATE OR REPLACE VIEW vw_plan_table_prod AS 
SELECT
sql_id AS statement_id, 
plan_hash_value AS plan_id, 
timestamp, 
remarks, 
operation, 
options, 
object_node, 
object_owner, 
object_name, 
object_alias, 
OBJECT# object_instance, 
object_type, 
optimizer, 
search_columns, 
id, 
parent_id, 
depth, 
position, 
cost, 
cardinality, 
bytes, 
other_tag, 
partition_start, 
partition_stop, 
partition_id, 
other, 
remarks other_xml, 
distribution, 
cpu_cost, 
io_cost, 
temp_space, 
access_predicates, 
filter_predicates, 
projection, 
time, 
qblock_name,
child_number
FROM v$sql_plan_prod

Для получения плана запроса использовать

SET LINESIZE 300 
SET PAGESIZE 0 
SET HEADING OFF
COLUMN PLAN_TABLE_OUTPUT FORMAT A300 TRUNCATE

SELECT *
FROM   TABLE(dbms_xplan.display(table_name   => 'vw_plan_table_prod',
                                statement_id => '0jhz0hkckw4q4',
                                format       => 'ALL',
                                filter_preds => 'plan_id=1275605462 and child_number=0'));

Дополнительный фильтр в параметре filter_preds опционален и используется, если для запроса построено несколько планов.
Объеснение как работает фильтр и как он трансформирует запрос к таблице с планами в статье, указанной выше.

Вариант 2.

Используем материализованное представление

CREATE MATERIALIZED VIEW vw_plan_table_prod 
REFRESH ON DEMAND 
AS 
SELECT
sql_id AS statement_id, 
plan_hash_value AS plan_id, 
timestamp, 
remarks, 
operation, 
options, 
object_node, 
object_owner, 
object_name, 
object_alias, 
OBJECT# object_instance, 
object_type, 
optimizer, 
search_columns, 
id, 
parent_id, 
depth, 
position, 
cost, 
cardinality, 
bytes, 
other_tag, 
partition_start, 
partition_stop, 
partition_id, 
other, 
remarks other_xml, 
distribution, 
cpu_cost, 
io_cost, 
temp_space, 
access_predicates, 
filter_predicates, 
projection, 
time, 
qblock_name,
child_number
FROM v$sql_plan@loopback
;

SET LINESIZE 300 
SET PAGESIZE 0 
SET HEADING OFF
COLUMN PLAN_TABLE_OUTPUT FORMAT A300 TRUNCATE

SELECT * FROM v$diag_info WHERE NAME = 'Default Trace File';
--ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
BEGIN dbms_mview.refresh(list => 'vw_plan_table_prod', method => 'C', atomic_refresh => FALSE); END;
/

SELECT *
FROM   TABLE(dbms_xplan.display(table_name   => 'vw_plan_table_prod',
                                statement_id => 'gd90ygn1j4026',
                                format       => 'ALL',
                                filter_preds => ''));

Аналогично можно построить запрос для dba_hist_sql_plan

Настройки плана счетов в OEBS

Запрос для получения настроек плана счетов в OEBS для набора книг (или всех наборов книг)

select b.set_of_books_id, b.name, b.chart_of_accounts_id, s.application_column_name, s.segment_name, t.flex_value_set_name
from gl_sets_of_books b, 
  FND_ID_FLEX_SEGMENTS s, 
  FND_FLEX_VALUE_SETS t
WHERE b.chart_of_accounts_id = s.id_flex_num
  AND b.set_of_books_id IN (...)
  AND t.flex_value_set_id = s.flex_value_set_id
ORDER BY b.set_of_books_id, segment_num;

Сохраняем html-список в файл при помощи jquery

Для удобства нужно было выкачать список книжек с сайта orelly.
Список представлял из себя следующую html-структуру:

<select class="ebook-select" size="15" name="book">

    <option value=""></option>
    <option data-date="Jan 2015" data-isbn="9781491909300" data-title=" Living Clojure" value="56358">

         Living Clojure 9781491909300 Jan 2015

    </option>

Для красоты нужно было выдернуть только data-title из каждого элемента списка.
Задача решилась простым jquery

$("[name]=books > option").each(function(){
    console.log($(this).attr('data-title'));
})

но вот проблема – ни Chrome (просто не нашел такого пункта), ни FF (почему-то фолдит вывод, предлагая его раскрыть) не дали сохранить вывод в файл.

Вывод получилось сделать при помощи тулзы debugout.js
Как пользоваться

• Заменяем в файле количество сохраняемых строк

self.maxLines = 5000; // if autoTrim is true, this many most recent lines are saved

• Прогоняем файл в консоли (тут файл приводить не буду, вдруг обновят и улучшат)
• Инициируем логгилку

var bugout = new debugout();

• Прогоняем скрипт, заменив с bugout

$("[name]=books > option").each(function(){
    bugout.log($(this).attr('data-title'));
})

• Сохраняем полученный файл

bugout.downloadLog()

Просто, понятно, быстро

Удаление CONSTRAINT и индексов

Помню несколько лет назад надо было почистить схему, удалив CONSTRAINTы из табличек.
При этом база вела себя как хотела: то сама удаляла индексы и ругалась при их повторном удалении, то оставляла их. Тогда дело решилось простой обработкой exception, сейчас пришла пора разобраться что тут к чему.
При удалении CONSTRAINT возможны следующие варианты:
Вариант 1: ALTER TABLE tbl DROP CONSTRAINT cons – базовый вариант, который все обычно и пишут
Вариант 2: ALTER TABLE tbl DROP CONSTRAINT cons CASCADE – расширеный вариант, который на самом деле удаляет связанные foreign key
Вариант 3: ALTER TABLE tbl DROP CONSTRAINT cons CASCADE DROP INDEX – машина для убийств

Опция cascade управляет только foreign key, которые ссылаются на таблицы

-- Инициализация
DROP TABLE chi_tst PURGE;
DROP TABLE tst PURGE;

CREATE TABLE tst (pk NUMBER NOT NULL, CONSTRAINT tst_pk PRIMARY KEY (pk));

INSERT INTO tst VALUES (1);

CREATE TABLE chi_tst(fk NUMBER NOT NULL, CONSTRAINT fk_chi_tst FOREIGN KEY (fk) REFERENCES tst(pk));

INSERT INTO chi_tst VALUES(1);

ALTER TABLE tst DROP CONSTRAINT tst_pk
ORA-02273: this unique/primary key is referenced by some foreign keys

ALTER TABLE tst DROP CONSTRAINT tst_pk CASCADE;
Table altered

Далее мы не будет использовать опцию cascade, т.к. у нас будет одна таблица

Когда нужно добавлять DROP INDEX, а когда индекс удалится сам? Ответ простой: DROP INDEX лучше добавлять всегда, когда хочется гарантировано удалить индекс.

Для того, что бы ответить на вопрос, когда индекс удалится сам, проведем следующий набор тестов:
Тест 1: primary key + атоматически создаваемый (уникальный индекс)
Тест 2: primary key + вручную созданный уникальный индекс
Тест 3: primary key + вручную созданый неуникальный индекс
Для каждого из тестов попробуем удалить индекс без опции (вариант 1) и с опцией (вариант 3)

-- индекс создан автоматически, удаляем без drop index
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL, CONSTRAINT tst_pk PRIMARY KEY (pk));
Table created
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------

-- индекс создан автоматически, удаляем с DROP INDEX
CREATE TABLE tst (pk NUMBER NOT NULL, CONSTRAINT tst_pk PRIMARY KEY (pk));
Table created
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk DROP INDEX;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------

как видно, если индекс создается автоматически, то он удаляется в любом случае

-- Уникальный индекс вручную, без drop index
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
CREATE UNIQUE INDEX pk_tst ON tst(pk);
Index created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk);
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------
PK_TST                         UNIQUE

-- Уникальный индекс вручную, с DROP INDEX
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
CREATE UNIQUE INDEX pk_tst ON tst(pk);
Index created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk);
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk  DROP INDEX;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------

Как видно, если индекс создавался вручную, то он не удаляется, если не указать DROP INDEX

Теперь сделаем constraint на обычном индексе. Этот вариант пробую из-за на достаточно подробном, но немного ошибочном посте Ричарда Фута

-- Неуникальный индекс вручную, без drop index
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
CREATE INDEX pk_tst ON tst(pk);
Index created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk);
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------
PK_TST                         NONUNIQUE

-- Неуникальный индекс вручную, с DROP INDEX
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
CREATE INDEX pk_tst ON tst(pk);
Index created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk);
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk  DROP INDEX;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------

Как видно, если индекс создавался вручную, то он не удаляется, если не указать DROP INDEX

И последний вариант, который узнал из вышеуказанного поста Фута, основанный на DEFFERABLE CONSTRAINT. Для его поддержки Oracle автоматически создает неуникальный индекс

-- DEFFERABLE, автоматически созданный, неуникальный, без drop index
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk) deferrable;
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------
TST_PK                         NONUNIQUE

-- DEFFERABLE, автоматически созданный, неуникальный, c DROP INDEX
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk) deferrable;
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk DROP INDEX;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------

Вывод: без DROP INDEX удаление CONSTRAINT не удаляет даже автоматически создаваемые индексы

Итого

Для того, что бы гарантировано удалить индекс – добавляйте DROP INDEX к команде. В противном случае индекс удаляется только если он был автоматически создан и является уникальным (т.е. в одном случае из четырех)

Скрипт для поиска бекапных таблиц

Ищем сегменты в имени которых есть заданная последовательность (TMP, BKP, 6 и более цифр подряд, …) для которых есть сегменты без этой последовательности.
Скрипт расширяется в подзапросе template, поэтому если при бекапе добавляется и число и суффикс TMP, то необходимо задать нужную последовательность для замены такого имени.

WITH templates AS (
  SELECT '_?TMP_?' t FROM dual UNION ALL
  SELECT '_?TEMP_?' t FROM dual UNION ALL
  SELECT '_?BKP_?' t FROM dual UNION ALL
  SELECT '_?BACKUP_?' t FROM dual UNION ALL
  SELECT '\d{6,}' t FROM dual
),
seg AS (
  SELECT owner, segment_name, segment_type, SUM(bytes)/1024/1024 mb
  FROM Dba_Segments_Prod_h 
  WHERE owner NOT IN ('SYS', 'SYSTEM', 'WMSYS', 'FLOWS_030100', 'APEX_030200')
  GROUP BY owner, segment_name, segment_type
),
bad_segments AS (
SELECT /*+ materialize*/DISTINCT * 
FROM (
  SELECT regexp_replace(s.segment_name, templates.t) new_name, owner, segment_name
  FROM seg s, templates
  )
WHERE new_name <> segment_name 
)
SELECT s.*, b.new_name "Exists"
FROM seg s, bad_segments b
WHERE s.segment_name = b.segment_name
  AND EXISTS (SELECT NULL FROM seg s2 WHERE s2.segment_name = b.new_name)
ORDER BY 4 DESC

Blogspot для разработчика

Замучился оформлять во встроенном редакторе blogspot статьи, в которых есть код. Пишешь в блокноте - пропадают переносы, пишешь в редакторе blospot – не вставляются теги. А скакать туда-сюда совершенно неинтересно.

И тут совершенно случайно в сткатье на хабре нашел упоминание о разметчике http://marxi.co/ – markdown editor для evernote.
Все отлично и писать удобно, и заголовочки, и жирненьким, и код вставляется оформленый сполпинка. Но синхронизация только с evernote. В blogspot только через export as html. Опять не очень удобно (конечно можно поискать средства для публикации заметок Evernote, но куда девать нажитое непосильным трудом :)).

После непродолжительных поисков нашел еще несколько markdown редакторов, в том чисдле и вот этот https://stackedit.io/
К счастью в нем оказался весь необходимый функционал + синхронизация с blogspot.

Следующим шагом встал вопрос о подсветке синтаксиса в коде. На текущий момент выбор сводится к
* HighLight JS – куча тем, языков, автоопределение языка. В итоге он и стал победлителем
* google-code-prettify – немного тем. Отказался, т.к. слишком долго переформатировать старые статьи. И номера строк почему-то не заработали
* SyntaxHighlighter – красивый, очень функциональный, но не нашел способ удобной разметки (тут можно прочитать про способы и подключение

В итогет победителем вышел HighLight JS. Он не только работает без лишних телодвижений после публикации из StackEdit, но и смог разметить (не очень поломав) весь существующий код, который был написан без тегов PRE
Что бы его подключить в панели управления необходимо открыть
Шаблон -> Изменить HTML
В конец секции HEAD добавить

<script src='https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js' type='text/javascript'/>
    <link href='http://yandex.st/highlightjs/8.2/styles/magula.min.css' rel='stylesheet'/>
    <script src='http://yandex.st/highlightjs/8.2/highlight.min.js'/>

<script type='text/javascript'>
  hljs.configure({
    languages: ["sql", "javascript", "bash"]}); 
</script>
</head>

Первая строка, если jquery еще не добавлен. В languages список языков, который необходим, или убрать этот блок, если необходимо автоопределение среди всех языков.
Для разметки уже написанных статей пришлось отказаться от стандартной инициализации

<script>hljs.initHighlightingOnLoad();</script>

заменив ее на

  <script type='text/javascript'>
$('code').each(function(i, block) {
  hljs.highlightBlock(block);
});
  </script>
</body>

который, как видно, необходимо поместить в конец секции BODY
Функционала копирования, номеров строк или разноцветных четных и нечетных строк не хватает, но надеюсь на развитие проекта.

Written with StackEdit.

Free space in tablespace

Script to calculate free space in oracle tablespace.

SELECT a.tablespace_name,
a.file_name,
a.file_id,
used_gb "Used Size GB",
a.max_gb "Max Size Gb",
a.max_gb - a.used_gb + NVL(c.free_gb, 0) free_space
FROM
(SELECT tablespace_name, file_name, file_id, a.bytes /1024/1024/1024 used_gb,
CASE WHEN a.maxbytes = 0 THEN bytes ELSE a.maxbytes END /1024/1024/1024 max_gb
FROM dba_data_files a) a,
(
SELECT tablespace_name, file_id, SUM(BYTES) / 1024/1024/1024 free_gb
FROM DBA_FREE_SPACE c
GROUP BY tablespace_name, file_id
) c
WHERE a.tablespace_name = c.tablespace_name(+)
AND a.file_id = c.file_id(+);


TNS-12535 on idle session

Столкнулся с проблемой, что через 10-20 минут неактивности пользовательской сессии (или слишком долгого выполнения процедуры) выкидывалась ошибка

Fatal NI connect error 12170.

  VERSION INFORMATION:
    TNS for Linux: Version 10.2.0.4.0 - Production
    Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.4.0 - Production
    TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.4.0 - Production
  Time: 06-FEB-2015 10:33:38
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505
    TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0

Краткое исследование вопроса привело к следующим возможным причинам:
* firewall/антивирус на клиенте
* firewall/антивирус на сервере
* firewall/NAT/железка посередине между клиентом и сервером.
Что-то из этого обрывало сессию после нескольких минут в состоянии idle
Т.к. проблема появилась после переезда на новый сервер в новое облако, то первый вариант отпал.

Железками и софтом посередине мы не управляем.

Решил применить 2 способа:
1. Согласно документу Doc ID 1628949.1 установил SQLNET.EXPIRE_TIME=n Where <n> is a non-zero value set in minutes. Этот параметр задает пинговалку ораклом клиента пустыми пакетами в указанный промежуток времени.
2. Согласно доке Doc ID 257650.1 первый способ мог и не помочь, поэтому попробовал поковыряться с TCP KeepAlive. В линуксе проверил:

# sysctl -A | grep keep
net.ipv4.tcp_keepalive_intvl = 75
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_time = 7200

echo "net.ipv4.tcp_keepalive_time = 300" >> /etc/sysctl.conf

/sbin/sysctl -p

Но если верить разделу Enable Linux kernel keepalive support for TCP connections вот этой статьи, заставить этот параметр работать не так то просто.

В итоге комлекс из этих двух мер проблему решил.

ORA-27037 in dbca during database creation

Напоролся на ошибку при создании базы dbca на Oracle 10.2.0.4.
 Откуда появилась эта база и кто как ее ставил -- я не знаю.
Ошибка такая:

Control file created with size 430 blocks
Prior to RESETLOGS processing...
ALTER SYSTEM ARCHIVE LOG ALL USING BACKUP CONTROLFILE start
Database is not in archivelog mode
ALTER SYSTEM ARCHIVE LOG ALL USING BACKUP CONTROLFILE complete
*** 2015-02-04 13:06:22.632
Thread 1: Sequence reset to 1.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/db1/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory

Дальше логи советуют, что немаловажно, открыть базу в режиме UPGRADE (к сожалению логи уже потер).

ORA-39700: database must be opened with UPGRADE option

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

Решение такое: создавать базу используя шаблон Custom

Перенос партицированных объектов в другой TS

Таблицы
Лемма 1: партицированную таблицу нельзя MOVE в другой TABLESPACE, иначе ORA-14511
Лемма 1.1: для партицированной таблицы делать alter table part_test modify default attributes tablespace new_part;  

Индексы
Лемма 2: партицированный индекс нельзя REBUILD, иначе ORA-14086
Лемма 2.1: Для партицированного индекса делать ALTER INDEX idx1 MODIFY DEFAULT ATTRIBUTES TABLESPACE new_part;

LOB
Лемма 3. Для LOB all_lobs.tablespace_name судя по всему указывает на DEFAULT_TABLESPACE и меняется вместе с таблицей
Лемма 3.1 Создание LOB через ALTER TABLE ADD доабвляет LOB в тот же TS, в котором находится партиция. Т.е. если партиции раскиданы по разным TS, то и LOB будет раскидан по TS
Лемма 3.2 Перенос партиции не переносит LOB. Лобы переносят отдельными командами ALTER TABLE ... MOVE LOB(...) STORE AS (TABLESPACE ...);
Лемма 3.3 Перенос LOB для партиционированной таблицы напрямую заканчивается ошибкой ORA-14511. LOB переносить в цикле поштучно for lobpart_rec in (SELECT * FROM All_Lob_Partitions WHERE table_name = 'PART_TEST' AND lob_name = 'SYS_LOB0000094688C00003$$') ...;