Oracle之删除表或数据及恢复

原创杨露瑶 2023-02-10

1899

Oracle删除表或数据

删除数据一定要谨慎！！！！请反复衡量之后再进行删除工作！！！

Oracle删除表或数据

删除数据一定要谨慎！！！！请反复衡量之后再进行删除工作！！！

实验数据
delete

实际操作
恢复数据

drop table

实际操作
恢复数据
drop table总结

truncate table

实际操作
恢复数据

三者的区别

实验数据

数据为脚本随机生成，如有雷同，纯属巧合

create table record(id int, name varchar(10), tel varchar(11));

insert into record values('0','Tbgmk','18725414287');
insert into record values('1','Ryg','13321000188');
insert into record values('2','Kfnoz','15316579433');
insert into record values('3','Cmosg','15354528706');
insert into record values('4','Zrqs','15131084680');
insert into record values('5','Nanam','13832700434');
insert into record values('6','Udzxb','13538027910');
insert into record values('7','Ucc','18836668108');
insert into record values('8','Rqbjz','15377357269');
insert into record values('9','Idyj','15853685975');
insert into record values('10','Mptt','13021288102');
insert into record values('11','Yzzh','13627573136');
insert into record values('12','Hzz','14745510672');
insert into record values('13','Sacau','15726754855');
insert into record values('14','Uqdf','15108679721');
insert into record values('15','Rvuqa','13495497080');
insert into record values('16','Kewo','15241960321');
insert into record values('17','Tec','15359549735');
insert into record values('18','Miil','13185970785');
insert into record values('19','Qig','13659522032');
insert into record values('20','Ndb','18738787678');
insert into record values('21','Mrsv','15718163815');
insert into record values('22','Vmpbh','15911516823');
insert into record values('23','Qte','13733268187');
insert into record values('24','Dylla','13116176666');
insert into record values('25','Agf','13473343336');
insert into record values('26','Eaa','13327654449');
insert into record values('27','Qmhzi','13258129813');
insert into record values('28','Dvtbe','13834896734');
insert into record values('29','Oslzb','14703836467');
insert into record values('30','Brtg','15303132610');
insert into record values('31','Nicx','13166610973');
insert into record values('32','Ngou','15355196445');
insert into record values('33','Arlr','15626234798');
insert into record values('34','Uwdul','13786498170');
insert into record values('35','Ans','13790692704');
insert into record values('36','Psww','14760269349');
insert into record values('37','Qcwg','18502356990');
insert into record values('38','Tdpfl','15619305773');
insert into record values('39','Xouz','13913072036');
insert into record values('40','Pdn','13292373826');
insert into record values('41','Ngql','13925531544');
insert into record values('42','Voos','18785623253');
insert into record values('43','Xdnfd','18881253135');
insert into record values('44','Euc','15239951681');
insert into record values('45','Iin','15265645228');
insert into record values('46','Mbre','15607776194');
insert into record values('47','Yqmc','18942280994');
insert into record values('48','Djtx','13353136184');
insert into record values('49','Yxj','15991474079');

SQL> select count(*) from record;

  COUNT(*)
----------
	50

delete

官方文档 - delete

用于删除表中的某行或整个数据表中的数据

语法：
DELETE FROM <table/view> [WHERE <condition>]

注意事项：

如果有外键关联，则删除数据之前,需先删除外键关联数据

delete只会删除表中的数据不会删除表

实际操作

-- 删除表中的部分数据
SQL> select * from record where id >= 40;

	ID NAME       TEL
---------- ---------- -----------
	40 Pdn	      13292373826
	41 Ngql       13925531544
	42 Voos       18785623253
	43 Xdnfd      18881253135
	44 Euc	      15239951681
	45 Iin	      15265645228
	46 Mbre       15607776194
	47 Yqmc       18942280994
	48 Djtx       13353136184
	49 Yxj	      15991474079

10 rows selected.

SQL> delete from record where id >= 40;

10 rows deleted.

SQL> select * from record where id >= 40;

no rows selected

-- 删除表中所有数据
SQL> select count(*) from record;

  COUNT(*)
----------
	40

SQL> delete from record;

40 rows deleted.

SQL> select count(*) from record;

  COUNT(*)
----------
	 0

SQL> select table_name,tablespace_name,status from dba_tables where table_name = 'RECORD';

TABLE_NAME		TABLESPACE_NAME		STATUS
----------   	 ---------------        ------
RECORD			SYSTEM 			    VALID

由此可见，delete一般用于删除表中被淘汰的部分数据或者所有记录，但并不会对表的存在以及结构有所影响。delete与insert类似，执行后需要进行commit才算完成对数据的删除操作。

恢复数据

-- 情况一：delete数据后还未commit，使用rollback命令即可闪回
SQL> select count(*) from record;

  COUNT(*)
