Enable and Disable Scheduler from Backend

Enable and Disable Scheduler from Backend

BEGIN

dbms_scheduler.enable('GEO_TRANS_PF_JOB');

END

BEGIN

dbms_scheduler.disable('GEO_TAG_PF_JOB');

END;

Checking Error From Backend

Its sometime very painful to Look for Error from Control Center.

It is better to check error from backend.

Below are the script i used to check the same :-

select * from ALL_RT_AUDIT_PROC_RUN_ERRORS where updated_on >= '13-JUL-11 03:08:59 PM'

order by updated_on desc

select * from ALL_RT_AUDIT_MAP_RUN_ERRORS where updated_on >= '13-JUL-11 05:07:08 PM'

order by updated_on desc

select * from ALL_RT_AUDIT_MAP_RUN_ERRORS map_err , ALL_RT_AUDIT_MAP_RUNS Map_run

where map_err.map_run_id = Map_run.map_run_id

and map_err.updated_on >= '13-JUL-11 05:07:08 PM'

order by map_err.updated_on desc

"Single-task message" OWB Wait

I Search the net for the wait event "single-task message" got below information on

http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/WE200/Default.aspx

"When running single task, this event indicates that the session waits for the client side of the executable."

I found that, if the mapping is in "invalid state" is called in the process flow then the process flow will hang forever and we get "single-task message" wait in the v$session table.

"invalid state" means = Mapping is working fine , then we change it but not able to deploy.



Hope this will someone .


Cheers
Nawneet

Chunk Loading in OWB 11G R2

Let's explore the Loading data using new feature in owb 11gr2.



Step1 :-
Create Source table for chunk loading.

CREATE TABLE countries_check
( "COUNTRY_ID" CHAR(2 BYTE) CONSTRAINT "COUNTRY_ID_NN" NOT NULL ENABLE,
"COUNTRY_NAME" VARCHAR2(40 BYTE),
"REGION_ID" NUMBER
);



Step2 :-
Load the data

begin
for i in 1..1500 loop
insert/*+ append */ into countries_check values ('b','USA',seq_chuck.nextval);
end loop;
end;


Step 3:-

created the Mapping that load the data form source table countires_check to Countries_tgt_view.





Step 4:-

Configure the chunk parameters :-

Right click Map-> Configure

