今天一朋友问到一个比较有意识的问题：
如果一个表空间，其中一个数据文件丢失，在无备份和非归档的情况下，如何最大程度的
把表空间里面的数据给抢救出来？
我当时直接的回答是，使用exp进行逻辑导出，当时对方直接说exp是不行的，真是这样吗？
这篇文章就的目的就是为了解答这个问题，首先准备下测试环境：
SQL> select file_id,file_name,bytes/1024/1024,tablespace_name,AUTOEXTENSIBLE
2 from dba_data_files order by 1;
FILE_ID FILE_NAME BYTES/1024/1024 TABLESPACE_NAME AUT
------- ------------------------------------------ --------------- ----------------- ---
1 /home/ora10g/oradata/roger/system01.dbf 450 SYSTEM YES
2 /home/ora10g/oradata/roger/undotbs01.dbf 925 UNDOTBS1 YES
3 /home/ora10g/oradata/roger/sysaux01.dbf 260 SYSAUX YES
4 /home/ora10g/oradata/roger/users01.dbf 5 USERS YES
5 /home/ora10g/oradata/roger/roger01.dbf 10 ROGER NO
6 /home/ora10g/oradata/roger/roger02.dbf 10 ROGER NO
7 /home/ora10g/oradata/roger/roger03.dbf 10 ROGER NO
7 rows selected.
SQL> create user roger identified by roger default tablespace roger;
User created.
SQL> grant connect,resource,dba to roger;
Grant succeeded.
SQL>
SQL> conn roger/roger
Connected.
SQL> create table killdb1 as select * from sys.dba_objects;
Table created.
SQL> create table killdb2 as select * from killdb1;
Table created.
SQL> begin
2 for i in 1..100 loop
3 insert /*+ append */into killdb2 select * from killdb2;
4 commit;
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table ROGER.KILLDB2 by 128 in tablespace ROGER
ORA-06512: at line 3
SQL> analyze table killdb1 compute statistics;
Table analyzed.
SQL> analyze table killdb2 compute statistics;
Table analyzed.
SQL> conn /as sysdba
Connected.
SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) file#
2 from roger.killdb1
3 union all
4 select distinct dbms_rowid.rowid_relative_fno(rowid) file#
5 from roger.killdb2;
FILE#
----------
6
5
7
6
5
7
6 rows selected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-00000: normal, successful completion
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 79693032 bytes
Database Buffers 83886080 bytes
Redo Buffers 2920448 bytes
Database mounted.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL>
SQL> !rm /home/ora10g/oradata/roger/roger03.dbf
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00000: normal, successful completion
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 79693032 bytes
Database Buffers 83886080 bytes
Redo Buffers 2920448 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/home/ora10g/oradata/roger/roger03.dbf'
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database datafile 7 offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> select count(*) from roger.killdb1;
select count(*) from roger.killdb1
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/home/ora10g/oradata/roger/roger03.dbf'
情景我们已经模拟出来来，现在问题是我们要如何去最大限度的抢救killdb1，killdb2里面的数据（虽然会
丢失部分数据）。
第一种方式：
[ora10g@killdb ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[ora10g@killdb ~]$ exp roger/roger file=killdb1.dmp tables=killdb1 grants=n indexes=n
Export: Release 10.2.0.5.0 - Production on Wed Nov 2 20:52:53 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
About to export specified tables via Conventional Path ...
. . exporting table KILLDB1
EXP-00056: ORACLE error 376 encountered
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/home/ora10g/oradata/roger/roger03.dbf'
Export terminated successfully with warnings.
[ora10g@killdb ~]$ imp system/oracle file=killdb1.dmp fromuser=roger touser=system indexes=n grants=n IGNORE=y
Import: Release 10.2.0.5.0 - Production on Wed Nov 2 20:55:43 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ROGER, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing ROGER's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 1658:
"CREATE TABLE "KILLDB1" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "
""SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER,"
" "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTA"
"MP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED"
"" VARCHAR2(1), "SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 M"
"AXTRANS 255 STORAGE(INITIAL 6291456 NEXT 1048576 FREELISTS 1 FREELIST GROUP"
"S 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace ROGER
Import terminated successfully with warnings.
[ora10g@killdb ~]$
[ora10g@killdb ~]$
roger表空间不足，我需要扩容一下
SQL> alter database datafile 5 resize 20m;
Database altered.
[ora10g@killdb ~]$ imp system/oracle file=killdb1.dmp fromuser=roger touser=system indexes=n grants=n IGNORE=y
Import: Release 10.2.0.5.0 - Production on Wed Nov 2 20:56:32 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ROGER, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing ROGER's objects into SYSTEM
. . importing table "KILLDB1" 9500 rows imported
Import terminated successfully without warnings.
[ora10g@killdb ~]$
SQL> conn /as sysdba
Connected.
SQL>
SQL> select count(*) from system.killdb1;
COUNT(*)
----------
9500
SQL>
SQL> select * from SYSSEGOBJ where obj# in(select object_id from dba_objects
2 where object_name='KILLDB1' and owner='ROGER');
OBJ# FILE# BLOCK# TYPE PCTFREE$ PCTUSED$
---------- ---------- ---------- ------- ---------- ----------
52059 5 11 TABLE 10 40
SQL> select file#,BLOCK#,BLOCKS,EXTENTS from seg$ where file#=5 and block#=11;
FILE# BLOCK# BLOCKS EXTENTS
---------- ---------- ---------- ----------
5 11 768 21
SQL>
SQL> col owner for a10
SQL> select a.owner,
2 a.segment_name,
3 a.initial_extent,
4 b.file_id,
5 a.extents,
6 b.extent_id,
7 b.blocks,
8 a.HEADER_FILE,
9 a.HEADER_BLOCK
10 from dba_segments a, dba_extents b
11 where a.owner = b.owner
12 and a.segment_name = b.segment_name
13 and a.owner = 'ROGER'
14 and b.segment_name = 'KILLDB1'
15 order by 6;
OWNER SEGMENT_NAME INITIAL_EXTENT FILE_ID EXTENTS EXTENT_ID BLOCKS HEADER_FILE HEADER_BLOCK
---------- --------------- -------------- ---------- ---------- ---------- ---------- ----------- ------------
ROGER KILLDB1 65536 5 21 0 8 5 11
ROGER KILLDB1 65536 5 21 1 8 5 11
ROGER KILLDB1 65536 5 21 2 8 5 11
ROGER KILLDB1 65536 5 21 3 8 5 11
ROGER KILLDB1 65536 5 21 4 8 5 11
ROGER KILLDB1 65536 5 21 5 8 5 11
ROGER KILLDB1 65536 5 21 6 8 5 11
ROGER KILLDB1 65536 5 21 7 8 5 11
ROGER KILLDB1 65536 5 21 8 8 5 11
ROGER KILLDB1 65536 5 21 9 8 5 11
ROGER KILLDB1 65536 5 21 10 8 5 11
OWNER SEGMENT_NAME INITIAL_EXTENT FILE_ID EXTENTS EXTENT_ID BLOCKS HEADER_FILE HEADER_BLOCK
---------- --------------- -------------- ---------- ---------- ---------- ---------- ----------- ------------
ROGER KILLDB1 65536 5 21 11 8 5 11
ROGER KILLDB1 65536 5 21 12 8 5 11
ROGER KILLDB1 65536 5 21 13 8 5 11
ROGER KILLDB1 65536 5 21 14 8 5 11
ROGER KILLDB1 65536 5 21 15 8 5 11
ROGER KILLDB1 65536 7 21 16 128 5 11
ROGER KILLDB1 65536 6 21 17 128 5 11
ROGER KILLDB1 65536 5 21 18 128 5 11
ROGER KILLDB1 65536 7 21 19 128 5 11
ROGER KILLDB1 65536 6 21 20 128 5 11
21 rows selected.
SQL> select owner,
2 TABLE_NAME,
3 NUM_ROWS,
4 BLOCKS,
5 EMPTY_BLOCKS,
6 AVG_SPACE,
7 AVG_ROW_LEN
8 from DBA_TAB_STATISTICS
9 where table_name = 'KILLDB1'
10 and owner = 'ROGER';
OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN
---------- ------------------------------ ---------- ---------- ------------ ---------- -----------
ROGER KILLDB1 50094 708 60 861 97
SQL> select 16*8*(50094/708) from dual;
16*8*(50094/708)
----------------
9056.54237
SQL>
通过上面的查询，我们可以看出，exp在抽取数据的时候，是根据extent来的，换句话说
在exp导出表killdb1时，只是抽取了前面0~15个extent，虽然extent id 17，18,20是完好的。
也就是说使用exp进行导出的话，实际上是丢失了这3个extent部分的数据的。
第2种方法： --基于rowid进行数据抢救
表killdb1 的block分布情况如下：
SQL> col owner for a10
SQL> select a.owner,
2 a.segment_name,
3 a.initial_extent,
4 b.file_id,
5 a.extents,
6 b.extent_id,
7 b.blocks,
8 a.HEADER_FILE,
9 a.HEADER_BLOCK
10 from dba_segments a, dba_extents b
11 where a.owner = b.owner
12 and a.segment_name = b.segment_name
13 and a.owner = 'ROGER'
14 and b.segment_name = 'KILLDB1'
15 order by 6;
OWNER SEGMENT_NAME INITIAL_EXTENT FILE_ID EXTENTS EXTENT_ID BLOCKS HEADER_FILE HEADER_BLOCK
---------- --------------- -------------- ---------- ---------- ---------- ---------- ----------- ------------
ROGER KILLDB1 65536 5 21 0 8 5 11
ROGER KILLDB1 65536 5 21 1 8 5 11
ROGER KILLDB1 65536 5 21 2 8 5 11
ROGER KILLDB1 65536 5 21 3 8 5 11
ROGER KILLDB1 65536 5 21 4 8 5 11
ROGER KILLDB1 65536 5 21 5 8 5 11
ROGER KILLDB1 65536 5 21 6 8 5 11
ROGER KILLDB1 65536 5 21 7 8 5 11
ROGER KILLDB1 65536 5 21 8 8 5 11
ROGER KILLDB1 65536 5 21 9 8 5 11
ROGER KILLDB1 65536 5 21 10 8 5 11
OWNER SEGMENT_NAME INITIAL_EXTENT FILE_ID EXTENTS EXTENT_ID BLOCKS HEADER_FILE HEADER_BLOCK
---------- --------------- -------------- ---------- ---------- ---------- ---------- ----------- ------------
ROGER KILLDB1 65536 5 21 11 8 5 11
ROGER KILLDB1 65536 5 21 12 8 5 11
ROGER KILLDB1 65536 5 21 13 8 5 11
ROGER KILLDB1 65536 5 21 14 8 5 11
ROGER KILLDB1 65536 5 21 15 8 5 11
ROGER KILLDB1 65536 7 21 16 128 5 11
ROGER KILLDB1 65536 6 21 17 128 5 11
ROGER KILLDB1 65536 5 21 18 128 5 11
ROGER KILLDB1 65536 7 21 19 128 5 11
ROGER KILLDB1 65536 6 21 20 128 5 11
21 rows selected.
select 139+128 from dual; --267
select 139+2*128 from dual; --395
select 139+3*128 from dual; --523
select 139+4*128 from dual; --651
select 139+5*128 from dual; --779
SQL> select object_id from dba_objects where object_name='KILLDB1' and
2 owner='ROGER';
OBJECT_ID
----------
52059
SQL>
SQL> select 139+128 from dual;
select 139+2*128 from dual;
139+128
----------
267
SQL> select 139+3*128 from dual;
139+2*128
----------
395
SQL>
139+3*128
----------
523
SQL> select 139+4*128 from dual;
139+4*128
----------
651
SQL> select 139+5*128 from dual;
139+5*128
----------
779
SQL> set lines 200
SQL> SELECT dbms_rowid.rowid_create(1,52059,5,139,0) FROM dual;
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAACLAAA
SQL> select dbms_rowid.rowid_create(1,52059,5,267,0) from dual;
select dbms_rowid.rowid_create(1,52059,5,395,0) from dual;
select dbms_rowid.rowid_create(1,52059,5,523,0) from dual;
select dbms_rowid.rowid_create(1,52059,5,651,0) from dual;
select dbms_rowid.rowid_create(1,52059,5,779,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAELAAA
SQL>
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAGLAAA
SQL>
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAILAAA
SQL>
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAKLAAA
SQL>
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAMLAAA
SQL>
SQL>
SQL> create table t1 as select * from roger.killdb1 where rowid < 'AAAMtbAAFAAAACLAAA';
Table created.
SQL> select count(*) from t1;
COUNT(*)
----------
9500 ---这个就是我们使用exp所能抽取的9500条数据
SQL> insert into t1
2 select *
3 from (select *
4 from roger.killdb1
5 where rowid < 'AAAMtbAAFAAAAELAAA'
6 union all
7 select *
8 from roger.killdb1
9 where rowid < 'AAAMtbAAFAAAAGLAAA'
10 and rowid >='AAAMtbAAFAAAAELAAA'
11 union all
12 select *
13 from roger.killdb1
14 where rowid < 'AAAMtbAAFAAAAKLAAA'
15 and rowid >= 'AAAMtbAAFAAAAGLAAA'
16 union all
17 select *
18 from roger.killdb1
19 where rowid < 'AAAMtbAAFAAAAMLAAA'
20 and rowid >= 'AAAMtbAAFAAAAKLAAA');
18347 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from t1;
COUNT(*)
----------
27847
SQL> ---我们可以看到，这样可以抢救出27847条数据，远大于9500条。
第三种方式： 使用ODU进行数据抽取
[ora10g@killdb data]$ ls -ltr
total 1036
-rw-r--r-- 1 ora10g oinstall 1036288 Nov 3 01:07 ROGER_KILLDB1.txt
-rw-r--r-- 1 ora10g oinstall 416 Nov 3 01:07 ROGER_KILLDB1.sql
-rw-r--r-- 1 ora10g oinstall 618 Nov 3 01:07 ROGER_KILLDB1.ctl
[ora10g@killdb data]$
SQL> @ /home/ora10g/odu/odu/data/ROGER_KILLDB1.sql
Table created.
SQL> !
[ora10g@killdb ~]$ cd /home/ora10g/odu/odu/data
[ora10g@killdb data]$ sqlldr system/oracle control=ROGER_KILLDB1.ctl
SQL*Loader: Release 10.2.0.5.0 - Production on Thu Nov 3 00:50:33 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached - logical record count 6492
Commit point reached - logical record count 9478
Commit point reached - logical record count 9479
[ora10g@killdb data]$ exit
exit
SQL> select count(*) from system.killdb1;
COUNT(*)
----------
9479
可以发现，对于低版本的OUD，似乎还存在一些问题，高版本的odu应该是ok的，我这里没测试。
目前我暂时所能想到的就这几种方式，如果谁还有更好的方法，请告诉我，谢谢，总的来说，
我个人认为第2种方式比较好，当然使用新版的ODU也比较省事，直接scan一下，然后unload，imp就完了。
如果一个表空间，其中一个数据文件丢失，在无备份和非归档的情况下，如何最大程度的
把表空间里面的数据给抢救出来？
我当时直接的回答是，使用exp进行逻辑导出，当时对方直接说exp是不行的，真是这样吗？
这篇文章就的目的就是为了解答这个问题，首先准备下测试环境：
SQL> select file_id,file_name,bytes/1024/1024,tablespace_name,AUTOEXTENSIBLE
2 from dba_data_files order by 1;
FILE_ID FILE_NAME BYTES/1024/1024 TABLESPACE_NAME AUT
------- ------------------------------------------ --------------- ----------------- ---
1 /home/ora10g/oradata/roger/system01.dbf 450 SYSTEM YES
2 /home/ora10g/oradata/roger/undotbs01.dbf 925 UNDOTBS1 YES
3 /home/ora10g/oradata/roger/sysaux01.dbf 260 SYSAUX YES
4 /home/ora10g/oradata/roger/users01.dbf 5 USERS YES
5 /home/ora10g/oradata/roger/roger01.dbf 10 ROGER NO
6 /home/ora10g/oradata/roger/roger02.dbf 10 ROGER NO
7 /home/ora10g/oradata/roger/roger03.dbf 10 ROGER NO
7 rows selected.
SQL> create user roger identified by roger default tablespace roger;
User created.
SQL> grant connect,resource,dba to roger;
Grant succeeded.
SQL>
SQL> conn roger/roger
Connected.
SQL> create table killdb1 as select * from sys.dba_objects;
Table created.
SQL> create table killdb2 as select * from killdb1;
Table created.
SQL> begin
2 for i in 1..100 loop
3 insert /*+ append */into killdb2 select * from killdb2;
4 commit;
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table ROGER.KILLDB2 by 128 in tablespace ROGER
ORA-06512: at line 3
SQL> analyze table killdb1 compute statistics;
Table analyzed.
SQL> analyze table killdb2 compute statistics;
Table analyzed.
SQL> conn /as sysdba
Connected.
SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) file#
2 from roger.killdb1
3 union all
4 select distinct dbms_rowid.rowid_relative_fno(rowid) file#
5 from roger.killdb2;
FILE#
----------
6
5
7
6
5
7
6 rows selected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-00000: normal, successful completion
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 79693032 bytes
Database Buffers 83886080 bytes
Redo Buffers 2920448 bytes
Database mounted.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL>
SQL> !rm /home/ora10g/oradata/roger/roger03.dbf
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00000: normal, successful completion
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 79693032 bytes
Database Buffers 83886080 bytes
Redo Buffers 2920448 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/home/ora10g/oradata/roger/roger03.dbf'
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database datafile 7 offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> select count(*) from roger.killdb1;
select count(*) from roger.killdb1
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/home/ora10g/oradata/roger/roger03.dbf'
情景我们已经模拟出来来，现在问题是我们要如何去最大限度的抢救killdb1，killdb2里面的数据（虽然会
丢失部分数据）。
第一种方式：
[ora10g@killdb ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[ora10g@killdb ~]$ exp roger/roger file=killdb1.dmp tables=killdb1 grants=n indexes=n
Export: Release 10.2.0.5.0 - Production on Wed Nov 2 20:52:53 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
About to export specified tables via Conventional Path ...
. . exporting table KILLDB1
EXP-00056: ORACLE error 376 encountered
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/home/ora10g/oradata/roger/roger03.dbf'
Export terminated successfully with warnings.
[ora10g@killdb ~]$ imp system/oracle file=killdb1.dmp fromuser=roger touser=system indexes=n grants=n IGNORE=y
Import: Release 10.2.0.5.0 - Production on Wed Nov 2 20:55:43 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ROGER, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing ROGER's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 1658:
"CREATE TABLE "KILLDB1" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "
""SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER,"
" "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTA"
"MP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED"
"" VARCHAR2(1), "SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 M"
"AXTRANS 255 STORAGE(INITIAL 6291456 NEXT 1048576 FREELISTS 1 FREELIST GROUP"
"S 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace ROGER
Import terminated successfully with warnings.
[ora10g@killdb ~]$
[ora10g@killdb ~]$
roger表空间不足，我需要扩容一下
SQL> alter database datafile 5 resize 20m;
Database altered.
[ora10g@killdb ~]$ imp system/oracle file=killdb1.dmp fromuser=roger touser=system indexes=n grants=n IGNORE=y
Import: Release 10.2.0.5.0 - Production on Wed Nov 2 20:56:32 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ROGER, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing ROGER's objects into SYSTEM
. . importing table "KILLDB1" 9500 rows imported
Import terminated successfully without warnings.
[ora10g@killdb ~]$
SQL> conn /as sysdba
Connected.
SQL>
SQL> select count(*) from system.killdb1;
COUNT(*)
----------
9500
SQL>
SQL> select * from SYSSEGOBJ where obj# in(select object_id from dba_objects
2 where object_name='KILLDB1' and owner='ROGER');
OBJ# FILE# BLOCK# TYPE PCTFREE$ PCTUSED$
---------- ---------- ---------- ------- ---------- ----------
52059 5 11 TABLE 10 40
SQL> select file#,BLOCK#,BLOCKS,EXTENTS from seg$ where file#=5 and block#=11;
FILE# BLOCK# BLOCKS EXTENTS
---------- ---------- ---------- ----------
5 11 768 21
SQL>
SQL> col owner for a10
SQL> select a.owner,
2 a.segment_name,
3 a.initial_extent,
4 b.file_id,
5 a.extents,
6 b.extent_id,
7 b.blocks,
8 a.HEADER_FILE,
9 a.HEADER_BLOCK
10 from dba_segments a, dba_extents b
11 where a.owner = b.owner
12 and a.segment_name = b.segment_name
13 and a.owner = 'ROGER'
14 and b.segment_name = 'KILLDB1'
15 order by 6;
OWNER SEGMENT_NAME INITIAL_EXTENT FILE_ID EXTENTS EXTENT_ID BLOCKS HEADER_FILE HEADER_BLOCK
---------- --------------- -------------- ---------- ---------- ---------- ---------- ----------- ------------
ROGER KILLDB1 65536 5 21 0 8 5 11
ROGER KILLDB1 65536 5 21 1 8 5 11
ROGER KILLDB1 65536 5 21 2 8 5 11
ROGER KILLDB1 65536 5 21 3 8 5 11
ROGER KILLDB1 65536 5 21 4 8 5 11
ROGER KILLDB1 65536 5 21 5 8 5 11
ROGER KILLDB1 65536 5 21 6 8 5 11
ROGER KILLDB1 65536 5 21 7 8 5 11
ROGER KILLDB1 65536 5 21 8 8 5 11
ROGER KILLDB1 65536 5 21 9 8 5 11
ROGER KILLDB1 65536 5 21 10 8 5 11
OWNER SEGMENT_NAME INITIAL_EXTENT FILE_ID EXTENTS EXTENT_ID BLOCKS HEADER_FILE HEADER_BLOCK
---------- --------------- -------------- ---------- ---------- ---------- ---------- ----------- ------------
ROGER KILLDB1 65536 5 21 11 8 5 11
ROGER KILLDB1 65536 5 21 12 8 5 11
ROGER KILLDB1 65536 5 21 13 8 5 11
ROGER KILLDB1 65536 5 21 14 8 5 11
ROGER KILLDB1 65536 5 21 15 8 5 11
ROGER KILLDB1 65536 7 21 16 128 5 11
ROGER KILLDB1 65536 6 21 17 128 5 11
ROGER KILLDB1 65536 5 21 18 128 5 11
ROGER KILLDB1 65536 7 21 19 128 5 11
ROGER KILLDB1 65536 6 21 20 128 5 11
21 rows selected.
SQL> select owner,
2 TABLE_NAME,
3 NUM_ROWS,
4 BLOCKS,
5 EMPTY_BLOCKS,
6 AVG_SPACE,
7 AVG_ROW_LEN
8 from DBA_TAB_STATISTICS
9 where table_name = 'KILLDB1'
10 and owner = 'ROGER';
OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN
---------- ------------------------------ ---------- ---------- ------------ ---------- -----------
ROGER KILLDB1 50094 708 60 861 97
SQL> select 16*8*(50094/708) from dual;
16*8*(50094/708)
----------------
9056.54237
SQL>
通过上面的查询，我们可以看出，exp在抽取数据的时候，是根据extent来的，换句话说
在exp导出表killdb1时，只是抽取了前面0~15个extent，虽然extent id 17，18,20是完好的。
也就是说使用exp进行导出的话，实际上是丢失了这3个extent部分的数据的。
第2种方法： --基于rowid进行数据抢救
表killdb1 的block分布情况如下：
SQL> col owner for a10
SQL> select a.owner,
2 a.segment_name,
3 a.initial_extent,
4 b.file_id,
5 a.extents,
6 b.extent_id,
7 b.blocks,
8 a.HEADER_FILE,
9 a.HEADER_BLOCK
10 from dba_segments a, dba_extents b
11 where a.owner = b.owner
12 and a.segment_name = b.segment_name
13 and a.owner = 'ROGER'
14 and b.segment_name = 'KILLDB1'
15 order by 6;
OWNER SEGMENT_NAME INITIAL_EXTENT FILE_ID EXTENTS EXTENT_ID BLOCKS HEADER_FILE HEADER_BLOCK
---------- --------------- -------------- ---------- ---------- ---------- ---------- ----------- ------------
ROGER KILLDB1 65536 5 21 0 8 5 11
ROGER KILLDB1 65536 5 21 1 8 5 11
ROGER KILLDB1 65536 5 21 2 8 5 11
ROGER KILLDB1 65536 5 21 3 8 5 11
ROGER KILLDB1 65536 5 21 4 8 5 11
ROGER KILLDB1 65536 5 21 5 8 5 11
ROGER KILLDB1 65536 5 21 6 8 5 11
ROGER KILLDB1 65536 5 21 7 8 5 11
ROGER KILLDB1 65536 5 21 8 8 5 11
ROGER KILLDB1 65536 5 21 9 8 5 11
ROGER KILLDB1 65536 5 21 10 8 5 11
OWNER SEGMENT_NAME INITIAL_EXTENT FILE_ID EXTENTS EXTENT_ID BLOCKS HEADER_FILE HEADER_BLOCK
---------- --------------- -------------- ---------- ---------- ---------- ---------- ----------- ------------
ROGER KILLDB1 65536 5 21 11 8 5 11
ROGER KILLDB1 65536 5 21 12 8 5 11
ROGER KILLDB1 65536 5 21 13 8 5 11
ROGER KILLDB1 65536 5 21 14 8 5 11
ROGER KILLDB1 65536 5 21 15 8 5 11
ROGER KILLDB1 65536 7 21 16 128 5 11
ROGER KILLDB1 65536 6 21 17 128 5 11
ROGER KILLDB1 65536 5 21 18 128 5 11
ROGER KILLDB1 65536 7 21 19 128 5 11
ROGER KILLDB1 65536 6 21 20 128 5 11
21 rows selected.
select 139+128 from dual; --267
select 139+2*128 from dual; --395
select 139+3*128 from dual; --523
select 139+4*128 from dual; --651
select 139+5*128 from dual; --779
SQL> select object_id from dba_objects where object_name='KILLDB1' and
2 owner='ROGER';
OBJECT_ID
----------
52059
SQL>
SQL> select 139+128 from dual;
select 139+2*128 from dual;
139+128
----------
267
SQL> select 139+3*128 from dual;
139+2*128
----------
395
SQL>
139+3*128
----------
523
SQL> select 139+4*128 from dual;
139+4*128
----------
651
SQL> select 139+5*128 from dual;
139+5*128
----------
779
SQL> set lines 200
SQL> SELECT dbms_rowid.rowid_create(1,52059,5,139,0) FROM dual;
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAACLAAA
SQL> select dbms_rowid.rowid_create(1,52059,5,267,0) from dual;
select dbms_rowid.rowid_create(1,52059,5,395,0) from dual;
select dbms_rowid.rowid_create(1,52059,5,523,0) from dual;
select dbms_rowid.rowid_create(1,52059,5,651,0) from dual;
select dbms_rowid.rowid_create(1,52059,5,779,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAELAAA
SQL>
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAGLAAA
SQL>
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAILAAA
SQL>
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAKLAAA
SQL>
DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAMLAAA
SQL>
SQL>
SQL> create table t1 as select * from roger.killdb1 where rowid < 'AAAMtbAAFAAAACLAAA';
Table created.
SQL> select count(*) from t1;
COUNT(*)
----------
9500 ---这个就是我们使用exp所能抽取的9500条数据
SQL> insert into t1
2 select *
3 from (select *
4 from roger.killdb1
5 where rowid < 'AAAMtbAAFAAAAELAAA'
6 union all
7 select *
8 from roger.killdb1
9 where rowid < 'AAAMtbAAFAAAAGLAAA'
10 and rowid >='AAAMtbAAFAAAAELAAA'
11 union all
12 select *
13 from roger.killdb1
14 where rowid < 'AAAMtbAAFAAAAKLAAA'
15 and rowid >= 'AAAMtbAAFAAAAGLAAA'
16 union all
17 select *
18 from roger.killdb1
19 where rowid < 'AAAMtbAAFAAAAMLAAA'
20 and rowid >= 'AAAMtbAAFAAAAKLAAA');
18347 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from t1;
COUNT(*)
----------
27847
SQL> ---我们可以看到，这样可以抢救出27847条数据，远大于9500条。
第三种方式： 使用ODU进行数据抽取
[ora10g@killdb data]$ ls -ltr
total 1036
-rw-r--r-- 1 ora10g oinstall 1036288 Nov 3 01:07 ROGER_KILLDB1.txt
-rw-r--r-- 1 ora10g oinstall 416 Nov 3 01:07 ROGER_KILLDB1.sql
-rw-r--r-- 1 ora10g oinstall 618 Nov 3 01:07 ROGER_KILLDB1.ctl
[ora10g@killdb data]$
SQL> @ /home/ora10g/odu/odu/data/ROGER_KILLDB1.sql
Table created.
SQL> !
[ora10g@killdb ~]$ cd /home/ora10g/odu/odu/data
[ora10g@killdb data]$ sqlldr system/oracle control=ROGER_KILLDB1.ctl
SQL*Loader: Release 10.2.0.5.0 - Production on Thu Nov 3 00:50:33 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached - logical record count 6492
Commit point reached - logical record count 9478
Commit point reached - logical record count 9479
[ora10g@killdb data]$ exit
exit
SQL> select count(*) from system.killdb1;
COUNT(*)
----------
9479
可以发现，对于低版本的OUD，似乎还存在一些问题，高版本的odu应该是ok的，我这里没测试。
目前我暂时所能想到的就这几种方式，如果谁还有更好的方法，请告诉我，谢谢，总的来说，
我个人认为第2种方式比较好，当然使用新版的ODU也比较省事，直接scan一下，然后unload，imp就完了。
分享到：
发表评论
-
Oracle关于时间/日期的操作
2012-04-09 23:12 8301.日期时间间隔操作 当前时间减去7分钟的时间 s ... -
oracle 数据库增量备份
2012-03-16 20:03 1117EXP和IMP是Oracle提供的一种逻辑备份工具。逻辑备份创 ... -
oracle start with connect by 用法
2012-02-19 23:58 990oracle 提供了start with connect by ... -
Oracle数据导入导出imp/exp
2012-02-09 17:38 590Oracle数据导入导出imp/exp 功能：Oracle数 ... -
性能监控SQL语句
2011-12-23 13:51 801分析表 analyze table tablename co ... -
常用SQL语句
2011-12-23 13:50 950查找数据库中所有字段 以对应的表 select C.colu ... -
安装到最后的两个脚本作用
2011-12-06 18:32 1016在linux下安装Oralce的时候，最后会让你以root的身 ... -
ORACLE中一个字符占多少字节
2011-12-01 22:07 3830在oracle中一个字符特别是中文占几个字节是不同的。 比如 ... -
提高SQL效率
2011-12-01 00:13 996下面就某些SQL 语句的 where 子句编写中需要注意 ... -
“exists”和“in”的效率问题
2011-11-30 23:49 988有两个简单例子，以说明 “exists”和“in”的效率问题 ... -
Oracle建立全文索引详解
2011-11-30 20:42 14521.全文检索和普通检索的区别 不使用Oracle text功 ... -
全文检索CLOB
2011-11-30 12:00 963建议使用全文检索(FULL TEXT SEARCH) ... -
“Oracle”数据库的“周数计算”
2011-11-16 13:25 954——日期计算，算第n周的第一天及最后一天是几号。 by key ... -
Oracle中TO_DATE格式
2011-11-16 13:17 806TO_DATE格式(以时间:2007-11-02 13:4 ... -
Oracle 索引 详解
2011-11-15 13:00 25998Oracle 索引 详解 ... -
Oracle SQL中的IN 和 EXSITS区别总结
2011-11-15 11:06 980IN 确定给定的值是否与子查询或列表中的值相匹配。 EX ... -
Oracle 字符集的查看和修改
2011-11-14 23:09 906一、什么是Oracle字符集 ... -
Oracle Flashback技术总结
2011-11-14 01:06 938Flashback 技术是以Undo segment中的内容为 ... -
oracle日志归档模式改变
2011-11-14 00:15 1069在Oracle数据库中，主要 ... -
oracle 性能优化建议小结
2011-10-24 23:45 420原则一：注意WHERE子句 ...
相关推荐
磁盘数据抢救工具,可以抢救磁盘的数据.谢谢使用。我喜欢这个软件
详细解释了硬盘数据存储的原理，描述了常用的数据恢复，数据修复，分区修复等数据急救的方法和技术。
本书主要介绍了硬盘常见故障判断及处理、硬盘硬故障的处理、硬盘软故障的处理、计算机病毒引起的硬盘故障处理、硬盘数据文件丢失的恢复、硬盘故障的预防、文件系统、系统数据的备份方法和工具、Windows XP的数据备份...
WST_542-2017_院前医疗急救基本数据集.pdf
关于计算机知识的资料!是好东西哦!
分区表损坏时的数据修复 硬盘主引导记录 在计算机系统中，硬盘分区表是存储硬盘的重要组成部分，它记录了硬盘的分区信息。如果分区表遭到破坏，可能会导致硬盘分区信息损失，甚至无法访问硬盘中的数据。因此，维护...
硬盘玩家,让你最快掌握数据备份、恢复与急救完全
急救人员登记表.doc
在各种原因造成的数据库不能打开时，用于抢救数据，最大限度地减少数据丢失。 ODU有什么功能特点？ 支持的Oracle数据库版本包括8i,9i,10g,11g 自动检测数据文件的表空间号和文件号 在有SYSTEM表空间的情况下，...
全国团体标准-区域急救平台及胸痛中心数据交互规范.pdf
数据备份、恢复与急救完全手册数据备份、恢复与急救完全手册
拯救硬盘数据抢救经验谈.docx
电脑日常维护与硬盘数据急救.ppt
ODU全称为Oracle Database Unloader，是类似于Oracle的DUL的软件，用于直接从Oracle数据库的数据文件中获取表数据。在各种原因造成的数据库不能打开时，用于抢救数据，最大限度地减少数据丢失。
电脑日常维护和数据急救知识.pptx
参考资料-急救人员登记表GDAQ21204.zip
抢救车 急救药品作用副作用一览表 (1) (1).doc
抢救U盘杀毒后不见的文件以及数据，有时真的很需要回复
针对急救中出现的医患纠纷，我们开发此设备，该设备的三个主要目的：第一，规范急救中收费规范化；第二，急救后可以直接打印并出具正规发票，符合目前营改增的税收政策...第三，将急救中获得的数据无线传输到服务器。