Wednesday, February 16, 2011
Appdata
Tuesday, November 4, 2008
How to use commit in a Trigger
Say you have a procedure which inserts/updates records.
And you want to do this after a row is updated in a table in oracle.
Now you create a trigger and call this procedure. But it fails because Oracle throws an error saying "Commit - not allowed in a trigger".
The solution for this is to use the following :
create or replace myprocedure is
pragma AUTONOMOUS_TRANSACTION;
begin
...
do stuff
...
end;
And that should take care of it.
Thursday, May 15, 2008
How to set the timeout variable in AIX
One of the things we noticed was that the session timeout was at a very low threshold. You leave your desk for 5 mins and your session is gone. Talked to the SA and the line is that its a compliance thing.
So what is the work around ?
set up the TMOUT environment variable. A value of 0 is unlimited.
System-wide, it could be added to /etc/profile; otherwise add it tousers $HOME/.profile
export TMOUT=3600
would terminate session if inactive for 1 hour
Tuesday, October 2, 2007
How to Optimize Sql in Oracle
So the scenario is you have your business users crying foul over how slow the system is. It could be anything a web application batch process, some stored procedures running forever, hell even some JDBC calls.
You go to the DBA and he throws some blah at you about how overworked he is and then will try to fit you in but you never know when he's gonna get to it.
So how do you know what to look for and do it yourself or get someone to get it done.We'll use oracle as an example. Some simple items to look out for :
>Check the tables in question:
select table_name, last_analyzed
from user_tables
where table_name like '%your tablename%'
More often then not the dates will be old which suggests you got to get these tables analyzed.
> Simply Run analyze on the tables. This may take a while to run depending upon the size of the data in the table
Analyze Table your_tablename compute statistics
Look at the indexes on each of the tables.
Often you will find they do not have indexes or they do but not on the columns that queries use.
So there you go create indexes on the most queried columns in the sql
Create index ID1 on your_table_name(Column_name)
.
Bham ! This should increase the efficiency 75% off the time. Now you have everything working at a threshold that is tolerable.For further fine tuning break down the code and optimize each query to get the output you desire.
By this time the DBA should be able to poke around and find the bottlenecks if any, in the system.
All things Technical and then some
Starting today I thought I would like to write simple How Tos on Techie Stuff.
It's not going to be a tutorial, there are several Experts & Gurus out there for that. It's gonna be simple tricks for newbies who want to get something done when they are caught in a techy bind as they say.
So welcome aboard and watch out for some geeky stuff :)