Oracle Create table Syntax,Tip and Examples

Last updated on September 7th, 2018 at 04:17 am

This article talks how to create table in Oracle,primary key ,Foreign keys,create table syntax in oracle witrh Examples. This will be very useful for Oracle DBA’ s and Developer both.They play with it many times in the day and an good knowledge can definitely help them expedite the task.They often get confused the datatype and what to use in what circumstances. Here i am trying to give overview of all the useful stuff for Oracle database tables

 

What is Oracle database Table?

-Tables are the basic unit of data storage in an Oracle Database. Data is stored in rows and columns.

-A table holds all the necessary about something in the real world

-A table contains a set of column. A column represent one kind of data in the table For example, salary column in EMP table will have the salaries

  • A row is a collection of column information corresponding to a single record.

Next we will talking in detail about Oracle create table statement

 

How to create table in  oracle

To create a table in the database, we must have following information

  • The table name
  • Table type
  • Constraints
  • table storage parameter

Let’s look at each of these in details

Table Naming Conventions for Oracle create table

  • The name you choose for a table must follow these standard rules:
  • The name must begin with a letter A-Z or a-z
  • Can contain numbers and underscores
  • Can be in UPPER of lower case
  • Can be up to 30 characters in length
  • Cannot use the same name of another existing object in your schema
  • Must not be a Oracle server and SQL reserved word
  • Column name, column data types, and column sizes.

Column Naming Conventions –

  • The name you choose for a column must follow these standard rules:
  • The name must begin with a letter A-Z or a-z
  • Can contain numbers and underscores
  • Can be in UPPER of lower case
  • Can be up to 30 characters in length
  • Cannot use the same name of another existing object in your schema
  • Must not be a Oracle server and SQL reserved word

 

Data types

oracle datatypes

Character

-CHAR, NCHAR, VARCHAR2 & NVARCHAR2.

  • The CHAR datatype is a fixed-length alphanumeric string which has a maximum length in bytes.

-When creating a CHAR datatype, the database will preserve space for the incoming data and if the data is shorter than maximum size, it will be space-padded to the right

-The VARCHAR2 datatype is a variable-length alphanumeric string, which has a maximum length in bytes. It can store up to 4000 bytes.

-NCHAR and NVARCHAR2 are Unicode datatypes that store Unicode character data. The character set of NCHAR and NVARCHAR2 datatypes can only be either AL16UTF16 or UTF8 and is specified at database creation time as the national character set. AL16UTF16 and UTF8 are both Unicode encoding.

Number

-NUMBER

-The NUMBER datatype stores number with precision and scale.

-Numeric datatypes store negative and positive integers fixed-point numbers and floating-point numbers

  • When a column is defined as NUMBER (6, 2), the range of values can be stored from –9999.99 to 9999.99. Oracle rounds the floating-point numbers.

Date and Time

–DATE, TIMESTAMP (with time zone or local time zone),

-The DATE data type is used to store date and time information.

-This data type has a number of specific functions for manipulating, formatting and viewing its data.

  • The DATE data type holds storage of seven bytes and has information about century, year, month, day, hours, minutes & seconds.

  • The NLS_DATE_FORMAT parameter can be changed to control the viewing of the data. The SYSDATE function returns the current date

Large Objects

-BLOB(binary large object), CLOB(character large object), NCLOB & BFILE

-Columns of these datatypes can store unstructured data including text, image, video, and spatial data. -The CLOB datatype can store up to eight terabytes of character data using the CHAR database character set.

-The BLOB datatype is used to store unstructured binary large objects such as those associated with image and video data where the data is simply a stream of “bit” values.

-The BFILE data type value works as a file locator or pointer to file on the server’s file system. The maximum file size supported is 8TB to 128TB.

Long

Variable length character data up to 2 G

rowid

A 64 base number system representing  the unique address of the row in the table

Type of table

 

oracle create table

Ordinary (heap-organized) table

-This is the basic, general purpose type of table.

-Its data is stored as an unordered collection (heap)

Clustered table

-A clustered table is a table that is part of a cluster.

-A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.

Index-organized table

-Unlike an ordinary (heap-organized) table, data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner.

-Besides storing the primary key column values of an index-organized table row, each index entry in the B-tree stores the nonkey column values as well.

