Data Definition Language Commands in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Data Definition Language (DDL) Commands in Oracle with Examples

In this article, I am going to discuss Data Definition Language (DDL) Commands in Oracle with Examples. Please read our previous article, where we discussed Datatypes in Oracle Database.

Data Definition Language (DDL) Commands in Oracle:

Data Definition Language (DDL) Commands in Oracle are used to define the database objects such as Tables, Views, Stored Procedures, Stored Functions, Triggers, etc. That means DDL statements in Oracle are used to ALTER or Modify a database or table structure and schema. The most important point that you need to remember is DDL Commands in Oracle are working on the structure of a database object, not on the data of a table.

DDL contains five commands. They are as follows.

1. Create
2. Alter
3. Rename
4. Truncate
5. Drop

The following three DDL Commands are the latest feature in Oracle

1. Recyclebin
2. Flashback
3. Purge

Let’s discuss each of these Data Definition Language Commands in detail with Examples.

CREATE DDL Command in Oracle:

The CREATE DDL command in the Oracle database is used to create a new database object such as Table, View, Stored Procedure, Stored Function, Trigger, etc. The syntax to create a table is shown below. 

Example:

Let us see how to create a table with an example. Let us first log in to the database using the SQL Plus client tool.

Step1: Login to the Oracle database

Open SQL Plus editor and then type the username and password and press the enter button as shown in the below image.

Step2: Clear the screen

To clear the SQL Plus editor screen, type Clear screen and press the enter button as shown in the below image.

Step3: Creating Employee table

Let us create the Employee table in the Oracle database by using the following CREATE DDL command.

CREATE TABLE Employee
( 
    Id INT, 
    Name  CHAR(100), 
    Salary NUMBER(8, 2) 
);

So, type the above CREATE DDL Command in SQL Plus editor and press the enter button as shown in the below image.

You can see the “Table created” message and that means the Employee table is created successfully.

How to view the list of tables in the oracle database?

To view the list of tables in the Oracle database, we need to use the following syntax.

SELECT * FROM TAB;

Here, TAB is a pre-defined / system-defined table that shows the list of tables in a database. Let us execute the above query in SQL Plus editor and see the output as shown in the below image. You can see, it is showing one table i.e. Employee that we just created.

How to view the structure of a table in the oracle database?

To view the structure of a table in the Oracle database, we need to use the following syntax.

Syntax: DESC <TABLE NAME>; (Desc = Describe)

Example: DESC EMPLOYEE;

Let us execute the above DESC Command in SQL Plus Editor and see the output as shown in the below image.

Note: As we progress in this course, later we will discuss how to create Views, Stored Procedures, Stored Functions, Triggers, etc. using the CREATE DDL command in Oracle.

Rules for creating a table in Oracle:

To create a table in the Oracle database, we must have certain information in hand i.e. the table name, the column name, the column data types, and the column sizes. All this information can be modified later using DDL commands. So, while creating a table in Oracle, we must follow the following rules.

1. The Table name must begin with a letter A-Z or a-z
2. The Table name can contain numbers and underscores
3. The name of the table can be in UPPER or lower case
4. The maximum length of the table name can be 30 characters
5. We cannot use the same name of another existing object in our schema
6. The table name must not be a SQL reserved word.
7. Don’t provide space in the table name. If you want to provide space in a table name then you can use the underscore symbol.
8. A table should contain a minimum of 1 column and a maximum of 1000 columns.

ALTER DDL Command in Oracle:

This command is used to change or modify the structure of a database object i.e. Table, Views, Stored Procedures, Stored Functions, Triggers, etc. In Oracle, using the ALTER DDL command we can perform the following operations on an existing table.

1. Increase/decrease the length of a column.
2. Change the data type of a column.
3. Change NOT NULL to NULL or NULL to NOT NULL.
4. Used to add a new column to an existing table.
5. Used to drop an existing column.
6. Add a new constraint.
7. Used to drop an existing constraint on a table.
8. To change the column name of a table.

