oracle: auto kill lock session

refer: http://www.via6.com/topico.php?tid=66496

create or replace procedure kill_locked_usr(itime in integer) as
--grant select on v$session to user;
--grant select on v$lock to user;
my_cursor number;
my_statement varchar2(100);
result integer;
cursor c1 is
select 'alter system kill session ' || '''' || v.sid || ',' || v.SERIAL# || ''' immediate' command
from v$session v
where v.SID in (select distinct(l.sid) from v$lock l where
l.block = 1 and
l.ctime > itime);
-- select 'alter system kill session ' || '''' || to_char(a.sid) || ',' ||
-- to_char(a.serial#) || ''''
-- from v$session a, v$lock b
-- where a.sid = b.sid
-- and b.lmode = 6
-- and a.username like 'THE_BOREING_USER'
-- and b.ctime > time;
begin
open c1;
loop
fetch c1
into my_statement;
exit when c1%notfound;
dbms_output.put_line(my_statement);
my_cursor := dbms_sql.open_cursor;
dbms_sql.parse(my_cursor, my_statement, dbms_sql.v7);
--dbms_output.put_line('1 - ' || my_statement);
result := dbms_sql.execute(my_cursor);
---- execute immediate sqltxt;
dbms_sql.close_cursor(my_cursor);
end loop;
close c1;
result := 0;
end;
/

select * from user_objects o
where o.status <> 'VALID';

VARIABLE JOBNO NUMBER;
begin
--1分钟检查一次
DBMS_JOB.SUBMIT(:JOBNO, 'KILL_LOCKED_USER(90);',SYSDATE,'SYSDATE+(60/86400)',NULL);
COMMIT;
end;

select job,log_user,priv_user,last_date,next_date,interval from user_jobs;

begin
dbms_job.remove('2');

commit;
end;

停止一个JOB
SQL> exec dbms_job.broken(2,true)

SQL>commit //必须提交否则无效

启动作业
SQL> exec dbms_job.broken(2,false)

exec dbms_job.broken(2,false,next_day(sysdate,'monday')) //标记为非broken,指定执行时间

Oracle Stored procedures JAVA

Purpose : To demonstrate that Oracle database has an embedded JVM

Objective: PL/SQL is great, but it is an Oracle Proprietary language that cannot be ported to other database platforms,, Java is portable

Environment : The example uses SQL*Plus, but other tools also work

Oracle Version: I Used Oracle9i, Oracle8i should work similarly

SQL> CREATE OR REPLACE and RESOLVE JAVA SOURCE NAMED "Hello" AS
public class Hello {
static public String Msg(String tail) {
return "Hello " + tail;
}
}
/
Java created.


SQL> CREATE OR REPLACE FUNCTION hello( str VARCHAR2 )
RETURN VARCHAR2 AS
LANGUAGE JAVA NAME
'Hello.Msg (java.lang.String)
return java.lang.String';
/

SQL>SELECT hello (ENAME) from EMP;

HELLO(ENAME)
-----------------------------
Hello SMITH
Hello ALLEN
Hello WARD
Hello JONES
Hello MARTIN
Hello BLAKE
Hello CLARK
Hello SCOTT
Hello KING
Hello TURNER
Hello ADAMS

HELLO(ENAME)
-----------------------------
Hello JAMES
Hello FORD
Hello MILLER

Bulk collect on records

Lets start with a working example of bulk collect into records.

SQL> create table T ( c1 number, c2 number );

Table created.

SQL> declare
 2    type r is record (
 3       x number,
 4       y number );
 5
 6    type rt is table of r;
 7
 8    d rt;
 9
10  begin
11    select rownum, rownum
12    bulk collect into d
13    from all_Objects
14    where rownum <= 20;
15
16    forall i in 1 .. 20
17      insert into T values d(i);
18
19  end;
20  /

PL/SQL procedure successfully completed.

But what if table T has three columns, and we wanted to add the constant value "10" when we insert. Then we have problems because it would look like this:

SQL> declare
 2    type r is record (
 3       x number,
 4       y number );
 5
 6    type rt is table of r;
 7
 8    d rt;
 9
10  begin
11    select rownum, rownum
12    bulk collect into d
13    from all_Objects
14    where rownum <= 20;
15
16    forall i in 1 .. 20
17      insert into T values ( d(i).x, d(i).y, 10);
18
19  end;
20  /
insert into T values ( d(i).x, d(i).y, 10);
       *
ERROR at line 17:
ORA-06550: line 17, column 28:
PLS-00436: implementation restriction: cannot reference .... etc

However, what we CAN do is use objects and then apply SQL to them. All we need is some objects to mimic our PLSQL types

SQL> create or replace type r is object ( x number, y number );
 2  /

Type created.

SQL> create or replace type rt is table of r;
 2  /

Type created.

SQL> declare
 2    d rt; -- this is now pointing to a database definition not a plsql definition
 3
 4  begin
 5    select r(rownum, rownum)
 6    bulk collect into d
 7    from all_Objects
 8    where rownum <= 20;
 9
 10    insert into T
 11    select x,y,10
 12    from table(d);
 13
 14  end;
 15  /

PL/SQL procedure successfully completed.