1.6 Behind Tables

What is a table? Is it a file, a block, or a stream of bytes? Here we look at tables logically and physically.

1.6.1 Application Tablespaces

All table data is ultimately stored in host operating system files; but, the insertion of rows never specifically identifies a host file.

The first step is to create an intermediate logical layer called a tablespace with a CREATE TABLESPACE statement. This statement includes the host pathnames of one or more host files that are to be created. The CREATE TABLESPACE statement creates the files mentioned in the statement, formats the files, and stores information in the Oracle data dictionary. The data dictionary information tracks the fact that a tablespace is made up of specific files.

Once the tablespace is created, the CREATE TABLE statement can reference the tablespace name in the create statement. From this point on, Oracle will use the files of that tablespace for row storage. Figure 1-3 illustrates this architecture showing that tables and tablespaces are logical entities whereas the datafiles are the ultimate physical component.

Figure 1-3. Tables in a Tablespace.

To replicate the environment in Figure 1-3 create the tablespace, then the table. The following creates a tablespace STUDENT_DATA and allocates 10M of disk space. The presumption is that this file does not exist; in fact, this statement will fail immediately if the file exists prior to statement execution.

  SQL>  CREATE TABLESPACE student_data DATAFILE  2  'D:\student_data.dbf' size 10M;  Tablespace created.  

To create a STUDENTS table in the STUDENT_DATA tablespace:

  SQL>  CREATE TABLE students  2  (student_id    VARCHAR2(10),  3  student_name  VARCHAR2(30),  4  college_major VARCHAR2(15),  5  status        VARCHAR2(20)) TABLESPACE student_data;  Table created.  

Other tables can be added to the STUDENT_DATA tablespace. The student demo is described in Chapter 4. All the demo tables are created in a STUDENT_DATA tablespace.

A single application usually has all tables in one tablespace. There are circumstances where multiple tablespaces are used. Multiple tablespaces are driven by a variety of issues including highly demanding physical storage requirements and partitioning. The following summarizes some remaining topics on tablespaces.

• There is a standard, known as the Optimal Flexible Architecture (OFA). The OFA standard recommends that database files fit into a directory structure where the parent directory name is the same name as the database name, plus other reasonable considerations. The aforementioned example violates this convention only to simplify the example.

• The datafile D:\student_data.dbf did not exist prior to the CREATE TABLESPACE statement. This file is created during the execution of the CREATE TABLESPACE statement. It is possible to create a tablespace on an existing datafile ”this requires a REUSE clause in the syntax.

• A tablespace can consist of multiple files. For example, if you need 20M you can have two 10M files.

• The datafiles in the CREATE TABLESPACE statement are formatted by Oracle. You'll notice that a CREATE TABLESPACE statement on a 2G datafile takes relatively longer that a 2M datafile. This is because Oracle formats the datafile using its own internal block structure.

• The aforementioned example is simple and may imply a strict architecture ”such as dealing with space when you fill up 10M of data. The tablespace model is highly flexible. You can add files to an existing tablespace, resize a datafile, move a datafile to another drive and resize it, or allow datafiles to auto-extend ”all without taking down the database. The physical layout of an Oracle database is highly flexible.

• A datafile can serve one and only one tablespace. You will never, and cannot possibly, have conditions where a datafile is "tied" to more than one tablespace.

• An Oracle user always has a DEFAULT tablespace. So, if you do not specify a tablespace name, that table is created in your default tablespace. You can get your default tablespace name by querying the data dictionary view USER_USERS.

    SQL>  SELECT default_tablespace FROM user_users;  DEFAULT_TABLESPACE   ------------------------------   USERS  

• A table is created in a single tablespace. Exceptions to this are partitioned tables where individual partitions are created in separate tablespaces.

• While a table is created in one tablespace, the indexes for that table are often in a separate tablespace. The DDL for the data model demo in Chapter 4 creates all indexes in the tablespace STUDENT_INDEX.

1.6.2 Data Dictionary

The execution of a CREATE TABLE statement causes information to be stored in the data dictionary. The data dictionary is the term used to describe tables and views that exist in the SYSTEM tablespace. The data dictionary is essentially a repository for Oracle to track information about all objects created in the database. The information tracked includes: the table name, who owns the table, when it was created, column names and datatypes, and the tablespace name to which a table belongs. All PL/SQL stored procedure source and compiled code is stored in the data dictionary. The data dictionary tables and views of the SYSTEM tablespace are illustrated in Figure 1-4.

Figure 1-4. Data Dictionary and System Tablespace.

The data dictionary consists of Oracle tables and views that are constructed from SELECT statements against the base tables. The data dictionary views provide the attributes of any object created. The view USER_TAB_COLUMNS can be queried to determine the column names of a table. The data dictionary view to query for student column definitions is USER_TAB_COLUMNS.

The SYSTEM tablespace is created when the database is first created. The SYSTEM tablespace and datafiles are generated as part of the CREATE DATABASE statement. Application tablespaces, such as STUDENT_DATA, can be added to the database at any time.

The following SQL*Plus session creates a STUDENTS table. A query of the data dictionary view USER_TAB_COLUMNS shows the column name and column type of all columns in the STUDENTS table.

  SQL>  CREATE TABLE students  2  (student_id VARCHAR2(10),  3  student_name VARCHAR2(30),  4  college_major VARCHAR2(15),  5  status VARCHAR2(20)) TABLESPACE student_Data;  Table created.   SQL>  SELECT table_name, column_name, data_type  2  FROM user_tab_columns  3  WHERE table_name='STUDENTS';  TABLE_NAME       COLUMN_NAME                    DATA_TYPE   ---------------- ------------------------------ ----------   STUDENTS         STUDENT_ID                     VARCHAR2   STUDENTS         STUDENT_NAME                   VARCHAR2   STUDENTS         COLLEGE_MAJOR                  VARCHAR2   STUDENTS         STATUS                         VARCHAR2  

To see the tablespace in which the STUDENTS table exists, use

  SQL>  SELECT tablespace_name  2  FROM user_tables  3  WHERE table_name='STUDENTS'; TABLESPACE_NAME ------------------------------ STUDENT_DATA 

The following shows the datafiles and file sizes associated with the STUDENT_DATA tablespace. This query selects from the DBA_DATA_FILES view and requires that you have the DBA role or SELECT_CATALOG_ROLE role.

 column file_name format a50  SQL>  SQL> SELECT file_name, bytes  2  FROM dba_data_files  3  WHERE tablespace_name='STUDENT_DATA';  FILE_NAME                                       BYTES   ------------------------------------------   --------   E:\ORACLE\ORADATA\ORA10\STUDENT_DATA01.DBF    5242880