Who has locked my package?

This question I have frequently faced while developing packages/procedures in PL/SQL in Oracle. 

Question: How will I identify who has locked a procedure or a package. I need to identify this because it should not hang after the compilation is issued?

Answer: You may see the details from v$sql with the search text (Your package name). If the query fetches results then join it with v$session to get the session information.

select * 
from v$sql, v$session
where sql_address=address
and upper(sql_text) like '%PACKAGE_NAME%';

Hope this simple tip saves you some time.

Getting file size with PL/SQL

UTL_FILE procedure has been enhanced in Oracle 9i and since then it provides a procedure fgetattr to return file size. PL/SQL Evangelist Steven Feuerstein has come up with this function that returns file length.

   location_in   IN   VARCHAR2,
   file_in       IN   VARCHAR2
   TYPE fgetattr_t IS RECORD (
      fexists       BOOLEAN,
      file_length   PLS_INTEGER,
      block_size    PLS_INTEGER

   fgetattr_rec   fgetattr_t;
   UTL_FILE.fgetattr (
      location         => location_in,
      filename         => file_in,
      fexists          => fgetattr_rec.fexists,
      file_length      => fgetattr_rec.file_length,
      block_size       => fgetattr_rec.block_size
   RETURN fgetattr_rec.file_length;
END flength; 

Pass a directory structure name as first parameter and the file name as the second parameter.

Create directory TEST as '/tmp';

select flength('TEST','test.txt') size_in_bytes from dual;


CAUTION: If you are using then there is a database bug which might not return the exact file size. This bug has been fixed in version.

For more information visit here.