Bo's Oracle Station

查看: 1940|回复: 0

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

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2016-11-21 10:50:23 | 显示全部楼层 |阅读模式
本帖最后由 botang 于 2016-11-22 23:18 编辑

上完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. select  * from v$transaction ;

  3. select  * from dba_rollback_segs  ;

  4. select  * from dba_tablespaces ;

  5. select  * from dba_data_files df where df.tablespace_name='UNDOTBS1';

  6. alter database datafile '+DATA/orcl/datafile/undotbs1.258.816169553'
  7. resize 200M;

  8. alter database datafile '+DATA/orcl/datafile/undotbs1.258.816169553'
  9. autoextend off;

  10. alter tablespace  undotbs1 retention guarantee;


  11. select * from v$undostat;


  12. select  * from dict where table_name like '%UNDO%' or table_name like '%ROLL%';

  13. select  * from DBA_HIST_UNDOSTAT;

  14. select  * from V$ROLLSTAT;
复制代码
做ora-01555实验:
  1. select  * from dba_tablespaces;

  2. select  * from dba_temp_files;

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

复制代码
闪回做ora-01555实验:
  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 20 15:31:56 2016

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

  4. SQL> conn hr/oracle_4U
  5. Connected.
  6. SQL> select  sysdate from dual;

  7. SYSDATE
  8. ------------------
  9. 20-NOV-16

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

  11. TO_CHAR(SYSDATE,'YY
  12. -------------------
  13. 2016-11-20:15:34:33

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

  15.     SALARY
  16. ----------
  17.      28003

  18. SQL> update employees  set salary=30000 where employee_id=100;

  19. 1 row updated.

  20. SQL> commit;

  21. Commit complete.

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

  23.     SALARY
  24. ----------
  25.      30000

  26. SQL> select  salary from employees   
  27.   2   as of timestamp
  28.   3   to_timstamp('2016-11-20:15:34:33','YYYY-MM-DD:HH24:MI:SS')
  29.   4   where employee_id=100;
  30. to_timstamp('2016-11-20:15:34:33','YYYY-MM-DD:HH24:MI:SS')
  31. *
  32. ERROR at line 3:
  33. ORA-00904: "TO_TIMSTAMP": invalid identifier


  34. SQL> select  salary from employees
  35.   2   as of timestamp
  36.   3    to_timestamp('2016-11-20:15:34:33','YYYY-MM-DD:HH24:MI:SS')
  37.   4    where employee_id=100;

  38.     SALARY
  39. ----------
  40.      28003

  41. SQL> /

  42.     SALARY
  43. ----------
  44.      28003

  45. SQL> /

  46.     SALARY
  47. ----------
  48.      28003

  49. SQL> /
  50. select        salary from employees
  51.                     *
  52. ERROR at line 1:
  53. ORA-01555: snapshot too old: rollback segment number  with name "" too small


  54. SQL> conn hr/oracle_4U
  55. Connected.
  56. SQL> !ps
  57.   PID TTY          TIME CMD
  58. 1764 pts/3    00:00:00 sqlplus
  59. 14076 pts/3    00:00:00 ps

  60. SQL> conn hr/oracle_4U@orcl
  61. Connected.
  62. SQL> !ps
  63.   PID TTY          TIME CMD
  64. 1764 pts/3    00:00:00 sqlplus
  65. 16477 pts/3    00:00:00 ps

  66. SQL> conn hr/oracle_4U@orcl
  67. Connected.
  68. SQL> !ps
  69.   PID TTY          TIME CMD
  70. 1764 pts/3    00:00:00 sqlplus
  71. 19866 pts/3    00:00:00 ps

  72. SQL>
  73. SQL>
复制代码
DBA_HIST_UNDOSTAT相关:
Screenshot.png

Screenshot-1.png


日志缓冲区相关:
  1. [oracle@station90 admin]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 20 17:40:16 2016

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

  4. SQL> conn / as sysdba
  5. Connected.
  6. SQL> show parameter log_buffer

  7. NAME                                     TYPE         VALUE
  8. ------------------------------------ ----------- ------------------------------
  9. log_buffer                             integer         7036928
  10. SQL> alter system set log_buffer=8M ;
  11. alter system set log_buffer=8M
  12.                  *
  13. ERROR at line 1:
  14. ORA-02095: specified initialization parameter cannot be modified
复制代码
  1. select * from v$session
  2. where  terminal='pts/3';

  3. select  * from v$process
  4.   where addr='00000001EC742EA0';
  5.   
  6.   select  * from v$sga_dynamic_components;
复制代码






回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-28 19:46 , Processed in 0.045645 second(s), 27 queries .

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