How to create table in Oracle

In this Oracle tutorial, we will learn about how to create table in oracle database 19c. Also, we will cover these topics.

• Oracle Create Table
• Oracle Create Table Syntax 19c
• Oracle create table from another table
• Oracle Create Table with Sequence Primary Key
• Oracle create a tablespace
• Oracle create a table with another schema
• Oracle create a table with primary key
• Oracle create a temp table
• Oracle create a partitioned table
• Oracle create a temporary table with stored procedure
• Oracle create a duplicate table
• Oracle create a temporary tablespace
• Oracle Create Table with Foreign Key
• Oracle Create Table with Constraint
• Oracle Create Table Default Timestamp
• Oracle Create Table Default Value Sysdate
• Oracle Create Table Auto_Increment Primary Key
• Oracle Create Table with Insert Data
• Oracle Create Table Backup
• Oracle Create Table comment
• Oracle Create Table if not exists
• Oracle Create Table as Select without Data
• Oracle Create Table Enable
• Oracle Create Table Float

Note: Ideally we should create a table on the Pluggable database (PDBs) But to keep things simple we have demonstrated it on a common user (c##).

Oracle Create Table

In this section, you will learn how to create a table in the oracle database.

• There are two ways of creating table in oracle database:
  • Using SQL developer tool
  • Using Command-line

Create Oracle Table Using SQL developer tool

In this section, we will learn how to create an oracle table using the SQL developer tool.

• SQL developer is a GUI tool for creating databases and tables in oracle database.
• Learn to install SQL developer tool on windows 10 operating system.
• Click on the plus icon on the top left side as marked in the picture below.
• Enter any name to represent this connection and provide the username and password of the user to which you want to connect.
• Provide the SID as orcl, Incase you have created a differenc sid then provide that.
• Test the connection if successful then click on connect button.

• Once connection is established successfully you will be able to see an interface like below.
• On the left side expand the connected database and right click on the Tables option.
• Click on the New Table option.

• Create table wizard will appear with the schema name as the username provided by you in first step.
• Provide the table name, in our case it is LIFE_EXPECTANCY.
• Start createing the table by filling in the information as demonstrated in the below picture.
• Use ‘+’ icon on the right to add new columns for the table and incase of mistake use ‘x’ icon to delete the column from oracle database.
• Click on the ok button after providing all the necessary information. Table will be created successfully.

• Once the table is created successfully, expand the Tables (filtered) option and double click on the table name.

So in this way, we can create an oracle table using the SQL developer tool. Moving forward we will learn how to create an oracle table using the command line in the oracle database.

Read Oracle Database vs MySQL Database

Create Oracle Table Using Command-line

• Using CREATE TABLE statement in oracle sqlplus command-line we can create a table in the database. The basic syntax to CREATE TABLE the statement is given below:

CREATE TABLE schema_name.table_name (
 column_name1 data_type column_constraint,
 column_name2 data_type column_constraint,
 ......
);

In this syntax:

• Use Create Table keyword followed by schema and table name.
• Second, list all columns names with paranthesis. In case you want to create multiple columns you need to separate them by commas(,). A column definition is by its column name by data types like varchar,float,integers, etc and column constraints such as check, primary key,not null.
• Third, try to apply table constraints if applicable primary key, foreign key, check.
• Please note that you must have CREATE TABLE system privilege to create a new table under a new schema.
• You can use CREATE ANY TABLE system privilege to create a new table under another user’s schema.
• The owner of the new table must quote a tablespace that contains a new table or UNLIMITED TABLESPACE system privilege.

Oracle CREATE TABLE statement example:

The following examples show how to create a new TABLE named life_expectancy under OT schema:

CREATE TABLE c##ot.life_expectancy(
countries_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
countries varchar(100) not null,
Year date not null,
Average_age_of_life float not null,
primary key(countries_id)
);

• In this table life_expectancy table are four columns:
  • countries_id,
  • countries,
  • year
  • Average_age_of_life.
