Никак не мог понять как работает PCT fast refresh в Materialized View без мат вью логов, пробавал гуглить но ничего разжеванного так и не нашел, точнее не так, нашел http://www.freepatentsonline.com/20060253483.pdf но то ли лыжи не едут то ли я не догоняю :)

В итоге просто прогнал рефреш мат вьюшки с трассировкой и от туда вытащил часть деталей.
Сделал на примере схеме "SH" и таблицы "SALES"
поправил немного табл "SALES" под свои изыскания :



Потом создал мат вьюшку для теста PCT fast refresh


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


Далее сделал два Update:

update SALES set QUANTITY_SOLD = 5, AMOUNT_SOLD = AMOUNT_SOLD*1.5

Далее включил трассировку и прогнал рефреш:

Из trace файла вытащил следующее - обновляются целиком партиции в которых были изменения:
Партиции для обновления определяются так:
из таблицы "SYS.SNAP$" вытаскиваем "RSCN" - SCN когда рефрешилась наша PCT MatView "SALES_MV" 

SELECT * FROM SYS.SNAP$ WHERE SOWNER = 'SH' AND VNAME = 'SALES_MV';
--------------------------
RSCN = 678670

в таблице "sys.""tabpart$" хранятся в "SNAP1" SCN изменения для партиции таблицы источника "SH.SALES" :

select * from tabpart$ where bo# = 52463;

bo# = dba_objects.object_id,  object_id для "SH.SALES" - 52463 
Далее определяются партиции в "SH.SALES"  с SCN больше чем SCN рефреша MatView "SALES_MV"

select nvl(max(spare1),0) from tabpart$ where bo# = 52463 and spare1 <= 678670;
---------------------
670321

Id-шники партиций в которых были изменения:


select * from dba_objects where object_id in (select tp.obj# from tabpart$ tp where tp.bo# = 52463 and tp.spare1 > 670321);
---------------------
SALES_Q1_2001
SALES_Q4_2001


Вот. Но :) когда в таблице источнике транкетиться партиция SCN в "tabpart$" - не меняются: 

alter table SALES truncate partition SALES_Q1_2001;

При этом транкированные партиции появляются в "sys.sumpartlog$"

select * from sys.sumpartlog$ where bo# = 52463;

Все, здесь мое терпение кончилось :) и на дальнейшие изыскания я забил.

Возникла задача на работе маппить клиентов из разных систем, маппить будет человек,
но нужно максимально облегчить ему работу поиском похожих клиентов,
а он уже будет окончательно решать один и тот же это клиент или нет.

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

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

Скрипт для таблицы

-- Create table
create table GLOBAL_CLIENTS
(
ID NUMBER not null,
CLIENT_NAME VARCHAR2(300 CHAR) not null,
DOMICILE_COUNTRY VARCHAR2(8 CHAR),
INDUSTRY_DESC VARCHAR2(100 CHAR),
WEBSITE_URL VARCHAR2(1000 CHAR),
EXT_CLIENT_ID VARCHAR2(20 CHAR) not null,
INSERT_DATE DATE default sysdate,
UPDATE_DATE DATE
);

-- Create/Recreate primary, unique and foreign key constraints
alter table GLOBAL_CLIENTS add constraint PK_GLOBAL_CLIENTS primary key (ID);



Создание индекса по колонкам "client_name", "domicile_country"

drop index glbl_clnt$clnt_name$txti;

begin
ctx_ddl.drop_preference('GLCLNT_DTSTR');
ctx_ddl.drop_preference('GLCLNT_WRDLST');
ctx_ddl.drop_preference ('GLCLNT_LEXER');
ctx_ddl.drop_preference('GLCLNT_FLTR');
ctx_ddl.drop_stoplist('GLCLNT_STPLST');
end;
/

