Re: Maxbytes sifgnification
Date: Wed, 23 Jan 2008 06:54:09 -0800 (PST)
Message-ID: <ad955190-61ca-48f3-b16a-92b679318796@e10g2000prf.googlegroups.com>
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 CONTENTSSTATUS 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 -- Received on Wed Jan 23 2008 - 08:54:09 CST