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
Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

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 Tools

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software