Oracle11g数据库导入Oracle10g数据库 EXPDP方法
EXPDP要注意的是，导出的文件是在服务器端的 DATA_PUMP_DIR目录里
11g备份，导入10g的时候会抛错，直接阻止导入。
一、在11g服务器上，使用expdp命令备份数据
EXPDP USERID='SYS/cuc2009@cuc as sysdba' schemas=sybj directory=DATA_PUMP_DIR dumpfile=aa.dmp logfile=aa.log version=10.2.0.1.0
其中，红色文字部分是根据需要改写的地方。例如我的sys密码是cuc2009，数据库sid是cuc，要到出的用户名是sybj，要导入到10.2.0.1.0版本的Oracle数据库中去。aa.dmp和aa.log将会在11g的dpdump目录中生成，例如我的11g装在了E盘下面，于是aa.dmp将会在E:\app\Administrator\admin\cuc\dpdump目录下被生成。
二、在10g服务器上，使用impdp命令恢复数据
准备工作：1.建库2.建表空间3.建用户并授权4.将aa.dmp拷贝到10g的dpdump目录下
1-3点可以去参考博主的上一篇博客“Oracle数据库移植全步骤”，介绍的很详细，这里不再多说。关于第4点， 10g装在了e:\tools目录下，于是我将aa.dmp文件拷贝到了E:\tools\admin\cucf\dpdump目录下。
IMPDP USERID='SYS/cuc2009@cucf as sysdba' schemas=sybj directory=DATA_PUMP_DIR dumpfile=aa.dmp logfile=aa.log version=10.2.0.1.0
其中红色部分是根据需要改写的地方。例如我的sys密码是cuc2009，数据库sid是cucf，要导入用户名为sybj，要导入到10.2.0.1.0版本的Oracle数据库中去。aa.log将会在10g的dpdump目录中生成。
oracle导入与导出
导入与导出
1. 测试库与测试用户
2. expdp 命令帮助
3. 确认备份导出文件的位置
4. 导出用户数据
5. 导入用户的数据
6. 导入时已存在的表默认将不被重新覆盖
7. 将由用户testuser1导出的数据导入到testuser2中
8. 导出特定表并进行导入
1. 测试库与测试用户
SQL> create tablespace yzhqts datafile '/u02/oradata/testdb/yzhqts01.dbf' size 500m;
Tablespace created.
SQL> create user testuser1 identified by sun2moon default tablespace yzhqts;
User created.
SQL> grant connect,resource to testuser1;
Grant succeeded.
SQL>
然后建立测试数据
2. expdp 命令帮助
[oracle@web151 ~]$ impdp -help
[oracle@Manager ~]$ expdp -help
[oracle@web151 ~]$ impdp -help
Import: Release 10.2.0.4.0 - Production on Wednesday, 03 June, 2009 13:25:46
Copyright (c) 2003, 2007, Oracle. All rights reserved.
The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:
Format: impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
USERID must be the first parameter on the command line.
[oracle@Manager ~]$ expdp -help
Export: Release 10.2.0.4.0 - Production on Wednesday, 03 June, 2009 11:26:33
Copyright (c) 2003, 2007, Oracle. All rights reserved.
The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:
Format: expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
COMPRESSION Reduce size of dumpfile contents where valid
keyword values are: (METADATA_ONLY) and NONE.
CONTENT Specifies data to unload where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dumpfiles and logfiles.
DUMPFILE List of destination dump files (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD Password key for creating encrypted column data.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
ESTIMATE_ONLY Calculate job estimates without performing the export.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FILESIZE Specify the size of each dumpfile in units of bytes.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Export entire database (N).
HELP Display Help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of export job to create.
LOGFILE Log file name (export.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile (N).
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to export a subset of a table.
SAMPLE Percentage of data to be exported;
SCHEMAS List of schemas to export (login schema).
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
TABLES Identifies a list of tables to export - one schema only.
TABLESPACES Identifies a list of tablespaces to export.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
The following commands are valid while in interactive mode.
Note: abbreviations are allowed
Command Description
------------------------------------------------------------------------------
ADD_FILE Add dumpfile to dumpfile set.
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
FILESIZE Default filesize (bytes) for subsequent ADD_FILE commands.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=<number of workers>.
START_JOB Start/resume current job.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS[=interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.
[oracle@Manager ~]$
3. 确认备份导出文件的位置
========================
SQL> set linesize 180
SQL> col directory_path format a60
SQL> select * from dba_directories
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------------------------------------
SYS WORK_DIR /ade/aime_stamc10/oracle/work
SYS ADMIN_DIR /ade/aime_stamc10/oracle/md/admin
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/oracle/product/10.2.0/db_1/ccr/state
SYS DATA_PUMP_DIR /u01/app/oracle/oracle/product/10.2.0/db_1/rdbms/log/
SQL>
SQL> select directory_path from dba_directories where directory_name='DATA_PUMP_DIR';
DIRECTORY_PATH
--------------------------------------------------------------------------------
/u01/app/oracle/oracle/product/10.2.0/db_1/rdbms/log/
SQL>
4. 导出用户数据
expdp testuser1/sun2moon DIRECTORY=data_pump_dir DUMPFILE=testuser1.dmp
[oracle@Manager ~]$ expdp testuser1/sun2moon DIRECTORY=data_pump_dir DUMPFILE=testuser1.dmp
Export: Release 10.2.0.4.0 - Production on Wednesday, 03 June, 2009 11:52:49
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid
[oracle@Manager ~]$
注意：普通用户需要授权才能使用DATA_PUMP_DIR, 以下是操作过程。
SQL> grant read,write on directory DATA_PUMP_DIR to testuser1;
Grant succeeded.
SQL>
[oracle@Manager ~]$ expdp testuser1/sun2moon DIRECTORY=data_pump_dir DUMPFILE=testuser1.dmp
Export: Release 10.2.0.4.0 - Production on Wednesday, 03 June, 2009 11:54:10
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TESTUSER1"."SYS_EXPORT_SCHEMA_01": testuser1/******** DIRECTORY=data_pump_dir DUMPFILE=testuser1.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "TESTUSER1"."T1" 4.968 KB 2 rows
. . exported "TESTUSER1"."T2" 17.35 KB 128 rows
Master table "TESTUSER1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TESTUSER1.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/oracle/product/10.2.0/db_1/rdbms/log/testuser1.dmp
Job "TESTUSER1"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:54:41
[oracle@Manager ~]$
日志 export.log 会存放在 /u01/app/oracle/oracle/product/10.2.0/db_1/rdbms/log/
5. 导入用户的数据
=================
在另一台服务器上：
impdp testuser1/sun2moon DIRECTORY=data_pump_dir DUMPFILE=testuser1.dmp
注意：建立相应的表空间、用户及相应的权限，并把testuser1.dmp放入指定的目录
(/u01/app/oracle/oracle/product/10.2.0/db_1/rdbms/log/) 下
建立用户：
create tablespace yzhqts datafile '/u02/oradata/zytest/yzhqts01.dbf' size 500m;
create user testuser1 identified by sun2moon default tablespace yzhqts;
grant connect,resource to testuser1;
grant read,write on directory DATA_PUMP_DIR to testuser1;
导入数据：
[oracle@web151 ~]$ impdp testuser1/sun2moon DIRECTORY=data_pump_dir DUMPFILE=testuser1.dmp
Import: Release 10.2.0.4.0 - Production on Wednesday, 03 June, 2009 13:49:40
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TESTUSER1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TESTUSER1"."SYS_IMPORT_FULL_01": testuser1/******** DIRECTORY=data_pump_dir DUMPFILE=testuser1.dmp
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER1"."T1" 4.968 KB 2 rows
. . imported "TESTUSER1"."T2" 17.35 KB 128 rows
Job "TESTUSER1"."SYS_IMPORT_FULL_01" successfully completed at 13:49:43
[oracle@web151 ~]$
SQL> set linesize 180
SQL> col segment_name format a10
SQL> select owner, segment_name ,segment_type, tablespace_name from dba_segments where owner='TESTUSER1' order by TABLESPACE_NAME;
OWNER SEGMENT_NA SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ---------- ------------------ ------------------------------
TESTUSER1 T1 TABLE YZHQTS
TESTUSER1 T2 TABLE YZHQTS
SQL>
可以看到数据按照要求已经全部跑到yzhqts表空里了。
6. 导入时已存在的表默认将不被重新覆盖
===================================
例如
[oracle@web151 log]$ impdp testuser1/sun2moon DIRECTORY=data_pump_dir DUMPFILE=testuser1-01.dmp
Import: Release 10.2.0.4.0 - Production on Wednesday, 03 June, 2009 14:44:21
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TESTUSER1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TESTUSER1"."SYS_IMPORT_FULL_01": testuser1/******** DIRECTORY=data_pump_dir DUMPFILE=testuser1-01.dmp
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "TESTUSER1"."T1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "TESTUSER1"."T2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER1"."T3" 4.937 KB 1 rows
Job "TESTUSER1"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 14:44:23
[oracle@web151 log]$
7. 将由用户testuser1导出的数据导入到testuser2中
知识点：
remap_schema=源用户:目标用户
Using this command you can import data from one schema to other schema
Even if the schema or username is not present it gets created.
Following is the syntax of creating Remap_Schema
impdp scott/tiger@oracle directory=testdir dumpfile=bij.dmp remap_schema=scott:testscott
例子1：
$ impdp testuser1/sun2moon DIRECTORY=data_pump_dir DUMPFILE=testuser1-01.dmp remap_schema=testuser1:testuser2
（1）先创建用户testuser2
create user testuser2 identified by sun2moon default tablespace yzhqts;
grant connect,resource to testuser2;
(2) 给用户testuser1 授导入权限
SQL> grant imp_full_database to testuser1;
Grant succeeded.
目标数据库上执行上面这条授权命令，否则会报如下错误：
Import: Release 10.2.0.4.0 - Production on Wednesday, 03 June, 2009 16:12:58
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remappings.
[oracle@web151 ~]$ impdp testuser1/sun2moon DIRECTORY=data_pump_dir DUMPFILE=testuser1-01.dmp remap_schema=testuser1:testuser2
Import: Release 10.2.0.4.0 - Production on Wednesday, 03 June, 2009 16:19:12
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TESTUSER1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TESTUSER1"."SYS_IMPORT_FULL_01": testuser1/******** DIRECTORY=data_pump_dir DUMPFILE=testuser1-01.dmp remap_schema=testuser1:testuser2
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER2"."T1" 4.976 KB 3 rows
. . imported "TESTUSER2"."T2" 17.35 KB 128 rows
. . imported "TESTUSER2"."T3" 4.937 KB 1 rows
Job "TESTUSER1"."SYS_IMPORT_FULL_01" successfully completed at 16:19:13
[oracle@web151 ~]$
例子2：
也可以直接使用 testuser2用户进行导入，此时就没有必要建立testuser1用户了。
create user testuser2 identified by sun2moon default tablespace yzhqts;
grant connect,resource to testuser2;
grant read,write on directory DATA_PUMP_DIR to testuser2;
grant imp_full_database to testuser2;
impdp testuser2/sun2moon DIRECTORY=data_pump_dir DUMPFILE=testuser1-01.dmp remap_schema=testuser1:testuser2
[oracle@web151 ~]$ impdp testuser2/sun2moon DIRECTORY=data_pump_dir DUMPFILE=testuser1-01.dmp remap_schema=testuser1:testuser2
Import: Release 10.2.0.4.0 - Production on Wednesday, 03 June, 2009 16:40:31
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TESTUSER2"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TESTUSER2"."SYS_IMPORT_FULL_01": testuser2/******** DIRECTORY=data_pump_dir DUMPFILE=testuser1-01.dmp remap_schema=testuser1:testuser2
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER2"."T1" 4.976 KB 3 rows
. . imported "TESTUSER2"."T2" 17.35 KB 128 rows
. . imported "TESTUSER2"."T3" 4.937 KB 1 rows
Job "TESTUSER2"."SYS_IMPORT_FULL_01" successfully completed at 16:40:33
[oracle@web151 ~]$
8. 导出特定表并进行导入
=======================
原库：导出用户的表t1,t2
$ expdp becvx/sun2moon tables=\(t1,t2\) directory=dp dumpfile=becvx01.dmp logfile=exp_becvx.log job_name=exp_becvx
Export: Release 10.2.0.4.0 - Production on Wednesday, 10 June, 2009 11:18:13
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "BECVX"."EXP_BECVX": becvx/******** tables=(t1,t2) directory=dp dumpfile=becvx01.dmp logfile=exp_becvx.log job_name=exp_becvx
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "BECVX"."T1" 67.95 KB 256 rows
. . exported "BECVX"."T2" 4.960 KB 1 rows
Master table "BECVX"."EXP_BECVX" successfully loaded/unloaded
******************************************************************************
Dump file set for BECVX.EXP_BECVX is:
/u02/backup/becvx01.dmp
Job "BECVX"."EXP_BECVX" successfully completed at 11:18:24
目标库：创建用户，并授予相应的权限（注意此次导出没有带任何的用户权限之类的数据，所以要全新创建用户）
create user becvx identified by sun2moon ;
grant connect, resource to becvx;
create directory dp as '/u02/backup'; <---- 之前执行过后，不用再执行
grant read,write on directory dp to public;
$ impdp becvx/sun2moon directory=dp dumpfile=becvx01.dmp
Import: Release 10.2.0.4.0 - Production on Wednesday, 10 June, 2009 10:55:45
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "BECVX"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "BECVX"."SYS_IMPORT_FULL_01": becvx/******** directory=dp dumpfile=becvx01.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "BECVX"."T1" 67.95 KB 256 rows
. . imported "BECVX"."T2" 4.960 KB 1 rows
Job "BECVX"."SYS_IMPORT_FULL_01" successfully completed at 10:55:46
1)问题1： Oracle11g数据库导入Oracle10g数据库操作导入解决方法：
11g备份，导入10g的时候会抛错，直接阻止导入。
但是有时候还必须得把11g的数据库导入到10g，我今天就遇到了这种情况。
一、在11g服务器上，使用expdp命令备份数据
EXPDP USERID='SYS/cuc2009@cuc as sysdba' schemas=sybj directory=DATA_PUMP_DIR dumpfile=aa.dmp logfile=aa.log version=10.2.0.1.0
其中，红色文字部分是根据需要改写的地方。例如我的sys密码是cuc2009，数据库sid是cuc，要到出的用户名是sybj，要导入到10.2.0.1.0版本的Oracle数据库中去。aa.dmp和aa.log将会在11g的dpdump目录中生成，例如我的11g装在了E盘下面，于是aa.dmp将会在E:\app\Administrator\admin\cuc\dpdump目录下被生成。
二、在10g服务器上，使用impdp命令恢复数据
准备工作：1.建库2.建表空间3.建用户并授权4.将aa.dmp拷贝到10g的dpdump目录下
1-3点可以去参考博主的上一篇博客“Oracle数据库移植全步骤”，介绍的很详细，这里不再多说。关于第4点，我的10g装在了e:\tools目录下，于是我将aa.dmp文件拷贝到了E:\tools\admin\cucf\dpdump目录下。
IMPDP USERID='SYS/cuc2009@cucf as sysdba' schemas=sybj directory=DATA_PUMP_DIR dumpfile=aa.dmp logfile=aa.log version=10.2.0.1.0
其中红色部分是根据需要改写的地方。例如我的sys密码是cuc2009，数据库sid是cucf，要导入用户名为sybj，要导入到10.2.0.1.0版本的Oracle数据库中去。aa.log将会在10g的dpdump目录中生成。
2）问题2：Oracle 11g导出来的dmp导入到 10g的数据库(IMP-00010：不是有效的导出文件，头部验证失败)
oracle 11g R2 导出去的dmp文件，导入11g R1或10G的，都显示：
IMP-00010：不是有效的导出文件，头部验证失败
为了这个问题一直苦恼，差点就想卸掉11g然后装10g了，后来想想，头部验证，那么头部到底是什么，用Notepad++查看了dmp文件，发现头部真的显示一些东西：
11g R2：V11.02.00
11g R1：V11.01.00
10g：V10.02.01
把版本改成对方机子数据库版本，执行imp就不再报错了。
考虑到如果文件过大，可能打不开导致死机，做个小程序。（C# WINFORM）
2个按钮的事件：
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog file = new OpenFileDialog();
file.InitialDirectory = Application.ExecutablePath;
if (file.ShowDialog() == DialogResult.OK)
{
String path =label11.Text= file.FileName;
FileStream fs = File.OpenRead(path);
fs.Seek(0, SeekOrigin.Begin);
byte[] byData = new byte[100];
fs.Read(byData, 0, 50);
string charData = new UTF8Encoding(true).GetString(byData, 0, byData.Length);
string[] da = System.Text.RegularExpressions.Regex.Split(charData, @":V", RegexOptions.IgnoreCase);
Regex r = new Regex(@":V\d{2}\.\d{2}\.\d{2}");
Match m = r.Match(charData);
label9.Text = m.Index.ToString ();
label10.Text = m.Length.ToString();
textBox1.Text = System.Text.RegularExpressions.Regex.Split(m.Value, @":V", RegexOptions.IgnoreCase)[1];
fs.Close();
}
}
private void button2_Click(object sender, EventArgs e)
{
Regex r = new Regex(@"\d{2}\.\d{2}\.\d{2}");
Match m = r.Match(textBox1.Text);
if (m.Success)
{
FileStream fs = File.OpenWrite(label11.Text);
fs.Seek(int.Parse(label9.Text.ToString())+2, SeekOrigin.Begin);
Byte[] info = new UTF8Encoding(true).GetBytes(textBox1.Text);
fs.Write(info, 0, info.Length);
fs.Close();
MessageBox.Show("版本修改成功。");
}
else
MessageBox.Show("版本格式错误。");
}
3)问题3： 11g exp数据时就会遗漏记录数为0的表导入错误解决方法：
原因在于11gR2中的新功能 – Deferred Segment Creation（延迟段创建），默认情况 下这个功能是启用的。
SQL>show parameter DEFERRED_SEGMENT_CREATION
NAME TYPE VALUE
------------------------------------ --------------------
deferred_segment_creation boolean TRUE
延迟段创建的含义是当此新创建一个可能会有Segment的对象时（比如表、索引、物化 视图等），如果这个对象中还没有任何记录需要消耗一个Extent，那么将不会在创建对象时自动创建Segment，这样做的好处无疑是在创建对象时大大提高了速度。
对于上例中的T2表，我们在创建结束就立刻检查DBA_SEGMENTS视图，会发现没有任何记
录。
SQL>select segment_name from user_segments where segment_name='T2';
no rows selected
而对于exp程序而言，当仅仅存在Object的定义而没有相应的Segment时，就会报出
EXP-00011对象不存在的错误。
解决方法就很简单了，以下方法任选其一。
1. 设置DEFERRED_SEGMENT_CREATION为FALSE，这样创建对象时就会自动创建Segment
2. 在创建对象时，明确指定立刻创建Segment
create table t2 (n number) SEGMENT CREATION IMMEDIATE;
3. 使用expdp替代exp（Datapump本身就是Oracle10g以后的推荐工具）
D:\Tempexpdp kamus/oracle tables=t2
Export: Release 11.2.0.1.0 - Production on Fri Apr 16 18:14:41 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights
reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
64bit Production
With the Partitioning, Oracle Label Security, Data Mining and Real
Application Testing opt ions
Starting "KAMUS"."SYS_EXPORT_TABLE_01": kamus/******** tables=t2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "KAMUS"."T2" 0 KB 0
rows
Master table "KAMUS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
****************************************************************************
Dump file set for KAMUS.SYS_EXPORT_TABLE_01 is:
D:\ORACLE\ADMIN\ORCL\DPDUMP\EXPDAT.DMP
Job "KAMUS"."SYS_EXPORT_TABLE_01" successfully completed at 18:15:10
4、如果一定要用exp的话可以考虑给空表分配段：
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0
310
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
