Oracle: Create table with examples


This tutorial helps you to create table in Oracle.

Oracle table creating script examples

With tablespace and storage declared.

CREATE TABLE oraerr_users 
    usrno NUMBER(5) PRIMARY KEY, 
    uname VARCHAR2(15) NOT NULL, 
    job VARCHAR2(10), 
    mgr NUMBER(5), 
    regdate DATE DEFAULT (sysdate), 
    photo BLOB, 
    sal NUMBER(7,2), 
    clickrate NUMBER(7,2) GENERATED ALWAYS AS (sal/2080), 
    comm NUMBER(7,2), 
    refno NUMBER(3) NOT NULL CONSTRAINT admin_dept_fkey REFERENCES oraerr_departments (department_id)
TABLESPACE oraerr_tbs 

COMMENT ON TABLE oraerr_users IS ' user table'; 

Note on that script:

  • BLOB is a part of LOBs, LOBs are used to store semi-structured data (such as an XML tree) and unstructured data (such as the stream of bits in a color image).
  • clickrate is a virtual column, this column computes the employee’s hourly rate as the yearly salary divided by 2,080
  • COMMENT statement is used to store a comment for the table
  • refno is a FOREIGN KEY which references to ORAERR_DEPARTMENTS’s department_id column.
  • You have to create tablespace ORAERR_TBS before executing this script.
  • If no tablespace statement found, Oracle will use default schema’s tablespace for the tables.