Table Creation On Oracle Database

Topic Introduction: In this tutorial, we will discuss table creation on Oracle Database, copying a table with data, Copy table structure without data, etc.

Table naming and column naming Convention:

• Begin with a letter
• Be 1–30 characters long
• Contain only A–Z, a–z, 0–9, _, $, and #
• Not duplicate the name of another object owned by the same user
• Not be an Oracle server–reserved word

CREATE TABLE Statement

CREATE TABLE [schema_name.] table_name

(column_name datatype [DEFAULT expr][, ...]);

1. schema_name: 

Schema_name is optional if we create on the same schema. if we want to create a table for another schema we must neet to specify the schema name where we want to create the table. 

2. table_name: 

we must need to give a name to the table. it's mandatory to create a table

column_name: column name is mandatory to create a table.

3. datatype: 

The column data type is mandatory to create a table. If the datatype is varchar2 size must be specified after the datatype

4. DEFAULT expr: 

Specifies a default value if a value is omitted in the INSERT statement. this is not a mandatory part of CREATE TABLE statement

Example

create table hr.customer_info

(

  customer_id    number(4),

  customer_name  varchar2(30) constraint customer_name_nn not null,

  customer_phone       varchar2(16),

  credit_limit       number, 

  location_id      number(4),

  creation_date date default sysdate);

);

Create Table - By Copying all columns with Data from another table 

CREATE TABLE new_table

AS (SELECT * FROM old_table);

Create Table - By Copying all columns without Data from another table 

CREATE TABLE new_table

  AS (SELECT * 

      FROM old_table WHERE 1=2);