exp/imp导出导入工具的使用
一. 导出工具 exp
1. 它是操作系统下一个可执行的文件 存放目录/ORACLE_HOME/bin
exp导出工具将数据库中数据备份压缩成一个二进制系统文件.可以在不同OS间迁移
它有三种模式：
用户模式： 导出用户所有对象以及对象中的数据；
表模式： 导出用户所有表或者指定的表；
整个数据库： 导出数据库中所有对象。
2. 导出工具exp非交互式命令行方式的例子
$exp scott/tiger tables=(emp,dept) file=/directory/scott.dmp log=/opt/oracle/bak/e.log
说明:把scott用户里两个表emp,dept导出到文件/directory/scott.dmp
"'sys/oracle as sysdba'"
tables=scott.emp
file=d:/d11.dmp ,d:/d12.dmp
filesize=1m log=/opt/oracle/bak/e.log
filesize指定生成的二进制备份文件的最大字节数
(可用来解决某些OS下2G物理文件的限制及加快压缩速度和方便刻历史数据光盘等)
以多个固定大小文件方式导出：这种做法通常用在表数据量较大，单个 dump文件可能会超出文件系统的限制的情况
$exp scott/tiger owner=scott
file=/opt/oracle/bak/scott.dmp
log=/opt/oracle/bak/e.log
整个数据库
$exp system/oracle
file=/opt/oracle/bak/full.dmp
log=/opt/oracle/bak/e.log
3.远程数据库导出到本地：
$exp system/oracle@orcl owner=scott
file=/opt/oracle/bak/scott.dmp
log=/opt/oracle/bak/e.log
(配制好网络服务名 ip,端口号)
二.导入工具 imp
1. 它是操作系统下一个可执行的文件 存放目录/ORACLE_HOME/bin
imp导入工具将EXP形成的二进制系统文件导入到数据库中.
只有拥有IMP_FULL_DATABASE和DBA权限的用户才能做整个数据库导入
(1) create table
(2) insert data
(3) create index (4) create triggers,constraints
2.导入工具imp非交互式命令行方式的例子
scott/tiger tables=emp file=c:/bak/e1.dmp ignore=y
注意：导入时候，表已经存在，需要加上ignore=y（忽略表结构存在，创建表在插入记录)
如果该表有唯一性约束，并且已经存在行记录，那么要去除约束后再导入
将a用户的表导入到b用户下：
[oracle@node247 ~]exp scott/tiger tables=a file=/opt/oracle/bak/emp.dmp
[oracle@node247 ~]imp system/oracle tables=a
file=/opt/oracle/bak/emp.dmp fromuser=scott touser=system
SQL& show user
USER is "SYSTEM"
SQL& desc a
----------------------------------------- -------- ----------------------------
VARCHAR2(10)
VARCHAR2(9)
NUMBER(7,2)
NUMBER(7,2)
3.导入工具imp可能出现的问题
(1) 数据库对象已经存在
一般情况, 导入数据前应该彻底删除目标数据下的表, 序列, 函数/过程,触发器等;
数据库对象已经存在, 按缺省的imp参数, 则会导入失败
如果用了参数ignore=y, 会把exp文件内的数据内容导入
如果表有唯一关键字的约束条件, 不合条件将不被导入
如果表没有唯一关键字的约束条件, 将引起记录重复
(2) 数据库对象有主外键约束
不符合主外键约束时, 数据会导入失败
解决办法: 先导入主表, 再导入依存表
disable目标导入对象的主外键约束, 导入数据后, 再enable它们
alter table dept disable constraint pk_(cascade将参照pk_dept参照完整性约束也置为不可用)。
drop constraint 后 重建；
alter table dept enable constraint pk_
alter table emp enable constraint fk_
exp scott/tiger tables=emp file=d:/e1.dmp
SQL& ALTER TABLE EMP DISABLE CONSTRAINT PK_EMP;
imp scott/tiger tables=emp file=d:/e1.dmp ignore=y INDEXES=N
SQL& ALTER TABLE EMP ENABLE CONSTRAINT PK_EMP;
ALTER TABLE EMP ENABLE CONSTRAINT PK_EMP
第 1 行出现错误:
ORA-02437: 无法验证 (SCOTT.PK_EMP) - 违反主键
表被截断。
SQL& ALTER TABLE EMP ENABLE CONSTRAINT PK_EMP;
表已更改。
C:\Documents and Settings\Administrator&imp scott/tiger tables=emp file=d:/e1.dmp ignore=y
如果要把A用户的数据导入B用户下, A用户需要有imp_full_database权限
导入大表( 大于80M ) 时, 存储分配失败
默认的EXP时, compress = Y, 也就是把所有的数据压缩在一个数据块上.
导入时, 如果不存在连续一个大数据块, 则会导入失败.
导出80M以上的大表时, 记得compress= N, 则不会引起这种错误.
(5) imp和exp使用的字符集不同
如果字符集不同, 导入会失败, 可以改变unix环境变量或者NT注册表里NLS_LANG相关信息.
导入完成后再改回来.
(6) imp和exp版本不能往上兼容
imp可以成功导入低版本exp生成的文件, 不能导入高版本exp生成的文件
三.exp imp 参数：
BUFFER=4096000(数据缓冲区大小 ,以字节为单位,可自行调整)
FEEDBACK (每 x 行显示进度 (0) )
COMPRESS=Y
(将在导出的同时合并碎块)
CONSTRAINTS=Y
(以direct方式导出，告诉EXP直接读取数据,速度快)
注意上面的第一个BUFFER和最后一个DIRECT参数,对性能有很大影响.
BUFFER和FEEDBACK，在导出比较多的数据时，考虑设置这两个参数
&导出某用户下的所有表&
set heading off
set linesize 200
set trimspool on
set feedback off
set verify off
spool /opt/oracle/bak/scriptfulltable.
select 'exp
scott/tiger tables='||table_name||' file=/opt/oracle/bak/'||table_name||'.dmp ' from user_
完成后在找到 /opt/oracle/bak/scriptfulltable.txt，编辑该批处理文件，将第一行和最后一行删掉，保存后运行该批处理文件就可全部导出该用户下的所有表。
&实现表跨越表空间&
在做2个库的导入导出工作。2边字符集要求一致，否则有很多问题，修改字符集风险很大
grant unlimited就算是在每个表空间上都没有配额限制，move可以就是说有时候可能创建用户的时候
会限制那个用户使用缺省表空间大小，这个就是不限制了
当imp回tablespace的table时报错，在某表空间下有配额限制导致
一，同一个表空间的处理
1，exp scott/scott tables=emp file=c:\life\a.txt
之后生成二进制文件，在将此文件导回同一个tablespace时候，
针对该表的处理方法不同truncate或者drop ,有不同的处理方法
2，truncate 后，导入时
imp scott/scott tables=emp file=c:\life\a.txt ignore=y(因为存在冲突)
drop后，导入时
imp scott/scott tables=emp file=c:\life\a.txt(不存在冲突)
3,当一张表没有被drop或者truncate 时，导入会失败
二.跨越表空间的处理
SQL& select
SEGMENT_NAME,TABLESPACE_NAME from dba_segments
where SEGMENT_NAME='EMP' and
OWNER='SCOTT';
SEGMENT_NAME
--------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
exp scott/scott tables=emp file=c:\life\a.txt
drop table scott.emp
imp scott/scott tables=emp file=c:\life\a.txt indexfile=c:\scott.emp_sql.txt 常见脚本，打开这个个脚本后，
进行修改，把rem都删除，在把其中表空间名字改为目的表空间名字，也就是我们要跨越的移出目的地表空间的名字
其中有三处 （注意大写，创建表的时候里面有tablespace users,把这个users改为system,把前面那些 REM去掉）
SQL& CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4, 0), "ENAME"
VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE,
"SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"
LOGGING NOCOMPRESS ;
表创建成功
是在SYSTEM表空间创建了一个emp表，最终实现表跨越表空间
在把二进制文件导入进来就可以了
imp scott/scott tables=emp file=C:\life\a.txt ignore=y
SQL& select
SEGMENT_NAME,TABLESPACE_NAME from dba_segments
where SEGMENT_NAME='EMP' and
OWNER='SCOTT';
SEGMENT_NAME
-----------------------------------------------------------------
TABLESPACE_NAME
------------------------------
&常见错误&
EXP时发生ORA-00932错误
EXP-00056: 遇到 ORACLE 错误 932
ORA-00932: 数据类型不一致: 应为 BLOB, CLOB, 但却获得 CHAR
EXP-00000: 导出终止失败
原因：数据字典中有存在一些无效KU$_视图；
SQL& select * from sys.ku$_xmlschema_
select * from sys.ku$_xmlschema_view
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
解决办法：
1.执行对象视图定义脚本 $ORACLE_HOME/rdbms/admin/catmetx.sql
SQL& @?/rdbms/admin/catmetx.sql
Package altered.
Index altered.
View created.
Grant succeeded.
View created.
Grant succeeded.
Procedure created.
System altered.
System altered.
System altered.
PL/SQL procedure successfully completed.
Procedure dropped.
2.执行重新编译无效对象脚本 $ORACLE_HOME/rdbms/admin/utlrp.sql
SQL& @?/rdbms/admin/utlrp.sql
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
验证一下程序
SQL& select * from ku$_xmlschema_
V V OWNER_NUM OWNER_NAME URL
- - ---------- ------------------------------ ---------------
1 0 46 MDSYS http://www.opengis.net/gml/geometry.xsd
1 0 46 MDSYS http://www.w3.org/1999/xlink/xlinks.xsd
1 0 46 MDSYS http://www.opengis.net/gml/feature.xsd
重新执行导出程序exp，一些正常。
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31672: Worker process DW01 died unexpectedly.
When available, download Patch 5879865 to resolve this issue.
Note that this patch was not available at the time of writing this article ({JAN 2008}).
2. In the meantime, please use one of the following as a workaround:
set both of the following in the init/spfile
"_complex_view_merging" = false
event="38066 trace name context forever, level 1"
or level 2
"_optimizer_cost_based_transformation"=off
Any of these workarounds can be done at the system level or set in the init or spfile file.
===============
AT SYSTEM LEVEL:
SQL& alter system set events '38066 trace name context forever, level 2';
SQL& alter system set "_complex_view_merging" =
SQL& alter system set "_optimizer_cost_based_transformation"=off
IN THE INIT.ORA:
event="38066 trace name context forever, level 2"
_complex_view_merging=false
_optimizer_cost_based_transformation=off
IN THE SPFILE (requires restart of instance):
SQL& alter system set events '38066 trace name context forever, level 2' scope=
SQL& alter system set "_complex_view_merging" = false scope=
SQL& alter system set "_optimizer_cost_based_transformation"=off scope=
Flush the shared pool or restart the database.
SQL&alter system flush shared_
Perform the Data Pump export again.
&可传输表空间&
操作系统字节顺序相同,可以在平台之间传输表空间(源数据库Linux IA (32-bit) ，目标数据库Microsoft Windows IA (32-bit)）
1. 使表空间为只读：
alter table
2. 导出表空间。在操作系统提示符下执行：
exp tablespaces=lijh transport_tablespace=y file=/oracle/exp_lijh.dmp
exp_lijh.dmp文件只包含元数据（不是表空间 USERS 的内容）因此它将非常小。
3. 将文件 exp_lijh.dmp 和 lijh.dbf 拷贝至主机 TGT1。如果您使用 FTP，那么您将需要指定二进制选项。
4. 将表空间插入到数据库中。在操作系统命令提示符下执行下面的语句：
imp tablespaces=lijh transport_tablespace=y file=D:\oracle\exp_lijh.dmp
datafiles='D:\oracle\lijh.dbf'
C:\Documents and Settings\Administrator&imp tablespaces=lijh transport_tablespace=y file=D:\oracle\exp_lijh.dmp
datafiles='D:\oracle\lijh.dbf'
Import: Release 10.2.0.1.0 - Production on 星期日 3月 8 16:20:31 2009
Copyright (c) , Oracle.
All rights reserved.
用户名: sys as sysdba
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
经由常规路径由 EXPORT:V10.02.01 创建的导出文件
即将导入可传输的表空间元数据...
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
导出客户机使用 US7ASCII 字符集 (可能的字符集转换)
. 正在将 SYS 的对象导入到 SYS
. 正在将 SYS 的对象导入到 SYS
成功终止导入, 没有出现警告。
SQL& select tablespace_name from dba_
TABLESPACE_NAME
------------------------------
已选择8行。
跨不同字节顺序的平台:
(在源平台上转换文件)
1.使表空间为只读：
SQL& alter tables
表空间已更改。
2. 导出表空间。在操作系统提示符下执行：
exp tablespaces=users transport_tablespace=y file=exp_ts_users.dmp
exp_ts_users.dmp 文件只包含元数据（不是表空间 USERS 的内容）因此它将非常小。
convert tablespace users
to platform 'HP-UX (64-bit)'
format='/oracle/%n_%f';
启动 backup 于 08-3月 -09
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USE
已转换的数据文件 = /oracle/ORCLXXXX_4
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
完成 backup 于 08-3月 -09
将文件 exp_ts_users.dmp 和 users_01.dbf 拷贝至主机 TGT1。如果您使用 FTP，那么您将需要指定二进制选项。
4. 将表空间插入到数据库中。在操作系统命令提示符下执行下面的语句：
imp tablespaces=users transport_tablespace=y file=exp_ts_users.dmp datafiles='users_01.dbf
(在目标平台上转换文件)
RMAN& convert
datafile 'D:\oracle\solo.dbf'
3& format 'D:\oracle\product\10.2.0\oradata\orcl\solo.dbf';
启动 backup 于 08-3月 -09
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 启动数据文件转换
输出文件名=D:\ORACLE\SOLO.DBF
已转换的数据文件 = D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SOLO.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:08
sys/oracle tablespaces=solo transport_tablespace=y file=F:\solo.dmp datafiles='D:\oracle\product\10.2.0\oradata\orcl\solo.dbf';
查看当前系统平台：
SQL& select PLATFORM_ID,PLATFORM_NAME from v$
PLATFORM_ID
-----------
PLATFORM_NAME
------------------------------------------------------------------
Microsoft Windows IA (32-bit)
SQL& select * from v$transportable_
PLATFORM_ID PLATFORM_NAME
ENDIAN_FORMAT
----------- ---------------------------------------- --------------
1 Solaris[tm] OE (32-bit)
2 Solaris[tm] OE (64-bit)
7 Microsoft Windows IA (32-bit)
10 Linux IA (32-bit)
6 AIX-Based Systems (64-bit)
3 HP-UX (64-bit)
5 HP Tru64 UNIX
4 HP-UX IA (64-bit)
11 Linux IA (64-bit)
15 HP Open VMS
8 Microsoft Windows IA (64-bit)
PLATFORM_ID PLATFORM_NAME
ENDIAN_FORMAT
----------- ---------------------------------------- --------------
9 IBM zSeries Based Linux
13 Linux 64-bit for AMD
16 Apple Mac OS
12 Microsoft Windows 64-bit for AMD
17 Solaris Operating System (x86)
18 IBM Power Based Linux
SQL& select * from nls_database_parameters
PARAMETER='NLS_CHARACTERSET';
------------------------------ -----------------
NLS_CHARACTERSET
已选择20行。
修改字符集：
SQL& startup mount
ORACLE 例程已经启动。
Total System Global Area
Fixed Size
1250428 bytes
Variable Size
Database Buffers
Redo Buffers
7135232 bytes
数据库装载完毕。
SQL& ALTER SYSTEM ENABLE RESTRICTED SESSION;
系统已更改。
SQL& ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
系统已更改。
SQL& ALTER SYSTEM SET AQ_TM_PROCESSES=0;
系统已更改。
SQL& ALTER DATABASE OPEN;
数据库已更改。
SQL& ALTER DATABASE CHARACTER SET ZHS16GBK;
数据库已更改。
SQL& ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
数据库已更改。
SQL& select * from nls_database_parameters
PARAMETER='NLS_CHARACTERSET';
------------------------------
-------------------------------------------------------------------------------
NLS_CHARACTERSET
&非自包含的表空间传输&
错误模拟，表空间不是自包含的，无法传输
SQL& alter tablespace tt1_e2
Tablespace altered.
exp tablespaces=tt1_e2
transport_tablespace=y file=/u01/app/oracle/oradata/emrep/tt1_e2.dmp
Export: Release 10.2.0.4.0 - Production on Thu Jun 24 16:27:47 2010
Copyright (c) , Oracle.
All rights reserved.
Username: sys as sysdba
Password: ******
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
EXP-00008: ORACLE error 29341 encountered
ORA-29341: The transportable set is not self-contained
ORA-06512: at "SYS.DBMS_PLUGTS", line 1387
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
解决办法：找到跟他相关联的表空间，一起传输
SQL& exec sys.dbms_tts.transport_set_check('tt1_e2',true);
PL/SQL procedure successfully completed.
SQL& select * from sys.transport_set_
VIOLATIONS
--------------------------------------------------------------------------------
Index TTS_U1.IDX_A in tablespace TT1_E2 points to table TTS_U1.EMP in tablespace
Index TTS_U1.IDX_B in tablespace TT1_E2 points to table TTS_U1.DEPT in tablespac
SQL& exec sys.dbms_tts.transport_set_check('tt1_e1',true);
PL/SQL procedure successfully completed.
SQL& select * from sys.transport_set_－－－没有记录，说明该表空间可以传输
no rows selected
SQL& exec sys.dbms_tts.transport_set_check('tt1_e1,tt1_e2',true);
PL/SQL procedure successfully completed.
SQL& select * from sys.transport_set_
no rows selected
exp tablespaces=tt1_e2, tt1_e1 transport_tablespace=y file=/u01/app/oracle/oradata/emrep/tt1_e.dmp
create user tts_u1 identified by tts_u1
tablespaces=tt1_e2, tt1_e1 transport_tablespace=y file=/oracle/oradata/rb/tt1_e.dmp
datafiles=/oracle/oradata/rb/tt1_e1.dbf,/oracle/oradata/rb/tt1_e2.dbf
&分区表的导出导入&
SQL& show user
USER 为 "SCOTT"
SQL& create table range (a int,b varchar2(30)) partition by range(a)
(partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30)
表已创建。
insert into range values(9,'aa');
已创建 1 行。
insert into range values(12,'aa');
已创建 1 行。
提交完成。
---------- ------------------------------
导出某个分区表的分区：
&exp scott/tiger file=c:/bak/fq.dmp tables=range:p1
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的表通过常规路径...
. . 正在导出表
. . 正在导出分区
成功终止导出, 没有出现警告。
directory=dump_dir
dumpfile=t_tt.dp tables=scott.emp,scott.range
Export: Release 10.2.0.1.0 - Production on 星期六, 03 4月, :45
Copyright (c) , Oracle.
All rights reserved.
用户名: sys as sysdba
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
启动 "SYS"."SYS_EXPORT_TABLE_01":
sys/******** AS SYSDBA directory=dump_dir dum
pfile=t_tt.dp tables=scott.emp,scott.range
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 192 KB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . 导出了 "SCOTT"."EMP"
. . 导出了 "SCOTT"."RANGE":"P1"
. . 导出了 "SCOTT"."RANGE":"P2"
. . 导出了 "SCOTT"."RANGE":"P3"
已成功加载/卸载了主表 "SYS"."SYS_EXPORT_TABLE_01"
******************************************************************************
SYS.SYS_EXPORT_TABLE_01 的转储文件集为:
C:\BAK\DUMP\T_TT.DP
作业 "SYS"."SYS_EXPORT_TABLE_01" 已于 16:22:04 成功完成
eg2: 分区表从scott用户导入到system用户
&expdp scott/tiger
directory=DUMP_DIR dumpfile=Article_Detail_20W.dmp
tables=range content=DATA_ONLY parallel=2
port: Release 10.2.0.1.0 - Production on 星期六, 03 4月, :12
pyright (c) , Oracle.
All rights reserved.
接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
th the Partitioning, OLAP and Data Mining options
动 "SCOTT"."SYS_EXPORT_TABLE_01":
scott/******** directory=DUMP_DIR dumpfile=
ticle_Detail_20W.dmp tables=range content=DATA_ONLY parallel=2
在使用 BLOCKS 方法进行估计...
理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
用 BLOCKS 方法的总估计: 128 KB
. 导出了 "SCOTT"."RANGE":"P1"
. 导出了 "SCOTT"."RANGE":"P2"
. 导出了 "SCOTT"."RANGE":"P3"
成功加载/卸载了主表 "SCOTT"."SYS_EXPORT_TABLE_01"
****************************************************************************
OTT.SYS_EXPORT_TABLE_01 的转储文件集为:
C:\BAK\DUMP\ARTICLE_DETAIL_20W.DMP
业 "SCOTT"."SYS_EXPORT_TABLE_01" 已于 22:04:54 成功完成
SQL& conn system/oracle
SQL& create table range (a int,b varchar2(30));
表已创建。
&impdp scott/tiger
directory=DUMP_DIR
remap_schema=scott:system
dumpfile=Article_Detail_20W.dmp table_exists_action=APPEND
parallel=2
Import: Release 10.2.0.1.0 - Production on 星期六, 03 4月, :42
Copyright (c) , Oracle.
All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功加载/卸载了主表 "SCOTT"."SYS_IMPORT_FULL_01"
启动 "SCOTT"."SYS_IMPORT_FULL_01":
scott/******** directory=DUMP_DIR remap_sche
ma=scott:system dumpfile=Article_Detail_20W.dmp table_exists_action=APPEND paral
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
. . 导入了 "SYSTEM"."RANGE":"P1"
. . 导入了 "SYSTEM"."RANGE":"P2"
. . 导入了 "SYSTEM"."RANGE":"P3"
作业 "SCOTT"."SYS_IMPORT_FULL_01" 已于 22:28:51 成功完成
SQL& select *
---------- ------------------------------
liuzhaomin
浏览: 124543 次
来自: 北京
赞。解决了我的问题。
(window.slotbydup=window.slotbydup || []).push({
id: '4773203',
container: s,
size: '200,200',
display: 'inlay-fix'使用sys as sysdba权限进行EXP/IMP与其它用户稍有不同，详细内容如下（摘自metalink）
Applies to:
Oracle Server - Enterprise Edition - Version: <span style="color: #.1.<span style="color: #.0 to <span style="color: #.2.<span style="color: #.0
Oracle Server - Personal Edition - Version: <span style="color: #.1.<span style="color: #.0 to <span style="color: #.2.<span style="color: #.0
Oracle Server - Standard Edition - Version: <span style="color: #.1.<span style="color: #.0 to <span style="color: #.2.<span style="color: #.0
Information in this document applies to any platform.
This document demonstrates how to connect AS SYSDBA when starting an export or import.
Incorrect usage of single or double quotes can result in errors such as:
LRM-<span style="color: #108: invalid positional parameter value 'as'
EXP-<span style="color: #019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-<span style="color: #000: Export terminated unsuccessfully
LRM-<span style="color: #108: invalid positional parameter value 'sysdba'
LRM-<span style="color: #108: Message <span style="color: #8 No message file for product=ORACORE, facility=LRM
SYSDBA is used internally in the Oracle database and has specialized functions. Its behavior is not the same as for generalized users. For example, the SYS user cannot do a transaction level consisent read (read-only transaction). Queries by SYS will return changes made during the transaction even if SYS has set the transaction to be READ ONLY.
Therefore export parameters like CONSISTENT, OBJECT_CONSISTENT, FLASHBACK_SCN, and FLASHBACK_TIME cannot be used.
Starting with Oracle10g, the export shows a warning that the export is not consistent when the export is started with CONSISTENT=Y and connects to the database with the user SYS (or as SYSDBA):
EXP-<span style="color: #105: parameter CONSISTENT is not supported for this user
Note that Oracle automatically provides read consistency to a query so that all the data that the query sees comes from a single point in time (statement-level read consistency). For export this means that the export of table data is consistent. However, if a table contains nested tables, the outer table and each inner table are exported as separate transactions. And if a table is partitioned, each partition is exported as a separate transaction. If a nested table or a partitioned table was updated during the export, the data that is exported while connected as the SYS schema could be inconsistent.
Typically, there is no need to invoke Export or Import as SYSDBA, except in the following situations:
- at the request of Orac
- when exporting a transportable tablespace set with the old-style export utility (Oracle9i and Oracle8i);
- when importing a transportable tablespace set with the old-style import utility (Oracle10g, Oracle9i, and Oracle8i).
The examples below are based on:
- the export of table emp, owned by the demo schema scott.
- schema SYS with the password: change_on_install.
- alias 'instance' that is specified in the tnsnames.ora file and used for a connect to the database.
To invoke Export or Import as SYSDBA, use the following syntax (this syntax is similar when invoking import and the syntax has not changed with the new Oracle10g Export DataPump and Import DataPump utilities):
<span style="color: #. Command line.
Enclose the connect string with a single quote character:
exp 'sys/change_on_install@instance as sysdba' tables=scott.emp
Unix (you need to 'escape' the single quote):
exp \'sys/change_on_install@instance as sysdba\' tables=scott.emp
VMS (use [double_quote][single_quote]...[single_quote][double_quote]):
exp "'sys/change_on_install@instance as sysdba'" tables=scott.emp
Note that this VMS syntax is also a valid syntax on Unix and on Windows.
<span style="color: #. Interactive
Do not specify any connect string on the command line, so you will be prompted to enter it. E.g.:
% exp tables=scott.emp
Export: Release <span style="color: #.1.<span style="color: #.2.<span style="color: # - Production on Fri Jun <span style="color: # <span style="color: #:<span style="color: #:<span style="color: # <span style="color: #04
Copyright (c) <span style="color: #82, <span style="color: #04, Oracle. All rights reserved.
Username: sys/change_on_install@instance as sysdba
Connected to: Oracle Database 10g Enterprise Edition Release <span style="color: #.1.<span style="color: #.2.<span style="color: # - 64bit Production
<span style="color: #. Parameter file.
You can also specify the username in the parameter file. In this situation, you have to enclose the connect string with a double quote character. However, to prevent possible security breaches we advice you to stop using the USERID parameter in a parameter file.
Contents of file exp.par:
USERID="sys/change_on_install@instance as sysdba"
TABLES=scott.emp
Run export with:
exp parfile=exp.par
<span style="color: #. If you have setup operating system authentication, it is not necessary to specify the SYS schema name, and password. E.g: exp "'/@instance as sysdba'" tables=scott.emp
<span style="color: #. In addition, if you have set the environment variable TWO_TASK (on Unix) or LOCAL (on Windows) or on the server where the database is installed you have set ORACLE_HOME and ORACLE_SID, it is not necessary to specify the @instance. E.g: exp "'/ as sysdba'" tables=scott.emp
<span style="color: #. The export parameters FLASHBACK_SCN and FLASHBACK_TIME cannot be used if the user that invoked the export is connected AS SYSDBA.
<span style="color: #. Known issues:
Bug <span style="color: #16035 "EXPORT FAILED WITH ORA-1031 WHEN LOGIN AS SYSDBA" (not a public fixed in <span style="color: #.1.<span style="color: #.3 and higher)
Bug <span style="color: #36288 "ORA-1925 OCCURS WHEN IMPORTING AS SYS ACCOUNT"
Bug <span style="color: #96947 "EXP DID NOT RAISE ERROR WHEN SYSDBA EXPORTS WITH CONSISTENT=Y" (not a public fixed in Oracle10g and higher)
References
Bug <span style="color: #36288 - Ora-<span style="color: #25 Occurs When Importing As Sys Account
Note <span style="color: #2269.1 - How to set Unix env. variable TWO_TASK and Windows NT counterpart, LOCAL
Note <span style="color: #0332.1 - Export / Import Connecting "AS SYSDBA" Fails with LRM-<span style="color: #108 and EXP-<span style="color: #019
Note <span style="color: #4334.1 - Parameters FLASHBACK_SCN And FLASHBACK_TIME: Point In Time Export
Note <span style="color: #8482.1 - Schema's CTXSYS, MDSYS and ORDSYS are Not Exported
Note <span style="color: #7606.1 - How to Prevent EXP-<span style="color: #079 or EXP-<span style="color: #080 Warning (Data in Table xxx is Protected) During Export
阅读(...) 评论()