search
HomeDatabaseMysql TutorialEXPDP/IMPDP与EXP/IMP在不同用户和表空间之间迁移数据的实现方法

1. EXPDP/IMPDP方式 SQL create user zlm identified by zlm; User created. SQL grant connect,resource to zlm; Grant succeeded. SQL create tablespace ts_zlm datafile /u01/app/oracle/oradata/ora10g/zlm01.dbf size 100M reuse; Tablespace created.

1. EXPDP/IMPDP方式

SQL> create user zlm identified by zlm;

User created.

SQL> grant connect,resource to zlm;

Grant succeeded.

SQL> create tablespace ts_zlm datafile '/u01/app/oracle/oradata/ora10g/zlm01.dbf' size 100M reuse;

Tablespace created.

SQL> alter user zlm default tablespace ts_zlm;

User altered.

SQL> select username,default_tablespace from dba_users where username='ZLM';

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ZLM TS_ZLM

SQL> col name for a50
SQL> select name,bytes/1024/1024 from v$datafile where name like '%users01.dbf';

NAME BYTES/1024/1024
-------------------------------------------------- ---------------
/u01/app/oracle/oradata/ora10g/users01.dbf 5

SQL> set lin 120 pages 120
SQL> col username for a8
SQL> col name for a45
SQL> select a.username,a.default_tablespace,b.name from dba_users a,v$datafile b,v$tablespace c where a.default_tablespace=c.name and b.ts#=c.ts# and a.username='SCOTT';

USERNAME DEFAULT_TABLESPACE NAME
-------- ------------------------------ ---------------------------------------------
SCOTT USERS /u01/app/oracle/oradata/ora10g/users01.dbf

SQL> alter database datafile '/u01/app/oracle/oradata/ora10g/users01.dbf' resize 100M;

Database altered.

SQL> select name,bytes/1024/1024 from v$datafile where name like '%users01.dbf';

NAME BYTES/1024/1024
--------------------------------------------- ---------------
/u01/app/oracle/oradata/ora10g/users01.dbf 100

SQL> select owner,directory_name from dba_directories;

OWNER DIRECTORY_NAME
------------------------------ ------------------------------
SYS DATA_PUMP_DIR
SYS SUBDIR
SYS XMLDIR
SYS MEDIA_DIR
SYS LOG_FILE_DIR
SYS DATA_FILE_DIR
SYS WORK_DIR
SYS ADMIN_DIR

SQL> create directory zlm_pump as '/u01/expdp';

Directory created.

SQL> !mkdir /u01/expdp

SQL> alter user scott identified by tiger account unlock;

User altered.

SQL> grant connect,resource to scott;

Grant succeeded.

SQL> grant read,write on directory zlm_pump to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> create table zlm1 as select * from dba_objects;

Table created.

SQL> insert into zlm1 select * from dba_objects;

50318 rows created.

SQL> /

50318 rows created.

SQL> select count(*) from zlm1;

COUNT(*)
----------
150954

SQL> select table_name from user_tables where tablespace_name='USERS';

TABLE_NAME
------------------------------
ZLM1
SALGRADE
BONUS
EMP
DEPT

[oracle@ora10g ~]$ expdp scott/tiger parallel=2 directory=zlm_pump dumpfile=scott01.dmp,scott02.dmp tablespaces=users

Export: Release 10.2.0.1.0 - Production on Sunday, 31 August, 2014 14:35:29

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLESPACE_01": scott/******** parallel=2 directory=zlm_pump dumpfile=scott01.dmp,scott02.dmp tablespaces=users
Estimate in progress using BLOCKS method...--可以通过estimate=block(默认值)指定,还有一个是statistic
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 17.18 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."ZLM1" 14.06 MB 150954 rows
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 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/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SCOTT"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLESPACE_01 is:
/u01/expdp/scott01.dmp
/u01/expdp/scott02.dmp
Job "SCOTT"."SYS_EXPORT_TABLESPACE_01" successfully completed at 14:36:10

