OCP培训笔记-flashback

来源：互联网发布：买家如何开通淘宝客编辑：程序博客网时间：2024/05/16 09:35

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

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300

7521 WARD SALESMAN 7698 22-FEB-81 1250 500

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7566 JONES MANAGER 7839 02-APR-81 2975

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400

7698 BLAKE MANAGER 7839 01-MAY-81 2850

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7782 CLARK MANAGER 7839 09-JUN-81 2450

7788 SCOTT ANALYST 7566 19-APR-87 3000

7839 KING PRESIDENT 17-NOV-81 5000

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0

7876 ADAMS CLERK 7788 23-MAY-87 1100

7900 JAMES CLERK 7698 03-DEC-81 950

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7902 FORD ANALYST 7566 03-DEC-81 3000

7934 MILLER CLERK 7782 23-JAN-82 1300

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)

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$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)

COMM 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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, 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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, 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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, 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, OLAP, 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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, 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, OLAP, 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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, 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, OLAP, 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

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300

7521 WARD SALESMAN 7698 22-FEB-81 1250 500

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7566 JONES MANAGER 7839 02-APR-81 2975

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400

7698 BLAKE MANAGER 7839 01-MAY-81 2850

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7788 SCOTT ANALYST 7566 19-APR-87 3000

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0

7876 ADAMS CLERK 7788 23-MAY-87 1100

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7900 JAMES CLERK 7698 03-DEC-81 950

7902 FORD ANALYST 7566 03-DEC-81 3000

11 rows selected.

SQL> commit;

Commit complete.

SQL> select count(*) from emp;

COUNT(*)

----------

SQL> select count(*) from emp as of scn 1427502;

COUNT(*)

----------

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(*)

----------

SQL> select count(*) from emp as of scn 1427542; >>>if query by a former SZN , the count haven’t been delete

COUNT(*)

----------

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(*)

----------