Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing o
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16GBK character set (possible charset conversion) . importing SCOTT's objects into SCOTT . importing SCOTT's objects into SCOTT
. . importing partition \ . . importing partition \ . . importing partition \ . . importing partition \ . . importing partition \ . . importing partition \ . . importing partition \ Import terminated successfully without warnings. SQL> select count(1) from tb_pt partition(sal_other); COUNT(1) ---------- 2875
四、使用expdp/impdb来实现分区表的导入导出 1.查看导入导出的目录设置
SQL> select directory_name,directory_path from dba_directories where directory_name='DMP';
DIRECTORY_NAME DIRECTORY_PATH
9
------------------------------ ------------------------------------------------------------ DMP /u02/dmp
2.为分区表创建一个本地索引 create index tb_pt_local_idx on tb_pt(sal_id) local
(partition local1, partition local2, partition local3, partition local4, partition local5, partition local6, partition local7) ;
3.导出整个表
[oracle@node1 ~]$ expdp scott/tiger directory=dmp dumpfile=tb_pt.dmp logfile=tb_pb.log tables=tb_pt parallel=3
Export: Release 11.2.0.1.0 - Production on Wed Mar 9 14:04:28 2011 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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting \dumpfile=tb_pt.dmp logfile=tb_pb.log tables= tb_pt parallel=3
10
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 512 KB
. . exported \ . . exported \ . . exported \ . . exported \ . . exported \ . . exported \ . . exported \ Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type
TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type
TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Master table \ ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /u02/dmp/tb_pt.dmp
Job \ 4.导出多个分区
[oracle@node1 ~]$ expdp scott/tiger directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt.log /
> tables=(tb_pt:sal_16,tb_pt:sal_other) parallel=2
Export: Release 11.2.0.1.0 - Production on Wed Mar 9 14:08:06 2011
11
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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting \dumpfile=tb_pts.dmp logfile=tb_pt.log
tables=(tb_pt:sal_16,tb_pt:sal_other) parallel=2 --*/ Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB
. . exported \ . . exported \ Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type
TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type
TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Master table \ ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /u02/dmp/tb_pts.dmp
Job \ 5.截断分区sal_other
12
