Bo's Oracle Station

查看: 2460|回复: 0

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

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2015-8-27 15:31:11 | 显示全部楼层 |阅读模式
本帖最后由 botang 于 2015-8-27 15:48 编辑

上完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. select  * from DBA_FLASHBACK_TXN_REPORT ;


  20.    
  21. select  * from flashback_transaction_query
  22.    where table_name='T05310_FLASHBACK3' and table_owner='HR'   order  by  commit_scn ;
  23.    
  24.      
  25.    begin
  26.       dbms_flashback.transaction_backout(numtxns => 1,
  27.       xids => xid_array('0300010000060000'),
  28.       options => dbms_flashback.nocascade_force);
  29.    end;
  30.    
  31.    commit;
  32.    
  33.    -----
  34.    select  * from flashback_transaction_query
  35.    where table_name='T05310_FLASHBACK5' and table_owner='HR'   order  by  commit_scn ;
  36.    
  37.    
  38.    begin
  39.       dbms_flashback.transaction_backout(numtxns => 1,
  40.       xids => xid_array('070002004A050000'),
  41.       options => dbms_flashback.nocascade_force);
  42.    end;
  43.    commit;
  44.    
  45.    
  46.      begin
  47.       dbms_flashback.transaction_backout(numtxns => 1,
  48.       xids => xid_array('0300070006060000'),
  49.       options => dbms_flashback.nonconflict_only);
  50.    end;
  51.    commit;
  52.    
  53.   select  * from DBA_FLASHBACK_TXN_REPORT  order by commit_time;
  54.   
  55.   
  56.   
  57.   
  58.    
复制代码


2015-08-26b.sql:
  1. select  * from dba_flashback_archive;

  2. select  * from dba_flashback_archive_tables;

  3. select  * from dba_flashback_archive_ts;

  4. create tablespace tbs05311  datafile size 20M autoextend on ;

  5. create flashback archive  fda1
  6. tablespace tbs05311 quota 500M retention  2 year;

  7. create flashback archive fda2
  8. tablespace tbs05311 quota 500M retention  1 year;

  9. alter   flashback archive fda1  set  default;


  10. create flashback archive   default fda3
  11. tablespace tbs05311 quota 500M retention  10 year;

  12. grant flashback archive on fda1 to hr;

  13. select  * from  hr.SYS_FBA_HIST_73953;


  14. create undo tablespace undotbs2 datafile size 20M autoextend on;

  15. alter system set undo_tablespace=undotbs2;

  16. drop tablespace undotbs1;

  17. select  * from dba_Tablespaces;

  18. select  * from v$transaction;

复制代码




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>
















回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 16:53 , Processed in 0.042241 second(s), 24 queries .

快速回复 返回顶部 返回列表