课程第9/10次（2016-11-20星期日上下午）

botang

上完1Z0-052第9/10章（Oracle数据库的Undo和读一致性问题）
上完1Z0-052介绍课程最前面章节
（052共19章，053共21章，063多租户共9章，49-6）

查询undo表空间信息：

1. select  * from v$session where terminal='pts/3';
   
2. 
   
3. select  * from v$transaction ;
   
4. 
   
5. select  * from dba_rollback_segs  ;
   
6. 
   
7. select  * from dba_tablespaces ;
   
8. 
   
9. select  * from dba_data_files df where df.tablespace_name='UNDOTBS1';
   
10. 
    
11. alter database datafile '+DATA/orcl/datafile/undotbs1.258.816169553'
    
12. resize 200M;
    
13. 
    
14. alter database datafile '+DATA/orcl/datafile/undotbs1.258.816169553'
    
15. autoextend off;
    
16. 
    
17. alter tablespace  undotbs1 retention guarantee;
    
18. 
    
19. 
    
20. select * from v$undostat;
    
21. 
    
22. 
    
23. select  * from dict where table_name like '%UNDO%' or table_name like '%ROLL%';
    
24. 
    
25. select  * from DBA_HIST_UNDOSTAT;
    
26. 
    
27. select  * from V$ROLLSTAT;
    

复制代码

做ora-01555实验：

1. select  * from dba_tablespaces;
   
2. 
   
3. select  * from dba_temp_files;
   
4. 
   
5. alter tablespace temp add tempfile '/u01/app/oracle/temp02.dbf'
   
6.   size 10G autoextend on maxsize 20G;
   
7.   
   
8.   create undo tablespace undotbs2 datafile size 256K autoextend off;
   
9.   
   
10.   drop tablespace undotbs1;
    
11.   
    
12.   ---------------------
    
13.   
    
14.   select  * from dba_rollback_segs;
    
15.   
    
16.   create  undo tablespace undotbs1 ;
    
17.   
    
18.   select  * from dba_data_files df
    
19.     where df.tablespace_name='UNDOTBS1';
    
20.    
    
21.     alter system set undo_tablespace=undotbs1;
    
22.    
    
23.     drop tablespace undotbs2;
    
24. 
    

复制代码

闪回做ora-01555实验：

1. [oracle@station90 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 20 15:31:56 2016
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn hr/oracle_4U
   
8. Connected.
   
9. SQL> select  sysdate from dual;
   
10. 
    
11. SYSDATE
    
12. ------------------
    
13. 20-NOV-16
    
14. 
    
15. SQL> select  to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;
    
16. 
    
17. TO_CHAR(SYSDATE,'YY
    
18. -------------------
    
19. 2016-11-20:15:34:33
    
20. 
    
21. SQL> select  salary from employees where employee_id=100;
    
22. 
    
23.     SALARY
    
24. ----------
    
25.      28003
    
26. 
    
27. SQL> update employees  set salary=30000 where employee_id=100;
    
28. 
    
29. 1 row updated.
    
30. 
    
31. SQL> commit;
    
32. 
    
33. Commit complete.
    
34. 
    
35. SQL> select  salary from employees where employee_id=100;
    
36. 
    
37.     SALARY
    
38. ----------
    
39.      30000
    
40. 
    
41. SQL> select  salary from employees   
    
42.   2   as of timestamp
    
43.   3   to_timstamp('2016-11-20:15:34:33','YYYY-MM-DD:HH24:MI:SS')
    
44.   4   where employee_id=100;
    
45. to_timstamp('2016-11-20:15:34:33','YYYY-MM-DD:HH24:MI:SS')
    
46. *
    
47. ERROR at line 3:
    
48. ORA-00904: "TO_TIMSTAMP": invalid identifier
    
49. 
    
50. 
    
51. SQL> select  salary from employees
    
52.   2   as of timestamp
    
53.   3    to_timestamp('2016-11-20:15:34:33','YYYY-MM-DD:HH24:MI:SS')
    
54.   4    where employee_id=100;
    
55. 
    
56.     SALARY
    
57. ----------
    
58.      28003
    
59. 
    
60. SQL> /
    
61. 
    
62.     SALARY
    
63. ----------
    
64.      28003
    
65. 
    
66. SQL> /
    
67. 
    
68.     SALARY
    
69. ----------
    
70.      28003
    
71. 
    
72. SQL> /
    
73. select        salary from employees
    
74.                     *
    
75. ERROR at line 1:
    
76. ORA-01555: snapshot too old: rollback segment number  with name "" too small
    
77. 
    
78. 
    
79. SQL> conn hr/oracle_4U
    
80. Connected.
    
81. SQL> !ps
    
82.   PID TTY          TIME CMD
    
83. 1764 pts/3    00:00:00 sqlplus
    
84. 14076 pts/3    00:00:00 ps
    
85. 
    
86. SQL> conn hr/oracle_4U@orcl
    
87. Connected.
    
88. SQL> !ps
    
89.   PID TTY          TIME CMD
    
90. 1764 pts/3    00:00:00 sqlplus
    
91. 16477 pts/3    00:00:00 ps
    
92. 
    
93. SQL> conn hr/oracle_4U@orcl
    
94. Connected.
    
95. SQL> !ps
    
96.   PID TTY          TIME CMD
    
97. 1764 pts/3    00:00:00 sqlplus
    
98. 19866 pts/3    00:00:00 ps
    
99. 
    
100. SQL>
     
101. SQL>
     

复制代码

DBA_HIST_UNDOSTAT相关：

日志缓冲区相关：

1. [oracle@station90 admin]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 20 17:40:16 2016
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn / as sysdba
   
8. Connected.
   
9. SQL> show parameter log_buffer
   
10. 
    
11. NAME                                     TYPE         VALUE
    
12. ------------------------------------ ----------- ------------------------------
    
13. log_buffer                             integer         7036928
    
14. SQL> alter system set log_buffer=8M ;
    
15. alter system set log_buffer=8M
    
16.                  *
    
17. ERROR at line 1:
    
18. ORA-02095: specified initialization parameter cannot be modified
    

复制代码

1. select * from v$session
   
2. where  terminal='pts/3';
   
3. 
   
4. select  * from v$process
   
5.   where addr='00000001EC742EA0';
   
6.   
   
7.   select  * from v$sga_dynamic_components;

复制代码