• The countries_id is identity column that identifies the column in the table.
• The data type of the column is number and GENERATED BY DEFAULT AS IDENTITY a clause is used to generate a new identity every time a new row is generated or inserted in the table.
• The countries columns contain datatype varchar2 that has maximum length of 100.
• It means that you cannot give the name of the countries column which contains a length of more than 50.
• The not null column constraint prevents the countries column to have null values.
• The year columns contain date constraints like "YYYY-MM-DD" or vice versa or just put YEAR value.
• Average_age_of_life columns contain the same characteristics as countries column but there is a small difference which uses FLOAT constraints.
• The primary key the clause specifies the countries_id column as the primary key column which is used for identifying unique rows in the column.

Read Number Datatype in Oracle Database

Oracle Create Table Syntax 19c

In this section, will learn about oracle create table syntax for oracle versions 19c & 12c.

• Oracle Create Table syntax is common in most of the databases like PostgreSQL, MySQL, SQL, etc. and it works same with all the versions of Oracle database.
• Oracle database version 19c, 12x, 11g, etc have the same format of creating a table.
• So the sysntax discussed in this section can be followed on all the version of oracle database.
• The syntax is divided into three parts:
  • Create Table Keyword
  • Table name
  • column names and their data types
  • Constraints (optional)

CREATE TABLE <schema_name>.<table_name>(
col_1 [data type] [constraints],
col_2 [data type] [constraints],
col_3 [data type] [constraints],
col_n [data type] [constraints]);

Read: Oracle Database Datatype

Oracle Create a Table from Another Table

In this section, we will learn about oracle creating a table from another table in the oracle database.

• Using Create Table <table name> As statement in oracle database we can create a table from another table.
• We can either use the entire columns from the other table or can select few column(s) in oracle database.
• Suppose there is a dog table that holds various attributes of dogs like breed, color of eye & hair, type of food they like, etc.
• So these columns or attributes can be used for other animals as well like for cat as cats also have breed, eye color, food types, etc.
• In that case using As Select statement in oracle database you can create new table with all or selective columns.
• In our example, we will demonstrate the three ways of creating a table from another table in oracle:
  • creating a table from another table with all the columns
  • creating a table from another table with selective columns
  • creating a table from multiple other tables
• For demonstration purposes we have created the dog table below and using this table we will create a cat table in further sub-sections using As Select in the oracle database.

Script:

-- CREATE SEQUENCE
CREATE SEQUENCE DOG_SEQ START WITH 1;

-- CREATE TABLE
CREATE TABLE DOG(
    SERIAL INTEGER DEFAULT DOG_SEQ.nextval PRIMARY KEY,
    BREED VARCHAR2(50),
    MAXAGE INTEGER,
    EYECOLOR VARCHAR2(20),
    HAIRTYPE VARCHAR2(200),
    "HUMANFRIENDLY(T/F)" CHAR(1),
    "TRAINABLE(T/F)" CHAR(1),
    "FOOD(VEG/NVEG/OMNI)" VARCHAR2(50)
);

-- INSERT DATA
INSERT INTO DOG 
(BREED, MAXAGE, EYECOLOR, HAIRTYPE, "HUMANFRIENDLY(T/F)", "TRAINABLE(T/F)", "FOOD(VEG/NVEG/OMNI)")
VALUES('AKITA', 15, 'DARK BROWN', 'short topcoat and dense undercoat','T', 'T', 'OMNI: RICE, FISH, SEA PLANT');

INSERT INTO DOG 
(BREED, MAXAGE, EYECOLOR, HAIRTYPE, "HUMANFRIENDLY(T/F)", "TRAINABLE(T/F)", "FOOD(VEG/NVEG/OMNI)")
VALUES('BULLDOG', 10, 'DARK BLACK/BROWN', 'straight, short, fine textured, smooth and glossy','T', 'T', 'omni: banana, lamb, fish, beef');

