Drop,recyclebin,flashback,purge - Drop and Truncate - Deepak YT

 Drop,recyclebin,flashback,purge - Drop and Truncate

Drop:

              It is used to remove database objects from database. In all relational database we are allow to drop only one database object at a time.

    Syntax: drop objecttype objectname;

·        Drop table tablename;

·        Drop view viewname;

 

Get it back from recyclebin:

Syntax: flashback table tablename to before drop;

To drop table permanently:

Syntax: drop table tablename purge;

 

Example:      (dropping a table -> cracle 10g Enterprise Edition)

                      SQL> drop table first;

                      Table dropped.

 

Get it back the table from recyclebin

SQt> flashback table first to before drop;

 

 

Testing

Method 1

                            SQL> desc first;

                        Error: Object first does not exist.

Method 2

                          SQL> flashback table first to before drop;

                          Error: object not in Recylebin.

Oracle 10g enterprise edition introduced recycle bin which is used to store dropped tables. It is also same as window recyclebin. If we want to view recyclebin then we are using following syntax.

 

Syntax: show recyclebin;

 

Oracle recyclebin is same as windows recyclebin that's why we can also remove single table or we can also remove all tables at a time from recyclebin by using purge comment.

Recylebin is a read only table whenever we are installing oracle then automatically so many read only tables are created. These read only tables are also called as "Data Dictionaries".

In oracle, we can also drop tables from recyclebin using "purge" command.

 

To Drop particular table from Recyclebin:

                                              

                      Syntax: purge table tablename;

Example

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

SQL> drop table first;

SQL> desc recyclebin;

SOL> select original_name from recyclebin;

Original_name First :-

SQL> purge table first;

Testing

SQL> select original_name from recyclebin;

No rows selected;

To Drop all tables from Recydebin:

Syntax: purge recyclebin;

Example

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

SOL> create table second (sno number (10));

SQL> drop table first;

SQL> drop table second;

SQL> desc recyclebin;

SQL> select original_namne from recyclebin;

Original_name

First

Second

SOL> purge recyclebin;

Testing

SOL> select original_name from recycle bin;

No rows selected

Note: In orale through recyclebin we can get it back dropped table but we cannot get_it back dropped

column.

 

 

Truncate: Oracle 7.0 introduced truncate command, whenever we are using "truncate" command total data permanently deleted from table.

Syntax: truncate table tablename;

Example

SQL>create table first as select from emp;

SQL> select from first;

SOL> truncate table first;

Testing

SQL> select from first;

No rows selected.

SOL> desc first;

Rename: It is used to rename a table and renaming a column also.

Syntax: rename oldtablename to newtablename;

Example: rename emp to empl;

Renaming a Column: (Oracle 9i)

Syntax: alter table <table name> rename column <old column name> to <new column name>;

Example

SQl> alter table emp rename column empno to sno;

SQL> select from emp;

Note: In oll database systems by default all DDl commands ore automatically committed.