begin
ctx_ddl.create_preference('GLCLNT_DTSTR', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('GLCLNT_DTSTR', 'columns', 'client_name, domicile_country');

ctx_ddl.create_preference('GLCLNT_WRDLST', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('GLCLNT_WRDLST','FUZZY_MATCH','ENGLISH');
ctx_ddl.set_attribute('GLCLNT_WRDLST','STEMMER','ENGLISH');
ctx_ddl.set_attribute('GLCLNT_WRDLST','WILDCARD_MAXTERMS', '15000' );

ctx_ddl.create_preference ('GLCLNT_LEXER', 'BASIC_LEXER');
ctx_ddl.set_attribute ('GLCLNT_LEXER', 'index_stems', 'ENGLISH');
ctx_ddl.set_attribute ('GLCLNT_LEXER', 'index_text', 'YES');

ctx_ddl.set_attribute ('GLCLNT_LEXER', 'punctuations', '!?~@$%^&*()-_=+|;:"/.,');
-- ctx_ddl.set_attribute ('GLCLNT_LEXER', 'printjoins', '.~@$%^&*()-_=+|;:"/!?,');
ctx_ddl.create_preference('GLCLNT_FLTR','NULL_FILTER');

ctx_ddl.create_stoplist('GLCLNT_STPLST');
ctx_ddl.add_stopword('GLCLNT_STPLST','CO');
ctx_ddl.add_stopword('GLCLNT_STPLST','LTD');
ctx_ddl.add_stopword('GLCLNT_STPLST','STG');
end;
/

create index glbl_clnt$clnt_name$txti on global_clients (client_name)
indextype is ctxsys.context
parameters ('datastore GLCLNT_DTSTR
filter GLCLNT_FLTR
lexer GLCLNT_LEXER
wordlist GLCLNT_WRDLST
stoplist GLCLNT_STPLST');


Детали

Для индексирования по нескольким столбца используем "MULTI_COLUMN_DATASTORE"

Чтобы убрать из индексируемого текста символы выставляем их в "punctuations" при этом oracle просто удалит их,
например строчка "MORGAN-STANLEY SECS. LTD GB" будет проиндексирована как: "MORGAN", "STANLEY", "SECS",
"LTD"(не индексируется т.к. находиться в стоп листе) , "GB"
можно также заставить его проиндексировать слова с символами как единое слово,
если добавить символ "-" в параметр "printjoins" будет так: "MORGAN-STANLEY", "SECS",
"LTD"(не индексируется т.к. находиться в стоп листе) , "GB"

В стоп листе прописываем те слова которые не нужно индексировать (если, к примеру, они очень часто встречаются в строках)

Запрос к индексированным данным

Проверяем по каким словам построен индекс:
select * from dr$glbl_clnt$clnt_name$txti$i where token_text = 'LTD';

Запрос к индексированным столбцам:
SELECT score(1) as scr,
glcnt.id,
glcnt.client_name,
glcnt.ext_client_id,
glcnt.domicile_country,
glcnt.industry_desc,
glcnt.website_url
FROM global_clients glcnt
WHERE
contains(glcnt.client_name,'
<QUERY>
  <textquery>MORGAN STANLEY SECS LTD GB
    <progression>
      <seq><REWRITE>transform((TOKENS, "{", "}", ";"))</REWRITE></seq>
      <seq><REWRITE>transform((TOKENS, "{", "}", ","))</REWRITE></seq> 
      <seq><REWRITE>transform((TOKENS, "{", "}", ";"))</REWRITE></seq>  
      <seq><REWRITE>transform((TOKENS, "${", "}", "AND"))</REWRITE></seq>
      <seq><REWRITE>transform((TOKENS, "!{", "}", "AND"))</REWRITE></seq>
      <seq><REWRITE>transform((TOKENS, "%", "%", "AND"))</REWRITE></seq>
      <seq><REWRITE>transform((TOKENS, "{" , "}", "OR" ))</REWRITE></seq>
      <seq><REWRITE>transform((TOKENS, "${", "}", "OR"))</REWRITE></seq>
      <seq><REWRITE>transform((TOKENS, "!{", "}", "OR"))</REWRITE></seq>
      <seq><REWRITE>transform((TOKENS, "%", "%", "OR" ))</REWRITE></seq>
    </progression>
  </textquery>
  <score datatype="integer" algorithm="DEFAULT"/>
</QUERY>',1 )> 0
order by scr desc;

Меняя порядок строк мы изменяем значения score для каждой записи, т.е. если найденные однокоренные слова должны иметь меньший вес тем слова похожие по произношению меням местами строчки
<seq><REWRITE>transform((TOKENS, "${", "}", "AND" ))</REWRITE></seq>
и
<seq><REWRITE>transform((TOKENS, "!{", "}", "AND"))</REWRITE></seq>
Эта новая возможность была добавлена в Orace 10 называется query rewrite template, суть в том что описываются только правила которые применяются при поиске.  Фактически вместо нескольких запросов можно написать один используя эту возможность просто нужно прописать по каким алгоритмам Oracle должен искать.
Например при поиске "MORGAN STANLEY SECS LTD GB" если мы указываем в поиске "<seq><REWRITE>transform((TOKENS, "${", "}", "AND" ))</REWRITE></seq>" (искать однокоренные слова) Oracle перепишет запрос в : "${MORGAN} and ${STANLEY} and ${SECS} and ${LTD} and ${GB}"
Ещё есть возможность указывать другой алгоритм поиска в algorithm="DEFAULT", но с этим я не разбирлся.

Самым полезным в типах поиска (если так можно сказать) для меня оказался Accum  или вот

эта строчка "<seq><REWRITE>transform((TOKENS, "{", "}", ","))</REWRITE></seq>" т.к. при этом учитывается вес слов (как часто они повторяются в тексте)  и сколько совпадений каждого слова было в тексте.
Плюс можно указать для каждого слова коэффициент для веса например при поиске "MORGAN STANLEY SECS LTD GB" первые слова "MORGAN STANLEY" для пользователя более важные чем остальные, даже если они более редкие(имеют больший вес) или несколько раз встречаются в тексте, для этого вместо query rewrite template нужно сформировать строку поиска самому: "<seq>MORGAN*5, STANLEY*4, SECS, LTD, GB</seq>" . При этом на первое место выдут строки содержащие "MORGAN" и/или "STANLEY" а не "SECS", "LTD" или "GB"

Теперь о косяках:

1) Запрос падает с ошибкой если в поисковой фразе встречаются символы, причем не всегда,
т.е. если в поис ввести "MORGAN STANLEY S.ECS. LTD GB" то запрос падает -
решил написание функц по пред обработке строки для поиска, где удалял весь потенциальный мусор

2) Чем больше слов в запросе тем строже поиск т.е.
если в поиске ввести: "0ADERF MORGAN STANLEY SECS LTD GB" то строчка с "MORGAN STANLEY SECS LTD GB" не находилась или имела малый score
непонятно почему, козалось бы откинь первое слово и тут же найдется практически полностью идентичная строка - нифига.
Возможно это настраивается но как это сделать я не нашел.  UPD - Нашел, как вариант использование в поиске Accum и указание коэффициентов для слов.

Возникает большое кол-во ошибок при запуске тестовой Java программы, которая генерит большое кол-во потоков по обновлению таблицы т.е. одновременно несколько потоков пытаются изменить разные строки из одного блока.
При этом 70% потоков отваливаются из-за ошибки "cann't serialize..."  хотя те строки которые они пытаются обновить не менялись другими потоками т.е. один поток обновляет одну запись.

Объяснение нашел здесь: my-oracle.it-blogs.com.ua/post-239.aspx
Где хранится информация о блокировках? ITL, INITRANS, MAXTRANS

Что такое ITL и для чего он нужен?

В ORACLE, как вы наверняка знаете, нет менеджера блокировок. Об этом упоминалось и на моем блоге в посте Типы блокировок . Информация о блокировках хранится непосредственно в блоках данных. А если быть точным, то в заголовке
блока, где заблокированная строка находится (см. здесь).
Если другая транзакция желает заблокировать эту же запись, она должна добраться к блоку, содержащему запись, и по заголовку блока определить заблокирована ли необходимая строка.
Где же именно в заголовке блока находится информация о блокировках?
Для этого существует специальная структура, называемая таблицей списка входов транзакций ( ITL - Interested Transaction List ) или списком заинтересованных транзакций.
В этой структуре хранится адрес транзакции и rowid заблокированной строки.
ITL состоит из нескольких слотов. Когда строка в блоке блокируется впервые, то транзакция устанавливает блокировку в одном из слотов с rowid блокируемой строки. Иначе говоря, транзакция ставит всех в известность, в своей
«заинтересованности» этой строкой.
Когда эта же или другая транзакция проявляет «заинтересованность» другой строкой, то информация помещается в
следующий слот.
И так далее. Когда транзакция завершается (commit, rollback), блокировки освобождаются,
ITL –слоты также становятся свободными для других транзакций. Можно сказать, что таким образом на блочном уровне формируется очередь.
Какое количество слотов допустимо?
Во время создания таблицы (CREATE TABLE) устанавливаются следующих два параметра: INITRANS и MAXTRANS.

INITRANS n
Устанавливает начальное число транзакционных записей в каждом блоке. n должно быть целым числом в диапазоне от 1 до 255 (если опущено, то подразумевается n = 1, для 9 -ки - 2 и не меньше!).
При первом использовании блока ORACLE резервирует в его свободной части 23 байта для каждой транзакции.
Когда число одновременных транзакций превысит INITTRANS, новые транзакционные записи будут размещаться динамически до тех пор, пока их число не достигнет MAXTRANS или пока не заполнится блок.

MAXTRANS n
Устанавливает максимальное число транзакций, имеющих возможность конкурентно модифицировать данные в блоке. n—целое, от 1 до 255. Подразумеваемое значение n = 255.
Можно сделать вывод, что при первоначальной записи данных в блок сразу же создается INITRANS

В документации нашел следующее: download.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_sq.htm

How Serializable Transactions Interact

When a serializable transaction fails with an ORA-08177 error ("cannot serialize access"), the application can take any of several actions:
Commit the work executed to that point
Execute additional, different, statements, perhaps after rolling back to a prior savepoint in the transaction
Roll back the entire transaction and try it again

Oracle Database stores control information in each data block to manage access by concurrent transactions.
To use the SERIALIZABLE isolation level, you must use the INITRANS clause of the CREATE TABLE or ALTER TABLE command to set aside storage for this control information.
To use serializable mode, INITRANS must be set to at least 3.

В итоге увеличили значение  INITRANS в таблице и в индексе !!!, увеличили ptcfree и пересоздали таблицы. Кол-во ошибок сократилось в разы.

 По работе нужно было при работе скриптов получать в Oracle бд разные параметры и выставлять их значения как environment variables в текущем сеансе.
Затем последующие скрипты которые будут работать в этом сеансе должны получать эти значения из  environment variables и использовать их в oracle скриптах.
В итоге сотворил следующее:  
скрипт выставляет environment variable:

#!/bin/bash

PROCESS_TYPE_="$1"
PROCESS_SCOPE_="$2"

if [ "$PROCESS_TYPE_" == "" ]; then
echo "Attention! Not set PROCESS TYPE"
echo "example ./start_etl.sh PROCESS_TYPE PROCESS_SCOPE"
exit 1
elif [ "$PROCESS_SCOPE_" == "" ]; then
echo "Attention! Not set PROCESS TYPE"
echo "example ./start_etl.sh PROCESS_TYPE PROCESS_SCOPE"
exit 1
fi

. `dirname $0`/config
# генерим фаил с переменными tmp_define_etl_pid.sh
. ${MODULE_BIN}/sql-starter ${MODULE_SQL}/start_etl.sql $@ "${MODULE_BIN}"


RESULT=$?
if [ $RESULT -ne 0 ]; then
echo "ERROR Create process id"
#set default values
export ETL_PID=0
export ETL_TYPE=UNDEFINED
export ETL_SCOPE=HISTORICAL

exit $RESULT
else
#set new ETL_PID, ETL_TYPE, ETL_SCOPE environment variables
. ${MODULE_BIN}/tmp_define_etl_pid.sh
fi

В sql скрипте генерю фаил для выставления environment variables:
-- register current process id
prompt BEGIN START_ETL SCRIPT

whenever sqlerror exit failure rollback
whenever oserror exit failure rollback

SET VERIFY OFF
SET HEADING OFF

variable v_str_process_type varchar2(400)
variable v_str_process_scope varchar2(400)
variable v_str_process_id varchar2(400)
variable v_PID number


-- get new process id
-- prepare define unix variables
declare
v_process_type varchar2(200) := '&1';
v_process_scope varchar2(200) := '&2';
begin
--:v_PID := p_etl.add_process_instance( v_process_type, v_process_scope);
--commit;
:v_PID := 100;
:v_str_process_id := 'export ETL_PID='||to_char(:v_PID);
:v_str_process_type := 'export ETL_TYPE='||v_process_type;
:v_str_process_scope := 'export ETL_SCOPE='||v_process_scope;
end;
/

-- create script for define unix variables
spool &3/tmp_define_etl_pid.sh

prompt #temporary script defined process environment variables

print v_str_process_id
print v_str_process_type
print v_str_process_scope

spool off

set heading on
set verify on

prompt END START_ETL SCRIPT

exit;

SQL скрипт для получение значений environment variables в текущем сеансе: 


PROMPT BEGIN LOGIN SCRIPT

whenever sqlerror continue commit
whenever oserror continue commit


set verify off

prompt set default value for environment variables
define ETL_PID=0
define ETL_TYPE=UNDEFINED
define ETL_SCOPE=HISTORICAL

define CURDATE=''
COLUMN curdt NEW_VALUE CURDATE
select to_char(sysdate, 'yyyymmddhh24miss') as curdt from dual;

-- генерю файл куда записываю текущие для данного сенаса значения environment variables и тут же  выполняю его
prompt get unix environment variable values
host echo /*get curent process environment variables*/ \\ndef ETL_PID=$ETL_PID \\ndef ETL_TYPE=$ETL_TYPE \\ndef ETL_SCOPE=$ETL_SCOPE > tmp_define_etl_pid_&CURDATE..sql
@tmp_define_etl_pid_&CURDATE..sql


prompt set sessions parameters
declare
v_pid number := &ETL_PID;
begin
--p_session.login(i_process_id => v_pid);
null;
end;
/

prompt delete temp file "tmp_define_etl_pid_&CURDATE..sql" with defined unix environment variable values
host rm tmp_define_etl_pid_&CURDATE..sql

set verify on
PROMPT END LOGIN SCRIPT

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

Не так давно наткнулся на рецепт 
 belonika.livejournal.com/199040.html
и наконец то дошли руки до этого чуда.
Как и обещала автор это что то с чем то,
пирог был уничтожен в первые 15 мин получился очень вкусный.
И что мне особенно понравилось(т.к. я с выпечкой на вы) - очень простой рецепт.
Яблоки правда не получилось запихать так же плотно как у автора, может в след. раз.
Результат: 

После запуска трёх виртуалок с поднятыми БД (это ещё пустые, не рабочие бд) комп послал меня куда подальше начал дико тормозить.
Пока ограничился двумя. Буду добавлять ещё 4 гига, может поможет (хотя скорее всего на проце просядет).
Ну и как вариант включать/выключать нужные инстансы.

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

Занялся чтением статей про новинки в железе, оптимальные конфигурации итд, итп. Через две недели бессоных ночей и простоев на работе решил, что я хочу и начал прикидывать комплектующие.
Было выделено из бюджета около 30 т.р. +- пару тыщ.
Изначально хотел собрать комп c ssd накопителем, но начав прикидывать понял что жаба душит отдавать за ssd 14 т.р. идею с быстрым ssd придется отложить, решил доставить его потом.

Системник собирался в основном под такие нужды:

• разработка, тестирование и возможность работать с работающим инстансом oracle(dwh) - оперативка, проц, и место на hdd
• интернет - оперативка, проц
• любительская обработка фотографий - оперативка, проц
• торенты - место на hdd
• игры - видеокарта, оперативка, место на hdd, проц

После всяческих подборов, переборов остановился на следующей конфигурации:

Компонент	Наименование	кол-во	Цена	%
Материнская плата	GigaByte GA-P55A-UD4	1	5680	16.0%
Процессор	Intel "Core i5-760" (2.80ГГц, 4x256КБ+8МБ, EM64T) Socket1156 (изменить)	1	6450	18.2%
Кулер	Thermaltake "Silent 1156" CL-P0560	1	800	2.3%
Память	2x2ГБ DDR3 SDRAM Kingston "ValueRAM" KVR1333D3N9K2/4G (PC10600, 1333МГц, CL9)	1	2610	7.4%
HDD	1000ГБ Western Digital "Caviar Black WD1002FAEX" 7200об./мин., 64МБ (SATA III)	1	2650	7.5%
Видеокарта	1280Mb Zotac GTX470 AMP! EDITION, PCI-E, 2xDVI, HDM	1	10370	29.3%
Корпус	ATX Gigabyte iSolo 210, Без БП, Серый/ Черный [GZ-AA1CB-SNG]	1	2690	7.6%
Блок питания	Thermaltake Toughpower QFan [W0163RE] ATX 650W	1	3530	10.0%
Привод CD/DVD	DVD±RW+CD/ RW LG GH22NS40, Black, SATA, OEM	1	650	1.8%

Итого: 35 430 р.

Соотношение цен получилось таким:

В целях аудита было решено хранить изменения данных в XML ( что было до, как стало после )
Была задача генерировать XML из различных таблиц и сохранять его в табл. аудита.
Дальше этот XML парсят Java кодом.

Для генерации был использован "dbms_xmlgen".
Чтобы не менять общий JAVA код стандартный XML, который выдает "dbms_xmlgen" дополнительно преобразуется с помощью XSLT (захадкоженного нафик в функц )

------------------------------------------------------------------------------------------------------------------------------------------
function get_xml_from_sql (i_object_name in varchar2, i_sql in varchar2)
return varchar2
is
v_ctx_handle dbms_xmlgen.ctxHandle;
v_XML CLOB;

/* xslt temlate for transformation
from XML
<TABLE_NAME>
<ROW>
<COLUMN_NAME>value<COLUMN_NAME>
...
</ROW>
</TABLE_NAME>
to XML
<TABLE name="TABLE_NAME">
<ROW>
<COLUMN name="COLUMN_NAME">value<COLUMN>
...
</ROW>
</TABLE>
*/
v_xslt_template constant varchar2(2000) :=
'<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:template match="/*">
<TABLE>
<xsl:attribute name="name"><xsl:value-of select="name()"/></xsl:attribute>
<xsl:for-each select="./ROW">
<ROW>
<xsl:for-each select="./*">
<COLUMN>
<xsl:attribute name="name"><xsl:value-of select="name()"/></xsl:attribute>
<xsl:value-of select="."/>
</COLUMN>
</xsl:for-each>
</ROW>
</xsl:for-each>
</TABLE>
</xsl:template>
</xsl:stylesheet>';
begin
-- create context
v_ctx_handle := dbms_xmlgen.newContext ( queryString => i_sql );

-- settings for Null value
dbms_xmlgen.setNullHandling ( ctx => v_ctx_handle, flag => dbms_xmlgen.EMPTY_TAG );

-- settings for rename ROWSET tag as Object Name
dbms_xmlgen.setRowSetTag ( ctx => v_ctx_handle, rowSetTagName => i_object_name );
-- sett XSLT transormation
dbms_xmlgen.setXSLT ( ctx => v_ctx_handle, stylesheet => v_xslt_template );

-- create xml
v_XML := dbms_xmlgen.getXML ( ctx => v_ctx_handle );

-- close context
dbms_xmlgen.closeContext ( ctx => v_ctx_handle );

-- return
return v_XML;
end get_xml_from_sql;
------------------------------------------------------------------------------------------------------------------------------------------

Производительность этого метода не сравнивал т.к. меня скорость работы вполне устраивала.

--включить в трассировку инф. времени
alter session set timed_statistics=true;
--включить трассировку:
alter session set events '10046 trace name context forever, level 8';

--директория с трейсами
select value
from v$parameter
where name = 'user_dump_dest';

/*
-- номер файла трейса текущей сесии
select p.spid
from v$session s, v$process p
where s.paddr=p.addr
  and s.SID = ( select m.SID from v$mystat m where rownum < 2 );
*/
 
-- полное имя файла
select c.value || '/' ||d.instance_name || '_ora_' || a.spid ||'.trc' as trace_file
  from v$process a,
       v$session b,
       v$parameter c,
       v$instance d
  where a.addr = b.paddr
    and b.audsid = userenv('sessionid')
    and c.name = 'user_dump_dest'; 
 
--мах размер файла трейса
select value from v$parameter where name = 'max_dump_file_size';

--выключить трассировку:
alter session set events '10046 trace name context off';

-- запись сообщений в трайс
begin   
  dbms_system.ksdwrt (1, ‘Output to trace file’);
  dbms_system.ksdwrt (2, ‘Output to alert log’);
  dbms_system.ksdwrt (3, ’Output to both’);
end;
/

-- запуск трассировки с помощью пакета DBMS_MONITOR
begin
   DBMS_MONITOR.SESSION_TRACE_ENABLE( SESSION_ID NUMBER,    -- SID
                                      SERIAL_NUM NUMBER,    -- Serial Number    
                                      WAITS BOOLEAN,    -- Include Waits
                                      BINDS BOOLEAN    -- Include Binds
                                     );

end;
/

-- Остановки трассировки с помощью пакета DBMS_MONITOR
begin
   DBMS_MONITOR.SESSION_TRACE_DISABLE( SESSION_ID NUMBER,    -- SID
                                       SERIAL_NUM NUMBER    -- Serial Number
                                       );
end;
/

-- триггер на включение трассировки
CREATE OR REPLACE TRIGGER trace_login_trigger
  AFTER logon ON DATABASE 
BEGIN
    IF (USER = 'HR') THEN -- для пользователя HR
      EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
    END IF;
END;
/