INSERT INTO DOG 
(BREED, MAXAGE, EYECOLOR, HAIRTYPE, "HUMANFRIENDLY(T/F)", "TRAINABLE(T/F)", "FOOD(VEG/NVEG/OMNI)")
VALUES('BULLDOG', 10, 'DARK BLACK/BROWN', 'straight, short, fine textured, smooth and glossy','T', 'T', 'omni: banana, lamb, fish, beef');

-- VIEW DATA
SELECT * FROM DOG;

-- DESCRIBE TABLE
DESC DOG;

Description of the dog table created after running the above script

Read: Oracle Create Index

Create a Table from Another Table with all the Columns

In this section, we will learn how to create a table from another table with all the columns in the oracle database.

• Using Oracle create table with As Select statement we can copy or duplicate the table columns and rows in oracle database 19c.
• Mention the name of schema with table name if you copying it from other table of different user.

Syntax:

In the below code, we have used the ‘As Select’ statement to create a table from another table. To fetch all the columns and data we have used ‘*‘ with a select statement.

Create Table <table_name> As
(Select * from <table_name>);

Example:

Here is an example to duplicate a table in the oracle database using As Select statement.

• In this example, we will create cat table using dog table mentioned above.
• Alternative name for this method is oracle clone table because we create exact replica of ther table.

Script:

-- CREATE TABLE
CREATE TABLE CAT AS 
    (SELECT * FROM SQLSERVERGUIDES.DOG);

-- DESCRIBE TABLE 
DESC CAT;

-- VIEW DATA
SELECT * FROM cat;

Here is the output of the above script, the description of the table is exactly the same as the dog table above. Even all the dog data will also be cloned here inside the cat table.

Read Connect to Oracle Database using Python

Create a Table from Another Table with Selective Columns

In this section, we will learn how to create a table from another table with selective columns in the oracle database.

• Using As Select statement in oracle database we can create a table from another table with selective columns in oracle database.
• In the select statament provide the name of columns that you want to clone.
• Column name you are providing in the new table must be present in the table from where you are creating this new table.
• Please provide the schema name followed by table name incase tables are from different users.

Syntax:

In this syntax, (col1, col2 .. coln ) are the column names that we want to clone from another table.

