Creating Regular and Temporary Tables

The CREATE TABLE command is used to create relational or object tables. Relational tables are the basic structure used to hold user data. Object tables are used to hold tables that have an object type for a column definition. Object tables are explicitly defined to hold object instances of a particular type (but we will not cover object tables for this exam).

Oracle suggests, rightfully, that locally managed tablespaces are best for table creation because they help to avoid fragmentation.

The following is an example of a CREATE TABLE script:

 CREATE TABLE friends( Fname varchar2(35), Lname varchar2(35), Birthday date, Email_address varchar2(100), Street_addr1 varchar2(100), Street_addr2 varchar2(100), Street_addr3 varchar2(100), City varchar2(50), State_abbr char(2), Zip_code number) TABLESPACE mydata; 

This example table creates a simple address book table of friends that the user wants to maintain.

Certain privileges are required to create tables. If your users are going to be creating tables only in their own schema, they need to have CREATE TABLE privileges. For a user to create tables in other people's schemas, they will have to be granted the CREATE ANY TABLE privilege.

If you are creating the table in a dictionary managed tablespace and want to be able to control the storage parameters associated with the table, you can use the following command:

 CREATE TABLE friends( Fname varchar2(35), Lname varchar2(35), Birthday date, Email_address varchar2(100), Street_addr1 varchar2(100), Street_addr2 varchar2(100), Street_addr3 varchar2(100), City varchar2(50), State_abbr char(2), Zip_code number) STORAGE (INITIAL 50K NEXT 50K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS UNLIMITED) TABLESPACE mydata; 

This STORAGE clause allocates the first extent of 50KB (the INITIAL parameter). Each additional extent needed will be 50KB (NEXT). There will be at least one extent allocated (MINEXTENTS) and no limit to the number of extents that can be allocated (MAXEXTENTS UNLIMITED). There will be no increase in size in any of the NEXT extents that are allocated (PCTINCREASE=0).

Table 12.1 provides a set of block utilization parameters that are still applicable to dictionary managed tablespace based tables. These parameters are deprecated with Automatic Segment Space Management.

Table 12.1. Block Utilization Parameters

Block Utilization Parameters	Description
PCTFREE	Specifies the percentage of the space that will be reserved in each data block to be filled with updates. Value must be between 0 and 99. 0 means that the entire block will be filled by inserts of new rows. Default is 10 (reserving 10% of each block for updates to existing rows and allows 90% of the block to be filled with inserts).
PCTUSED	The amount of used space that will be maintained for each data block in a table. A block becomes a candidate for row insertion when its used space falls below PCTUSED (which is an integer between 0 and 99 with a default of 40). PCTUSED + PCTFREE <= 100%
INITRANS	Initial number of transaction entries allocated within each data block allocated to the table. Value can range from 1 to 255 and defaults to 1. INITRANS ensures that a minimum number of transactions are allowed to update the block. Ordinarily, this value should not be changed.
MAXTRANS	The maximum number of concurrent transactions that can update a data block. This limit only applies to inserts, updates, and deletes. SELECT statements don't apply. Value can range from 1 to 255, and the default is a function related to data block clause.

If the TABLESPACE clause is omitted, the table will be created in the user's default tablespace.

Temporary Table Creation

Temporary tables are created to hold session level private data that exists only for the duration of the session. Temporary tables are created similarly to regular tables, but by using the additional GLOBAL TEMPORARY clause.

The following command creates a temporary table that can be either transaction-or session-specific.

 CREATE TABLE addresses_temp AS SELECT * FROM addresses; 

Transaction Specific

For transaction-specific temporary tables, the data within the table exists exactly for the duration of the transaction but goes away at the end of the transaction.

Session Specific

Session-specified temporary tables maintain their data for the duration of the session.

The clauses that control the duration of the data rows within the temporary table are as follows:

• ON COMMIT DELETE ROWS is used to specify that the rows are only to be visible and available throughout the duration of the transaction.

• ON COMMIT PRESERVE ROWS is used to specify that rows are to be fixable for the entire session.

Global temporary tables retain their data only for the duration of a single transaction or session, and the data in a session is private to that session, although any session or transaction can access the temporary table and manipulate its own data within. Data Manipulation Language (DML) locks are not acquired on the data within, and DML does not generate redo logs. You can create indexes, views, and triggers on temporary tables just like you can on regular tables. You can use the Import/Export utilities to export the definition of the temporary table and import it again; however, even if you use the ROWS option, no rows of data will ever be exported.

If you choose to build your tables in dictionary managed tablespaces, you will have to maintain some storage parameters for the tables. These are outlined in the following section.