Борьба с ORA-04030

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

ORA-04030: out of process memory when trying to allocate 32780 bytes (kxs-heap-b,bind var buf)

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

Исследование ошибки начал с

select * from v$process order by pga_used_mem DESC

В чемпионах сессия, которая удаляет данные.
Далее делаю

SELECT * FROM v$process_memory ORDER BY max_allocated DESC NULLS LAST;

получаю, что больше всего памяти выделено в сессии под CATEGORY SQL
Выполняю

SELECT * FROM v$process_memory_detail;

Пусто :(.
В ходе поисков решения натыкаюсь на отличную статью
Делаю

SELECT * FROM V$SQL_WORKAREA_ACTIVE;

Пусто, не мой вариант. У меня нет ни сортировок, ни хешей, ни битовых индексов.
Но далее в статье решение. Для того, что бы увидеть данные в v$process_memory_detail, нужно сказать ораклу, чтобы он их заполнил

SQL> ORADEBUG SETMYPID
Statement processed.
SQL> ORADEBUG DUMP PGA_DETAIL_GET 24
Statement processed.

и в v$process_memory_detail нахожу, что больше всего места занимает heap kxt.c: PL/SQL pgadef и ноту на метлинке PLSQL performance on 11.1.x much slower than previous DB versions (Doc ID 739064.1)
На лицо мой баг в Oracle 11.1.0.6
После применения рекомендованых

ALTER SYSTEM SET session_cached_cursors=0 SCOPE=SPFILE;
ALTER SYSTEM SET cursor_space_for_time=FALSE SCOPE=SPFILE;

все заработало как надо

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)
    )
  )

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