zl程序教程

您现在的位置是:首页 >  后端

当前栏目

【数据泵】EXPDP导出表结构

导出数据 结构 EXPDP
2023-09-27 14:26:19 时间

【数据泵】EXPDP导出表结构(真实案例)

BLOG文档结构图

 

 

 

 

因工作需要现需要把一个生产库下的元数据(表定义,索引定义,函数定义,包定义,存储过程)导出到测试库上,本来以为很简单的,可是做的过程发现很多的问题,现记录如下,希望有同样需要的朋友不要再走弯路了。

 

 

  1. 导读

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:

① EXPDP和IMPDP如何导出导入元数据,包括表定义,索引定义,函数定义,包定义,存储过程(重点)

② 表的初始化定义参数initial,及如何批量修改该参数

③ 如何导出DMP文件中的DDL语句(重点)

④ 10g和11g默认情况下有哪些用户及其作用

⑤ linux中的批量替换sed命令

⑥ sqlldr和spool命令

 

 

 

本文如有错误或不完善的地方请大家多多指正,ITPUB留言或QQ皆可,您的批评指正是我写作的最大动力。

  1. 实验环境介绍

 

源库:10.2.0.1 AIX

目标库:11.2.0.3 RHEL6.5

 

  1. 执行导出工作

    1. 确定需要导出的用户

oracle安装好后有很多的系统默认用户,比如sys和system,对于这2个用户里的元数据我们就没有必要再重新导出嘛,不然导入的时候还提示错误,看着实在不好。

官网信息:

All databases created by the Database Configuration Assistant (DBCA) include the SYS, SYSTEM, SYSMAN, and DBSNMP database accounts. In addition, Oracle Database provides several other administrative accounts. Before using these accounts, you must unlock them and reset their passwords.

 

11g默认用户比较多:

User Name

Description

See Also

ANONYMOUS

Enables HTTP access to Oracle XML DB.

Oracle XML DB Developer's Guide

APEX_030200

The account owns the Application Express schema and metadata.

Oracle Application Express Application Builder User's Guide

APEX_PUBLIC_USER

The minimally privileged account used for Application Express configuration with Oracle HTTP Server and mod_plsql.

Oracle Application Express Application Builder User's Guide

APPQOSSYS

Used for storing and managing all data and metadata required by Oracle Quality of Service Management.

None

BI

The account that owns the Business Intelligence schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas.

Oracle Database Sample Schemas

CTXSYS

The Oracle Text account.

Oracle Text Reference

DBSNMP

The account used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database.

Oracle Enterprise Manager Grid Control Installation and Basic Configuration

DIP

The account used by the Directory Integration Platform (DIP) to synchronize the changes in Oracle Internet Directory with the applications in the database.

None

DVSYS

There are two roles associated with this account. The Database Vault owner role manages the Database Vault roles and configurations. The Database Vault Account Manager is used to manage database user accounts.

Oracle Database Vault Administrator's Guide

 

Note: Part of Oracle Database Vault user interface text is stored in database tables in the DVSYS schema. By default, only the English language is loaded into these tables. You can use Oracle Database Vault Configuration Assistant to add more languages to Oracle Database Vault. For the necessary steps, see Appendix C in Oracle Database Vault Administrator's Guide

EXFSYS

The account owns the Expression Filter schema.

None

FLOWS_FILES

The account owns the Application Express uploaded files.

Oracle Application Express Application Builder User's Guide

HR

The account that owns the Human Resources schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas.

Oracle Database Sample Schemas

IX

The account that owns the Information Transport schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas.

Oracle Database Sample Schemas

LBACSYS

The Oracle Label Security administrator account.

Oracle Label Security Administrator's Guide

MDDATA

The schema used by Oracle Spatial for storing geocoder and router data.

Oracle Spatial Developer's Guide

MDSYS

The Oracle Spatial and Oracle Multimedia Locator administrator account.

Oracle Spatial Developer's Guide

MGMT_VIEW

An account used by Oracle Enterprise Manager Database Control.

None

OE

The account that owns the Order Entry schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas.

Oracle Database Sample Schemas

ORDPLUGINS

The Oracle Multimedia user. Plug-ins supplied by Oracle and third-party plug-ins are installed in this schema.

Oracle Multimedia Reference

ORDSYS

The Oracle Multimedia administrator account.

Oracle Multimedia Reference

ORDDATA

