Data Definition Language

Data Definition Language


1.    DDL (Data Definition Language)
2.    DML (Data Manipulation Language)
3.    DRL/DQL (Retrieval/query)
4.    TCL (Transaction Control Language)
5.    DCL (Data Control Language

Data Definition Language(DDL):

        Data definition language (DDL) statements enable you to perform these tasks:

•    Create, alter, and drop schema objects
•    Grant and revoke privileges and roles
•    Analyze information on a table, index, or cluster
•    Establish auditing options
•    Add comments to the data dictionary

    The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table.

    The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.

  Oracle implicitly commits the current transaction before and after every DDL statement.
Many DDL statements may cause Oracle to recompile or reauthorize schema objects.
DDL Statements are.






     It is used to create objects in the Database. like CREATE TABLE, CREATE FUNCTION, CREATE SYNONYM, CREATE VIEW. Etc
Syantax: CREATE TABLE [table name] ( [column definitions] ) [table parameters].

Ex:             create table emp (empno number(5) primary key,
                   name varchar2(20),
                   sal number(10,2),
                   job varchar2(20),
                   mgr  number(5),
                   Hiredate  date,
                   comm number(10,2));


  This command is used to modify an existing database object.It helps to add or delete  the columns ,rename the columns.
 Syntax to add a new column:

   ALTER  table tablename ADD columnname datatype

  To add  new columns  like addr, city, pin, ph, fax to employee table you can give the following statement

alter table emp add (addr varchar2(20), city varchar2(20), pin varchar2(10),ph varchar2(20));

syntax to Modifying the column with new datatype:
  ALTER table tablename MODIFY columnname new datatype.

   To modify the datatype and width of a column. For example we you want to increase the length of the column ename from varchar2(20) to varchar2(30) then give the following command.

alter table emp modify (ename varchar2(30));

syntax to renaming a column:
  ALTER table tablename RENAME  column oldcolumnname to newcolumnname

Ex: Alter table emp1 RENAME  column empno to en;

Syntax to drop  a column:
     ALTER  table tablename drop column columnname

Ex: Alter table emp1 DROP column job;

    Use the drop statement to drop tables, functions, procedures, packages, views, synonym, sequences, tablespaces etc.


      Drop table table name

  Ex: drop table emp2;

     Use the Truncate statement to delete all the rows from table permanently . It is same as “DELETE FROM ” except

•    Truncate does not generate any rollback data hence, it cannot be roll backed.
•    If any delete triggers are defined on the table. Then the triggers are not fired
•    It deallocates free extents from the table. So that the free space can be use by other tables.


    TRUNCATE  table tablename:

Ex;  TRUNCATE  table EMP1;

 If you do not want free space and keep it with the table. Then specify the REUSE storage clause like this

 TRUNCATE  table EMP1  REUSE  storage;

     Use the RENAME statement to rename a table, view, sequence, or private synonym for a table, view, or sequence.

•    Oracle automatically transfers integrity constraints, indexes, and grants on the old object to the new object.
•    Oracle invalidates all objects that depend on the renamed object, such as views, synonyms, and stored procedures and functions that refer to a renamed table.

     RENAME oldtablename to newtablename


To rename table emp2 to employee2 you can give the following command.

    rename emp2 to emp3;


Post a Comment