How to identify sessions from a Database Object

Oracle Session (SID) Details on a database object:
=====================================

SELECT   sid, serial#, vs.*
  FROM   v$session vs
 WHERE   vs.sid IN (SELECT   SID
                   FROM   v$access
                  WHERE   object = <object_name>)

***Please leave a comment***

Difference Between Length and Vsize

Difference Between Length and Vsize

It appears that the Oracle Functions Length ( ) and Vsize () return the same results, but practically there is a difference in their usage

For Example :

Select Length ('Have a Nice Day') , Vsize ( 'Have a Nice Day') From Dual; Result : 15 , 15 

Well the result is same for both , but let’s see another example :

Select Length (9.234), Vsize ( 9.234) from Dual;
Result : 5 , 4


More Examples :
Select Length ( Sysdate) , Vsize ( Sysdate)  From Dual;
Result : 9 , 7  ( Current sysdate is '05-May-2013')

So we can see difference in the results, this is because  Vsize returns the number of bytes used to store the internal representation in any datatype, whereas Length returns the length of the passed string. Length function takes the character arguments (Char, Varchar2, Nchar, NVarchar2, Clob or Nclob) , so even if you pass a non-character argument, the Length Function converts the data type implicitly into a string and then returns its length.

***Please leave a comment***

How to convert number of seconds into Oracle date format ( Days Hours :Minutes : Seconds )

How to convert number of seconds into Oracle date format (Days Hours:Minutes:Seconds):

SELECT TO_CHAR(TO_DATE(ROUND('<variable>'), 'SSSSS'), 'HH24:MI:SS') Hours_Minutes_Format

FROM DUAL;

SELECT ROUND('<variable>'/'86399') || ' Day(s) ' || TO_CHAR(TO_DATE(ROUND(MOD('<variable>','86399')), 'SSSSS'), 'HH24:MI:SS') Days_Hours_Minutes_Format

FROM DUAL;

Replace <variable> with number of seconds. ROUND function is used to round to nearest integer if you provide seconds in a decimal format

Example1: 

SELECT TO_CHAR(TO_DATE(ROUND('3000'), 'SSSSS'), 'HH24:MI:SS') Hours_Minutes_Format

FROM DUAL;

Output:

Hours_Minutes_Format

------------------------------

00:50:00

3000 seconds is displayed in the hours, minutes and seconds format: read as 50 minutes

Example2: 

SELECT TO_CHAR(TO_DATE(ROUND('30007.345'), 'SSSSS'), 'HH24:MI:SS') Hours_Minutes_Format

FROM DUAL;

Output:

Hours_Minutes_Format

------------------------------

08:20:07

30007.345 seconds is displayed in the hours, minutes and seconds format: read as 8 hours 20 minutes and 7 seconds

Example3: 
Above examples cover the seconds for a day. Lets take a large number of seconds which goes beyond a day.  Below query provides the output for 90740 seconds

SELECT ROUND('90740'/86399) || ' Day(s) ' || TO_CHAR(TO_DATE(ROUND(MOD('90740','86399')), 'SSSSS'), 'HH24:MI:SS') Days_Hours_Minutes_Format

FROM DUAL;

Output:

Days_Hours_Minutes_Format

---------------------------------------

1 Day(s) 01:12:21

90740 seconds is displayed in the days, hours, minutes and seconds format: read as 1 Day 1 hours 12 minutes and 21 seconds

Tip: 

This is very useful if you are trying to get the run time of a concurrent request in a readable format in Oracle Applications. Sample query below

SELECT request_id, (actual_completion_date - actual_start_date)*24*60*60 run_time_secs,

       TO_CHAR(TO_DATE((actual_completion_date - actual_start_date)*24*60*60, 'SSSSS'), 'HH24:MI:SS') run_time_Hours_Minutes_Format

FROM fnd_concurrent_requests

WHERE request_id  = 89110175;

Sample Output:

***Please leave a comment***