Monthly Archives: August 2013

Oracle, script, SQL, sqlplus

Currently executing SQL in Oracle database

Leave a comment

I was just asked by a junior administrator, how to determine what SQL is currently executing against her database. While this may seem a trivial task for those in the industry for some time, I thought it a very good question. The below SQL will display currently executing SQL within an Oracle RDBMS.

1. Logon to SQL*PLUS with dba privileges.

2. Execute the following SQL block against the v$sql and v$session tables. NOTE: in a grid environment, use the gv$ views.

select sql_text from v$sql where sql_id in
(select sql_id from v$session where status=’ACTIVE’);

3. Displayed results show currently executing SQL blocks.

SQL> select sql_text from v$sql where sql_id in
2 (select sql_id from v$session where status=’ACTIVE’);

SQL_TEXT
——————————————————————————–
select sql_text from v$sql where sql_id in (select sql_id from v$session where s
tatus=’ACTIVE’)

Larry Catt, OCP

hp-ux, linux, Oracle

Translate UNIX / Linux EPOCH time to Oracle date

Leave a comment

Translate UNIX / Linux EPOCH time to Oracle date

Epoch time in computers is general defined as the number of milliseconds since 1 January 1970 and provides for a very useful way of determining times between system and other time specific functions.   However, it is very difficult for humans to read.   The following code will convert an Epoch time to Oracle date time format.

Given epoch time is 579052800000. we can retrieve oracle time as below.

SQL> define test_time=579052800000

SQL> select  (TO_DATE(’01-01-1970′,’MM-DD-YYYY’) + (&test_time / 86400000)-1) from dual;

old   1: select  (TO_DATE(’01-01-1970′,’MM-DD-YYYY’) + (&test_time / 86400000)-1) from dual

new   1: select  (TO_DATE’01-01-1970′,’MM-DD-YYYY’) + (579052800000 / 86400000)-1) from dual

(TO_DATE(

———

07-MAY-88

SQL>

and convert standard oracle date to epoch

 

select to_char(sysdate – to_date(’01-01-1970′,’MM-DD-YYYY’))*86400000 from dual;

 

SQL> select to_char(sysdate – to_date(’01-01-1970′,’MM-DD-YYYY’))*86400000 from dual;

TO_CHAR(SYSDATE-TO_DATE(’01-01-1970′,’MM-DD-YYYY’))*86400000 ————————————————————

1.3807E+12

SQL>

Larry Catt