Extents, Blocks, etc. - can anyone point me to help?
Newsgroups: comp.databases.oracle.server
References: <3401FF3C.64F@utopia.com>
Organization: InterAccess, Co. - Chicagoland's Full Service Internet Provider
Reply-To: akaplan@interaccess.com
Distribution:
Simon,
A great explanation is in the Oracle "Concepts Guide". Basically, a table
(or index or cluster) is stored within a tablespace. A tablespace is a
collection of one or more datafiles, which are physical files on the
computer's disks. To find which tablespaces you have, type:
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;
To find out which data files you have (and their locations), type:
SELECT FILE_NAME, TABLESPACE_NAME, BLOCKS FROM DBA_DATA_FILES;
The "BLOCKS" column in the DBA_DATA_FILES shows how large the data file is
in BLOCKS. How many bytes the BLOCKS have depends on how the database was
created. To find out, type:
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size';
Within each BLOCK is header information, actual data, and space reserved
for data growth. A table is made up of several blocks. For instance, if a
table is 40k in size, and a block is 4k in size, this means that a table
is stored in 10 blocks.
Extents are simply a portion of a table (or index or cluster). The reason
they are important is that Oracle pre-allocates tables/indexes/clusters.
For example, when you create a table, you tell Oracle that initially 40K
should be allocated for the table. Oracle will create the table, grabbing
40K (10 blocks in length for a 4k block size). Even if there is one record
in the table, 40k will be allocated. The more records you add, the more of
the 40k will be filled. Once 40k worth of data is in the table, Oracle
grabs another EXTENT and reserves it for exclusive use by the table. The
size of this next extent may be different than the size of the first
extent.
Hope this helped!
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 105+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Simon Rakov (srakov@utopia.com) wrote:
: Hi,
:
: I've had a lot of trouble lately with Oracle extents and blocks. None of
: the books I've seen on the subject seem to make any sense to me. Can
: anyone point me to a good one, or to an FAQ about the topic? What are
: extents, how do they work, what do they do; what are blocks, what size
: are they, and how does one manage tables that are running into their
: maximum number of extents? How do table extents differ from tablespace
: extents? Where are they related?
:
: You can see that I'm a little confused. Any help would be greatly
: appreciated, particularly help that would point me to a resource that
: would explain all of this. I've tried "Oracle Unleashed," "The Oracle
: DBA Survival Guide" and "Tuning PL/SQL" all to no avail, although the
: "DBA Survival Guide" is a good book. It's just not explaining things
: simply enough.
:
: Thanks very much for any information.
:
: yours,
: Simon Rakov
: USWeb Utopia
Back to Ari Kaplan's Home Page