关于12C版本导出导入11G版本的这点事

一、      介绍实验使用的例子

1.       创建一个实验环境

 

先创建一个名字叫 user_testtbs的表空间

SQL> create tablespace user_testtbs

  2  datafile '/oradata/DB196/datafile/user_testtbs01.dbf' size 20M

  3  autoExtend on;

 

 

Tablespace created.

在表空间下创建一个名字叫user_test的用户密码是 user_test1234

SQL> create user user_test identified by user_test1234 default tablespace user_testtbs QUOTA unlimited ON  user_testtbs;

 

User created.

 

在这个用户下创建一个叫 user_test.t_class的表

SQL> create table user_test.t_class

  2  (name varchar2(20) not null,

  3  age  number(20),

  4  id number(20),

  5  sex  varchar2(5))

  6  tablespace user_testtbs;

 

Table created.

给出一个导出文件的路径并且给这个路径设置好权限　

# mkdir /expdp

chown -R oracle:oinstall  /expdp

chmod 777 /expdp

然后给这个用户各种授权啊

SQL> grant CREATE SESSION    TO user_test;

grant CREATE TRIGGER    TO user_test;

 

Grant succeeded.

创建一个路径，并且命名，这个路径叫exppump

SQL>create or replace directory exppump as '/expdp';

SQL>grant read,write on directory exppump to user_test;

SQL>alter tablespace user_testtbs read only;

SQL>exec sys.dbms_tts.transport_set_check(' user_testtbs ',true);

SQL>select * from sys.transport_set_violations;

 

 

 

SQL>

Grant succeeded.

 

SQL> grant CREATE SEQUENCE   TO user_test;

 

Grant succeeded.

 

SQL> grant CREATE CLUSTER    TO user_test;

 

Grant succeeded.

 

SQL> grant CREATE TYPE       TO user_test;

 

Grant succeeded.

 

SQL> grant CREATE PROCEDURE  TO user_test;

 

Grant succeeded.

 

SQL> grant CREATE TABLE      TO user_test;

 

Grant succeeded.

 

SQL> grant CREATE INDEXTYPE  TO user_test;

 

Grant succeeded.

 

SQL> grant CREATE OPERATOR   TO user_test;

 

Grant succeeded.

 

SQL> grant CREATE VIEW       TO user_test;

对着这张表插入一堆没用的数据

SQL> insert into t_class values( 'dd',18,1,'man');

 

1 row created.

 

SQL> insert into t_class values( 'hl',28,2,'man');

 

1 row created.

 

SQL> insert into t_class values( 'hl',28,2,'man');

 

1 row created.

 

SQL> insert into t_class values( 'hl',28,2,'man');

 

1 row created.

 

SQL> insert into t_class values( 'hl',28,2,'man');

 

1 row created.

 

 

2.       按照表的方式来导出

查看一下这个hl.dbf的文件到底有多大

@TORCL1:/home/oracle\/vi expdp.par

userid='dbmgr/dba4only'  用户密码

directory=exppump      路径的名字  刚才已经创建了这个路径 这里表现出一个结果

dumpfile=t_class.dmp    导出来的文件名

filesize=21M           文件的大小 命令是 du -h /要导出文件的路径和文件名

parallel=4              并行导出

version= 11.2.0.3.0       这个比较关键 就是12C 导出11G 必须填写正确的版本号

tables=user_test.t_class   导出的表 说清楚是哪个用户下的哪个表

job_name=expdp_user_test  起个名字 自己好记住 不然系统默认给个怪异的名字不好记

Logfile=expdp.log        日志名

 

@TORCL1:/home/oracle\/expdp parfile=expdp.par

 

Export: Release 12.1.0.1.0 - Production on Fri Jan 17 14:19:52 2014

 

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

 

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Starting "DBMGR"."EXPDP_USER_TEST":  dbmgr/******** parfile=expdp.par

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 0 KB

. . exported "USER_TEST"."T_CLASS"                           0 KB       0 rows

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Master table "DBMGR"."EXPDP_USER_TEST" successfully loaded/unloaded

******************************************************************************

Dump file set for DBMGR.EXPDP_USER_TEST is:

  /expdp/t_class.dmp

Job "DBMGR"."EXPDP_USER_TEST" successfully completed at Fri Jan 17 14:20:28 2014 elapsed 0 00:00:35

 

 

3.       按照表的方式来导入

create tablespace user_testtbs

datafile '/u01/app/oracle/oradata/ENMOEDU/user_testtbs01.dbf' size 20M

autoExtend on;

 

Tablespace created.

 

 

SYS@ENMOEDU> create user user_test identified by user_test1234 default tablespace user_testtbs QUOTA unlimited ON  user_testtbs;

 

# mkdir /impdp

chown -R oracle:oinstall  /expdp

chmod 777 /expdp

 

[root@ENMOEDU /]# ls -al /impdp/

total 12

drwxrwxrwx  2 oracle oinstall 4096 Jan 17 14:52 .

drwxr-xr-x 27 root   root     4096 Jan 17 14:52 ..

 

SYS@ENMOEDU> create or replace directory imppump as '/impdp';

 

Directory created.

 

 

User created.

 

SYS@ENMOEDU> grant CREATE SESSION    TO user_test;

 

Grant succeeded.

 

grant read,write on directory imppump to user_test;

 

Grant succeeded.

 

[oracle@ENMOEDU ~]$ impdp parfile=impdp.par

 

Import: Release 11.2.0.3.0 - Production on Fri Jan 17 17:38:00 2014

 

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 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

dMaster table "SYS"."IMPDP_USER_TEST" successfully loaded/unloaded

Starting "SYS"."IMPDP_USER_TEST":  /******** AS SYSDBA parfile=impdp.par

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "USER_TEST"."T_CLASS"                           0 KB       0 rows

 

 

 

 

 

 [oracle@ENMOEDU ~]$ vi impdp.par

erid='/ as sysdba'

directory=imppump

dumpfile=t_class.dmp

parallel=4

job_name=impdp_user_test

Logfile=impdp.log

 

 

 

 

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29366855/viewspace-1072668/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29366855/viewspace-1072668/