Partitioned table

-Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions, or even subpartitions.

  • Each partition can be managed individually, and can operate independently of the other partitions, thus providing a structure that can be better tuned for availability and performance.

External Table

External tables allow Oracle to query data that is stored outside the database in flat files.

Global temporary table

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction

Constraints and Rules

You can specify rules for each column of a table. These rules are called integrity constraints. One such example is a not null integrity constraint. This constraint forces the column to contain a value in every row. These rules are enforced placed for each column or set of columns. Whenever the table participates in data action, these rules are validated and raise exception upon violation.

A constraints can be one of the following:

  • a column-level constraint

Column-level constraints refer to a single column in the table and do not specify a column name (except check constraints). They refer to the column that they follow.

  • a table-level constraint

Table-level constraints refer to one or more columns in the table. Table-level constraints specify the names of the columns to which they apply. Table-level CHECK constraints can refer to 0 or more columns in the table.

The available constraint types are NOT NULL, Primary Key, Unique, Check, and Foreign Key.

Primary Key

A primary key is a column in a  table whose values uniquely identify the row in the table. A primary key value:

a) Must uniquely identify the row;

b) cannot have NULL values;

Oracle internally creates unique index to prevent duplication in the column values. It can be defined at the column or table level

A primary key can be multiple column also

Unique Key

It means uniqueness for the column. Oracle server will not allow duplicate values in the column having unique constraints. Oracle internally creates unique index to prevent duplication in the column values. But it allows some values to be null. It can be defined at the column or table level

Foreign Key

-A foreign key is a referential constraint between two tables.

-A foreign key constraint validates the values an INSERT or UPDATE against the values in another column, either in a different table or another column in the same

-A foreign key always defines a parent/child relationship. The “parent” is the column that is referenced in the foreign key and the “child” is the column or columns that contain the foreign key constraint.

-Generally, though, a foreign key is a field (or fields) that points to the primary key of another table.
-It can be defined at the column or table level

Check constraint

check constraint requires a value in the database to comply with a specified condition. Check constraint allows to impose a conditional rule on a column, which must be validated before data is inserted into the column. The condition must not contain a sub query or pseudo column CURRVAL NEXTVAL, LEVEL, ROWNUM, or SYSDATE.

Oracle allows a single column to have more than one CHECK constraint. In fact, there is no practical limit to the number of CHECK constraints that can be defined for a column.

It can be defined at the column or table level

Not Null

It means that a data row must have a value for the column specified as NOT NULL. The Oracle server will not allow rows to be stored that violate this constraint .It can only be defined at column level, and not at the table level.

Table storage parameter

Tables are stored in Tablespace in the database. If no Tablespace is specified, the table goes in user default Tablespace.

So in nutshell

1)You need to choose the appropriate column name and table name as per the standard given above. It is recommended to give column name and table name such that you can identify the purpose by checking the names

2) Secondly  you have to choose the right datatype for the table.
If it is a character string, we should be choosing VARCHAR2 (10). This is a variable string , we should choose the value appropriately. This means it can stores character till 10 bytes. The space utilized would be depending of the values in the column and there will be not wastage of space.

I would stress here one thing that when we give VARCHAR2(10) here is 10 is the number of the bytes not the character.
Number of character and bytes are similar when ASCII character are involved,but the equation get changed we start using using character other than ASCII

Right now VARCHAR2 supports till 4000 bytes , if you column is bigger than that, you can use LOB datatype,which can stores text for giga bytes.

If it is number, we use the datatype Number. Again we can choose the value appropiately. Example number(6,2) This can contain maximum from 999999.99

If you want to store date, Oracle has provided date datatype for storage.

3) Identify the constraint on the table . Decide what will be primary key and what all column would be not null.

Once we have all the required information, we can move forward with table creation

create table syntax in oracle

CREATE TABLE table_name
(
col1 datatype [ NULL | NOT NULL ],
col2 datatype [ NULL | NOT NULL ],

col_n datatype [ NULL | NOT NULL ]
)  tablespace <tablepace name>;

 

SYNTAX  for oracle create Table  primary key. It can be both defined at column level or table level

