设为首页收藏本站

Botang唐波's Oracle Station

查看: 339|回复: 0

课程第41次(2018-05-09星期三)

[复制链接]

719

主题

1081

帖子

7844

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7844
发表于 2018-5-9 20:28:40 | 显示全部楼层 |阅读模式
  1. select   * from v$diag_info;

  2. select  * from v$hm_check  order by name;

  3. begin
  4.    dbms_hm.run_check(check_name => 'Dictionary Integrity Check',run_name => 'MyCheck');
  5. end;

  6. select  * from v$hm_run;

  7. select  
  8.     dbms_hm.get_run_report(run_name => 'MyCheck') from dual;



复制代码

Screenshot.png


  1. select  * from v$hm_check  order by name;

  2. begin
  3.    dbms_hm.run_check(check_name => 'Data Block Integrity Check',
  4.        run_name => 'LogicalBlock',input_params => 'BLC_DF_NUM=9;BLC_BL_NUM=136'
  5.        );
  6. end;

  7. select  * from v$hm_run;

  8. select  dbms_hm.get_run_report('LogicalBlock') from dual;
复制代码

具体案例:处理逻辑坏块(SR):
  1. BEGIN
  2. DBMS_REPAIR.ADMIN_TABLES (
  3.    table_name => 'REPAIR_TABLE',
  4.    table_type => DBMS_REPAIR.REPAIR_TABLE,
  5.    action => DBMS_REPAIR.CREATE_ACTION,
  6.    tablespace => 'USERS');
  7. END;
复制代码
  1. BEGIN
  2. DBMS_REPAIR.ADMIN_TABLES (
  3. table_name => 'REPAIR_TABLE',
  4. table_type => DBMS_REPAIR.REPAIR_TABLE,
  5. action => DBMS_REPAIR.CREATE_ACTION,
  6. tablespace => 'USERS');
  7. END;

  8. select * from REPAIR_TABLE;

  9. BEGIN
  10. DBMS_REPAIR.ADMIN_TABLES (
  11.    table_name => 'ORPHAN_KEY_TABLE',
  12.    table_type => DBMS_REPAIR.ORPHAN_TABLE,
  13.    action => DBMS_REPAIR.CREATE_ACTION,
  14.    tablespace => 'USERS');
  15. END;

  16. select  * from ORPHAN_KEY_TABLE;

  17. 64  A-Z a-z 0-9 + /
  18. ---------------------
  19. DECLARE
  20. num_corrupt  INT;
  21. BEGIN
  22. num_corrupt := 0;
  23. DBMS_REPAIR.CHECK_OBJECT (
  24.     schema_name => 'HR',
  25.            object_name => 'TLOGICAL',
  26.     repair_table_name => 'REPAIR_TABLE',
  27.     corrupt_count => num_corrupt);
  28.     dbms_output.put_line( 'Find  '|| num_corrupt||' Bolcks.'  );
  29. END;

  30. --------------------------

  31. select * from REPAIR_TABLE;

  32. --------------------------------
  33. DECLARE
  34.   num_orphans INT;
  35. BEGIN
  36. num_orphans := 0;
  37. DBMS_REPAIR.DUMP_ORPHAN_KEYS (
  38.   schema_name => 'HR',
  39.   object_name => 'ILOGICAL_A',
  40.   object_type => DBMS_REPAIR.INDEX_OBJECT,
  41.   repair_table_name => 'REPAIR_TABLE',
  42.   orphan_table_name => 'ORPHAN_KEY_TABLE',
  43.   key_count => num_orphans);
  44. DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
  45. END;

  46. DECLARE
  47.   num_orphans INT;
  48. BEGIN
  49. num_orphans := 0;
  50. DBMS_REPAIR.DUMP_ORPHAN_KEYS (
  51.   schema_name => 'HR',
  52.   object_name => 'ILOGICAL_B',
  53.   object_type => DBMS_REPAIR.INDEX_OBJECT,
  54.   repair_table_name => 'REPAIR_TABLE',
  55.   orphan_table_name => 'ORPHAN_KEY_TABLE',
  56.   key_count => num_orphans);
  57. DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
  58. END;

  59. -----------------------------------
  60. select  * from ORPHAN_KEY_TABLE
  61.   where index_name='ILOGICAL_B'  and keyrowid||'a'='AAATrJAIgAAAEACAAA'||'a';
  62. ------------------------------------
  63. begin
  64.    dbms_repair.skip_corrupt_blocks(schema_name => 'HR',object_name => 'TLOGICAL');
  65. end;  
  66.   
  67. drop index hr.ilogical_a;

  68. drop index hr.ilogical_b;

  69. create index hr.ilogical_a on     hr.tlogical   (a) ;
  70.   create index hr.ilogical_b on     hr.tlogical   (b) ;

  71. -------------------------------------------------

  72. select  * from orphan_key_table;
  73. --------------------------------
  74. select dump(key) from orphan_key_table;

  75. 97,115,97,114,111,110
  76. a,  s,    a,  r,    o,    n
  77. --------------------------------------------------------
  78. 195, 3, 61 ,74
  79. 195, 3,61, 73
  80. ---------------------------------------------------------





复制代码

100^(193-193-0)(2-1)=1
100^(193-193-0)(11-1)=10
100^(194-193-0)(2-1)=100
100^(194-193-0)(11-1)=1000
100^(194-193-0)(2-1)+
   100^(194-193-1)(11-1)=110
100^(194-193-0)(12-1)=1100
100^(62-62-0)(101-100)=1
100^(62-62-0)(101-91)=10
100^(62-61-0)(101-100)=100
123456.789
100^(195-193-0)(13-1)+
100^(195-193-1)(35-1)+
100^(195-193-2)(57-1)+
100^(195-193-3)(79-1)+
100^(195-193-4)(91-1)=120000+3400+56+0.78+0.009=
123456.789
-123456.789
100^(62-60-0)(101-89)+
100^(62-60-1)(101-67)+
100^(62-60-2)(101-45)+
100^(62-60-3)(101-23)+
100^(62-60-4)(101-11)=120000+3400+56+0.78+0.009=
-123456.789
-123456.78901
100^(62-60-0)(101-89)+
100^(62-60-1)(101-67)+
100^(62-60-2)(101-45)+
100^(62-60-3)(101-23)+
100^(62-60-4)(101-11)+
100^(62-60-5)(101-91)
=120000+3400+56+0.78+0.009+ 0.00001=
-123456.78901

回复

使用道具 举报

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

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-9-19 08:08 , Processed in 0.129985 second(s), 27 queries .

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