Now that you have the concepts of storage under your belt, you are ready to understand the default storage parameters in all_tablespaces. These are only defaults; you can override them for database objects that you create (as is shown in the sections that follow). If you do not specify the default storage parameters when you create the tablespace, you will get the default values associated with the database. If you do not specify default parameters for your database, you inherit a set of defaults that Oracle provides that are tailored to your operating system. The following are the basic storage parameters for a tablespace:

•  initial_extent enables you to specify the size of this first extent. You have to allocate at least one extent when you create database objects. Note that you cannot create an extent larger than the largest contiguous extent that is available in a single data file. You may have a lot of space available, but if it is scattered between disk files and is interrupted with other extents, you will be limited to a small extent size for new objects.

•  next_extent specifies the size of the second extent that you allocate. All further extents are sized according to the value for this extent increased successively by the percent increase factor discussed later.

•  min_extents is the minimum number of extents you specify. You do not have to create just one extent when you create an object. I almost always create one extent, but you can specify any number of extents in this field, limited only by the max_extents parameter. The only scenario that I could come up with for allocating more than one extent is if you had a very large table which you knew was going to have to be split between multiple data files on multiple disks (for example, you have three 2GB disk drives and a table that needed three 1.5GB extents). You might want to allocate the large extents before allocating any other extents in the database or loading any data.

•  max_extents is the maximum number of extents that this object can occupy. As stated in the earlier discussions, if you have a large number of extents, it may take some time for the disks to locate all the extents that you may need in a query and send back the data. Generally speaking, the fewer extents that there are for an object, the more rapid the response time will be.

•  pct_increase is a parameter that I always set to zero, but some people use it. This factor represents a percentage increase to apply to each successive extent allocated. For example, consider a table where the next_extent size is 1000 bytes and the pct_increase is 10. Your second extent would be 1,000 bytes, your third extent would be 1,100 bytes, your fourth extent would be 1,210 bytes, and so on. I do not like to use it because I like to keep the number of extents down to a reasonable value for performance and know exactly what the size of the next extent to be allocated is without having to go to a calculator. For example, the 20th extent allocated in this example would consume over 5,500 bytes. Imagine what the 500th extent would be.

Another feature in all_tablespaces is the status column. You do not have to have all tablespaces online at the same time. If you do not want data altered for a given application that has its own tablespace, you can take the whole tablespace offline. The users will be told that they cannot gain access to any objects in that tablespace while it is offline. This comes into play later when you learn about recovery. If a single data file is damaged, you can bring the rest of your Oracle instance up and recover that single tablespace.

Partitions

Partitions are designed to allow you to take a large table or index and split it into multiple pieces based on one of the attributes associated with that table (for example, a date). The partitions are stored in separate tablespaces. While you could store different tables or indexes in separate tablespaces in earlier versions of Oracle, Oracle8 extends this concept to enable you to divide a given table into multiple tablespaces. This provides you with the following advantages:

•  Increased performance as you level the load between disk drives for a search of a given table. You can even search different partitions in parallel.

•  You can alter the storage parameters for different partitions. For example, old data, which is unlikely to be changed, can use minimal pctfree and maximum pctused parameters.

•  If you lose a tablespace due to a disk drive failure, you can still access data in the other partitions that are not associated with the failed disk drive.

•  You can back up and recover partitions individually. Here you can stop backups for old, static data and instead back up only the data that is changing frequently.

The following example shows how to create a partitioned table:

Create table addresses (employee_id            number(10),

                        Street                 varchar2(25),

                        City                   varchar2(25),

                        State                  varchar2(2),

                        Zip_code               varchar2(5),

                        Effective_date         date)

          Partition by range (zip_code)

                (partition values less than (30000) tablespace add1,

                 partition values less than (60000) tablespace add2,

                 partition values less than (99999) tablespace add3); 

If you are not dealing with large tables or indexes, you may want to consider partitioning for the reliability factor. You might also want to consider partitioning if you have a moderate size table that is the limiting factor for performance because it is accessed all of the time (and it is too big to cache in memory). Otherwise, you can stick to simple, nonpartitioned tables.