This account contains the Oracle Multimedia DICOM data model.

Oracle Multimedia DICOM Developer's Guide

OUTLN

The account that supports plan stability. Plan stability enables you to maintain the same execution plans for the same SQL statements. OUTLN acts as a role to centrally manage metadata associated with stored outlines.

Oracle Database Concepts

ORACLE_OCM

This account contains the instrumentation for configuration collection used by the Oracle Configuration Manager.

Oracle Configuration Manager Installation and Administration Guide

OWBSYS

The account used by Oracle Warehouse Builder as its default repository. You must unlock this account after installing the Oracle Database and before launching the Warehouse Builder Repository Assistant.

Oracle Warehouse Builder Installation and Administration Guide

OWBSYS_AUDIT

This account is used by the Warehouse Builder Control Center Agent to access the heterogeneous execution audit tables in the OWBSYS schema.

Oracle Warehouse Builder Installation and Administration Guide

PM

The account that owns the Product Media schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas.

Oracle Database Sample Schemas

SCOTT

An account used by Oracle sample programs and examples.

Oracle Database Administrator's Guide

SH

The account that owns the Sales History schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas during an Enterprise Edition installation.

Oracle Database Administrator's Guide

SI_INFORMTN_SCHEMA

The account that stores the information views for the SQL/MM Still Image Standard.

Oracle Multimedia Reference

SPATIAL_CSW_ADMIN_USR

The Catalog Services for the Web (CSW) account. It is used by the Oracle Spatial CSW cache manager to load all record type metadata, and record instances from the database into the main memory for the record types that are cached.

Oracle Spatial Developer's Guide

SPATIAL_WFS_ADMIN_USR

The Web Feature Service (WFS) account. It is used by the Oracle Spatial WFS cache manager to load all feature-type metadata, and feature instances from the database into main memory for the feature types that are cached.

Oracle Spatial Developer's Guide

SYS

The account used to perform database administration tasks.

Oracle Database Administrator's Guide

SYSMAN

The account used to perform Oracle Enterprise Manager database administration tasks.

Oracle Enterprise Manager Grid Control Installation and Basic Configuration

SYSTEM

Another account used to perform database administration tasks.

Oracle Database Administrator's Guide

WMSYS

The account used to store the metadata information for Oracle Workspace Manager.

Oracle Database Workspace Manager Developer's Guide

XDB

The account used for storing Oracle XML DB data and metadata.

Oracle XML DB Developer's Guide

 

 

 

10g下比较少:

CTXSYS

CTXSYS

The Oracle Text account

Oracle Text Reference

DBSNMP

DBSNMP

The account used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database

Oracle Enterprise Manager Grid Control Installation and Basic Configuration

LBACSYS

LBACSYS

The Oracle Label Security administrator account

Oracle Label Security Administrator's Guide

MDDATA

MDDATA

The schema used by Oracle Spatial for storing Geocoder and router data

Oracle Spatial User's Guide and Reference

MDSYS

MDSYS

The Oracle Spatial and Oracle interMedia Locator administrator account

Oracle Spatial User's Guide and Reference

DMSYS

DMSYS

The Oracle Data Mining account.

Oracle Data Mining Administrator's Guide

 

Oracle Data Mining Concepts

OLAPSYS

MANAGER

The account used to create OLAP metadata structures. It owns the OLAP Catalog (CWMLite).

Oracle OLAP Application Developer's Guide

ORDPLUGINS

ORDPLUGINS

The Oracle interMedia user. Plug-ins supplied by Oracle and third party format plug-ins are installed in this schema.

Oracle interMedia User's Guide

ORDSYS

ORDSYS

The Oracle interMedia administrator account

Oracle interMedia User's Guide

OUTLN

OUTLN

The account that supports plan stability. Plan stability enables you to maintain the same execution plans for the same SQL statements. OUTLN acts as a role to centrally manage metadata associated with stored outlines.

Oracle Database Performance Tuning Guide

SI_INFORMTN_SCHEMA

SI_INFORMTN_SCHEMA

The account that stores the information views for the SQL/MM Still Image Standard

Oracle interMedia User's Guide

SYS

CHANGE_ON_INSTALL

The account used to perform database administration tasks

Oracle Database Administrator's Guide

SYSMAN

CHANGE_ON_INSTALL

The account used to perform Oracle Enterprise Manager database administration tasks. Note that SYS and SYSTEM can also perform these tasks.

