- 浏览: 2182627 次
文章分类
最新评论
Exporting and Importing Objects Using Oracle Data Pump(1.expdp)
1.查看数据泵的目录对象,目录对象是数据泵导出数据时存放的文件路径:
SQL> SELECT * FROM dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ------------------------------ ------------------------------ -------------------------------------------------------------------------------- SYS ADMIN_DIR /ade/aime_10.2_lnx_push/oracle/md/admin SYS DATA_PUMP_DIR /home/oracle/oracle/product/10.2.0/db_1/rdbms/log/ SYS WORK_DIR /ade/aime_10.2_lnx_push/oracle/work
只有SYS或者SYSTEM用户才能使用默认DATA_PUMP_DIR目录对象
所以SYSTEM用户可以启用数据泵导出作业,而不需要提供目录名。
[oracle@localhost ~]$ expdp system/lubinsu Export: Release 10.2.0.1.0 - Production on Wednesday, 01 May, 2013 17:04:18 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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 320 KB Processing object type SCHEMA_EXPORT/USER 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 Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC Processing object type SCHEMA_EXPORT/DB_LINK Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/PRE_TABLE_ACTION Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/VIEW/GRANT/CROSS_SCHEMA/OBJECT_GRANT Processing object type SCHEMA_EXPORT/VIEW/COMMENT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA . . exported "SYSTEM"."REPCAT$_AUDIT_ATTRIBUTE" 5.953 KB 2 rows . . exported "SYSTEM"."REPCAT$_OBJECT_TYPES" 6.507 KB 28 rows . . exported "SYSTEM"."REPCAT$_RESOLUTION_METHOD" 5.648 KB 19 rows . . exported "SYSTEM"."REPCAT$_TEMPLATE_STATUS" 5.296 KB 3 rows . . exported "SYSTEM"."REPCAT$_TEMPLATE_TYPES" 5.914 KB 2 rows . . exported "SYSTEM"."DEF$_AQCALL" 0 KB 0 rows . . exported "SYSTEM"."DEF$_AQERROR" 0 KB 0 rows . . exported "SYSTEM"."DEF$_CALLDEST" 0 KB 0 rows . . exported "SYSTEM"."DEF$_DEFAULTDEST" 0 KB 0 rows . . exported "SYSTEM"."DEF$_DESTINATION" 0 KB 0 rows . . exported "SYSTEM"."DEF$_ERROR" 0 KB 0 rows . . exported "SYSTEM"."DEF$_LOB" 0 KB 0 rows . . exported "SYSTEM"."DEF$_ORIGIN" 0 KB 0 rows . . exported "SYSTEM"."DEF$_PROPAGATOR" 0 KB 0 rows . . exported "SYSTEM"."DEF$_PUSHED_TRANSACTIONS" 0 KB 0 rows . . exported "SYSTEM"."DEF$_TEMP$LOB" 0 KB 0 rows . . exported "SYSTEM"."LOGSTDBY$APPLY_MILESTONE" 0 KB 0 rows . . exported "SYSTEM"."LOGSTDBY$APPLY_PROGRESS":"P0" 0 KB 0 rows . . exported "SYSTEM"."LOGSTDBY$EVENTS" 0 KB 0 rows . . exported "SYSTEM"."LOGSTDBY$HISTORY" 0 KB 0 rows . . exported "SYSTEM"."LOGSTDBY$PARAMETERS" 0 KB 0 rows . . exported "SYSTEM"."LOGSTDBY$PLSQL" 0 KB 0 rows . . exported "SYSTEM"."LOGSTDBY$SCN" 0 KB 0 rows . . exported "SYSTEM"."LOGSTDBY$SKIP" 0 KB 0 rows . . exported "SYSTEM"."LOGSTDBY$SKIP_TRANSACTION" 0 KB 0 rows . . exported "SYSTEM"."MVIEW$_ADV_INDEX" 0 KB 0 rows . . exported "SYSTEM"."MVIEW$_ADV_PARTITION" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_AUDIT_COLUMN" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_COLUMN_GROUP" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_CONFLICT" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_DDL" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_EXCEPTIONS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_EXTENSION" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_FLAVORS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_FLAVOR_OBJECTS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_GENERATED" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_GROUPED_COLUMN" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_INSTANTIATION_DDL" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_KEY_COLUMNS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_OBJECT_PARMS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_PARAMETER_COLUMN" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_PRIORITY" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_PRIORITY_GROUP" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_REFRESH_TEMPLATES" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_REPCAT" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_REPCATLOG" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_REPCOLUMN" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_REPGROUP_PRIVS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_REPOBJECT" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_REPPROP" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_REPSCHEMA" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_RESOLUTION" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_RESOLUTION_STATISTICS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_RESOL_STATS_CONTROL" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_RUNTIME_PARMS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_SITES_NEW" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_SITE_OBJECTS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_SNAPGROUP" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB 0 rows . . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB 0 rows . . exported "SYSTEM"."SERV_MSG":"P_CZ" 0 KB 0 rows . . exported "SYSTEM"."SERV_MSG":"P_HA" 0 KB 0 rows . . exported "SYSTEM"."SERV_MSG":"P_LYG" 0 KB 0 rows . . exported "SYSTEM"."SERV_MSG":"P_NJ" 0 KB 0 rows . . exported "SYSTEM"."SERV_MSG":"P_NT" 0 KB 0 rows . . exported "SYSTEM"."SERV_MSG":"P_PROVICE" 0 KB 0 rows . . exported "SYSTEM"."SERV_MSG":"P_SQ" 0 KB 0 rows . . exported "SYSTEM"."SERV_MSG":"P_SZ" 0 KB 0 rows . . exported "SYSTEM"."SERV_MSG":"P_TZ" 0 KB 0 rows . . exported "SYSTEM"."SERV_MSG":"P_WX" 0 KB 0 rows . . exported "SYSTEM"."SERV_MSG":"P_XZ" 0 KB 0 rows . . exported "SYSTEM"."SERV_MSG":"P_YC" 0 KB 0 rows . . exported "SYSTEM"."SERV_MSG":"P_YZ" 0 KB 0 rows . . exported "SYSTEM"."SERV_MSG":"P_ZJ" 0 KB 0 rows . . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /home/oracle/oracle/product/10.2.0/db_1/rdbms/log/expdat.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:04:57
其他用户需要授权使用目录对象:
1.创建目录对象:
[oracle@localhost ~]$ sqlplus system/lubinsu SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 1 17:07:28 2013 Copyright (c) 1982, 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 SQL> create directory dpump_dir01 as '/home/lubinsu/dpump'; Directory created.
2.授权:
SQL> grant read,write on directory dpump_dir01 to lubinsu; Grant succeeded.
3.导出:
[lubinsu@localhost oracle]$ expdp lubinsu/lubinsu DIRECTORY=dpump_dir01 dumpfile=lubinsu.dmp Export: Release 10.2.0.1.0 - Production on Wednesday, 01 May, 2013 17:12:38 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 ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 475 ORA-29283: invalid file operation
4.真实路径下的目录实际上并未创建,而oracle也不会自动创建该目录
创建真是目录并修改权限:
chmod 777 lubinsu
chmod 777dpump
将lubinsu用户下的dpump开放给其他用户
重新执行刚才的操作:
[lubinsu@localhost oracle]$ expdp lubinsu/lubinsu dumpfile=lubinsu.dmp directory=DPUMP_DIR05 Export: Release 10.2.0.1.0 - Production on Wednesday, 01 May, 2013 22:24:43 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 "LUBINSU"."SYS_EXPORT_SCHEMA_01": lubinsu/******** dumpfile=lubinsu.dmp directory=DPUMP_DIR05 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 128.6 MB Processing object type SCHEMA_EXPORT/USER 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 Processing object type SCHEMA_EXPORT/DB_LINK Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "LUBINSU"."T_TEST" 36.69 MB 393800 rows . . exported "LUBINSU"."GRID_MSG" 20.79 MB 156588 rows . . exported "LUBINSU"."BSS_PO_SPEC_D" 6.229 MB 26570 rows . . exported "LUBINSU"."MANAGER_TM" 3.885 MB 65099 rows . . exported "LUBINSU"."MANAGER_TM_T" 3.937 MB 65994 rows . . exported "LUBINSU"."AREA_TM" 1.786 MB 33105 rows . . exported "LUBINSU"."SERV_MSG":"P_CZ" 2.055 MB 10008 rows . . exported "LUBINSU"."SERV_MSG":"P_HA" 2.041 MB 10008 rows . . exported "LUBINSU"."SERV_MSG":"P_LYG" 1.991 MB 10008 rows . . exported "LUBINSU"."SERV_MSG":"P_NJ" 2.011 MB 10008 rows . . exported "LUBINSU"."SERV_MSG":"P_NT" 2.197 MB 10008 rows . . exported "LUBINSU"."SERV_MSG":"P_SQ" 1.996 MB 10008 rows . . exported "LUBINSU"."SERV_MSG":"P_SZ" 2.084 MB 10008 rows . . exported "LUBINSU"."SERV_MSG":"P_TZ" 1.951 MB 10008 rows . . exported "LUBINSU"."SERV_MSG":"P_WX" 2.059 MB 10008 rows . . exported "LUBINSU"."SERV_MSG":"P_XZ" 2.038 MB 10008 rows . . exported "LUBINSU"."SERV_MSG":"P_YC" 1.935 MB 10008 rows . . exported "LUBINSU"."SERV_MSG":"P_YZ" 1.819 MB 10008 rows . . exported "LUBINSU"."SERV_MSG":"P_ZJ" 1.927 MB 10008 rows . . exported "LUBINSU"."PROCEDURE_RESULT" 6.335 KB 15 rows . . exported "LUBINSU"."STAT_PROCEDURE" 46.99 KB 163 rows . . exported "LUBINSU"."TEACHER_VPN_STAT_DAILY" 80.25 KB 2377 rows . . exported "LUBINSU"."DEPT" 5.656 KB 4 rows . . exported "LUBINSU"."EMP" 7.820 KB 14 rows . . exported "LUBINSU"."LATN_AREA_INFO" 16.11 KB 14 rows . . exported "LUBINSU"."PO_SPEC_CATE_TREE_RELA" 24.95 KB 578 rows . . exported "LUBINSU"."TEACHER_VPN_STAT_DAILY_T" 9.937 KB 65 rows . . exported "LUBINSU"."SERV_MSG":"P_PROVINCE" 0 KB 0 rows Master table "LUBINSU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for LUBINSU.SYS_EXPORT_SCHEMA_01 is: /home/lubinsu/dpump/lubinsu.dmp Job "LUBINSU"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:24:58
相关推荐
Part 1: Overview and Configuration Understanding Project Management...........................................................21 Why Use Project Portfolio Management?.....................................
Cloning Oracle Service Bus Projects and Folders.......................................................................... 2-2 Creating Oracle Service Bus Configuration Projects...........................
Excel Importing & Exporting Text Data ---Quickly Turn Raw Data Into Excel Tables, By Scott Hartshorn, epub格式版。
CHAPTER 13 Importing, Exporting, Printing, and Reporting CHAPTER 14 Naming Standards CHAPTER 15 Compare/Merge Utility CHAPTER 16 Features for Agile Teams and Continuous Improvement APPENDIX A ...
1 What is Liferay? 1 1.1 Building a site with Liferay Web Content . . . . . . . . . . . . . . . . . 1 1.2 Using Liferay Portal as a collaborative platform . . . . . . . . . . . . . . 6 1.3 Using ...
1. a book Developing Flex Applications 2. a web page viewer for doc88 ebt 3. a DDA downloader for doc88.com CONTENTS PART I: Presenting Flex CHAPTER 1: Introducing Flex. . . . . . . . . . . . . . ...
Using Point objects. 434 Using Rectangle objects436 Using Matrix objects440 Example: Applying a matrix transformation to a display object.442 8 Chapter 14: Using the drawing API...447 Basics of using ...
Introduction to Word's XML Features An End-to-End Scenario: Creating a Schema and Mapping It into a Word Document Exporting the Mapped XML in the Document to an XML Data File ...
Introduction to Word's XML Features An End-to-End Scenario: Creating a Schema and Mapping It into a Word Document Exporting the Mapped XML in the Document to an XML Data File ...
About This Book...............................................................................................1 How This Book Is Organized.................................................................
Chapter 1 Basic Linux 3D Graphics Concepts ..................1 2D Graphics Fundamentals ......................................1 3D Graphics Fundamentals ......................................3 3D ...
1.1 SystemVerilog origins.......................................................................................................1 1.1.1 Generations of the SystemVerilog standard..........................
exporting table BOOK 1 rows exported . . exporting table CLASS 0 rows exported . . exporting table CUSTOMER 0 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting ...
NULL 博文链接:https://fengyonghui.iteye.com/blog/643507
Now the BLOB filed data is uploaded using base64 encoding. (For Delphi 2005/CBuilder 2006 versions and older). 4. QExport4XLS. Now the exported data can be splitted into Excel worksheets by the ...
Database Tour and Database Tour Pro are cross-database tools with large set of db tools and utilities like SQL tool, reporting tool, data converting, exporting, importing utilities and much more. ...
Illustrate your data in a more interactive way by implementing data visualization principles and creating visual stories using Tableau About This Book Use data visualization principles to help you to...
highchars图表用到的基础js,还有其他js需要用到的会一同上传
2.5. Importing and mixing configurations 2.5.1. Referencing XML configuration in JavaConfig 2.5.2. Referencing JavaConfig in XML configuration 2.6. Summary Chapter 3. Advanced wiring 3.1. Environments...
The meat of data analysis occurs in the topics involving statistical techniques, parallelism, concurrency, and machine learning algorithms, along with various examples of visualizing and exporting ...