posts - 197,comments - 38,views - 83万

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

  

  

posted on   wisdo  阅读(245)  评论(0编辑  收藏  举报
编辑推荐:
· 智能桌面机器人:用.NET IoT库控制舵机并多方法播放表情
· Linux glibc自带哈希表的用例及性能测试
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
阅读排行:
· 手把手教你在本地部署DeepSeek R1,搭建web-ui ,建议收藏!
· 新年开篇:在本地部署DeepSeek大模型实现联网增强的AI应用
· Janus Pro:DeepSeek 开源革新,多模态 AI 的未来
· 互联网不景气了那就玩玩嵌入式吧,用纯.NET开发并制作一个智能桌面机器人(三):用.NET IoT库
· 【非技术】说说2024年我都干了些啥
< 2025年1月 >
29 30 31 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 1
2 3 4 5 6 7 8

点击右上角即可分享
微信分享提示