A table is the basic building block for storing data in the database. Conceptually, a table is a file that is created and
within the data files assigned to the database.
3.3.1 Table Parameters
creates a table, he or she specifies parameters such as the table
, the column
, their data types, and their lengths. The tablespace is also usually specified in the CREATE statement to ensure that the table is created in the proper tablespace. If a tablespace name is not specified, the table will be created in the user's default tablespace. An initial amount of space is specified for the table's data using the INITIAL parameter of the STORAGE clause in the CREATE TABLE statement. When that amount of space is filled, more space will be allocated based on the value set by the
parameter in the STORAGE clause. If no STORAGE clause is present in the CREATE TABLE statement, the default storage values of the tablespace in which the table has been created will be used. The kernel handles this allocation, and the space comes from the datafiles assigned to the tablespace in which the table was created.
3.3.2 What Happens When a Table Is Created
Suppose the database has a data01 tablespace and there are three datafiles named
associated with the data01 tablespace. User
creates a table named "employee" and specifies the data01 tablespace. A command such as the following might be used while
is logged into SQL*Plus:
CREATE TABLE employee
(employee_num NUMBER(6) NOT NULL,
STORAGE (INITIAL 275K
The kernel will allocate 275 Kbytes in one of the three datafiles of the data01 tablespace. Generally, the space allocated will be the first contiguous space found that is greater than or equal to the
specified for the initial allocation. If many tables have already been created, this table may be in file
. If the table is one of the first created in the tablespace, it will probably be built in
. If prior tables that were created were allocated with very large amounts of space, there may not be enough space for the minimum size specified for this table. This situation would occur, for example, if the
file were 1 megabyte in size and a table were created with an initial size of 800 Kbytes. There is only 200 Kbytes left in the
file. This is not enough for the new table, so in this case,
's employee table will be created in
. The 200 Kbytes left in
unused until an allocation request is made for an amount of space that is equal to or less than the 200 Kbytes remaining in
. Should that space be available when the initial allocation for the employee table is filled, the NEXT allocation of 50 Kbytes could be taken from the remaining 200 Kbytes in
3.3.3 As the Table Grows
When the initial 275 Kbytes allocated to the employee table are filled, an additional 50 Kbytes (the NEXT parameter) will be allocated. This will also come from any one of the three datafiles, but will not
come from the same file as the first allocation. Each allocation is referred to as an
. Since in our example this table is defined with the MAXEXTENTS parameter as UNLIMITED, the table can grow unbounded except by the space available in the three datafiles in the tablespace. If the datafiles fill up, any more attempts to allocate space will generate a "cannot allocate extent" error. In all cases, the space
to build a table or perform an allocation of an extent must be "contiguous" space, that is, enough bytes located in a
A tablespace may become
. That happens when there is enough total space available within a tablespace to meet the allocation request, but the space is not contiguous. In this situation, the CREATE command or the attempt to allocate the NEXT extent will fail to complete.
Note that each field is defined by a data type and length. The kernel enforces the type and length restrictions during
and updates. An error will be returned when an INSERT or UPDATE statement contains data that does not fit the type defined for the field or when the data is too large to fit in the field.
3.3.4 Table Ownership
Tables are usually owned by the user who creates them. The exception is when a privileged user creates an object on
of another user. In that case, the specified user owns the object as though the specified user had created the object explicitly. For example, a user with the CREATE ANY TABLE privilege (such as the DBA) could specify the statement as:
CREATE TABLE mary.employee ...
and the resultant table would be owned by
, and not by the user who actually entered the command.
Tables can have more complex characteristics than we have shown in this example, such as partitions and embedded objects, which are beyond the scope of our discussion. The general concept does not change.
Users can access other users' tables only if the owning user or a DBA grants privileges to them. These grants may be made directly to the users, to a special user named
, or to a role. These types of grants are discussed later in this chapter in the "Privileges" section.