LL@dbfs1>set autotrace

Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

例：
1.创建表

create table t as select object_name,owner,object_type,object_id from dba_objects;

 
2.查看表中数据
set line 160 pagesize 500

col object_name for a50

col OWNER for a10

select * from t where rownum<10;

OBJECT_NAME                                        OWNER      OBJECT_TYPE                                                OBJECT_ID

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

ICOL$                                              SYS        TABLE                                                             20

I_USER1                                            SYS        INDEX                                                             46

CON$                                               SYS        TABLE                                                             28

UNDO$                                              SYS        TABLE                                                             15

C_COBJ#                                            SYS        CLUSTER                                                           29

I_OBJ#                                             SYS        INDEX                                                              3

PROXY_ROLE_DATA$                                   SYS        TABLE                                                             25

I_IND1                                             SYS        INDEX                                                             41

I_CDEF2                                            SYS        INDEX                                                             54

LL@dbfs1>select count(*) from t;

  COUNT(*)

----------

     15774

 新建的表有15774行数据。

 3.执行set autotrace traceonly explain

LL@dbfs1>set autotrace traceonly explain;

LL@dbfs1>delete from t where object_id<10000;

9806 rows deleted.    --已经给出提示删除了9806行

Execution Plan            --同时也给出了执行计划

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

Plan hash value: 3335594643

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

| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | DELETE STATEMENT           |      |     1 |    13 |    28   (0)| 00:00:01 |

|   1 |  DELETE                    | T    |       |       |            |          |

|*  2 |   TABLE ACCESS STORAGE FULL| T    |     1 |    13 |    28   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - storage("OBJECT_ID"<10000)

       filter("OBJECT_ID"<10000)

Note

-----

   - dynamic sampling used for this statement (level=2)

 4.确认此事表中数据

LL@dbfs1>set autotrace off;

LL@dbfs1>select count(*) from t;

  COUNT(*)

----------

      5968     --已经被删除了，如果此时你还没有推出事务，rollback 还是可以找回数据的。

LL@dbfs1>exit   --不幸的是你已经退出事务了，这时会触发数据库隐式提交，数据彻底丢失，如果你有flashback或者备份的话，你还是幸运的，否则你可能要卷铺盖走人了，如果不知道什么是隐式提交，就需要去百度一下了。

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

[oracle@dm01db01 ~]$ sqlplus ll/ll

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 16 10:00:50 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

LL@dbfs1>select count(*) from t;

  COUNT(*)

----------

      5968  --真正丢失了。呵呵


我们在需要查看执行计划的时候，要使用
explain plan for select * from t;
select * from table(dbms_xplan.display);