今天一朋友问到一个比较有意识的问题：
如果一个表空间，其中一个数据文件丢失，在无备份和非归档的情况下，如何最大程度的
把表空间里面的数据给抢救出来？
我当时直接的回答是，使用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 8551.日期时间间隔操作 当前时间减去7分钟的时间 s ... -
oracle 数据库增量备份
2012-03-16 20:03 1164EXP和IMP是Oracle提供的一种逻辑备份工具。逻辑备份创 ... -
oracle start with connect by 用法
2012-02-19 23:58 1031oracle 提供了start with connect by ... -
Oracle数据导入导出imp/exp
2012-02-09 17:38 627Oracle数据导入导出imp/exp 功能：Oracle数 ... -
性能监控SQL语句
2011-12-23 13:51 832分析表 analyze table tablename co ... -
常用SQL语句
2011-12-23 13:50 985查找数据库中所有字段 以对应的表 select C.colu ... -
安装到最后的两个脚本作用
2011-12-06 18:32 1050在linux下安装Oralce的时候，最后会让你以root的身 ... -
ORACLE中一个字符占多少字节
2011-12-01 22:07 3875在oracle中一个字符特别是中文占几个字节是不同的。 比如 ... -
提高SQL效率
2011-12-01 00:13 1031下面就某些SQL 语句的 where 子句编写中需要注意 ... -
“exists”和“in”的效率问题
2011-11-30 23:49 1023有两个简单例子，以说明 “exists”和“in”的效率问题 ... -
Oracle建立全文索引详解
2011-11-30 20:42 14761.全文检索和普通检索的区别 不使用Oracle text功 ... -
全文检索CLOB
2011-11-30 12:00 998建议使用全文检索(FULL TEXT SEARCH) ... -
“Oracle”数据库的“周数计算”
2011-11-16 13:25 996——日期计算，算第n周的第一天及最后一天是几号。 by key ... -
Oracle中TO_DATE格式
2011-11-16 13:17 837TO_DATE格式(以时间:2007-11-02 13:4 ... -
Oracle 索引 详解
2011-11-15 13:00 26050Oracle 索引 详解 ... -
Oracle SQL中的IN 和 EXSITS区别总结
2011-11-15 11:06 1016IN 确定给定的值是否与子查询或列表中的值相匹配。 EX ... -
Oracle 字符集的查看和修改
2011-11-14 23:09 952一、什么是Oracle字符集 ... -
Oracle Flashback技术总结
2011-11-14 01:06 967Flashback 技术是以Undo segment中的内容为 ... -
oracle日志归档模式改变
2011-11-14 00:15 1113在Oracle数据库中，主要 ... -
oracle 性能优化建议小结
2011-10-24 23:45 448原则一：注意WHERE子句 ...
相关推荐
在面对数据丢失或损坏的情况下，运用这些技术可以帮助我们最大程度地恢复数据，减少损失。此外，了解这些基础知识也有助于我们在日常使用计算机过程中采取有效的数据保护措施，避免不必要的数据丢失风险。
三维激光扫描技术在古建筑保护中的应用，改变了传统的手工绘图和近景摄影测量方法的局限性，能够快速而准确地进行建筑测绘，全面地表达建筑立体空间，为古建筑的保护、抢救及修复工作提供重要的科学记录档案。...
首先，我们要理解什么是shp文件格式，它是ESRI公司开发的一种通用的地理空间数据存储格式，广泛用于GIS数据交换和处理。大冶市的道路矢量shp数据，包含了该地区道路网络的几何信息（如线段和节点）、属性信息（如...
此外，模板还提供了空间添加具体数据和统计信息，比如在急救情况下的存活率或常见伤害的处理方法。 此外，HI-HOO的核心业务不仅限于PPT制作，还包括PPT模板/图表开发、动画演示开发和PPT设计，以及应用技能培训。这...
7. **数据分析**：通过对上海市某区过去三年公益活动中的急救医疗保障数据的分析，揭示了应急事件的特点和急救效能，为进一步改革提供了实证依据。 8. **医疗急救转运效能**：公益性活动中的医疗急救转运效能相对较...
1. 数据抢救：在灾难发生后，首要任务是在确保人员安全的前提下，抢救重要数据资源，包括文件、资料、磁带、硬盘和服务器。这一过程由安全组协调，遵循预先制定的计划，所有相关人员需服从统一调度。 2. 损坏评估：...
通过这份签到表，管理员可以系统地收集和分析培训数据，评估培训效果，同时为未来的消防培训提供改进依据。此外，这种记录方式也有助于满足法规要求，证明企业在消防安全方面的责任履行。总的来说，有效的消防安全...
只有综合考虑各种因素，才能最大程度地保障员工的生命安全和健康，同时保证生产的连续性和效率。在未来的发展中，随着技术的进步和管理水平的提高，这一领域的安全防护措施还将不断优化和完善。
5. **读取错误检测**：该工具可以检测和报告磁盘上的读取错误，这在诊断硬件问题或尝试从损坏的媒体中抢救数据时非常有价值。 6. **Boot Sector修复**：对于主引导记录（MBR）或分区表的问题，Examine32提供了一定...
【员工能力评价表】是企业...通过这份表格，企业能够全面了解员工的优势和改进空间，为员工的培训和发展提供依据，同时也为人力资源管理提供了数据支持。评估者在进行评价时，需保持公正客观，以促进员工的持续成长。
5. **设施选址**：在“最小成本路径”或“分配”分析中，可以找到最佳的设施位置，以最大程度地覆盖目标区域或最小化运输成本。 6. **交通流量分配**：利用“流量分配”工具，可以根据交通需求量在路网上分配流量，...
通过分析这些数据，机械陀螺仪的转速得以调整，确保拐杖在一定程度上保持直立，为老年人提供稳定支撑，防止因失衡而摔倒。 2. GPS定位系统：当老人遇到紧急情况或迷路时，集成的GPS模块可以精确定位老人的位置。...
- **知识点总结**：耦合度反映了模块间的关联程度，常见耦合类型按从强到弱排序依次为：内容耦合（最紧密，一个模块直接访问另一个模块内部数据）、控制耦合（通过传递控制信息进行连接）、公共环境耦合（多个模块...
煤气中毒的急救至关重要，应迅速将受害者移至新鲜空气中，并根据中毒程度进行相应处理，包括人工呼吸、心肺复苏等。急救过程中需注意保护自身安全，避免二次伤害。 【煤气事故应急处理】 煤气事故的应对涉及事故...
5. **数据恢复**：在修复过程中，部分工具可能提供数据恢复功能，帮助用户抢救因U盘问题而丢失的文件。 6. **安全防护**：辅助工具可能包含预防病毒感染的机制，比如写保护功能，防止U盘被恶意软件感染。 7. **...
同时，数据分析在其中的作用也不可忽视，通过对大量车辆数据的收集和分析，可以优化汽车性能，预测故障，进一步提升行车安全。这些都表明，电子信息与智能化技术在汽车行业的应用将对未来汽车行业的发展产生深远影响...
- 优化共享池，确保足够的空间用于库缓存和数据字典缓存，避免硬解析； - 调整重做日志缓冲区的大小，以确保重做日志在高事务率场景下的性能。 2. 优化PGA使用： - 调整PGA_AGGREGATE_TARGET参数，以管理PGA的...
**急救功能**：当遇到计算机Windows系统崩溃，无法正常启动的情况时，通过Linux live CD或安装在移动硬盘上的Linux系统，可以轻松地访问硬盘上的文件并将其备份到安全的地方，例如硬盘的其他分区、外部存储设备或...
南京马拉松在这些方面可能已经有一定的基础，但仍然存在优化的空间。 现状分析： 1. 急救人员配置：赛事中应有充足的急救人员分布于赛道各处，以便及时处理突发状况。南京马拉松可能需要评估现有急救人员的数量和...
- 应急救援设备：明确了应配备的应急设备，如消防器材、防护装备、医疗急救设施等，并规定了设备的定期检查和维护。 - 现场应急措施：针对不同类型的事故，制定具体的现场处理步骤，如火警、泄漏、中毒等，以指导...