Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: How do you folks keep track of your total Oracle space useage?

Re: How do you folks keep track of your total Oracle space useage?

From: <>
Date: Mon, 18 Jun 2007 22:32:34 -0000
Message-ID: <>

On Jun 19, 12:42 am, GS <> wrote:
> Kind of an odd question, I know..
> A couple of times a year I get asked for various reasons how much total
> disk space are all of our databases taking up. Usually I have a ball
> park figure just going from memory, and at times just scroll through the
> database storage tabs in OEM or run "select sum(bytes)/1024/1024 from
> dba_data_files;" on each database and tabulate the results manually.
> With our SAN environment going through many changes and new off site
> backup strategies being looked at I am thinking I should have a "one
> button" approach to this where I can have this result with a moments
> notice. AFAIK there is no tool to do just this, none that I am aware of
> anyway. OEM would be the obvious place to have this, since all the
> username/pwds can be stored as preferred credentials. I was also
> thinking of just taking one of my sandbox db's and create dblinks to
> each and every database then running the above SQL on each db with many
> UNION clauses and using sql formatting to sum the totals. A bit
> cumbersome to set up but would do the trick.
> Anyone think of a better way? Or IS there a tool or bit of freeware out
> there that does just this?
> thanks!

I've got a simple table which stores the tablespace size and freespace, date and tablespace name and then a job that just populates it once a day. then you can extract the data and do funky graphs. Management seem to love that sort of thing. It doesn't take into account what others have said about arch logs, backups etc.

 INSERT INTO cng.tablespace_history
   SELECT TRUNC (SYSDATE) run_date, d.tablespace_name, d.totalspace,
       FROM (SELECT   tablespace_name, SUM (BYTES / 1048576)
                 FROM SYS.DBA_DATA_FILES
             GROUP BY tablespace_name) d,
            (SELECT   tablespace_name, TRUNC (SUM (BYTES / 1048576))
                 FROM DBA_FREE_SPACE
             GROUP BY tablespace_name) f
      WHERE d.tablespace_name = f.tablespace_name;

Received on Mon Jun 18 2007 - 17:32:34 CDT

Original text of this message