Create Table <schema>.<new_table_name> As
(Select col1, col2, .. coln from <schema>.<other_table_name>

Example:

In this example, we have created a cat table using a few columns from the dog table mentioned above.

-- CREATE TABLE
CREATE TABLE CAT AS 
(SELECT dog.Serial, dog.breed, dog.maxage FROM SQLSERVERGUIDES.DOG);

-- DESCRIBE TABLE 
DESC CAT;

--VIEW DATA
SELECT * FROM CAT;

Output:

In this output, a cat table is created with the selective columns in the oracle database. Data of those columns are also copied from the source table.

Create a table from multiple other tables

In this section, we will learn how to create a table from multiple other tables in the oracle database.

• Using Oracle create table As Select statament we can create a table from multiple other tables.
• Two or more table name needs to pass in the select statement to fetch information from the tables.
• You can also fetch selective columns from the multiple tables. All you need to do is instead of using ‘*’ use the names of columns.
• We have created a new table ‘mammals’ . Using this table and dog table we will demonstrate this section.
• Here is the script to create a table mammals in oracle database.

-- CREATE TABLE
CREATE TABLE MAMMALS(
    ANIMAL_NAME VARCHAR2(200),
    BREED VARCHAR2(50),
    SOUND VARCHAR2(50),
    ORIGIN_COUNTRY VARCHAR2(50)
);

-- INSERT DATA
INSERT INTO MAMMALS
VALUES('DOG', 'AKITA', 'BARK', 'JAPAN');

INSERT INTO MAMMALS
VALUES('Puma concolor', 'Felinae', 'ROAR', 'UNITED STATES OF AMERICA');

-- VIEW DATA
SELECT * FROM MAMMALS;

Syntax:

• This syntax shows two methods, in the first method we are creating a table from multiple tables and we are copying all the columns and data of those tables.
• In the second method, we are copying only selective columns from multiple tables.
• Please note that you need to specify the schema name followed by table name.

# All columns
Create Table <Table_name> As
(Select * from table_name1, table_name2 .. table_name_n);

# few Columns
Create Table <Table_name> As
(Select <table_name1.col1>, <table_name2.col1> from <table_name1> <table_name2> .. <table_name_n>);

Example:

In this script, using oracle create table as select statement we have created a new table with the name cat which has all the columns and data of dog and mammal table.

-- CREATE TABLE
CREATE TABLE CAT AS 
(SELECT * FROM SQLSERVERGUIDES.DOG, SQLSERVERGUIDES.MAMMALS);

-- DESCRIBE TABLE 
DESC CAT;

--VIEW DATA
SELECT * FROM CAT;

Read How to create a database in Oracle 19c

Oracle Create Table with Sequence Primary Key

In this section, we will learn about the oracle create table with sequence primary key.

• Sequence in oracle database is used to auto-generate the value in the given range and type.
• We can create sequence primary key in two ways in oracle database:
  • Oracle create sequence primary key using command-line
  • Oracle create sequence primary key using SQL developer tool (GUI).

Oracle creates sequence primary key using SQL developer tool (GUI).

Right-click on the Table -> Trigger -> Create (PK from Sequence)… -> Provide the trigger name in the dialog box -> Select the available sequence name -> Select column on which you want to apply sequence primary key -> Click on Apply.

Once you have followed the above picture’s steps then you will see the below wizard. Provide trigger name, select sequence name from the drop-down, select the column name on which you want to apply this setting. Click on apply button.

Oracle create sequence primary key using command-line

In this section, we will learn how to create a sequence primary key using a command-line oracle database.

CREATE SEQUENCE emp_id
MINVALUE 1
MAXVALUE 999999999999999
START WITH 1
INCREMENT BY 1
NOCACHE; 

• Parameter Description:
  • MINVALUE: minimum value that for the sequence
  • MAXVALUE: Maximum till where Sequence can go.
  • START WITH: Starting point of Sequence.
  • INCREMENT BY: increment as per the provided value
  • NOCACHE : If you want to speed up the accessing speed then set cache <value>

Oracle Create a Tablespace

A tablespace is a storage location where the actual data datatype underlying database objects can be kept. In layman’s language, where tables get stored. It links physical storage layers like(files on disks) and logical storage layers like (tables, indexes).

The following illustration shows how to create a tablespace named tb1 with the size of 1Mb:

CREATE TABLESPACE tb1
DATAFILE 'tb1_data.dbf'
size 1m;

This statement shows:

• Firstly, specify the tablespace name after CREATE TABLESPACE keyword. In this above example, the tablespace name is tb1.
• Secondly, specifty the datafile name after the keyword DATAFILE clause. In this case, the datafile name is tb1_data.dbf as datafile full path.
• Third, specify the size of the data file under the tablespace keyword as SIZE clause.In this example, 1m stands for 1mb file which is quite really small version file size.

Once the database files are created, you can find the information by querying data from the tb1_data_files view:

SELECT TABLESPACE_NAME,FILENAME,BYTES /1024/1024 MB FROM dba_data_files;

Here are all the tablespaces created in the database:

The CREATE TABLESPACE is quite complex with many options.

Read How to Fetch Data from Oracle Database in Python

Oracle Create a Table with Another Schema

The only way to allow a non-DBA user is to create a table with another schema is to give the user the CREATE ANY TABLE system privilege.

The following command shows how to create a table from one user to another user:

# login as sys user
sqlplus / as sysdba;

# username: c##smithj password: smith
CREATE USER c##smithj identifed by smith;

# grant permission
grant create session,create table to c##smithj;
alter user c##smithj quota unlimited on users;

# username: c##romej  password: sql
CREATE USER c##romej identifed by sql;

# grant permission
grant session,create table to c##romej;
alter user c##romej quota unlimited on users;

# table name: life_expectancy
Create table c##smithj.life_expectancy(
   country_id number generated by default as identity,
   country varchar(100) not null,
   Year date not null,
   Average_age_of_life float not null,
   primary key(country_id));

