第47次：2015-08-26星期三

botang

上完1Z0-053第10章        （52-40）

2015-08-26a.sql：

1. select  d.SUPPLEMENTAL_LOG_DATA_MIN, d.SUPPLEMENTAL_LOG_DATA_PK
   
2.    from v_$database d ;
   
3.    
   
4.    
   
5. select  * from flashback_transaction_query
   
6.    where table_name='T05310_FLASHBACK2' and table_owner='HR'   order  by  commit_scn ;
   
7.    
   
8.    begin
   
9.       dbms_flashback.transaction_backout(numtxns => 1,
   
10.       xids => xid_array('0700130070050000'),
    
11.       options => dbms_flashback.cascade);
    
12.    end;
    
13.    
    
14.    
    
15.    
    
16.    
    
17.    commit;
    
18. select  * from DBA_FLASHBACK_TXN_STATE ;
    
19. 
    
20. 
    
21. select  * from DBA_FLASHBACK_TXN_REPORT ;
    
22. 
    
23. 
    
24.    
    
25. select  * from flashback_transaction_query
    
26.    where table_name='T05310_FLASHBACK3' and table_owner='HR'   order  by  commit_scn ;
    
27.    
    
28.      
    
29.    begin
    
30.       dbms_flashback.transaction_backout(numtxns => 1,
    
31.       xids => xid_array('0300010000060000'),
    
32.       options => dbms_flashback.nocascade_force);
    
33.    end;
    
34.    
    
35.    commit;
    
36.    
    
37.    -----
    
38.    select  * from flashback_transaction_query
    
39.    where table_name='T05310_FLASHBACK5' and table_owner='HR'   order  by  commit_scn ;
    
40.    
    
41.    
    
42.    begin
    
43.       dbms_flashback.transaction_backout(numtxns => 1,
    
44.       xids => xid_array('070002004A050000'),
    
45.       options => dbms_flashback.nocascade_force);
    
46.    end;
    
47.    commit;
    
48.    
    
49.    
    
50.      begin
    
51.       dbms_flashback.transaction_backout(numtxns => 1,
    
52.       xids => xid_array('0300070006060000'),
    
53.       options => dbms_flashback.nonconflict_only);
    
54.    end;
    
55.    commit;
    
56.    
    
57.   select  * from DBA_FLASHBACK_TXN_REPORT  order by commit_time;
    
58.   
    
59.   
    
60.   
    
61.   
    
62.    

复制代码

2015-08-26b.sql：

1. select  * from dba_flashback_archive;
   
2. 
   
3. select  * from dba_flashback_archive_tables;
   
4. 
   
5. select  * from dba_flashback_archive_ts;
   
6. 
   
7. create tablespace tbs05311  datafile size 20M autoextend on ;
   
8. 
   
9. create flashback archive  fda1
   
10. tablespace tbs05311 quota 500M retention  2 year;
    
11. 
    
12. create flashback archive fda2
    
13. tablespace tbs05311 quota 500M retention  1 year;
    
14. 
    
15. alter   flashback archive fda1  set  default;
    
16. 
    
17. 
    
18. create flashback archive   default fda3
    
19. tablespace tbs05311 quota 500M retention  10 year;
    
20. 
    
21. grant flashback archive on fda1 to hr;
    
22. 
    
23. select  * from  hr.SYS_FBA_HIST_73953;
    
24. 
    
25. 
    
26. create undo tablespace undotbs2 datafile size 20M autoextend on;
    
27. 
    
28. alter system set undo_tablespace=undotbs2;
    
29. 
    
30. drop tablespace undotbs1;
    
31. 
    
32. select  * from dba_Tablespaces;
    
33. 
    
34. select  * from v$transaction;
    
35. 
    

复制代码

t05310.sql

1. undefine s1
   
2. create table &&s1( id number ) ;
   
3. ! sleep 5
   
4. insert into &&s1 values (1) ;
   
5. insert into &&s1 values (2) ;
   
6. insert into &&s1 values (3) ;
   
7. commit;
   
8. update &&s1 set id=11 where id=1;
   
9. update &&s1 set id=22 where id=2;
   
10. update &&s1 set id=33 where id=3;
    
11. commit;
    
12. update &&s1 set id=222 where id=22;
    
13. update &&s1 set id=333 where id=33;
    
14. commit;
    
15. update &&s1 set id=22 where id=222;
    
16. commit;

复制代码

HR：

