RE: MAXBYTES
- From: "Goulet, Dick" <DGoulet@xxxxxxxx>
- To: <sol.beach@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 18 Nov 2004 12:54:57 -0500
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@xxxxxxxxx]=20
Sent: Thursday, November 18, 2004 11:59 AM
To: oracle-l@xxxxxxxxxxxxx
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 ))
10* --and ((bytes/maxbytes)*100) > 75
SQL> /
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
/b/oradata/cdb1/system02.dbf
15 rows selected.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
