exp/imp小练习

monkeybird

环境：平台分别是REDHAT 5.5和WINDOWS SP3，ORACLE数据库都是11gr2

 

一、导入表
1.在windows环境中，用用户SCOTT下创建一张新表并插入一些数据
SQL> conn scott/tiger
已连接。
SQL> create table aa(name varchar2(20));

表已创建。

SQL> insert into aa values('james');

已创建 1 行。

SQL> insert into aa values('wade');

已创建 1 行。

SQL> insert into aa values('bosh');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from aa;

NAME
--------------------
james
wade
bosh

2.导出表aa
C:\Documents and Settings\Administrator>exp scott/tiger file=f:\exp\exp_table.dmp tables=(aa);

Export: Release 11.2.0.3.0 - Production on 星期二 10月 16 15:56:23 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径...
. . 正在导出表                              AA导出了           3 行
导出成功终止, 但没有警告。

3.查看linux中SCOTT用户没有表aa
SQL> conn scott/tiger
Connected.
SQL> select table_name from tabs;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE

4.将导出文件上传到linux中

5.进行导入
[oracle@dbserver ~]$ imp scott/tiger file=/home/oracle/exp_table.dmp tables=aa

Import: Release 11.2.0.1.0 - Production on Tue Oct 16 16:05:12 2012

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

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)
export client uses ZHS16GBK character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                           "AA"          3 rows imported
Import terminated successfully without warnings.

