Sequences

The next database object to consider is the sequence. As discussed in the introduction, sequences are tools used to generate a unique sequential number that you can use in your data tables. It is an excellent way to generate a guaranteed unique number to use as an internal key to link two tables in an application. One of the best features of sequences is that they guarantee that you will get a unique value when you access the sequence. If, for example, you create your own sequence table and write applications that read the next available value, increment it by one, and then update the sequence table with this new value, there is always a chance that someone else can access the same record that you did before your update statement is issued (you could get around this if you played fancy games with locks, but why bother when sequences are available?). If this is a primary key to a table, you will have a corrupted primary key. One final note: You cannot roll back your incrementing of a sequence value. If you decide not to insert the row with that sequence value into your table, you do not recover the sequence number.

There are three parameters associated with a sequence—start value, max value, and increment (which is usually 1). The following shows the syntax used for creating a sequence:

SQL> create sequence location_id

  2  increment by 1

  3  start with 1

  4    maxvalue 999999999

  5    cycle;



Sequence created. 

Sequences are easy to access. For example, if you create a sequence called staff_id to track a unique, internal key for each staff member entered into a personnel database, you can enter a new record of data and generate a unique key with a simple SQL statement such as the following:

insert into personnel

values(staff_id.nextval,

‘Jones’,

‘Sandra’,

‘123-4567’); 

The maintenance issues associated with sequences are associated with the sequence not reaching its maximum value or the sequence not being updated during certain data loads into its associated data tables. By default, indexes are created with the cycle option, which means that they will recycle. This can cause problems if the sequence is feeding a column with a unique constraint or index. Therefore, you have to ensure that the sequence and column are large enough to hold a large array of unique values for the sequence. The other common problem with sequences is when someone writes software that makes up its own ID numbers as opposed to using the sequence. When a user tries to use the sequence, he will be given a value that has already been inserted into the associated data table. Therefore, if you use programs that do not use sequences, you have to update the sequences after this program is run to ensure that the sequence is up-to-date.

As a final note on sequences, there are a series of Oracle system privileges that are needed to create or work with sequences. These privileges are CREATE SEQUENCE and DROP SEQUENCE. This should be enough to get you started and enable you to understand what sequences are. There is additional background material in the basic Oracle documentation set if you want to try to get fancy with sequences.

Tablespaces

The fundamental unit of storage within a database is the tablespace. The tablespaces contain the various other types of objects to store user data. They also contain the series of tables, views, and so forth that Oracle uses to store information related to the operation of the database. The tablespace is the fundamental unit of storage for a second reason: It is the link to the physical storage world. A tablespace has one or more data files associated with it. You cannot control which data file Oracle uses to store a row that you are adding to the table, but you can control the tablespace, which at least narrows the list of data files that you will be accessing. Many of you may be uncomfortable with this lack of control. There are two points to be made in favor of this setup. First, you are already busy enough managing the database, so the burden of controlling physical storage is something you should welcome passing off to it. Second, you can always control which disk receives the data by creating tablespaces that have only one data file (if you really have to be certain on which disk a table is located).

It will be useful now to go over a command that enables you to see what tablespaces you have on your system. This illustrates an important general concept when dealing with Oracle. To find out what you want to know, you usually issue a Structured Query Language (SQL) query. Some prefer to use Oracle’s SQL*Plus product and others prefer SQL*DBA or Oracle Server Manager. However, either way you are accessing a view created for the database administrator that searches the internal Oracle tables to find the information that you need. I keep a list at my desk of the DBA views for those times when I am going after a view I rarely use. These are your windows into what is happening with your database. Oracle provides a GUI-based tool (Enterprise Manager) that executes these queries and then displays the results in the form of charts and numbers. For those of you who do not have access to Enterprise Manager, you can always get the data executing the basic queries.

One of the first things you will notice if you query the all_tablespaces view within Oracle8 is the series of columns that deal with extents or pct_increase. These are the default storage parameters for the tablespace. Every object that takes up space in the database (for example, tables and indexes) uses preallocated chunks of space known as extents. Oracle could have been designed to put rows of data into a big tablespace in the order in which they were received; however, with larger systems, you would have to scan a large amount of disk space to find all the rows associated with a particular table. Recall that Oracle chose to group the rows of a table into areas on the disks known as extents. This helps because Oracle reads extra rows into the SGA buffer cache (which makes sense because most operating systems read entire blocks of data from disk at a time).

Oracle could have stopped with the idea of having everything in a single extent for a database object; however, that would force developers to size each table accurately at the beginning of the project. You also would have to preallocate a large amount of space at creation for tables that may grow slowly over time. The compromise position that Oracle adopted was to make extents even multiples of the operating system block size and enable each table to occupy a reasonable number of extents. The maximum number of extents that a given object can occupy varies by operating system, but can usually go to over 100 extents.