66,688
社区成员
数据为脚本随机生成,如有雷同,纯属巧合
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 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用户执行后引起数据库崩溃而设置的限制
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
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只有在PURGE状态下才会导致空间被释放回表空间供其他对象使用
truncate table的数据空间会被自动释放回表空间,truncate之后的状态和create table的状态基本相同