Table level
CREATE TABLE table_name
(
col1 datatype [ NULL | NOT NULL ],
col2 datatype [ NULL | NOT NULL ],

col_n datatype [ NULL | NOT NULL ]
constraint <name> primary key (col1,col2)
)  tablespace <tablepace name>

Column Level

CREATE TABLE table_name
(
col1 datatype [ NULL | NOT NULL ] constraint <name> primary key
,
col2 datatype [ NULL | NOT NULL ],

col_n datatype [ NULL | NOT NULL ]
)  tablespace <tablepace name>

Example:

CREATE TABLE SCOTT.TEST
(
Created_by date,          SOURCE CHAR(10),
REQUEST_ID CHAR(64) NOT NULL CONSTRAINT TEST_PK PRIMARY KEY,
COMMENTS VARCHAR(3000)
);

CREATE TABLE TESTEXP
(
INV_ID CHAR(7) NOT NULL,
ITEM_ID CHAR(7) NOT NULL,           CREATED  date,
WHO CHAR(7),          CONSTRAINT TEST1_PK PRIMARY KEY (INV_ID,ITEM_ID)
);

 

oracle create table primary key

Oracle create index to enforce the primary key constraints

oracle create table primary key index

Related:Top interview questions for Oracle sql

Explanation for table TEST1

1 The first column is called inv_id which is created as a char datatype (maximum 7 digits in length) and cannot contain null values
2 The second column is called item_id which is created as a char datatype (maximum 7 digits in length) and cannot contain null values
3 The third column is called created which is a date  datatype and also can  contain null values.
4 The fourth column is called who which is a cahe datatype and also can  contain null values.
5 Table level primary key constraint TEST1_PK is defined on the composite key (INV_ID, ITEM_ID)

 

SYNTAX  for oracle create Table statement FOREIGN Key. It can be both defined at column level or table level

CREATE TABLE table_name
(
col1 datatype [ NULL | NOT NULL ],
col2 datatype [ NULL | NOT NULL ],

col_n datatype [ NULL | NOT NULL ]
constraint <name> FOREIGN KEY (col1,col2) REFERENCES table(col1,col2)
)  tablespace <tablepace name>

CREATE TABLE table_name
(
col1 datatype [ NULL | NOT NULL ] constraint <name> primary key
,
col2 datatype [ NULL | NOT NULL ],

col_n datatype [ NULL | NOT NULL ]
)  tablespace <tablepace name>;

CREATE TABLE dept
( dept_id number(10) NOT NULL,
dept_name varchar2(50) NOT NULL,
CONSTRAINT dept_pk PRIMARY KEY (dept_id)
);

CREATE TABLE emp
( emp_no number(10) NOT NULL,
emp_name varchar2(50) NOT NULL,
dept_id number(10),
sal number(6),
CONSTRAINT emp_pk PRIMARY KEY (emp_no),
CONSTRAINT dept_fk
FOREIGN KEY (dept_id)
REFERENCES dept(dept_id) );

Explanation for table EMP

1 The first column is called EMP_NO  which is created as a number and cannot contain null values
2 The second column is called emp_name  which is created as varchar2(50) and cannot contain null values
3 The third column is called dept_id which is a date  number  .
4 The fourth column is called sal which is a number datatype and also can  contain null values.
5 Table level primary key constraint EMP_PK is defined on the  key (EMP_NO)
6 Table level Foreign Key constraints dept_fk  which references dept table dept_id

oracle create table foreign key

 

Privilege required to oracle create table

-You must have the create table system privilege in order to create a new table in your schema,

  • You must have the create any table system privilege in order  to create a table in another user’s schema, additionally, the owner of the table must have a quota for the tablespace that contains the table, or the UNLIMITED TABLESPACE system privilege

Related:How to write sql queries

Other characteristics associated with oracle database table

Cache/nocache

Use the CACHE clauses to indicate how Oracle Database should store blocks in the buffer cache. If you don’t specify anything in create table command ,it is by default nocache

DEFAULT

The value inserted into the column if the insert or update would leave the column value NULL.

<DEFAULT <value> | NULL>

PARALLEL

Specify PARALLEL if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.

Compress

This is used by oracle to store the table in  compressed format. This is available from 11g onwards

Comment a table or column

You can comment on table using the command

COMMENT ON TABLE <table_name> IS ‘<comment>’;

  

 

