On Jun 19, 12:42 am, GS <G..._at_GS.com> 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.
- Table -----
CREATE TABLE TABLESPACE_HISTORY
(
RUN_DATE DATE,
TABLESPACE_NAME VARCHAR2(64 BYTE),
TOTALSPACE NUMBER,
FREESPACE NUMBER
)
TABLESPACE TOOLS
;
and the code that runs.
INSERT INTO cng.tablespace_history
SELECT TRUNC (SYSDATE) run_date, d.tablespace_name, d.totalspace,
f.freespace
FROM (SELECT tablespace_name, SUM (BYTES / 1048576)
totalspace
FROM SYS.DBA_DATA_FILES
GROUP BY tablespace_name) d,
(SELECT tablespace_name, TRUNC (SUM (BYTES / 1048576))
freespace
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
