注：需要打开监听
1.传统的导入导出exp/imp
传统的导入导出程序是exp/imp，用于实施数据库的逻辑备份和恢复
导出程序exp将数据库的对象定义和数据被分到一个二进制文件中
导入程序imp将二进制中的对象定义和数据导入到数据库中
导入导出程序特点：
- 1）按时间保存
- 2）允许导出指定表，并重新导入新的数据库中
- 3）可以把数据库迁移到另外一台异构服务器上
- 4）在2个不同版本的oracle之间的数据传输数据
- 5）在联机状态下进行备份和恢复
- 6）可以重新组织表的存储结构，减少链接及磁盘碎片
导入导出数据库对象的四种模式：
- 1）数据库模式
- 2）表空间模式
- 3）用户模式
- 4）表模式
1.1导入导出表
create table emp1 as select * from emp;
create table dept1 as select * from dept;
$exp scott/scott@single06 file=/home/oracle/empdept1.dmp tables=emp1,dept1
drop table emp1 purge;
drop table dept1 purge;
$ imp scott/scott@single06 file=/home/oracle/empdept1.dmp
system用户导出导入scott表
导出：$exp 'system/oracle@single06' file=/u01/app/sysempdept.dmp tables=scott.dept1,scott.emp1
导入：$imp 'system/oracle@single06' file=/u01/app/sysempdept.dmp FROMUSER=SCOTT（此处大小写不敏感）
1.2导入导出用户
当用户scott
导出scott用户数据
[oracle@single06 ~]$ exp scott/scott@single06 file=/home/oracle/scott.dmp owner=scott
Export: Release 11.2.0.4.0 - Production on Fri Oct 15 16:13:21 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table A 4 rows exported
. . exporting table B 3 rows exported
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table DEPT1 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table EMP1 14 rows exported
. . exporting table MLOG$_DEPT 0 rows exported
. . exporting table MLOG$_REMOTE_PERSON01 0 rows exported
. . exporting table REMOTE_PERSON01 110000 rows exported
. . exporting table RUPD$_DEPT
. . exporting table RUPD$_REMOTE_PERSON01
. . exporting table SALGRADE 5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
[oracle@single06 ~]$
删除scott用户及数据并重建scott用户
SQL> drop user scott cascade;
SQL> grant connect,resource to scott identified by scott;
重新导入scott用户数据
[oracle@single06 ~]$ imp scott/scott@single06 file=/home/oracle/scott.dmp
Import: Release 11.2.0.4.0 - Production on Fri Oct 15 16:15:28 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table "A" 4 rows imported
. . importing table "B" 3 rows imported
. . importing table "BONUS" 0 rows imported
. . importing table "DEPT" 4 rows imported
. . importing table "DEPT1" 4 rows imported
. . importing table "EMP" 14 rows imported
. . importing table "EMP1" 14 rows imported
. . importing table "MLOG$_DEPT" 0 rows imported
. . importing table "MLOG$_REMOTE_PERSON01" 0 rows imported
. . importing table "REMOTE_PERSON01" 110000 rows imported
. . importing table "SALGRADE" 5 rows imported
About to enable constraints...
Import terminated successfully without warnings.
或者使用system用户
[oracle@single06 ~]$ imp 'system/oracle@single06' file=/home/oracle/scott.dmp fromuser=scott touser=scott
Import: Release 11.2.0.4.0 - Production on Fri Oct 15 16:31:45 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by SCOTT, not by you
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table "A" 4 rows imported
. . importing table "B" 3 rows imported
. . importing table "BONUS" 0 rows imported
. . importing table "DEPT" 4 rows imported
. . importing table "DEPT1" 4 rows imported
. . importing table "EMP" 14 rows imported
. . importing table "EMP1" 14 rows imported
. . importing table "MLOG$_DEPT" 0 rows imported
. . importing table "MLOG$_REMOTE_PERSON01" 0 rows imported
. . importing table "REMOTE_PERSON01" 110000 rows imported
. . importing table "SALGRADE" 5 rows imported
About to enable constraints...
Import terminated successfully without warnings.
1.3 导入导出空间表
引入导入导出可传输表空间技术（只导元数据），使表空间迁移更加快速高效；使用tcp/ip传输数据文件，速度更快
1）在sys下创建tb1
SQL> create tablespace tb1 datafile '/u01/app/oracle/oradata/single06/mytb1.dbf' size 5m;
在scott用户下创建属于tb1的表’测试‘
SQL> create table 测试 (姓名 char(10),年龄 int) tablespace tb1;
SQL> insert into 测试 values('张三',20);
SQL> insert into 测试 values('李四',18);
2）sys用户下让表处于只读状态：
SQL> alter tablespace tb1 read only;
导出数据 用exp导出tb1的元数据
exp \'sys/oracle as sysdba\' tablespaces=tb1 transport_tablespace=y file=/home/oracle/tb1.dmp
[oracle@single06 single06]$ exp \'sys/oracle as sysdba\' tablespaces=tb1 transport_tablespace=y file=/home/oracle/tb1.dmp
Export: Release 11.2.0.4.0 - Production on Fri Oct 15 16:45:27 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TB1 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table 测试
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
注：
需要\'sys/oracle as sysdba\'来转义，否则将报错
3）导出tb1.dmp，和mytb1.dbf到single07虚拟机中
4）执行导入
[oracle@single07 admin]$ imp userid=\'sys/oracle@single07 as sysdba\' tablespaces=tb1 file=/home/oracle/tb1.dmp transport_tablespace=y datafiles=/u01/app/oracle/oradata/single06/mytb1.dbf
Import: Release 19.0.0.0.0 - Production on Mon Oct 18 10:09:00 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
IMP-00403:
Warning: This import generated a separate SQL file "import_sys" which contains DDL that failed due to a privilege issue.
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table "测试"
. importing SYS's objects into SYS
Import terminated successfully with warnings.
imp userid=\'sys/oracle@single07 as sysdba\' tablespaces=tb1 file=/home/oracle/tb1.dmp transport_tablespace=y datafiles=/u01/app/oracle/oradata/single06/mytb1.dbf
//datafiles为数据文件存放的位置，file为emp存放的位置
5）重设读写模式
SQL> alter tablespace tb1 read write; //两边均需执行
6）验证
scott:
- select * from tab;
- select * from 测试;
- select table_name,tablespace_name from user_tables;
说明：
可传输表空间需要满足几个前提条件
1）源库和目标库字符集需一致，若不一致可通过转换环境变量NLS_LANG解决
2）字符序有大端和小端之分，可以通过
select * from v$transportable_platform
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ ---------------
########## Solaris[tm] OE (32-bit) Big
########## Solaris[tm] OE (64-bit) Big
########## Microsoft Windows IA (32-bit) Little
########## Linux IA (32-bit) Little
########## AIX-Based Systems (64-bit) Big
########## HP-UX (64-bit) Big
########## HP Tru64 UNIX Little
########## HP-UX IA (64-bit) Big
########## Linux IA (64-bit) Little
########## HP Open VMS Little
########## Microsoft Windows IA (64-bit) Little
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ ---------------
########## IBM zSeries Based Linux Big
########## Linux x86 64-bit Little
########## Apple Mac OS Big
########## Microsoft Windows x86 64-bit Little
########## Solaris Operating System (x86) Little
########## IBM Power Based Linux Big
########## HP IA Open VMS Little
########## Solaris Operating System (x86- Little
64)
########## Apple Mac OS (x86-64) Little
20 rows selected.
查看，若不一致可使用rman转换
3）compatible 10.0.0.或更高
4）迁移的表空间需要自包含（self contained，当前表空间中的对象不依赖于该表空间之外的对象）
- 检查表空间中是否有自包含可使用如下：
- execute dbms_tts.transport_set_check('表空间名',true)；
- 然后查看结果
- select * from transport_set_violations;
5)将表空间设置成只读模式
1.4导出整个数据库的对象
exp \'sys/oracle@single06 as sysdba\' file=/home/oracle/full.emp full=y //太慢，真是情况下不采用这种情况
[oracle@single06 ~]$ exp \'sys/oracle@single06 as sysdba\' file=/home/oracle/full.emp full=y
Export: Release 11.2.0.4.0 - Production on Mon Oct 18 10:28:16 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table DEF$_AQCALL 0 rows exported
. . exporting table DEF$_AQERROR 0 rows exported
. . exporting table DEF$_CALLDEST 0 rows exported
. . exporting table DEF$_DEFAULTDEST 0 rows exported
. . exporting table DEF$_DESTINATION 1 rows exported
. . exporting table DEF$_ERROR 0 rows exported
. . exporting table DEF$_LOB 0 rows exported
. . exporting table DEF$_ORIGIN 0 rows exported
. . exporting table DEF$_PROPAGATOR 0 rows exported
. . exporting table DEF$_PUSHED_TRANSACTIONS 0 rows exported
. . exporting table MVIEW$_ADV_INDEX 0 rows exported
. . exporting table MVIEW$_ADV_OWB
. . exporting table MVIEW$_ADV_PARTITION 0 rows exported
. . exporting table OL$
. . exporting table OL$HINTS
. . exporting table OL$NODES
. . exporting table REPCAT$_AUDIT_ATTRIBUTE 2 rows exported
. . exporting table REPCAT$_AUDIT_COLUMN 0 rows exported
. . exporting table REPCAT$_COLUMN_GROUP 0 rows exported
. . exporting table REPCAT$_CONFLICT 0 rows exported
. . exporting table REPCAT$_DDL 0 rows exported
. . exporting table REPCAT$_EXCEPTIONS 0 rows exported
. . exporting table REPCAT$_EXTENSION 0 rows exported
. . exporting table REPCAT$_FLAVORS 0 rows exported
. . exporting table REPCAT$_FLAVOR_OBJECTS 0 rows exported
. . exporting table REPCAT$_GENERATED 0 rows exported
. . exporting table REPCAT$_GROUPED_COLUMN 0 rows exported
. . exporting table REPCAT$_INSTANTIATION_DDL 0 rows exported
. . exporting table REPCAT$_KEY_COLUMNS 0 rows exported
. . exporting table REPCAT$_OBJECT_PARMS 0 rows exported
. . exporting table REPCAT$_OBJECT_TYPES 28 rows exported
. . exporting table REPCAT$_PARAMETER_COLUMN 0 rows exported
. . exporting table REPCAT$_PRIORITY 0 rows exported
. . exporting table REPCAT$_PRIORITY_GROUP 0 rows exported
. . exporting table REPCAT$_REFRESH_TEMPLATES 0 rows exported
. . exporting table REPCAT$_REPCAT 0 rows exported
. . exporting table REPCAT$_REPCATLOG 0 rows exported
. . exporting table REPCAT$_REPCOLUMN 0 rows exported
. . exporting table REPCAT$_REPGROUP_PRIVS 0 rows exported
. . exporting table REPCAT$_REPOBJECT 0 rows exported
. . exporting table REPCAT$_REPPROP 0 rows exported
. . exporting table REPCAT$_REPSCHEMA 0 rows exported
. . exporting table REPCAT$_RESOLUTION 0 rows exported
. . exporting table REPCAT$_RESOLUTION_METHOD 19 rows exported
. . exporting table REPCAT$_RESOLUTION_STATISTICS 0 rows exported
. . exporting table REPCAT$_RESOL_STATS_CONTROL 0 rows exported
. . exporting table REPCAT$_RUNTIME_PARMS 0 rows exported
. . exporting table REPCAT$_SITES_NEW 0 rows exported
. . exporting table REPCAT$_SITE_OBJECTS 0 rows exported
. . exporting table REPCAT$_SNAPGROUP 0 rows exported
. . exporting table REPCAT$_TEMPLATE_OBJECTS 0 rows exported
. . exporting table REPCAT$_TEMPLATE_PARMS 0 rows exported
. . exporting table REPCAT$_TEMPLATE_REFGROUPS 0 rows exported
. . exporting table REPCAT$_TEMPLATE_SITES 0 rows exported
. . exporting table REPCAT$_TEMPLATE_STATUS 3 rows exported
. . exporting table REPCAT$_TEMPLATE_TARGETS 0 rows exported
. . exporting table REPCAT$_TEMPLATE_TYPES 2 rows exported
. . exporting table REPCAT$_USER_AUTHORIZATIONS 0 rows exported
. . exporting table REPCAT$_USER_PARM_VALUES 0 rows exported
. . exporting table SQLPLUS_PRODUCT_PROFILE 0 rows exported
. about to export OUTLN's tables via Conventional Path ...
. . exporting table OL$ 0 rows exported
. . exporting table OL$HINTS 0 rows exported
. . exporting table OL$NODES 0 rows exported
. about to export ORDDATA's tables via Conventional Path ...
. . exporting table ORDDCM_ANON_ACTION_TYPES 4 rows exported
. . exporting table ORDDCM_ANON_ATTRS 37 rows exported
. . exporting table ORDDCM_ANON_ATTRS_TMP
. . exporting table ORDDCM_ANON_ATTRS_WRK 0 rows exported
. . exporting table ORDDCM_ANON_RULES 3 rows exported
. . exporting table ORDDCM_ANON_RULES_TMP
. . exporting table ORDDCM_ANON_RULES_WRK 0 rows exported
. . exporting table ORDDCM_ANON_RULE_TYPES 3 rows exported
. . exporting table ORDDCM_CT_ACTION 7 rows exported
. . exporting table ORDDCM_CT_ACTION_TMP
. . exporting table ORDDCM_CT_ACTION_WRK 0 rows exported
. . exporting table ORDDCM_CT_DAREFS 72 rows exported
. . exporting table ORDDCM_CT_DAREFS_TMP
. . exporting table ORDDCM_CT_DAREFS_WRK 0 rows exported
. . exporting table ORDDCM_CT_LOCATORPATHS 95 rows exported
. . exporting table ORDDCM_CT_LOCATORPATHS_TMP
. . exporting table ORDDCM_CT_LOCATORPATHS_WRK 0 rows exported
. . exporting table ORDDCM_CT_MACRO_DEP 1 rows exported
. . exporting table ORDDCM_CT_MACRO_DEP_WRK 0 rows exported
. . exporting table ORDDCM_CT_MACRO_PAR 2 rows exported
. . exporting table ORDDCM_CT_MACRO_PAR_WRK 0 rows exported
. . exporting table ORDDCM_CT_PRED 61 rows exported
. . exporting table ORDDCM_CT_PRED_OPRD
EXP-00107: Feature (BINARY XML) of column OPERAND in table ORDDATA.ORDDCM_CT_PRED_OPRD is not supported. The table will not be exported.
. . exporting table ORDDCM_CT_PRED_OPRD_TMP
EXP-00107: Feature (BINARY XML) of column OPERAND in table ORDDATA.ORDDCM_CT_PRED_OPRD_TMP is not supported. The table will not be exported.
. . exporting table ORDDCM_CT_PRED_OPRD_WRK
EXP-00107: Feature (BINARY XML) of column OPERAND in table ORDDATA.ORDDCM_CT_PRED_OPRD_WRK is not supported. The table will not be exported.
. . exporting table ORDDCM_CT_PRED_PAR 3 rows exported
. . exporting table ORDDCM_CT_PRED_PAR_TMP
. . exporting table ORDDCM_CT_PRED_PAR_WRK 0 rows exported
. . exporting table ORDDCM_CT_PRED_SET 9 rows exported
. . exporting table ORDDCM_CT_PRED_SET_TMP
. . exporting table ORDDCM_CT_PRED_SET_WRK 0 rows exported
. . exporting table ORDDCM_CT_PRED_TMP
. . exporting table ORDDCM_CT_PRED_WRK 0 rows exported
. . exporting table ORDDCM_CT_VLD_MSG 0 rows exported
. . exporting table ORDDCM_DATA_MODEL 1 rows exported
. . exporting table ORDDCM_DATA_MODEL_WRK 0 rows exported
. . exporting table ORDDCM_DICT_ATTRS 2418 rows exported
. . exporting table ORDDCM_DICT_ATTRS_TMP
. . exporting table ORDDCM_DICT_ATTRS_WRK 0 rows exported
. . exporting table ORDDCM_DOCS
EXP-00107: Feature (BINARY XML) of column DOC_CONTENT in table ORDDATA.ORDDCM_DOCS is not supported. The table will not be exported.
. . exporting table ORDDCM_DOCS_TMP
EXP-00107: Feature (BINARY XML) of column DOC_CONTENT in table ORDDATA.ORDDCM_DOCS_TMP is not supported. The table will not be exported.
. . exporting table ORDDCM_DOCS_WRK
EXP-00107: Feature (BINARY XML) of column DOC_CONTENT in table ORDDATA.ORDDCM_DOCS_WRK is not supported. The table will not be exported.
. . exporting table ORDDCM_DOC_REFS 7 rows exported
. . exporting table ORDDCM_DOC_REFS_TMP
. . exporting table ORDDCM_DOC_REFS_WRK 0 rows exported
. . exporting table ORDDCM_DOC_TYPES 8 rows exported
. . exporting table ORDDCM_INSTALL_DOCS 9 rows exported
. . exporting table ORDDCM_INTERNAL_TAGS 42 rows exported
. . exporting table ORDDCM_MAPPED_PATHS 0 rows exported
. . exporting table ORDDCM_MAPPED_PATHS_TMP
. . exporting table ORDDCM_MAPPED_PATHS_WRK 0 rows exported
. . exporting table ORDDCM_MAPPING_DOCS
EXP-00107: Feature (BINARY XML) of column XSLT in table ORDDATA.ORDDCM_MAPPING_DOCS is not supported. The table will not be exported.
. . exporting table ORDDCM_MAPPING_DOCS_TMP
EXP-00107: Feature (BINARY XML) of column XSLT in table ORDDATA.ORDDCM_MAPPING_DOCS_TMP is not supported. The table will not be exported.
. . exporting table ORDDCM_MAPPING_DOCS_WRK
EXP-00107: Feature (BINARY XML) of column XSLT in table ORDDATA.ORDDCM_MAPPING_DOCS_WRK is not supported. The table will not be exported.
. . exporting table ORDDCM_PREFS_LOOKUP 13 rows exported
. . exporting table ORDDCM_PREFS_DEF_VALUES_TAB 7 rows exported
. . exporting table ORDDCM_PREFS_VALID_VALUES_TAB 33 rows exported
. . exporting table ORDDCM_PRV_ATTRS 3 rows exported
. . exporting table ORDDCM_PRV_ATTRS_TMP
. . exporting table ORDDCM_PRV_ATTRS_WRK 0 rows exported
. . exporting table ORDDCM_RT_PREF_PARAMS 13 rows exported
. . exporting table ORDDCM_RT_PREF_PARAMS_TMP
. . exporting table ORDDCM_RT_PREF_PARAMS_WRK 0 rows exported
. . exporting table ORDDCM_STD_ATTRS 2415 rows exported
. . exporting table ORDDCM_STD_ATTRS_TMP
. . exporting table ORDDCM_STD_ATTRS_WRK 0 rows exported
. . exporting table ORDDCM_STORED_TAGS 0 rows exported
. . exporting table ORDDCM_STORED_TAGS_TMP
. . exporting table ORDDCM_STORED_TAGS_WRK 0 rows exported
. . exporting table ORDDCM_UID_DEFS 245 rows exported
. . exporting table ORDDCM_UID_DEFS_TMP
. . exporting table ORDDCM_UID_DEFS_WRK 0 rows exported
. . exporting table ORDDCM_VR_DT_MAP 32 rows exported
. about to export OLAPSYS's tables via Conventional Path ...
. . exporting table XML_LOAD_LOG 0 rows exported
. . exporting table XML_LOAD_RECORDS 0 rows exported
. about to export MDDATA's tables via Conventional Path ...
. about to export SPATIAL_WFS_ADMIN_USR's tables via Conventional Path ...
. about to export SPATIAL_CSW_ADMIN_USR's tables via Conventional Path ...
. about to export OWBSYS's tables via Conventional Path ...
. . exporting table OWBRTPS 0 rows exported
. about to export OWBSYS_AUDIT's tables via Conventional Path ...
. about to export OE's tables via Conventional Path ...
. . exporting table CATEGORIES_TAB 22 rows exported
. . exporting table PRODUCT_REF_LIST_NESTEDTAB 288 rows exported
. . exporting table SUBCATEGORY_REF_LIST_NESTEDTAB 21 rows exported
. . exporting table CUSTOMERS 319 rows exported
. . exporting table INVENTORIES 1112 rows exported
. . exporting table ORDERS 105 rows exported
. . exporting table ORDER_ITEMS 665 rows exported
. . exporting table PRODUCT_DESCRIPTIONS 8640 rows exported
. . exporting table PRODUCT_INFORMATION 288 rows exported
. . exporting table PROMOTIONS 2 rows exported
. . exporting table PURCHASEORDER 132 rows exported
. . exporting table WAREHOUSES
EXP-00107: Feature (BINARY XML) of column WAREHOUSE_SPEC in table OE.WAREHOUSES is not supported. The table will not be exported.
. about to export IX's tables via Conventional Path ...
. . exporting table AQ$_ORDERS_QUEUETABLE_G 0 rows exported
. . exporting table AQ$_ORDERS_QUEUETABLE_H 0 rows exported
. . exporting table AQ$_ORDERS_QUEUETABLE_I 0 rows exported
. . exporting table AQ$_ORDERS_QUEUETABLE_L 0 rows exported
. . exporting table AQ$_ORDERS_QUEUETABLE_S 4 rows exported
. . exporting table AQ$_ORDERS_QUEUETABLE_T 0 rows exported
. . exporting table AQ$_STREAMS_QUEUE_TABLE_C 0 rows exported
. . exporting table AQ$_STREAMS_QUEUE_TABLE_G 0 rows exported
. . exporting table AQ$_STREAMS_QUEUE_TABLE_H 0 rows exported
. . exporting table AQ$_STREAMS_QUEUE_TABLE_I 0 rows exported
. . exporting table AQ$_STREAMS_QUEUE_TABLE_L 0 rows exported
. . exporting table AQ$_STREAMS_QUEUE_TABLE_S 1 rows exported
. . exporting table AQ$_STREAMS_QUEUE_TABLE_T 0 rows exported
. . exporting table ORDERS_QUEUETABLE 0 rows exported
. . exporting table STREAMS_QUEUE_TABLE 0 rows exported
. about to export SH's tables via Conventional Path ...
. . exporting table CAL_MONTH_SALES_MV 48 rows exported
. . exporting table CHANNELS 5 rows exported
. . exporting table COSTS
. . exporting partition COSTS_1995 0 rows exported
. . exporting partition COSTS_1996 0 rows exported
. . exporting partition COSTS_H1_1997 0 rows exported
. . exporting partition COSTS_H2_1997 0 rows exported
. . exporting partition COSTS_Q1_1998 4411 rows exported
. . exporting partition COSTS_Q2_1998 2397 rows exported
. . exporting partition COSTS_Q3_1998 4129 rows exported
. . exporting partition COSTS_Q4_1998 4577 rows exported
. . exporting partition COSTS_Q1_1999 5884 rows exported
. . exporting partition COSTS_Q2_1999 4179 rows exported
. . exporting partition COSTS_Q3_1999 4336 rows exported
. . exporting partition COSTS_Q4_1999 5060 rows exported
. . exporting partition COSTS_Q1_2000 3772 rows exported
. . exporting partition COSTS_Q2_2000 3715 rows exported
. . exporting partition COSTS_Q3_2000 4798 rows exported
. . exporting partition COSTS_Q4_2000 5088 rows exported
. . exporting partition COSTS_Q1_2001 7328 rows exported
. . exporting partition COSTS_Q2_2001 5882 rows exported
. . exporting partition COSTS_Q3_2001 7545 rows exported
. . exporting partition COSTS_Q4_2001 9011 rows exported
. . exporting partition COSTS_Q1_2002 0 rows exported
. . exporting partition COSTS_Q2_2002 0 rows exported
. . exporting partition COSTS_Q3_2002 0 rows exported
. . exporting partition COSTS_Q4_2002 0 rows exported
. . exporting partition COSTS_Q1_2003 0 rows exported
. . exporting partition COSTS_Q2_2003 0 rows exported
. . exporting partition COSTS_Q3_2003 0 rows exported
. . exporting partition COSTS_Q4_2003 0 rows exported
. . exporting table COUNTRIES 23 rows exported
. . exporting table CUSTOMERS 55500 rows exported
. . exporting table DIMENSION_EXCEPTIONS 0 rows exported
. . exporting table FWEEK_PSCAT_SALES_MV 11266 rows exported
. . exporting table PRODUCTS 72 rows exported
. . exporting table PROMOTIONS 503 rows exported
. . exporting table SALES
. . exporting partition SALES_1995 0 rows exported
. . exporting partition SALES_1996 0 rows exported
. . exporting partition SALES_H1_1997 0 rows exported
. . exporting partition SALES_H2_1997 0 rows exported
. . exporting partition SALES_Q1_1998 43687 rows exported
. . exporting partition SALES_Q2_1998 35758 rows exported
. . exporting partition SALES_Q3_1998 50515 rows exported
. . exporting partition SALES_Q4_1998 48874 rows exported
. . exporting partition SALES_Q1_1999 64186 rows exported
. . exporting partition SALES_Q2_1999 54233 rows exported
. . exporting partition SALES_Q3_1999 67138 rows exported
. . exporting partition SALES_Q4_1999 62388 rows exported
. . exporting partition SALES_Q1_2000 62197 rows exported
. . exporting partition SALES_Q2_2000 55515 rows exported
. . exporting partition SALES_Q3_2000 58950 rows exported
. . exporting partition SALES_Q4_2000 55984 rows exported
. . exporting partition SALES_Q1_2001 60608 rows exported
. . exporting partition SALES_Q2_2001 63292 rows exported
. . exporting partition SALES_Q3_2001 65769 rows exported
. . exporting partition SALES_Q4_2001 69749 rows exported
. . exporting partition SALES_Q1_2002 0 rows exported
. . exporting partition SALES_Q2_2002 0 rows exported
. . exporting partition SALES_Q3_2002 0 rows exported
. . exporting partition SALES_Q4_2002 0 rows exported
. . exporting partition SALES_Q1_2003 0 rows exported
. . exporting partition SALES_Q2_2003 0 rows exported
. . exporting partition SALES_Q3_2003 0 rows exported
. . exporting partition SALES_Q4_2003 0 rows exported
. . exporting table SALES_TRANSACTIONS_EXT
. . exporting table SUPPLEMENTARY_DEMOGRAPHICS 4500 rows exported
. . exporting table TIMES 1826 rows exported
. about to export PM's tables via Conventional Path ...
. . exporting table ONLINE_MEDIA 9 rows exported
. . exporting table PRINT_MEDIA 4 rows exported
. . exporting table TEXTDOCS_NESTEDTAB 12 rows exported
. about to export BI's tables via Conventional Path ...
. about to export SCOTT's tables via Conventional Path ...
. . exporting table A 4 rows exported
. . exporting table B 3 rows exported
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table DEPT1 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table EMP1 14 rows exported
. . exporting table MLOG$_DEPT 0 rows exported
. . exporting table MLOG$_REMOTE_PERSON01 0 rows exported
. . exporting table REMOTE_PERSON01 110000 rows exported
. . exporting table RUPD$_DEPT
. . exporting table RUPD$_REMOTE_PERSON01
. . exporting table SALGRADE 5 rows exported
. . exporting table 测试 2 rows exported
. about to export XP's tables via Conventional Path ...
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.
2.数据泵
2.1数据泵优点
- 1）改进性能（较传统的exp/imp速度提高1~2个数量级10倍~。导出东西在服务器，不在客户端，比exp快很多）
- 2）重启作业
- 3）并行执行
- 4）关联运行作业能力
- 5）估算空间需求能力
- 6）操作网络方式
2.2数据泵组成部分
数据泵核心部分程序包: dbms_datapump
提供元数据程序包：dbms_metadata
命令行客户机（实用程序）：expdp，impdp //客户端执行，但是导出在服务器端
2.3数据泵文件
转储文件：包含对象数据 类似于dmp文件
日志文件：记录操作信息和结果
sql文件：将导入（impdp）作业中的DDL语句写入SQLFILE指定的参数文件中
2.4数据泵的目录及位置文件
使用sys或system用户完成数据泵导入导出时，可以使用缺省目录DATA_PUMP_DIR
select * from dba_directories;
若设置了环境变量ORACLE_BASE，则缺省位置是：
$ORACLE_BASE/admin/database_name/dpdump
否则是：
$ORACLE_HOME/admin/database_name/dpdump
3.数据泵的两种操作方式
3.1使用expdp和impdp客户端实用程序
1）建存放mt表的物理目录
mkdir -p /u01/app/dump
2）server端用sys建立目录对象
create directory MY_DIR as '/u01/app/dump';
3）为scott授予目录权限
grant read,write on directory MY_DIR to scott;
4）导出scott的emp dept表，导出过程中在server端有MT表出现SYS_EXPORT_TABLE_01,到处完成后MT表自动消失
expdp scott/scott@single06 directory=MY_DIR dumpfile=expdp_scott1.dmp tables=emp,dept
[oracle@single06 ~]$ expdp scott/scott@single06 directory=MY_DIR dumpfile=expdp_scott1.dmp tables=emp,dept
Export: Release 11.2.0.4.0 - Production on Mon Oct 18 11:50:58 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/********@single06 directory=MY_DIR dumpfile=expdp_scott1.dmp tables=emp,dept
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/POST_TABLE_ACTION
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/app/dump/expdp_scott1.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Mon Oct 18 11:51:03 2021 elapsed 0 00:00:04
[oracle@single06 ~]$ cd /u01/app/dump
[oracle@single06 dump]$ ll
total 168
-rw-r----- 1 oracle oinstall 167936 Oct 18 11:51 expdp_scott1.dmp
-rw-r--r-- 1 oracle oinstall 1527 Oct 18 11:51 export.log
5）导入expdp实验 删掉原来的emp，dept表
drop table emp purge
drop table dept purge;
[oracle@single06 dump]$ impdp scott/scott@single06 directory=MY_DIR dumpfile=expdp_scott1.dmp
Import: Release 11.2.0.4.0 - Production on Mon Oct 18 11:53:56 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01": scott/********@single06 directory=MY_DIR dumpfile=expdp_scott1.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT" 5.929 KB 4 rows
. . imported "SCOTT"."EMP" 8.562 KB 14 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/POST_TABLE_ACTION
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at Mon Oct 18 11:53:57 2021 elapsed 0 00:00:01
6）导出scott的emp dept数据，但不导出结构
expdp scott/scott@single06 directory=MY_DIR dumpfile=expdp_scott1.dmp tables=emp,dept content=data_only reuse_dumpfiles=y
[oracle@single06 dump]$ expdp scott/scott@single06 directory=MY_DIR dumpfile=expdp_scott1.dmp tables=emp,dept content=data_only reuse_dumpfiles=y
Export: Release 11.2.0.4.0 - Production on Mon Oct 18 13:35:14 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/********@single06 directory=MY_DIR dumpfile=expdp_scott1.dmp tables=emp,dept content=data_only reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/app/dump/expdp_scott1.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Mon Oct 18 13:35:15 2021 elapsed 0 00:00:01
drop table emp purge
drop table dept purge;
注：执行上述操作后，需要在scott用户下重建emp表和dept表结构，再执行下述导入操作才能成功导入数据，否则会报错找不到该表
[oracle@single06 dump]$ impdp scott/scott@single06 directory=MY_DIR dumpfile=expdp_scott1.dmp
Import: Release 11.2.0.4.0 - Production on Mon Oct 18 14:02:33 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01": scott/********@single06 directory=MY_DIR dumpfile=expdp_scott1.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT" 5.929 KB 4 rows
. . imported "SCOTT"."EMP" 8.562 KB 14 rows
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at Mon Oct 18 14:02:35 2021 elapsed 0 00:00:01
7）导出scott用户
expdp scott/scott@single06 directory=MY_DIR dumpfile=expdp1.dmp schemas=scott
[oracle@single06 dump]$ expdp scott/scott@single06 directory=MY_DIR dumpfile=expdp1.dmp schemas=scott
Export: Release 11.2.0.4.0 - Production on Mon Oct 18 13:51:39 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********@single06 directory=MY_DIR dumpfile=expdp1.dmp schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13.37 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG
. . exported "SCOTT"."REMOTE_PERSON01" 10.90 MB 110000 rows
. . exported "SCOTT"."A" 5.882 KB 4 rows
. . exported "SCOTT"."B" 5.867 KB 3 rows
. . exported "SCOTT"."DEPT1" 5.937 KB 4 rows
. . exported "SCOTT"."EMP1" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."测试" 5.453 KB 2 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
. . exported "SCOTT"."MLOG$_REMOTE_PERSON01" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/u01/app/dump/expdp1.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Oct 18 13:51:53 2021 elapsed 0 00:00:14
删除scott用户及其所有数据后，需要重建该用户，同时授权connect ，resource ，read write on direcotory，之后再执行impdp
[oracle@single06 dump]$ impdp scott/scott@single06 directory=MY_DIR dumpfile=expdp1.dmp
Import: Release 11.2.0.4.0 - Production on Mon Oct 18 13:56:35 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01": scott/********@single06 directory=MY_DIR dumpfile=expdp1.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 "SCOTT"."REMOTE_PERSON01" 10.90 MB 110000 rows
. . imported "SCOTT"."A" 5.882 KB 4 rows
. . imported "SCOTT"."B" 5.867 KB 3 rows
. . imported "SCOTT"."DEPT1" 5.937 KB 4 rows
. . imported "SCOTT"."EMP1" 8.562 KB 14 rows
. . imported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . imported "SCOTT"."测试" 5.453 KB 2 rows
. . imported "SCOTT"."BONUS" 0 KB 0 rows
. . imported "SCOTT"."MLOG$_REMOTE_PERSON01" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at Mon Oct 18 13:56:37 2021 elapsed 0 00:00:02
3.2使用OEM操作数据泵
略（现在已经不常用，19c删除了OEM的大部分功能）
2951
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
