Creating a Table

You use the CREATE TABLE statement to create a table. The simplified syntax for the CREATE TABLE statement:


CREATE [GLOBAL TEMPORARY] TABLE table_name (
    column_name type [CONSTRAINT constraint_def DEFAULT default_exp]
   [, column_name type [CONSTRAINT constraint_def DEFAULT default_exp] ...]
)[ON COMMIT {DELETE | PRESERVE} ROWS]
TABLESPACE tab_space;

Option	Description
GLOBAL TEMPORARY	the table is a temporary table.
table_name	the name of the table.
column_name	the name of a column.
type	the type of a column.
constraint_def	a constraint on a column.
default_exp	an expression to assign a default value to a column.
ON COMMIT	the duration of the rows in a temporary table. DELETE means the rows are deleted at the end of a transaction. PRESERVE means the rows are kept until the end of a user session, at which point the rows are deleted. The default is DELETE.
tab_space	the tablespace for the table. If you omit a tablespace, then the table is stored in the user's default tablespace.

The following example connects as the store user and creates a table named myTable:


SQL> CREATE TABLE myTable(
  2     id            INTEGER CONSTRAINT my_pk PRIMARY KEY,
  3     status        VARCHAR2(10),
  4     last_modified DATE DEFAULT SYSDATE
  5  );

Table created.

SQL>

Creates a temporary table whose rows will be kept until the end of a user session (ON COMMIT PRESERVE ROWS):


SQL> CREATE GLOBAL TEMPORARY TABLE myTable (
  2  id            INTEGER,
  3  status        VARCHAR2(10),
  4  last_modified DATE DEFAULT SYSDATE
  5  )
  6  ON COMMIT PRESERVE ROWS;

Table created.

SQL>

Home »
Oracle »
Table »

Table Creation: