Lock Identification

/* This script shows all users who are waiting for locked resources */

SELECT b.username, b.serial#, c.sid, c.owner object_owner, c.object, d.id1, a.sql_text SQL FROM v$sqltext a,v$session b, v$access c, v$lock d
WHERE a.address = b.sql_address AND a.hash_value = b.sql_hash_value AND b.sid = c.sid AND b.lockwait = d.kaddr AND c.owner NOT IN ('SYS','SYSTEM');

/* This script shows the users who are locking the above waiting resources. This script will also show the SQL being executed by the user. NOTE: If the locking user is no longer executing any SQL, the rows returned will be 0. The user, however, is still locking the above users since he/she has not yet committed his/her transaction -- maybe they are away from their desk?? You should call them and ask them to either commit or rollback. */

SELECT x.serial#, x.sid, x.username, y.id1, z.sql_text SQL FROM v$session x, v$lock y, v$sqltext z
WHERE y.id1 IN (SELECT distinct b.id1 FROM v$session a, v$lock b WHERE b.kaddr = a.lockwait) AND x.sid = y.sid AND z.hash_value = x.sql_hash_value AND y.request = 0


/* This script is identical to the above script EXCEPT that it does not return the SQL text being executed by the locking user. You can use it to quickly identify the locking user's serial#, sid and username if you get to a point where you will need to kill the user's session. */

SELECT x.serial#, x.sid, x.username, y.id1 FROM v$session x, v$lock y WHERE y.id1 IN (SELECT distinct b.id1 FROM v$session a, v$lock b WHERE b.kaddr = a.lockwait) AND x.sid = y.sid AND y.request = 0;

To Check the Database Version

Here is the query to check what is the Database version

Connect as sys/pwd@dbname as sysdba

select username,account_status,default_tablespace,temporary_tablespace,created
from dba_users


Sample Output

PRODUCT VERSION STATUS
NLSRTL 10.2.0.1.0 Production
Oracle Database 10g Enterprise Edition 10.2.0.1.0 Prod
PL/SQL 10.2.0.1.0 Production
TNS for Linux: 10.2.0.1.0 Production

PL/SQL Last Compiled Date/Time

To check when was the pl/sql procedure compiled.

Connect to the schema which you want and give the query as

Eg:

select to_char(LAST_DDL_TIME,'YYYY-MM-DD HH24:MI:SS') from user_objects where object_name = upper('dcs_dev');