||Oracle Tips by Burleson
Chapter 6 Oracle Tablespace and File
only use the default storage for minor tables.
Ideally, application developers will estimate the table size
To avoid fragmentation issues altogether, use
the fixed-size extent model for tablespaces. The fixed-size extent
model creates initial extent sizes for tablespace objects that are
multiples of a fixed default value, and subsequent extent sizes that
are equal to the default extent value for the tablespace. With
fixed-extent, tablespaces are sized according to the needs of the
application, usually a small, medium, and large extent. The
fixed-size model permits extents that are released due to table or
index maintenance to be used by any other object in the table. By
allowing freed-extent reuse, fragmentation is no longer a concern in
fixed-extent-size model tablespaces.
In Oracle8i and later releases consider using
the locally managed tablespace option that uses a bitmap to control
extent allocation and deallocation. The bitmap is located locally to
the tablespace thus avoiding the I/O overhead of the dictionary
managed tablespace uet$ and fet$ dictionary table operations. The
LMT can use auto allocated or uniform allocated extent sizes.
If the autoextend feature is used, database
autoextend status and data file locations should be monitored, in
addition to space usage and fragmentation monitoring.
dba_file_data.sql shows SQL that will create a view monitoring
autoextend data for pre-Oracle8. In pre-Oracle8 versions, the only
way to get this information is to query the SYS table FILEXT$,
which, unfortunately, looks like this:
The above text is
an excerpt from:
Ault’s Oracle Internals Monitoring & Tuning Scripts
Written by the world's top DBA and author
of over 20 Oracle books, Mike Ault shares his secrets for Oracle
DBA management scripts. This handy reference is indispensable
for all Oracle professionals who must quickly automate and
manage their Oracle databases with scripts.
This stellar book comes complete with an
online code depot full of over 200 pre-tested Oracle DBA
scripts, and covers all areas of Oracle database administration
including data files, tablespace, table and index management
scripts. Based on more than 20 years full-time experience, Mike
Ault brings his expertise to the public with this fantastic
book. Your time savings from a single script is worth the price
of this great book.
Mike Ault is one of the
leading names in Oracle technology. The author of 20
books and hundreds of articles in national publications,
Mike was the first popular Oracle author with his
landmark book "Oracle7 Administration and Management".
Mike also wrote several of the "Exam Cram" books, and
enjoys a reputation as a leading author and Oracle
working with computers in 1979 right out of a stint in
the Nuclear Navy. He began working with Oracle in 1990
and has since become a World Renowned Oracle expert (in
the words of his publisher anyway!) Mike has written 14
Oracle related books, dozens of articles, and has
presented at conferences in Europe and the USA. Mike has
two wonderful daughters and is kept out of trouble by
his wife of 29 years, Susan.
See the Oracle script collection::