一、创建测试用户sa以及一系列测试表，并插入不同量的数据
SQL> create user sa identified by oracle;
User created.
SQL> grant resource ,connect to sa;
Grant succeeded.
SQL> conn sa/oracle
Connected.
SQL> create table a1 (id number,a varchar2(4));
Table created.
SQL> create table aa1 (id number,a varchar2(4))
Table created.
SQL> create table ac1 (id number,a varchar2(4));
Table created.
SQL> create table b1 (id number,a varchar2(4));
Table created.
SQL> create table c1 (id number,a varchar2(4));
Table created.
SQL> create table d1 (id number,a varchar2(4));
Table created.
SQL> create table bc1 (id number,a varchar2(4));
Table created.
SQL>
以这种方式插入数据
SQL> begin
2 for i in 1..100000 loop
3 insert into a1 values(i,'ss');
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
二、exp和imp导出导入
[ora11203@mcdbatest bak]$ exp sa/oracle file=sa.dmp log=sa.log owner=sa
Export: Release 11.2.0.3.0 - Production on Fri Mar 18 14:18:32 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing 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 SA
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SA
About to export SA's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SA's tables via Conventional Path ...
. . exporting table A1 100000 rows exported
. . exporting table AA1 100 rows exported
. . exporting table AC1 2000000 rows exported
. . exporting table B1 2000000 rows exported
. . exporting table BC1 2000 rows exported
. . exporting table C1 40000 rows exported
. . exporting table D1 100 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 without warnings.
看起来是按字母排序的、看看imp
[ora11203@mcdbatest bak]$ imp sa/oracle file=sa.dmp log=ss.log ignore=y
Import: Release 11.2.0.3.0 - Production on Fri Mar 18 14:19:25 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
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 SA's objects into SA
. . importing table "A1" 100000 rows imported
. . importing table "AA1" 100 rows imported
. . importing table "AC1" 2000000 rows imported
. . importing table "B1" 2000000 rows imported
. . importing table "BC1" 2000 rows imported
. . importing table "C1" 40000 rows imported
. . importing table "D1" 100 rows imported
Import terminated successfully without warnings.
也是按字母顺利
三、expdp和impdp看看
创建directory grant 读写
SQL> create directory tt as '/home/ora11203/test';
Directory created.
SQL> grant write,read on directory tt to sa;
Grant succeeded.
SQL>
[ora11203@mcdbatest test]$ expdp sa/oracle dumpfile=sa.dmp logfile=sb.log directory=tt
Export: Release 11.2.0.3.0 - Production on Fri Mar 18 14:35:58 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SA"."SYS_EXPORT_SCHEMA_01": sa/******** dumpfile=sa.dmp logfile=sb.log directory=tt
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 125.2 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "SA"."AC1" 47.63 MB 4000000 rows
. . exported "SA"."B1" 47.63 MB 4000000 rows
. . exported "SA"."A1" 2.272 MB 200000 rows
. . exported "SA"."C1" 922.3 KB 80000 rows
. . exported "SA"."BC1" 48.12 KB 4000 rows
. . exported "SA"."AA1" 7.335 KB 200 rows
. . exported "SA"."D1" 7.335 KB 200 rows Master table "SA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SA.SYS_EXPORT_SCHEMA_01 is:
/home/ora11203/test/sa.dmp
Job "SA"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:36:57
[ora11203@mcdbatest test]$
从大到小的顺序的，impdp就不测了
四、impdp导入exp导出的dmp文件试试
[ora11203@mcdbatest ~]$ impdp sa/oracle dumpfile=sa.dmp logfile=sb.log directory=tt
Import: Release 11.2.0.3.0 - Production on Fri Mar 18 14:35:14 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39143: dump file "/home/ora11203/test/sa.dmp" may be an original export dump file
不行，所以，expdp/impdp和exp/imp不能混用
阅读(3094) | 评论(0) | 转发(0) |
