Oracle 数据库常用的闪回sql 语句及其它操作语句:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 | --Oracle 数据库dml sql -- 查看当前用户所拥有的表 select * from tab; --表空间,auto: 自动管理, manual: 手动管理 create tablespace tsp1 datafile 'D:\ORACLE\ORADATA\O10\tsp1.dbf' size 2M segment space management auto; -- 查看表空间 select tablespace_name,segment_space_management from dba_tablespaces; -- 回退段管理 --系统回退段,存放于 system 表空间,只有system 表空间内的对象才可以使用 -- 查看回退段 select * from v$rollname; show parameter undo ; -- 数据回退 -- 当前系统的scn 号, delete 数据后, commit 获取scn select dbms_flashback.get_system_change_number() from dual; -- 闪回 execute dbms_flashback.enable_at_system_change_number(#####); -- 结束闪回 execute dbms_flashback.disable(); -- ORA-08182: operation not supported while in Flashback mode, 这时要结束闪回 execute dbms_flashback.disable(); -- 闪回到指定的时间点,物理时间和数据库时间的SCN 的对照表,每5分钟采样 , purge 表后,无法再恢复 select to_char(time_dp, 'yyyy/mm/dd:hh24:mi:ss' ),SCN from sys.smon_scn_time; execute dbms_flashback.enable_at_time(to_date( '2017/9/2/24:12:22' , 'yyyy/mm/dd:hh24:mi:ss' )); --闪回---取值到游标----停止闪回----将游标中的值插入原表 declare cursor c1 is select * from scott.e2 where empno=7369; v_sal c1%rowtype; begin DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_number (13346413); open c1; fetch c1 into v_sal; dbms_flashback.disable(); update scott.e2 set sal=v_sal.sal where empno=v_sal.empno; close c1; end ; / -- Flashback Transaction Query, 通过整条来闪回,利用此功能进行诊断问题、性能分析和审计事务 select xid from v$ transaction ; select * from flashback_transaction_query where xid= '050025002F020000' -- 管理回收站 -- 清空回收站 purge recyclebin; -- 恢复回收站中指定的表 flashback table 表名 to before drop ; -- 彻底删除表,不放在回收站中, SYS用户删除的表、视图是不进入回收站的,因此不支持闪回删除 drop table 表名 purge; -- 查看表的更改痕迹 select versions_starttime,versions_endtime,versions_xid,versions_operation, sal from t1 versions between timestamp minvalue and maxvalue order by versions_starttime select versions_starttime, versions_endtime , versions_xid, versions_operation,id from t2 versions between timestamp to_timestamp( '2017-10-17 14:53:00' , 'yyyy-mm-dd hh24:mi:ss' ) and to_timestamp( '2017-10-17 14:54:30' , 'yyyy-mm-dd hh24:mi:ss' ); -- 查看 一个时间段内的更改痕迹 select versions_starttime,versions_endtime,versions_xid,versions_operation, sal from t1 versions between timestamp to_date( '2017/9/2/24:12:22' , 'yyyy/mm/dd:hh24:mi:ss' ) and maxvalue order by versions_starttime -- 查看原sql 语句 select undo_sql from flashback_transaction_query where xid= '08001100C7010000' -- 多次drop 后,建立同名表, 必须9i或10g以上版本支持,flashback无法恢复全文索引 flashback table t1 to before drop rename to t2; -- 构造rowid select dbms_rowid.rowid_create(1,5413,4,32,7) from dual; --参数1: 新版本的rowid 格式,64进制,18位, 0:旧版本的rowid 格式,16进制,16位 -- 上面的rowid 代表 4号文件的,第32个块,第7行,将8位16进制转换成10进制,正好是4M, -- 所以一个数据块的上限为 4M 个oracle 块。 -- 查看每个块中的行数,行的长度不同,块内存放的行数也不同 select dbms_rowid,rowid_block_number(rowid), block#, count (*) from t1 group by dbms_rowid,rowid_block_number(rowid); -- 删除指定的列 alter table t1 drop column 列名 checkpoint 1000; --drop 过程中表的状态为 invalid,如果过程中停电,启动数据库后 alter table t1 drop columns 列名 continue -- 查看索引在哪个表哪个列上 select index_name,table_name,column_name from user_ind_columns order by 2,32 -- 查看索引的属性 select index_name,index_type,table_name,uniqueness from user_indexes; -- 查看索引的内部信息 select height,blocks,br_blks,lf_blks,lf_rows,del_lf_rows from index_stats -- height: 高度, blocks:索引总块数, br_blks: 枝干块数, lf_rows:叶子内行数 -- del_lf_rows: 叶子中被删除的行数 -- 索引的合并 alter index 索引名 coalesce ; -- 重新索引 alter index 索引名 rebuild; -- 查看索引的引用状态 select * from v$objcet_usage; -- 授权 grant create session to u1 -- 回收权限 revoke create session from u1; -- 查看字符编码集 select * from v$nls_valid_values where PARAMETER= 'CHARACTERSET' order by 2; -- 提取元数据 select dbms_metadata.get_ddl( 'TABLE' , 'EMP' ) FROM DUAL; -- 数据库连接(连接到远程的数据库) CREATE PUBLIC DATABASE LINK dh connect to system identified by manager using '111' ; --一个共有的数据库连接,名称是 dh,连接到 111 所描述的数据库中的 system 用户, -- 密码为manager -- 查看数据库连接 select * from dba_db_links -- 关闭数据库连接 ALTER SESSION CLOSE DATABASE LINK dh; -- 强制使用 hash 连接 -- 适用于大量数据的连接,将两个表中较小的表连接列建立一个hash表, --放入到内存中,必须有等值条件 select /*+ use_hash(t1 t2) */ t1.* from t1 ,t2 where t1.id=t2.id -- 强制使用 merge 连接 -- 排序融合连接,hash 连接大部分时候都比排序融合连接性能好 -- 如果不是等值条件,即 > < >= <= ,不能使用hash 连接,使用排序连接和嵌套循环连接 select /*+ use_merge(t1 t2) */ t1.* from t1 ,t2 where t1.id=t2.id -- 强制使用 nest_loop 连接, 嵌套循环连接,外部表的每一个行都要和内部表的所有行连接 -- 当表的行数较少的时候,数据库会选择这种连接方式 select /*+ use_nl(t1 t2) */ t1.* from t1 ,t2 where t1.id=t2.id -- 强制使用 全表扫描而不使用主键 select /*+ full(t1) */ * from t1 -- 强制使用主键而不使用全表扫描 select /*+ index(index_name) */ * from t1 -- 强制使用并行查询,提高全表扫描效率 select /*+ full(t1) parallel(t1,4) */ * from t1; -- flashback database sys 用户不支持闪回,sys 为dba 账号 --先关闭数据库 shutdown immediate; -- 启动数据库到 mount startup mount; -- 闪回数据库 flashback database to timestamp to_date( '2017-10-17 16:13:00' , 'yyyy-mm-dd hh24:mi:ss' ); -- 打开数据库到 read only alter database open read only ; alter database 数据库名 open ; -- 查询删除的数据 delete , commit 后 select * from t3 as of timestamp to_timestamp( '2017-10-18 11:25:00' , 'yyyy-mm-dd hh24:mi:ss' ); select * from t3 as of scn 2323267; -- 向删除的表中插入数据 insert into t3 select * from t3 as of timestamp to_timestamp( '2017-10-18 11:25:00' , 'yyyy-mm-dd hh24:mi:ss' ); -- 修改闪回恢复区 flashback database 时需要配置 flash_recovery_area alter system set db_recovery_file_dest_size=3g scope=both; alter system set db_recovery_file_dest= 'D:\oracle\product\10.2.0\flash_recovery_area' show parameter db_recovery_file_dest; show parameter db_flashback -- 查看闪回恢复区 show parameter db_recovery -- 查看闪回恢复区的使用情况,在11.2以后,v$flash_recovery_area_usage已经被v$recovery_area_usage取代 select * from v$flash_recovery_area_usage; -- 查看 flashback 状态 select flashback_on from v$ database ; --查询当前的scn 号, select current_scn from v$ database ; -- 恢复数据库到特定的scn flashback database to scn 2323267; -- 查看事务对应的scn select xid,commit_scn,commit_timestamp,operation,undo_sql from flashback_transaction_query q where q.xid in ( select versions_xid from B versions between scn 413946 and 413959); -- 闪回时,需要启用表 row movement alter table t3 enable row movement; alter table t3 disable row movement; -- 查看表结构 desc 表名 -- 以dba连接数据库 connect 用户名/密码 as sysdba; -- 与rowid 一样, ora_rowscn 也是伪列 select ora_rowscn from 表名 -- 显示当前用户 show user ; -- 修改oracle 的时间格式 alter session set nls_date_formate= 'yyyy-mm-dd hh24:mi:ss' --设置sqlplus 窗口的SQL> 提示 set sqlprompt "_user'@'_connect_identifier _date>" -- oracle 查询列名合并 select distinct b.s_stuffid, b.s_stuffname || '(' || u.s_username || ')' s_stuffname from US_B_STUFF_DICT b |
本博客是自己在学习和工作途中的积累与总结,仅供自己参考,也欢迎大家转载,转载时请注明出处。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 智能桌面机器人:用.NET IoT库控制舵机并多方法播放表情
· Linux glibc自带哈希表的用例及性能测试
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 手把手教你在本地部署DeepSeek R1,搭建web-ui ,建议收藏!
· 新年开篇:在本地部署DeepSeek大模型实现联网增强的AI应用
· Janus Pro:DeepSeek 开源革新,多模态 AI 的未来
· 互联网不景气了那就玩玩嵌入式吧,用纯.NET开发并制作一个智能桌面机器人(三):用.NET IoT库
· 【非技术】说说2024年我都干了些啥