SQL & PL/SQL

Hi,

The elapsed time in the below query to be displayed in  hours, minutes, seconds and milliseconds

SELECT SQL_ID,

      PARSING_SCHEMA_NAME,

      CHILD_NUMBER,

      DISK_READS,

      EXECUTIONS,

      LAST_LOAD_TIME,

      ELAPSED_TIME

      FROM V$SQL

The last column is display in nanoseconds which we have to convert it into  hours, minuts and seconds column  like   5h:55mi:55ss:67ms   something in this format

Sample Result

REM INSERTING into EXPORT_TABLE

SET DEFINE OFF;

Insert into EXPORT_TABLE (SQL_ID,PARSING_SCHEMA_NAME,CHILD_NUMBER,DISK_READS,EXECUTIONS,LAST_LOAD_TIME,ELAPSED_TIME) values ('4w6bsuqvgw009','REPORTER',0,0,3,'2017-10-20/13:09:51',2955164);

Insert into EXPORT_TABLE (SQL_ID,PARSING_SCHEMA_NAME,CHILD_NUMBER,DISK_READS,EXECUTIONS,LAST_LOAD_TIME,ELAPSED_TIME) values ('dmu0n9pz4n02p','REPORTER',0,0,1,'2017-11-02/11:24:04',803);

Insert into EXPORT_TABLE (SQL_ID,PARSING_SCHEMA_NAME,CHILD_NUMBER,DISK_READS,EXECUTIONS,LAST_LOAD_TIME,ELAPSED_TIME) values ('2a7x0uj8q402p','REPORTER',0,0,1,'2017-11-02/11:24:06',873);

Insert into EXPORT_TABLE (SQL_ID,PARSING_SCHEMA_NAME,CHILD_NUMBER,DISK_READS,EXECUTIONS,LAST_LOAD_TIME,ELAPSED_TIME) values ('autntzckk8035','REPORTER',0,0,1,'2017-11-02/11:24:10',804);

Thank you

Regards

hsemar