Re: what all physical space allocated to my DB's

"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:<3b0997b8_at_news.iprimus.com.au>...
> "Sunil Dua" <sdua_at_solutionsny.com> wrote in message
> news:bc03ebd5.0105211350.4623a2de_at_posting.google.com...
> > Thanks for the reply... I appreciate the information provided was
> > really helpful.. now I could work out what all space my database files
> > are taking..my second query was how do i make sure if the space
> > allocated is sufficient or do I need to take any preventive steps to
> > avoid DB breakdown.
> >
>
> Er.... so, how much free space have you got then? 2K, 2Mb, 200Mb, 2Gb,
> 200Tb???? And are you inserting 1 row a day, or 1 million????It's a bit
> difficult to advise given the paucity of information you provide.
>
> If you are not entirely sure what you are doing, investigate the
> 'autoextend' option for your data files. That will allow all data files to
> grow in size whenever needed. Of course, performance on the database
> suffers as the autoextension takes place, but at least your database won't
> keel over entirely. You can allow autoextensible files to take over the
> whole hard disk, or you can artificially restrict them with a 'maxsize'
> clause.
>
> Naturally, good DBA technique rather suggests that you proactively monitor
> your file growth, and respond appropriately, and autoextend is thus not a
> technique I'd normally recommend. But as quick fixes go, it will do the
> job.
>
> Regards
> HJR
>
>
>
>
>
>
>
>
>
>
>
> > thanks & Regards
> > Sunil
> >
> >
> > "Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
 news:3B092137.74F7_at_yahoo.com...
> > > Sunil Dua wrote:
> > > >
> > > > I have my Oracle database 8.1.5 installed on a sun solaris machine
> > > > some time ago... I just want to find out what all physical space
> > > > allocated to the databases installed on it and wether they have
> > > > sufficient space to continue ... I'll appreciate if you could guide me
> > > > a bit.
> > > >
> > > > thanks a lot.
> > > >
> > > > Regards
> > > > Sunil
> > >
> > > select * from v$datafile;
> > > select * from v$controlfile;
> > > select * from v$logfile;
> > > select * from v$tempfile;
> > >
> > > hth
> > > connor
> > > --
> > > ===========================================
> > > Connor McDonald
> > > http://www.oracledba.co.uk (mirrored at
> > > http://www.oradba.freeserve.co.uk)
> > >
> > > "Some days you're the pigeon, some days you're the statue"
> > >
 

> > > sdua_at_solutionsny.com (Sunil Dua) wrote in message
> > > news:<bc03ebd5.0105210936.70ac71f5_at_posting.google.com>...
> > > I have my Oracle database 8.1.5 installed on a sun solaris machine
> > > some time ago... I just want to find out what all physical space
> > > allocated to the databases installed on it and wether they have
> > > sufficient space to continue ... I'll appreciate if you could guide me
> > > a bit.
> > >
> > >
> > > thanks a lot.
> > >
> > > Regards
> > > Sunil

Sunil

this is one of the better queries thro which you can find out the free and the used space.

select name, free.mb free_mb, used.mb used_mb from
v$tablespace t,
(select tablespace_name,sum(bytes)/1024/1024 mb from  dba_free_space
 group by tablespace_name) free,
(select tablespace_name,sum(bytes)/1024/1024 mb from  dba_extents
 group by tablespace_name) used
where
t.name = free.tablespace_name (+) and
t.name = used.tablespace_name (+)
/

2.
To determine if the space allocated is enough or not you may have to undergo few steps in Capacity planning.

Build a staging machine and create the tables and indexes with no data. Analyze the table and indexes and take the statistics and compile it in xl sheet. Add one record to each table and analyze the tables and indexes and record the statistics. Add 100 or 1000 records to all the tables and once again analyze the tables and indexes and record the stats. This will give
you the approximate growth for 100 / 1000 records, now you need to find out
how much records you are going to store in a month and derive the size accordingly.

You can also use the sql I have provided after everystage to find out the free and used space.

Hope this helps.

Balaji Received on Mon May 21 2001 - 20:40:03 CDT