with tab1 as (
select to_date('01.01.2014','dd.mm.yyyy') as DAY_ID, '000032' as ARTICLE, 1 as QTY from dual union all
select to_date('02.01.2014','dd.mm.yyyy') as DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
select to_date('03.01.2014','dd.mm.yyyy') as DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
select to_date('04.01.2014','dd.mm.yyyy') as DAY_ID, '000032' as ARTICLE, 1 as QTY from dual union all
select to_date('05.01.2014','dd.mm.yyyy') as DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
select to_date('06.01.2014','dd.mm.yyyy') as DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
select to_date('07.01.2014','dd.mm.yyyy') as DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
select to_date('08.01.2014','dd.mm.yyyy') as DAY_ID, '000032' as ARTICLE, 1 as QTY from dual union all
select to_date('09.01.2014','dd.mm.yyyy') as DAY_ID, '000032' as ARTICLE, 1 as QTY from dual union all
select to_date('10.01.2014','dd.mm.yyyy') as DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
select to_date('11.01.2014','dd.mm.yyyy') as DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
select to_date('12.01.2014','dd.mm.yyyy') as DAY_ID, '000032' as ARTICLE, 1 as QTY from dual union all
select to_date('13.01.2014','dd.mm.yyyy') as DAY_ID, '000032' as ARTICLE, 0 as QTY from dual
)
select
t.DAY_ID
,t.ARTICLE
,t.QTY
,decode(qty,0,count(*) over(partition by s order by day_id)) zero_n
from
(
select
tab1.*
,row_number()over(order by day_id) - decode(qty,0,count(*)over(partition by qty order by day_id)) s
from tab1
) t
order by day_idselect grantee
from dba_role_privs
group by grantee
having count(*)=2
and cast(collect(GRANTED_ROLE) as sys.ku$_vcnt) = sys.ku$_vcnt('CONNECT','RESOURCE')select grantee
from dba_role_privs
group by grantee
having count(*)=2
and min(GRANTED_ROLE) ='CONNECT'
and max(GRANTED_ROLE) ='RESOURCE'1. Как ее правильно разбить на partition по date, если чаще всего отчеты делаются за текущий месяц?
И стоит ли делать допустим такое разбиение partition по году затем в subpartition по месяцам.не стоит. Если уж режете по дате, то делайте сразу по месяцам, иначе получите лишний оверхед.
2. Можно ли создание новой партиции сделать автоматическим согласно критерия деления?да, смотрите Interval partitioning - docs.oracle.com/cd/E11882_01/server.112/e25523/partition.htm#CACHFHHF
3. Если структуру перестроить разбить данную таблицу на две.Снова Reference partitioning
create or replace trigger catch_unique
after servererror on database
disable
declare
l_sql_text ora_name_list_t;
l_msg varchar2(2000) := null;
l_stmt varchar2(2000) := null;
begin
if IS_SERVERERROR(1) then
/* если нужен текст ошибки, запроса и тд:
for depth in 1 .. ora_server_error_depth loop
l_msg := l_msg || ora_server_error_msg(depth);
end loop;
for i in 1 .. ora_sql_txt(sql_text) loop
l_stmt := l_stmt || sql_text(i);
end loop;
*/
raise_application_error(-20001,'Ooops!');
end if;
end;
/
sho err;
--если ошибок нет, то включаем:
alter trigger catch_unique enable;
--а теперь проверка:
SQL> insert into xt_unique1 values(1);
insert into xt_unique1 values(1)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Ooops!
ORA-06512: at line 16
ORA-00001: unique constraint (XTENDER.SYS_C0043662) violated
SQL> @tests/errlog_ddl.sql
Table created.
Sequence created.
Trigger created.
No errors.
SQL> alter trigger tr_errorlog enable;
Trigger altered.
SQL> insert into nonexistent values(1);
insert into nonexistent values(1)
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> col err_sql format a50
SQL> select id,err_level, err_sql from errorlog;
ID ERR_LEVEL ERR_SQL
---------- ---------- -----------------------------------
1 1 insert into nonexistent values(1)