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

最新推荐文章于 2024-03-12 16:04:08 发布

weixin_34370347

最新推荐文章于 2024-03-12 16:04:08 发布

阅读量903

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/maojindaoGG/blog/829718

版权

2019独角兽企业重金招聘Python工程师标准>>>

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

转载于:https://my.oschina.net/maojindaoGG/blog/829718