Re: Finding AUTOEXETEND objects

From: <hacb_at_my-dejanews.com>
Date: Thu, 11 Mar 1999 13:27:04 GMT
Message-ID: <7c8gb0$tsn$1@nnrp1.dejanews.com>

In article <36E6894C.F27FED99_at_bigfoot.com>,   Doug Cowles <dcowles_at_bigfoot.com> wrote:
> I need to find things with AUTOEXTEND on.
> Oracle 7.3.3
> I also need to turn AUTOEXTEND off on these
> objects. I don't see an obvious column for
> autoextensibility. Not v$datafile, not dba_data_files,
> not dba_tablespaces.
>
> - Dc.
>
>

Try using the following report:
rem
rem auto_ext73.sql
rem List files in the database that have autoextend on. rem Only those datafiles with entries in sys.FILEXT$ rem have autoextend ON.
rem
rem Note: Until AUTOEXTEND is turned on for a file, rem sys.FILEXT$ is not created. rem
rem For version 7.3 only. For Oracle8, columns rem AUTOEXTENSIBLE, INCREMENT_BY, MAXBLOCKS rem were added to hold this information. rem
rem To turn autoextend off, appropriate syntax is:

rem     ALTER DATABASE DATAFILE 'filename'
rem       AUTOEXTEND OFF;

rem
column TABLESPACE_NAME format A20
column FILE# format 999 heading 'FILE#' column MAXEXTEND format 99999999 heading 'MAX EXT' column INC format 99999
column FILE_NAME format A30
Set Term Off
set pagesize 60

Column Instance        New_Value _Instance NoPrint
Column Today           New_Value _Today NoPrint
Column Time            New_Value _Time  NoPrint

Select Global_Name Instance
From Global_Name;

TTitle On
ttitle _Today center 'Datafiles with AUTOEXTEND ON V7.3 ' -

   right 'Page ' format 990 sql.pno skip 1 -    _Time -
   Center 'Instance Name: ' _Instance Skip 2

select to_char(sysdate, 'DD-MON-YYYY') today,

         to_char(sysdate, 'HH:MI:SS AM') time   from dual;

Set Term On
spool auto_ext73.lis
select ddf.tablespace_name,

       filext.FILE#,
       filext.MAXEXTEND,
       filext.INC,
       ddf.FILE_NAME
from sys.FILEXT$ filext,
     V$DATAFILE datafile,
     dba_data_files ddf

where datafile.FILE# = filext.FILE#
  and ddf.FILE_ID = filext.FILE#
order by ddf.tablespace_name,

       filext.FILE#
/
spool off

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Mar 11 1999 - 07:27:04 CST