Exporting and Importing Objects Using Oracle Data Pump(1.expdp)

2024-01-15 10:32

本文主要是介绍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/lubinsuExport: Release 10.2.0.1.0 - Production on Wednesday, 01 May, 2013 17:04:18Copyright (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/lubinsuSQL*Plus: Release 10.2.0.1.0 - Production on Wed May 1 17:07:28 2013Copyright (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 optionsSQL> 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.dmpExport: Release 10.2.0.1.0 - Production on Wednesday, 01 May, 2013 17:12:38Copyright (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 777 dpump
将lubinsu用户下的dpump开放给其他用户

重新执行刚才的操作:
[lubinsu@localhost oracle]$ expdp lubinsu/lubinsu dumpfile=lubinsu.dmp directory=DPUMP_DIR05Export: Release 10.2.0.1.0 - Production on Wednesday, 01 May, 2013 22:24:43Copyright (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

成功导出

这篇关于Exporting and Importing Objects Using Oracle Data Pump(1.expdp)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/608586

相关文章

论文翻译:arxiv-2024 Benchmark Data Contamination of Large Language Models: A Survey

Benchmark Data Contamination of Large Language Models: A Survey https://arxiv.org/abs/2406.04244 大规模语言模型的基准数据污染:一项综述 文章目录 大规模语言模型的基准数据污染:一项综述摘要1 引言 摘要 大规模语言模型(LLMs),如GPT-4、Claude-3和Gemini的快

Oracle type (自定义类型的使用)

oracle - type   type定义: oracle中自定义数据类型 oracle中有基本的数据类型,如number,varchar2,date,numeric,float....但有时候我们需要特殊的格式, 如将name定义为(firstname,lastname)的形式,我们想把这个作为一个表的一列看待,这时候就要我们自己定义一个数据类型 格式 :create or repla

ORACLE 11g 创建数据库时 Enterprise Manager配置失败的解决办法 无法打开OEM的解决办法

在win7 64位系统下安装oracle11g,在使用Database configuration Assistant创建数据库时,在创建到85%的时候报错,错误如下: 解决办法: 在listener.ora中增加对BlueAeri-PC或ip地址的侦听,具体步骤如下: 1.启动Net Manager,在“监听程序”--Listener下添加一个地址,主机名写计

Oracle Start With关键字

Oracle Start With关键字 前言 旨在记录一些Oracle使用中遇到的各种各样的问题. 同时希望能帮到和我遇到同样问题的人. Start With (树查询) 问题描述: 在数据库中, 有一种比较常见得 设计模式, 层级结构 设计模式, 具体到 Oracle table中, 字段特点如下: ID, DSC, PID; 三个字段, 分别表示 当前标识的 ID(主键), DSC 当

oracle分页和mysql分页

mysql 分页 --查前5 数据select * from table_name limit 0,5 select * from table_name limit 5 --limit关键字的用法:LIMIT [offset,] rows--offset指定要返回的第一行的偏移量,rows第二个指定返回行的最大数目。初始行的偏移量是0(不是1)。   oracle 分页 --查前1-9

CentOS下mysql数据库data目录迁移

https://my.oschina.net/u/873762/blog/180388        公司新上线一个资讯网站,独立主机,raid5,lamp架构。由于资讯网是面向小行业,初步估计一两年内访问量压力不大,故,在做服务器系统搭建的时候,只是简单分出一个独立的data区作为数据库和网站程序的专区,其他按照linux的默认分区。apache,mysql,php均使用yum安装(也尝试

使用Spring Boot集成Spring Data JPA和单例模式构建库存管理系统

引言 在企业级应用开发中,数据库操作是非常重要的一环。Spring Data JPA提供了一种简化的方式来进行数据库交互,它使得开发者无需编写复杂的JPA代码就可以完成常见的CRUD操作。此外,设计模式如单例模式可以帮助我们更好地管理和控制对象的创建过程,从而提高系统的性能和可维护性。本文将展示如何结合Spring Boot、Spring Data JPA以及单例模式来构建一个基本的库存管理系统

ORACLE语法-包(package)、存储过程(procedure)、游标(cursor)以及java对Result结果集的处理

陈科肇 示例: 包规范 CREATE OR REPLACE PACKAGE PACK_WMS_YX IS-- Author : CKZ-- Created : 2015/8/28 9:52:29-- Purpose : 同步数据-- Public type declarations,游标 退休订单TYPE retCursor IS REF CURSOR;-- RETURN vi_co_co

Oracle主键和外键详解及实用技巧

在 Oracle 数据库中,主键(Primary Key)和外键(Foreign Key)用于维护数据库表之间的数据完整性。 1. 主键(Primary Key) 主键是一列或多列,能够唯一标识表中的每一行。表中只能有一个主键,并且主键列不能为空(即 NOT NULL)。 特性: 唯一性:主键中的每一个值都是唯一的,不能重复。非空性:主键列不能包含 NULL 值。索引:Oracle 自动为

15 组件的切换和对组件的data的使用

划重点 a 标签的使用事件修饰符组件的定义组件的切换:登录 / 注册 泡椒鱼头 :微辣 <!DOCTYPE html><html lang="en"><head><meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0"><meta http-equiv="X-UA-