Re: Maxbytes sifgnification

On Jan 23, 8:54 am, teddyber <teddy..._at_gmail.com> wrote:
> On 23 jan, 15:48, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > On Jan 23, 8:45 am, teddyber <teddy..._at_gmail.com> wrote:
>
> > > On 23 jan, 15:31, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > > On Jan 23, 8:12 am, teddyber <teddy..._at_gmail.com> wrote:
>
> > > > > On 23 jan, 15:01, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > > > > Comments embedded.
> > > > > > On Jan 23, 7:25 am, teddyber <teddy..._at_gmail.com> wrote:
>
> > > > > > > Hello,
>
> > > > > > > I understand what this MAXBYTES column (DBA_DATA_FILES) means for an
> > > > > > > autoextensible datafile. What I don't know is the meaning of this
> > > > > > > column when the datafile is NOT autoextensible.
>
> > > > > > It's the current size of the datafile.
>
> > > > > not exactly. Actually I fell upon this question because I had (and
> > > > > still have!) different values for BYTES and MAXBYTES columns for
> > > > > datafiles not autoextensible.
> > > > > BYTES is the size of the file on the filesystem.
>
> > > > > > > Also, how is this
> > > > > > > value initialized/changed by Oracle for this kind of datafile?
>
> > > > > > alter database datafile '.......' resize [some number here];
>
> > > > > this only affects the filesize and the changes the BYTES column on my
> > > > > server...
>
> > > > > > which sets the maxbytes to the newly adjusted size.
>
> > > > > > > I can't find any clue in Oracle doc or groups or any Oracle related-
> > > > > > > page. Anyone can tell?
> > > > > > > Thanks.
>
> > > > > > David Fitzjarrell- Hide quoted text -
>
> > > > > - Show quoted text -
>
> > > > Looking at 10.2.0.3 I see the following:
>
> > > > FILE_NAME                                               BYTES
> > > > MAXBYTES
> > > > -------------------------------------------------- ----------
> > > > ----------
> > > > /d09/oradata/********/system01.dbf                  367001600
> > > > 0
>
> > > > So I stand corrected (teaches me to answer from memory).  MAXBYTES is
> > > > 0 which indicates the file is not autoextensible.  It won't change
> > > > until you activate autoextend for that file.
>
> > > > My apologies for answering without the aid of investigation.
>
> > > > David Fitzjarrell
>
> > > no problem! I'm still puzzled since i do not have a zero value for
> > > maxbytes but 10Go or 32Go or the same as the BYTES column for
> > > tablespaces where autoextend is off.
> > > I also cannot spot the difference between those tablespaces !
> > > (creation method, modifications done, etc.).- Hide quoted text -
>
> > > - Show quoted text -
>
> > It would be nice if  you'd see your way clear to mention the Oracle
> > release you're using.  And an example of what you're talking about
> > Without that information any response to your situation is merely
> > guesswork.
>
> > David Fitzjarrell
>
> sure. thanks for your help!
> Oracle version is 9.2.0.1.0
>
> here is some info on my tablespaces:
>
> SQL> set pages 100
> SQL> set lines 140
> SQL> col tablespace format a30
> SQL> col bl_kb format 99
> SQL> col management format a10
> SQL> col segment format a6
> SQL> col contents format a9
> SQL> col status format a7
> SQL> col size_mb format 999,999
> SQL> col max_mb format 999,999
> SQL> col nfile format 9999
> SQL> col perfree format 999.99
> SQL> col real_free_mb format 999,999
> SQL> select t.tablespace_name tablespace,
>   2  t.block_size/1024 bl_kb,
>   3  t.extent_management management,
>   4  t.segment_space_management segment,
>   5  t.contents contents ,
>   6  t.status,
>   7  f.nfile,
>   8  f.size_mb,
>   9  f.max_mb,
>  10  round((f.max_mb-(f.size_mb-nvl(fs.free_mb,0)))
>  11  /f.max_mb*100,2) perfree,
>  12  f.max_mb-(f.size_mb-nvl(fs.free_mb,0)) real_free_mb
>  13  from dba_tablespaces t,
>  14  (select tablespace_name,
>  15  round(sum(bytes)/1024/1024) size_mb,
>  16  round(sum(
>  17  case when autoextensible='NO' then bytes
>  18  else greatest(bytes,maxbytes) end)
>  19  /1024/1024) max_mb,
>  20  count(file_id) nfile
>  21  from dba_data_files
>  22  group by tablespace_name) f,
>  23  (select tablespace_name,
>  24  round(sum(bytes)/1024/1024) free_mb
>  25  from dba_free_space
>  26  group by tablespace_name) fs
>  27  where t.tablespace_name=f.tablespace_name
>  28  and t.tablespace_name=fs.tablespace_name(+)
>  29  order by perfree;
>
> TABLESPACE                     BL_KB MANAGEMENT SEGMEN CONTENTS
> STATUS  NFILE  SIZE_MB   MAX_MB PERFREE
> REAL_FREE_MB
> ------------------------------ ----- ---------- ------ ---------
> ------- ----- -------- -------- -------
> ------------
> V12                                8 LOCAL      MANUAL PERMANENT
> ONLINE      1    1,350   32,767   95.91
> 31,427
> MSG_AUTRE_TS_FEVRIER               8 LOCAL      MANUAL PERMANENT
> ONLINE      1    1,024   10,240   97.55
> 9,989
> MSG_AUTRE_TS_MARS                  8 LOCAL      MANUAL PERMANENT
> ONLINE      1    1,024   10,240   99.38
> 10,176
> MSG_AUTRE_TS_JANVIER               8 LOCAL      MANUAL PERMANENT
> ONLINE      1    1,024   10,240   99.67
> 10,206
> TS_NJUE_TRIO_DATA01                8 LOCAL      MANUAL PERMANENT
> ONLINE      1      100    2,000   99.85
> 1,997
> TRACE_TS_JANVIER                   8 LOCAL      MANUAL PERMANENT
> ONLINE      1      110   10,240   99.91
> 10,231
> IMSG_COMMUN_TS_FEVRIER             8 LOCAL      MANUAL PERMANENT
> ONLINE      1      230   10,240   99.92
> 10,232
> ITRACE_TS_JANVIER                  8 LOCAL      MANUAL PERMANENT
> ONLINE      1      165   10,240   99.94
> 10,234
> MSG_COMMUN_TS_FEVRIER              8 LOCAL      MANUAL PERMANENT
> ONLINE      1      205   10,240   99.94
> 10,234
> MSG_AUTRE_TS_DECEMBRE              8 LOCAL      MANUAL PERMANENT
> ONLINE      1    1,024   10,240   99.95
> 10,235
> DATA_SERIE_TS_JANVIER              8 LOCAL      MANUAL PERMANENT
> ONLINE      1    1,024   10,240   99.96
> 10,236
> ITRACE_TS_FEVRIER                  8 LOCAL      MANUAL PERMANENT
> ONLINE      1      165   10,240   99.96
> 10,236
> TRACE_TS_FEVRIER                   8 LOCAL      MANUAL PERMANENT
> ONLINE      1      110   10,240   99.96
> 10,236
> MSG_COMMUN_TS_JANVIER              8 LOCAL      MANUAL PERMANENT
> ONLINE      1      205   10,240   99.96
> 10,236
> IMSG_COMMUN_TS_MARS                8 LOCAL      MANUAL PERMANENT
> ONLINE      1      230   10,240   99.96
> 10,236
> -- snip --- Hide quoted text -
>
> - Show quoted text -

My first thought is for you to patch to the most current level for 9.2.0 which would be 9.2.0.8. Knowing you're on 9.2.0 I find, upon querying dba_data_files the exact same output I found with 10.2.0.3: MAXBYTES is 0 for tablespaces not set to autoextend.

I notice you don't post the results of the AUTOEXTEND column for these tablespaces; please do so and also post the tablespace creation commands (if you have them).

David Fitzjarrell Received on Wed Jan 23 2008 - 09:31:39 CST