Copyright Oriole Corporation, 1999

Tablespaces

When you create an Oracle database, you assign to it a number of files, which can be seen as a pool of free space made available to the database. Files are organized in tablespaces (a file belongs to a single tablespace). Except in a handful of statements, only tablespaces, not files, are referenced in SQL commands.
The golden rule of physical storage management is to store different types of objects - tables, indexes, rollback segments ... - in different, dedicated tablespaces. The golden rule is also one of the most broken ones, because people who create tables or indexes - or more subtly which define primary keys, for which an index is implicitly created - are often untrained beginners themselves who forget, in the lucky case when they have heard the name TABLESPACE mentioned, to specify where to create their objects. As a result, everything tends to pile up in what was defined as the default tablespace for the user. A useful trick for avoiding this unpleasant situation is given in the paper on user management.

Basically, when assigning objects to tablespace (which is rarely done by database administrators), objects which are likely to be fetched from disk at the same time should be placed on different disks - in order to have some parallelization of I/O. This is the main reason why a table should be separated from its indexes, as an indexed search accesses the index, where it finds the physical address of the rows, and the table. In practice, modern configurations in which arrays of disks are used often make it difficult to predict exactly where this table or this index will be located. So, rather than disks, one should pay attention to having the tablespaces (rather than the individual files) accessible through different controllers.

Special tablespaces

Creating a database always creates the SYSTEM tablespace. This is where dictionary tables (which describe the other tables) are created.SYSTEM should be hallowed ground and nothing should ever been created in it. One should always create a special TEMP temporary tablespace (for sorts, chiefly) and one for rollback segments.

Segments, extents and blocks

What is called segment is nothing more than the physical implementation of something like a table or an index (or a work area, in the case of rollback or temporary segments). They are themselves made of a number of extents which are "contiguous" chunks in a file (the notion of contiguity is quite relative. The extent is contiguous at the file level, but the file may be fragmented on the disk itself) expressed as a number of blocks. Blocks are the elementary storage unit for Oracle, in order to have efficient I/Os their size must be chosen to match the possibilities of the hardware and of the operating system (default values provided by Oracle are usually quite suitable). The size of the block is one of the key parameters when you create a database, it cannot be changed afterwards.
Most experienced DBAs pay great attention to the fragmentation of tables and indexes, i.e. they like segments to be composed of a very small number of extents (the dictionary table to look at for that is DBA_SEGMENTS). This is definitely the right thing to do, although not always for the reason they think. Many people are afraid of fragmented tables because fetching the data in them would imply going to different places on the disk, thus slowing down performance. In fact, if programs are decently written, indexes are used to reach the exact block where the data is, so fragmentation is not so much of a concern - 20 or 30 extents are bearable. However, a much fragmented table usually indicates a very poor prior study of storage allocation, which IS a cause for concern, and probably also means heavy internal fragmentation, i.e. inside the blocks - Oracle stores almost everything as variable length data, so after a huge number of updates some data may have been migrated to overflow areas in other blocks, etc. which seriously impairs performance.

How segments increase in size

When a table or index or anything is created, an associated segment is created with the physical characteristics defined by the STORAGE clause. When the data to be stored outgrows the allocated space, Oracle automatically allocates a new extent; if this extent is the second one to be allocated, its size will the NEXT parameter in the STORAGE clause, rounded up to a whole number of blocks. Otherwise, its size will be the size of the latest extent allocated to the segment (which can be found in DBA_EXTENTS which lists all the allocated extents in the database) increased by a percentage specified by the PCTINCREASE parameter in the STORAGE clause.

Frequent errors

In practise, the problem you will meet most often is the failure to allocate an extent. There may be three reasons for this failure :

• No more space in the tablespace
  Your tablespace is full; usually the only thing to do is to add a new datafile to your database, but beware, the number of file you can add to a database is limited by, first a parameter read at startup (a soft limit), and second a value defined at creation time which defines (in part) the size of your control files. You can however recreate your control files (command CREATE CONTROLFILE) to push the limit up, but it's the kind of operation to be executed very carefully. Note that since Oracle 7.2 you have the possibility, when adding a file to a tablespace, to specify that this file will 'auto-extend'. Except perhaps in the case of work areas, such as the temporary or rollback tablespaces, or perhaps the SYSTEM tablespace (especially when migrating, each new release inserts ever more stuff into the data dictionary tables), we are against this practise. As long as there is no 'auto-order a new disk' command, this is just avoiding the issue by making a database management problem a systems management problem - your files will increase, fine, but someday they may outgrow the disk and you will have the same problem. Moreover, the power of the SQL language makes it much easier to monitor how space usage evolves within the database by querying the data dictionary tables than it will ever be possible to do with system utilities.
• Not enough contiguous space in the tablespace
  The dictionary view DBA_FREE_SPACE shows that there is enough space within the tablespace to accommodate the new extent, but no single free chunk big enough - remember that an extent is a contiguous number of blocks. Your tablespace is a victim of the 'Swiss Cheese' syndrome, with a lot of free space everywhere. Like disks, Oracle datafiles need to be defragmented from time to time. The easiest way to do it is to use the exp and imp utilities, which unload your data to a file (proprietary format!) and reload it into a database. Easiest is a figure of speech, because you always have problems with integrity constraints or procedures which have trouble recompiling. As a rule, you can bet that any reorganization will mean problems with integrity constraints you will have to deactivate and reactivate, whichever way you do it. Moreover, if you have big tables, the time taken to reimport may be huge - reimporting a 500,000 row table can take seven or eight hours. At Oriole we advocate the use of our unloader, pdqout, and of Oracle's very fast SQL*Loader. However, fast doesn't mean easy and reorganizations are always complicated matters, whatever the tools, because you always have some weird table with a LONG RAW column or God knows what, which messes up everything and for which you need to be creative. Better left to experienced DBAs! (but watch them).
  For this kind of problem, the best strategy is still a defensive one : if tables and indexes are correctly sized, if your tablespaces are correctly organized and you do not have temporary work tables created and dropped amidst the important data, there is no serious reason why your tablespaces should become fragmented.
  A source of problems may also be the PCTINCREASE parameter, the default value of which is 50 (meaning that each new extent is one and a half times bigger than the previous one); you can compute it, it quickly becomes very big, and an extent allocation may fail because it's just enormous. Be very careful not to have objects created with a value for PCTINCREASE bigger than 10 or 20 at most. And if you have a fair idea about the final size of a table, create it with this size, set NEXT to 10% of the creation (INITIAL parameter) and PCTINCREASE to 0, which is just perfect for 99% of cases.
• The segment has reached its maximum number of extents
  Prior to Oracle 7.3, the maximum number of extents which can be allocated to a segment is limited (it depends on the block size - 121 for the common 2K block size, roughly twice as much for a 4K block size, and so on). You can specify that the number of extents is unlimited with Oracle8. You will get this error 99% of the time with rollback segments (see the paper on rollback segments). It usually means they have been improperly sized (extents are too small). There is a script in the X-rated section of the oriolecorp website to deal with such problems, but this is definitely NOT a script for beginners - resize your rollback segments manually, you will learn in the process...