RE: Controlling disk space usage

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Wed, 26 Sep 2001 20:15:29 -0700
Message-ID: <F001.0039A1FD.20010926201521@fatcity.com>

An improved version that allows for some files not being set to autoextend. For these, it treats the max bytes as being equal to the current bytes.

select

   substr ( file_name ,1,1) disk,
   sum (bytes / 1024/1024) curr_mb ,
   sum ( DECODE ( maxbytes , 0 , bytes , maxbytes ) / 1024/1024) max_mb from

dba_data_files
group by

substr ( file_name ,1,1)
;

Note - only tested on NT and on Oracle 805 and 817.

Regards,
Bruce Reardon

-----Original Message-----
Sent: Thursday, 27 September 2001 12:40

Sean,

We use Oracle on NT with autoextend.

We also have automated disk space remaining monitoring with notification to mobile phones.

However, I still configure the maxsize such that the sum of the maxsize on each disk is less than the available space on the disk.

Consider the following script as a simple way of measuring current and maximum per disk (if only it was easy to work out disk size within Oracle):

space_by_disk.sql
27-Sep-2001 , Bruce Reardon : Created.
Assumptions
That DB is on NT as uses first character of filename to group by disk name.

COLUMN curr_mb FORMAT 999,990.9
COLUMN max_mb FORMAT 999,990.9

select

   substr ( file_name ,1,1) disk,
   sum (bytes / 1024/1024) curr_mb ,
   sum (maxbytes / 1024/1024) max_mb
from

dba_data_files
group by

substr ( file_name ,1,1)
;

Regards,
Bruce Reardon

-----Original Message-----
Sent: Thursday, 27 September 2001 1:56

Sean,
I assume from your question that you are already using autoextend on your tablespaces.
I personally do not use that option and therefore the chance of a tablespace expanding above available disk space never occurs. However in the event of my running a massive insert or import when I was not sure how much space would be used I might use autoextend until the creation was over and then turn autoextend off
John
-----Original Message-----
Sent: 26 September 01 15:15

Wondering what folk out there do to manage disk space usage by tablespaces. Is it common/bad practice to have MAXSIZE unlimited and/or MAX_EXENTS set to

a value which if reached would be larger than disk capacity and therefore regular observation of space is required, or is there a better way to do this?

Sean :)
Data Base Administrator
Oracle 7.3.3, 8.0.5, 8.1.7 - NT, W2K
[0%] OCP Oracle8i DBA ---> End 2002 deadline =:-O [0%] OCP Oracle9i DBA

-------------------------------- ------------

Organon (Ireland) Ltd.
E-mail: sean.oneill_at_organon.ie [subscribed: Digest Mode] Visit: http://groups.yahoo.com/group/Oracle-OCP-DBA "Nobody loves me but my mother... and she could be jivin' too." - BB King
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Reardon, Bruce (CALBBAY)
INET: Bruce.Reardon_at_comalco.riotinto.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Sep 26 2001 - 22:15:29 CDT