Oracle Enterprise Manager Grid Control Installation and Basic Configuration

SYSTEM

MANAGER

Another account used to perform database administration tasks.

Oracle Database Administrator's Guide

 

 

  1. 确定需要导出的用户在哪些表空间,及其表初始化时占用的表空间大小

 

SELECT D.tablespace_name, SUM(D.initial_extent)/1024/1024 initial_extent

FROM DBA_SEGMENTS D

WHERE D.owner IN

(SELECT a.username

FROM DBA_USERS A

WHERE A.account_status = 'OPEN'

AND A.username NOT IN

('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'))

GROUP BY D.tablespace_name

ORDER BY initial_extent desc ;

 

 

SELECT SUM(D.initial_extent)/1024/1024 initial_extent

FROM DBA_SEGMENTS D

WHERE D.owner IN

(SELECT a.username

FROM DBA_USERS A

WHERE A.account_status = 'OPEN'

AND A.username NOT IN

('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'))

;

 

 

由此可以知道,创建这些元数据大约需要49G的空间,如果涉及到数据的话,还需要判断数据占用空间,这里一定需要判断这个,不然执行导入的时候会因为表空间不足而不能导入,我第一次导入的时候就是因为这里没有判断导致花费了很长的时间,我一直扩展表空间,但是就是就表空间不足的错误(ORA-01659),想想建表不会花这么大的空间的吧,最后查看了表的定义才知道,原来表初始化的时候就很大,这个问题后边还需要再处理一下的,不然测试库没法导入,当然存储够的话就另当别论了。

 

 

ORA-39171: Job is experiencing a resumable wait.

ORA-01659: unable to allocate MINEXTENTS beyond 4 in tablespace DWII_SOR_F_01

 

  1. 确定需要导出的用户中有哪些无效的对象、及总共需要导出的对象数量

 

这一步也很重要,决定着最终导出结果的正确性验证。

 

SELECT d.OWNER,count(1)

FROM dba_objects d

WHERE d.OWNER in (SELECT a.username

FROM DBA_USERS A

WHERE A.account_status = 'OPEN'

AND A.username NOT IN

('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'))

group by d.OWNER;

 

 

SELECT d.OWNER,

d.status,count(1)

FROM dba_objects d

WHERE d.OWNER in (SELECT a.username

FROM DBA_USERS A

WHERE A.account_status = 'OPEN'

AND A.username NOT IN

('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'))

group by d.OWNER, d.status;

 

SELECT d.OWNER,

d.OBJECT_NAME,

d.OBJECT_TYPE,

d.status

FROM dba_objects d

WHERE d.status = 'INVALID'

and d.owner in (SELECT a.username

FROM DBA_USERS A

WHERE A.account_status = 'OPEN'

AND A.username NOT IN

('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'));

 

 

  1. expdp数据泵利用content=metadata_only导出元数据

 

 

导出命令,注意这里不导出数据只导出定义我们采用content=metadata_only来处理:

expdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=lhrsql20150515.log content=metadata_only schemas=TEST,SQCHECK,DWUSER,DPA,CNYDM,ONL1,LHR,TEST1,FXDM,DWII_ETL,DWUSER1,SOR,DW_ETL,NRDM,NRDM_ETL,FXDM_ETL,LCM2,CNY_ETL

 

 

由于是事后写文档,所以这里只贴出导出元数据的日志:

 

;;;

Export: Release 10.2.0.1.0 - 64bit Production on Friday, 15 May, 2015 13:05:54

 

Copyright (c) 2003, 2005, Oracle. All rights reserved.

;;;

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Starting "LHR"."SYS_EXPORT_SCHEMA_01": lhr/******** directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=lhrsql20150515.log content=metadata_only schemas=TEST,SQCHECK,DWUSER,DPA,CNYDM,ONL1,LHR,TEST1,FXDM,DWII_ETL,DWUSER1,SOR,DW_ETL,NRDM,NRDM_ETL,FXDM_ETL,LCM2,CNY_ETL

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/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/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/FUNCTION/FUNCTION

Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

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/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Master table "LHR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for LHR.SYS_EXPORT_SCHEMA_01 is:

/oracle/product/10.2.0/db_1/rdbms/log/lhrsql20150515.dmp

Job "LHR"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:09:49

 

 

接下来就是把导出来的文件利用ftp工具或scp传递到目标端,我这里就直接传到我的虚拟机上吧。