Re: AUTOEXTEND question

From: Michel Cadot <micadot_at_netcourrier.com>
Date: 2000/05/25
Message-ID: <8gjec6$rjt$1@s2.feed.news.oleane.net>#1/1

answer embedded.

Syltrem <syltrem_at_videotron.ca> a écrit dans le message : 5IaX4.811$p7.7098_at_wagner.videotron.net...
> Hello!
>
> Is there a way to know if a datafile AUTOEXTEND option is ON?

You have information on autoextend in sys.filext$ table:

v734> select * from sys.filext$;

     FILE# MAXEXTEND INC
---------- ---------- ----------

         1    4194302       5120
         2      25600       2560
         3      25600       2560
         4    4194302       5120
         5    4194302       4096

5 rows selected.

You can join with v$dbfile to get the name of the file instead of its id:

v734> select substr(b.name,1,30) "Filename",

  2         nvl(to_char(a.maxextend, 9999999999999), 'AUTOEXTEND OFF') "Max Extend",
  3         nvl(to_char(a.inc, 9999999999999), 'AUTOEXTEND OFF') "Increment"

  4 from sys.filext$ a, v$dbfile b
  5 where b.file# = a.file# (+)
  6 order by b.name;

Filename                       Max Extend     Increment
------------------------------ -------------- --------------
C:\ORACLE\ORA1\TS_D0101.ORA           4194302           5120
C:\ORACLE\ORA1\TS_I0101.ORA           4194302           4096
C:\ORACLE\ORA1\TS_ORACLE01     AUTOEXTEND OFF AUTOEXTEND OFF
C:\ORACLE\ORA1\TS_R0101.ORA             25600           2560
C:\ORACLE\ORA1\TS_S0101.ORA           4194302           5120
C:\ORACLE\ORA1\TS_T0101.ORA             25600           2560

6 rows selected.

>
> Also, is it possible that even though autoextend is on, I end up getting the
> message
> ORA-1652: unable to extend temp segment by 10070 in tablespace TEMP

Maybe your file system or your disk is full or maybe you reached the maxextend of your datafile.

>
> The fact that the tablespace has grown a lot (I don't yet know what the
> programmers do in there) makes me think that autoextend was on, but with
> this message I don't know anymore, and I can't check to make sure.
>
> Thanks!
>

--
Have a nice day
Michel

Received on Thu May 25 2000 - 00:00:00 CDT