關於12C版本匯出匯入11G版本的這點事

638476發表於2014-01-19


一、      介紹實驗使用的例子

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章