killing sessions and OS processes

been dealing with hung sessions today.
I'm still not sure of why are they being left there hanging. They are just ready to wait eternally for the "SQL*Net message from dblink" event. I guess that remote instance is failing or something.. I don't have access to that server, so I don't know what's happening there and admins are finns - pretty quiet types, they don't talk much. Anyway. I have faced a problem of having a big number of sessions either waiting for dblink or for client message and I KNOW that neither is coming. So a need in mass killing appears :) You can kill oracle sessions with

ALTER SYSTEM KILL SESSION SID, SERIAL# immediate;

But after you kill it like this, it still stays in the v$session view having the 'KILLED' status. And you cannot get rid of it otherwise but killing the process on the OS level. Because PMON waits for a client to issue next command so to send him "your session has been killed" message, before he can actually eliminate the process. Quite polite, but sometimes you just know that client is not coming back... For this matter there is a orakill.exe tool (for Windows). Used like this:

orakill [instance_name] [spid]

This one will kill the thread inside the oracle.exe process. You need to pass the spid of the process, taken from the v$process view.

So there were two problems in front of me:

1. To kill all the sessions started by a specific program.
2. To kill the processes so that all the resources and locks would be freed.

To solve the first one I coded a nice PL/SQL procedure:

CREATE OR REPLACE PROCEDURE KILL_SESSIONS_BY_PROGRAM (in_program IN varchar2) IS
CURSOR sessions_cur IS
select s.sid
,s.serial#
from v$session s
where lower(s.program)=lower(in_program)
and s.status != 'KILLED';
sessions_rec sessions_cur%ROWTYPE;
sess_to_kill varchar2(50);
cmd_string varchar2(100);
e_marked_for_kill exception;
PRAGMA EXCEPTION_INIT(e_marked_for_kill, -31);
begin
OPEN sessions_cur;
LOOP
FETCH sessions_cur INTO sessions_rec;
IF sessions_cur%ROWCOUNT = 0 AND sessions_cur%NOTFOUND THEN
RAISE_APPLICATION_ERROR (-20001,'NO sessions found!');
END IF;
EXIT WHEN sessions_cur%NOTFOUND;
sess_to_kill := TO_CHAR(sessions_rec.sid)','TO_CHAR(sessions_rec.serial#);
cmd_string:='ALTER SYSTEM KILL SESSION '''sess_to_kill''' immediate';
begin
DBMS_OUTPUT.PUT_LINE(cmd_string);
execute immediate cmd_string;
exception when e_marked_for_kill then
null;
end;
END LOOP;
end;
/

And for the second one I made a small SQLPlus script, which generates me a .cmd file containg as much orakill commands as it is needed. Here it is:

CLEAR BUFFER
set heading off;
set feedback off;
variable sess refcursor;
begin
open :sess for select 'orakill &instance ' p.spid
from v$session s, v$process p
where s.paddr = p.addr
and lower(s.program)=lower('&in_program')
and s.status = 'KILLED';
end;
/
spool h:\sql\kill.cmd;
print sess;
spool off;

privileges in bulk

have been dealing with oracle privileges today for the first time seiously.

It turned out that granting privileges is quite a pain (as everything else) in oracle. I needed to grant select (or any other) privilege on all tables in a schema at once. But oracle standard command only makes it possible to grant privs on a single object. So I had to write my own procedure, which would grant privileges in bulk. Here it is:

CREATE OR REPLACE PROCEDURE GRANT_BULK_PRIV (inSchema IN varchar,
inObjType IN varchar,
inPriv IN varchar,
inUser IN varchar) IS
/******************************************************************************
NAME: GRANT_MULTI_PRIV
PURPOSE: to grant priviledges in bulk
NOTES:
PARAMETERS:
inObjType must specify the oracle defined type of the object (in upper case)
inSchema: Name of the schema where the objects reside (must be in upper case)
inPriv: what priviledge to give (oracle defined privilege name)
inUser: a user or a role to whom the privilege will be granted
Sysdate: 8.02.2007
******************************************************************************/
cursor allobjects (inOwner IN varchar, inType IN varchar) IS
Select o.object_name, o.status
from sys.all_objects o
where o.object_type = inType
and o.owner = inOwner
and o.status = 'VALID' ;
allobjects_rec allobjects%rowtype;
cmdtext varchar(255);
BEGIN
OPEN allobjects (inSchema, inObjType);
LOOP
FETCH allobjects INTO allobjects_rec;
EXIT WHEN allobjects%NOTFOUND;
cmdtext:= 'grant ' ||inPriv|| ' on ' ||inSchema|| '."' || allobjects_rec.object_name ||'" to '|| inUser;
--DBMS_OUTPUT.PUT_LINE(cmdtext);
execute immediate cmdtext;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END GRANT_BULK_PRIV;
/

usage like this:
execute grant_bulk_priv ('SHEMA', 'TABLE', 'SELECT', 'SOME_ROLE');

oracle archive log destinations

been recently having a problem with oracle when the disk was getting full with archivelogs and oracle just stopped responding until the logwriter could save the log and the redo log could be switched. So I thought I should add an alternate location, which would be used if the primary one becomes invalid.

So I read the manual and did like it says:

firstly:

The first method is to use the LOG_ARCHIVE_DEST_n parameter (where n is an integer from 1 to 10) to specify from one to ten different destinations for archival. Each numerically-suffixed parameter uniquely identifies an individual destination.

secondly:

The LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 10) initialization parameter allows you to control the availability state of the specified destination (n). The destination state can have three values: ENABLE,DEFER, or ALTERNATE. The value ENABLE indicates that Oracle can use the destination, whereas DEFER indicates that the location is temporarily disabled. The third value, ALTERNATE, means that the destination is an alternate. It's availability state is DEFER, unless there is a failure of its parent destination, in which case its state becomes ENABLE.

So after I added parameters to my init.ora file accordingly, the database wouldn't start!

Alert log didn't contain any errors, it would simply end with this line:

Using log_archive_dest parameter default value

But if to try starting the database from sqlplus with startup nomount, it would give this error:

ORA-00439: feature not enabled: Managed Standby

So it comes out that the Managed Standby is a feature of Enterprise Edition ONLY! And LOG_ARCHIVE_DEST_n depends on that feature and therefore cannot be used with Standard Edition.
So I have do with LOG_ARCHIVE_DEST parameter and just make the disk larger... Can't think of any other good solution now...

Administrator's guide doesn't say a word about this. It has a link although to the 9i Reference, which has a note of this dependency trick..

This parameter is valid only if you have installed Oracle Enterprise
Edition. You may continue to use LOG_ARCHIVE_DEST if you have installed Oracle
Enterprise Edition. However, you cannot use both LOG_ARCHIVE_DEST_n and
LOG_ARCHIVE_DEST, as they are not compatible.

rich text editor for web

lately have been looking for some free and crossbrowser rich text editors. Out of all the variety, picked out two which seemed the best:

1. TinyMCE
http://tinymce.moxiecode.com/
pros:
nice interface, loads faster, has a lot of commands (huge number of them). it's a practically winword editor under web. *amazed*. Has API. All browsers supported (even opera and safari)
cons:
the image and file browsers are commercial. Although it is possible to link your own filebrowser as plugin. But I couldn't find any freeware ones. And who wants to code, if there is something ready and for free? Therefore..

2. FCKeditor
http://www.fckeditor.net/

pros: also very powerful, a lot of commands, API. And has a free file and image browser included.
cons: interface is a little bit slow and loads a little bit slower than Tiny. Neither Opera nor Safari are supported.


I personally have picked the FCKeditor, mostly because of the free filebrowser. And it's not my problem if someone likes to use stupid browsers ;)
Othewise would have picked the TinyMCE...

Hello World! :)

here comes the first record.

I have been into some ajax programming lately. Feels like this is the future of the application development in the whole. Google's spreadsheet has totally amazed me. There is even some server->client communication. How is that implemented I wonder... Got to learn it someday.

Today I have been into some beginner programming using jQuery (http://jquery.com/)
It's really easy to learn and it saves a lot of time when it comes to DHTML tricks.

The best thing with it, is that you don't have to specify events on objects using something like onclick="foo()". With jQuery you set the events from the script area. And what is really nice is that you can select many object (and do it very easily) at once, using XPath and/or CSS.

So for example you set up a click event on a button called "btn1" like this:

$("button[@name=btn1]").click(function(){ alert("button clicked!") });

this will register an "onclick" event with the button who's name parameter is equal "btn1"

But what I was fighting with today was: how to reference the parent windows out of an iframe?
Turned out to be like this:

parent.$("element").action()

So it was as easy as putting "parent" in front of the $ sign.
Oh and also that the parent page must include the jquery source too.

Particularly what I have built today. Is a script which uploads pictures "on the fly", without a page reload. And immediately displays the newly uploaded picture on the page. For this matter I have used an iframe which had an upload form inside of it. After the form submits, the php script saves and registeres the picture in db and outputs some jQuery code, which dynamically adds the new picture thumb to the parent frame (this is why I needed to reference the parent windows with jQuery). So this way it looks as if picture being uploaded totally dynamically, without a page reload.

Soon my work will be done, I'll post a link to it.