数据泵是服务器端工具，导出的文件是放在数据库所在的服务器上，当然我们知道可以通过directory目录对象来控制。目录对象默认有四个级别，当然是有优先级顺序的
数据泵是服务器端工具，免备案空间，导出的文件是放在数据库所在的服务器上，当然我们知道可以通过directory目录对象来控制。目录对象默认有四个级别，当然是有优先级顺序的，优先级从上往下
1.每个文件单独的指定具体的目录
2.expdp导出时，指定的目录参数
3.用户定义的环境变量DATA_PUMP_DIR指定的目录
4.默认的目录对象DATA_PUMP_DIR
当然了对于oracle11g R2来说，又引入了一个可选项，我们就当是5
5.DATA_PUMP_DIR_SCHEMA_NAME目录
一、默认的目录对象DATA_PUMP_DIR测试
SQL> desc dba_directories
Name Null? Type
----------------------------------------------------------------- -------- ---------------------- OWNER NOT NULL VARCHAR2(30)
DIRECTORY_NAME NOT NULL VARCHAR2(30)
DIRECTORY_PATH VARCHAR2(4000)
SQL> set linesize 120 pagesize 100
SQL> col OWNER for a5
SQL> col DIRECTORY_NAME for a22
SQL> col DIRECTORY_PATH for a80
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
----- ---------------------- ----------------------------------------------------------
SYS SUBDIR /u01/app/oracle/product/11.2.0/db/demo/schema/order_entry//2002/Sep
SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/db/demo/schema/order_entry/
SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/db/demo/schema/log/
SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/db/demo/schema/product_media/
SYS XMLDIR /u01/app/oracle/product/11.2.0/db/rdbms/xml
SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/db/demo/schema/sales_history/
SYS DATA_PUMP_DIR /u01/app/oracle/admin/tj01/dpdump/
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/db/ccr/state
通过查询我们看到，所有的目录都属于SYS用户，而不管是哪个用户创建的，香港空间，在数据库里已经提前建好了这个目录对象DATA_PUMP_DIR。如果在使用expdp导出时，不指定目录对象参数，Oracle会使用数据库缺省的目录DATA_PUMP_DIR，不过如果想使用这个目录的话，用户需要具有exp_full_database的权限才行
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL> create table demo as select empno,ename,sal,deptno from emp;
Table created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
由于没有exp_full_database，所以这样的导出会报错，网站空间，你可以选择授权或者使用有权限的用户
[oracle@asm11g ~]$ expdp scott/tiger dumpfile=emp.dmp tables=emp
Export: Release 11.2.0.3.0 - Production on Fri Nov 16 13:48:19 2012
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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39145: directory object parameter must be specified and non-null
[oracle@asm11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 13:58:14 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> grant exp_full_database to scott;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
授权之后，导出成功，并且导出的文件和日志会到DATA_PUMP_DIR对象指定的目录
[oracle@asm11g ~]$ expdp scott/tiger dumpfile=emp.dmp tables=emp
Export: Release 11.2.0.3.0 - Production on Fri Nov 16 13:58:52 2012
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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** dumpfile=emp.dmp tables=emp
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/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/tj01/dpdump/emp.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 13:59:05
对于oracle11g来说，我们还可以定义一个新的目录对象，对象名称是DATA_PUMP_DIR_SCHEMA_NAME,如果定义了这个目录对象，并授予权限，在用户导出没有指定
具体的目录参数的情况下，导出文件会到这个目录
[oracle@asm11g ~]$ mkdir sdir
[oracle@asm11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 14:13:06 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> create directory data_pump_dir_scott as '/home/oracle/sdir';
Directory created.
SQL> grant read,write on directory data_pump_dir_scott to scott;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@asm11g ~]$ expdp scott/tiger dumpfile=emp1.dmp tables=emp
Export: Release 11.2.0.3.0 - Production on Fri Nov 16 14:14:29 2012
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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Database Directory Object has defaulted to: "DATA_PUMP_DIR_SCOTT".
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** dumpfile=emp5.dmp tables=emp
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/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/sdir/emp1.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:14:34
二、如果设定了环境变量DATA_PUMP_DIR的话，环境变量会覆盖缺省设置
[oracle@asm11g ~]$ mkdir udir
[oracle@asm11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 13:59:15 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> create directory udir as '/home/oracle/udir';
Directory created.
SQL> grant write ,read on directory udir to scott;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
注意，在定义环境变量的时候，后面的值要用大写
[oracle@asm11g ~]$ export DATA_PUMP_DIR=udir
[oracle@asm11g ~]$ expdp scott/tiger dumpfile=emp2.dmp tables=emp
Export: Release 11.2.0.3.0 - Production on Fri Nov 16 14:05:07 2012
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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name udir is invalid
[oracle@asm11g ~]$ export DATA_PUMP_DIR=UDIR
[oracle@asm11g ~]$ expdp scott/tiger dumpfile=emp2.dmp tables=emp
Export: Release 11.2.0.3.0 - Production on Fri Nov 16 14:05:23 2012
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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Database Directory Object has defaulted to: "UDIR".
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** dumpfile=emp3.dmp tables=emp
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/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/udir/emp2.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:05:29
三、如果导出时定义了参数directory，会使用参数指定的目录
[oracle@asm11g ~]$ expdp scott/tiger directory=udir tables=emp
Export: Release 11.2.0.3.0 - Production on Fri Nov 16 14:07:33 2012
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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=udir tables=emp
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/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/udir/expdat.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:07:41
四、如果每个文件都指定了具体的目录的话，会以文件定义的目录为准
[oracle@asm11g ~]$ mkdir userdir
[oracle@asm11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 14:09:58 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> create or replace directory userdir as '/home/oracle/userdir';
Directory created.
SQL> grant read,write on directory userdir to scott;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@asm11g ~]$ expdp scott/tiger dumpfile=userdir:emp3.dmp logfile=userdir:test.log tables=emp
Export: Release 11.2.0.3.0 - Production on Fri Nov 16 14:12:38 2012
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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Database Directory Object has defaulted to: "UDIR".
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** dumpfile=userdir:emp4.dmp logfile=userdir:test.log tables=emp
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/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/userdir/emp3.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:12:44
oracle视频教程请关注:
MySQL：世界上最受欢迎的数据库的简介Apr 12, 2025 am 12:18 AMMySQL是一种开源的关系型数据库管理系统，主要用于快速、可靠地存储和检索数据。其工作原理包括客户端请求、查询解析、执行查询和返回结果。使用示例包括创建表、插入和查询数据，以及高级功能如JOIN操作。常见错误涉及SQL语法、数据类型和权限问题，优化建议包括使用索引、优化查询和分表分区。
MySQL的重要性：数据存储和管理Apr 12, 2025 am 12:18 AMMySQL是一个开源的关系型数据库管理系统，适用于数据存储、管理、查询和安全。1.它支持多种操作系统，广泛应用于Web应用等领域。2.通过客户端-服务器架构和不同存储引擎，MySQL高效处理数据。3.基本用法包括创建数据库和表，插入、查询和更新数据。4.高级用法涉及复杂查询和存储过程。5.常见错误可通过EXPLAIN语句调试。6.性能优化包括合理使用索引和优化查询语句。
为什么要使用mysql？利益和优势Apr 12, 2025 am 12:17 AM选择MySQL的原因是其性能、可靠性、易用性和社区支持。1.MySQL提供高效的数据存储和检索功能，支持多种数据类型和高级查询操作。2.采用客户端-服务器架构和多种存储引擎，支持事务和查询优化。3.易于使用，支持多种操作系统和编程语言。4.拥有强大的社区支持，提供丰富的资源和解决方案。
描述InnoDB锁定机制（共享锁，独家锁，意向锁，记录锁，间隙锁，下一键锁）。Apr 12, 2025 am 12:16 AMInnoDB的锁机制包括共享锁、排他锁、意向锁、记录锁、间隙锁和下一个键锁。1.共享锁允许事务读取数据而不阻止其他事务读取。2.排他锁阻止其他事务读取和修改数据。3.意向锁优化锁效率。4.记录锁锁定索引记录。5.间隙锁锁定索引记录间隙。6.下一个键锁是记录锁和间隙锁的组合，确保数据一致性。
MySQL查询性能差的常见原因是什么？Apr 12, 2025 am 12:11 AMMySQL查询性能不佳的原因主要包括没有使用索引、查询优化器选择错误的执行计划、表设计不合理、数据量过大和锁竞争。 1.没有索引导致查询缓慢，添加索引后可显着提升性能。 2.使用EXPLAIN命令可以分析查询计划，找出优化器错误。 3.重构表结构和优化JOIN条件可改善表设计问题。 4.数据量大时，采用分区和分表策略。 5.高并发环境下，优化事务和锁策略可减少锁竞争。
您什么时候应该使用复合索引与多个单列索引？Apr 11, 2025 am 12:06 AM在数据库优化中，应根据查询需求选择索引策略：1.当查询涉及多个列且条件顺序固定时，使用复合索引；2.当查询涉及多个列但条件顺序不固定时，使用多个单列索引。复合索引适用于优化多列查询，单列索引则适合单列查询。
如何识别和优化MySQL中的慢速查询？ （慢查询日志，performance_schema）Apr 10, 2025 am 09:36 AM要优化MySQL慢查询，需使用slowquerylog和performance_schema：1.启用slowquerylog并设置阈值，记录慢查询；2.利用performance_schema分析查询执行细节，找出性能瓶颈并优化。
MySQL和SQL：开发人员的基本技能Apr 10, 2025 am 09:30 AMMySQL和SQL是开发者必备技能。1.MySQL是开源的关系型数据库管理系统，SQL是用于管理和操作数据库的标准语言。2.MySQL通过高效的数据存储和检索功能支持多种存储引擎，SQL通过简单语句完成复杂数据操作。3.使用示例包括基本查询和高级查询，如按条件过滤和排序。4.常见错误包括语法错误和性能问题，可通过检查SQL语句和使用EXPLAIN命令优化。5.性能优化技巧包括使用索引、避免全表扫描、优化JOIN操作和提升代码可读性。
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
热工具
Atom编辑器mac版下载
最流行的的开源编辑器
SecLists
SecLists是最终安全测试人员的伙伴。它是一个包含各种类型列表的集合，这些列表在安全评估过程中经常使用，都在一个地方。SecLists通过方便地提供安全测试人员可能需要的所有列表，帮助提高安全测试的效率和生产力。列表类型包括用户名、密码、URL、模糊测试有效载荷、敏感数据模式、Web shell等等。测试人员只需将此存储库拉到新的测试机上，他就可以访问到所需的每种类型的列表。
DVWA
Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序，非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具，帮助Web开发人员更好地理解保护Web应用程序的过程，并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞，难度各不相同。请注意，该软件中
SublimeText3 Linux新版
SublimeText3 Linux最新版
EditPlus 中文破解版
体积小，语法高亮，不支持代码提示功能
