Regular expression to remove comments

Regexp to remove comments, but not hints in Notepad++

\/\*[^+].*?\*/|--[^+]*?$

Опасные GLOBAL CONTEXT

Постигла такая же ошибка, что описана тут
Значение глобального контекста доступны/недоступны в зависимости от установок client identifier при коннекте.
Случаи выходят такие
DBMS_SESSION.SET_IDENTIFIER НЕ УСТАНОВЛЕН
- SET_CONTEXT.CLIENT_ID не установлен – работает
- SET_CONTEXT.CLIENT_ID установлен – работает
DBMS_SESSION.SET_IDENTIFIER УСТАНОВЛЕН
- SET_CONTEXT.CLIENT_ID не установлен – NULL НЕ РАБОТАЕТ
- SET_CONTEXT.CLIENT_ID установлен, но другой != DBMS_SESSION.SET_IDENTIFIER– NULL НЕ РАБОТАЕТ
- SET_CONTEXT.CLIENT_ID установлен = DBMS_SESSION.SET_IDENTIFIER - РАБОТАЕТ
Как видно работать будет в 2 случаях:
1. DBMS_SESSION.SET_IDENTIFIER не установлен, т.е. попробовать очищать его в logon триггере.
2. копировать глобальный контекст для каждого DBMS_SESSION.SET_IDENTIFIER, т.е. после подключения выполнять SET_CONTEXT с CLIENT_ID = DBMS_SESSION.SET_IDENTIFIER – получается не глобальный контекст, а локальный.
Т.е. в любом случае для глобальных контекстов потребуются дополнительные дейстивия: или ОЧИСТКА DBMS_SESSION.CLEAR_IDENTIFIER или КОПИРОВАНИЕ КОНТЕКСТА
Тесты, взятые из вышеуказанной статьи

CLEAR SCREEN 

DROP CONTEXT TEST_CONTEXT;

CREATE OR REPLACE PACKAGE my_ctx_pkg
AS
PROCEDURE set_context( p_namespace VARCHAR2,
p_attribute VARCHAR2,
p_value     VARCHAR2,
p_client_id VARCHAR2 DEFAULT NULL);
END;
/
CREATE OR REPLACE PACKAGE BODY my_ctx_pkg
AS
PROCEDURE set_context( p_namespace VARCHAR2,
p_attribute VARCHAR2,
p_value     VARCHAR2,
p_client_id VARCHAR2 DEFAULT NULL)
AS
BEGIN
DBMS_SESSION.SET_CONTEXT(p_namespace, p_attribute, p_value, client_id => p_client_id);
END set_context;
END;
/
CREATE CONTEXT TEST_CONTEXT USING my_ctx_pkg ACCESSED GLOBALLY;

PROMPT НЕ устанавливаем client identifier при коннекте
exec DBMS_SESSION.CLEAR_IDENTIFIER();

PROMPT В контексте НЕ устанавливаем -- значение ВИДИМ
exec my_ctx_pkg.set_context('TEST_CONTEXT','ATTR', 'VAL', p_client_id => NULL);
SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
SYS_CONTEXT('TEST_CONTEXT','ATTR') ATTR_VAL
FROM dual;

PROMPT В контексте УСТАНАВЛИВАЕМ -- значение ВИДИМ
exec my_ctx_pkg.set_context('TEST_CONTEXT','ATTR', 'VAL', p_client_id => 'set_in_context');
SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
SYS_CONTEXT('TEST_CONTEXT','ATTR') ATTR_VAL
FROM dual;


PROMPT УСТАНАВЛИВАЕМ client identifier при коннекте
BEGIN
  dbms_session.set_identifier(client_id => 'session connect_id');
END;
/

PROMPT В контексте НЕ устанавливаем -- значение НЕ ВИДИМ
exec my_ctx_pkg.set_context('TEST_CONTEXT','ATTR', 'VAL', p_client_id => NULL);
SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
SYS_CONTEXT('TEST_CONTEXT','ATTR') ATTR_VAL
FROM dual;

PROMPT В контексте устанавливаем ДРУГОЕ -- значение НЕ ВИДИМ
exec my_ctx_pkg.set_context('TEST_CONTEXT','ATTR', 'VAL', p_client_id => 'set_in_context');
SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
SYS_CONTEXT('TEST_CONTEXT','ATTR') ATTR_VAL
FROM dual;

PROMPT В контексте устанавливаем ТОЖЕ -- значение ВИДИМ
exec my_ctx_pkg.set_context('TEST_CONTEXT','ATTR', 'VAL', p_client_id => 'session connect_id');
SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
SYS_CONTEXT('TEST_CONTEXT','ATTR') ATTR_VAL
FROM dual;

Create database link in another schema

Here you can find couple of ways to create database link in another schema: using dbms_ijob and dbms_sys_sql

DECLARE
  l_link_owner VARCHAR2(30) := 'TEST_USER';
  l_query VARCHAR2(32767) := q'[
