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: joel garry <>
Date: Mon, 18 Jun 2007 10:32:47 -0700
Message-ID: <>

On Jun 18, 9: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 don't think there is a general one-button approach, since the definition of space used is so highly variable.

You have to define whether you include backups, how many, archives, exports, whether you are going to include flashback space, how much empty space internal to tablespaces (which can change, think of the case where you do an exp/imp on a TS that has densely packed blocks - or sparse blocks), etc. A DW might tolerate much higher packing and less backup than an OLTP.

I think this is at least partly why the more recent versions of Oracle just throw everything into a huge bin and you just use as much as you have hardware for.

Once you have your own requirements defined it's not that hard to sum it all up. You might just use the html formatting capabilities of SQL on each box, leaving the output in some specific directory, rather than worrying about the maintenance of unioning a lot of different databases that might not be up - thinking about that makes the "intelligent agent" concept make sense. But that's a site specific decision. I thought OEM or grid or something already had it, but I haven't looked. Probably thinking of which sure isn't generalized.

In the O7/O8 days I used to write spiders that would go around and grab the info, but that wouldn't be a good thing given modern security practices.


-- is bogus.  "If it is at all possible, I would suggest we do
so in conjunction with the RIF (Reduction in Force) - rather than
telling him it is a result of his misdeeds. Let him think that he
remained anonymous, but that he just got caught up in the numbers
game. 'We're terribly sorry. We love you to death. Here is the door.'
" -
Received on Mon Jun 18 2007 - 12:32:47 CDT

Original text of this message