Oracle database is made up of one or more tablespaces. You might be familiar with the few standard tablespaces like SYSTEM, SYSAUX ( from Oracle 10g), Users, Tools, Index, Temp, Undo. A tablespace is invisible in the file system. A tablespace is a logical storage and consists of one or more datafiles, which are physical structures that conform to the OS in which Oracle DB is running. Prior to Oracle 8i, all tablespaces were created as 'dictionary managed'. Dictionary Managed tablespaces rely on data dictionary tables for space utilization. 'Locally Managed' introduced later use bitmaps to track of used and free space. Locally Managed result in ease of management and better performance.
To create a locally managed tablespace, specify LOCAL in the EXTENT MANAGEMENT clause of the CREATE TABLESPACE statement.
CREATE [UNDO|TEMPORARY] TABLESPACE tablespace
DATAFILE|TEMPFILE '.dbf'
[MINIMUM EXTENT n [K|M]]
[AUTOEXTEND
OFF
ON NEXT n K|M MAXSIZE UNLIMITED|n [K|M]]
[LOGGING|NOLOGGING]
[ONLINE|OFFLINE]
[PERMANENT|TEMPORARY] (Not needed for TEMPORARY)
[EXTENT MANAGEMENT
DICTIONARY (Must be LOCAL for TEMPFILE)
LOCAL
AUTOALLOCATE
UNIFORM [SIZE n [K|M]]]
[SEGMENT SPACE MANAGEMENT MANUAL|AUTO]
You can have Oracle manage extents for you automatically with the AUTOALLOCATE option (the default), or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM SIZE). If the tablespace is expected to contain objects of varying sizes requiring different extent sizes and having many extents, then AUTOALLOCATE is the best choice. DBMS_SPACE_ADMIN package provides maintenance procedures for locally managed tablespaces.
Tablespaces are divided into logical units of storage called segments, which are further divided into extents. Extents are a collection of contiguous blocks.
When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed. Options are a) Manual - Oracle use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. You need need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace. b) Auto - Oracle use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows.
A database can contain more than one undo tablespace, but only one can be in use at any time. Undo data is managed within an undo tablespace using undo segments that are automatically created and maintained by the database.
With Oracle 10g, you can create Big Tablespaces. This lets Oracle Database utilize the ability of 64-bit systems to create and manage ultralarge files. The consequence of this is that Oracle Database can now scale up to 8 exabytes in size. Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management. An Oracle database can contain both bigfile and smallfile tablespaces.
A datafile can be associated with only one tablespace and only one database. Oracle Database creates a datafile for a tablespace by allocating the specified amount of disk space plus the overhead required for the file header.
Segments are the database objects that consume storage. Data segments holds data of one of the following - a table, a partition of a partitioned table, a cluster of tables. Index segment holds every non-partitioned index, every partition for a partitioned index. Oracle requires a temporary segment for sorting.
Extents is a logically contiguous allocation of space in a file(not necessarily contiguous on disk). When you create a table, Oracle allocates to the table's data segment an initial extent of a specified number of data blocks. Although no rows have been inserted yet, the Oracle data blocks that correspond to the initial extent are reserved for that table's rows. Oracle uses different algorithms to allocate extents, depending on whether they are locally managed or dictionary managed.
With locally managed tablespaces, Oracle looks for free space to allocate to a new extent by first determining a candidate datafile in the tablespace and then searching the datafile's bitmap for the required number of adjacent free blocks. If that datafile does not have enough adjacent free space, then Oracle looks in another datafile.
When Oracle completes the execution of a statement requiring a temporary segment, Oracle automatically drops the temporary segment and returns the extents allocated for that segment to the associated tablespace.
A block is the smallest unit of space allocation in Oracle. Blocks stores the rows of data, index. Oracle reads a block and writes block to disk. Recent versions of Oracle Database, allow to have multiple data block sizes. While creating an Oracle database we have to choose the db block as a multiple of the OS block size. Legitimate values are from 2KB, 4KB, 8KB or 16 KB. You need to configure subcaches within the buffer cache for each of the different block sizes used with the database. Multiple block sizes are useful primarily when transporting a tablespace.
Block Structure is:
a) Header - information about type of block (table or index), active and past transaction information, location of the block on the disk.
b) Table Directory - Date from more than one table can be stored in a block. This section contains info about tables that store rows in this block.
c) Row Directory - is an array of pointers to where the rows are to be found in the data portion of the block
d) Free space
e) Data
f) Tail
Now lets discuss about a common performance bottleneck - row migration and row chaining.