To change/modify the structure of a table, we can use the following sub-commands of Alter Data Definition Language (DDL) Command in Oracle.

1. ALTER – MODIFY
2. ALTER – ADD
3. ALTER – RENAME
4. ALTER – DROP

Example: We are going to use the same Employee table that we just created using the DDL CREATE command to understand ALTER DDL Command.

ALTER – MODIFY DDL Command in Oracle:

This command in the Oracle database is used to change a data type from an old data type to a new data type and also to change the size of the data type of a column. The syntax to use this ALTER – MODIFY command is given below.

ALTER TABLE <TABLE NAME> MODIFY <COLUMN NAME> <NEW DATATYPE> [NEW SIZE];

Example: Changing the length of a column 

Let us see how we can change the width of a column using the Alter Modify DDL command in the Oracle database. In our Employee table, the column Name length is CHAR(100). Let’s change the length to CHAR (200). To do so, we need to use the Alter Modify DDL command as shown below.

ALTER TABLE EMPLOYEE MODIFY NAME CHAR(200);

Let us execute the above ALTER MODIFY command in the SQL Plus editor as shown in the below image. You can see, we are getting Table altered message which means the structure of the table is altered or you can say modified.

Now, if you verify the structure of the table using DESC EMPLOYEE; command, you will see that the Name column length is changed to 200 from 100 as shown in the below image.

Note: When we are increasing the length of a column, we will not face any problem but while decreasing the length, if the table contains data in it we cannot decrease the length less than the max existing characters in the column.

Example: Changing the data type of an existing column

If you want to change the data type of an existing column, then you can also use the ALTER MODIFY DDL command in the Oracle database. For example, currently, the Name column data type is CHAR and our requirement is to change the data type from CHAR to NVARCHAR2. To do so, we need to use the ALTER MODIFY command as shown below.

ALTER TABLE EMPLOYEE MODIFY NAME VARCHAR2(200);

Let us execute the above ALTER MODIFY DDL command in the SQL Plus editor as shown in the below image. You can see, we are getting the “Table altered” message that means the structure of the table is altered or you can say modified.

Now, if you verify the structure of the table using DESC EMPLOYEE; command, you will see that the Name column data type is changed to VARCHAR2 from CHAR as shown in the below image.

ALTER – ADD DDL Command in Oracle:

If you want to add a new column to an existing table in the Oracle database, then you need to use the ALTER ADD DDL Command. The syntax to use the ALTER ADD DDL command in Oracle is given below.

ALTER TABLE <TABLE NAME> ADD <NEW COLUMN NAME> <DATATYPE>[SIZE];

Example: Suppose, now you want to add the Address column to the existing Employee table. Then you need to use the ALTER ADD DDL command in Oracle as follows.

ALTER TABLE EMPLOYEE ADD ADDRESS VARCHAR2(100);

Let us execute the above ALTER ADD DDL command in the SQL Plus editor as shown in the below image. You can see, we are getting the “Table altered” message that means the structure of the table is altered or modified i.e. a new column is added to the EMPLOYEE table.

Now, if you verify whether the new Address column is added or not in the Employee table by using DESC EMPLOYEE; command as shown in the below image. You can see, ADDRESS column is added to the existing Employee table.

ALTER – RENAME DDL Command in Oracle:

Theis ALTER – RENAME DDL Command in Oracle is used to change the column name in a table. The syntax to use this command is given below.

ALTER TABLE <TABLE NAME> RENAME <COLUMN> <OLD COLUMN NAME> TO <NEW COLUMN NAME>;

Example: Suppose, you want to change the Address column from Address to EmpAddrees, then you can do this by using the ALTER – RENAME Command as follows.

ALTER TABLE EMPLOYEE RENAME COLUMN ADDRESS TO EMPADDREES;

Let us execute the above ALTER RENAME DDL command in the SQL Plus editor as shown in the below image. You can see, we are getting the “Table altered” message that means the structure of the table is altered or modified i.e. the Address column is changed to EmpAddress in the EMPLOYEE table.

