From oracle-l-bounce@freelists.org Sat Feb 5 06:59:22 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j15CxM7r008477 for ; Sat, 5 Feb 2005 06:59:22 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j15CxMem008473 for ; Sat, 5 Feb 2005 06:59:22 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 90A086CC3D; Sat, 5 Feb 2005 06:58:23 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 10704-02; Sat, 5 Feb 2005 06:58:23 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 106526D08C; Sat, 5 Feb 2005 06:58:23 -0500 (EST) Message-ID: <4204B472.3060705@comcast.net> Date: Sat, 05 Feb 2005 06:56:34 -0500 From: Bob Metelsky Organization: Shoreline Resources User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.3) Gecko/20040910 X-Accept-Language: en-us, en MIME-Version: 1.0 To: oracle-l@freelists.org Subject: tricky sql report... Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-archive-position: 15817 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: bobmetelsky@comcast.net Precedence: normal Reply-To: bobmetelsky@comcast.net X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL,LINES_OF_YELLING autolearn=ham version=2.60 X-Spam-Level: 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." -- http://www.freelists.org/webpage/oracle-l