Meu caro,
Verifique que você está fazendo de errado. Tenho absoluta certeza que a query que postei funciona no Oracle 11gR2. 1) Minha versão do banco SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production 2) Query executada SELECT OWNER, JOB_NAME, STATUS, LOG_DATE FROM (SELECT OWNER, JOB_NAME, STATUS, LOG_DATE, RANK() OVER(PARTITION BY OWNER, JOB_NAME ORDER BY LOG_DATE DESC) RANKING FROM DBA_SCHEDULER_JOB_RUN_DETAILS) WHERE RANKING Rodrigo, obigado pelo retorno, estou usando sim, Oracle 11gR2 mas a sua consulta está retornando o erro abaixo: Eu preciso os 5 últimos JOB executados por cada job_name. ORA-00923: palavra-chave FROM não localizada onde esperada 00923. 00000 - "FROM keyword not found where expected" *Cause: *Action: Erro na linha: 21 Coluna: 22 ________________________________ De: Rodrigo Mufalani Para: oracle_br@yahoogrupos.com.br [2] Enviadas: Sexta-feira, 19 de Julho de 2013 11:41 Assunto: Re: [oracle_br] Ajuda query Monitoring Job Bom dia Rafael, Segue abaixo a consulta usando uma função analítica, suponho que use >=10g onde o dbms_scheduler foi introduzido, sendo assim acho que vai funcionar... SELECT OWNER , JOB_NAME , STATUS , LOG_DATE FROM ( SELECT OWNER , JOB_NAME , STATUS , LOG_DATE , RANK() OVER (PARTITION BY OWNER,JOB_NAME ORDER BY LOG_DATE DESC) RANKING FROM DBA_SCHEDULER_JOB_RUN_DETAILS ) WHERE RANKING SELECT to_char(log_date, 'DD-MON-YY HH24:MI:SS') TIMESTAMP, job_name, status, SUBSTR(additional_info, 1, 40) ADDITIONAL_INFO FROM dba_scheduler_job_run_details where owner = 'XUXA' ORDER BY log_date; Pessoal, bom dia. Montei essa query com intuíto de monitorar a execução dos Schedulers que estão sendo executados. Só que eu preciso de uma modificação nessa query. Eu gostaria de trazer apenas as últimas 5 execuções por cada job(job_name) Eu tinha colocado o "group by job_name" mas ficou faltando mostrar as últimas 5 execuções por job. Então colei a query original para que vocês pudessem ajudar. [As partes desta mensagem que não continham texto foram removidas] -- Links: ------ [1] mailto:mailto:raffaell.ti77%40yahoo.com?subject=Res%3A%20Ajuda%20query%20Monitoring%20Job [2] mailto:mailto:oracle_br%40yahoogrupos.com.br?subject=Res%3A%20Ajuda%20query%20Monitoring%20Job [3] http://br.groups.yahoo.com/group/oracle_br/post;_ylc=X3oDMTJyYjdzMDFuBF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BG1zZ0lkAzExMzk0MgRzZWMDZnRyBHNsawNycGx5BHN0aW1lAzEzNzQyNDAzMjk-?act=reply&messageNum=113942 [3] [4] http://br.groups.yahoo.com/group/oracle_br/post;_ylc=X3oDMTJlZm5xaHFnBF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BHNlYwNmdHIEc2xrA250cGMEc3RpbWUDMTM3NDI0MDMyOQ-- [4] [5] http://br.groups.yahoo.com/group/oracle_br/message/113942;_ylc=X3oDMTM4dGcyaGQ3BF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BG1zZ0lkAzExMzk0MgRzZWMDZnRyBHNsawN2dHBjBHN0aW1lAzEzNzQyNDAzMjkEdHBjSWQDMTEzOTQy [5] [6] http://br.groups.yahoo.com/group/oracle_br/members;_ylc=X3oDMTJmM21kOWg5BF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BHNlYwN2dGwEc2xrA3ZtYnJzBHN0aW1lAzEzNzQyNDAzMjk-?o=6 [6] [7] http://br.groups.yahoo.com/group/oracle_br;_ylc=X3oDMTJlc3VmdTM3BF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BHNlYwN2dGwEc2xrA3ZnaHAEc3RpbWUDMTM3NDI0MDMyOQ-- [7] [8] http://www.mail-archive.com/oracle_br@yahoogrupos.com.br/ [8] [9] http://www.oraclebr.com.br/ [9] [10] http://br.groups.yahoo.com/;_ylc=X3oDMTJkMGtvOGw3BF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BHNlYwNmdHIEc2xrA2dmcARzdGltZQMxMzc0MjQwMzI5 [10] [11] mailto:mailto:oracle_br-traditional%40yahoogrupos.com.br?subject=Mudar Formato de Envio: Tradicional [12] mailto:mailto:oracle_br-digest%40yahoogrupos.com.br?subject=Envio de email: Resenha [13] mailto:mailto:oracle_br-unsubscribe%40yahoogrupos.com.br?subject=Sair do grupo [14] http://br.yahoo.com/info/utos.html [11] [As partes desta mensagem que não continham texto foram removidas] [As partes desta mensagem que não continham texto foram removidas] Links: ------ [1] mailto:rodrigo%40mufalani.com.br [2] mailto:oracle_br%40yahoogrupos.com.br [3] http://br.groups.yahoo.com/group/oracle_br/post;_ylc=X3oDMTJyYjdzMDFuBF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BG1zZ0lkAzExMzk0MgRzZWMDZnRyBHNsawNycGx5BHN0aW1lAzEzNzQyNDAzMjk-?act=reply&messageNum=113942 [4] http://br.groups.yahoo.com/group/oracle_br/post;_ylc=X3oDMTJlZm5xaHFnBF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BHNlYwNmdHIEc2xrA250cGMEc3RpbWUDMTM3NDI0MDMyOQ-- [5] http://br.groups.yahoo.com/group/oracle_br/message/113942;_ylc=X3oDMTM4dGcyaGQ3BF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BG1zZ0lkAzExMzk0MgRzZWMDZnRyBHNsawN2dHBjBHN0aW1lAzEzNzQyNDAzMjkEdHBjSWQDMTEzOTQy [6] http://br.groups.yahoo.com/group/oracle_br/members;_ylc=X3oDMTJmM21kOWg5BF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BHNlYwN2dGwEc2xrA3ZtYnJzBHN0aW1lAzEzNzQyNDAzMjk-?o=6 [7] http://br.groups.yahoo.com/group/oracle_br;_ylc=X3oDMTJlc3VmdTM3BF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BHNlYwN2dGwEc2xrA3ZnaHAEc3RpbWUDMTM3NDI0MDMyOQ-- [8] http://www.mail-archive.com/oracle_br@yahoogrupos.com.br/ [9] http://www.oraclebr.com.br/ [10] http://br.groups.yahoo.com/;_ylc=X3oDMTJkMGtvOGw3BF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BHNlYwNmdHIEc2xrA2dmcARzdGltZQMxMzc0MjQwMzI5 [11] http://br.yahoo.com/info/utos.html [12] mailto:raffaell.t...@yahoo.com?subject=Res%3A%20Re%3A%20%EF%BB%BF%5Boracle_br%5D%20Ajuda%20query%20Monitoring%20Job [13] mailto:oracle_br@yahoogrupos.com.br?subject=Res%3A%20Re%3A%20%EF%BB%BF%5Boracle_br%5D%20Ajuda%20query%20Monitoring%20Job [14] http://br.groups.yahoo.com/group/oracle_br/post;_ylc=X3oDMTJycXNmaDhtBF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BG1zZ0lkAzExMzk1MARzZWMDZnRyBHNsawNycGx5BHN0aW1lAzEzNzQyNDU5ODg-?act=reply&messageNum=113950 [15] http://br.groups.yahoo.com/group/oracle_br/post;_ylc=X3oDMTJlYmc0YWw1BF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BHNlYwNmdHIEc2xrA250cGMEc3RpbWUDMTM3NDI0NTk4OA-- [16] http://br.groups.yahoo.com/group/oracle_br/message/113942;_ylc=X3oDMTM4dXUycTlxBF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BG1zZ0lkAzExMzk1MARzZWMDZnRyBHNsawN2dHBjBHN0aW1lAzEzNzQyNDU5ODgEdHBjSWQDMTEzOTQy [17] http://br.groups.yahoo.com/group/oracle_br/members;_ylc=X3oDMTJmdHBhYmk3BF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BHNlYwN2dGwEc2xrA3ZtYnJzBHN0aW1lAzEzNzQyNDU5ODg-?o=6 [18] http://br.groups.yahoo.com/group/oracle_br;_ylc=X3oDMTJlMGk3MDhrBF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BHNlYwN2dGwEc2xrA3ZnaHAEc3RpbWUDMTM3NDI0NTk4OA-- [19] http://www.mail-archive.com/oracle_br@yahoogrupos.com.br/ [20] http://www.oraclebr.com.br/ [21] http://br.groups.yahoo.com/;_ylc=X3oDMTJkam5iMDFqBF9TAzk3NDkwNDM3BGdycElkAzE2ODI4OTYEZ3Jwc3BJZAMyMTM3MTE0Njg5BHNlYwNmdHIEc2xrA2dmcARzdGltZQMxMzc0MjQ1OTg4 [22] mailto:oracle_br-traditio...@yahoogrupos.com.br?subject=Mudar Formato de Envio: Tradicional [23] mailto:oracle_br-dig...@yahoogrupos.com.br?subject=Envio de email: Resenha [24] mailto:oracle_br-unsubscr...@yahoogrupos.com.br?subject=Sair do grupo [25] http://br.yahoo.com/info/utos.html [As partes desta mensagem que não continham texto foram removidas]
