RE: tricky sql report...
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <bobmetelsky@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 5 Feb 2005 13:26:30 +0100
You might start thinking along these lines:
SQL> select month
2 , sum(decode(...)) as DB1
3 , sum(decode(...)) as DB2
4 , sum(decode(...)) as DB3
5 , sum(...) as COMBINED
6 from db_space
7 group by month;
This only works if the list of databases is indeed hard-coded.
Hope this helps, kind regards,
Lex.
----------------------------------------------------------------
Tom Kyte Seminar: http://www.naturaljoin.nl/events/seminars.html
----------------------------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Bob Metelsky
Sent: Saturday, February 05, 2005 12:57
To: oracle-l@xxxxxxxxxxxxx
Subject: tricky sql report...
All, Im doing an inventory report to provide monthly summaries of space
being used by databases in the envioriments.
I have a reporting table with the following columns
MONTH, SCHEMA , SPACE_USED, SPACE_FREE
This table will be populated by a sql script ran against a hard coded list
of databases 1x per month
the rub is Id like the report to look like this
DB1, DB2, DB3, COMBINED
JAN 22 ,10,20, 52
FEB 1, 2, 3, 6
MAR 1, 1, 1, 3
The sql to populate the reporting table
create table DB_SPACE
(MONTH date,
SCHEMA varchar(10),
USED_MB varchar(16),
FREE_SPACE_MB varchar(16))
/
insert into DB_SPACE
SELECT sysdate MONTH,
SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA', 8) SCHEMA,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024,0))
USED_MB,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) FREE_SPACE_MB
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes;
I know this can be done but havent had success goggling for this sort of
query
Thanks!
bob
--
"Oracle error messages being what they are, do not highlight the correct
cause of fault, but will identify some other error located close to where
the real fault lies."
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
