Oracle table autoextend

For Oracle support & training call (800) 766-1884

Oracle Tips

Donald K. Burleson

select
       tablespace_name,
       autoextend ,
       round ((total_space / 1024 / 1024), 2) as
       total_space,
       round ((total_free_space /
       1024 / 1024), 2) as total_free,
       round (((total_space - total_free_space) /
       1024 / 1024), 2) as used_space,
       to_char (
          nvl (
             round (
                (100 *
                    sum_free_blocks /
                    sum_alloc_blocks),2),0)) || '%'
             as pct_free
from (select
. . . .          dba_data_files
         group by tablespace_name),
       (select
               b.tablespace_name fs_ts_name,
               nvl (sum (bytes), 0) as total_free_space,
               sum (blocks) as sum_free_blocks
          from
               dba_free_space a, dba_tablespaces b
         where
               a.tablespace_name (+) = b.tablespace_name
         group by b.tablespace_name, status)
where
       tablespace_name = fs_ts_name
union all
. . . .

contents like 'TEMPORARY'
order by 1;

While the queries above will let you know if a tablespace has autoextend enabled, it will not tell which datafile, if the tablespace has multiple datafiles. For that, you will need the datafileae.sql script, which will work for all Oracle versions:

See Code depot for complete script

datafileae.sql

select

. . . .

       dba_data_files b,
       sys.filext$ c
where
       c.file# (+)= b.file_id
order by

2, 1;

The above is an excerpt from Oracle Performance Troubleshooting by Robin Schumacher.

It's only $19.95 and you can order it and get instant access to the Oracle scripts here:

http://www.rampant-books.com/book_2003_1_perf.htm

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot.

Fast-Track Oracle Support
PO Box 511
Kittrell, NC 27544

Email BC: