Wednesday, 8 June 2016

Multi Row Based Constraints in Oracle

Requirement from Client 

 As an advocate of enforcing as much integrity as possible declaratively, I am 
disappointed that little has changed in that arena in 10G.  CHECK constraints are still 
limited to simple single-table, single-row checks, and triggers have to be used to 
enforce any more complex rules.

Do you think Oracle will ever add multi-table check constraints or ASSERTIONS as defined 
by ANSI SQL?  Or has declarative integrity gone about as far as it is going to in Oracle? 
and we said...

I'm asking around about the future of "SQL" as far as enhancments go like that (will 
update when I get some feedback), but -- you are not limited to triggers (in fact, I 
would avoid triggers as it is virtually IMPOSSIBLE to implement cross row/cross object 
constraints with them!!!!  at least correctly)

I look to database objects to enforce them - lets look at a couple of cases (i know, 
these are not assertions, these are not domains -- they are working solutions using 
existing features in a way "perhaps not intended")

o case: we need to have a rule that enforces unique names for "current" projects.   That 
is, we have a projects table, there are active projects and historical (completed) 
projects.  the project name for all active projects must be unique.  historical projects 
can have duplicates.  

ops$tkyte@ORA9IR2> create table projects
  2  ( id         int primary key,
  3    status  varchar2(1) not null check (status in ('A','C')),
  4    name       varchar2(10)
  5  )
  6  /
Table created.
ops$tkyte@ORA9IR2> create unique index proj_name_idx on projects
  2  ( case when status = 'A' then name end )
  3  /
Index created.
ops$tkyte@ORA9IR2> insert into projects values ( 1, 'C', 'hello' );
1 row created.
ops$tkyte@ORA9IR2> insert into projects values ( 2, 'C', 'hello' );
1 row created.
ops$tkyte@ORA9IR2> insert into projects values ( 3, 'A', 'hello' );
1 row created.
ops$tkyte@ORA9IR2> insert into projects values ( 4, 'A', 'hello' );
insert into projects values ( 4, 'A', 'hello' )
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.PROJ_NAME_IDX) violated

function based indexes can be used to implemented some interesting and complex rules 
for conditional uniqueness

Some will say this is better solved via a data model change (eg: a projects table with 
subtypes "active" and "completed" -- but sometimes this is just easier to have a single 
physical table)

o case averages/mins/counts whatever "by something".  refresh fast on commit MV's can be 
useful.  consider, we want between 1 and 6 employees/department:

ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
Table created.
ops$tkyte@ORA9IR2> alter table emp add constraint emp_pk primary key(empno);
Table altered.
ops$tkyte@ORA9IR2> create materialized view log on emp with rowid, (deptno) including new 
Materialized view log created.
ops$tkyte@ORA9IR2> create materialized view emp_mv
  2  refresh fast on commit
  3  as
  4  select deptno, count(*) cnt from emp group by deptno
  5  /
Materialized view created.
ops$tkyte@ORA9IR2> alter table emp_mv
  2  add constraint check_cnt
  3  check ( cnt between 1 and 6 )
  4  deferrable
  5  /
Table altered.
ops$tkyte@ORA9IR2> insert into emp (empno,deptno) values ( 1000, 20 );
1 row created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2> insert into emp (empno,deptno) values ( 1001, 20 );
1 row created.
ops$tkyte@ORA9IR2> commit;
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (OPS$TKYTE.CHECK_CNT) violated

the logic is to "materialize" the aggregation and add a check constraint to the 


Friday, 3 June 2016

Issue with Informatica Loading into Partitioned Oracle Target Table

"ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired" when Metadata Manager session fails

Problem Description

I m facing a issue in regard to loading into Partitioned Oracle Target table.
We have 2 sessions having same table in Oracle as Target
     a. INSERT data into Partition1
     b. UPDATE data in Partition2
We are trying to achieve parallelism in the workflow, and there are more Partitions and sessions to be created
 for different data but into same table, but different partitions..
Currently when we run both these sessions parallely, the Update session runs successfully, but the INSERT 
session gets a NOWAIT error.
NOTE: both are loading data for different partitions.

The session is running in Bulk mode to target. Bulk mode requires full table lock and at times this leads to an issue.
This is an Oracle returned error. It implies, Oracle cannot handle the bulk mode on that target table at that point of time.
To resolve this issue, turn off the Bulk mode. Edit the concerned session in Workflow Manager tool and
 make the session to run in normal mode: edit the session > Mapping tab > Target object > Target load type and change the value to Normal. 
Save the session.