Datafile's max size

[els]

I ran the following query to get the actual and max size of my datafiles:

select file_name, sum(bytes)/1024/1024 "actual size MB",
sum(maxbytes)/1024/1024 "max size MB"
from dba_data_files
group by file_name;

In the query result, I found some actual size greater than the max size (like for my rollback datafile:

Datafile : rbs1.dbf
Actual size : 300
Max size : 200

How can you get a file that exceed the max size specified during the tablespace's creation?

In the case you would need it, here's how I created the rollback datafile:
CREATE TABLESPACE ROLLBACK
DATAFILE 'rbs1.dbf' SIZE 10M REUSE AUTOEXTEND ON
NEXT 10M MAXSIZE 200M
DEFAULT STORAGE ...

Thanks.

[Edited by els on 12-14-2000 at 12:31 PM]

[Paul H]

I would think , that its because you have setup the tablespace with autoextend. By the way I tried running that query on one of my databases, but the field MAXBYTES doesnt exist, perhaps because its Oracle 7.3.4?

[DBAtrix]

Why are you doing a sum() for each file_name? There should only be one row for each file. Admittedly you should not get a bytes>maxbytes as written regardless, but try doing it without the group functions to be sure you haven't introduced some weirdness. If it is really true, you might need to call Oracle.

P.S. to Paul: In 7.3.4 you have to look in sys.filext$ for max size, i think. The units may be blocks, not bytes. you'll have to check.

D.

[jt]

This is what I actually got from 8.0.6 test database, which has very little activities in there though.

SQL> select bytes, maxbytes,maxblocks from dba_data_files;

BYTES MAXBYTES MAXBLOCKS
---------- ---------- ----------
57671680 0 0
235929600 0 0
199229440 0 0
36700160 0 0
115343360 0 0
5242880 0 0
6291456 0 0

7 rows selected.

SQL> select bytes from v$datafile;

BYTES
----------
57671680
235929600
199229440
36700160
115343360
5242880
6291456

So why all the zeros for maxbytes and maxblocks?

[els]

You are right DBAtrix, I didn't have to sum, so I get rid of it in my query, and I'm getting the same result as with the sum. So I have no idea why I'm getting an actual size bigger than the datafile's limit (MAXSIZE).

Thanks anyway (but still open to other opinions)

[pwoneill]

just for kicks can you show the output of

select bytes, blocks, maxbytes, maxblocks from dba_datafiles where file_name = 'file_in_question'?

[els]

As requested, here's the result:

BYTES: 314572800
BLOCKS: 38400
MAXBYTES: 209715200
MAXBLOCKS: 25600

I can see that the number of blocks did not reach the maxblocks, but the numbers of bytes did reach the maxbytes. How come???

[marist89]

Are you sure somebody didn't futz with the parameters for that datafile? You can set the size of a datafile and then set an autoextend value less than the datafile size. In addition, you can manually resize past the maxsize parameter.
--
For example, you have a datafile (/u01/somefile.dbf) that is 32M. If somebody issues:
alter database datafile '/u01/somefile.dbf' autoextend on next 4M maxsize 16M
you will get entries in dba_data_files similar to what you are seeing.
--
example 2: You have a 32M datafile (/u01/somefile.dbf) that is 32M and has autoextend on, next 32M and maxsize of 64M. You can issue:
alter database datafile '/u01/somefile.dbf' resize 64M
and get similar results.

[els]

Thanks Jeff for the clarifications. I'm not the bda who's created that database, so ...