Oracle之删除表或数据及恢复

m0_57042151 2023-03-07 16:51:53

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

 

Table of Contents

 

实验数据

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


 

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总结

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

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的表)

三者的区别

  1. 三者删除的对象不一样

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

image.png

delete的数据空间不会被处理,但会被新插入的数据覆盖

image.png

drop table只有在PURGE状态下才会导致空间被释放回表空间供其他对象使用

image.png

truncate table的数据空间会被自动释放回表空间,truncate之后的状态和create table的状态基本相同

...全文
327 回复 打赏 收藏 转发到动态 举报
写回复
用AI写文章
回复
切换为时间正序
请发表友善的回复…
发表回复
第一篇 Oracle管理配置 第1章 Oracle安装配置(教学视频:10分钟) 23 1.1 Oracle简介 23 1.1.1 数据库术语 23 1.1.2 主流数据库简介 24 1.1.3 Oracle数据库的特点 24 1.2 安装Oracle数据库 25 1.2.1 Oracle数据库的版本变迁及安装环境 25 1.2.2 安装过程 26 1.2.3 安装中需要注意的问题 27 1.3 本章小结 28 1.4 习题 28 第2章 Oracle常用工具(教学视频:7分钟) 29 2.1 Net Configuration Assistant(网络配置助手) 29 2.1.1 监听程序配置 29 2.1.2 命名方法配置 31 2.1.3 本地Net服务名配置 32 2.2 Net Manager(网络管理员) 34 2.3 本章实例 36 2.4 本章小结 38 2.5 习题 38 第3章 SQL Plus和PL/SQL(教学视频:11分钟) 39 3.1 SQL Plus与PL/SQL简介 39 3.2 使用SQL Plus 40 3.2.1 登录SQL Plus 40 3.2.2 SQL Plus输出结果的格式化 41 3.2.3 SQL Plus小结 46 3.3 PL/SQL 46 3.3.1 PL/SQL常用开发工具 46 3.3.2 开发一个简单的PL/SQL程序 48 3.4 本章实例 49 3.5 本章小结 50 3.6 习题 50 第二篇 Oracle数据库对象 第4章 Oralce数据库(教学视频:15分钟) 51 4.1 创建Oracle数据库 51 4.2 Oracle数据库的相关术语 52 4.2.1 数据库 53 4.2.2 数据库实例和SID 53 4.2.3 ORACLE_SID 54 4.3 Oracle数据库的备份与恢复 55 4.3.1 逻辑备份/恢复(导出/导入) 55 4.3.2 物理备份/恢复 56 4.3.3 利用PL/SQL Developer备份数据库 60 4.4 本章实例 61 4.5 本章小结 61 4.6 习题 62 第5章 Oracle数据表对象(教学视频:42分钟) 63 5.1 Oracle表空间 63 5.1.1 Oracle表空间简介 63 5.1.2 创建Oracle表空间 64 5.1.3 查看表空间 66 5.1.4 修改数据库默认表空间 67 5.1.5 修改表空间名称 68 5.1.6 删除表空间 69 5.2 创建Oracle数据表 70 5.2.1 利用工具创建数据表 70 5.2.2 利用工具查看数据表 71 5.2.3 利用命令创建数据表 72 5.2.4 利用命令查看表结构 72 5.3 修改Oracle数据表结构 73 5.3.1 利用工具修改数据表结构 73 5.3.2 利用命令修改数据表结构 74 5.4 删除数据表 75 5.4.1 利用工具删除数据表 76 5.4.2 利用SQL语句删除数据表 76 5.5 备份/恢复数据表 76 5.5.1 利用工具备份/恢复数据表 77 5.5.2 利用命令备份/恢复数据表 82 5.6 临时表 83 5.6.1 临时表简介 83 5.6.2 会话级临时表 84 5.6.3 事务级临时表 85 5.6.4 查看临时表在数据库中的信息 86 5.6.5 临时表的应用场景 86 5.7 特殊的表dual 87 5.7.1 分析dual表 87 5.7.2 dual表的应用场景 87 5.7.3 修改dual表对查询结果的影响 88 5.8 本章实例 89 5.9 本章小结 90 5.10 习题 90 第6章 约束(教学视频:43分钟) 91 6.1 主键约束 91 6.1.1 主键简介 91 6.1.2 创建主键约束 92 6.1.3 修改表的主键约束 94 6.1.4 主键应用场景 96 6.2 外键约束 97 6.2.1 外键简介 97 6.2.2 创建外键约束 97 6.2.3 级联更新与级联删除 100 6.2.4 修改外键属性 102 6.2.5 外键使用 104 6.3 唯一性约束 105 6.3.1 唯一性约束简介 105 6.3.2 创建唯一性约束 105 6.3.3 修改唯一性约束 107 6.3.4 唯一性约束的使用 108 6.4 检查约束 108 6.4.1 检查约束简介 108 6.4.2 创建检查约束 108 6.4.3 修改检查约束 110 6.4.4 检查约束的使用 111 6.5 默认值约束 111 6.5.1 默认值约束简介 112 6.5.2 创建默认值约束 112 6.5.3 修改默认值约束 113 6.6 本章实例 115 6.7 本章小结 116 6.8 习题 116 第7章 视图(教学视频:50分钟) 117 7.1 关系视图 117 7.1.1 建立关系视图 117 7.1.2 修改/删除视图 118 7.1.3 联接视图 120 7.1.4 编译视图 122 7.1.5 使用force选项强制创建视图 124 7.1.6 利用视图更新数据表 125 7.1.7 with check option选项 126 7.1.8 关系视图小结 128 7.2 内嵌视图 128 7.2.1 内嵌视图简介 128 7.2.2 内嵌视图的使用 128 7.2.3 内嵌视图小结 130 7.3 对象视图 131 7.3.1 对象视图简介 131 7.3.2 对象视图简介 131 7.4 物化视图 133 7.4.1 物化视图简介 133 7.4.2 物化视图的使用 133 7.4.3 物化视图的数据加载 135 7.4.4 物化视图的数据更新 135 7.4.5 查询重写 136 7.5 本章小结 136 7.6 本章实例 137 7.7 习题 137 第8章 函数与存储过程(教学视频:48分钟) 138 8.1 函数 138 8.1.1 函数简介 138 8.1.2 创建函数 139 8.1.3 函数中的括号 140 8.1.4 函数的参数 141 8.1.5 函数的确定性 142 8.1.6 典型函数举例 143 8.2 存储过程 144 8.2.1 存储过程简介 144 8.2.2 创建存储过程 144 8.2.3 存储过程的参数——IN参数 146 8.2.4 存储过程的参数——OUT参数 147 8.2.5 存储过程的参数——IN OUT参数 149 8.2.6 存储过程的参数——参数顺序 149 8.2.7 存储过程的参数——参数的默认值 152 8.2.8 存储过程的参数——参数顺序总结 153 8.3 程序包 153 8.3.1 规范 153 8.3.2 主体 155 8.3.3 调用程序包中的函数/存储过程 157 8.3.4 程序包中的变量 158 8.4 本章实例 159 8.5 本章小结 161 8.6 习题 161 …… 第9章 游标(教学视频:36分钟) 162 第10章 触发器(教学视频:58分钟) 178 第11章 序列(教学视频:28分钟) 206 第12章 用户角色与权限控制(教学视频:45分钟) 215 第三篇 Oracle中的SQL 第13章 Oracle数据类型(教学视频:21分钟) 231 第14章 Oracle中的函数与表达式(教学视频:111分钟) 240 第15章 Oracle中的控制语句(教学视频:16分钟) 282 第16章 SQL查询(教学视频:55分钟) 290 第17章 SQL更新数据(教学视频:34分钟) 319 第四篇 Oracle编程高级应用 第18章 数据库速度优化与数据完整性(教学视频:32分钟) 332 第19章 数据一致性与事务管理(教学视频:46分钟) 341 第20章 并发控制(教学视频:35分钟) 356 第21章 Oracle中的正则表达式(教学视频:29分钟) 369 第五篇 Oracle与编程语言综合使用实例 第22章 Oracle在Java开发中的应用(教学视频:38分钟) 376 第23章 Oracle在C#开发中的应用(教学视频:12分钟) 391
下载了一大堆的免费文档 我也贡献一个我自己写的PowerDesigner16 5的使用文档 这个是公司领导让我自己写来做使用指导用的 写的仓促 多有不恰当的地方 望大家见谅 1 这个是16 5的版本 2 这个是以oralce11g为模板 3 文档目录如下: PowerDesigner 16 5 指导 1 一 安装 3 二 新建模板步骤 这里以Oracle11g数据库为例 3 1 新建Model 3 2 Modle设置 3 3 模板设置 4 4 右侧工具条 5 5 新建测试包 6 6 表配置 6 6 1进入模板 6 6 2新建表 6 6 3修改表 7 6 4添加注释 7 6 5属性设置 8 6 5 1 8 6 5 2 9 6 5 3 9 6 5 4 9 6 6添加表关系 10 6 7表关系设置 10 6 8模板保存 12 6 9查看模板 12 三 模板导入脚本 12 1 打开Change Current DRMS 12 2 删除多余双引号 13 3 生成脚本 13 四 配置数据库 15 1 点击Database >Configure Connections 15 2 选择第三个系统数据根源 17 3 选择Oracle in OraDb11g hom1 选择下一步 然后点击完成 17 4 数据源配置 18 5 配置成功 19 五 反向工程生成模板 20 1 按照二 1和二 2生成一个空模板 20 2 选择Database >Update Model from Database 20 3 选择数据源 20 4 数据源配置 21 5 选择反向工程导出对象 22 6 可以看到反向工程自动生成的模板 即代表操作工程 23 六 生成HTML文档 24 1 点击Report >Generate Report 24 2 配置完成 点击确认即可 24 3 打开html文档 应该是下图格式 25">下载了一大堆的免费文档 我也贡献一个我自己写的PowerDesigner16 5的使用文档 这个是公司领导让我自己写来做使用指导用的 写的仓促 多有不恰当的地方 望大家见谅 1 这个是16 5的版本 2 这个是以oralce11g为模板 3 文档 [更多]

66,688

社区成员

发帖
与我相关
我的任务
社区描述
汇集数据库的爱好者和关注者,大家共同学习、探索、分享数据库前沿知识和技术,像松鼠一样剥开科学的坚果;交流Gauss及其他数据库的使用心得和经验,互助解决问题,共建数据库技术交流圈。
数据库数据仓库 企业社区 北京·海淀区
社区管理员
  • Gauss松鼠会
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告

欢迎大家同时关注Gauss松鼠会专家酷哥。

https://www.zhihu.com/people/ku-ge-78-98

试试用AI创作助手写篇文章吧