【Oracle】Oracle-Linux下导出、导入数据库命令实现（三）

原创

艾文编程 2023-04-30 06:14:33 博主文章分类：数据库技术入门 ©著作权

文章标签 Oracle 数据库 plsql Processing oracle 文章分类 Html/CSS 前端开发

©著作权归作者所有：来自51CTO博客作者艾文编程的原创作品，请联系作者获取转载授权，否则将追究法律责任

Linux下导出数据库

1、导出全库（规则备份表后缀为 product_20130817.dmp）
2、导出相关数据表到指定用户(作为验证使用)
1、在linux中创建备份目录 (以oracle dba身份登录)
1.1、登录linux环境
1.2、在tmp下面创建备份文件夹
cd /tmp
mkdir backup (删除文件夹：rmdir backup、删除文件 rm filename.log)
2、使用System身份登录PL/SQL客户端或者服务器
2.1、创建文件备份目录
create directory bakdir as '/tmp/backup'
select * from dba_directories
2.2、给定该用户读写权限
grant read,write on directory bakdir to ngves3;
2.3、在cmd下执行导出操作或者服务器上执行（蓝色部分可以省略）
导出命令：
第一种情况：
[oracle@demoserver lib]$ expdp ngves3/asiainfo@mydb dumpfile=product_20130818_2.dmp directory=bakdir tables=t_user,t_dept
第二种情况：
[oracle@demoserver lib]$ expdp ngves3/asiainfo@192.168.204.135:1521/mydb dumpfile=product_20130818.dmp directory=bakdir tables=t_user,t_dept
第三种情况：
[oracle@demoserver lib]$ expdp ngves3/asiainfo@mydb dumpfile=product_20130818_3.dmp directory=bakdir

具体如图所示：

[oracle@demoserver lib]$ expdp ngves3/asiainfo@192.168.204.135:1521/mydb dumpfile=product_20130818.dmp directory=bakdir tables=t_user,t_dept
Export: Release 10.2.0.1.0 - Production on Saturday, 17 August, 2013 20:40:19
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "NGVES3"."SYS_EXPORT_TABLE_01": ngves3/********@192.168.204.135:1521/mydb dumpfile=product_20130818.dmp directory=bakdir tables=t_user,t_dept
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
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "NGVES3"."T_USER" 6.390 KB 5 rows
. . exported "NGVES3"."T_DEPT" 0 KB 0 rows
Master table "NGVES3"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for NGVES3.SYS_EXPORT_TABLE_01 is:
Linux 5 下 Oracle10 使用手册 1.0 版
- 11 -
/tmp/backup/product_20130818.dmp
Job "NGVES3"."SYS_EXPORT_TABLE_01" successfully completed at 20:40:27

Linux导入备份数据库

1.使用System身份登录PL/SQL客户端或者Linux服务器
--查看用户和默认表空间的关系
 记录查询结果集(ngves3默认表空间名称) select du.default_tablespace from dba_users du where du.username='NGVES3'; 结果为： TBS_VES3_BUSI_01
 创建用户check_ngves3 指定默认表空间为1查询的结果 create user check_ngves3 identified by check_ngves3_pass default tablespace TBS_VES3_BUSI_01
 给check_ngves3指定权限 grant read,write on directory bakdir to check_ngves3;
GRANT UNLIMITED TABLESPACE TO CHECK_NGVES3; GRANT CONNECT TO CHECK_NGVES3; GRANT RESOURCE TO CHECK_NGVES3; GRANT DBA TO CHECK_NGVES3; GRANT EXP_FULL_DATABASE TO CHECK_NGVES3; GRANT IMP_FULL_DATABASE TO CHECK_NGVES3; ALTER USER CHECK_NGVES3 DEFAULT ROLE ALL;
2. 在cmd下执行导入操作获取Linux服务器下执行导入命令（ngves3导出的数据导入到check_ngves3上）
impdp check_ngves3/check_ngves3_pass@192.168.204.135:1521/mydb remap_schema=ngves3:check_ngves3 dumpfile=product_20130818_3.dmp directory=bakdir TABLE_EXISTS_ACTION=TRUNCATE
执行结果如下图所示：
[oracle@demoserver backup]$ impdp check_ngves3/check_ngves3_pass@192.168.204.135:1521/mydb remap_schema=ngves3:check_ngves3 dumpfile=product_20130818_3.dmp directory=bakdir TABLE_EXISTS_ACTION=REPLACE
Import: Release 10.2.0.1.0 - Production on Sunday, 18 August, 2013 9:21:07
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "CHECK_NGVES3"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "CHECK_NGVES3"."SYS_IMPORT_FULL_01": check_ngves3/********@192.168.204.135:1521/mydb remap_schema=ngves3:check_ngves3 dumpfile=product_20130818_3.dmp directory=bakdir TABLE_EXISTS_ACTION=REPLACE
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"CHECK_NGVES3" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Linux 5 下 Oracle10 使用手册 1.0 版
- 12 -
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "CHECK_NGVES3"."T_USER" 6.390 KB 5 rows
. . imported "CHECK_NGVES3"."T_DEPT" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT