| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: MAXBYTES
Sol,
It is absolutely possible to have MAXBYTES be less than BYTES for a data file. Doing so though is equal to turning auto extend off. What you really want to do is add the difference of maxbytes-bytes to sum(bytes) from dba_free_space to see the total amount of free space available to a tablespace, that is assuming uniform extents otherwise this is error prone. Biggest headache occurs when the maxbytes for a data file - bytes exceeds the available disk space for the mount point. Oracle does not check that.=20
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: sol beach [mailto:sol.beach_at_gmail.com]=20
Sent: Thursday, November 18, 2004 11:59 AM
To: oracle-l_at_freelists.org
Subject: MAXBYTES
The Fine Manual states -
"MAXBYTES NUMBER Maximum file size in bytes"
however as shown below the first file of my SYSTEM tablespace
is about 2GB while MAXBYTES is at about 1GB.
I find this strange & slightly confusing.
The problem I really hope to solve is that I have inherited some
Oracle DBs where
all the tablespaces are set to AUTOEXTEND.
I want to be able to be warned well ahead of time when a tablespace is
"getting full"
and soon will fail to successfully autoextend.
AFAIK, all the tablespaces do have a value in MAXBYTES (for at least 1
of the files in the tablespace).
If anyone has any SQL or PL/SQL that might provide the basis for an
early wanring script,
I'd greatly appreciate you sending it to me, pointing me at a URL, or
posting back to this list.
TIA & HAND!
1 select bytes,maxbytes, tablespace_name, file_name
2 from dba_data_files
3 where tablespace_name in ( select tablespace_name from (select
tablespace_name, count(file_name)
4 from dba_data_files 5 where bytes > 1800000000 6 and tablespace_name not like 'UNDO%' 7 having count(file_name) > 1 8 group by tablespace_name 9 ))
BYTES MAXBYTES TABLESPACE_NAME FILE_NAME
---------- ---------- ------------------------------
------------------------------------------------
173146112 2097152000 EVENT_DATA =20
/b/oradata/cdb1/event_data_01.dbf
2097152000 0 EVENT_DATA =20
/b/oradata/cdb1/event_data_02.dbf
2097152000 0 EVENT_DATA =20
/b/oradata/cdb1/event_data_03.dbf
2097152000 0 EVENT_DATA =20
/b/oradata/cdb1/event_data_04.dbf
2097152000 0 EVENT_DATA =20
/b/oradata/cdb1/event_data_08.dbf
2097152000 0 EVENT_DATA =20
/b/oradata/cdb1/event_data_10.dbf
2097152000 0 EVENT_DATA =20
/b/oradata/cdb1/event_data_12.dbf
188874752 0 EVENT_DATA =20
/b/oradata/cdb1/event_data_13.dbf
2097152000 0 EVENT_DATA =20
/b/oradata/cdb1/event_data_11.dbf
2097152000 0 EVENT_DATA =20
/b/oradata/cdb1/event_data_09.dbf
2097152000 0 EVENT_DATA =20
/b/oradata/cdb1/event_data_07.dbf
2097152000 0 EVENT_DATA =20
/b/oradata/cdb1/event_data_06.dbf
2097152000 0 EVENT_DATA =20
/b/oradata/cdb1/event_data_05.dbf
2146435072 1048576000 SYSTEM =20
/b/oradata/cdb1/system01.dbf
2097152000 0 SYSTEM =20
15 rows selected.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 18 2004 - 12:24:28 CST
 |
 |