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

最新推荐文章于 2023-05-24 18:38:18 发布

cuizhi4718

最新推荐文章于 2023-05-24 18:38:18 发布

阅读量226

点赞数

本文详细介绍了数据库操作，包括创建目录、创建表、查看表所在表空间、导出和导入数据，并演示了数据表的重命名、表空间更改和用户更改的过程。最后通过查询数据验证了操作的有效性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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，完成！

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

转载于:http://blog.itpub.net/10271187/viewspace-1283100/