[bbk4727] 第24集 - 第三章 Flashback Table 01
Guaranteeing Unod Retention
SQL> select ts#,name,included_in_database_backup,bigfile,flashback_on,encrypt_in_backup from v$tablespace; TS# NAME INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON ENCRYPT_IN_BACKUP ---------- --------------- ------------------------------ ---------- --------------- -------------------- 0 SYSTEM YES NO YES 1 SYSAUX YES NO YES 2 UNDOTBS1 YES NO YES 4 USERS YES NO YES 3 TEMP NO NO YES 6 EXAMPLE YES NO YES 7 TAB_U2 YES NO YES 8 TAB_U3 YES NO YES 9 TS0 YES NO YES 10 TS1 YES NO YES 11 TS2 YES NO YES 12 TS3 YES NO YES 13 TS4 YES NO YES 13 rows selected.
SQL> select tablespace_name,contents,retention from dba_tablespaces; TABLESPACE_NAME CONTENTS RETENTION ------------------------------ --------- ----------- SYSTEM PERMANENT NOT APPLY SYSAUX PERMANENT NOT APPLY UNDOTBS1 UNDO NOGUARANTEE TEMP TEMPORARY NOT APPLY USERS PERMANENT NOT APPLY EXAMPLE PERMANENT NOT APPLY TAB_U2 PERMANENT NOT APPLY TAB_U3 PERMANENT NOT APPLY TS0 PERMANENT NOT APPLY TS1 PERMANENT NOT APPLY TS2 PERMANENT NOT APPLY TS3 PERMANENT NOT APPLY TS4 PERMANENT NOT APPLY 13 rows selected.
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
Flashback Query
Flashback Query:Example
利用闪回查询，恢复数据案例
/***************************************************/
实验报告
1、7369 ->? get scn
2、update-7369 get scn
3、识别错误
4、恢复数据
/***************************************************/
SQL> select employee_id,last_name,salary from t; EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 198 OConnell 2600 199 Grant 2600 200 Whalen 4400 201 Hartstein 13000 202 Fay 6000 203 Mavris 6500 204 Baer 10000 205 Higgins 12008 206 Gietz 8300 100 King 24000 101 Kochhar 17000 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 102 De Haan 17000 103 Hunold 9000 104 Ernst 6000 105 Austin 4800 106 Pataballa 4800 107 Lorentz 4200 108 Greenberg 12008 109 Faviet 9000 110 Chen 8200 111 Sciarra 7700 112 Urman 7800 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 113 Popp 6900 114 Raphaely 11000 115 Khoo 3100 116 Baida 2900 117 Tobias 2800 118 Himuro 2600 119 Colmenares 2500 120 Weiss 8000 121 Fripp 8200 122 Kaufling 7900 123 Vollman 6500 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 124 Mourgos 5800 125 Nayer 3200 126 Mikkilineni 2700 127 Landry 2400 128 Markle 2200 129 Bissot 3300 130 Atkinson 2800 131 Marlow 2500 132 Olson 2100 133 Mallin 3300 134 Rogers 2900 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 135 Gee 2400 136 Philtanker 2200 137 Ladwig 3600 138 Stiles 3200 139 Seo 2700 140 Patel 2500 141 Rajs 3500 142 Davies 3100 143 Matos 2600 144 Vargas 2500 145 Russell 14000 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 146 Partners 13500 147 Errazuriz 12000 148 Cambrault 11000 149 Zlotkey 10500 150 Tucker 10000 151 Bernstein 9500 152 Hall 9000 153 Olsen 8000 154 Cambrault 7500 155 Tuvault 7000 156 King 10000 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 157 Sully 9500 158 McEwen 9000 159 Smith 8000 160 Doran 7500 161 Sewall 7000 162 Vishney 10500 163 Greene 9500 164 Marvins 7200 165 Lee 6800 166 Ande 6400 167 Banda 6200 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 168 Ozer 11500 169 Bloom 10000 170 Fox 9600 171 Smith 7400 172 Bates 7300 173 Kumar 6100 174 Abel 11000 175 Hutton 8800 176 Taylor 8600 177 Livingston 8400 178 Grant 7000 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 179 Johnson 6200 180 Taylor 3200 181 Fleaur 3100 182 Sullivan 2500 183 Geoni 2800 184 Sarchand 4200 185 Bull 4100 186 Dellinger 3400 187 Cabrio 3000 188 Chung 3800 189 Dilly 3600 EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 190 Gates 2900 191 Perkins 2500 192 Bell 4000 193 Everett 3900 194 McCain 3200 195 Jones 2800 196 Walsh 3100 197 Feeney 3000 107 rows selected. SQL> conn /as sysdba Connected. SQL> select dbms_flashback.get_system_change_number() from dual; DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() ----------------------------------------- 4330451
SQL> conn U2/U2 Connected. SQL> update t set salary=30000 where employee_id=197; 1 row updated. SQL> commit; Commit complete. SQL> select employee_id,last_name,salary from t where employee_id = 197; EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 197 Feeney 30000
SQL> conn /as sysdba Connected. SQL> select dbms_flashback.get_system_change_number() from dual; DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() ----------------------------------------- 4330562
SQL> conn U2/U2 Connected. SQL> select employee_id,last_name,salary 2 as of scn 4330451 3 where employee_id=197; as of scn 4330451 * ERROR at line 2: ORA-00923: FROM keyword not found where expected SQL> 1 1* select employee_id,last_name,salary SQL> a/ from t 1* select employee_id,last_name,salary/ from t SQL> l 1 select employee_id,last_name,salary/ from t 2 as of scn 4330451 3* where employee_id=197 SQL> 0 select employee_id,last_name,salary from t SQL> l 1 select employee_id,last_name,salary from t 2 select employee_id,last_name,salary/ from t 3 as of scn 4330451 4* where employee_id=197 SQL> del 2 SQL> l 1 select employee_id,last_name,salary from t 2 as of scn 4330451 3* where employee_id=197 SQL> / EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 197 Feeney 3000
SQL> show user USER is "U2" SQL> update t set salary = (select salary from t as of scn 4330451 where employee_id = 197) 2 where employee_id = 197; 1 row updated. SQL> commit; Commit complete. SQL> select employee_id,last_name,salary from t where employee_id = 197; EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 197 Feeney 3000
