Datafile's max size
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Datafile's max size

  1. #1
    Join Date
    Dec 2000
    Posts
    16
    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]

  2. #2
    Join Date
    Oct 2000
    Posts
    57
    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?

  3. #3
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    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.


  4. #4
    Join Date
    Oct 2000
    Posts
    76
    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?

    J.T.

  5. #5
    Join Date
    Dec 2000
    Posts
    16

    Same result with or without "sum"

    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)

  6. #6
    Join Date
    Jun 2000
    Posts
    417
    just for kicks can you show the output of

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

  7. #7
    Join Date
    Dec 2000
    Posts
    16
    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???

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Dec 2000
    Posts
    16
    Thanks Jeff for the clarifications. I'm not the bda who's created that database, so ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width