----------
	50

SQL> delete from record where id >= 40;

10 rows deleted.

SQL> rollback;

Rollback complete.

SQL> select count(*) from record;

  COUNT(*)
----------
	50

-- 情况二：delete数据且已经commit，但该表不属于sys或system用户，使用flashback闪回
SQL> select count(*) from record;

  COUNT(*)
----------
	50

SQL> !date
Wed Feb  8 03:38:34 EST 2023

SQL> delete from record where id >= 40;

10 rows deleted.

SQL> commit;

Commit complete.

SQL> alter table record enable row movement;

Table altered.

SQL> flashback table record to timestamp to_timestamp('2023-02-08 03:38:34','yyyy-mm-dd hh24:mi:ss');
flashback table record to timestamp to_timestamp('2023-02-08 03:38:34','yyyy-mm-dd hh24:mi:ss')
                *
ERROR at line 1:
ORA-08185: Flashback not supported for user SYS


SQL> alter table record disable row movement;

Table altered.

SQL> conn scott/tiger
Connected.
SQL> conn / as sysdba
Connected.
SQL> create table scott.record as select * from record;

Table created.

SQL> select count(*) from scott.record;

  COUNT(*)
----------
	40

SQL> !date 
Wed Feb  8 03:43:39 EST 2023

SQL> delete from scott.record where id >= 30;

10 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from scott.record;

  COUNT(*)
----------
	30

SQL> alter table scott.record enable row movement;

Table altered.

