SQL Oracle 中删除和删除清除的区别

Difference Between Drop And Drop Purge In Oracle

提问by Randy

I am using Oracle Database and I am a bit confused about Drop and Purge Commands. In fact for me both does the same thing. Removes the table with schema from database. What is the main difference between these two?

我正在使用 Oracle 数据库，但我对删除和清除命令有点困惑。事实上，对我来说，两者都做同样的事情。从数据库中删除具有架构的表。这两者之间的主要区别是什么？

1. Drop Table Tablename;
2. Drop Table Tablename Purge;

1. 删除表表名；
2. 删除表表名清除；

回答by Randy

Normally, a table is moved into the recycle bin (as of Oracle 10g), if it is dropped. However, if the purge modifier is specified as well, the table is unrecoverably (entirely) dropped from the database.

通常，如果一个表被删除，它会被移动到回收站（从 Oracle 10g 开始）。但是，如果还指定了清除修饰符，则该表将不可恢复地（完全）从数据库中删除。

回答by Vignesh

Oracle Database 10g introduces a new feature for dropping tables. When you drop a table, the database does not immediately release the space associated with the table. Rather, the database renames the table and places it in a recycle bin, where it can later be recovered with the FLASHBACK TABLE statement if you find that you dropped the table in error. If you want to immediately release the space associated with the table at the time you issue the DROP TABLE statement, then include the PURGE clause as follows. DROP TABLE employees PURGE; Specify PURGE only 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. NOTE: You cannot roll back a DROP TABLE statement with the PURGE clause, and you cannot recover the table if you drop it with the PURGE clause. This feature was not available in earlier releases.

Oracle 数据库 10g 引入了一个用于删除表的新特性。删除表时，数据库不会立即释放与该表关联的空间。相反，数据库会重命名该表并将其放入回收站，如果您发现错误删除了该表，稍后可以使用 FLASHBACK TABLE 语句将其恢复到回收站中。如果要在发出 DROP TABLE 语句时立即释放与表关联的空间，请按如下方式包含 PURGE 子句。DROP TABLE 员工清除；仅当您想在单个步骤中删除表并释放与其关联的空间时才指定 PURGE。如果指定 PURGE，则数据库不会将表及其依赖对象放入回收站。注意：您不能使用 PURGE 子句回滚 DROP TABLE 语句，如果使用 PURGE 子句删除该表，则无法恢复该表。此功能在早期版本中不可用。

回答by phil

A link to asktom article: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:32543538041420

asktom 文章的链接：http://asktom.oracle.com/pls/asktom/f ?p=100:11:0::::P11_QUESTION_ID: 32543538041420

Basically with oracle10, all dropped tables go into recycle bin from where they can be undropped. With purge you basically skip the recycle bin part and you drop the table without an option to undo the action.

基本上在 oracle10 中，所有被删除的表都会进入回收站，从那里它们可以被取消删除。使用清除，您基本上可以跳过回收站部分，并且您删除表而没有撤消操作的选项。

回答by Devaraj B

SQL>drop table table_name;

This command deletes the table named table_name, but internally it is moved to recycle bin of oracle (which is just similar to deleting any file/folder using Deletekey on windows os).
Benefits:
1. We will be able to restore the above deleted table if required.
Drawbacks:
1. Still occupies some amount of memory.

该命令删除名为table_name的表，但在内部将其移动到oracle的回收站（这与Deletewindows os上使用key删除任何文件/文件夹类似）。
好处：
1. 如果需要，我们将能够恢复上面删除的表。
缺点：
1. 仍然占用一定的内存量。

SQL>drop table table_name purge;

This command deletes the table table_name completely from the database (which is similar to deleting any file/folder using Shift+ Deletekey on windows OS).

此命令从数据库中完全删除表 table_name（这类似于在 Windows 操作系统上使用Shift+Delete键删除任何文件/文件夹）。

The Benefits and Drawbacks will be vice-verse of the above.

优点和缺点将与上述相反。

回答by Danish Rizvi

The below statement will drop the table and place it into the recycle bin.

下面的语句将删除该表并将其放入回收站。

DROP TABLE emp_new;

删除表 emp_new;

The below statement will drop the table and flush it out from the recycle bin also.

下面的语句将删除表并将其从回收站中清除。

DROP TABLE emp_new PURGE;

删除表 emp_new PURGE;

回答by sandy v

It's clear from @Randy comment, just to add on:

从@Randy 的评论中可以清楚地看出，补充一下：

(1) Drop Table Tablename:

(1) Drop Table 表名：

Without purge, the table can be in the RECYCLEBIN or USER_RECYCLEBIN; which can be restored using the command FLASHBACK. This is similar to files we delete in our windows desktop, which move to the recycle bin, and can be restored back.

没有purge，表可以在 RECYCLEBIN 或 USER_RECYCLEBIN 中；可以使用命令恢复FLASHBACK。这类似于我们在 Windows 桌面中删除的文件，它们移动到回收站，并且可以恢复回来。

(2) Drop Table Tablename Purge:

（2）Drop Table Tablename Purge：

If Dropis given along with Purge, its tablespace is released and cannot be restored. (Like Shift+Delete in desktop)

如果Drop与 一起给出Purge，则其表空间被释放且无法恢复。（就像桌面上的 Shift+Delete）

Following example gives practical example:

以下示例给出了实际示例：

create table test_client (val_cli integer, status varchar2(10));
drop table test_client ;

select tablespace_name from all_tables where owner = 'test' and table_name = 'TEST_CLIENT';

SELECT * FROM RECYCLEBIN where ORIGINAL_NAME='TEST_CLIENT';

SELECT * FROM USER_RECYCLEBIN where ORIGINAL_NAME='TEST_CLIENT';

FLASHBACK TABLE test_client  TO BEFORE DROP;

select tablespace_name from all_tables where owner = 'test' and table_name = 'TEST_CLIENT';

drop table test_client purge;

select tablespace_name from all_tables where owner = 'test' and table_name = 'TEST_CLIENT';

回答by Osho Balhariya

drop table table_name purge;

删除表表名清除；

By using this query what happened:- Normally, a table is moved into the recycle bin (as of Oracle 10g), if it is dropped.But still occupied the memory in database so to release the the memory for another object we can use the the Purge clause.

通过使用这个查询发生了什么： - 通常，一个表被移动到回收站（从 Oracle 10g 开始），如果它被删除。但仍然占用数据库中的内存，以便为另一个对象释放内存，我们可以使用清除条款。