FLASHBACK,PURGE,DELETE,DROP,TRUNCATE WITH Examples

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TBL_NEST                       TABLE
PNUM                           TABLE
EMP4                           TABLE
BIN$LswCjbjBRTGpLwo1oLywCw==$0 TABLE
BIN$2fKrqxsUS8WWDQLXwtfXwg==$0 TABLE
BIN$/uITtvy1RYqYBEy/D6A4Gw==$0 TABLE
BIN$yvG6FJmfS2WY7rIVVq2fHQ==$0 TABLE
BIN$NZ+wxPeUQ72R87M92KErqA==$0 TABLE

12 rows selected.

SQL> purge emp4;
purge emp4
      *
ERROR at line 1:
ORA-38302: invalid PURGE option


SQL> purge recyclebin;

Recyclebin purged.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TBL_NEST                       TABLE
PNUM                           TABLE
EMP4                           TABLE

7 rows selected.

SQL>
SQL> create table emp as select * from emp;
create table emp as select * from emp
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> ed
Wrote file afiedt.buf

  1* create table emp45 as select * from emp
SQL> /

Table created.

SQL> select * from emp45;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> drop table emp4;

Table dropped.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TBL_NEST                       TABLE
PNUM                           TABLE
EMP45                          TABLE
BIN$bQi4LM2JQci4Y5VlD+AwwA==$0 TABLE

8 rows selected.

SQL> flashback table emp45 before drop;
flashback table emp45 before drop
                             *
ERROR at line 1:
ORA-00905: missing keyword


SQL> ed
Wrote file afiedt.buf

  1* flashback table emp45 before to drop
SQL> /
flashback table emp45 before to drop
                             *
ERROR at line 1:
ORA-00905: missing keyword


SQL> ed
Wrote file afiedt.buf

  1* flashback table emp45 to before drop
SQL> /
flashback table emp45 to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN


SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TBL_NEST                       TABLE
PNUM                           TABLE
EMP45                          TABLE
BIN$bQi4LM2JQci4Y5VlD+AwwA==$0 TABLE

8 rows selected.

SQL> purge recycle;
purge recycle
      *
ERROR at line 1:
ORA-38302: invalid PURGE option


SQL> purge recyclebin;

Recyclebin purged.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TBL_NEST                       TABLE
PNUM                           TABLE
EMP45                          TABLE

7 rows selected.

SQL> create table emp45 as select * from emp;
create table emp45 as select * from emp
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TBL_NEST                       TABLE
PNUM                           TABLE
EMP45                          TABLE

7 rows selected.

SQL> select * from emp45;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> truncate table emp45;

Table truncated.

SQL> select * from emp45;

no rows selected

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TBL_NEST                       TABLE
PNUM                           TABLE
EMP45                          TABLE

7 rows selected.

SQL> delete table emp45;
delete table emp45
       *
ERROR at line 1:
ORA-00903: invalid table name

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TBL_NEST                       TABLE
PNUM                           TABLE
EMP45                          TABLE

7 rows selected.

SQL> delete emp45;

0 rows deleted.

SQL> select * from emp45;

no rows selected

SQL> drop table emp45;

Table dropped.

SQL> select * from emp45;
select * from emp45
              *
ERROR at line 1:
ORA-00942: table or view does not exist