chunking Stategy :- Serial
Chunk Size :- 1 (number of rows to be loaded in each iteration
Chunk table :- Countries_check (Source table )





step 5:-
Max Check Iterator count :- 5000000
Chunk stop if no data. -tick yes

Mapping will loop maximun to 5000000 or the when no data is found
whatever condition meet first.









Step 5:-

Deploy and run the map













Cheers
Nawneet

ORA-30926: unable to get a stable set of rows in the source tables

Lets see when we got Error ORA-30926

1. Execute the below scripts:-

CREATE TABLE employee (employee_id NUMBER(5),first_name VARCHAR2(20),last_name VARCHAR2(20),dept_no NUMBER(2),salary NUMBER(10));

INSERT INTO employee VALUES (1, 'Dan', 'Morgan', 10, 100000);

INSERT INTO employee VALUES (2, 'Helen', 'Lofstrom', 20, 100000);

INSERT INTO employee VALUES (3, 'Akiko', 'Toyota', 20, 50000);

INSERT INTO employee VALUES (4, 'Jackie', 'Stough', 20, 40000);

INSERT INTO employee VALUES (5, 'Richard', 'Foote', 20, 70000);

INSERT INTO employee VALUES (6, 'Joe', 'Johnson', 20, 30000);

INSERT INTO employee VALUES (7, 'Clark', 'Urling', 20, 90000);

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses (employee_id) VALUES (1);

INSERT INTO bonuses (employee_id) VALUES (2);

INSERT INTO bonuses (employee_id) VALUES (4);

INSERT INTO bonuses (employee_id) VALUES (6);

INSERT INTO bonuses (employee_id) VALUES (7);

COMMIT;

SELECT * FROM employee;

Its has 7 rows for all employees.

SELECT * FROM bonuses;

It has only 1,2,4,6,7 employees.

Run the Below Merge statement :-

MERGE INTO bonuses bUSING (MERGE INTO bonuses bUSING ( SELECT employee_id, salary, dept_no FROM employee) eON (b.employee_id = e.employee_id)WHEN MATCHED THEN UPDATE SET b.bonus = e.salary * 0.1WHEN NOT MATCHED THEN INSERT (b.employee_id, b.bonus) VALUES (e.employee_id, e.salary * 0.05);

7 rows merged.

Now check the target table

SELECT * FROM bonuses;

You will see all the rows from 1 to 7 .

And for matching row the bounus is e.salary * 0.1 (1,2,4,6,7) ,nd for new rows bounus e.salary * 0.05 (3,5)

Now run again the same merge . and it will show 7 Rows merged again .
And the values of bonus column will increase in bonuses table.
And you can run that merge any number of time.

Now test it again ... truncate all both the table and load them again but donot run the merge.

Now add the new row to employees as below
INSERT INTO employee VALUES (1, 'Dan1', 'Morgan', 10, 100000);

Now run merge you will got the error.
ORA-30926: unable to get a stable set of rows in the source tables

Why this time ??Because this time you got two rows in select statement for same employee is i.e 1 , so the merger will not be able to find which row to update so got the error.

" SELECT employee_id, salary, dept_no FROM employee) eON (b.employee_id = e.employee_id"

Now delete the employee_id 1 from bonuses table and run merge again.

delete from bonuses where employee_id=1;

Now run it again , this time it will work and this time 8 rows merged.

This time it worked because this time you will not got any row in select statement for same employee is i.e 1 , so the merger will insert both the rows in the bonus table.

(Note :- Now if you have primary key on employee_id (matching column )in bonus then it will give primary key violation because it will try to insert 2 rows of same employee_id on bonus table)

But if again you run the merge then you will got the error
ORA-30926: unable to get a stable set of rows in the source tables
With the same reason that now employee_id 1 is there in bonuses table and the select statement will give 4 rows and not able to update .
So if to avoid the error “ORA-30926” you should not have more then one rows in target table for same matching columns.

What my Mapping is currently doing ?

Step 1:- Execute the below query to find out which Mapping is executing:-

select

exe.execution_audit_id,

tar.NUMBER_RECORDS_INSERTED,
step_tar.TARGET_NAME,
tar.STEP_TYPE,
tar.NUMBER_RECORDS_MERGED ,
tar.NUMBER_RECORDS_UPDATED ,exe.execution_audit_id, tar.ELAPSE_TIME,exe.EXECUTION_NAME,exe.EXECUTION_AUDIT_STATUS,map_run.MAP_NAME
from ALL_RT_AUDIT_MAP_RUNS Map_run,ALL_RT_AUDIT_EXECUTIONS Exe, ALL_RT_AUDIT_STEP_RUNS tar, ALL_RT_AUDIT_STEP_RUN_TARGETS step_tar
where exe.EXECUTION_AUDIT_ID=map_run.EXECUTION_AUDIT_ID(+) AND
map_run.map_run_id=tar.map_run_id(+) AND
step_tar.STEP_ID=tar.STEP_ID and
exe.execution_audit_id > '114305' order by exe.execution_audit_id desc

Note the Execution audit id of the Map which is currently running
In My case it is 114358.

Note :- Here the '114305' is the id which we got from OWB control center.

Step 2:-Connect to Database using user having privilege on V$ tables
Execute the below query :-
select * from v$active_session_history where module like '%114358%'

Step 3:-
Check for the column EVENT .
This would tell you what the Mapping is currently doing
SQL*Net more data from dblink :- extracting data from Database link

Load the data form Oracle DB to File

Test is tested on OWB 10.2.0.4

1.Create the Location of file where it need to store. (on server where OWB Server is installed)
1.1 Go to Connection Explorer->File ->new
1.2 Create the location of file say FILE_LOC and path E:\owb_files


2.Create the File Module:-

2.1 Right click on the Files tab under your Project -> New
2.2 Select the location created in step 1.2
2.3 Finish

3.Create the mapping say( MAP_load_to_file)
3.1 Drag the target table to canvas.
3.2 Drag the flat file operator in canvas.
3.3 Then create unbound operator with no attribute.(Test1)
3.4 Select the INOUTGRP1 from Table(source) and drop it into INOUTGRP1 of file Test1
3.5 Right click Test1 Operator and "Create and bind"-> Create in -> select the file location created into step 2. then Ok
3.6 Right click Test1 Operator ->Synchronize ->Outbound ->OK
3.7 Close the map

4.Configure the Map:-
4.1 Right click the map ( Created Step 3. in MAP_load_to_file)
4.2 Click on Flat file Operator -> file name
4.3 On right side "Access Specification "
Fill the details :- Target Data File Location = created in Step1 (FILE_LOC)
Target Data file Name :-Give any name with extension like ( target_file.txt)
4.4 Ok

5.Open the map and validate .

6. Deploy and execute.