OCP培训笔记-flashback
|
FlashBack Keywords:? SQL>?conn?scott/tiger Connected. SQL>?select?*?from?tab; TNAME??????????????????????????TABTYPE??CLUSTERID ------------------------------?-------?---------- BIN$u4X8FW5uuvfgQBCsWwoQFA==$0?TABLE >>>here,BIN$***?is?the?table?name?(droped) BIN$u4Z5O4T/UwfgQBCsWwoQEw==$0?TABLE BIN$u4ZHfRkVsYHgQBCsWwoNrA==$0?TABLE BONUS??????????????????????????TABLE DEPT???????????????????????????TABLE EMP????????????????????????????TABLE SALGRADE???????????????????????TABLE WB2????????????????????????????TABLE 8?rows?selected. SQL>?show?recyclebin; ORIGINAL?NAME????RECYCLEBIN?NAME????????????????OBJECT?TYPE??DROP?TIME ----------------?------------------------------?------------?------------------- EMP??????????????BIN$u4X8FW5uuvfgQBCsWwoQFA==$0?TABLE????????2012-03-18:11:20:41 EMP??????????????BIN$u4Z5O4T/UwfgQBCsWwoQEw==$0?TABLE????????2012-03-18:10:44:05 EMP??????????????BIN$u4ZHfRkVsYHgQBCsWwoNrA==$0?TABLE????????2012-03-18:10:30:11 SQL>?purge?recyclebin??; >>>purge?the?recyclebin Recyclebin?purged. SQL>?show?recyclebin; SQL>?select?*?from?tab; TNAME??????????????????????????TABTYPE??CLUSTERID ------------------------------?-------?---------- BONUS??????????????????????????TABLE DEPT???????????????????????????TABLE EMP????????????????????????????TABLE SALGRADE???????????????????????TABLE WB2????????????????????????????TABLE SQL>?drop?table?emp; Table?dropped. SQL>?desc?emp ERROR: ORA-04043:?object?emp?does?not?exist SQL>?select?*?from?emp; select?*?from?emp ??????????????* ERROR?at?line?1: ORA-00942:?table?or?view?does?not?exist SQL>?show?recyclebin; ORIGINAL?NAME????RECYCLEBIN?NAME????????????????OBJECT?TYPE??DROP?TIME ----------------?------------------------------?------------?------------------- EMP??????????????BIN$u4fZJelpzMTgQBCsWwoRTA==$0?TABLE????????2012-03-18:13:34:05 SQL>?----emp?----users?tablespace? SQL>?select?*?from?emp; >>>the?table?emp?was?dropped,?no?contacts select?*?from?emp ??????????????* ERROR?at?line?1: ORA-00942:?table?or?view?does?not?exist SQL>?flashback?table?emp?to?before?drop;? >>>flashebacked Flashback?complete. SQL>?show?recyclebin; SQL>?select?*?from?emp; ?????EMPNO?ENAME??????JOB??????????????MGR?HIREDATE?????????SAL???????COMM ----------?----------?---------?----------?---------?----------?---------- ????DEPTNO ---------- ??????7369?SMITH??????CLERK???????????7902?17-DEC-80????????800 ????????20 ??????7499?ALLEN??????SALESMAN????????7698?20-FEB-81???????1600????????300 ????????30 ??????7521?WARD???????SALESMAN????????7698?22-FEB-81???????1250????????500 ????????30 ?????EMPNO?ENAME??????JOB??????????????MGR?HIREDATE?????????SAL???????COMM ----------?----------?---------?----------?---------?----------?---------- ????DEPTNO ---------- ??????7566?JONES??????MANAGER?????????7839?02-APR-81???????2975 ????????20 ??????7654?MARTIN?????SALESMAN????????7698?28-SEP-81???????1250???????1400 ????????30 ??????7698?BLAKE??????MANAGER?????????7839?01-MAY-81???????2850 ????????30 ?????EMPNO?ENAME??????JOB??????????????MGR?HIREDATE?????????SAL???????COMM ----------?----------?---------?----------?---------?----------?---------- ????DEPTNO ---------- ??????7782?CLARK??????MANAGER?????????7839?09-JUN-81???????2450 ????????10 ??????7788?SCOTT??????ANALYST?????????7566?19-APR-87???????3000 ????????20 ??????7839?KING???????PRESIDENT????????????17-NOV-81???????5000 ????????10 ?????EMPNO?ENAME??????JOB??????????????MGR?HIREDATE?????????SAL???????COMM ----------?----------?---------?----------?---------?----------?---------- ????DEPTNO ---------- ??????7844?TURNER?????SALESMAN????????7698?08-SEP-81???????1500??????????0 ????????30 ??????7876?ADAMS??????CLERK???????????7788?23-MAY-87???????1100 ????????20 ??????7900?JAMES??????CLERK???????????7698?03-DEC-81????????950 ????????30 ?????EMPNO?ENAME??????JOB??????????????MGR?HIREDATE?????????SAL???????COMM ----------?----------?---------?----------?---------?----------?---------- ????DEPTNO ---------- ??????7902?FORD???????ANALYST?????????7566?03-DEC-81???????3000 ????????20 ??????7934?MILLER?????CLERK???????????7782?23-JAN-82???????1300 ????????10 14?rows?selected. SQL>?show??user USER?is?"SCOTT" SQL>?create?table?test?as?select?*?from?emp; Table?created. SQL>?create?index?idx_o?on?test(empno); >>>create?a?index?to?test?drop/flashback Index?created. SQL>?desc?test ?Name??????????????????????????????????????Null?????Type ?-----------------------------------------?--------?---------------------------- ?EMPNO??????????????????????????????????????????????NUMBER(4) ?ENAME??????????????????????????????????????????????VARCHAR2(10) ?JOB????????????????????????????????????????????????VARCHAR2(9) ?MGR????????????????????????????????????????????????NUMBER(4) ?HIREDATE???????????????????????????????????????????DATE ?SAL????????????????????????????????????????????????NUMBER(7,2) ?COMM???????????????????????????????????????????????NUMBER(7,2) ?DEPTNO?????????????????????????????????????????????NUMBER(2) SQL>?drop?table?test; Table?dropped. SQL>?show?recyclebin; ORIGINAL?NAME????RECYCLEBIN?NAME????????????????OBJECT?TYPE??DROP?TIME ----------------?------------------------------?------------?------------------- TEST?????????????BIN$u4fZJelrzMTgQBCsWwoRTA==$0?TABLE????????2012-03-18:13:39:14 SQL>?desc?test ?Name??????????????????????????????????????Null?????Type ?-----------------------------------------?--------?---------------------------- ?EMPNO??????????????????????????????????????????????NUMBER(4) ?ENAME??????????????????????????????????????????????VARCHAR2(10) ?JOB????????????????????????????????????????????????VARCHAR2(9) ?MGR????????????????????????????????????????????????NUMBER(4) ?HIREDATE???????????????????????????????????????????DATE ?SAL????????????????????????????????????????????????NUMBER(7,2) ?DEPTNO?????????????????????????????????????????????NUMBER(2) SQL>?drop?table?test; Table?dropped. SQL>?show?recyclebin; ORIGINAL?NAME????RECYCLEBIN?NAME????????????????OBJECT?TYPE??DROP?TIME ----------------?------------------------------?------------?------------------- TEST?????????????BIN$u4fZJeltzMTgQBCsWwoRTA==$0?TABLE????????2012-03-18:13:44:37 SQL>?desc??test; ERROR: ORA-04043:?object?test?does?not?exist SQL>?create?table?test?(a?int); >>>re?create?a?table?named?test,which?used?to?show?the?table?droped?in?recyclebin?is?a?logical?name Table?created. SQL>?desc??test; ?Name??????????????????????????????????????Null?????Type ?-----------------------------------------?--------?---------------------------- ?A??????????????????????????????????????????????????NUMBER(38) SQL>?drop?table?test; Table?dropped. SQL>?show?recyclebin; ORIGINAL?NAME????RECYCLEBIN?NAME????????????????OBJECT?TYPE??DROP?TIME ----------------?------------------------------?------------?------------------- TEST?????????????BIN$u4fZJeluzMTgQBCsWwoRTA==$0?TABLE????????2012-03-18:13:45:00 >>>2?table?named?TEST,just?rename TEST?????????????BIN$u4fZJeltzMTgQBCsWwoRTA==$0?TABLE????????2012-03-18:13:44:37 it?in?tablespace SQL>?create?table?test?(b?int); Table?created. SQL>?desc?test ?Name??????????????????????????????????????Null?????Type ?-----------------------------------------?--------?---------------------------- ?B??????????????????????????????????????????????????NUMBER(38) SQL>?show?recyclebin; ORIGINAL?NAME????RECYCLEBIN?NAME????????????????OBJECT?TYPE??DROP?TIME ----------------?------------------------------?------------?------------------- TEST?????????????BIN$u4fZJeluzMTgQBCsWwoRTA==$0?TABLE????????2012-03-18:13:45:00 TEST?????????????BIN$u4fZJeltzMTgQBCsWwoRTA==$0?TABLE????????2012-03-18:13:44:37 SQL>?desc?test ?Name??????????????????????????????????????Null?????Type ?-----------------------------------------?--------?---------------------------- ?B??????????????????????????????????????????????????NUMBER(38) SQL>?flashback?table?test?to?before?drop; >>>if?the?test?currently?exist,flashback?failed.but?can?rename?it flashback?table?test?to?before?drop * ERROR?at?line?1: ORA-38312:?original?name?is?used?by?an?existing?object SQL>?flashback?table?test?to?before?drop?rename?to?test1; Flashback?complete. SQL>?show?recyclebin; ORIGINAL?NAME????RECYCLEBIN?NAME????????????????OBJECT?TYPE??DROP?TIME ----------------?------------------------------?------------?------------------- TEST?????????????BIN$u4fZJeltzMTgQBCsWwoRTA==$0?TABLE????????2012-03-18:13:44:37 SQL>?flashback?table?test?to?before?drop?rename?to?test2; Flashback?complete. SQL>?show?recyclebin; SQL>?desc?test2 ?Name??????????????????????????????????????Null?????Type ?-----------------------------------------?--------?---------------------------- ?EMPNO??????????????????????????????????????????????NUMBER(4) ?ENAME??????????????????????????????????????????????VARCHAR2(10) ?JOB????????????????????????????????????????????????VARCHAR2(9) ?MGR????????????????????????????????????????????????NUMBER(4) ?HIREDATE???????????????????????????????????????????DATE ?SAL????????????????????????????????????????????????NUMBER(7,2) ?DEPTNO?????????????????????????????????????????????NUMBER(2) SQL>?desc?test1 ?Name??????????????????????????????????????Null?????Type ?-----------------------------------------?--------?---------------------------- ?A??????????????????????????????????????????????????NUMBER(38) SQL>?flashback?table?"BIN$u4fZJeltzMTgQBCsWwoRTA==$0"?to?before?drop; SQL>?bill? SQL>?drop?table?bill; SQL>?create?table?bill?(,) SQL>?rename?to?bill12? SQL>?insert?into?bill?select?*?form?bill12 SQL>?drop?table?emp; Table?dropped. SQL>?show?recyclebin; ORIGINAL?NAME????RECYCLEBIN?NAME????????????????OBJECT?TYPE??DROP?TIME ----------------?------------------------------?------------?------------------- EMP??????????????BIN$u4fZJelxzMTgQBCsWwoRTA==$0?TABLE????????2012-03-18:13:53:06 SQL>?drop?table?dept??purge; >>>?drop?permanent Table?dropped. SQL>?show?recyclebin; ORIGINAL?NAME????RECYCLEBIN?NAME????????????????OBJECT?TYPE??DROP?TIME ----------------?------------------------------?------------?------------------- EMP??????????????BIN$u4fZJelxzMTgQBCsWwoRTA==$0?TABLE????????2012-03-18:13:53:06 SQL>?10g?11g? SQL>?exit Disconnected?from?Oracle?Database?11g?Enterprise?Edition?Release?11.2.0.1.0?-?Production With?the?Partitioning,?OLAP,?Data?Mining?and?Real?Application?Testing?options [oracle@db?logicalbak]$?rlwrap?sqlplus??/?as?sysdba SQL*Plus:?Release?11.2.0.1.0?Production?on?Sun?Mar?18?13:58:49?2012 Copyright?(c)?1982,?2009,?Oracle.??All?rights?reserved. Connected?to: Oracle?Database?11g?Enterprise?Edition?Release?11.2.0.1.0?-?Production With?the?Partitioning,?Data?Mining?and?Real?Application?Testing?options SQL>?conn?scott/tiger >>>the?user?SCOTT?had?been?deleted ERROR: ORA-01017:?invalid?username/password;?logon?denied Warning:?You?are?no?longer?connected?to?ORACLE. SQL>?@?/rdbms/admin/utlsampl.sql >>>rebuild?the?user?SCOTT [oracle@db?logicalbak]$?rlwrap?sqlplus??/?as?sysdba SQL*Plus:?Release?11.2.0.1.0?Production?on?Sun?Mar?18?13:59:17?2012 Copyright?(c)?1982,?Data?Mining?and?Real?Application?Testing?options SQL>?@?/rdbms/admin/utlsampl.sql SQL>?exit [oracle@db?logicalbak]$?rlwrap?sqlplus??/?as?sysdba SQL*Plus:?Release?11.2.0.1.0?Production?on?Sun?Mar?18?13:59:43?2012 Copyright?(c)?1982,?Data?Mining?and?Real?Application?Testing?options SQL>?@utlsampl.sql SP2-0310:?unable?to?open?file?"utlsampl.sql" SQL>?exit Disconnected?from?Oracle?Database?11g?Enterprise?Edition?Release?11.2.0.1.0?-?Production With?the?Partitioning,?Data?Mining?and?Real?Application?Testing?options [oracle@db?logicalbak]$?cd?/oracle/product/db_1/rdbms/admin/ [oracle@db?admin]$?rlwrap?sqlplus??/?as?sysdba SQL*Plus:?Release?11.2.0.1.0?Production?on?Sun?Mar?18?14:00:07?2012 Copyright?(c)?1982,?Data?Mining?and?Real?Application?Testing?options SQL>?@utlsampl.sql Disconnected?from?Oracle?Database?11g?Enterprise?Edition?Release?11.2.0.1.0?-?Production With?the?Partitioning,?Data?Mining?and?Real?Application?Testing?options [oracle@db?admin]$?rlwrap?sqlplus??/?as?sysdba SQL*Plus:?Release?11.2.0.1.0?Production?on?Sun?Mar?18?14:00:19?2012 Copyright?(c)?1982,?Data?Mining?and?Real?Application?Testing?options SQL>?conn?scott/tiger Connected. SQL>?show?user USER?is?"SCOTT" SQL>?drop?table?emp; Table?dropped. SQL>?drop?table?dept; Table?dropped. SQL>?show?parameter?recyclebin ORA-00942:?table?or?view?does?not?exist SQL>?show?parameter?recycle ORA-00942:?table?or?view?does?not?exist SQL>?show?user USER?is?"SCOTT" SQL>?conn?sys?as?sysdba Enter?password:? Connected. SQL>?grant?select?any?table?to?u1; Grant?succeeded. SQL>?@utlsampl.sql Disconnected?from?Oracle?Database?11g?Enterprise?Edition?Release?11.2.0.1.0?-?Production With?the?Partitioning,?Data?Mining?and?Real?Application?Testing?options SQL>?delete?from?emp?where?deptno=10; >>>there?is?an?experiment, 3?rows?deleted. SQL>?select?*?from?emp; ?????EMPNO?ENAME??????JOB??????????????MGR?HIREDATE?????????SAL???????COMM ----------?----------?---------?----------?---------?----------?---------- ????DEPTNO ---------- ??????7369?SMITH??????CLERK???????????7902?17-DEC-80????????800 ????????20 ??????7499?ALLEN??????SALESMAN????????7698?20-FEB-81???????1600????????300 ????????30 ??????7521?WARD???????SALESMAN????????7698?22-FEB-81???????1250????????500 ????????30 ?????EMPNO?ENAME??????JOB??????????????MGR?HIREDATE?????????SAL???????COMM ----------?----------?---------?----------?---------?----------?---------- ????DEPTNO ---------- ??????7566?JONES??????MANAGER?????????7839?02-APR-81???????2975 ????????20 ??????7654?MARTIN?????SALESMAN????????7698?28-SEP-81???????1250???????1400 ????????30 ??????7698?BLAKE??????MANAGER?????????7839?01-MAY-81???????2850 ????????30 ?????EMPNO?ENAME??????JOB??????????????MGR?HIREDATE?????????SAL???????COMM ----------?----------?---------?----------?---------?----------?---------- ????DEPTNO ---------- ??????7788?SCOTT??????ANALYST?????????7566?19-APR-87???????3000 ????????20 ??????7844?TURNER?????SALESMAN????????7698?08-SEP-81???????1500??????????0 ????????30 ??????7876?ADAMS??????CLERK???????????7788?23-MAY-87???????1100 ????????20 ?????EMPNO?ENAME??????JOB??????????????MGR?HIREDATE?????????SAL???????COMM ----------?----------?---------?----------?---------?----------?---------- ????DEPTNO ---------- ??????7900?JAMES??????CLERK???????????7698?03-DEC-81????????950 ????????30 ??????7902?FORD???????ANALYST?????????7566?03-DEC-81???????3000 ????????20 11?rows?selected. SQL>?commit; Commit?complete. SQL>?select?count(*)?from?emp; ??COUNT(*) ---------- ????????11 SQL>?select?count(*)?from?emp?as?of?scn?1427502; ??COUNT(*) ---------- ????????14 SQL>?show?parameter?fla ORA-00942:?table?or?view?does?not?exist SQL>?select?count(*)?from?emp?as?of?scn?1427582; >>>the?scn?is?the?index?by?database?,it?will?change?now?and?then ??COUNT(*) ---------- ????????11 SQL>?select?count(*)?from?emp?as?of?scn?1427542; >>>if?query?by?a?former?SZN?,?the?count?haven’t?been?delete ??COUNT(*) ---------- ????????14 SQL>?flashback?table?emp?to?scn??1427542; flashback?table?emp?to?scn??1427542 ????????????????* ERROR?at?line?1: ORA-08189:?cannot?flashback?the?table?because?row?movement?is?not?enabled SQL>?alter?table?emp?enable?row?movement; >>>default,?row?movement?is?not?enabled,?should?alter?it! Table?altered. SQL>?select?current_scn?from?v$database; >>>query?the?szn?from?v$databse CURRENT_SCN ----------- ????1138138 SQL>?flashback?table?emp?to?scn??1427542;s Flashback?complete. SQL>?select?count(*)?from?emp; ??COUNT(*) ---------- ????????14 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |