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,指定执行时间

How do I see who is currently connected?

SELECT username, program FROM v$session WHERE username IS NOT NULL;

How to see the database version?

By Radoslav Rusinov

To view components and their versions, loaded into the database:

SELECT comp_name, status, version FROM dba_registry;

To view version numbers of core library components, including the database:

SELECT banner FROM v$version;

To display options that are installed (the value column is ‘FALSE’ if the option is not installed)

SELECT parameter, value FROM v$option;

How to set a table in read-only mode ?

ALTER TABLE emp
ADD CONSTRAINT read_only CHECK (1=1) DISABLE VALIDATE;

It is now, impossible to insert, update or delete anything with this table.

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

Using Loggers in Java Applications

In order to add logging features to your applications, you can use apache log4j API

You need 3 things

1) have access to log4j jar file

2) have a properties file that will act as a configuration environment for the logging process

3) write the appropriate Java code

here is more details using the Oracle Jdeveloper IDE

Download log4j1.3.jar from http://logging.apache.org

Put the log4j.jar in the classpath, or add it to the Jdeveloper project as shown

You need to specify where the properties file is located, otherwise logging will fail

As an example, I have create a properties file and called it log4j.properties and added placed the file at C:\

You can use the project properties dialogue to define the location of the Properties file

Invoke project properties à Run/Debug à Edit the default setting à and add the Java option shown below

-Dlog4j.configuration = file:c:/log.properties

The following is a sample properties file in which the logging output is specified (log4j.appender.R.File=C:\x.log)

Log.properties

log4j.rootLogger=INFO, R

# first appender writes to a file

log4j.appender.R=org.apache.log4j.RollingFileAppender

#RollingFileAppender OPTIONS

log4j.appender.R.Threshold=INFO

log4j.appender.R.ImmediateFlush=true

log4j.appender.R.File=C:\x.log

log4j.appender.R.MaxFileSize=6000KB

log4j.appender.R.MaxBackupIndex=2

#log4j.debug=true

#log4j.disable=fatal

# Pattern to output the caller's file name and line number.

log4j.appender.R.layout=org.apache.log4j.PatternLayout

log4j.appender.R.layout.ConversionPattern=%p %c %d{dd/MM/yyyy HH:mm:ss} - %m%n

#log4j.appender.stdout.layout.ConversionPattern=%5p [%t] (%F:%L) - %m%n

#log4j.appender.R.layout.ConversionPattern=%p %t %c - %m%n

Finally, this is an example code

package model;

import org.apache.log4j.Logger;

public class Class1 {

public Class1() {

Logger log = Logger.getLogger(Class1.class.getName());

log.warn("hello warning");

}

public static void main(String[] args) {

Class1 class1 = new Class1();

}

}

Note: the (Class1.class.getName()) retrieves the name of the current class that is being executes. Every logging message entry will have this variable printed so that the users can know which class was responsible for this entry.. off course, if you choose to use a string value instead, that strng value shall appear in each logging entry.

Thanks to Mr. Ammar for this Article..

Showing Calendar in SQL*Plus

Similar to UNIX cal command

Thanks to Tony Davis for this tip

Thanks to Mr. Ammar too