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

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

BLOG文档结构图

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

1.1 导读

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

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

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

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

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

⑤ linux中的批量替换sed命令

⑥ sqlldr和spool命令

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

1.2 实验环境介绍

源库：10.2.0.1 AIX

目标库：11.2.0.3 RHEL6.5

1.3 执行导出工作

1.3.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.

APEX_030200 The account owns the Application Express schema and metadata.

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

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.

CTXSYS The Oracle Text account.

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.

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.

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.

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.

LBACSYS The Oracle Label Security administrator account.

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

MDSYS The Oracle Spatial and Oracle Multimedia Locator administrator account.

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.

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

ORDSYS The Oracle Multimedia administrator account.

ORDDATA This account contains the Oracle Multimedia DICOM data model.

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_OCM This account contains the instrumentation for configuration collection used by the Oracle Configuration Manager.

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.

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

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.

SCOTT An account used by Oracle sample programs and examples.

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.

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

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.

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.

SYS The account used to perform database administration tasks.

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.

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

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

10g下比较少：CTXSYS CTXSYS The Oracle Text account

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

LBACSYS LBACSYS The Oracle Label Security administrator account

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

MDSYS MDSYS The Oracle Spatial and Oracle interMedia Locator administrator account

DMSYS DMSYS The Oracle Data Mining account.

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

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

ORDSYS ORDSYS The Oracle interMedia administrator account

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.

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

SYS CHANGE_ON_INSTALL The account used to perform database administration tasks

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.

SYSTEM MANAGER Another account used to perform database administration tasks.

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

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