[root@station90 ~]# su - oracle
[oracle@station90 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 26 20:44:01 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> conn hr/oracle_4U
ERROR:
ORA-28002: the password will expire within 5 days


Connected.
SQL> select salary from employees where employee_id=100;

    SALARY
----------
     34002

SQL> alter table employees  flashback archive;
alter table employees  flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive


SQL> /

Table altered.

SQL> update employees set salary=50000 where employee_id=100;

1 row updated.

SQL> commit;

Commit complete.

SQL> select  salary from employees  where employee_id=100
  2  ;

    SALARY
----------
     50000

SQL> sselect  salary from employees
SP2-0734: unknown command beginning "sselect  s..." - rest of line ignored.
SQL>  

SQL>
SQL> select  salary from employees
  2    versions

SQL>
SQL>
SQL> select versions_xid , salary
  2    from employees
  3   versions between  scn minvalue and maxvalue
  4   where employee_id=100;

VERSIONS_XID         SALARY
---------------- ----------
              34002
0600060031060000      50000

SQL> select versions_xid , salary
  2   from employees
  3  versions between  scn minvalue and maxvalue
  4  where employee_id=100;

VERSIONS_XID         SALARY
---------------- ----------
              34002
0600060031060000      50000

SQL> select versions_xid , versions_startscn , salary
  2  from employees
  3   versions between  scn minvalue and maxvalue
  4    where employee_id=100;

VERSIONS_XID     VERSIONS_STARTSCN     SALARY
---------------- ----------------- ----------
                    34002
0600060031060000       2247793    50000

SQL> select  salary  from employees
  2  as of scn 2247792
  3  where employee_id=100;

    SALARY
----------
     34002

SQL> alter table employees enable row movement ;

Table altered.

SQL> flashback table employees to scn 2247792;

Flashback complete.

SQL> select  salary from employees  where employee_id=100;

    SALARY
----------
     34002

SQL> alter table departments flashback archive fda2 ;
alter table departments flashback archive fda2
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive


SQL> /

Table altered.

SQL>  select  salary from employees  where employee_id=100;

    SALARY
----------
     34002

SQL> update employees set salary=60000   where employee_id=100;

1 row updated.

SQL> commit;

Commit complete.

SQL> select  salary  from employees
  2   as of scn 2247792
  3   where employee_id=100;

    SALARY
----------
     34002

SQL> select  salary  from employees
  2   as of scn 2247792
  3   where employee_id=100;

    SALARY
----------
     34002

SQL> alter table employees flashback archive none;
alter table employees flashback archive none
*
ERROR at line 1:
ORA-55605: Incorrect Flashback Archive is specified


SQL> alter table employees no flashback archive
  2  ;
alter table employees no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive


SQL> alter table employees no flashback archive  fda1 ;
alter table employees no flashback archive  fda1
                                            *
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option


SQL> alter table employees no flashback archive ;
alter table employees no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive


SQL> select  salary  from employees
  2   as of scn 2247792
  3   where employee_id=100;
select    salary    from employees
                     *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number  with name "" too small


SQL> alter table employees flashback archive ;

Table altered.

SQL> select  salary from employees where employee_id=100;

    SALARY
----------
     60000

SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')   from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2015-08-26:21:36:22

SQL> update employees set salary=70000;

107 rows updated.

SQL> rollback;

Rollback complete.

SQL> update employees set salary=70000 where employee_id=100;

1 row updated.

SQL>
SQL> commit;

Commit complete.

SQL> select  * from employees where employee_id=100;

EMPLOYEE_ID FIRST_NAME         LAST_NAME
----------- -------------------- -------------------------
EMAIL              PHONE_NUMBER           HIRE_DATE      JOB_ID
------------------------- -------------------- ------------------ ----------
    SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- -------------- ---------- -------------
    100 Steven         King
SKING              515.123.4567           17-JUN-03      AD_PRES
     70000                    90


SQL> select salary from employees
  2   as of timestamp to_timestamp('2015-08-26:21:36:22','YYYY-MM-DD:HH24:MI:SS')
  3   where employee_id=100;

    SALARY
----------
     60000

SQL> /

    SALARY
----------
     40000

SQL> flashback table employees to timestamp to_timestamp('2015-08-26:21:36:22','YYYY-MM-DD:HH24:MI:SS');

Flashback complete.

SQL> select salary from employees
  2   where employee_id=100;

    SALARY
----------
     40000

SQL> insert into part1 values ( 1 , 'A' ) ;

1 row created.

SQL> insert into part1 values ( 50 , 'B' ) ;

1 row created.

SQL> insert into part1 values ( 500 , 'C' ) ;

1 row created.

SQL> commit;

Commit complete.

SQL> select  * from part1;

     A B
---------- --------------------
     1 A
    50 B
       500 C

SQL> select  * from part1 partition (part1_p1) ;

     A B
---------- --------------------
     1 A
    50 B

SQL> alter table part1  flashback archive;

Table altered.

SQL> alter table part1  split partition part1_p1  at (50)  
  2    into  ( partition part1_p11, partition part1_p12) ;
alter table part1  split partition part1_p1  at (50)
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


SQL> /

Table altered.

SQL> select  * from part1 partition (part1_p11) ;

     A B
---------- --------------------
     1 A

SQL> select  * from part1 partition (part1_p12) ;

     A B
---------- --------------------
    50 B

SQL> select  * from part1 partition (part1_p2) ;

     A B
---------- --------------------
       500 C

SQL>








[root@station90 ~]# su - oracle
[oracle@station90 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 26 20:44:01 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> conn hr/oracle_4U
ERROR:
ORA-28002: the password will expire within 5 days


Connected.
SQL> select salary from employees where employee_id=100;

    SALARY
----------
     34002

SQL> alter table employees  flashback archive;
alter table employees  flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive


SQL> /

Table altered.

SQL> update employees set salary=50000 where employee_id=100;

1 row updated.

SQL> commit;

Commit complete.

SQL> select  salary from employees  where employee_id=100
  2  ;

    SALARY
----------
     50000

SQL> sselect  salary from employees
SP2-0734: unknown command beginning "sselect  s..." - rest of line ignored.
SQL>  

SQL>
SQL> select  salary from employees
  2    versions

SQL>
SQL>
SQL> select versions_xid , salary
  2    from employees
  3   versions between  scn minvalue and maxvalue
  4   where employee_id=100;

VERSIONS_XID         SALARY
---------------- ----------
              34002
0600060031060000      50000

SQL> select versions_xid , salary
  2   from employees
  3  versions between  scn minvalue and maxvalue
  4  where employee_id=100;

VERSIONS_XID         SALARY
---------------- ----------
              34002
0600060031060000      50000

SQL> select versions_xid , versions_startscn , salary
  2  from employees
  3   versions between  scn minvalue and maxvalue
  4    where employee_id=100;

VERSIONS_XID     VERSIONS_STARTSCN     SALARY
---------------- ----------------- ----------
                    34002
0600060031060000       2247793    50000

SQL> select  salary  from employees
  2  as of scn 2247792
  3  where employee_id=100;

    SALARY
----------
     34002

SQL> alter table employees enable row movement ;

Table altered.

SQL> flashback table employees to scn 2247792;

Flashback complete.

SQL> select  salary from employees  where employee_id=100;

    SALARY
----------
     34002

SQL> alter table departments flashback archive fda2 ;
alter table departments flashback archive fda2
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive


SQL> /

Table altered.

SQL>  select  salary from employees  where employee_id=100;

    SALARY
----------
     34002

SQL> update employees set salary=60000   where employee_id=100;

1 row updated.

SQL> commit;

Commit complete.

SQL> select  salary  from employees
  2   as of scn 2247792
  3   where employee_id=100;

    SALARY
----------
     34002

SQL> select  salary  from employees
  2   as of scn 2247792
  3   where employee_id=100;

    SALARY
----------
     34002

SQL> alter table employees flashback archive none;
alter table employees flashback archive none
*
ERROR at line 1:
ORA-55605: Incorrect Flashback Archive is specified


SQL> alter table employees no flashback archive
  2  ;
alter table employees no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive


SQL> alter table employees no flashback archive  fda1 ;
alter table employees no flashback archive  fda1
                                            *
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option


SQL> alter table employees no flashback archive ;
alter table employees no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive


SQL> select  salary  from employees
  2   as of scn 2247792
  3   where employee_id=100;
select    salary    from employees
                     *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number  with name "" too small


SQL> alter table employees flashback archive ;

Table altered.

SQL> select  salary from employees where employee_id=100;

    SALARY
----------
     60000

SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')   from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2015-08-26:21:36:22

SQL> update employees set salary=70000;

107 rows updated.

SQL> rollback;

Rollback complete.

SQL> update employees set salary=70000 where employee_id=100;

1 row updated.

SQL>
SQL> commit;

Commit complete.

SQL> select  * from employees where employee_id=100;

EMPLOYEE_ID FIRST_NAME         LAST_NAME
----------- -------------------- -------------------------
EMAIL              PHONE_NUMBER           HIRE_DATE      JOB_ID
------------------------- -------------------- ------------------ ----------
    SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- -------------- ---------- -------------
    100 Steven         King
SKING              515.123.4567           17-JUN-03      AD_PRES
     70000                    90


SQL> select salary from employees
  2   as of timestamp to_timestamp('2015-08-26:21:36:22','YYYY-MM-DD:HH24:MI:SS')
  3   where employee_id=100;

    SALARY
----------
     60000

SQL> /

    SALARY
----------
     40000

SQL> flashback table employees to timestamp to_timestamp('2015-08-26:21:36:22','YYYY-MM-DD:HH24:MI:SS');

Flashback complete.

SQL> select salary from employees
  2   where employee_id=100;

    SALARY
----------
     40000

SQL> insert into part1 values ( 1 , 'A' ) ;

1 row created.

SQL> insert into part1 values ( 50 , 'B' ) ;

1 row created.

SQL> insert into part1 values ( 500 , 'C' ) ;

1 row created.

SQL> commit;

Commit complete.

SQL> select  * from part1;

     A B
---------- --------------------
     1 A
    50 B
       500 C

SQL> select  * from part1 partition (part1_p1) ;

     A B
---------- --------------------
     1 A
    50 B

SQL> alter table part1  flashback archive;

Table altered.

SQL> alter table part1  split partition part1_p1  at (50)  
  2    into  ( partition part1_p11, partition part1_p12) ;
alter table part1  split partition part1_p1  at (50)
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


SQL> /

Table altered.

SQL> select  * from part1 partition (part1_p11) ;

     A B
---------- --------------------
     1 A

SQL> select  * from part1 partition (part1_p12) ;

     A B
---------- --------------------
    50 B

SQL> select  * from part1 partition (part1_p2) ;

     A B
---------- --------------------
       500 C

SQL>