[bbk5116] 第43集 - 第四章 Flashback Database 07
/********************************/
闪回已经删除过的表
注意：假如一张表被删除之后，那么它对应的索引以及约束也会一同被删除.被删除的对象(表、索引、约束、触发器)放到回收站之后，oracle都会重新分配一个新的名称.假如通过flashback table技术闪回表的时候，表的名称可以回到之前，但是索引名称、约束的名称将不会回到之前.仍将沿用在回收站分配的名称.如果闪回成功之后，就需要手工将索引、约束、触发器的名称rename到之前的名称.
/********************************/
SQL> drop table t; Table dropped. SQL> select employee_id,last_name,salary from t; select employee_id,last_name,salary from t * ERROR at line 1: ORA-00942: table or view does not exist SQL> flashback table t before drop; flashback table t before drop * ERROR at line 1: ORA-00905: missing keyword SQL> flashback table t to before drop; Flashback complete. 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> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- T BIN$3KaADKSpAoDgQAAAAABNZA==$0 TABLE 2013-05-14:11:48:03
SQL> desc user_recyclebin; Name Null? Type ----------------------------------------- -------- ---------------------------- OBJECT_NAME NOT NULL VARCHAR2(30) ORIGINAL_NAME VARCHAR2(32) OPERATION VARCHAR2(9) TYPE VARCHAR2(25) TS_NAME VARCHAR2(30) CREATETIME VARCHAR2(19) DROPTIME VARCHAR2(19) DROPSCN NUMBER PARTITION_NAME VARCHAR2(32) 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> select object_name,original_name from user_recyclebin; OBJECT_NAME ORIGINAL_NAME ------------------------------ -------------------------------- BIN$3KaADKSpAoDgQAAAAABNZA==$0 T