[oracle@ora10g ~]$ impdp zlm/zlm parallel=2 remap_schema=scott:zlm remap_tablespace=users:ts_zlm directory=zlm_pump dumpfile=scott01.dmp,scott02.dmp

Import: Release 10.2.0.1.0 - Production on Sunday, 31 August, 2014 14:41:30

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "ZLM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ZLM"."SYS_IMPORT_FULL_01": zlm/******** parallel=2 remap_schema=scott:zlm remap_tablespace=users:ts_zlm directory=zlm_pump dumpfile=scott01.dmp,scott02.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ZLM"."DEPT" 5.656 KB 4 rows
. . imported "ZLM"."EMP" 7.820 KB 14 rows
. . imported "ZLM"."SALGRADE" 5.585 KB 5 rows
. . imported "ZLM"."BONUS" 0 KB 0 rows
. . imported "ZLM"."ZLM1" 14.06 MB 150954 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
Job "ZLM"."SYS_IMPORT_FULL_01" successfully completed at 14:41:43
[oracle@ora10g ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 31 14:43:12 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn zlm/zlm
Connected.
SQL> select table_name from user_tables where tablespace_name='TS_ZLM';

TABLE_NAME
------------------------------
ZLM1
SALGRADE
BONUS
EMP
DEPT

SQL> select object_name from user_objects;

OBJECT_NAME
--------------------------------------------------------------------------------
DEPT
EMP
BONUS
SALGRADE
ZLM1
PK_DEPT
PK_EMP

SCHEMA SCOTT的表空间USERS中的全部对象已经被导入到SCHEMA ZLM的表空间,包括表和索引

2. EXP/IMP方式

SQL> conn / as sysdba
Connected.
SQL> drop user zlm cascade;

User dropped.

SQL> create user zlm identified by zlm;

User created.

SQL> grant connect,resource to zlm;

Grant succeeded.

SQL> alter user zlm default tablespace ts_zlm;

User altered.

SQL> conn zlm/zlm
Connected.
SQL> select * from cat;

no rows selected

SQL> !
[oracle@ora10g ~]$ exp scott/tiger owner=scott file=/u01/scott01.dmp

Export: Release 10.2.0.1.0 - Production on Sun Aug 31 15:42:00 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
. 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 BONUS 0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table DEPT 4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP 14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table SALGRADE 5 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table ZLM1 150954 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 with warnings.
[oracle@ora10g ~]$ imp zlm/zlm fromuser=scott touser=zlm file=/u01/exp/scott01.dmp

Import: Release 10.2.0.1.0 - Production on Sun Aug 31 15:43:01 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

IMP-00002: failed to open /u01/exp/scott01.dmp for read --路径指定错了
Import file: expdat.dmp > /u01/scott01.dmp

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. . importing table "BONUS" 0 rows imported
. . importing table "DEPT" 4 rows imported
. . importing table "EMP" 14 rows imported
. . importing table "SALGRADE" 5 rows imported
. . importing table "ZLM1" 150954 rows imported
About to enable constraints...
Import terminated successfully with warnings.
[oracle@ora10g ~]$ exit
SQL> col object_name for a15
SQL> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
--------------- -------------------
BONUS TABLE
DEPT TABLE
PK_DEPT INDEX
EMP TABLE
PK_EMP INDEX
SALGRADE TABLE
ZLM1 TABLE

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
BONUS USERS
DEPT USERS
EMP USERS
SALGRADE USERS
ZLM1 USERS

注意,用imp导入后的对象,都是存放在exp导出时的表空间的,如果要迁移到别的表空间,就要使用alter table move tablespace来实现,先通过下面的SQL拼接语句来获得批量move的语句: SQL> select 'alter table '||table_name||' MOVE TABLESPACE ts_zlm;' from user_tables;

'ALTERTABLE'||TABLE_NAME||'MOVETABLESPACETS_ZLM;'
------------------------------------------------------------------
alter table BONUS MOVE TABLESPACE ts_zlm;
alter table DEPT MOVE TABLESPACE ts_zlm;
alter table EMP MOVE TABLESPACE ts_zlm;
alter table SALGRADE MOVE TABLESPACE ts_zlm;
alter table ZLM1 MOVE TABLESPACE ts_zlm;

SQL> select 'ALTER INDEX ' ||index_name || ' REBUILD TABLESPACE ts_zlm;' from user_indexes;

'ALTERINDEX'||INDEX_NAME||'REBUILDTABLESPACETS_ZLM;'
---------------------------------------------------------------------
ALTER INDEX PK_EMP REBUILD TABLESPACE ts_zlm;
ALTER INDEX PK_DEPT REBUILD TABLESPACE ts_zlm;

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SALGRADE TS_ZLM
EMP TS_ZLM
ZLM1 TS_ZLM
DEPT TS_ZLM
BONUS TS_ZLM

SQL> select index_name,tablespace_name from user_indexes;

INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PK_EMP TS_ZLM
PK_DEPT TS_ZLM

可以看到,zlm用户imp导入的数据库对象已经全部迁移到TS_ZLM表空间去了

总结:

EXPDP/IPMDP只能在server上运行,其运行效率完全取决于磁盘I/O,而EXP/IMP不仅可以运行在server上,也可以运行在client上,所以除了磁盘I/O的制约因素,还有网络方面的因素。

EXPDP/IMPDP比EXP/IMP性能有很大的提高,其中影响最大的就是paralle,通常设置为CPU的个数,通过该参数可以并行导出,大大提高导出速度,而EXP/IMP是没有这个功能的,最多也就只有一个DIRECT=Y,使导出不用经过SELECT到SGA的BUFFER中,提高的速度是有限的,注意他们之间的语法也有不小的区别,参数不可互相替代。

EXPDP/IMPDP可以通过remap_schema和remap_tablespace直接实现不同用户和表空间之间的数据迁移;而EXP/IMP则稍微麻烦一点,需要ALTER TABLE xxx MOVE TABLESPACE xxx/ALTER INDEX xxx REBUILD TABLESPACE xxx方式来实现。move相当于把表再重建一次,可以修改storage参数(如initial、pctfree、pctincrease等),还可以move到另外的表空间,如果不指定表空间,则在原来的表空间中move,因此做move操作时,目标表空间需要保证有这个表同样大小的剩余空间,整个操作相当于exp/imp(从表空间A导出到表空间B),move完表后要重建索引。

基于以上几种原因,现在做数据逻辑迁移都是用EXPDP/IMPDP而很少再使用EXP/IMP了。

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Windows 11 上缺少本地用户和组:如何添加它Windows 11 上缺少本地用户和组:如何添加它Sep 22, 2023 am 08:41 AM

“本地用户和组”实用程序内置于“计算机管理”中,可以从控制台访问,也可以独立访问。但是,一些用户发现Windows11中缺少本地用户和组。对于可以访问它的一些人来说,该消息显示,此管理单元可能不适用于此版本的Windows10。若要管理此计算机的用户帐户,请使用“控制面板”中的“用户帐户”工具。该问题已在上一次迭代Windows10中报告,并且通常是由于用户端的问题或疏忽引起的。为什么Windows11中缺少本地用户和组?您运行的是Windows家庭版,本地用户和组在专业版及更高版本上可用。活动

探索Windows 11指南:如何访问旧硬盘驱动器上的用户文件夹探索Windows 11指南:如何访问旧硬盘驱动器上的用户文件夹Sep 27, 2023 am 10:17 AM

由于权限,并不总是可以访问某些文件夹,在今天的指南中,我们将向您展示如何在Windows11上的旧硬盘驱动器上访问用户文件夹。此过程很简单,但可能需要一段时间,有时甚至数小时,具体取决于驱动器的大小,因此请格外耐心并严格按照本指南中的说明进行操作。为什么我无法访问旧硬盘上的用户文件夹?用户文件夹的所有权属于另一台电脑,因此您无法对其进行修改。除了所有权之外,您对该文件夹没有任何权限。如何打开旧硬盘上的用户文件?1.取得文件夹的所有权并更改权限找到旧的用户目录,右键单击它,然后选择属性。导航到“安

Windows 11 KB5031455无法安装,导致某些用户出现其他问题Windows 11 KB5031455无法安装,导致某些用户出现其他问题Nov 01, 2023 am 08:17 AM

Microsoft开始推出作为Windows503145511H22或更高版本的可选更新向公众KB2。这是第一个默认启用Windows11Moment4功能的更新,包括受支持区域中的WindowsCopilot、对“开始”菜单中项目的预览支持、任务栏的取消分组等。此外,它还修复了Windows11的几个错误,包括导致内存泄漏的潜在性能问题。但具有讽刺意味的是,2023年<>月的可选更新对于尝试安装更新的用户甚至已经安装更新的用户来说都是一场灾难。许多用户不会安装此Wi

Win11 新版画图:一键移除背景实现抠图功能Win11 新版画图:一键移除背景实现抠图功能Sep 15, 2023 pm 10:53 PM

微软邀请Canary和Dev频道的WindowsInsider项目成员,测试和体验新版画图(Paint)应用,最新版本号为11.2306.30.0。本次版本更新最值得关注的新功能是一键抠图功能,用户只需要点击一下,就能自动消除背景,凸显画面主体,便于用户后续操作。整个步骤非常简单,用户在新版画图应用中导入图片,然后点击工具栏上“移除背景”(removebackground)按钮,就可以删除图片中的背景,用户也可以使用矩形来选择要消除背景的区域。

TranslucentTB不起作用:如何解决TranslucentTB不起作用:如何解决Jun 06, 2023 am 08:21 AM

TranslucentTB是寻求时尚简约桌面外观的Windows11爱好者广泛使用的工具,遇到了障碍。自从发布以来Windows11内部版本22621.1344(22H2)28年2023月日,TranslucentTB对大多数用户不起作用。此错误使用户努力应对其任务栏的有限自定义选项。用户在寻求克服这一挫折的解决方案时,挫败感显而易见。在最近的Windows11更新之后,TranslucentTB无法正常工作的问题已在多个在线平台上广泛报道,包括论坛和社交媒体。用户一直在分享他们的经验,拼命寻找

mysql 怎么修改hostmysql 怎么修改hostFeb 15, 2023 am 10:17 AM

mysql修改host的方法:1、通过“sudo service mysql stop”停掉mysql服务;2、以安全模式启动mysql,然后重置root密码;3、通过“update user set Host='%' where User='hive';”语句修改host即可。

如何下载iPhone地图以供离线使用如何下载iPhone地图以供离线使用Nov 04, 2023 pm 11:13 PM

随着iOS17的推出,Apple使iPhone用户可以下载地图以供离线使用。此功能为可能前往互联网连接不可靠或不存在的地区的用户提供了实用的解决方案。通过将地图下载到他们的设备上,用户可以确保即使失去蜂窝连接,他们也可以始终访问他们想要的路线。离线地图不仅仅是一个位置的静态图像。它们为用户提供了丰富的信息,例如地点的小时数和评级、转弯路线和预计到达时间。这意味着即使没有Wi-Fi或蜂窝连接,用户仍然可以导航和访问有关周围环境的关键信息。在“地图”中使用搜索功能打开“地图”应用,在搜索中搜索某个地

修复: LSA 数据库包含内部不一致修复: LSA 数据库包含内部不一致Aug 04, 2023 am 09:17 AM

在使用驱动器映射和网络共享时,可能会出错很多问题。无线网络不如有线网络可靠。一直困扰用户的错误是本地安全机构数据库包含内部不一致。这会影响所有网络驱动器,用户无法访问或连接到它。该错误还会导致通过Samba到Linux和其他工作站的网络共享出现问题。在某些情况下,用户会看到消息,云端硬盘无法访问。您可能没有使用此网络资源的权限。本地安全机构数据库包含内部不一致。继续阅读以了解有关问题的所有信息!为什么无法访问网络驱动器?以下是网络驱动器突然停止工作的几个原因:缺少权限:通常,使用权限不足的帐户(

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools