Thursday, January 31, 2008

Altering a JOB

In oracle 9i in order to alter a job you must enter all the parameters of the job even they remain the same.

e.g

BEGIN
SYS.DBMS_JOB.CHANGE
(
job => [job_number]
,what => 'begin myproc; end;'
,next_date => to_date('31/01/2008 10:40:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'sysdate+1/24'
);
commit;
END;
/

Here i changed only the "what" parameter but i included in my script the other 2.

Tuesday, January 22, 2008

Update with join and ORA-01779

A very fast update when we want to update 2 tables T1 ,T2 having a join on a column and unique values on the join.(One row form T1,matches only with one row from T2)

update (
select a.ordering a1,b.ind a2
from T1 a,T2 b
where a.cmp_id=139
and a.cmp_key=b.k)
set a1=a2;

For the given cmp_id any cmp_key is unique on table T1.
Also for any k on T2 there must be only one value for ind.
So we need a primary key on T2.k in order to work the query and avoid the ORA-01779 error.


Tuesday, January 15, 2008

Merging partitions

This week i have to merge a huge number of tables partition into one yearly partition per table.

Steps :

1) Create new tablespace for yearly partition.

2) Merge all tables partitions

3) Rebuild unusable local indexes

4) Gather new partition statistics

Tuesday, January 8, 2008

unix timestamp to date

select to_date('01/01/1970', 'dd/mm/yyyy HH24:MI:SS')+ (1197889557.848 / ( 60*60*24)) from dual;

Returns gmt time

HAPPY NEW YEAR

I just got back from my holidays.

Happy new year to all