drop table purge

最新推荐文章于 2021-05-22 16:53:08 发布

原创最新推荐文章于 2021-05-22 16:53:08 发布 · 2.1k 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

sql/plsql基础知识

本文详细介绍了 Oracle 数据库中 PURGE 子句的作用及其使用方法。当使用 DROP TABLE PURGE 时，表将被永久删除且无法通过回收站进行恢复。文中还提供了实例演示如何使用 PURGE 和对比常规 DROP TABLE 的不同。

Oracle官方文档：Oracle Database SQL Language Reference

PURGE

Specify PURGE if you want to drop the table and release the space associated with it in a single step. If you specify PURGE, then the database does not place the table and its dependent objects into the recycle bin.

Caution:

You cannot roll back a DROP TABLE statement with the PURGE clause, nor can you recover the table if you have dropped it with the PURGE clause.

Using this clause is equivalent to first dropping the table and then purging it from the recycle bin. This clause lets you save one step in the process. It also provides enhanced security if you want to prevent sensitive material from appearing in the recycle bin.

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

1、drop table zml后,zml表被放入recycle bin.可以通过flashback table zml to before drop恢复drop掉的表，表中数据也可以被恢复。

2、drop table zml purge后zml表被直接删除，不可恢复

--------实例：----------

---------------drop table zml-------------------------
SQL> show parameter recycle;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle                  string
db_recycle_cache_size                big integer 0
recyclebin                           string      on
---
SQL> purge recyclebin;

回收站已清空。

SQL> show recyclebin;
SQL> create table zml(id int);

表已创建。

SQL> insert into zml values(1);

已创建 1 行。

SQL> drop table zml;

表已删除。

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------

ZML              BIN$2cOZCZj0Ry27btpd7ymTHg==$0 TABLE        2014-04-23:21:56:58
SQL> flashback table zml to before drop;

闪回完成。

SQL> select * from zml;

        ID
----------
         1

SQL>
-----------------drop table zml1 purge-------------------------
SQL> show recyclebin;
SQL> create table zml1(id int);

表已创建。

SQL> insert into zml1 values(2);

已创建 1 行。

SQL> drop table zml1 purge;

表已删除。

SQL> show recyclebin;
SQL>

标签

#oracel #drop table purge