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: [EMAIL PROTECTED] [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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
