Re: Maxbytes sifgnification

On 23 jan, 16:56, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Jan 23, 9:46 am, teddyber <teddy..._at_gmail.com> wrote:
>
>
>
> > On 23 jan, 16:31, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > 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).
>
> > AUTOEXTEND is for datafiles, not tablespaces. And it is set to NO for
> > all datafiles.
>
> > tablespaces are created with a sh script (here snipped a lot as 168
> > tablesspaces are created!) :
>
> > ORACLE_SID=MYSID
> > export ORACLE_SID
>
> > TS_TRACE_KEY_VALUE_DATA=/home/oracle/oracle9i/oradata/MYSID
> > SZ_VAL_KEY_02=170M
>
> > sqlplus system/system <<!
> > spool cr_tablespace_data.log
> > create tablespace TRACE_TS_FEVRIER datafile '$TS_TRACE_KEY_VALUE_DATA/
> > TRACE_FEVRIER.dbf' size $SZ_VAL_KEY_02;
> > ...
>
> > spool off
> > !
>
> > > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -
>
> And a datafile is associated with a tablespace, and, yes, it's the
> DATAFILE which is set to autoextend, but the question still remains
> that this query is returning data as though these DATAFILES are set to
> autoextend:
>
> 16 round(sum(
> 17 case when autoextensible='NO' then bytes
> 18 else greatest(bytes,maxbytes) end)
> 19 /1024/1024) max_mb,
>
> Have you checked the actual value of AUTOEXETNSIBLE in
> DBA_DATA_FILES? Could it possibly be that it isn't 'NO' but 'NO '?
>
> select ''''||autoextensible||''''
> from dba_data_files;
>
> Something in this data is causing that CASE statement to execute as
> though AUTOEXTENSIBLE is set to 'YES'.
>
> David Fitzjarrell

OMG! you're right. I don't have direct access to the production environment but I fear our hoster did set datafiles to autoexted without telling us... I just asked them, answer will come back shortly. As you can see in my previous post, tablespaces are created at the same time as datafiles with no autoextend. I guess their DBA changed something... I'll tell you. Thanks for pointing this! Received on Wed Jan 23 2008 - 10:08:55 CST