✔ SQL Abfrage

Capper

Capper

Grünschnabel
Hai,

ich habe folgendes Problem. Ich möchte aus der v$loghist die Anzahl der Redologs pro Tag auslesen.
Das ansich ist ja kein Problem und funktioniert mit dem Befehl:
select count(SEQUENCE#) from v$loghist where FIRST_TIME like '02-APR-07';

Ergibt dann folgende Ausgabe: 74 --> Die Spaltenüberschriften habe ich ausgeblendet, da ich für diese keine Verwendung habe

Jetzt möchte ich aber auch noch das Datum mit ausgeben, das in der Spalte FIRST_TIME steht, das ich nacher folgende Ausgabe erhalte
74 02-APR-07

Ich habe schon einige "select"-Statements ausprobiert, die aber alle nicht zu dem gewünschten Ergebnis führen.

Statement 1:
select FIRST_TIME, count (SEQUENCE#) from v$loghist where FIRST_TIME like '02-APR-07' group by FIRST_TIME;

Ausgabe 1:
02-APR-07 1
02-APR-07 1
02-APR-07 1
02-APR-07 1
02-APR-07 1
02-APR-07 1
02-APR-07 1
02-APR-07 1
02-APR-07 1
02-APR-07 1
02-APR-07 1

Statement 2:
select FIRST_TIME, count (SEQUENCE#) from v$loghist where FIRST_TIME like '02-APR-07';

Ausgabe 2:
ERROR at line 1:
ORA-00937: not a single-group group function

Statment 3:
select count (SEQUENCE#) from v$loghist where FIRST_TIME in
(select FIRST_TIME from v$loghist where FIRST_TIME like '02-APR-07');

Ausgabe 3:
74

Statement 4:
select FIRST_TIME from v$loghist where FIRST_TIME in
(
select count (SEQUENCE#) from v$loghist where FIRST_TIME like '02-APR-07'
);

Ausgabe 4:
select FIRST_TIME from v$loghist where FIRST_TIME in
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Wie man sehen kann, hat keines dieser Statements zur gewünschten Ausgabe geführt :( . Ich weis jetzt nicht ob ich irgendwo einen Denkfehler habe, aber im Prinzip müsste ich doch nur folgende Statements vereinen um die gewünschte ausgabe zubekommen?

select count(SEQUENCE#) from v$loghist where FIRST_TIME like '02-APR-07'; und select FIRST_TIME from v$loghist where FIRST_TIME like '02-APR-07';

Kann mir hier jemand weiterhelfen, bin schon total verzweifelt und benötige dringend Hilfe :confused:

Danke schon mal im vorraus

Grüße Capper
 
Die Spalte FIRST_TIME ist vom Datentype DATE und dazu gehört auch immer die Uhrzeit. Somit funktioniert dein GROUP nicht, da zwar das Datum, aber niemals die Uhrzeit der Zeilen übereinstimmt.

Probiers mal so:
SQL: 
SELECT  TRUNC(FIRST_TIME), 
        COUNT(*) 
FROM    V$LOGHIST 
WHERE   FIRST_TIME >= TO_DATE( '02.04.2007', 'DD.MM.YYYY' ) 
AND     FIRST_TIME <  TO_DATE( '02.04.2007', 'DD.MM.YYYY' ) + 1 
GROUP   BY TRUNC(FIRST_TIME);

TRUNC(FIRST_TIME)          COUNT(*)
------------------- ---------------
02.04.2007 00:00:00               7

DU solltest auch immer mit der TO_DATE Funktion und dem Formatstring arbeiten um ein Datum anzugeben, da Oracle mit anderen Clienteinstellungen andere Datumsformate erwarten kann. (Ist Session bzw. Länderabhängig)
Mann könnte die WHERE Bedingung auch mit ...
SQL: 
TRUNC(FIRST_TIME) = TO_DATE( '02.04.2007', 'DD.MM.YYYY' )
... schreiben. In diesem Fall kann Oracle aber kein Index nutzen (sofern überhaupt einer existiert)
 
Zuletzt bearbeitet von einem Moderator:
Noch als Anmerkung:
Was vielleicht spannender ist, als die Anzahl der Logswitches ist die Anzahl und vor allem die Größe der Archivelogs:

SQL: 
SELECT  TIMES.HOUR,
        LOGS.LOGSIZE,
        LOGS.LOGCOUNT
FROM    (
    SELECT  TO_CHAR( SYSDATE - 1 + ROWNUM / 24, 'YYYY-MM-DD HH24') || ':00' AS HOUR,
            ROWNUM AS ZEILE
    FROM    DUAL 
    CONNECT BY SYSDATE - 1 + ROWNUM / 24 <= SYSDATE        
) TIMES 
LEFT JOIN
( 
    SELECT  TO_CHAR( NEXT_TIME, 'YYYY-MM-DD HH24') || ':00' AS HOUR, 
            ROUND( SUM( BLOCKS * BLOCK_SIZE )/1024/1024, 2) AS LOGSIZE,
            COUNT( * ) AS LOGCOUNT
    FROM    V$ARCHIVED_LOG log
    WHERE   NEXT_TIME  > SYSDATE - 1
    GROUP   BY TO_CHAR( NEXT_TIME, 'YYYY-MM-DD HH24')   
) LOGS
ON  (
    TIMES.HOUR = LOGS.HOUR
)
ORDER BY TIMES.ZEILE;

HOUR                     LOGSIZE        LOGCOUNT
---------------- --------------- ---------------
2007-04-03 13:00         1088,53              18
2007-04-03 14:00          847,29              14
2007-04-03 15:00         1330,42              22
2007-04-03 16:00          967,59              16
2007-04-03 17:00         1088,58              18
2007-04-03 18:00          967,59              16
2007-04-03 19:00          868,44              16
2007-04-03 20:00          246,64               8
2007-04-03 21:00          362,85               6
2007-04-03 22:00          483,79               8
2007-04-03 23:00         4599,07              76
2007-04-04 00:00           483,8               8
2007-04-04 01:00           241,9               4
2007-04-04 02:00
2007-04-04 03:00
2007-04-04 04:00          120,95               2
2007-04-04 05:00
2007-04-04 06:00          120,98               2
2007-04-04 07:00
2007-04-04 08:00          967,59              16
2007-04-04 09:00          846,85              14
2007-04-04 10:00         1451,36              24
2007-04-04 11:00          967,59              16
2007-04-04 12:00          967,59              16

24 Zeilen ausgewählt.

Dieses Statement gibt die Größe der Archivelogs und Anzahl innerhalb der letzten 24 Stunden aus.

p.s. Falls jemand versucht das auf einer 10.2.0.2 zu testen... Ist leider ein BUG drin ;-)
Workaraound auf Session Ebene:
SQL: 
alter session set "_optimizer_connect_by_cost_based"=false;
Ist aber ein undokumentierter Parameter.
 
Danke für die schnelle Hilfe. Beide Varianten funktionieren super, jetzt hätte ich aber nur noch eine Frage.
Ich lass mir die Ausgabe des Statements in eine Datei spoolen um die Daten nacher weiter verwenden zu können.
An dieser Stelle sollte noch erwähnt werden, dass das SQL-Statement teil eines Shellprogramms ist und somit auch das Anmelden / Abmelden an sqlplus mit gespoolt wird. Gibt es eine Möglichkeit zu sagen was genau gespoolt bzw. das überflüssig nicht gespoolt werden soll?

Ausgabe des Spools im Moment, benötigt wird aber nur das rotmarkierte.:
SQL*Plus: Release 9.2.0.6.0 - Production on Wed Apr 4 14:30:10 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> SQL> SQL> 2 3 4 5 6
02-APR-07 74

SQL> SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
 
Du kannst beim Aufruf von SQL*Plus den Parameter -S mitgeben:
-S Legt den vollautomatischen Modus fest, der die Anzeige von
SQL*Plus Banner,Prompts und die Anzeige der Befehle auf dem
Bildschirm deaktiviert.
Zum Vergrößern anklicken....
Einfacher ist es aber sicher ein einfaches SQL Script zu schreiben, das per SQL*Plus auszuführen und darin das Spool zu beginnen:
Code: 
$ sqlplus test/test@mydev @test.sql
(hab atm nur eine cygwin Umgebung..) Hiermit würde sich SQL*Plus an der Datenbank anmelden und das Script test.sql ausführen. Da würde dann etwa folgendes drin stehen:
SQL: 
set pages 0
set heading off
set echo off
set feed off
set trimout on
set trims on
ALTER session SET "_optimizer_connect_by_cost_based"=false; 
spool myspool.txt
SELECT  TIMES.HOUR,
        LOGS.LOGSIZE,
        LOGS.LOGCOUNT
FROM    (
    SELECT  TO_CHAR( SYSDATE - 1 + ROWNUM / 24, 'YYYY-MM-DD HH24') || ':00' AS HOUR,
            ROWNUM AS ZEILE
    FROM    DUAL 
    CONNECT BY SYSDATE - 1 + ROWNUM / 24 <= SYSDATE        
) TIMES 
LEFT JOIN
( 
    SELECT  TO_CHAR( NEXT_TIME, 'YYYY-MM-DD HH24') || ':00' AS HOUR, 
            ROUND( SUM( BLOCKS * BLOCK_SIZE )/1024/1024, 2) AS LOGSIZE,
            COUNT( * ) AS LOGCOUNT
    FROM    V$ARCHIVED_LOG log
    WHERE   NEXT_TIME  > SYSDATE - 1
    GROUP   BY TO_CHAR( NEXT_TIME, 'YYYY-MM-DD HH24')   
) LOGS
ON  (
    TIMES.HOUR = LOGS.HOUR
)
ORDER BY TIMES.ZEILE;
spool off
exit

Demo: (hab keine Archivelogs in meiner Testdatenbank, daher nur fast leere Zeilen..)
Code: 
$ sqlplus test/test@mydev @test.sql

SQL*Plus: Release 10.2.0.3.0 - Production on Mi Apr 4 15:02:36 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Verbunden mit:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

TEST @mydev> set pages 0
TEST @mydev> set heading off
TEST @mydev> set echo off
2007-04-03 16:00
2007-04-03 17:00
2007-04-03 18:00
2007-04-03 19:00
2007-04-03 20:00
2007-04-03 21:00
2007-04-03 22:00
2007-04-03 23:00
2007-04-04 00:00
2007-04-04 01:00
2007-04-04 02:00
2007-04-04 03:00
2007-04-04 04:00
2007-04-04 05:00
2007-04-04 06:00
2007-04-04 07:00
2007-04-04 08:00
2007-04-04 09:00
2007-04-04 10:00
2007-04-04 11:00
2007-04-04 12:00
2007-04-04 13:00
2007-04-04 14:00
2007-04-04 15:00
Verbindung zu Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options beendet

$ cat myspool.txt
2007-04-03 16:00
2007-04-03 17:00
2007-04-03 18:00
2007-04-03 19:00
2007-04-03 20:00
2007-04-03 21:00
2007-04-03 22:00
2007-04-03 23:00
2007-04-04 00:00
2007-04-04 01:00
2007-04-04 02:00
2007-04-04 03:00
2007-04-04 04:00
2007-04-04 05:00
2007-04-04 06:00
2007-04-04 07:00
2007-04-04 08:00
2007-04-04 09:00
2007-04-04 10:00
2007-04-04 11:00
2007-04-04 12:00
2007-04-04 13:00
2007-04-04 14:00
2007-04-04 15:00
 
und wie wärs damit:

select SEQUENCE# Y,
count(decode(SEQUENCE#, '02-APR-07' ,SEQUENCE#)) "X"
from v$loghist
group by SEQUENCE#
having FIRST_TIME = '02-APR-07'

... ein bisschen aufwendig schaut ja bisher alles aus..:)

susmel
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück