Home Database Mysql Tutorial EXPDP/IMPDP与EXP/IMP在不同用户和表空间之间迁移数据的实现方法

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

Jun 07, 2016 pm 04:01 PM
exp different user space

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 of this Website
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

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

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

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to use Xiaohongshu account to find users? Can I find my mobile phone number? How to use Xiaohongshu account to find users? Can I find my mobile phone number? Mar 22, 2024 am 08:40 AM

With the rapid development of social media, Xiaohongshu has become one of the most popular social platforms. Users can create a Xiaohongshu account to show their personal identity and communicate and interact with other users. If you need to find a user’s Xiaohongshu number, you can follow these simple steps. 1. How to use Xiaohongshu account to find users? 1. Open the Xiaohongshu APP, click the "Discover" button in the lower right corner, and then select the "Notes" option. 2. In the note list, find the note posted by the user you want to find. Click to enter the note details page. 3. On the note details page, click the "Follow" button below the user's avatar to enter the user's personal homepage. 4. In the upper right corner of the user's personal homepage, click the three-dot button and select "Personal Information"

Local users and groups are missing on Windows 11: How to add it Local users and groups are missing on Windows 11: How to add it Sep 22, 2023 am 08:41 AM

The Local Users and Groups utility is built into Computer Management and can be accessed from the console or independently. However, some users find that local users and groups are missing in Windows 11. For some people who have access to it, the message suggests that this snap-in may not work with this version of Windows 10. To manage user accounts for this computer, use the User Accounts tool in Control Panel. The issue has been reported in previous iterations of Windows 10 and is usually caused by issues or oversights on the user's side. Why are local users and groups missing in Windows 11? You are running Windows Home edition, local users and groups are available on Professional edition and above. Activity

Log in to Ubuntu as superuser Log in to Ubuntu as superuser Mar 20, 2024 am 10:55 AM

In Ubuntu systems, the root user is usually disabled. To activate the root user, you can use the passwd command to set a password and then use the su- command to log in as root. The root user is a user with unrestricted system administrative rights. He has permissions to access and modify files, user management, software installation and removal, and system configuration changes. There are obvious differences between the root user and ordinary users. The root user has the highest authority and broader control rights in the system. The root user can execute important system commands and edit system files, which ordinary users cannot do. In this guide, I'll explore the Ubuntu root user, how to log in as root, and how it differs from a normal user. Notice

Explore Windows 11 guide: How to access user folders on your old hard drive Explore Windows 11 guide: How to access user folders on your old hard drive Sep 27, 2023 am 10:17 AM

Certain folders are not always accessible due to permissions, and in today’s guide we will show you how to access user folders on your old hard drive on Windows 11. The process is simple but can take a while, sometimes even hours, depending on the size of the drive, so be extra patient and follow the instructions in this guide closely. Why can't I access my user folders on my old hard drive? User folders are owned by another computer, so you cannot modify them. You don't have any permissions on the folder other than ownership. How to open user files on old hard drive? 1. Take ownership of the folder and change permissions Find the old user directory, right-click on it and select Properties. Navigate to "An

What is sudo and why is it important? What is sudo and why is it important? Feb 21, 2024 pm 07:01 PM

sudo (superuser execution) is a key command in Linux and Unix systems that allows ordinary users to run specific commands with root privileges. The function of sudo is mainly reflected in the following aspects: Providing permission control: sudo achieves strict control over system resources and sensitive operations by authorizing users to temporarily obtain superuser permissions. Ordinary users can only obtain temporary privileges through sudo when needed, and do not need to log in as superuser all the time. Improved security: By using sudo, you can avoid using the root account during routine operations. Using the root account for all operations may lead to unexpected system damage, as any mistaken or careless operation will have full permissions. and

Tutorial: How to delete a normal user account in Ubuntu system? Tutorial: How to delete a normal user account in Ubuntu system? Jan 02, 2024 pm 12:34 PM

Many users have been added to the Ubuntu system. I want to delete the users that are no longer in use. How to delete them? Let’s take a look at the detailed tutorial below. 1. Open the terminal command line and use the userdel command to delete the specified user. Be sure to add the sudo permission command, as shown in the figure below. 2. When deleting, be sure to be in the administrator directory. Ordinary users do not have this permission. , as shown in the figure below 3. After the delete command is executed, how to judge whether it has been truly deleted? Next we use the cat command to open the passwd file, as shown in the figure below 4. We see that the deleted user information is no longer in the passwd file, which proves that the user has been deleted, as shown in the figure below 5. Then we enter the home file

Windows 11 KB5031455 fails to install, causing other issues for some users Windows 11 KB5031455 fails to install, causing other issues for some users Nov 01, 2023 am 08:17 AM

Microsoft began rolling out KB2 to the public as an optional update for Windows 503145511H22 or later. This is the first update to enable Windows 11 Moment 4 features by default, including Windows Copilot in supported areas, preview support for items in the Start menu, ungrouping of the taskbar, and more. Additionally, it fixes several Windows 11 bugs, including potential performance issues that caused memory leaks. But ironically, the optional update for September 2023 will be a disaster for users trying to install the update, or even for those who have already installed it. Many users will not install this Wi

Analysis of user password storage mechanism in Linux system Analysis of user password storage mechanism in Linux system Mar 20, 2024 pm 04:27 PM

Analysis of user password storage mechanism in Linux system In Linux system, the storage of user password is one of the very important security mechanisms. This article will analyze the storage mechanism of user passwords in Linux systems, including the encrypted storage of passwords, the password verification process, and how to securely manage user passwords. At the same time, specific code examples will be used to demonstrate the actual operation process of password storage. 1. Encrypted storage of passwords In Linux systems, user passwords are not stored in the system in plain text, but are encrypted and stored. L

See all articles