Home/Database/Oracle Database
  1. Asked: January 23, 2023In:

    How to Find Queries Running For More than 5 Minutes

    Hassan AbdElrahman
    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant

    To find the queries running for more than 5 or x of minutes you can try the below query and change the seconds parameter as per your need: Query: SELECT S.USERNAME ,Q.SQL_ID ,ROUND (S.LAST_CALL_ET, 2) TIME_IN_SECONDS ,ROUND (S.LAST_CALL_ET / 60, 2) TIME_IN_MINS ,S.SID ,Q.SQL_TEXT FROM GV$SESSION S,Read more

    To find the queries running for more than 5 or x of minutes you can try the below query and change the seconds parameter as per your need:

    Query:

    SELECT S.USERNAME
,Q.SQL_ID
,ROUND (S.LAST_CALL_ET, 2) TIME_IN_SECONDS
,ROUND (S.LAST_CALL_ET / 60, 2) TIME_IN_MINS
,S.SID
,Q.SQL_TEXT
FROM GV$SESSION S, V$SQL Q
WHERE S.SQL_ID = Q.SQL_ID
AND STATUS = 'ACTIVE'
AND USERNAME LIKE '%APPS%'
AND S.LAST_CALL_ET > 300;

    Output:

    USERNAME SQL_ID TIME_IN_SECONDS TIME_IN_MINS
    —————————— ————- ————— ————
    SID
    ———-
    SQL_TEXT
    ——————————————————————————–
    APPS 497wh6n7hu14f 10562436 176040.6
    331
    BEGIN FND_CP_GSM_IPC.Get_Message(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10); END;

    1 row selected.

    Note: 300 is (5*60). change it as you want to.

    See less
  2. Asked: November 24, 2022In:

    Directory Object vs BLOB

    Hassan AbdElrahman
    Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | Oracle Senior ERP Technical Consultant

    It depends on the number and size of files you want to store. If you have a small amount of files you can store them into the database however, the database size will get bigger by time. On the other side the directory you can assign a specific space you need based on your size of the files and youRead more

    It depends on the number and size of files you want to store. If you have a small amount of files you can store them into the database however, the database size will get bigger by time. On the other side the directory you can assign a specific space you need based on your size of the files and you can leverage other advantages like to open the file write into it and so on.

    This a brief and for sure there are other benefits for each option but hopefully my answer give you a little insight.

    See less
  3. Asked: July 30, 2017In:

    How to Create the TKPROF Trace File in oracle ?

    Mmorsy
    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    Hello , Solution : For the TKPROF a) tkprof rawtrace.trc output_file explain=apps/apps/sort=(exeela,fchela) sys=no b) rawtrace.trc: Name of trace file output_file: tkprof out file explain: This option provides the explain plan for the SQL statements sort: This provides the sort criteria in which allRead more

    Hello ,

    Solution :

    • For the TKPROF
      a) tkprof rawtrace.trc output_file explain=apps/apps/sort=(exeela,fchela) sys=no
      b) rawtrace.trc: Name of trace file
      output_file: tkprof out file
      explain: This option provides the explain plan for the SQL statements
      sort: This provides the sort criteria in which all SQL statements will be sorted. This will bring the bad SQL at the top of the outputfile.
      sys=no: Disables SQL statements issued by user SYS

    Regards ,

    Mahmoud Morsy

    See less
  4. Asked: July 30, 2017In:

    How to check Archive log sequence in Oracle DB 11g?

    Mmorsy
    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    Hi, Use the below query : [code] SELECT * FROM v$archived_log ORDER BY stamp DESC [/code] Regards, Mahmoud Morsy

    Hi,

    Use the below query :

    [code]
    SELECT *
    FROM v$archived_log
    ORDER BY stamp DESC
    [/code]

    Regards,

    Mahmoud Morsy

    See less
  5. Asked: July 27, 2017In:

    How to kill current session in Oracle (ORA-00027 ) ?

    Mmorsy
    Mmorsy Junior mahmoudmorsymm1985@gmail.com
    This answer was edited.

    You can use this SQL statement to get the SID, SERIAL# of the current session: SELECT s.sid,s.serial#,spid,TRIM (s.machine) machine,TRIM (s.module) module,statusFROM v$session s, v$process pWHERE paddr = addr AND module IS NOT NULLORDER BY 1, 2 Then with sqlplus Run ALTER SYSTEM KILL SESSION 'sid,seRead more

    You can use this SQL statement to get the SID, SERIAL# of the current session:


    SELECT s.sid,
    s.serial#,
    spid,
    TRIM (s.machine) machine,
    TRIM (s.module) module,
    status
    FROM v$session s, v$process p
    WHERE paddr = addr AND module IS NOT NULL
    ORDER BY 1, 2

    Then with sqlplus Run

    ALTER SYSTEM KILL SESSION 'sid,serial#';

    Or by shell commnad:

    kill -9 spid.

    Regards,

    Mahmoud Morsy.

    See less
  6. Asked: July 11, 2017In:

    How to restore database with until time ?

    Mmorsy
    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    Hello ,You can run this command in the shell session:export NLS_DATE_FORMAT="YYYY-MM-DD:HH24:MI:SS"Thenconnect with RMAN rman target /RUN{ ALLOCATE CHANNEL prmy1 TYPE disk ; set until time= "to_date('14/07/2015 10:34:36','dd/mm/yyyy hh24:mi:ss')"; RESTORE DATABASE; RECOVER DATABASE; RELEASE CHANNELRead more

    Hello ,

    You can run this command in the shell session:

    export NLS_DATE_FORMAT=”YYYY-MM-DD:HH24:MI:SS”

    Then

    connect with RMAN
    rman target /

    RUN
    {
    ALLOCATE CHANNEL prmy1 TYPE disk ;
    set until time= “to_date(’14/07/2015 10:34:36′,’dd/mm/yyyy hh24:mi:ss’)”;
    RESTORE DATABASE;
    RECOVER DATABASE;
    RELEASE CHANNEL prmy1;
    }

    See less