DATA DEFINITION LANGUAGE IN ORACLE

DATA DEFINITION LANGUAGE ( DDL )

The DDL is used to create and destroy databases and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project.

 1. CREATE - It is used to create a new table in the database. 

Syntax - CREATE TABLE <table_name> (column_1 datatype, column_2 datatype,....);

Creating a Table from another Table/view

SQL> CREATE TABLE empbak AS SELECT * FROM EMP;

2. ALTER - Alter command is used to modify the definition (structure) of a table by modifying the definition of its columns. The ALTER command is used to perform the following function - Add,drop and modify table columns, Add and drop constraints, Enable and Disable constraints.

Adding a column in table

Syntax -  ALTER TABLE <tablename> ADD (COLNAME DATATYPE(SIZE),---);

SQL> ALTER TABLE emp ADD (DOB DATE);

Removing a column from table

Syntax - ALTER TABLE <tablename> DROP(col1,col2,...);

SQL> ALTER TABLE emp DROP DOB;

Renaming a column in Table

Syntax - ALTER TABLE <tablename> RENAME column <COLNAME> to <NEWCOLNAME>;

SQL> ALTER TABLE emp RENAME COLUMN sal to salary;
Note - We cannot rename multiple column at a time.

Increasing or Decreasing Precision of a column

Syntax - ALTER TABLE <tablename> MODIFY <COLNAME><datatype>;

SQL> ALTER TABLE emp MODIFY ename VARCHAR2(25);

3. DROP - It is used to drop database objects from database. In all databases at a time we are allowed to drop one database object only.

Syntax - Drop object-type object-name;

Example - Drop table c1;

Example - Drop index indexname;

Dropping a table

Before Oracle 10g if we drop any object then it would be permanently deleted, but now its possible to get it back.

Syntax - Drop table tablename;

Example -

SQL> drop table c1;

SQL> desc c1;

ERROR : object c1 doesnot exist

Get it back from Recycle Bin

Syntax - Flashback table tablename to before drop;

Example - 

SQL> flashback table c1 to before drop;

SQL> desc c1;

To drop permanently

Syntax - Drop table tablename purge;

Example - 

SQL> drop table c1 purge;

SQL> flashback table c1 to before drop;

ERROR : object not in RECYCLE BIN

RECYCLE BIN - Oracle 10g (Enterprise Edition) introduced Recycle Bin which is used to store dropped tables, it is also same as windows Recycle Bin. Recycle Bin is a system generated table. In Oracle, Oracle server automatically creates read only tables which stores information related to specific database object. This table are also called as Data Dictionaries. In Oracle Recycle bin is a Data Dictionary. In Oracle if you want to view all data dictionaries then we are using following "select" statement.

SQL> select * from dict;

Example -

SQL> create table c1(sno number(10));

SQL> drop table c1;

SQL> desc c1;

ERROR : object does not exists.

SQL>desc recyclebin;

SQL> select original_name from recyclebin;

ORIGINAL_NAME

-------------------------

c1

In Oracle we can also drop tables from Recycle bin by using "purge" command. 

For dropping single table at a time from Recycle bin - purge table tablename;

For dropping all table at a time from Recycle bin - purge recyclebin;

3. TRUNCATE - Oracle 7.0 introduced "Truncate" table command.
Syntax - Truncate table tablename;
Whenever we are using this command oracle server delete all rows permanently from a table.
Example -
SQL> create table test as select * from emp;
SQL> select * from test;
SQL> truncate table test;
Checking -
SQL> select * from test;
no rows selected;
SQL> desc test;

4. RENAME - It is used to renaming a table.
Syntax - rename oldtablename to newtablename;
Example -
SQL> create table c1(sno number(10));
SQL> rename c1 to c2;
SQL> desc c2;

Note - In all databases by default all DDL commands are automatically committed.