EXPDP/IMPDP更改用户 表空间 表名

1、创建directory JZH@test>create directory test_dir as '/home/oracle/'; Directory created. 2、创建测试表 JZH@test>create table emp1 as select * from scott.emp; Table created. 3、查看表所在表空间 JZH@test>select owner,table_name,tablespace_name from dba_tables where table_name='EMP1'; OWNER TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ JZH EMP1 USERS expdp jzh/jzh dumpfile=emp1.dmp directory=test_dir tables=emp1 logfile=emp1.log Export: Release 11.2.0.3.0 - Production on Sat Sep 27 15:09:23 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "JZH"."SYS_EXPORT_TABLE_01": jzh/******** dumpfile=emp1.dmp directory=test_dir tables=emp1 logfile=emp1.log Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "JZH"."EMP1" 8.562 KB 14 rows Master table "JZH"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for JZH.SYS_EXPORT_TABLE_01 is: /home/oracle/emp1.dmp Job "JZH"."SYS_EXPORT_TABLE_01" successfully completed at 15:09:40 4、查询emp1表 select empno,ename,sal,comm from emp1; EMPNO ENAME SAL COMM ---------- ---------- ---------- ---------- 7369 SMITH 1900 7499 ALLEN 1600 300 7521 WARD 1250 500 7566 JONES 2975 7654 MARTIN 1250 1400 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 0 7876 ADAMS 1100 EMPNO ENAME SAL COMM ---------- ---------- ---------- ---------- 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 14 rows selected. Package body created. 5、更改表名，更改表空间，更改用户 impdp park/park dumpfile=emp1.dmp directory=test_dir logfile=emp1.log remap_schema=jzh:park remap_tablespace=users:example remap_table=emp1:emp2 6、检查结果 PARK@test>select owner,table_name,tablespace_name from dba_tables where table_name='EMP2'; OWNER TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ PARK EMP2 EXAMPLE 用户已由jzh更改为park,表名由EMP1更改为EMP2，表空间由USERS更改为EXAMPLE 6、查询数据 PARK@test>select empno,ename,sal,comm from emp2; EMPNO ENAME SAL COMM ---------- ---------- ---------- ---------- 7369 SMITH 1900 7499 ALLEN 1600 300 7521 WARD 1250 500 7566 JONES 2975 7654 MARTIN 1250 1400 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 0 7876 ADAMS 1100 EMPNO ENAME SAL COMM ---------- ---------- ---------- ---------- 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 14 rows selected. OK，完成！

补充：

/*第1步：创建临时表空间 */ create temporary tablespace AZTJ_temp tempfile 'D:\data\AZTJ_temp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;

/*第2步：创建数据表空间 */ create tablespace TPAZTJ logging datafile 'D:\data\TPAZTJ.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;

/*第3步：创建用户并指定表空间 */ create user 用户名 identified by 密码 default tablespace TPAZTJ temporary tablespace AZTJ_temp ;

/*第4步：给用户授予权限 */ grant connect,resource,dba to 用户名;

/* 导出 */ SELECT * FROM DBA_DIRECTORIES --创建DBA_DIRECTORIES --create directory dump_gjms_dir as 'C:\orcldump'; --DMP文件COPY到'C:\orcldump' --expdp GJMS/GJMS DIRECTORY=DUMP_ZDZB_DDIR dumpfile=20150325gjms.dmp logfile=20150325gjms.log --执行导入，打开CMD编辑，把Impdp拷贝执行

--有效 Impdp gjms/gjms DIRECTORY=DUMP_GJMS_DDIR DUMPFILE=20141231GJMS.dmp LOGFILE=20150104_3gjms.log