Oracle7 Server Administrator's Guide

Contents Index Home Previous Next

Creating Tables

To create a new table in your schema, you must have the CREATE TABLE system privilege. To create a table in another user's schema, you must have the CREATE ANY TABLE system privilege. Additionally, the owner of the table must have a quota for the tablespace that contains the table, or the UNLIMITED TABLESPACE system privilege.

Create tables using the SQL command CREATE TABLE. When user SCOTT issues the following statement, he creates a non-clustered table named EMP in his schema and stores it in the USERS tablespace:

CREATE TABLE emp (
   empno     NUMBER(5) PRIMARY KEY,
   ename     VARCHAR2(15) NOT NULL,
   job       VARCHAR2(10),
   mgr       NUMBER(5),
   hiredate  DATE DEFAULT (sysdate),
   sal       NUMBER(7,2),
   comm      NUMBER(7,2),
   deptno    NUMBER(3) NOT NULL
             CONSTRAINT dept_fkey REFERENCES dept)
   PCTFREE 10
   PCTUSED 40
   TABLESPACE users
   STORAGE ( INITIAL 50K
             NEXT 50K
             MAXEXTENTS 10
             PCTINCREASE 25 );

Notice that integrity constraints are defined on several columns of the table and that several storage settings are explicitly specified for the table.

See Also: For more information about system privileges, see Chapter 20. For more information about tablespace quotas, see Chapter 19.

Contents Index Home Previous Next