6.进行确认
[oracle@dbserver ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 16 16:05:53 2012

Copyright (c) 1982, 2009, Oracle.  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

SQL> select table_name from tabs;

TABLE_NAME
------------------------------
AA
DEPT
EMP
BONUS
SALGRADE

SQL> select * from aa;

NAME
--------------------
james
wade
bosh
导入成功。

5dm

收藏

monkeybird

四、导入全库
1.在WINDOWS上做一些修改方便以后导入的验证
(1)修改用户SCOTT模式下表EMP的数据
SQL>conn scott/tiger
已连接。
SQL> select ename,sal from emp;

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300

已选择14行。

SQL> delete from emp where ename='JAMES';

已删除 1 行。

SQL> update emp set sal=2600 where ename='ALLEN';

已更新 1 行。

SQL> select ename,sal from emp;

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            2600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
FORD             3000
MILLER           1300

已选择13行。

SQL> commit;

提交完成。

(2)新建一个表空间lianxi
SQL> conn / as sysdba
已连接。
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

F:\DATABASE\INSTALL\ORADATA\ORCL\SYSTEM01.DBF
F:\DATABASE\INSTALL\ORADATA\ORCL\SYSAUX01.DBF
F:\DATABASE\INSTALL\ORADATA\ORCL\UNDOTBS01.DBF
F:\DATABASE\INSTALL\ORADATA\ORCL\USERS01.DBF
F:\DATABASE\INSTALL\ORADATA\ORCL\EXAMPLE01.DBF

SQL> create tablespace lianxi datafile 'F:\DATABASE\INSTALL\ORADATA\ORCL\lianxi01.dbf' size 10m;

表空间已创建。

(3)新创建一个用户lx并指定其默认表空间为lianxi
SQL> create user lx identified by lx
 2>  default tablespace lianxi 
 3>  temporary tablespace temp;

用户已创建。

(4)给用户lx授权
SQL> grant connect,resource to lx;

授权成功。

(5)用用户lx新建一张表并插入一些数据
SQL> conn lx/lx
已连接。

SQL> create table tt(name varchar2(20),age number);

表已创建。

SQL> insert into tt values('KOBE',21);

已创建 1 行。

SQL> insert into tt values('WADE',43);

已创建 1 行。

SQL> insert into tt values('WANG',19);

已创建 1 行。

SQL> insert into tt values('JAMES',27);

已创建 1 行。

SQL> insert into tt values('BIRD',23);

已创建 1 行。

SQL> SELECT * FROM TT;

NAME                        AGE
-------------------- ----------
KOBE                         21
WADE                         43
WANG                         19
JAMES                        27
BIRD                         23

SQL> commit;

提交完成。

2.查看有哪些表空间
SQL> conn / as sysdba
已连接。
SQL> select  tablesapce_name from dba_tablespaces;

TABLESPACE_NAME                  

-------------------------

UNDOTBS1                             

SYSAUX                               
USERS                                  
SYSTEM                               
EXAMPLE                              
LIANXI                                

已选择6行。

3.进行全库的导出
C:\Documents and Settings\Administrator>exp 'sys/oracle as sysdba' file=f:\exp\exp_full.dmp full=y log=f:\exp\exp_full.log

Export: Release 11.2.0.3.0 - Production on 星期二 10月 16 16:24:34 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出整个数据库...
. 正在导出表空间定义
...............
...............
 正在导出统计信息
导出成功终止, 但出现警告。

4.查看linux中oracle数据库有哪些表空间
SQL> conn / as sysdba
connected.
SQL>select  tablesapce_name from dba_tablespaces;

TABLESPACE_NAME                  

-------------------------

UNDOTBS1                             

SYSAUX                               
USERS                                  
SYSTEM                               
EXAMPLE                               
                             

5.创建表空间lianx
SQL> select name from v$datafile; 

NAME
--------------------------------------------------------------------------------
/oracle/app/oracle/oradata/sztech1/system01.dbf
/oracle/app/oracle/oradata/sztech1/sysaux01.dbf
/oracle/app/oracle/oradata/sztech1/undotbs01.dbf
/oracle/app/oracle/oradata/sztech1/users01.dbf
/oracle/app/oracle/oradata/sztech1/example01.dbf

SQL> create tablespace lianxi datafile '/oracle/app/oracle/oradata/sztech1/lianxi01.dbf' size 10m;

Tablespace created.

6.查看用户SCOTT下表EMP的数据
SQL> conn scott/tiger
Connected.
SQL> select ename,sal from emp;

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300

14 rows selected.

7.上传WINDOWS中导出的文件
[oracle@dbserver ~]$ pwd
/home/oracle
[oracle@dbserver ~]$ ls
oradiag_oracle
[oracle@dbserver ~]$ rz
rz waiting to receive.
Starting zmodem transfer.  Press Ctrl+C to cancel.
Transferring exp_full.dmp...
  100%  146324 KB 5225 KB/s 00:00:28       0 Errors
[oracle@dbserver ~]$ ls
exp_full.dmp    oradiag_oracle

8.删除SCOTT用户下的emp表
SQL>conn scott/tiger
connected.
SQL>drop table emp;
TABLE DROPED.

9.进行全库导入
[oracle@dbserver ~]$ imp \'/ as sysdba\' file=exp_full.dmp full=y log=imp_full.log ignore=y

Import: Release 11.2.0.1.0 - Production on Tue Oct 16 16:47:56 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

............................
............................

Import terminated successfully with 3042 errors.

10.进行确认
SQL>conn / as sysdba

connected.

SQL> select  tablesapce_name from dba_tablespaces;

TABLESPACE_NAME                  

-------------------------

UNDOTBS1                             

SYSAUX                               
USERS                                  
SYSTEM                               
EXAMPLE                              
LIANXI                                

SQL> conn lx/lx
Connected.
SQL> select * from tt;

NAME                        AGE
-------------------- ----------
KOBE                         21
WADE                         43
WANG                         19
JAMES                        27
BIRD                         23

SQL> conn scott/tiger
Connected.
SQL> select ename,sal from emp;

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            2600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
FORD             3000
MILLER           1300

13 rows selected.

导入成功。

monkeybird

三、导入表空间
1.在windows服务器中新建一个表空间
SQL> conn / as sysdba
已连接。
SQL> select name from v$datafile;

NAME
-------------------------------------------------------------------------------

F:\DATABASE\INSTALL\ORADATA\ORCL\SYSTEM01.DBF
F:\DATABASE\INSTALL\ORADATA\ORCL\SYSAUX01.DBF
F:\DATABASE\INSTALL\ORADATA\ORCL\UNDOTBS01.DBF
F:\DATABASE\INSTALL\ORADATA\ORCL\USERS01.DBF
F:\DATABASE\INSTALL\ORADATA\ORCL\EXAMPLE01.DBF

SQL> create tablespace dbkj datafile 'F:\DATABASE\INSTALL\ORADATA\ORCL\dbkj01.d
f' size 10m;

表空间已创建。

2.新建一个用户并指定其默认表空间
SQL> create user dj identified by dj default tablespace dbkj;

用户已创建。

SQL> grant connect,resource to dj;

授权成功。

3.用新建用户创建新表并插入数据
SQL> conn dj/dj
已连接。
SQL> create table tt(name varchar2(10),age number);

表已创建。

SQL> insert into tt values ('james',27);

已创建 1 行。

SQL> insert into tt values ('wade',30);

已创建 1 行。

SQL> insert into tt values ('bosh',30);

已创建 1 行。

SQL> insert into tt values ('kobe',33);

已创建 1 行。
SQL> commit;

提交完成。

SQL> select * from tt;

NAME              AGE
---------- ----------
james              27
wade               30
bosh               30
kobe               33

4.将表空间离线准备导出
SQL> conn / as sysdba
已连接。
SQL> alter tablespace dbkj read only;

表空间已更改。

5.导出表空间
C:\Documents and Settings\Administrator>exp system/oracle tablespaces=dbkj file=f:\exp_tbs.dmp log=f:\exp_tbs.log

Export: Release 11.2.0.3.0 - Production on 星期一 10月 22 15:07:18 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出所选表空间...
对于表空间 DBKJ...
. 正在导出簇定义
. 正在导出表定义
. . 正在导出表                              TT导出了           4 行
. 正在导出引用完整性约束条件
. 正在导出触发器
成功终止导出, 没有出现警告。

6.在Linux中创建同样的表空间和用户
SQL>conn / as sysdba
connected.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oracle/app/oracle/oradata/sztech1/system01.dbf
/oracle/app/oracle/oradata/sztech1/sysaux01.dbf
/oracle/app/oracle/oradata/sztech1/undotbs01.dbf
/oracle/app/oracle/oradata/sztech1/users01.dbf
/oracle/app/oracle/oradata/sztech1/example01.dbf

SQL> create tablespace dbkj datafile '/oracle/app/oracle/oradata/sztech1/dbkj01.dbf' size 10m;

Tablespace created.

SQL> create user dj identified by dj default tablespace dbkj ;

User created.

SQL> grant connect,resource to dj;

Grant succeeded.

7.上传导出文件
[oracle@dbserver ~]$ pwd
/home/oracle
[oracle@dbserver ~]$ ls
oradiag_oracle
[oracle@dbserver ~]$ rz
rz waiting to receive.
Starting zmodem transfer.  Press Ctrl+C to cancel.
Transferring exp_tbs.dmp...
  100%       4 KB    4 KB/s 00:00:01       0 Errors
[oracle@dbserver ~]$ ls
exp_tbs.dmp     oradiag_oracle

8.进行导入
[oracle@dbserver ~]$ imp system/oracle  tablespaces=dbkj file=/home/oracle/exp_tbs.dmp full=y  log=/home/oracle/imp_tbs.log

Import: Release 11.2.0.1.0 - Production on Mon Oct 22 15:16:24 2012

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

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)
export client uses ZHS16GBK character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
. importing DJ's objects into DJ
. . importing table                           "TT"          4 rows imported
Import terminated successfully without warnings.

