课程第10次（2018-06-07星期四）

botang

1. create tablespace tbsfda datafile size 20M autoextend off;
   
2. 
   
3. select  * from dba_data_files where tablespace_name='TBSFDA';
   
4. 
   
5. select * from dba_flashback_archive;
   
6. 
   
7. create flashback archive fda1 tablespace tbsfda
   
8. quota 20M retention 2 year;
   
9.   
   
10. select * from dba_flashback_archive;
    
11. 
    
12. 
    

复制代码

HR：

1. [oracle@station87 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 8 03:50:10 2018
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn hr/oracle_4U
   
8. Connected.
   
9. SQL> select  salary from employees where employee_id=100;
   
10. 
    
11.     SALARY
    
12. ----------
    
13.      30000
    
14. 
    
15. SQL> alter table employees flashback archive fda1;
    
16. alter table employees flashback archive fda1
    
17. *
    
18. ERROR at line 1:
    
19. ORA-55620: No privilege to use Flashback Archive
    
20. 
    
21. 
    
22. SQL> alter table employees flashback archive fda1;
    
23. 
    
24. Table altered.
    
25. 
    
26. SQL> update employees set salary=40000 where employee_id=100;
    
27. 
    
28. 1 row updated.
    
29. 
    
30. SQL> commit;
    
31. 
    
32. Commit complete.
    
33. 
    
34. SQL>
    

复制代码

1. select  * from dba_tab_privs tp
   
2. where tp.grantee='HR';
   
3. 
   
4. grant flashback archive on fda1 to hr;
   
5. 
   
6. select  * from dba_flashback_archive_tables;
   
7. 
   
8. select  * from dba_objects o where o.object_name='EMPLOYEES' and
   
9.    o.owner='HR';
   
10.    
    
11.    select  * from hr.SYS_FBA_HIST_73933;

复制代码

1. SQL> create table t05311_a ( a  number , b varchar2(20)) ;
   
2. 
   
3. Table created.
   
4. 
   
5. SQL> insert into t05311_a values (1,'A') ;
   
6. 
   
7. 1 row created.
   
8. 
   
9. SQL> commit;
   
10. 
    
11. Commit complete.
    
12. 
    
13. SQL> alter table t05311_a flashback archive;
    
14. 
    
15. Table altered.
    
16. 
    
17. SQL>
    

复制代码

1. create flashback archive  default fda2 tablespace tbsfda
   
2. quota 20M retention 30 day;
   
3. 
   
4. select * from dba_flashback_archive;
   
5. 
   
6. select  * from dba_flashback_archive_tables;
   
7. 
   
8. grant flashback archive on fda2 to hr;
   
9. 
   
10. select  * from hr.SYS_FBA_HIST_78247;

复制代码

确认历史表不可更改：

1. select * from dba_flashback_archive_tables;
   
2. 
   
3. select  * from hr.SYS_FBA_HIST_73933;
   
4. 
   
5. select salary from hr.employees  as of scn 1272896  where employee_id=100;
   
6. 
   
7. update  hr.SYS_FBA_HIST_73933 set salary=20000;

复制代码

1. SQL> select  to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')  from dual;
   
2. 
   
3. TO_CHAR(SYSDATE,'YY
   
4. -------------------
   
5. 2018-06-08:04:06:11
   
6. 
   
7. SQL> alter table t05311_a  drop ( b)  ;
   
8. 
   
9. Table altered.
   
10. 
    
11. SQL> select  * from t05311_a;
    
12. 
    
13.          A
    
14. ----------
    
15.          1
    
16. 
    
17. SQL> update t05311_a set a=2;
    
18. 
    
19. 1 row updated.
    
20. 
    
21. SQL> commit;
    
22. 
    
23. Commit complete.
    
24. 
    
25. SQL> select * from t05311_a  as of timestamp to_timestamp('2018-06-08:04:06:11','YYYY-MM-DD:HH24:MI:SS') ;
    
26. 
    
27.          A B
    
28. ---------- --------------------
    
29.          1 A
    
30. 
    
31. SQL> select  versions_xid, versions_startscn , a  , b from t05311_a
    
32.   2   versions between scn minvalue and maxvalue;
    
33. 
    
34. VERSIONS_XID         VERSIONS_STARTSCN            A B
    
35. ---------------- ----------------- ---------- --------------------
    
36.                                             1 A
    
37. 01000C000D030000           1273619            2
    
38.                            1273540            1
    
39. 
    
40. SQL> alter table t05311_a enable row movement;
    
41. 
    
42. Table altered.
    
43. 
    
44. SQL> flashback table t05311_a to timestamp to_timestamp('2018-06-08:04:06:11','YYYY-MM-DD:HH24:MI:SS');
    
45. flashback table t05311_a to timestamp to_timestamp('2018-06-08:04:06:11','YYYY-MM-DD:HH24:MI:SS')
    
46.                 *
    
47. ERROR at line 1:
    
48. ORA-01466: unable to read data - table definition has changed
    
49. 
    
50. 
    
51. 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. 
   
7. 
   
8. update hr.t04209_uname set uvalue=1568 where uname='a1567'
   
9.           *
   
10. ERROR at line 1:
    
11. ORA-55617: Flashback Archive "FDA1" runs out of space and tracking on
    
12. "T04209_UNAME" is suspended
    
13. 
    
14. 
    
15. update hr.t04209_uname set uvalue=1569 where uname='a1568'
    
16.           *
    
17. ERROR at line 1:
    
18. ORA-55617: Flashback Archive "FDA1" runs out of space and tracking on
    
19. "T04209_UNAME" is suspended
    
20. 
    
21. 
    
22. update hr.t04209_uname set uvalue=1570 where uname='a1569'
    
23.           *
    
24. ERROR at line 1:
    
25. ORA-01013: user requested cancel of current operation
    
26. 
    
27. 
    
28. 
    
29. SQL>
    
30. 
    

复制代码

1. select  * from dba_flashback_archive_ts;
   
2. 
   
3. select  bytes/1024/1024 from dba_segments s where s.owner='HR'
   
4. and s.segment_name='T04209_UNAME';
   
5. 
   
6. alter flashback archive fda1 set default;
   
7. 
   
8. select  * from dba_flashback_Archive;
   
9. 
   
10. select  * from dba_flashback_archive_tables;
    
11. 
    
12. select  * from hr.SYS_FBA_HIST_78257;
    
13. 
    
14. alter tablespace tbsfda  add datafile size 100M ;
    
15. 
    
16. alter flashback archive fda1 modify tablespace tbsfda quota 120M;
    
17. 
    
18. select  * from dba_flashback_archive_ts;
    

复制代码

1. 
   
2. 8 rows updated.
   
3. 
   
4. 
   
5. 8 rows updated.
   
6. 
   
7. 
   
8. 8 rows updated.
   
9. 
   
10. 
    
11. 8 rows updated.
    
12. 
    
13. 
    
14. 8 rows updated.
    
15. 
    
16. 
    
17. 8 rows updated.
    
18. 
    
19. 
    
20. 8 rows updated.
    
21. 
    
22. 
    

复制代码

全局方案进化：

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. 
   
6. Table created.
   
7. 
   
8. SQL>
   
9. SQL>
   
10. SQL> insert into part1  values ( 10 ) ;
    
11. 
    
12. 1 row created.
    
13. 
    
14. SQL> insert into part1  values ( 90 ) ;
    
15. 
    
16. 1 row created.
    
17. 
    
18. SQL> insert into part1  values ( 100 ) ;
    
19. 
    
20. 1 row created.
    
21. 
    
22. SQL> commit;
    
23. 
    
24. Commit complete.
    
25. 
    
26. SQL> select  * from part1 ( p1 ) ;
    
27. select        * from part1 ( p1 )
    
28.                      *
    
29. ERROR at line 1:
    
30. ORA-00933: SQL command not properly ended
    
31. 
    
32. 
    
33. SQL> select  * from part1  partition ( p1 ) ;
    
34. 
    
35.          A
    
36. ----------
    
37.         10
    
38.         90
    
39. 
    
40. SQL> select  * from part1  partition ( p2 ) ;
    
41. 
    
42.          A
    
43. ----------
    
44.        100
    
45. 
    
46. SQL> alter table part1 flashback archive;
    
47. 
    
48. Table altered.
    
49. 
    
50. SQL>
    
51. SQL>
    
52. SQL> alter table part1  split partition p1 at ( 50 )
    
53.   2  into ( partition p11 , partition p12 ) ;
    
54. alter table part1  split partition p1 at ( 50 )
    
55. *
    
56. ERROR at line 1:
    
57. ORA-55610: Invalid DDL statement on history-tracked table
    
58. 
    
59. 
    
60. SQL>
    

复制代码

1. select * from dba_flashback_archive_tables ;
   
2. 
   
3. select  * from hr.SYS_FBA_HIST_78265;
   
4. 
   
5. begin
   
6.    dbms_flashback_archive.disassociate_fba(
   
7.    owner_name => 'HR',
   
8.    table_name => 'PART1');
   
9.    end;
   
10.    
    
11.    update hr.SYS_FBA_HIST_7826a5 set a=8;
    
12.    
    
13.    commit;
    
14.    
    
15.    select  * from hr.SYS_FBA_HIST_78265;
    
16.    
    
17.    begin
    
18.    dbms_flashback_archive.reassociate_fba(
    
19.    owner_name => 'HR',
    
20.    table_name => 'PART1');
    
21.    end;
    
22.    

复制代码

1. SQL>
   
2. SQL> alter table part1  split partition p1 at ( 50 )
   
3.   2  into ( partition p11 , partition p12 ) ;
   
4. 
   
5. Table altered.
   
6. 
   
7. SQL> select  * from part1 partition (p11) ;
   
8. 
   
9.          A
   
10. ----------
    
11.         20
    
12. 
    
13. SQL> select  * from part1 partition (p12) ;
    
14. 
    
15.          A
    
16. ----------
    
17.         90
    
18. 
    
19. SQL> select  * from part1 partition (p2) ;
    
20. 
    
21.          A
    
22. ----------
    
23.        100
    
24. 
    
25. SQL> select  * from part1 as of scn 1347953;
    
26. 
    
27.          A
    
28. ----------
    
29.          8
    
30.         20
    
31.         90
    
32.        100
    
33. 
    
34. SQL>
    

复制代码

闪回删除表：

1. SQL> conn / as sysdba
   
2. Connected.
   
3. SQL> show parameter recycle
   
4. 
   
5. NAME                                     TYPE         VALUE
   
6. ------------------------------------ ----------- ------------------------------
   
7. buffer_pool_recycle                     string
   
8. db_recycle_cache_size                     big integer 0
   
9. recyclebin                             string         on
   
10. SQL> conn hr/oracle_4U
    
11. Connected.
    
12. SQL> create table t05311_b ( a  number )  ;
    
13. 
    
14. Table created.
    
15. 
    
16. SQL> insert into t05311_b values (1)  ;
    
17. 
    
18. 1 row created.
    
19. 
    
20. SQL> commit;
    
21. 
    
22. Commit complete.
    
23. 
    
24. SQL> drop table t05311_b ;
    
25. 
    
26. Table dropped.
    
27. 
    
28. SQL> create table t05311_b ( a  date  )  ;
    
29. 
    
30. Table created.
    
31. 
    
32. SQL> insert into t05311_b values ( sysdate ) ;
    
33. 
    
34. 1 row created.
    
35. 
    
36. SQL> commit;
    
37. 
    
38. Commit complete.
    
39. 
    
40. SQL> drop table t05311_b ;
    
41. 
    
42. Table dropped.
    
43. 
    
44. SQL> create table t05311_c ( a  varchar2(20)   )  ;
    
45. 
    
46. Table created.
    
47. 
    
48. SQL> rename t05311_c to t05311_b;
    
49. 
    
50. Table renamed.
    
51. 
    
52. SQL> insert into t05311_b values ( 'A' ) ;
    
53. 
    
54. 1 row created.
    
55. 
    
56. SQL> commit;
    
57. 
    
58. Commit complete.
    
59. 
    
60. SQL> drop table t05311_b;
    
61. 
    
62. Table dropped.
    
63. 
    
64. SQL> flashback table t05311_b to before drop ;
    
65. 
    
66. Flashback complete.
    
67. 
    
68. SQL> select  * from t05311_b;
    
69. 
    
70. A
    
71. --------------------
    
72. A
    
73. 
    
74. SQL> flashback table  "BIN$bhOnD4s0L7XgUKjAVwAvIA==$0" to before drop rename to t05311_c;
    
75. 
    
76. Flashback complete.
    
77. 
    
78. SQL> select  * from t05311_c;
    
79. 
    
80.          A
    
81. ----------
    
82.          1
    
83. 
    
84. SQL> insert into t04209_uname select  * from t04209_uname;
    
85. 
    
86. 800000 rows created.
    
87. 
    
88. SQL> rollback;
    
89. 
    
90. Rollback complete.
    
91. 
    
92. SQL> flashback table  t05311_b  to before drop rename to t05311_d;
    
93. flashback table  t05311_b  to before drop rename to t05311_d
    
94. *
    
95. ERROR at line 1:
    
96. ORA-38305: object not in RECYCLE BIN
    
97. 
    
98. 
    
99. SQL>
    

复制代码

1. select bytes/1024/1024  from dba_data_files
   
2. where tablespace_name='USERS';
   
3. 
   
4. select  * from dba_recyclebin;
   
5. 
   
6. select  * from hr."BIN$bhOnD4s0L7XgUKjAVwAvIA==$0";

复制代码

1. SQL> create table t05311_d ( a  number ) ;
   
2. 
   
3. Table created.
   
4. 
   
5. SQL> insert into t05311_d values (1) ;
   
6. 
   
7. 1 row created.
   
8. 
   
9. SQL> commit;
   
10. 
    
11. Commit complete.
    
12. 
    
13. SQL> drop  table t05311_d purge;
    
14. 
    
15. Table dropped.
    
16. 
    
17. SQL> flashback table t05311_d to before drop;
    
18. flashback table t05311_d to before drop
    
19. *
    
20. ERROR at line 1:
    
21. ORA-38305: object not in RECYCLE BIN
    
22. 
    
23. 
    
24. SQL> create table t05311_d ( a  number ) ;
    
25. 
    
26. Table created.
    
27. 
    
28. SQL> insert into t05311_d values (1) ;
    
29. 
    
30. 1 row created.
    
31. 
    
32. SQL> commit;
    
33. 
    
34. Commit complete.
    
35. 
    
36. SQL> drop  table t05311_d ;
    
37. 
    
38. Table dropped.
    
39. 
    
40. SQL> purge table t05311_d;
    
41. 
    
42. Table purged.
    
43. 
    
44. SQL> create table t05311_d ( a  number ) ;
    
45. 
    
46. Table created.
    
47. 
    
48. SQL> insert into t05311_d values (1) ;
    
49. 
    
50. 1 row created.
    
51. 
    
52. SQL> commit;
    
53. 
    
54. Commit complete.
    
55. 
    
56. SQL> drop  table t05311_d ;
    
57. 
    
58. Table dropped.
    
59. 
    
60. SQL>
    

复制代码

1. select * from dba_recyclebin;
   
2. 
   
3. purge tablespace users user hr;
   
4. 
   
5. purge dba_recyclebin;

复制代码

1. select  flashback_on from v$database;
   
2. 
   
3. select  * from v$flashback_database_log;
   
4. 
   
5. alter database flashback on;
   
6. 
   
7. select  * from v$flashback_database_logfile;

复制代码