1. EXPDPIMPDP SQLcreateuserzlmidentifiedbyzlm; Usercreated. SQLgrantconnect, resourcetozlm; Grantsucceeded. Large; Tablespacecreated.
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 100 M reuse; Tablespace created.
1. EXPDP/IMPDP Mode
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 100 M 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. username,. default_tablespace, B. name from dba_users a, v $ datafile B, v $ tablespace c where. default_tablespace = c. name and B. ts # = c. ts # and. 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 100 M;
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,200 5, 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... -- You can specify it through estimate = block (default), and statistic
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 17.18 MB
Processing object type TABLE_EXPORT/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
Processing object type TABLE_EXPORT/TABLE/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,200 5, 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". "release": zlm/******** parallel = 2 remap_schema = scott: zlm remap_tablespace = users: ts_zlm directory = mongodumpfile = scott01.dmp, scott02.dmp
Processing object type TABLE_EXPORT/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
Processing object type TABLE_EXPORT/TABLE/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,200 5, 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
All objects in schema scott's tablespace USERS have been imported into the tablespace of schema zlm, including tables and indexes.
2. EXP/IMP Mode
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,200 5, 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,200 5, 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 -- the path is specified incorrectly.
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
Note: All objects imported with imp are stored in the tablespace during exp export. to migrate to another tablespace, use alter table move tablespace, first, use the following SQL concatenation statement to obtain the batch move statement: 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
We can see that all the database objects imported by zlm user imp have been migrated to the TS_ZLM tablespace.
Summary:
EXPDP/IPMDP can only run on the server. Its operation efficiency depends entirely on disk I/O, while EXP/IMP can run not only on the server but also on the client, therefore, in addition to the disk I/O constraints, there are also network factors.
The EXPDP/IMPDP performance is greatly improved than the EXP/IMP performance. The most influential factor is paralle, which is usually set to the number of cpus. This parameter can be exported in parallel, greatly improving the export speed, EXP/IMP does not have this function, so there is only one DIRECT = Y at most, so that the export does not have to go through the SELECT to sga buffer, and the speed of improvement is limited, note that the syntax between them is quite different, and parameters cannot be replaced by each other.
EXPDP/IMPDP can directly migrate data between different users and tablespaces through remap_schema and remap_tablespace, while EXP/IMP is slightly more troublesome, you must use the alter table xxx move tablespace xxx/alter index xxx rebuild tablespace xxx method. Moving is equivalent to recreating a table. You can modify the storage parameters (such as initial, pctfree, and pctincrease) and move the table to another table space. If no table space is specified, then move in the original tablespace. Therefore, when performing the move operation, ensure that the target tablespace has the same size of the table, the entire operation is equivalent to exp/imp (export from tablespace A to tablespace B). After moving the table, re-create the index.
Based on the above reasons, the current data logical migration uses EXPDP/IMPDP and seldom uses EXP/IMP.
