-- 數(shù)據(jù)表空間create tablespace dbs_d_jingyu datafile '/u02/oradata/jingyu/dbs_d_jingyu01.dbf' size 30M autoextend off;-- 臨時(shí)表空間create temporary tablespace temp_jingyu tempfile '/u02/oradata/jingyu/temp_jingyu01.tmp' size 30M autoextend off;-- 索引表空間(可選)create tablespace dbs_i_jingyu datafile '/u02/oradata/jingyu/dbs_i_jingyu01.dbf' size 30M autoextend off;-- 假設(shè)創(chuàng)建用戶 jingyu 密碼 jingyu，默認(rèn)臨時(shí)表空間 temp_jingyu， 默認(rèn)數(shù)據(jù)表空間 dbs_d_jingyu。CREATE USER jingyu IDENTIFIED BY jingyu TEMPORARY TABLESPACE temp_jingyu DEFAULT TABLESPACE dbs_d_jingyu QUOTA UNLIMITED ON dbs_d_jingyu;-- 賦予普通業(yè)務(wù)用戶權(quán)限grant resource, connect to jingyu;-- 賦予DBA用戶權(quán)限grant dba to jingyu;-- 業(yè)務(wù)用戶登錄conn jingyu/jingyu -- 1.1 創(chuàng)建分區(qū)表create table t_part(id number, name varchar2(20), start_time date, content varchar2(200))partition by range(start_time)( partition P20150101 values less than (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace dbs_d_jingyu, partition P20150102 values less than (TO_DATE(' 2015-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace dbs_d_jingyu, partition P20150103 values less than (TO_DATE(' 2015-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace dbs_d_jingyu);-- 1.2 插入測(cè)試數(shù)據(jù)--分區(qū)P20150102插入10000行數(shù)據(jù)begin for i in 1..10000 loop insert into t_part values (i,'alfred'||i, to_date('2015-01-01','yyyy-mm-dd'), 'AAAAAAAAAA'); end loop; commit;end;/--分區(qū)P20150103插入20000行數(shù)據(jù)begin for i in 10001..30000 loop insert into t_part values (i,'alfred'||i, to_date('2015-01-02','yyyy-mm-dd'), 'AAAAAAAAAA'); end loop; commit;end;/-- 1.3查詢表數(shù)據(jù)量和大小select count(1) from t_part; --result: 30000select count(1) from t_part partition(P20150102); --result: 10000select count(1) from t_part partition(P20150103); --result: 20000--普通表/分區(qū)表的每個(gè)分區(qū)大約__G大小set linesize 160col segment_name for a30select (t.bytes/1024/1024) "MB", t.owner, t.segment_name, t.partition_name, t.tablespace_name from dba_segments t where segment_name = 'T_PART'; MB OWNER  SEGMENT_NAME  PARTITION_NAME  TABLESPACE_NAME---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ 8 JINGYU  T_PART  P20150102  DBS_D_JINGYU 8 JINGYU  T_PART  P20150103  DBS_D_JINGYU

--查詢分區(qū)P20150103的HEADER_BLOCKselect header_file,header_block from dba_segments where segment_name='T_PART' and partition_name='P20150103' and owner='JINGYU';SQL> select header_file,header_block from dba_segments where segment_name='T_PART' and partition_name='P20150103' and owner='JINGYU';HEADER_FILE HEADER_BLOCK----------- ------------  5  1169--查詢某一行記錄所在的塊select rowid, dbms_rowid.rowid_relative_fno(rowid)rel_fno, dbms_rowid.rowid_block_number(rowid)blockno, dbms_rowid.rowid_row_number(rowid) rowno from t_part where id = 20000; SQL> select 2 rowid, 3 dbms_rowid.rowid_relative_fno(rowid)rel_fno, 4 dbms_rowid.rowid_block_number(rowid)blockno, 5 dbms_rowid.rowid_row_number(rowid) rowno 6 from t_part where id = 20000;ROWID   REL_FNO BLOCKNO ROWNO------------------ ---------- ---------- ----------AAAVveAAFAAAATBABX  5 1217  87

[oracle/9040.html">oracle/198155.html">oracle@JY-DB01 ~]$ bbed parfile=/tmp/bbed.parPassword:BBED: Release 2.0.0.0.0 - Limited Production on Tue Jan 19 11:37:59 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.************* !!! For Oracle Internal Use only !!! ***************BBED> set dba 5,1217 DBA  0x014004c1 (20972737 5,1217)BBED> map File: /u02/oradata/jingyu/dbs_d_jingyu01.dbf (5) Block: 1217     Dba:0x014004c1------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes   @0  struct ktbbh, 72 bytes   @20  struct kdbh, 14 bytes   @100  struct kdbt[1], 4 bytes   @114  sb2 kdbr[177]    @118  ub1 freespace[815]    @472  ub1 rowdata[6901]    @1287  ub4 tailchk    @8188 BBED> d /v offset 0 count 128 File: /u02/oradata/jingyu/dbs_d_jingyu01.dbf (5) Block: 1217 Offsets: 0 to 127 Dba:0x014004c1------------------------------------------------------- 06a20000 c1044001 52733100 00000106 l ......@.Rs1..... a18b0000 01000c00 de5b0100 4d733100 l .........[..Ms1. 0000e81f 021f3200 81044001 02001b00 l ......2...@..... 5d0b0000 fc0fc000 df030600 b1200000 l ]............ .. 52733100 00000000 00000000 00000000 l Rs1............. 00000000 00000000 00000000 00000000 l ................ 00000000 0001b100 ffff7401 a3042f03 l ..........t.../. 2f030000 b100711f 4a1f231f fc1ed51e l /.....q.J.#..... <16 bytes per line>BBED> modify /x 19901010 offset 0 File: /u02/oradata/jingyu/dbs_d_jingyu01.dbf (5) Block: 1217  Offsets: 0 to 127  Dba:0x014004c1------------------------------------------------------------------------ 19901010 c1044001 52733100 00000106 a18b0000 01000c00 de5b0100 4d733100  0000e81f 021f3200 81044001 02001b00 5d0b0000 fc0fc000 df030600 b1200000  52733100 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 0001b100 ffff7401 a3042f03 2f030000 b100711f 4a1f231f fc1ed51e <32 bytes per line>BBED> sum applyCheck value for File 5, Block 1217:current = 0xa9ae, required = 0xa9aeBBED>

select * from v$database_block_corruption;SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO---------- ---------- ---------- ------------------ ---------  5 1217  1   0 CORRUPT--此時(shí)查詢分區(qū)表T_PARTalter system flush buffer_cache;select count(1) from t_part;--查詢報(bào)錯(cuò)ORA-01578select count(1) from t_part partition(P20150102);--查詢正常，即分區(qū)P20150102未受影響select count(1) from t_part partition(P20150103);--查詢報(bào)錯(cuò)ORA-01578--嘗試邏輯導(dǎo)出表數(shù)據(jù)失敗[oracle@JY-DB01 ~]$ exp jingyu/jingyu tables=t_part file=t_part.dmp log=exp_t_part.logExport: Release 11.2.0.4.0 - Production on Tue Jan 19 11:52:21 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsExport done in ZHS16GBK character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path .... . exporting table    T_PART. . exporting partition   P20150101  0 rows exported. . exporting partition   P20150102 10000 rows exported. . exporting partition   P20150103EXP-00056: ORACLE error 1578 encounteredORA-01578: ORACLE data block corrupted (file # 5, block # 1217)ORA-01110: data file 5: '/u02/oradata/jingyu/dbs_d_jingyu01.dbf'Export terminated successfully with warnings.[oracle@JY-DB01 ~]$

--啟用10231內(nèi)部事件alter system set events='10231 trace name context forever,level 10';--關(guān)閉10231內(nèi)部事件alter system set events='10231 trace name context off';

[oracle@JY-DB01 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 19 14:01:43 2016Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsSQL> alter system set events='10231 trace name context forever,level 10';System altered.SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing options[oracle@JY-DB01 ~]$ exp jingyu/jingyu tables=t_part file=t_part.dmp log=exp_t_part.logExport: Release 11.2.0.4.0 - Production on Tue Jan 19 14:01:57 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsExport done in ZHS16GBK character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path .... . exporting table    T_PART. . exporting partition   P20150101  0 rows exported. . exporting partition   P20150102 10000 rows exported. . exporting partition   P20150103 19823 rows exportedExport terminated successfully without warnings.--成功導(dǎo)出后記得要關(guān)閉10231內(nèi)部事件alter system set events='10231 trace name context off';20000 - 19823 = 177行，也就是說(shuō)該數(shù)據(jù)塊損壞直接導(dǎo)致了177行數(shù)據(jù)丟失。不過(guò)還好，保住了大部分?jǐn)?shù)據(jù)。

SQL> select count(1) from t_part;select count(1) from t_part*ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 5, block # 1217)ORA-01110: data file 5: '/u02/oradata/jingyu/dbs_d_jingyu01.dbf'SQL> alter system set events='10231 trace name context forever,level 10';System altered.SQL> select count(1) from t_part; COUNT(1)---------- 29823SQL> create table temp_t_part_20150103 as select * from t_part partition(P20150103);Table created.SQL> alter system set events='10231 trace name context off';System altered.SQL> select count(1) from t_part partition(P20150103);select count(1) from t_part partition(P20150103)*ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 5, block # 1217)ORA-01110: data file 5: '/u02/oradata/jingyu/dbs_d_jingyu01.dbf'SQL> select count(1) from temp_t_part_20150103; COUNT(1)---------- 19823