Now, if you verify the same by using DESC EMPLOYEE; command as shown in the below image. You can see, Address column is changed to EmpAddress in the Employee table.

ALTER – DROP DDL Command in Oracle:

If you want to delete or drop a column from an existing table, then you need to use the ALTER DROP DDL Command in Oracle. The syntax to use the ALTER DROP DDL command is given below.

ALTER TABLE <TABLE NAME> DROP <COLUMN> <COLUMN NAME>;

Example: Suppose, you want to delete or drop the EmpAddrees column from the existing Employee table, then you need to use the ALTER DROP DDL command as follows.

ALTER TABLE EMPLOYEE DROP COLUMN EMPADDREES;

Let us execute the above ALTER DROP DDL command in the SQL Plus editor as shown in the below image. You can see, we are getting the “Table altered” message that means the structure of the table is altered or modified i.e. the EmpAddrees column is deleted from the EMPLOYEE table.

Now, if you verify the same by using DESC EMPLOYEE; command as shown in the below image. You can see, EmpAddrees column is deleted from the Employee table.

RENAME DDL Command in Oracle:

If you want to change the table name then you need to use the RENAME DDL Command in Oracle. The syntax to use the RENAME command is given below.

RENAME <OLD TABLE NAME> TO <NEW TABLE NAME>;

Example: Suppose, you want to change the name of the Employee table from Employee to EmployeeDetails, then you can do the same by using the RENAME DDL Command in the Oracle database as follows.

RENAME EMPLOYEE TO EMPLOYEEDETAILS;

Let us execute the above RENAME DDL command in the SQL Plus editor as shown in the below image. You can see, we are getting the “Table renamed” message that means the name of the table is changed from Employee to EmployeeDetails.

Now, let us try to verify the structure of the Employee table as shown in the below image. As the Employee table is renamed, so we are getting the object EMPLOYEE does not exist error message.

Now, let us try to verify the structure of the EmployeeDetails table as shown in the below image and you can see the structure of EmployeeDetails as expected.

Let us rename the table back to EMPLOYEE by executing the below RENAME statement.

RENAME EMPLOYEEDETAILS TO EMPLOYEE;

TRUNCATE DDL Command in Oracle:

If you want to delete all the records or rows from a table without any condition, then you need to use the Truncate DDL command in Oracle. So, using this TRUNCATE DDL command you cannot delete specific records from the table because this command does not support the “WHERE” clause. The syntax to use the TRUNCATE DDL command in Oracle Database is given below.

TRUNCATE TABLE <TABLE NAME>;

Example: Suppose, you want to delete all the records from the Employee table, then you need to use the TRUNCATE DDL command as shown below in oracle.

TRUNCATE TABLE EMPLOYEE;

Let us execute the above TRUNCATE DDL command in the SQL Plus editor as shown in the below image. You can see, we are getting the “Table truncated” message that all the rows from the Employee table are deleted.

Points to Remember while working with TRUNCATE Command:

1. It is used to delete all rows from a table at a time.
2. It is used for deleting rows but not columns.
3. Rows are deleted permanently.
4. Cannot delete a specific row from a table.
5. It is not supporting the “where” condition.
6. The truncate command will delete rows but not the structure of the table.

DROP DDL Command in Oracle:

If you want to delete the table (rows & columns) from the database, then you need to use the DROP DDL command in oracle. The syntax to use the DROP command is given below.

DROP TABLE <TABLE NAME>;

Example: Suppose, you want to delete the Employee table from the database, then you need to use the DROP command as follows.

DROP TABLE EMPLOYEE;

Let us execute the above DROP DDL command in the SQL Plus editor as shown in the below image. You can see, we are getting the “Table dropped” message that means the EMPLOYEE table (rows & columns) is deleted from the database.

Note1: When a table is dropped all the dependent constraint which are associated with the table also gets dropped. 

