Bo's Oracle Station

查看: 1772|回复: 0

课程第10次(2018-06-07星期四)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-6-7 19:47:42 | 显示全部楼层 |阅读模式
  1. create tablespace tbsfda datafile size 20M autoextend off;

  2. select  * from dba_data_files where tablespace_name='TBSFDA';

  3. select * from dba_flashback_archive;

  4. create flashback archive fda1 tablespace tbsfda
  5. quota 20M retention 2 year;
  6.   
  7. select * from dba_flashback_archive;


复制代码
HR:
  1. [oracle@station87 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 8 03:50:10 2018

  3. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  4. SQL> conn hr/oracle_4U
  5. Connected.
  6. SQL> select  salary from employees where employee_id=100;

  7.     SALARY
  8. ----------
  9.      30000

  10. SQL> alter table employees flashback archive fda1;
  11. alter table employees flashback archive fda1
  12. *
  13. ERROR at line 1:
  14. ORA-55620: No privilege to use Flashback Archive


  15. SQL> alter table employees flashback archive fda1;

  16. Table altered.

  17. SQL> update employees set salary=40000 where employee_id=100;

  18. 1 row updated.

  19. SQL> commit;

  20. Commit complete.

  21. SQL>
复制代码
  1. select  * from dba_tab_privs tp
  2. where tp.grantee='HR';

  3. grant flashback archive on fda1 to hr;

  4. select  * from dba_flashback_archive_tables;

  5. select  * from dba_objects o where o.object_name='EMPLOYEES' and
  6.    o.owner='HR';
  7.    
  8.    select  * from hr.SYS_FBA_HIST_73933;
复制代码

a.png

  1. SQL> create table t05311_a ( a  number , b varchar2(20)) ;

  2. Table created.

  3. SQL> insert into t05311_a values (1,'A') ;

  4. 1 row created.

  5. SQL> commit;

  6. Commit complete.

  7. SQL> alter table t05311_a flashback archive;

  8. Table altered.

  9. SQL>
复制代码

  1. create flashback archive  default fda2 tablespace tbsfda
  2. quota 20M retention 30 day;

  3. select * from dba_flashback_archive;

  4. select  * from dba_flashback_archive_tables;

  5. grant flashback archive on fda2 to hr;

  6. select  * from hr.SYS_FBA_HIST_78247;
复制代码


确认历史表不可更改:

  1. select * from dba_flashback_archive_tables;

  2. select  * from hr.SYS_FBA_HIST_73933;

  3. select salary from hr.employees  as of scn 1272896  where employee_id=100;

  4. update  hr.SYS_FBA_HIST_73933 set salary=20000;
复制代码

b.png


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

  2. TO_CHAR(SYSDATE,'YY
  3. -------------------
  4. 2018-06-08:04:06:11

  5. SQL> alter table t05311_a  drop ( b)  ;

  6. Table altered.

  7. SQL> select  * from t05311_a;

  8.          A
  9. ----------
  10.          1

  11. SQL> update t05311_a set a=2;

  12. 1 row updated.

  13. SQL> commit;

  14. Commit complete.

  15. SQL> select * from t05311_a  as of timestamp to_timestamp('2018-06-08:04:06:11','YYYY-MM-DD:HH24:MI:SS') ;

  16.          A B
  17. ---------- --------------------
  18.          1 A

  19. SQL> select  versions_xid, versions_startscn , a  , b from t05311_a
  20.   2   versions between scn minvalue and maxvalue;

  21. VERSIONS_XID         VERSIONS_STARTSCN            A B
  22. ---------------- ----------------- ---------- --------------------
  23.                                             1 A
  24. 01000C000D030000           1273619            2
  25.                            1273540            1

  26. SQL> alter table t05311_a enable row movement;

  27. Table altered.

  28. SQL> flashback table t05311_a to timestamp to_timestamp('2018-06-08:04:06:11','YYYY-MM-DD:HH24:MI:SS');
  29. flashback table t05311_a to timestamp to_timestamp('2018-06-08:04:06:11','YYYY-MM-DD:HH24:MI:SS')
  30.                 *
  31. ERROR at line 1:
  32. ORA-01466: unable to read data - table definition has changed


  33. SQL>
复制代码

在一个不可扩展的表空间上,FDA空间限额满了,会报以下错误:
  1. update hr.t04209_uname set uvalue=1567 where uname='a1566'
  2.           *
  3. ERROR at line 1:
  4. ORA-55617: Flashback Archive "FDA1" runs out of space and tracking on
  5. "T04209_UNAME" is suspended


  6. update hr.t04209_uname set uvalue=1568 where uname='a1567'
  7.           *
  8. ERROR at line 1:
  9. ORA-55617: Flashback Archive "FDA1" runs out of space and tracking on
  10. "T04209_UNAME" is suspended


  11. update hr.t04209_uname set uvalue=1569 where uname='a1568'
  12.           *
  13. ERROR at line 1:
  14. ORA-55617: Flashback Archive "FDA1" runs out of space and tracking on
  15. "T04209_UNAME" is suspended


  16. update hr.t04209_uname set uvalue=1570 where uname='a1569'
  17.           *
  18. ERROR at line 1:
  19. ORA-01013: user requested cancel of current operation



  20. SQL>

复制代码
  1. select  * from dba_flashback_archive_ts;

  2. select  bytes/1024/1024 from dba_segments s where s.owner='HR'
  3. and s.segment_name='T04209_UNAME';

  4. alter flashback archive fda1 set default;

  5. select  * from dba_flashback_Archive;

  6. select  * from dba_flashback_archive_tables;

  7. select  * from hr.SYS_FBA_HIST_78257;

  8. alter tablespace tbsfda  add datafile size 100M ;

  9. alter flashback archive fda1 modify tablespace tbsfda quota 120M;

  10. select  * from dba_flashback_archive_ts;
复制代码

  1. 8 rows updated.


  2. 8 rows updated.


  3. 8 rows updated.


  4. 8 rows updated.


  5. 8 rows updated.


  6. 8 rows updated.


  7. 8 rows updated.


复制代码

全局方案进化:
  1. SQL> create table part1 ( a  number )
  2.   2  partition by range (a )
  3.   3  (partition p1 values less than (100),
  4.   4   partition p2 values less than (maxvalue)) ;

  5. Table created.

  6. SQL>
  7. SQL>
  8. SQL> insert into part1  values ( 10 ) ;

  9. 1 row created.

  10. SQL> insert into part1  values ( 90 ) ;

  11. 1 row created.

  12. SQL> insert into part1  values ( 100 ) ;

  13. 1 row created.

  14. SQL> commit;

  15. Commit complete.

  16. SQL> select  * from part1 ( p1 ) ;
  17. select        * from part1 ( p1 )
  18.                      *
  19. ERROR at line 1:
  20. ORA-00933: SQL command not properly ended


  21. SQL> select  * from part1  partition ( p1 ) ;

  22.          A
  23. ----------
  24.         10
  25.         90

  26. SQL> select  * from part1  partition ( p2 ) ;

  27.          A
  28. ----------
  29.        100

  30. SQL> alter table part1 flashback archive;

  31. Table altered.

  32. SQL>
  33. SQL>
  34. SQL> alter table part1  split partition p1 at ( 50 )
  35.   2  into ( partition p11 , partition p12 ) ;
  36. alter table part1  split partition p1 at ( 50 )
  37. *
  38. ERROR at line 1:
  39. ORA-55610: Invalid DDL statement on history-tracked table


  40. SQL>
复制代码
  1. select * from dba_flashback_archive_tables ;

  2. select  * from hr.SYS_FBA_HIST_78265;

  3. begin
  4.    dbms_flashback_archive.disassociate_fba(
  5.    owner_name => 'HR',
  6.    table_name => 'PART1');
  7.    end;
  8.    
  9.    update hr.SYS_FBA_HIST_7826a5 set a=8;
  10.    
  11.    commit;
  12.    
  13.    select  * from hr.SYS_FBA_HIST_78265;
  14.    
  15.    begin
  16.    dbms_flashback_archive.reassociate_fba(
  17.    owner_name => 'HR',
  18.    table_name => 'PART1');
  19.    end;
  20.    
复制代码
  1. SQL>
  2. SQL> alter table part1  split partition p1 at ( 50 )
  3.   2  into ( partition p11 , partition p12 ) ;

  4. Table altered.

  5. SQL> select  * from part1 partition (p11) ;

  6.          A
  7. ----------
  8.         20

  9. SQL> select  * from part1 partition (p12) ;

  10.          A
  11. ----------
  12.         90

  13. SQL> select  * from part1 partition (p2) ;

  14.          A
  15. ----------
  16.        100

  17. SQL> select  * from part1 as of scn 1347953;

  18.          A
  19. ----------
  20.          8
  21.         20
  22.         90
  23.        100

  24. SQL>
复制代码

闪回删除表:
  1. SQL> conn / as sysdba
  2. Connected.
  3. SQL> show parameter recycle

  4. NAME                                     TYPE         VALUE
  5. ------------------------------------ ----------- ------------------------------
  6. buffer_pool_recycle                     string
  7. db_recycle_cache_size                     big integer 0
  8. recyclebin                             string         on
  9. SQL> conn hr/oracle_4U
  10. Connected.
  11. SQL> create table t05311_b ( a  number )  ;

  12. Table created.

  13. SQL> insert into t05311_b values (1)  ;

  14. 1 row created.

  15. SQL> commit;

  16. Commit complete.

  17. SQL> drop table t05311_b ;

  18. Table dropped.

  19. SQL> create table t05311_b ( a  date  )  ;

  20. Table created.

  21. SQL> insert into t05311_b values ( sysdate ) ;

  22. 1 row created.

  23. SQL> commit;

  24. Commit complete.

  25. SQL> drop table t05311_b ;

  26. Table dropped.

  27. SQL> create table t05311_c ( a  varchar2(20)   )  ;

  28. Table created.

  29. SQL> rename t05311_c to t05311_b;

  30. Table renamed.

  31. SQL> insert into t05311_b values ( 'A' ) ;

  32. 1 row created.

  33. SQL> commit;

  34. Commit complete.

  35. SQL> drop table t05311_b;

  36. Table dropped.

  37. SQL> flashback table t05311_b to before drop ;

  38. Flashback complete.

  39. SQL> select  * from t05311_b;

  40. A
  41. --------------------
  42. A

  43. SQL> flashback table  "BIN$bhOnD4s0L7XgUKjAVwAvIA==$0" to before drop rename to t05311_c;

  44. Flashback complete.

  45. SQL> select  * from t05311_c;

  46.          A
  47. ----------
  48.          1

  49. SQL> insert into t04209_uname select  * from t04209_uname;

  50. 800000 rows created.

  51. SQL> rollback;

  52. Rollback complete.

  53. SQL> flashback table  t05311_b  to before drop rename to t05311_d;
  54. flashback table  t05311_b  to before drop rename to t05311_d
  55. *
  56. ERROR at line 1:
  57. ORA-38305: object not in RECYCLE BIN


  58. SQL>
复制代码


  1. select bytes/1024/1024  from dba_data_files
  2. where tablespace_name='USERS';

  3. select  * from dba_recyclebin;

  4. select  * from hr."BIN$bhOnD4s0L7XgUKjAVwAvIA==$0";
复制代码


  1. SQL> create table t05311_d ( a  number ) ;

  2. Table created.

  3. SQL> insert into t05311_d values (1) ;

  4. 1 row created.

  5. SQL> commit;

  6. Commit complete.

  7. SQL> drop  table t05311_d purge;

  8. Table dropped.

  9. SQL> flashback table t05311_d to before drop;
  10. flashback table t05311_d to before drop
  11. *
  12. ERROR at line 1:
  13. ORA-38305: object not in RECYCLE BIN


  14. SQL> create table t05311_d ( a  number ) ;

  15. Table created.

  16. SQL> insert into t05311_d values (1) ;

  17. 1 row created.

  18. SQL> commit;

  19. Commit complete.

  20. SQL> drop  table t05311_d ;

  21. Table dropped.

  22. SQL> purge table t05311_d;

  23. Table purged.

  24. SQL> create table t05311_d ( a  number ) ;

  25. Table created.

  26. SQL> insert into t05311_d values (1) ;

  27. 1 row created.

  28. SQL> commit;

  29. Commit complete.

  30. SQL> drop  table t05311_d ;

  31. Table dropped.

  32. SQL>
复制代码

  1. select * from dba_recyclebin;

  2. purge tablespace users user hr;

  3. purge dba_recyclebin;
复制代码
  1. select  flashback_on from v$database;

  2. select  * from v$flashback_database_log;

  3. alter database flashback on;

  4. select  * from v$flashback_database_logfile;
复制代码


回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 14:17 , Processed in 0.059777 second(s), 27 queries .

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