# connect to c##romej user
conn c##romej/sql;

# create a table & grant all permission
create table romej_table(a char);
grant all on romej_table to c##smithj;

# connect to c##smithj user
conn c##smithj/smith

# create table from another table
create table country_life_expectancy as select * from c##romej.sqlplus_table;

Read Connect Excel to Oracle Database

Oracle Create a Table with Primary Key

The primary key is the column that uniquely identifies each row in the table.

• There are certain rules of primary key in oracle database:
  • Primary key in oracle database can’t contain a null value or an empty string.
  • Primary key in oracle database must be unique within entire table.
  • Primary key in oracle database can’t be changed over time.
• According to these rules, there are certain recommendations to follow primary key:
• First, primary key should be meaningless. If the primary key will be meaningful then you probably need it. For example, If you created phone number as primary key then this piece of information you may need later and you want to change it or perform other opertations on it. so this should be avoided.
• Primary key should be something that you solely use only for identifying a record.
• Second, the primary keys should be compact. The primary keys are typically numeric and Oracle posses number faster than any other data type

The following CREATE TABLE statement creates the USA_healthcare table:

CREATE TABLE USA_HEALTHCARE(
patient_id number generated by default as identity, 
health_Camp_id number not null,
outcome float not null,
primary key(patient_id));

• The USA_healthcare the table has four columns:
  • patient’s number (patient_id),
  • health camper id as (health_camp_id),
  • outcome as (outcome)
• This table is defined by the patient_id column as the primary key in oracle database.
• Primary key column can’t be empty so there is no need for ‘Not Null’ constraint.

Read How to Check Oracle Database Version

Oracle Create a Temp Table

• Oracle create temp table is defined as an object where data is stored on a disk and after a particular session or transaction, that data is deleted.
• These tables are generally embedded in the SQL program in Oracle sessions.
• There are two types of temporary tables:
  • global temporary table
  • private temporary table.
• To create a global temporary table, we need to add a global keyword.
• Here is the syntax of the global temporary table in oracle database:

CREATE GLOBAL TEMPORARY TABLE TABLE_NAME(
column1_datatype [constraint],
column2_datatype [constraint],
...,
);

• Parameters Description:
  • table_name refers to temporary table that users is creating.
  • column1, column2 refers to name of column.
  • constraints refers to column constraints which we will add or may not.

• Here is an example of a global keyword in oracle database. The query contains ON COMMIT DELETE which means, the table will be deleted after every transaction.

CREATE GLOBAL TEMPORARY TABLE temp_table(
id number primary key,
description varchar2(20)),
on commit delete rows;

• To preserve the data in a temperory table for enitire session use ON COMMIT PRESERVE ROWS. Here is the example for the same.

CREATE GLOBAL TEMPORARY TABLE temp_table(
id number primary key,
description varchar2(20)),
on commit preserve rows;

Read How to Get List all Tables in Oracle Database

Oracle Create a Partitioned Table

• To handle Very Large Databases (VLDB) of enterprises Oracle provides a new feature called partition which is used to create a partition in a table i.e: Suppose you have a table containing millions of records like 1991,1992,1993,1994 and 1995. But you are worried about one year then you will give queries in the following :

SELECT PRODUCT,SUM(AMT) from Sales where year=1992 
group by product;

• While using this query under the search table, then this will make a performance issue where you use partition this table according to year, then the performance is improved and oracle will scan a single partition, not the whole table.

CREATE TABLE under_armour(
year number (4),
product varchar2(10),
product_amount number(10,2))
partitioned by range (year)
partitioned p1 values less than (1992) tablespace u1,
partitioned p2 values less than (1993) tablespace u2;

• There are mainly two ways using which we can perform partitioning of a table in oracle database:
  • Range Partitioning
  • Hash Partitioning
  • List Partitioning
  • Compsite Partitioning

1. Range Partitioning

Range partitioning in an oracle database allows objects to be partitioned by a specific range of partitioned keys. In this case, you can create partition by month, quarter, or year using a column by DATE field.

