Oracle_Create Table


admin DataBase, Oracle

Hello guys,
In this blog, we will discuss different ways of creating a table in Oracle database.
The first question is, what is a table?
A table is an object in the database which actually holds the data in the form of rows and columns.
So whenever we talk about data, we actually talk about tables exist in the database software.

To create a table in your own schema (we will talk about schema in detail in up-coming blogs, here you can understand it as a user account in the database) you must have CREATE TABLE system privilege. Also, if you want to create a table in another schema, you must have CREATE ANY TABLE system privilege. Also, the owner of the schema must have space quota to contain the table data on the “default tablespace” which was assigned while creating user account (schema) in the Oracle database.
Let me know if you want me to write a separate detailed blog about Tablespace and Datafile.

CREATE TABLE schema.table_name (
column_name datatype (data width),
column_name2 datatype (data width)

Note: you can omit schema if you are creating table in your own schema. If you want to create a table another user’s schema, mentioned that schema.

A. Creating a table in your own schema-

FIRST_NAME varchar2 (20),
LAST_NAME varchar2 (20),
EMAIL varchar2 (50));

B. If you want to create a table in dbgeek’s schema than you need to mentioned dbgeek in the query before the table name.

CREATE TABLE dbgeek.emp (
FIRST_NAME varchar2 (20),
LAST_NAME varchar2 (20),
EMAIL varchar2 (50));

Let’s do some more examples and see how easy it is.

Creating a new table with columns from existing table (Oracle database )
For the demonstration purpose, I have already created a table EMP with some data in it. I will use this table as existing table in the following examples.

1) With all columns from existing table with data

Using the above query, you can create new table emp2 with same columns and data in table emp.
As we have not mentioned column name, data type and data width in the Create Table statement so the columns and their data type will be same what emp table has. So, we can say that emp2 table will have the same columns and data returned by SELECT statement after AS keyword.

2) With all columns from existing table without data

In the above query, I added a condition in the where clause that is considered False all the time. 1 can never be equal to 2. It means Select query will return 0 rows.
Though emp3 table has been created with the columns but there are no rows as SELECT statement returns 0 rows.

3) With selected columns from existing table with data.

I assume that the output of above query is clear to you.
Table Emp3 is created with First_Name and Last_Name columns with data records in these columns. You can run the select statement after the AS keyword first and then a select statement to emp3 table. The output will be same.

4) With selected columns from existing table without data.

Do I still need to explain above query?

If you have any questions please comment below or drop me an email.

If you like this blog, do share with your friends and colleagues on social media.
For more updates join my facebook group and do like my facebook page.

Thank you,

Sharing is caring!

You May Also Like..


Parentheses in SQL

Hey time travellers, Though Parentheses are used pretty much everywhere in SQL but in this blog, we will talk about […]

Logical Conditions

Oracle – Logical Conditions

Hey time travellers, In this article, we will be discussing Logical Conditions. It is a very important aspect of SQL. […]


Oracle – IN Condition

Hey people, In this Oracle tutorial, we will learn how to use IN condition in SQL. IN condition is used […]

Leave a Reply

Your email address will not be published. Required fields are marked *