Creation of table using sub query(oracle create table as select)

-A table can be created from an existing table in the database using a sub query option.

  • The table is created with specified column names and rows retrieved by the select statement are inserted into the table

-if the table column specification is not given, table is created  with the same column as given in sub query

The below Oracle create table as select script creates a new table FND_BACKUP. All  the data in FND table is inserted into FND_BACKUP table

CREATE TABLE TEST2
AS
SELECT * FROM TEST

oracle create table as select

-Data can also be copied based on conditions or we can put rownum <1  condition to just get the table structure

-The column data type definitions including the explicitly imposed NOT NULL constraints are copied into the new table.

 

Data Dictionary Tables and Views

All the table and column information are stored in SYS.TAB$ and SYS.COL$ tables.  Oracle has provided data dictionary views  to get the information about table and columns

There are three categories of  views

How to List All Tables in Oracle

To list all tables owned by the current user, type:select tablespace_name, table_name from user_tables;

To list all tables in a database:

select tablespace_name, table_name from dba_tables;

To list all tables accessible to the current user, type:

select tablespace_name, table_name from all_tables

To describe the table in sqlplus

desc <table_name>

 

How to determine Table size

select
owner as “Schema”
, segment_name as “Object Name”
, segment_type as “Object Type”
, round(bytes/1024/1024,2) as “Object Size (Mb)”
, tablespace_name as “Tablespace”
from dba_segments
where segment_name=’<table_name>’;

Oracle create table changes in 12c

IDENTITY Columns
In Oracle Database 12c, We can define Table columns with SQL keyword IDENTITY which is a American National Standards Institute (ANSI) SQL keyword. Which are auto-incremented at the time of insertion (like in MySQL).

Example:
create table test
(
id number generated as identity,
name varchar2(100),
email varchar2(100),
password varchar2(100)firstname varchar2(100)lastname varchar2(100)
);

Sequence as Default Value
With Oracle Database 12c, we can directly assign sequence nextval as a default value for a column, So you no longer need to create a trigger to populate the column with the next value of sequence, you just need to declare it with table definition.It is a sort of autoincrement feature for a column in oracle just like mysql

Example:
create sequence tech_test_seq start with 1 increment by 1 nocycle;create table test
(
id number default tech_test_seq.nextval primary key
name varchar(30)
);

Metadata-Only DEFAULT Values

Before pre Oracle 11g, adding a new column to an existing table required all rows in that table to be modified to add the new column. So if the table is very large , alter table add column took substantial time and locking time as also more

With Oracle 11g introduced the concept of metadata-only default values. Adding a NOT NULL column with a DEFAULT clause to an existing table involved just a metadata change, rather than a change to all the rows in the table. Queries of the new column were rewritten by the optimizer to make sure the result was consistent with the default definition.

Read more about that in below article

How to alter table add column oracle

Oracle 12c takes this a step further, allowing metadata-only default values of both mandatory and optional columns. As a result, adding a new column with a DEFAULT clause to an existing table will be handled as a metadata-only change, regardless of whether that column is defined as NOT NULL or not.

This is a huge benefits for all.

Tables with number of rows and comments

The below query can be used to find the count of rows and comments in SCOTT schema

select tab.owner as schema_name,
tab.table_name as table_name,
obj.created,
obj.last_ddl_time as last_modified,
tab.num_rows,
tab.last_analyzed,
comm.comments
from all_tables tab
inner join all_objects obj
on obj.owner = tab.owner
and obj.object_name = tab.table_name
left outer join all_tab_comments comm
on tab.table_name = comm.table_name
and tab.owner = comm.owner
where tab.owner = ‘SCOTT’
order by tab.owner,
tab.table_name;

Practice Questions

1. For which two constraints does the Oracle Server implicitly create a unique index?

2. What does the TRUNCATE statement do?

3. Which constraint can be defined only at the column level?

4. Evaluate the SQL statement
DROP TABLE DEPT;
Which four statements are true of the SQL statement?

5. Which SQL statement defines a FOREIGN KEY constraint on the DEPTNO column of the EMP table?

6. Which statement explicitly names a constraint?


 

Hope you like this articles on oracle create table statement,command  ,syntax and tips.

 

Leave a Reply