DDL: DATA DEFINITION LANGUAGE

DDL Data Definition Language

CREATE 
ALTER 
   ADD
   MODIFY
   RENAME
   DROP
RENAME 
DROP
TRUNCATE 


TABLE

Database Design Document
Combination of rows and column with data or structure.

CREATE

Syntax : CREATE TABLE Table_Name( Column 1 Datatypes,Column 2 Datatypes); 

CREATE TABLE Student113
(
Sid Number,
Sname varchar(10),
SLocation varchar(10)
);

Table created

ALTER - ADD, MODIFY, RENAME, DROP

ADD

ALTER TABLE Student113 ADD Sdept_id Number(3);
ALTER TABLE Student113 ADD Dob Date;
ALTER TABLE Student113 ADD resume long;

MODIFY

ALTER TABLE STUDENT113 MODIFY Sdept_id char(2);

RENAME 

ALTER TABLE Student113 RENAME COLUMN RESUME TO FEEDBACK;

desc student111;

ALTER TABLE STUDENT113 Drop Column FEEDBACK;

TABLE ALTERED

RENAME : RENAME the table name into an another name here the name is student113 now rename student113 nme into student234

RENAME STUDENT113 TO STUDENT234;

Statement processed

DESC student234;

Drop : Drop command is used to delete the structure and data.

Drop table Student111;

Table Dropped 

Desc Student111;

Object to be described could not be found.-- because the structure and table is dropped.

TRUNCATE: Its used to delete the data only 

TRUNCATE table student234;

Table Truncated

The data only truncated table structure is same.

To View the dropped table:

Select * from recyclebin; -- this query is used to view the dropped table list.

To Retrieve the dropped table

FLASHBACK -- Its used to retrieve the dropped table

FLASHBACK TABLE student111 TO BEFORE DROP RENAME TO student222;
    
FLASHBACK TABLE students TO BEFORE DROP; -- this is the syntax for the flashback here students is table name 

PURGE -- Its used to delete permanently in the database

Syntax : purge table t1;

The table t1 is already dropped but it stores in the recyclebin if the above syntax is used to delete the table inthe reycle bin also.


FLASHBACK TABLE t1 TO BEFORE DROP;

ORA-38305: object not in RECYCLE BIN -- now t1 objects not in the recycle bin.