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 STORAGE ( INITIAL 50K); COMMENT ON TABLE oraerr_users IS 'Oraerr.com 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.