begin
    execute immediate 'CREATE DATABASE LINK test_link4
                          connect to    "test_user"
                          identified by "test_user"
                          using         ''localhost:1521/orcl''';
END;  
  ]';
BEGIN
  sys.dbms_isched.create_job(job_name            => 'CREATE_DBLINK',
                             job_style           => 'REGULAR',
                             program_type        => 'PLSQL_BLOCK',
                             program_action      => l_query,
                             number_of_arguments => 0,
                             schedule_type       => 'NOW', -- ONCE, NOW, EVENT
                             schedule_expr       => NULL,
                             queue_spec          => NULL,
                             start_date          => NULL,
                             end_date            => NULL,
                             job_class           => 'DEFAULT_JOB_CLASS', -- SELECT * FROM dba_scheduler_job_classes
                             comments            => NULL,
                             enabled             => TRUE,
                             auto_drop           => TRUE,
                             invoker             => l_link_owner,
                             sys_privs           => 1, -- sys.dbms_scheduler.check_sys_privs()
                             aq_job              => FALSE);
  COMMIT;
  dbms_isched.run_job('CREATE_DBLINK',
                      asynchronous   => TRUE,
                      invoker        => l_link_owner,
                      sys_privs      => 1/*sys.dbms_scheduler.check_sys_privs()*/);
END;
/

Foreign keys and nulls

Be careful with null enabled foreign key, especially multicolumn.
Test case below:

CREATE TABLE par(id1 NUMBER NOT NULL, id2 NUMBER NOT NULL, CONSTRAINT par_pk PRIMARY KEY(id1, id2));
Table created
INSERT INTO par VALUES(1,1);
1 row inserted
CREATE TABLE chi(id1 NUMBER, id2 NUMBER, CONSTRAINT par_fk FOREIGN KEY (id1, id2) REFERENCES par(id1, id2));
Table created
INSERT INTO chi VALUES(NULL, NULL);-- double nulls are allowed
1 row inserted
INSERT INTO chi VALUES(1, NULL);
1 row inserted
INSERT INTO chi VALUES(2, NULL);--!!!we have no 2 in par table!!!
1 row inserted
INSERT INTO chi VALUES(2, 1);
INSERT INTO chi VALUES(2, 1)
ORA-02291: integrity constraint (DOCKER.PAR_FK) violated - parent key not found

If one of the fields is null, the second field doesn’t need to present in parent table.

dblink, которого нет

SELECT * FROM GLOBAL_NAME;
GLOBAL_NAME
--------------------------
ORCL
SELECT COUNT(*) cnt FROM dba_db_links l WHERE l.db_link = 'ORCL';
       CNT
----------
         0
SELECT * FROM dual@ORCL;
DUMMY
-----
X

Liquibase test: sorting in includeAll

Let we have the following folder structure

folder_01
  subfolder_03
    file_1.sql
    file_2.sql
  subfolder_04
    file_1.sql
    file_2.sql
folder_02
  subfolder_01
    file_1.sql
    file_2.sql
  subfolder_02
    file_1.sql
    file_2.sql
file_01.sql
file_02.sql
zfile.sql

and I have changelog with includeAll:

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">
     <includeAll path="/multifolder/"/>
</databaseChangeLog>

the order of inclusion will be following

-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: multifolder.xml
-- Ran at: 19.09.16 15:40
-- Against: null@offline:oracle
-- Liquibase version: 3.5.1
-- *********************************************************************

SET DEFINE OFF;

-- Changeset multifolder/file_01.sql::1::includeall
select 'file01' f from dual;

-- Changeset multifolder/file_02.sql::1::includeall
select 'file02' f from dual;

-- Changeset multifolder/folder_01/subfolder_03/1.sql::1::includeall
select 'folder_01subfolder_03_file1' f from dual;

-- Changeset multifolder/folder_01/subfolder_03/2.sql::1::includeall
select 'folder_01subfolder_03_file2' f from dual;

-- Changeset multifolder/folder_01/subfolder_04/1.sql::1::includeall
select 'folder_01subfolder_04_file1' f from dual;

-- Changeset multifolder/folder_01/subfolder_04/2.sql::1::includeall
select 'folder_01subfolder_04_file2' f from dual;

-- Changeset multifolder/folder_02/subfolder_01/1.sql::1::includeall
select 'folder_02subfolder_01_file1' f from dual;

-- Changeset multifolder/folder_02/subfolder_01/2.sql::1::includeall
select 'folder_02subfolder_01_file2' f from dual;

-- Changeset multifolder/folder_02/subfolder_02/1.sql::1::includeall
select 'folder_02subfolder_02_file1' f from dual;

-- Changeset multifolder/folder_02/subfolder_02/2.sql::1::includeall
select 'folder_02subfolder_02_file2' f from dual;

-- Changeset multifolder/zfile.sql::1::includeall
select 'zfile.sql' f from dual;

So files and folders are sorted together level by level.

One moment you should to keep in mind. If I write

     <includeAll path="multifolder/"/>

instead of

     <includeAll path="/multifolder/"/>

I’ve got Unexpected error running Liquibase: Unknown Reason

ORA-12154: space is really matter

Got an stupid error ORA-12154: TNS:could not resolve the connect identifier specifed.
Double check tnsnames.ora, sqlnet.ora, TNS_ADMIN – all were correct
This one in tnsnames doesn’t work:

TEST =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL= TCP)(HOST= myhost)(Port= 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = db1)
)
)

and this one works:

TEST =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL= TCP)(HOST= myhost)(Port= 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = db1)
 )
)

Difference only in one space in penultimate line.

It’s interesting, that tnsping shows another error

TNS-12533: TNS:illegal ADDRESS parameters

but sqlplus

ORA-12154: TNS:could not resolve the connect identifier specifed

Sql*plus login.sql file

SQLPATH/login.sql

The User Profile is executed after the Site Profile and is intended to allow users to specifically customize their session. The User Profile script is generally named login.sql. SQL*Plus searches for the User Profile in your current directory, and then the directories you specify with the SQLPATH environment variable. SQL*Plus searches this colon-separated list of directories in the order they are listed.
Content of my file

set echo off
set termout off
set feedback off
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 120
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
define _editor="C:\Program Files\IDM Computer Solutions\UltraEdit-32\Uedit32.exe"
define sqlplprompt=idle
column sqlplprompt noprint new_value sqlplprompt
-- user@connect_identifier
--select LOWER(user) || '@' || '&_CONNECT_IDENTIFIER' sqlplprompt from dual;
-- user@sqlprompt
--select LOWER(user) || '@' || global_name sqlplprompt from global_name;
-- connect identifier only
define sqlplprompt=&_CONNECT_IDENTIFIER
set sqlprompt '&sqlplprompt.>'
ALTER SESSION SET statistics_level = ALL;
set termout on
set feedback on

--set timing ondefine

Also you can use glogin.sql file, but usually we don’t have access to it

The Site Profile script is generally named glogin.sql. SQL*Plus or the iSQL*Plus Server executes this script whenever a user starts a SQL*Plus or iSQL*Plus session and successfully establishes the Oracle Database connection. The Site Profile enables the DBA to set up SQL*Plus environment defaults for all users of a particular SQL*Plus or iSQL*Plus Application Server installation. Users cannot directly access the Site Profile.The Site Profile script is $ORACLE_HOME/sqlplus/admin/glogin.sql in UNIX, and %ORACLE_HOME%\sqlplus\admin\glogin.sql in Windows.

Oracle scheduler and commit

Oracle scheduler DOES EXPLICIT COMMIT.
Proof and test case

dba_hist_sqlstat statistics

Template for PL/SQL developer based on link

Counts execution statistics from dba_hist_sqlstat for given sql_id

select to_char(min(s.end_interval_time),'DD.MM.YYYY HH24:MI') sample_end
, q.sql_id
, q.plan_hash_value
, sum(q.EXECUTIONS_DELTA) executions
, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),1) pio_per_exec
, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),1) lio_per_exec
, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000000),3) sec_exec
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.SQL_ID=trim('&sqlid')
and s.snap_id = q.snap_id
and s.dbid = q.dbid
and s.instance_number = q.instance_number
&<name="start_time_dd_mm_YYYY_hh24_mi"
  prefix="and s.end_interval_time >= to_date(trim('"
  suffix="'),'DD.MM.YYYY hh24:mi')">
&<name="end_time_dd_mm_YYYY_hh24_mi"
  prefix="and s.begin_interval_time <= to_date(trim('"
  suffix="'),'DD.MM.YYYY hh24:mi')">
group by s.snap_id
, q.sql_id
, q.plan_hash_value
order by s.snap_id, q.sql_id, q.plan_hash_value;

Fast access to dba_hist_active_sess_history

dba_hist_active_sess_history view code

create or replace view sys.dba_hist_active_sess_history
(snap_id, dbid, instance_number, sample_id, sample_time, session_id, session_serial#, session_type, flags, user_id, sql_id, is_sqlid_current, sql_child_number, sql_opcode, sql_opname, force_matching_signature, top_level_sql_id, top_level_sql_opcode, sql_plan_hash_value, sql_plan_line_id, sql_plan_operation, sql_plan_options, sql_exec_id, sql_exec_start, plsql_entry_object_id, plsql_entry_subprogram_id, plsql_object_id, plsql_subprogram_id, qc_instance_id, qc_session_id, qc_session_serial#, px_flags, event, event_id, seq#, p1text, p1, p2text, p2, p3text, p3, wait_class, wait_class_id, wait_time, session_state, time_waited, blocking_session_status, blocking_session, blocking_session_serial#, blocking_inst_id, blocking_hangchain_info, current_obj#, current_file#, current_block#, current_row#, top_level_call#, top_level_call_name, consumer_group_id, xid, remote_instance#, time_model, in_connection_mgmt, in_parse, in_hard_parse, in_sql_execution, in_plsql_execution, in_plsql_rpc, in_plsql_compilation, in_java_execution, in_bind, in_cursor_close, in_sequence_load, capture_overhead, replay_overhead, is_captured, is_replayed, service_hash, program, module, action, client_id, machine, port, ecid, dbreplay_file_id, dbreplay_call_counter, tm_delta_time, tm_delta_cpu_time, tm_delta_db_time, delta_time, delta_read_io_requests, delta_write_io_requests, delta_read_io_bytes, delta_write_io_bytes, delta_interconnect_io_bytes, pga_allocated, temp_space_allocated)
as
select /* ASH/AWR meta attributes */
       ash.snap_id, ash.dbid, ash.instance_number,
       ash.sample_id, ash.sample_time,
       /* Session/User attributes */
       ash.session_id, ash.session_serial#,
       decode(ash.session_type, 1,'FOREGROUND', 'BACKGROUND'),
       ash.flags,
       ash.user_id,
       /* SQL attributes */
       ash.sql_id,
       decode(bitand(ash.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'),
       ash.sql_child_number, ash.sql_opcode,
       (select command_name from DBA_HIST_SQLCOMMAND_NAME
        where command_type = ash.sql_opcode
        and dbid = ash.dbid) as sql_opname,
       ash.force_matching_signature,
       decode(ash.top_level_sql_id, NULL, ash.sql_id, ash.top_level_sql_id),
       decode(ash.top_level_sql_id, NULL, ash.sql_opcode,
              ash.top_level_sql_opcode),
       /* SQL Plan/Execution attributes */
       ash.sql_plan_hash_value,
       decode(ash.sql_plan_line_id, 0, to_number(NULL), ash.sql_plan_line_id),
       (select operation_name from DBA_HIST_PLAN_OPERATION_NAME
        where  operation_id = ash.sql_plan_operation#
          and  dbid = ash.dbid) as sql_plan_operation,
       (select option_name from DBA_HIST_PLAN_OPTION_NAME
        where  option_id = ash.sql_plan_options#
          and  dbid = ash.dbid) as sql_plan_options,
       decode(ash.sql_exec_id, 0, to_number(NULL), ash.sql_exec_id),
       ash.sql_exec_start,
       /* PL/SQL attributes */
       decode(ash.plsql_entry_object_id,0,to_number(NULL),
              ash.plsql_entry_object_id),
       decode(ash.plsql_entry_object_id,0,to_number(NULL),
              ash.plsql_entry_subprogram_id),
       decode(ash.plsql_object_id,0,to_number(NULL),
              ash.plsql_object_id),
       decode(ash.plsql_object_id,0,to_number(NULL),
              ash.plsql_subprogram_id),
       /* PQ attributes */
       decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_instance_id),
       decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_id),
       decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_serial#),
       decode(ash.px_flags,      0, to_number(NULL), ash.px_flags),
       /* Wait event attributes */
       decode(ash.wait_time, 0, evt.event_name, NULL),
       decode(ash.wait_time, 0, evt.event_id,   NULL),
       ash.seq#,
       evt.parameter1, ash.p1,
       evt.parameter2, ash.p2,
       evt.parameter3, ash.p3,
       decode(ash.wait_time, 0, evt.wait_class,    NULL),
       decode(ash.wait_time, 0, evt.wait_class_id, NULL),
       ash.wait_time,
       decode(ash.wait_time, 0, 'WAITING', 'ON CPU'),
       ash.time_waited,
       (case when ash.blocking_session = 4294967295
               then 'UNKNOWN'
             when ash.blocking_session = 4294967294
               then 'GLOBAL'
             when ash.blocking_session = 4294967293
               then 'UNKNOWN'
             when ash.blocking_session = 4294967292
               then 'NO HOLDER'
             when ash.blocking_session = 4294967291
               then 'NOT IN WAIT'
             else 'VALID'
        end),
       (case when ash.blocking_session between 4294967291 and 4294967295
               then to_number(NULL)
             else ash.blocking_session
        end),
       (case when ash.blocking_session between 4294967291 and 4294967295
               then to_number(NULL)
             else ash.blocking_session_serial#
        end),
       (case when ash.blocking_session between 4294967291 and 4294967295
               then to_number(NULL)
             else ash.blocking_inst_id
          end),
       (case when ash.blocking_session between 4294967291 and 4294967295
               then NULL
             else decode(bitand(ash.flags, power(2, 3)), NULL, 'N',
                         0, 'N', 'Y')
          end),
       /* Session's working context */
       ash.current_obj#, ash.current_file#, ash.current_block#,
       ash.current_row#, ash.top_level_call#,
       (select top_level_call_name from DBA_HIST_TOPLEVELCALL_NAME
        where top_level_call# = ash.top_level_call#
        and dbid = ash.dbid) as top_level_call_name,
       decode(ash.consumer_group_id, 0, to_number(NULL),
              ash.consumer_group_id),
       ash.xid,
       decode(ash.remote_instance#, 0, to_number(NULL), ash.remote_instance#),
       ash.time_model,
       decode(bitand(ash.time_model,power(2, 3)),0,'N','Y')
                                                         as in_connection_mgmt,
       decode(bitand(ash.time_model,power(2, 4)),0,'N','Y')as in_parse,
       decode(bitand(ash.time_model,power(2, 7)),0,'N','Y')as in_hard_parse,
       decode(bitand(ash.time_model,power(2,10)),0,'N','Y')as in_sql_execution,
       decode(bitand(ash.time_model,power(2,11)),0,'N','Y')
                                                         as in_plsql_execution,
       decode(bitand(ash.time_model,power(2,12)),0,'N','Y')as in_plsql_rpc,
       decode(bitand(ash.time_model,power(2,13)),0,'N','Y')
                                                       as in_plsql_compilation,
       decode(bitand(ash.time_model,power(2,14)),0,'N','Y')
                                                       as in_java_execution,
       decode(bitand(ash.time_model,power(2,15)),0,'N','Y')as in_bind,
       decode(bitand(ash.time_model,power(2,16)),0,'N','Y')as in_cursor_close,
       decode(bitand(ash.time_model,power(2,17)),0,'N','Y')as in_sequence_load,
       decode(bitand(ash.flags,power(2,5)),NULL,'N',0,'N','Y')
                                                       as capture_overhead,
       decode(bitand(ash.flags,power(2,6)), NULL,'N',0,'N','Y' )
                                                           as replay_overhead,
       decode(bitand(ash.flags,power(2,0)),NULL,'N',0,'N','Y') as is_captured,
       decode(bitand(ash.flags,power(2,2)), NULL,'N',0,'N','Y' )as is_replayed,
       /* Application attributes */
       ash.service_hash, ash.program,
       substrb(ash.module,1,(select ksumodlen from x$modact_length)) module,
       substrb(ash.action,1,(select ksuactlen from x$modact_length)) action,
       ash.client_id,
       ash.machine, ash.port, ash.ecid,
       /* DB Replay info */
       ash.dbreplay_file_id, ash.dbreplay_call_counter,
       /* stash columns */
       ash.tm_delta_time,
       ash.tm_delta_cpu_time,
       ash.tm_delta_db_time,
       ash.delta_time,
       ash.delta_read_io_requests,
       ash.delta_write_io_requests,
       ash.delta_read_io_bytes,
       ash.delta_write_io_bytes,
       ash.delta_interconnect_io_bytes,
       ash.pga_allocated,
       ash.temp_space_allocated
from WRM$_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY ash, WRH$_EVENT_NAME evt
where      ash.snap_id          = sn.snap_id(+)
      and  ash.dbid             = sn.dbid(+)
      and  ash.instance_number  = sn.instance_number(+)
      and  ash.dbid             = evt.dbid
      and  ash.event_id         = evt.event_id;
comment on table SYS.DBA_HIST_ACTIVE_SESS_HISTORY is 'Active Session Historical Statistics Information';

WRH$_ACTIVE_SESSION_HISTORY partitioned by DBID and SNAP_ID columns, so the fastest way to query this view – using snap_id ranges.
You can determine right snap_id from dba_hist_snapshot view.

This query uses partition pruning and run fast

select * from dba_hist_active_sess_history WHERE snap_id >= 12345

and this is not (it usees FTS on WRH$_ACTIVE_SESSION_HISTORY ash)

select * from dba_hist_active_sess_history WHERE sample_time >= SYSTIMESTAMP -1 

Be careful with subqueries. The following queries can also use FTS:

select * from dba_hist_active_sess_history WHERE snap_id >= (SELECT :1 FROM dual);
select * from dba_hist_active_sess_history WHERE snap_id >= (SELECT min(snap_id) FROM Dba_Hist_Snapshot WHERE begin_interval_time >= SYSTIMESTAMP - 1)

So, the safest way is determine snap_id in separate query.

PL/SQL Developer session window

В окне списка сессий PL/SQL Developer (Tools -> Sessions) можно добавить кучу полезных вкладок, которые будут выполнять скрипты и запросы, получая любое значение из подсвеченной сессии в списке через :[ИМЯ_КОЛОНКИ v$session]. В настоящее время в стоковом PL/SQL Developer (версия 11) есть 5 вкладок:
Cursors
SQL Text
Statistics
Locks
Sql Monitor

Добавлять свои вкладки можно при помощи кнопки с гаечным ключиком -> Details

В настоящий момент я использую

План запроса dbms_xplan

Особое внимание /* concatenate */ из последней строчки – результат будет сцеплен в одно поле, его можно скопировать и вставить в другое окно для детального анализа.

SELECT t.plan_table_output || CHR(10) plan_table_output 
FROM table(dbms_xplan.display_cursor(:sql_id, :sql_child_number,format => 'ADVANCED')) t
/* concatenate */

План запроса из v$

SELECT decode(id, 1, child_number) || decode(:sql_address, '00', '-P') AS c,
       output_rows AS tot_r,
       last_output_rows AS r,
       rpad(' ', depth * 3) || operation || ' ' || options ||
       nvl2(object_name, ' -> ', '') || object_name AS op,
       cost,
       cardinality AS card,
       bytes,
       access_predicates AS "ACCESS",
       filter_predicates AS filter,
       round(temp_space / 1024 / 1024) AS temp_mb,
       partition_start || nvl2(partition_start, ' - ', '') || partition_stop AS p,
       partition_id,
       other,
       other_tag,
       cpu_cost,
       io_cost,
       distribution,
       object_owner,
       optimizer,
       position,
       search_columns,
       executions,
       last_starts,
       starts,
       last_cr_buffer_gets,
       cr_buffer_gets,
       last_cu_buffer_gets,
       cu_buffer_gets,
       last_disk_reads,
       disk_reads,
       last_disk_writes,
       disk_writes,
       round(last_elapsed_time / 1000000, 2) AS last_ela_time,
       round(elapsed_time / 1000000, 2) AS elapsed_time,
       policy,
       estimated_optimal_size,
       estimated_onepass_size,
       last_memory_used,
       last_execution,
       last_degree,
       total_executions,
       optimal_executions,
       onepass_executions,
       multipasses_executions,
       round(active_time / 1000000, 2) AS active_time_avg,
       max_tempseg_size,
       last_tempseg_size
FROM (SELECT *
      FROM v$sql_plan_statistics_all
      WHERE address = hextoraw(:sql_address)
      AND hash_value = :sql_hash_value
      UNION ALL
      SELECT *
      FROM v$sql_plan_statistics_all
      WHERE address = hextoraw(:prev_sql_addr)
      AND hash_value = :prev_hash_value) t
CONNECT BY address = PRIOR address
    AND hash_value = PRIOR hash_value
    AND child_number = PRIOR child_number
    AND PRIOR id = parent_id
START WITH id = 1
ORDER BY address, hash_value, child_number DESC, id, position

SQL Workarea

Объем памяти, потребляемой сессией. Правда не очень часто пригождается

SELECT operation_type,
       policy,
       estimated_optimal_size,
       estimated_onepass_size,
       last_memory_used,
       last_execution,
       last_degree,
       total_executions,
       optimal_executions,
       onepass_executions,
       multipasses_executions,
       active_time,
       max_tempseg_size,
       last_tempseg_size
FROM v$sql_workarea
WHERE address = hextoraw(:sql_address)
AND hash_value = :sql_hash_value

Таким способом достаточно удобно заниматься troubleshooting’ом, если известа сессия, которая испытывает проблемы. Очень удобный и мощный механизм.

Copy Outlook folders to another machine

Возникла проблема переноса правил Outlook с машины на машину. Решения, приводимые в интернете с import-export почтового ящика не работают из-за недостатка прав. Единственное решение в этом случае – скрипт на VBA
На основании скрипта выгрузки дописал создание структуры папок.
Ограничения:

• переносятся папки из Inbox в Inbox
• не работают русские буквы в названиях папок
• если папка с таким именем есть, то создание упадет с ошибкой

Ошибки, кроме русских букв, легко устраняются исправлением кода

1. На машине, откуда надо экспортировать файлы заходим в Outlook и оттуда в редактор Visual Basic (Alt-F11).
2. Вставляем в него скрипт, приведенный ниже. Запускаем процедуру ExportFolderNames (F5)
3. В результате работы процедуры на рабочем столе создается файл outlookfolders.txt. Передаем его на машину, где надо создать папки и кладем на рабочий стол
4. На машине-получателе запускаем редактор Visual Basic (Alt-F11), вставляем скрипт и запускаем createFolders
5. Проверяем и радуемся

Текст макроса (особо не причесывал, просто добился работоспособности)

Private myFile As String
Private Structured As Boolean
Private Base As Integer

Private Function getIndent(folderName)
  i = 1
  Do Until Mid(folderName, i, 1) <> "-"
    i = i + 1
  Loop

  getIndent = i - 1

End Function
Public Sub createFolders()
  Dim objNewFolder, objParentFolder As Outlook.Folder
  Dim myFile As String, line, folderName As String

  Dim parentObjects(100) As Outlook.Folder
  ' init for inbox folder
  Set parentObjects(0) = Session.GetDefaultFolder(olFolderInbox)

  myFile = GetDesktopFolder() & "\outlookfolders.txt"
  Open myFile For Input As #1

  Do Until EOF(1)
    Line Input #1, line
     curIndent = getIndent(line)

     If curIndent > 0 Then
       folderName = Right(line, Len(line) - curIndent)
       Set objParentFolder = parentObjects(curIndent - 1)
       'MsgBox ("create folder " + folderName + " in " + objParentFolder.Name + " level=" & curIndent)
       Set objNewFolder = objParentFolder.Folders.Add(folderName)
       Set parentObjects(curIndent) = objNewFolder
     End If
  Loop
  Close #1
  Set objNewFolder = Nothing
  Erase parentObjects

End Sub

Public Sub ExportFolderNames()
  Dim F As Outlook.MAPIFolder
  Dim Folders As Outlook.Folders

  Set F = Session.GetDefaultFolder(olFolderInbox)
  Set Folders = F.Folders

  Dim Result As Integer

  Structured = True


  myFile = GetDesktopFolder() & "\outlookfolders.txt"
  Base = Len(F.FolderPath) - Len(Replace(F.FolderPath, "\", "")) + 1

  WriteToATextFile (StructuredFolderName(F.FolderPath, F.Name))

  LoopFolders Folders

  Set F = Nothing
  Set Folders = Nothing
End Sub


Private Function GetDesktopFolder()
  Dim objShell
  Set objShell = CreateObject("WScript.Shell")
  GetDesktopFolder = objShell.SpecialFolders("Desktop")
  Set objShell = Nothing
End Function

Private Sub LoopFolders(Folders As Outlook.Folders)
  Dim F As Outlook.MAPIFolder

  For Each F In Folders
    WriteToATextFile (StructuredFolderName(F.FolderPath, F.Name))
    LoopFolders F.Folders
  Next
End Sub

Private Sub WriteToATextFile(OLKfoldername As String)
  fnum = FreeFile()

  Open myFile For Append As #fnum
    Print #fnum, OLKfoldername
  Close #fnum
End Sub

Private Function StructuredFolderName(OLKfolderpath As String, OLKfoldername As String) As String
  If Structured = False Then
    StructuredFolderName = Mid(OLKfolderpath, 3)
  Else
    Dim i As Integer
    i = Len(OLKfolderpath) - Len(Replace(OLKfolderpath, "\", ""))

    Dim x As Integer
    Dim OLKprefix As String
    For x = Base To i
      OLKprefix = OLKprefix & "-"
    Next x

    StructuredFolderName = OLKprefix & OLKfoldername
  End If
End Function

Staсkedit hangs on starting

В нижеуказанном Stackedit случилась небольшая неприятность.
После попытки импортировать страницу (Import URL) он наглухо повис и перестал открываться после перезагрузки. Судя по всему пытался отрисовать страницу с большим количеством HTML не преобразованного в маркдаун.
После тщетных попыток c переоткрытиями, запретами скриптов и т.д. были предприняты исследования по результатам которых:

• база хранится локально в так называемом Web storage
• в Firefox есть просмоторщик (правда без возможности редактирования) этого Web storage. Редактирование обещают в следующих релизах. Причем под Mozilla есть несколько плагинов, которые позволяют смотреть/редактировать Web storage. Ни один из них так и не заработал, причины были разные. Какой-то уменьшал окно до минимума, какие-то просто висли.
• При помощи Noscript отключаем скрипты, заходим на https://stackedit.io/
• Запускаем Storage inspector (Shift-F9, если включен в настройках или открыть панель разработчика и включить в настройках). В Local storage ищем, какие файлы хранятся на компьютере
• Находим файл, который подвешивает систему, копируем его ключ и вставляем в скрипт очистки

var filename = 'file.q0rBqPMvChLNtkN55HimybX9';

localStorage.removeItem(filename + '.content');
localStorage.removeItem(filename + '.title');
localStorage.removeItem(filename + '.editorEnd');
localStorage.removeItem(filename + '.editorStart');
localStorage.removeItem(filename + '.publish');
localStorage.removeItem(filename + '.selectTime');
localStorage.removeItem(filename + '.sync');
localStorage.removeItem(filename + '.title');
localStorage.setItem('file.list', localStorage.getItem('file.list').replace(';' + filename + ';', ';'));
console.log(localStorage.getItem('file.list'));

• Запускаем скрипт в консоли и база очищена

Статья, приведшая к зависанию будет удалена!

Вообще Stackedit отличный сервис для публикаций! Вот только в последее время публикация в Blogger работает раз из пяти

Written with StackEdit.

Технология дня

Отличная статья от Toon Koppelaars с перечнем front-end технологий, которые появились за последние несколько лет. И многие из них, как правильно заметил автор

technologies du-jour: hot today, forgotten tomorrow

Надеюсь, что он прав и нам, разработчикам БД, можно расслабиться и продолжать развиваться в своей области.

DBLINK и сессии

В ходе исследований по loopback links чуть было не попал впросак с отловом сессий, создаваемых для dblink.
Судя по всему Oracle создает сессию для dblink один раз. В доказательство этого сделаем after logon trigger, который будет собирать информацию о подключениях

SQL> DROP TABLE log_session PURGE;

Table dropped.

SQL> CREATE TABLE log_session(username VARCHAR2(30), conn_time timestamp, info VARCHAR2(4000));

Table created.
SQL> CREATE OR REPLACE TRIGGER ta_connect AFTER logon ON DATABASE
  2  BEGIN
  3    INSERT INTO log_session VALUES (USER, SYSTIMESTAMP, NULL);
  4    COMMIT;
  5  END;
  6  /

Trigger created.

SQL> SHOW ERRORS
No errors.
SQL> CONNECT SYSTEM/manager
Connected.
SQL> SELECT COUNT(*) FROM log_session;

  COUNT(*)
----------
         1

Мы подключились, строка вставилась

SQL> SELECT * FROM dual@loopback;

D
-
X

SQL> SELECT COUNT(*) FROM log_session;

  COUNT(*)
----------
         2

Первое обращение по dblink, создалась сессия

SQL> SELECT * FROM dual@loopback;

D
-
X

SQL> SELECT COUNT(*) FROM log_session;

  COUNT(*)
----------
         2

Второе обращение по dblink – сессия не создалась, используется предыдущая

SQL> SELECT 'Hello' FROM a@loopback WHERE ROWNUM=1;

'HELL
-----
Hello

SQL> SELECT COUNT(*) FROM log_session;

  COUNT(*)
----------
         2

Поменяем таблицу – результат тот же. Новой сессии нет

SQL> CONNECT SYSTEM/manager
Connected.
SQL> SELECT COUNT(*) FROM log_session;

  COUNT(*)
----------
         3

Переподключились, теперь счетчик стал 3

SQL> SELECT * FROM dual@loopback;

D
-
X

SQL> SELECT COUNT(*) FROM log_session;

  COUNT(*)
----------
         4

В свежей сессии для dblink выполняется еще одно подключение

Loopback links, ORA-04091 и dirty read в Oracle

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

CREATE TABLE a(n NUMBER);
Table created
INSERT INTO a VALUES(100);
1 row inserted
INSERT INTO a VALUES(200);
1 row inserted
INSERT INTO a VALUES(300);
1 row inserted
COMMIT;
Commit complete
CREATE OR REPLACE FUNCTION a_avg RETURN NUMBER AS
  l_avg NUMBER;
BEGIN
  SELECT AVG(n) INTO l_avg FROM a;
  dbms_output.put_line('avg=' || l_avg);
  RETURN l_avg;
END a_avg;
/
Function created
SHOW ERRORS
No errors for FUNCTION SYSTEM.A_AVG
UPDATE a SET n = a_avg();
UPDATE a SET n = a_avg()
ORA-04091: table SYSTEM.A is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.A_AVG", line 4

Мы создали простую таблицу и функцию, которая считает среднее значение по этой таблице. С помощью функции мы попробуем усреднить все значения.
В результате мы получаем ORA-04091 без каких-либо триггеров

Далее в статье приводится способ обхода через loopback database link. Модифицируем немного функцию и вставляем @loopback

CREATE OR REPLACE FUNCTION a_avg_loopback RETURN NUMBER AS
  l_avg NUMBER;
BEGIN
  SELECT AVG(n) INTO l_avg FROM a@loopback;
  dbms_output.put_line('avg=' || l_avg);
  RETURN l_avg;
END a_avg_loopback;
/
Function created
SHOW ERRORS
No errors for FUNCTION SYSTEM.A_AVG_LOOPBACK
UPDATE a SET n = a_avg_loopback();
avg=200
avg=233.333333333333333333333333333333333333
avg=244.444444444444444444444444444444444444
3 rows updated
ROLLBACK;
Rollback complete

Ошибка пропала, но… Через loopback мы смогли увидеть данные, которые еще не закоммичены. Этакий dirty read, но скорее всего мы просто присоединяемся к той же транзакции. Причем, как видно, результат для каждой строчки считается с учетом обновленных строк.

UPD: дальнейшее исследование показало, что loopback link вообще не создал отдельной сессии.

UPD2: сессия создается, но ровно 1 раз за сессию. Исследование тут

Классификация ограничений

Наткнулся на очень интересную статью адепта триггеров о констрейнтах.
Классификация вышла такая:

• Статические ограничения
  
  • на атрибут – salary > 10000
  • на группу атрибутов – усы могут быть только у мужчин
  • табличные – проверяется несколько строчек таблицы, например уникальные ключи
  • базы данных – проверяются несколько таблиц, например сумма заказа не превышает остатка по счету клиента
• Динамические ограничения – те, что невозможно проверить по снапшоту базы данных (т.е. запросом или несколькими запросами), они связаны с изменением во времени. Например по каждому документу в течении рабочего дня должен быть выписан акт.

Такие классификации полезны для построения однозначных архитектур, т.к. формализовав правила реализации каждого из видов мы не получим всего многообразия методов :)

Parallel merge

Немножко о parallel merge в упрощенном виде. Для того, что бы часть insert/update шла параллельно необходимо:
1. alter session enable parallel dml;
2. указывать в хинте таблицу в которую мержим

merge /*+ parallel(t1) */ into t1
USING (select c1, c2 from t2) t2
on (t1.c1 = t2.c1)
when matched then
         update set t1.c2 = t1.c2
when not matched then
INSERT(c1, c2) values(t2.c1, t2.c2)

Взято отсюда
К сожалению проблема с прода, когда параллельность не возникала на стенде не воспроизвелась.

Если Merge не использует индекс

Если Merge не использует индекс, то можно попробовать явно указать колонки явно. Т.е. вместо

merge into t1
using t2

написать

merge into (select c1, c2 from t1) t1
using (select c3 from t2) t2

Это же относится и к избыточной работе с temp/памяти для HASH JOIN.
Отличные исследования по этому поводу:
https://alexanderanokhin.wordpress.com/2012/07/18/dont-forget-about-column-projection/
https://jonathanlewis.wordpress.com/2016/06/06/merge-precision/