一、创建DIRECTORY
SQL> create directory backupdir as '/u01/backupdir/';
Directory created
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS BACKUPDIR /u01/backupdir/
SYS SQLDR /u01/oracle/sqldr
SYS EXPDIR /u01/expdir
SYS SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep
SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/
SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/
SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/
SYS XMLDIR /ade/b/1191423112/oracle/rdbms/xml
SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/
SYS DATA_PUMP_DIR /u01/app/oracle/admin/orcl/dpdump/
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
11 rows selected
二、创建测试表空间和用户
SQL> create tablespace backupdir01 datafile '/u01/app/oracle/oradata/orcl/backup01.dbf' size 500m;
Tablespace created
SQL> create user user01 identified by user01 default tablespace backupdir01 temporary tablespace temp01;
User created
SQL> create tablespace backupdir02 datafile '/u01/app/oracle/oradata/orcl/backup02.dbf' size 500m;
Tablespace created
SQL> create user user02 identified by user02 default tablespace backupdir02 temporary tablespace temp01;
User created
三、授权
SQL> grant read,write on directory backupdir to user01,user02;
Grant succeeded
SQL> grant connect,resource to user01,user02;
Grant succeeded
SQL> conn user01/user01
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as user01@ORACLE_LINUX
SQL> create table user01 (id number,name varchar2(500));
Table created
SQL>
SQL> begin
2 for i in 1..20 loop
3 insert into user01 values(i,'Jerry'||i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
SQL> select * from user01;
ID NAME
---------- --------------------------------------------------------------------------------
1 Jerry1
2 Jerry2
3 Jerry3
4 Jerry4
5 Jerry5
6 Jerry6
7 Jerry7
8 Jerry8
9 Jerry9
10 Jerry10
11 Jerry11
12 Jerry12
13 Jerry13
14 Jerry14
15 Jerry15
16 Jerry16
17 Jerry17
18 Jerry18
19 Jerry19
20 Jerry20
20 rows selected
SQL> conn user02/user02
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as user02@ORACLE_LINUX
SQL> create table user02 (id number,name varchar2(500));
Table created
SQL>
SQL> begin
2 for i in 1..20 loop
3 insert into user02 values(i,'Jerry'||i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
SQL> select * From user02;
ID NAME
---------- --------------------------------------------------------------------------------
1 Jerry1
2 Jerry2
3 Jerry3
4 Jerry4
5 Jerry5
6 Jerry6
7 Jerry7
8 Jerry8
9 Jerry9
10 Jerry10
11 Jerry11
12 Jerry12
13 Jerry13
14 Jerry14
15 Jerry15
16 Jerry16
17 Jerry17
18 Jerry18
19 Jerry19
20 Jerry20
20 rows selected
四、全库导出测试
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" directory=backupdir full=y dumpfile=fullexp.dmp logfile=fullexp.log parallel=2
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 146.7 MB
. . exported "SH"."CUSTOMERS" 9.853 MB 55500 rows
. . exported "PM"."ONLINE_MEDIA" 7.752 MB 9 rows
. . exported "EYGLE"."EYGLE" 7.037 MB 72829 rows
. . exported "JERRY"."JERRY" 7.037 MB 72828 rows
. . exported "JERRY"."JERRY_COPY" 6.153 MB 62829 rows
. . exported "APEX_030200"."WWV_FLOW_PAGE_PLUGS" 3.835 MB 7417 rows
. . exported "APEX_030200"."WWV_FLOW_STEP_ITEMS" 3.505 MB 9673 rows
. . exported "APEX_030200"."WWV_FLOW_DICTIONARY$" 2.909 MB 70601 rows
. . exported "SH"."SUPPLEMENTARY_DEMOGRAPHICS" 697.3 KB 4500 rows
. . exported "JERRY"."PARTITION_DBA_TABLES_COPY":"P7" 1.919 MB 120832 rows
. . exported "JERRY"."PARTITION_DBA_TABLES_COPY":"P8" 2.298 MB 147456 rows
. . exported "OE"."PRODUCT_DESCRIPTIONS" 2.379 MB 8640 rows
. . exported "APEX_030200"."WWV_FLOW_STEP_PROCESSING" 1.248 MB 2239 rows
. . exported "APEX_030200"."WWV_FLOW_REGION_REPORT_COLUMN" 1.148 MB 7918 rows
. . exported "APEX_030200"."WWV_FLOW_STEP_ITEM_HELP" 1003. KB 6335 rows
Processing object type DATABASE_EXPORT/TABLESPACE
. . exported "SH"."SALES":"SALES_Q4_2001" 2.257 MB 69749 rows
. . exported "SYSMAN"."MGMT_METRICS" 3.203 MB 12635 rows
. . exported "APEX_030200"."WWV_FLOW_STEPS" 571.0 KB 1755 rows
. . exported "SH"."SALES":"SALES_Q1_1999" 2.071 MB 64186 rows
. . exported "SH"."SALES":"SALES_Q3_2001" 2.130 MB 65769 rows
. . exported "SH"."SALES":"SALES_Q1_2000" 2.012 MB 62197 rows
. . exported "SH"."SALES":"SALES_Q1_2001" 1.965 MB 60608 rows
......................................................................
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
/u01/backupdir/fullexp.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 05:33:03
expdp/impd 会调用 DBMS_DATAPUMP PL/SQL和 DBMS_METADATA PL/SQL ，一个提供导入导出，一个将metadata（对象定义）存储在 XML 。
五、导出表
[oracle@pc-centos backupdir]$ expdp \'/ as sysdba\' directory=backupdir dumpfile=table.dmp logfile=table.log tables=user01.user01;
Export: Release 11.2.0.1.0 - Production on Wed Jan 15 05:44:57 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=backupdir dumpfile=table.dmp logfile=table.log tables=user01.user01
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "USER01"."USER01" 5.726 KB 21 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/u01/backupdir/table.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 05:45:05
六导入表（两种方式）
[oracle@pc-centos backupdir]$ impdp user01/user01 directory=backupdir dumpfile=table.dmp logfile=table.log tables=user01 table_exists_action=replace;
Import: Release 11.2.0.1.0 - Production on Wed Jan 15 05:47:25 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "USER01"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "USER01"."SYS_IMPORT_TABLE_01": user01/******** directory=backupdir dumpfile=table.dmp logfile=table.log tables=user01 table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "USER01"."USER01" 5.726 KB 21 rows
Job "USER01"."SYS_IMPORT_TABLE_01" successfully completed at 05:47:26
[oracle@pc-centos backupdir]$ impdp \'/ as sysdba\' directory=backupdir dumpfile=table.dmp logfile=table.log tables=user01.user01 table_exists_action=replace;
Import: Release 11.2.0.1.0 - Production on Wed Jan 15 05:48:52 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": "/******** AS SYSDBA" directory=backupdir dumpfile=table.dmp logfile=table.log tables=user01.user01 table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "USER01"."USER01" 5.726 KB 21 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 05:48:54
七、导出用户
[oracle@pc-centos backupdir]$ expdp \'/ as sysdba\' directory=backupdir dumpfile=user.dmp logfile=user.log schemas=user01,user02;
Export: Release 11.2.0.1.0 - Production on Wed Jan 15 05:51:00 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=backupdir dumpfile=user.dmp logfile=user.log schemas=user01,user02
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DAT
total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "USER01"."USER01" 5.726 KB 21 rows
. . exported "USER02"."USER02" 5.695 KB 20 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/backupdir/user.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 05:51:22
八、导入用户
impdp \'/ as sysdba\' directory=backupdir dumpfile=user.dmp logfile=user.log schemas=user01,user02 table_exists_action=replace;
九、导出表空间
[oracle@pc-centos backupdir]$ expdp \'/ as sysdba\' directory=backupdir dumpfile=tbs.dmp logfile=tbs.log tablespaces=jerry;
Export: Release 11.2.0.1.0 - Production on Wed Jan 15 06:04:41 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLESPACE_01": "/******** AS SYSDBA" directory=backupdir dumpfile=tbs.dmp logfile=tbs.log tablespaces=jerry
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 18.18 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "JERRY"."JERRY" 7.037 MB 72828 rows
. . exported "JERRY"."JERRY_COPY" 6.153 MB 62829 rows
. . exported "JERRY"."TEST" 62.08 KB 835 rows
. . exported "JERRY"."JERRYTEST" 5.015 KB 1 rows
. . exported "JERRY"."ADDRESS":"P1" 0 KB 0 rows
. . exported "JERRY"."ADDRESS":"P_444" 0 KB 0 rows
. . exported "JERRY"."ADDRESS":"P_666" 0 KB 0 rows
. . exported "JERRY"."ADDRESS":"P_OTHER" 0 KB 0 rows
. . exported "JERRY"."PARTITION_DBA_TABLES_COPY":"P1" 0 KB 0 rows
. . exported "JERRY"."PARTITION_DBA_TABLES_COPY":"P10" 0 KB 0 rows
. . exported "JERRY"."PARTITION_DBA_TABLES_COPY":"P11" 0 KB 0 rows
. . exported "JERRY"."PARTITION_DBA_TABLES_COPY":"P12" 0 KB 0 rows
. . exported "JERRY"."PARTITION_DBA_TABLES_COPY":"P2" 0 KB 0 rows
. . exported "JERRY"."PARTITION_DBA_TABLES_COPY":"P3" 0 KB 0 rows
. . exported "JERRY"."PARTITION_DBA_TABLES_COPY":"P4" 0 KB 0 rows
. . exported "JERRY"."PARTITION_DBA_TABLES_COPY":"P5" 0 KB 0 rows
. . exported "JERRY"."PARTITION_DBA_TABLES_COPY":"P6" 0 KB 0 rows
. . exported "JERRY"."PARTITION_DBA_TABLES_COPY":"P7" 0 KB 0 rows
. . exported "JERRY"."PARTITION_DBA_TABLES_COPY":"P8" 0 KB 0 rows
. . exported "JERRY"."PARTITION_DBA_TABLES_COPY":"P9" 0 KB 0 rows
. . exported "JERRY"."REDEFIE_TABLE":"P1" 0 KB 0 rows
. . exported "JERRY"."REDEFIE_TABLE":"P2" 0 KB 0 rows
. . exported "JERRY"."REDEFIE_TABLE":"P3" 0 KB 0 rows
. . exported "JERRY"."REDEFIE_TABLE":"P4" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
/u01/backupdir/tbs.dmp
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at 06:04:47
十、导入表空间
[oracle@pc-centos backupdir]$ impdp \'/ as sysdba\' directory=backupdir dumpfile=tbs.dmp logfile=tbs.log tablespaces=jerry table_exists_action=replace;
Import: Release 11.2.0.1.0 - Production on Wed Jan 15 06:06:24 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLESPACE_01": "/******** AS SYSDBA" directory=backupdir dumpfile=tbs.dmp logfile=tbs.log tablespaces=jerry table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "JERRY"."JERRY" 7.037 MB 72828 rows
. . imported "JERRY"."JERRY_COPY" 6.153 MB 62829 rows
. . imported "JERRY"."TEST" 62.08 KB 835 rows
. . imported "JERRY"."JERRYTEST" 5.015 KB 1 rows
. . imported "JERRY"."ADDRESS":"P1" 0 KB 0 rows
. . imported "JERRY"."ADDRESS":"P_444" 0 KB 0 rows
. . imported "JERRY"."ADDRESS":"P_666" 0 KB 0 rows
. . imported "JERRY"."ADDRESS":"P_OTHER" 0 KB 0 rows
. . imported "JERRY"."PARTITION_DBA_TABLES_COPY":"P1" 0 KB 0 rows
. . imported "JERRY"."PARTITION_DBA_TABLES_COPY":"P10" 0 KB 0 rows
. . imported "JERRY"."PARTITION_DBA_TABLES_COPY":"P11" 0 KB 0 rows
. . imported "JERRY"."PARTITION_DBA_TABLES_COPY":"P12" 0 KB 0 rows
. . imported "JERRY"."PARTITION_DBA_TABLES_COPY":"P2" 0 KB 0 rows
. . imported "JERRY"."PARTITION_DBA_TABLES_COPY":"P3" 0 KB 0 rows
. . imported "JERRY"."PARTITION_DBA_TABLES_COPY":"P4" 0 KB 0 rows
. . imported "JERRY"."PARTITION_DBA_TABLES_COPY":"P5" 0 KB 0 rows
. . imported "JERRY"."PARTITION_DBA_TABLES_COPY":"P6" 0 KB 0 rows
. . imported "JERRY"."PARTITION_DBA_TABLES_COPY":"P7" 0 KB 0 rows
. . imported "JERRY"."PARTITION_DBA_TABLES_COPY":"P8" 0 KB 0 rows
. . imported "JERRY"."PARTITION_DBA_TABLES_COPY":"P9" 0 KB 0 rows
. . imported "JERRY"."REDEFIE_TABLE":"P1" 0 KB 0 rows
. . imported "JERRY"."REDEFIE_TABLE":"P2" 0 KB 0 rows
. . imported "JERRY"."REDEFIE_TABLE":"P3" 0 KB 0 rows
. . imported "JERRY"."REDEFIE_TABLE":"P4" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_TABLESPACE_01" successfully completed at 06:06:31
十一、REMAP_SCHEMA将原SCHEMA下的对象转移到目标SCHEMA下面
[oracle@pc-centos backupdir]$ expdp \'/ as sysdba\' directory=backupdir dumpfile=user011.dmp logfile=user011.log schemas=user01;
Export: Release 11.2.0.1.0 - Production on Wed Jan 15 06:14:25 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=backupdir dumpfile=user011.dmp logfile=user011.log schemas=user01
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "USER01"."USER01" 5.695 KB 20 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/backupdir/user011.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 06:14:43
[oracle@pc-centos backupdir]$ impdp \'/ as sysdba\' directory=backupdir dumpfile=user011.dmp logfile=user011.log remap_schema=user01:user02 table_exists_action=replace;
Import: Release 11.2.0.1.0 - Production on Wed Jan 15 06:15:30 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
阅读(785) | 评论(0) | 转发(0) |