9.进行验证
SQL> conn dj/dj
Connected.
SQL> select * from tt;

NAME              AGE
---------- ----------
james              27
wade               30
bosh               30
kobe               33
导入成功。

monkeybird

二、导入用户模式
1.在linux中的oracle上新建一个用户james并授予相应权限
[root@dbserver ~]# su - oracle
[oracle@dbserver ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 16 15:11:18 2012

Copyright (c) 1982, 2009, Oracle.  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

SQL> create user james identified by james ;

User created.

SQL> grant connect,resource to james;

Grant succeeded.

2.用用户james登录创建一张表并插入一些数据
SQL> conn james/james
Connected.
SQL> create table lbj(id number);

Table created.

SQL> insert into lbj values(23);

1 row created.

SQL> insert into lbj values(34);

1 row created.

SQL> insert into lbj values(55);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from lbj;

        ID
----------
        23
        34
        55

3.导出用户james
[oracle@dbserver ~]$ exp system/oracle owner=james file=/home/oracle/exp_owner.dmp

Export: Release 11.2.0.1.0 - Production on Tue Oct 16 15:16:41 2012

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
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user JAMES
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user JAMES
About to export JAMES's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export JAMES's tables via Conventional Path ...
. . exporting table                            LBJ          3 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 dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

4.在windows中的oracle上创建用户james并授予同样的权限
C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 10月 16 15:30:26 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user james identified by james ;

User created.

SQL> grant connect,resource to james;

Grant succeeded.

5.用james登录并确定他没有表

SQL> conn james/james
已连接。

SQL> select table_name from tabs；
未选定行。

6.将linux中导出的文件拷贝到windows中

7.进行导入
C:\Documents and Settings\Administrator>imp system/oracle fromuser=james touser=james file=f:\exp\exp_owner.dmp

Import: Release 11.2.0.3.0 - Production on 星期二 10月 16 15:23:00 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

经由常规路径由 EXPORT:V11.02.00 创建的导出文件
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
导出客户机使用 US7ASCII 字符集 (可能的字符集转换)
. 正在将 JAMES 的对象导入到 JAMES
. . 正在导入表                           "LBJ"导入了           3 行
成功终止导入, 没有出现警告。

8.用james登录确认导入成功
SQL> conn james/james
已连接。

SQL> select table_name from tabs;

TABLE_NAME
------------------------------
LBJ

SQL> select * from lbj;
        ID
----------
        23
        34
        55
导入成功。