Linux下导出数据库
1、 导出全库(规则备份表后缀为 product_0817.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_0818_2.dmp directory=bakdir tables=t_user,t_dept
第二种情况：
[oracle@demoserver lib]$ expdp ngves3/asiainfo@192.168.204.135:1521/mydb dumpfile=product_0818.dmp directory=bakdir tables=t_user,t_dept
第三种情况：
[oracle@demoserver lib]$ expdp ngves3/asiainfo@mydb dumpfile=product_0818_3.dmp directory=bakdir
具体如图所示：
[oracle@demoserver lib]$ expdp ngves3/asiainfo@192.168.204.135:1521/mydb dumpfile=product_0818.dmp directory=bakdir tables=t_user,t_dept
Export: Release 10.2.0.1.0 - Production on Saturday, 17 August, 20:40:19
Copyright (c) , , 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_0818.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_0818.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_0818_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_0818_3.dmp directory=bakdir TABLE_EXISTS_ACTION=REPLACE
Import: Release 10.2.0.1.0 - Production on Sunday, 18 August, 9:21:07
Copyright (c) , , 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_0818_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
如果觉得《oracle linux命令导出 Oracle-Linux下导出 导入数据库命令实现（三）》对你有帮助，请点赞、收藏，并留下你的观点哦！
