Table Creation Syntax

For the creation of tables, the SQL commands DROP/CREATE TABLE and ALTER/DROP/CREATE TABLESPACE are a very important part of the syntax:

  CREATE TABLE   table name  (   Column-name1           datatype1 column1 constraint,   Column-name2           datatype2,  .                          .   .                          .   .                          .  any primary key combinations...  ) TABLESPACE  tablespace-nam  e   ;  

(1) Note that the last column definition does not end in a comma.

(2) Note that the TABLESPACE line comes after the right parenthesis.

We will use these commands extensively in this chapter. Here's a concrete example:

 CREATE TABLE MY_TABLE ( FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(20), PRIMARY KEY FIRST_NAME, LAST_NAME                    ) TABLESPACE  MY_NAME_TABLESPACE 

Syntax Rules

Here are the specific syntax rules for creating tables:

• Column definitions are contained in parentheses. Start the column definition with a left parenthesis; put a comma after each column definition line except the last one; end the column definitions with the right parenthesis.

• Place the TABLESPACE line after you close out the column definitions.

• Wrap the whole thing up with a semicolon.

You need to be very careful with special characters such as the parenthesis, comma, and semicolon. After you get syntax errors a couple of times, you'll catch on. I have tried to make these characters clear by highlighting them in the example at the beginning of this section. You do not need to put them on their own lines, as this example shows:

 CREATE TABLE NAMES (FIRST_NAME VARCHAR2(20)) TABLESPACE   NAMES_SPACE; 

Remember that constraints include making a column a primary key, making it "not null", and adding referential integrity. Quite a few other variables can be used with table creation, but the ones I have described here will suffice for our purposes.

Remember tablespaces? We will be putting all our tables into the tablespace that was created when the Oracle database was built: the CW1 tablespace.

Tablespaces

Remember that a tablespace is just a slice of storage. We separate tables for performance, backup, and other reasons. In a multidisk system, you can balance the input/output load by separating the system tables and the data tables into their own tablespaces on their own disks.

Here's the general process:

1. Make sure that the tablespace is deleted if it already exists. Unlike the process with tables, this is done in two steps:

   1. Alter the tablespace to put it offline in normal mode.

   2. Drop the tablespace.

2. Create the tablespace.

And here's the syntax:

  ALTER TABLESPACE  MY_NAMES   OFFLINE NORMAL            ;  DROP TABLESPACE  MY_NAMES   INCLUDING CONTENTS             ;  CREATE TABLESPACE  MY_NAMES   DATAFILE 'D:\TABLESPACES\DATA\MY_NAMES' SIZE 500M   ONLINE   PERMANENT             ; 

Note the following about the syntax with tablespace creation:

• Everything ends with a semicolon.

• NORMAL in the ALTER statement means wait until all users are out of the tablespace before taking it offline. In our case there is no activity, so this statement has the effect of taking the tablespace offline immediately .

• In the DROP command, INCLUDING CONTENTS means delete the tablespace even if it contains tables.

• The CREATE statement also stipulates that we want the tablespace to be online and permanent. (We will create a temporary tablespace later that will be used for sorts and other temporary tables and files.) Put the data file name in single quotes. (There are many other ways to refine the CREATE command, but this is enough to get us going. You will learn more as you gain experience.)

• Finally, if you don't specify the path and directory for the data file, Oracle will put it in the \oracle\oraXX\database folder.

Note

Now that you're familiar with the syntax for tablespace and table creation, I want to make you aware that tablespaces can be refined further to significantly improve performance. The refinement is to define the tablespaces as locally managed . We do this by adding EXTENT MANAGEMENT LOCAL to the CREATE TABLESPACE syntax. This command gives the tablespace control over space allocation and deallocation as blocks are used and released.

I refer you to more technical documentation for an in-depth discussion of locally managed tablespaces and the advantages.