2. Hash Partitioning

Hash Partitioning in an oracle database is a method of separating rows and spreading them in sub-tables within the database. For this spreading out, hash keys are used effectively and efficiently. The following example shows how to create a partition table.

CREATE TABLE Life_expectancy(
part_no number,
decription varchar(60))
Partition by Hash(part_no)
Partitions 4
Store In (tab1, tab2, tab3, tab4);

3. List Partitioning

List partitioning in an oracle database enables you to explicit control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition. It allows unordered and unrelated sets of data to be grouped and organized together very naturally.

The following examples use list partitioning to create a table:

CREATE TABLE CUSTOMERS (
customer_code number(5),
name varchar2(20),
city varchar2(20),
balance number))
Partition by list(city),
Partition north_india values('DELHI', 'CHANDIGARH'),
Partition west_india values('BOMBAY','GOA');

4. Composite Partitioning

Composite partitioning in an oracle database uses the range method to partition table and within each partition, subpartition uses the hash method. When you are creating a composite partition, you specify the following:

• Partition method: range
• Partitioning columns
• Partition description defines partition bounds
• Subpartition method:hash
• Subpartition columns(s)
• No of subpartitions for each partitions

The following methods tell how to create a composite partition. In this example, three range partitions are created, each containing eight subpartitions.

CREATE TABLE PRODUCTS(
Part_no number,
description varchar(25)),
partition by range(Part_no),
Subpartition by hash(description)
Subpartition 8 store in (tab_1, tab_2,tab_3,tab_4)
(Partition p1 value less than (100),
 Partition p2 values less than (MAXVALUE));

Oracle Create a Temporary Table in Stored Procedure

• Oracle has a global temporary table that has objects whose data are stored in disk and are deleted with the end of the session or transaction.
• It is visible to all sessions currently connected to the database. To create a temporary table, you must have to CREATE TEMPORARY TABLE clause used at the end of the query.
• The following example show to create a temporary table in the stored procedure.

CREATE global temporary table my_medicare
(coumn_1 number) on commit delete rows
tablespace temporary_ts;

Oracle Create a Temporary Tablespace

Use of create temporary tablespace to create locally managed temporary tablespace and it is allocation of space in the database that contains schema objects for the duration of the session.
Note:

• Specify in bytes the size of tablespace extents. Use K or M to specify the size in kilobytes and megabytes.
• The temp_tablespace_extent clause let you specify how the tablespace is managed.
• All extents of temporary tablespace are the same size(uniform), so this keyowrd is optional. However, you must specify UNIFORM in order to specify size.

Creating a Temporary Tablespace: Example

CREATE TEMPORARY TABLESPACE temp_demo
tempfile 'temp01.ddf' size 5M AUTOEXTEND ON;

Oracle Create a Duplicate Table

The copy command will be obsoleted in the future part of SQL plus. No new data types will be supported by COPY. Duplicate/ Copy the command is used to copy data from one table to another table. Copy supports the following data types which are given below:

• CHAR
• DATE
• LONG
• NUMBER
• VARCHAR2

The syntax of copy command:

COPY FROM database TO database action
destination_table (column1_name,column2_name,
USING query

Examples: The following command copies the entire employees table to a table named as westemployees.The tables are located in two different databases. If westemployees table already existed, the SQL plus already replace the table and its contents.

COPY FROM HR@HQ TO JOHN@WEST 
REPLACE WESTEMPLOYEES
USING SELECT * FROM EMPLOYEES;

Oracle Create Table Data types

In this section, we will learn about oracle create table data types. We will understand what are data types and will learn to use data types in an oracle database.

• Data types in oracle determines the number of characters that can be passed as a value in a particular column.
• Every column in oracle database has a data type assigned and each data type occupies some space on the drive.
• Experienced database developers wisely choose the data types so that only that much space is used which is required.
• Each data type has range which can be crosschecked with the requirement of column and accordingly data type can be assinged in the database.
• In our example, we have created an employee table inside the company schema. Here, we have u