Note: Before oracle10g enterprise edition when we drop a table from the database, the table is permanently dropped. But, from oracle 10g enterprise edition once we drop a table from the database then it will drop temporarily. And the user has a chance to restore the dropped table again into the database by using the following commands are,

1. Recyclebin
2. Flashback
3. Purge

RECYCLEBIN Command in Oracle:

It is a system-defined table that is used for storing the information about the dropped tables. It is similar to Windows Recyclebin. To view the structure of recycle bin, you can use the following syntax.

DESC RECYCLEBIN;

When you execute the above command, you will get the following output. As you can see in the below image, this table having various columns like OBJECT_NAME, ORIGINAL_NAME, OPERATION, TYPE, TS_NAME, etc.

How to view dropped tables in recycle bin?

To view the dropped tables in recycle bin we need to query the recyclebin as follows. You can specify the column names that are available in the recyclebin table.

SELECT OBJECT_NAME, ORIGINAL_NAME, OPERATION, TYPE, TS_NAME FROM RECYCLEBIN;

We have deleted the Employee table just before some time, so let us see whether that table info is available in the recyclebin table or not by executing the below query in the SQL Plus editor. You can see the ORIGINAL_NAME column value as EMPLOYEE and the OPERATION TYPE column value as DROP TABLE.

FLASHBACK Command in Oracle:

The Flashback command is used to restore the dropped table from recycle bin to the database. The syntax to use the Flashback command is given below.

FLASHBACK TABLE <TABLE NAME> TO BEFORE DROP;

Example: We have deleted the Employee table. Let us restore the deleted EMPLOYEE table into the database from the recycle bin using the following Flashback DDL command.

FLASHBACK TABLE EMPLOYEE TO BEFORE DROP;

Let us execute the above FLASHBACK DDL command in the SQL Plus editor as shown in the below image. You can see, we are getting a “Flashback complete” message that means the EMPLOYEE table is restored into the database from the recycle bin.

Now, you can verify the same by using the DESC EMPLOYEE; Command as shown in the below image. You can see, it is showing the structure of the Employee table.

Now, if you view the dropped tables in recycle bin, then you will see that no rows selected message as we have restored the Employee table from the recycle bin and there is no more dropped table in the recycle bin.

SELECT OBJECT_NAME, ORIGINAL_NAME, OPERATION, TYPE, TS_NAME FROM RECYCLEBIN;

So, when you execute the above query, you will get the following output.

PURGE Command in Oracle:

This DDL command is used to drop a table permanently i.e. it will drop a specific table from recycle bin permanently: The following is the syntax to use the PURGE command in Oracle.

PURGE TABLE <TABLE NAME>;

Example:

First, drop the table using the DROP command.
DROP TABLE EMPLOYEE;

Then permanently delete the table from the recycle bin by executing the below PURGE DDL command.
PURGE TABLE EMPLOYEE;

Once you delete the table from the recycle bin then you cannot restore the table from the recycle bin into the database by executing the below Flashback command. The above three steps are given in the below image. You can see, the first two statements are executed successfully but when we execute the third statement i.e. Flashback statement, we got an error saying “object not in RECYCLE BIN” and this makes sense because when we execute the second statement i.e. PURGE statement the table is deleted permanently from the recycle bin.

How to drop all tables from recycle bin permanently in Oracle?

To drop all tables from the recycle bin permanently we need to use the following syntax.

PURGE RECYCLEBIN;

How to drop a table from the database permanently?

To drop a table permanently from the database in oracle we need to use the following syntax.

DROP TABLE <TABLE NAME> PURGE;

Example: DROP TABLE EMPLOYEE PURGE;

In the next article, I am going to discuss Data Manipulation Language (DML) Commands in Oracle with Examples. Here, in this article, I try to explain Data Definition Language (DDL) Commands in Oracle with Examples and I hope you enjoy this Data Definition Language (DDL) Commands in Oracle with Examples article.