SQL> flashback table scott.record to timestamp to_timestamp('2023-02-08 03:43:39','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SQL> alter table scott.record disable row movement;

Table altered.

SQL> select count(*) from scott.record;

  COUNT(*)
----------
	40

实验说明：恢复操作的两种方案分别对应delete数据后是否提交，且有可能只适用于测试环境，生产环境实时产生新数据可能会很快就将数据块覆盖，所以删除数据一定要谨慎！

注意：commit之后的flashback恢复只有在该表不属于sys或system用户的情况下才有效，可能是防止对sys或system用户执行后引起数据库崩溃而设置的限制

drop table

官方文档 - drop table

Use the DROP TABLE statement to move a table or object table to the recycle bin or to remove the table and all its data from the database entirely.

用于删除表中的某行或整个数据表中的数据

语法：
DROP TABLE [schema.]<table> [PURGE]

注意事项：
除非您指定了PURGE子句，否则DROP TABLE语句不会导致空间被释放回表空间供其他对象使用，并且该空间将继续计入用户的空间配额

实际操作

SQL> select count(*) from record;

  COUNT(*)
----------
	40

SQL> drop table record;

Table dropped.

SQL> select * from recyclebin where type='TABLE';

no rows selected

SQL> select * from recyclebin;

no rows selected

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


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

SQL> !oerr ora 38305
38305, 00000, "object not in RECYCLE BIN"
// *Cause: Trying to Flashback Drop an object which is not in RecycleBin.
// *Action: Only the objects in RecycleBin can be Flashback Dropped.

SQL> show parameter RECYCLE;

NAME				     	TYPE			VALUE
--------------------------  -----------  	----------
buffer_pool_recycle		    string
db_recycle_cache_size		big integer 	0
recyclebin			     	string	 		on

由此可见，删除的表并没有放到回收站，但是回收站功能一般都是默认开启的，所以导致该情况出现的原因很有可能也是sys或system用户建表的原因

SQL> conn scott/tiger
Connected.
SQL> select count(*) from record;

  COUNT(*)
----------
	40

SQL> drop table record;

Table dropped

SQL> desc recyclebin;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_NAME				   NOT NULL VARCHAR2(128)
 ORIGINAL_NAME					    VARCHAR2(128)
 OPERATION					    VARCHAR2(9)
 TYPE						    VARCHAR2(25)
 TS_NAME					    VARCHAR2(30)
 CREATETIME					    VARCHAR2(19)
 DROPTIME					    VARCHAR2(19)
 DROPSCN					    NUMBER
 PARTITION_NAME 				    VARCHAR2(128)
 CAN_UNDROP					    VARCHAR2(3)
 CAN_PURGE					    VARCHAR2(3)
 RELATED				   NOT NULL NUMBER
 BASE_OBJECT				   NOT NULL NUMBER
 PURGE_OBJECT				   NOT NULL NUMBER
 SPACE						    NUMBER

SQL> set lines 10000;
SQL> set pages 1000;
SQL> col OBJECT_NAME for a50;
SQL> col TYPE for a10;
SQL> col ORIGINAL_NAME for a30;
SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,TS_NAME from recyclebin;

OBJECT_NAME							ORIGINAL_NAME		TYPE	    TS_NAME
----------------------------------	------------------ 	---------	---------
BIN$9E96u4RFIZ/gVQIMKWsMcw==$0		RECORD			  	TABLE		USERS

由此可见，非sys用户下的表被drop table会被放到回收站内，接下来重新用sys建表查看有何区别

SQL> conn / as sysdba
Connected.

SQL> create table record(id int, name varchar(10), tel varchar(11));

Table created.

SQL> show user 
USER is "SYS"
SQL> select owner,table_name,tablespace_name,status from dba_tables where table_name = 'RECORD';

OWNER			TABLE_NAME			TABLESPACE_NAME			STATUS
------------	----------------	------------------		--------
SYS				RECORD 				SYSTEM			 		VALID

SQL> CONN scott/tiger
Connected.
SQL> select table_name,tablespace_name,status from user_tables where table_name = 'RECORD';

no rows selected

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

SQL> create table record(id int, name varchar(10), tel varchar(11));

Table created.

SQL> select table_name,tablespace_name,status from user_tables where table_name = 'RECORD';

TABLE_NAME			TABLESPACE_NAME			STATUS
-----------------	--------------------	--------
RECORD				USERS					VALID

由上可见，sys用户所建立的表默认将其放在system表空间，而scott用户的表则是放在users表空间。查看官网得知：The table is sent to the recyclebin only if it existed in some other tablespace other than SYSTEM tablespace and that tablespace must be locally managed.

恢复数据

从上可知，非system表空间被drop table删除的表在非PURGE状态下会被放入回收站，所以可以通过FLASHBACK TABLE table_name TO BEFORE DROP闪回到表删除之前的状态从而达到恢复表数据的目的

SQL> conn scott/tiger
Connected.
SQL> select count(*) from record;

  COUNT(*)
----------
	11

SQL> drop table record;

Table dropped.

SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,TS_NAME from recyclebin;

OBJECT_NAME					   ORIGINAL_NAME		  TYPE	     TS_NAME
-------------------------------------------------- ------------------------------ ---------- ------------------------------
BIN$9E96u4RFIZ/gVQIMKWsMcw==$0			   RECORD			  TABLE      USERS
BIN$9FBDU5zmLOjgVQIMKWsMcw==$0			   RECORD			  TABLE      USERS

SQL> flashback table record to before drop;

Flashback complete.

SQL> select count(*) from record;

  COUNT(*)
----------
	11

SQL> insert into record values('34','Uwdul','13786498170');

1 row created.

SQL> select count(*) from record;

  COUNT(*)
----------
	12

SQL> drop table record purge;

Table dropped.

SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,TS_NAME from recyclebin;

OBJECT_NAME					   ORIGINAL_NAME		  TYPE	     TS_NAME
-------------------------------------------------- ------------------------------ ---------- ------------------------------
BIN$9E96u4RFIZ/gVQIMKWsMcw==$0			   RECORD			  TABLE      USERS

SQL> purge recyclebin;

Recyclebin purged.

SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,TS_NAME from recyclebin;

no rows selected

drop table总结

非system表空间下的表在drop table后一般会被放入回收站
回收站内的表可以通过flashback table闪回
回收站内有多个相同表名的记录，闪回时以最靠近当前scn的表记录为准
flashback table后回收站内的对应记录会自动删除
purge的表不会被放进回收站，慎用！！！！
purge recyclebin可直接清理回收站所有内容
回收站不是无限制，过多时会删除回收站内最遥远的记录以腾空间

truncate table

官方文档 - truncate table

Use the TRUNCATE TABLE statement to remove all rows from a table. By default, Oracle Database also performs the following tasks:

Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter
Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process

用于删除整个数据表中的数据

语法：
TRUNCATE TABLE [schema.]<table>

注意事项：
TRUNCATE的数据不可以rollback和flashback

实际操作

SQL> conn scott/tiger
Connected.
SQL> select count(*) from record;

  COUNT(*)
----------
	17

SQL> truncate table record;

Table truncated.

SQL> select count(*) from record;

  COUNT(*)
----------
	 0

SQL> rollback;

Rollback complete.

SQL> select count(*) from record;

  COUNT(*)
----------
	 0

SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,TS_NAME from recyclebin;

no rows selected

恢复数据

oracle truncate table recover(oracle 如何拯救误操作truncate的表)

三者的区别

三者删除的对象不一样
- delete删除的仅仅是符合条件的行数据，不对表的结构造成影响
- drop table删除的是整个表，一般情况是将整个表移入回收站
- truncate table删除的是整个表中的数据且不对表的结构造成影响
- 删除表将使依赖对象失效，并删除表上的对象特权。如果要重新创建表，则必须重新授予表上的对象特权，为表重新创建索引、完整性约束和触发器，并重新指定其存储参数。truncate没有这些影响。因此，使用TRUNCATE语句删除行比删除并重新创建表更有效。
三者对被删除数据的空间处理不同