3.3 Tables A table is the basic building block for storing data in the database. Conceptually, a table is a file that is created and maintained within the data files assigned to the database. 3.3.1 Table Parameters When a user creates a table, he or she specifies parameters such as the table name , the column names , 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 NEXT 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 persdata01.dbf , persdata02.dbf , and persdata03.dbf associated with the data01 tablespace. User mary creates a table named "employee" and specifies the data01 tablespace. A command such as the following might be used while mary is logged into SQL*Plus: CREATE TABLE employee (employee_num NUMBER(6) NOT NULL, employee_name VARCHAR2(20), employee_location VARCHAR2(10), manager_name VARCHAR2(20), init_employment_date DATE, title VARCHAR2(20)) TABLESPACE data01 STORAGE (INITIAL 275K NEXT 50K MAXEXTENTS UNLIMITED PCTINCREASE 0); 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 size specified for the initial allocation. If many tables have already been created, this table may be in file persdata03.dbf . If the table is one of the first created in the tablespace, it will probably be built in persdata01.dbf . 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 persdata01.dbf 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 persdata01.dbf file. This is not enough for the new table, so in this case, mary 's employee table will be created in persdata02.dbf . The 200 Kbytes left in persdata01.dbf will remain unused until an allocation request is made for an amount of space that is equal to or less than the 200 Kbytes remaining in persdata01.dbf . 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 persdata01.dbf . 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 necessarily come from the same file as the first allocation. Each allocation is referred to as an extent . 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 requested to build a table or perform an allocation of an extent must be "contiguous" space, that is, enough bytes located in a group together. A tablespace may become fragmented . 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 inserts 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 behalf 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 mary , 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 public , or to a role. These types of grants are discussed later in this chapter in the "Privileges" section. | 
