EXPDP导出导入表结构不分配存储空间案例

0    53    1

👉 本文共约29243个字,系统预计阅读时间或需110分钟。

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

导读

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

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

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

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

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

⑤ linux中的批量替换sed命令

⑥ sqlldr和spool命令

实验环境介绍

源库:10.2.0.1 AIX

目标库:11.2.0.3 RHEL6.5

执行导出工作

确定需要导出的用户

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 NameDescriptionSee Also
ANONYMOUSEnables HTTP access to Oracle XML DB.Oracle XML DB Developer's Guide
APEX_030200The account owns the Application Express schema and metadata.Oracle Application Express Application Builder User's Guide
APEX_PUBLIC_USERThe minimally privileged account used for Application Express configuration with Oracle HTTP Server and mod_plsql.Oracle Application Express Application Builder User's Guide
APPQOSSYSUsed for storing and managing all data and metadata required by Oracle Quality of Service Management.None
BIThe 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
CTXSYSThe Oracle Text account.Oracle Text Reference
DBSNMPThe 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
DIPThe account used by the Directory Integration Platform (DIP) to synchronize the changes in Oracle Internet Directory with the applications in the database.None
DVSYSThere 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
EXFSYSThe account owns the Expression Filter schema.None
FLOWS_FILESThe account owns the Application Express uploaded files.Oracle Application Express Application Builder User's Guide
HRThe 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
IXThe 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
LBACSYSThe Oracle Label Security administrator account.Oracle Label Security Administrator's Guide
MDDATAThe schema used by Oracle Spatial for storing geocoder and router data.Oracle Spatial Developer's Guide
MDSYSThe Oracle Spatial and Oracle Multimedia Locator administrator account.Oracle Spatial Developer's Guide
MGMT_VIEWAn account used by Oracle Enterprise Manager Database Control.None
OEThe 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
ORDPLUGINSThe Oracle Multimedia user. Plug-ins supplied by Oracle and third-party plug-ins are installed in this schema.Oracle Multimedia Reference
ORDSYSThe Oracle Multimedia administrator account.Oracle Multimedia Reference
ORDDATAThis account contains the Oracle Multimedia DICOM data model.Oracle Multimedia DICOM Developer's Guide
OUTLNThe 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_OCMThis account contains the instrumentation for configuration collection used by the Oracle Configuration Manager.Oracle Configuration Manager Installation and Administration Guide
OWBSYSThe 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_AUDITThis 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
PMThe 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
SCOTTAn account used by Oracle sample programs and examples.Oracle Database Administrator's Guide
SHThe 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_SCHEMAThe account that stores the information views for the SQL/MM Still Image Standard.Oracle Multimedia Reference
SPATIAL_CSW_ADMIN_USRThe 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_USRThe 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
SYSThe account used to perform database administration tasks.Oracle Database Administrator's Guide
SYSMANThe account used to perform Oracle Enterprise Manager database administration tasks.Oracle Enterprise Manager Grid Control Installation and Basic Configuration
SYSTEMAnother account used to perform database administration tasks.Oracle Database Administrator's Guide
WMSYSThe account used to store the metadata information for Oracle Workspace Manager.Oracle Database Workspace Manager Developer's Guide
XDBThe account used for storing Oracle XML DB data and metadata.Oracle XML DB Developer's Guide

10g下比较少:

CTXSYSCTXSYSThe Oracle Text accountOracle Text Reference
DBSNMPDBSNMPThe account used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the databaseOracle Enterprise Manager Grid Control Installation and Basic Configuration
LBACSYSLBACSYSThe Oracle Label Security administrator accountOracle Label Security Administrator's Guide
MDDATAMDDATAThe schema used by Oracle Spatial for storing Geocoder and router dataOracle Spatial User's Guide and Reference
MDSYSMDSYSThe Oracle Spatial and Oracle interMedia Locator administrator accountOracle Spatial User's Guide and Reference
DMSYSDMSYSThe Oracle Data Mining account.Oracle Data Mining Administrator's Guide
Oracle Data Mining Concepts
OLAPSYSMANAGERThe account used to create OLAP metadata structures. It owns the OLAP Catalog (CWMLite).Oracle OLAP Application Developer's Guide
ORDPLUGINSORDPLUGINSThe Oracle interMedia user. Plug-ins supplied by Oracle and third party format plug-ins are installed in this schema.Oracle interMedia User's Guide
ORDSYSORDSYSThe Oracle interMedia administrator accountOracle interMedia User's Guide
OUTLNOUTLNThe 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_SCHEMASI_INFORMTN_SCHEMAThe account that stores the information views for the SQL/MM Still Image StandardOracle interMedia User's Guide
SYSCHANGE_ON_INSTALLThe account used to perform database administration tasksOracle Database Administrator's Guide
SYSMANCHANGE_ON_INSTALLThe 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
SYSTEMMANAGERAnother account used to perform database administration tasks.Oracle Database Administrator's Guide

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

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 ;

EXPDP导出导入表结构不分配存储空间案例

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'))

;

EXPDP导出导入表结构不分配存储空间案例

由此可以知道,创建这些元数据大约需要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

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

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

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'));

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

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

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传递到目标端,我这里就直接传到我的虚拟机上吧。

执行导入工作

先建立相应的表空间

根据上边的工作我们知道需要在测试库上建立以下的表空间,我这里都设置的是20M,实际情况下应该根据上边查询出来的结果来设置相应的大小,表空间应用包含用户的默认表空间及用户下对象所在的表空间

create tablespace DWII_CNY_BK_F_01 datafile '+DATA' size 20M;

create tablespace DWII_DPA_F_01 datafile '+DATA' size 20M;

create tablespace DWII_DPA_I_01 datafile '+DATA' size 20M;

create tablespace DWII_DPA_S_01 datafile '+DATA' size 20M;

create tablespace DWII_SOR_F_01 datafile '+DATA' size 20M;

create tablespace DWII_SOR_I_01 datafile '+FRA' size 20M;

create tablespace DW_USER datafile '+FRA' size 20M;

create tablespace SQCHECK datafile '+FRA' size 20M;

create tablespace SD_CNY_D_01 datafile '+FRA' size 20M;

create tablespace SD_CNY_F_01 datafile '+FRA' size 20M;

create tablespace SD_DPA_D_01 datafile '+FRA' size 20M;

create tablespace SD_DPA_F_01 datafile '+FRA' size 20M;

create tablespace SD_SORT_T_01 datafile '+FRA' size 20M;

create tablespace DWII_FXDM_F_01 datafile '+FRA' size 20M;

create tablespace SD_SOR_T_01 datafile '+FRA' size 20M;

如果空间不够,我们可以追加数据文件:alter tablespace DWII_DPA_F_01 add datafile '+FRA' size 50M;

执行导入语句

一般情况下,如果存储够的话,我们把相应的表空间设置大一点之后这里直接执行导入语句就可以了,但是我是在本机的虚拟机里执行的,由前边的情况我们可以知道大约需要49G的空间,这个显然不太合适,哪该怎么办呢?我能想到的办法只有如下2种,如果大家还有好的办法可以给我留言。

① 在源库上修改表的定义后然后再执行导出命令

② 从已经导出来的dmp文件中抽取其中的DDL语句,然后将DDL语句导入到数据库中,update掉其中的STORAGE(INITIAL 参数后再将语句导出到sql文本中执行sql语句,这样可以解决表的定义问题。

③ 从已经导出来的dmp文件中抽取其中的DDL语句,然后利用linux的sed批量替换功能替换掉不正确的参数。

显然,第一种比较方便,也比较快,但是不实用,由于是生产库,参数不能随便修改,我们就采用第二或第三种办法,也可以多演示一种impdp的用法。

导出DMP文件中的DDL语句

我们在impdp的导入命令中添加sqlfile参数后执行导入并不会真正将数据导入到数据库,而会抽取出dmp文件中的DDL语句,如下:

[oracle@rhel6_lhr dpdump]$ impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log sqlfile=lhrsql20150515.sql

Import: Release 11.2.0.3.0 - Production on Fri May 15 15:08:03 2015

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

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

Starting "LHR"."SYS_SQL_FILE_FULL_01": lhr/******** directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log sqlfile=lhrsql20150515.sql

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

Job "LHR"."SYS_SQL_FILE_FULL_01" successfully completed at 15:21:50

[oracle@rhel6_lhr dpdump]$ ll lhrsql20150515.sql

-rw-r--r-- 1 oracle asmadmin 65707967 May 15 15:21 lhrsql20150515.sql

[oracle@rhel6_lhr dpdump]$ more lhrsql20150515.sql

-- CONNECT LHR

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

-- new object type path: SCHEMA_EXPORT/USER

-- CONNECT SYSTEM

CREATE USER "CNY_ETL" IDENTIFIED BY VALUES '4686A1050F638F44'

DEFAULT TABLESPACE "DW_USER"

TEMPORARY TABLESPACE "TEMP";

CREATE USER "LCM2" IDENTIFIED BY VALUES '48BCFDF435352212'

DEFAULT TABLESPACE "DWII_SOR_F_01"

TEMPORARY TABLESPACE "TEMP";

CREATE USER "FXDM_ETL" IDENTIFIED BY VALUES 'EA010AEA839BFA14'

DEFAULT TABLESPACE "DW_USER"

TEMPORARY TABLESPACE "TEMP";

CREATE USER "NRDM_ETL" IDENTIFIED BY VALUES '54A4A046AEE8B31E'

DEFAULT TABLESPACE "DW_USER"

TEMPORARY TABLESPACE "TEMP";

CREATE USER "NRDM" IDENTIFIED BY VALUES '1AE3DF7368DF560D'

DEFAULT TABLESPACE "SD_CNY_F_01"

TEMPORARY TABLESPACE "TEMP";

CREATE USER "DW_ETL" IDENTIFIED BY VALUES '91635F9C0744E7EC'

DEFAULT TABLESPACE "DW_USER"

TEMPORARY TABLESPACE "TEMP";

CREATE USER "SOR" IDENTIFIED BY VALUES 'BA3A6C912E6BFF14'

DEFAULT TABLESPACE "DWII_SOR_F_01"

TEMPORARY TABLESPACE "TEMP";

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。省略

[oracle@rhel6_lhr dpdump]$ tail -n 50 lhrsql20150515.sql

c := 'SPOT_EXCHNG_RATE_SRC';

EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,

2,.5,2,156,0,2.65784513562818E+35,2.65784513872303E+35,5,0,nv,nv,

TO_DATE('2015-05-14 22:00:18',df),'33303031','33303032',nv,2,nv;

c := 'SPOT_EXCHNG_RATE_SRC_NM';

EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,

2,.5,2,156,0,1.18758942587854E+36,1.20817519861257E+36,12,0,nv,nv,

TO_DATE('2015-05-14 22:00:18',df),'E4B8ADE997B4E4BBB7','E8AFA2E4BBB7E59D87E580BC',nv,2,nv;

c := 'FLEG_SWAP_PNT';

EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,

14,.0714285714285714,14,156,0,-64.01,16.5,4,0,nv,nv,

TO_DATE('2015-05-14 22:00:18',df),'3E256466','C11133',nv,2,nv;

c := 'SRC_SYS_LBL';

EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,

1,1,1,156,0,3.44097282552972E+35,3.44097282552972E+35,5,0,nv,nv,

TO_DATE('2015-05-14 22:00:18',df),'42454E4D','42454E4D',nv,2,nv;

END;

/

DECLARE

c varchar2(60);

nv varchar2(1);

df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';

s varchar2(60) := 'DPA';

t varchar2(60) := 'BNCHMK_OPTN_DLT_PARAM_F';

p varchar2(1);

sp varchar2(1);

stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';

BEGIN

NULL;

c := 'CRT_TMST';

EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,

1,1,1,156,0,2457157.57244213,2457157.57244213,11,0,nv,nv,

TO_DATE('2015-05-14 22:00:18',df),'7873050E0E2D14','7873050E0E2D14',nv,2,nv;

c := 'PPLN_WKDT';

EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,

1,1,1,156,0,20150422,20150422,6,0,nv,nv,

TO_DATE('2015-05-14 22:00:18',df),'C415100517','C415100517',nv,2,nv;

c := 'PPLN_TMST';

EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,

1,1,1,156,0,2457157.57244213,2457157.57244213,11,0,nv,nv,

TO_DATE('2015-05-14 22:00:18',df),'7873050E0E2D14','7873050E0E2D14',nv,2,nv;

DBMS_STATS.IMPORT_TABLE_STATS('"DPA"','"BNCHMK_OPTN_DLT_PARAM_F"',NULL,'"IMPDP_STATS"',NULL,NULL,'"SYS"');

DELETE FROM "SYS"."IMPDP_STATS";

END;

/

[oracle@rhel6_lhr dpdump]$

可以看到生成的SQL语句开始部分是ddl的建用户语句,最后是导入表的统计信息部分。

处理导出来的ddl文件

方法一,利用sed命令批量替换 (推荐)

这一步其实如果linux的sed命令熟悉的话可以直接替换掉sql语句中的相关行,我今天也是临时网上搜了下,经过大量实验,然后居然成功了,如下:

[oracle@rhel6_lhr dpdump]$ sed 's/\^ STORAGE(INITIAL.*/STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645/g' lhrsql20150515.sql > lhrsql20150515.sql_bk2

[oracle@rhel6_lhr dpdump]$ more lhrsql20150515.sql_bk2 | grep "STORAGE(INITIAL"

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

[oracle@rhel6_lhr dpdump]$ more lhrsql20150515.sql_bk2 | grep "STORAGE(INITIAL 131072"

[oracle@rhel6_lhr dpdump]$ more lhrsql20150515.sql | grep "STORAGE(INITIAL 131072"

STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

。。。。。。。。。。。。。

可以看到替换已经成功,如果不熟悉该命令,那么先熟悉该命令就麻烦了。

方法二:sqlldr导入到数据库,利用update来更新

都是搞数据库的,这样做虽然麻烦点,但是绝对不会出错,而且也是一种技巧,如果有的文本很大又很难处理的话我们就可以导入到数据库中,然后处理。

首先建表:

create table imp_sql_lhr (id number ,text varchar2(4000)) ;

sqlldr的控制文件内容:sqlldr_table.ctl :

UNRECOVERABLE

load data

LENGTH CHARACTER

infile 'lhrsql20150515.sql'

APPEND imp_sql_lhr

trailing nullcols

(

id SEQUENCE(1,1),

text char(4000) "TRIM(:text)"

)

sqlldr命令:

sqlldr lhr/lhr control=sqlldr_table.ctl log=a.log parallel=y readsize=4194304 streamsize=10485760 date_cache=5000 direct=true

导入到数据库后,我们就可以非常方便的来处理表中的数据了,如下:

EXPDP导出导入表结构不分配存储空间案例

SELECT * FROM imp_sql_lhr a where a.text like '%STORAGE(INITIAL%' and a.text not like '%STORAGE(INITIAL 65536%' ; EXPDP导出导入表结构不分配存储空间案例

执行更新语句:

UPDATE imp_sql_lhr t

SET t.text = 'STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645'

WHERE t.text LIKE '%STORAGE(INITIAL%'

AND t.text NOT LIKE '%STORAGE(INITIAL 65536 %';

最后利用spool来导出到sql文本中:

set echo on

set trimspool on

set trimout on

set linesize 4000

set pagesize 0

set sqlblanklines on

set feedback off

set serveroutput off

set term off

set echo off

spool lhrsql20150515.sql_bk2

SELECT t.text from imp_sql_lhr t order by id;

spool off

执行处理好的sql语句

该步骤比较简单,就是把处理好的ddl语句提前执行一下,让数据库中包含相应的对象,这样再执行impdp导入的时候就不会再创建这些表了。

需要注意的是:DDL语句中创建存过、函数、包的语句中是不包含对象所属的schema的,这样的话如果那个用户执行sql脚本的话,这些对象就创建在那个用户下了,这个显然是错误的,不是我们期望的,那么如何处理这个问题呢,想了想,很简单的嘛,我们sql脚本执行完毕后,再执行一次impdp的命令就可以把这些对象重建,然后把错误的存过删除就可以了。

SELECT t.* from imp_sql_lhr t where t.text like 'CREATE PROCEDURE%' order by id;

EXPDP导出导入表结构不分配存储空间案例

[oracle@rhel6_lhr dpdump]$ sqlplus lhr/lhr

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 15 16:32:08 2015

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

16:32:09 SQL> set echo off;

16:32:21 SQL> set serveroutput off;

16:32:30 SQL> set timing on;

16:32:40 SQL> set time on;

16:32:45 SQL> set timing off;

16:32:50 SQL> set time off;

SQL>

SQL>

SQL>

SQL> @lhrsql20150515.sql_bk2;

Session altered.

Session altered.

Session altered.

。。。。。。。。。。。。。。。。。。。。。。。。。。。 省略

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

impdp继续导入

[oracle@rhel6_lhr dpdump]$ impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=lhrsql20150515_imp.log parallel=4;

Import: Release 11.2.0.3.0 - Production on Fri May 15 19:05:29 2015

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

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

Starting "LHR"."SYS_IMPORT_FULL_02": lhr/******** directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=lhrsql20150515_imp.log parallel=4

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"CNY_ETL" already exists

ORA-31684: Object type USER:"LCM2" already exists

ORA-31684: Object type USER:"FXDM_ETL" already exists

ORA-31684: Object type USER:"NRDM_ETL" already exists

ORA-31684: Object type USER:"NRDM" already exists

ORA-31684: Object type USER:"DW_ETL" already exists

ORA-31684: Object type USER:"SOR" already exists

ORA-31684: Object type USER:"DWUSER1" already exists

ORA-31684: Object type USER:"DWII_ETL" already exists

ORA-31684: Object type USER:"FXDM" already exists

ORA-31684: Object type USER:"TEST1" already exists

ORA-31684: Object type USER:"LHR" already exists

ORA-31684: Object type USER:"ONL1" already exists

ORA-31684: Object type USER:"CNYDM" already exists

ORA-31684: Object type USER:"DPA" already exists

ORA-31684: Object type USER:"DWUSER" already exists

ORA-31684: Object type USER:"SQCHECK" already exists

ORA-31684: Object type USER:"TEST" already exists

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

ORA-31684: Object type DB_LINK:"SOR"."COG_DB" already exists

ORA-31684: Object type DB_LINK:"DPA"."COG_DB" already exists

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

ORA-31684: Object type SEQUENCE:"SOR"."SEQ_BDS_CL_IP_RL_ID" already exists

ORA-31684: Object type SEQUENCE:"SOR"."SEQ_GOLD_RATE_INFO" already exists

ORA-31684: Object type SEQUENCE:"SOR"."SEQ_MBR_API_ELGBLTY" already exists

ORA-31684: Object type SEQUENCE:"SOR"."SEQ_MBR_MKT_ELGBLTY" already exists

ORA-31684: Object type SEQUENCE:"SOR"."SEQ_MBR_MMKNG_ROLE" already exists

ORA-31684: Object type SEQUENCE:"SOR"."SEQ_ORG_CD_HSTRY" already exists

ORA-31684: Object type SEQUENCE:"SOR"."SQ_DW_IP_IP_RL" already exists

ORA-31684: Object type SEQUENCE:"SOR"."SQ_MKT_CLNDR_ID" already exists

ORA-31684: Object type SEQUENCE:"FXDM"."SEQ_GOLD_MBR_MMKNG_ROLE_D" already exists

ORA-31684: Object type SEQUENCE:"FXDM"."SEQ_GOLD_MINAMNT_CONFIG" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SEQ_BDS_MBR_D_ID" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SEQ_DL_MKT_INFO" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SEQ_GOLD_RATE_INFO" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SEQ_MBR_AUTH_BY_API_D" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SEQ_MBR_MMKNG_ROLE_D" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SEQ_ORG_D" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SQ_DW_BEST_QUOTE" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SQ_DW_BOND_MID_QUOTE_ID" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SQ_DW_MEMBER_D_ID" already exists

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39151: Table "DPA"."FX_DL_MKT_INFO" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_FWD_CPI_QT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_FWD_DL_BY_SF_CP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_FWD_QT_BY_DIR" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_SPOT_CPI_QT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_SPOT_DL_BY_SF_CP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_SPOT_QT_BY_DIR" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_SWAP_CPI_QT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_SWAP_DL_BY_SF_CP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_SWAP_DL_LEG_MKT_INFO" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_SWAP_QT_BY_DIR" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."BOND_TURNOVER_RATE_F" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."MONI_TORT_CRCLTN_AMNT_F" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."MONI_TURNOVER_RATE_F" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "SOR"."BST_QT_HIST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "SOR"."BST_QT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

。。。。。。。。。。。。。。。。。。。。。。。。。省略

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_BOND_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_FX_FWD_SWAP_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_CCS_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_CREPO_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_FCO_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_FRA_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_IBO_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_IRS_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_RIBD_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."FX_BM_FXO_DL_DAY_HIST" creation failed

Job "LHR"."SYS_IMPORT_FULL_02" completed with 2165 error(s) at 19:16:19

[oracle@rhel6_lhr dpdump]$

[oracle@rhel6_lhr dpdump]$

[oracle@rhel6_lhr dpdump]$

删除错误用户下的包、存过和函数

执行如下的脚本来删除相应的错误对象:

BEGIN

FOR CUR IN (SELECT 'DROP ' || D.OBJECT_TYPE || ' ' || D.OBJECT_NAME SQLT

FROM dba_objects d

WHERE d.OWNER = 'LHR'

AND d.CREATED >=

to_date('2015-05-15 09:25:27',

'YYYY-MM-DD HH24:MI:SS')

AND D.OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE', 'PACKAGE')) LOOP

EXECUTE IMMEDIATE CUR.SQLT;

END LOOP;

END;

数据校验

执行如下脚本和源库作比较,查看数据是否完整。

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'));

总结

到此所有的处理算是基本完毕,过程很简单,但是不同的场景处理方式有很多种,我们应该学会灵活变通,核心即expdp和impdp但是需要做很多的处理。

本文后传

由于表的storage参数存储很大,导致不能导入到测试库,我提出了2种办法,但是今天经过网友的提醒说是有一个参数TRANSFORM可以解决这个问题,于是就研究了一下这个参数,发现大牛的一篇文章,http://blog.itpub.net/26736162/viewspace-1662276/ ,然后自己又去官网查看了这个参数的详细解释,颇有收获,现分享给大家。

[oracle@rhel6_lhr dpdump]$ impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log TRANSFORM=storage:n TRANSFORM=SEGMENT_CREATION:n

Import: Release 11.2.0.3.0 - Production on 星期二 5月 19 15:51:50 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

已成功加载/卸载了主表 "LHR"."SYS_IMPORT_FULL_01"

启动 "LHR"."SYS_IMPORT_FULL_01": lhr/******** directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log TRANSFORM=storage:n TRANSFORM=SEGMENT_CREATION:n

处理对象类型 SCHEMA_EXPORT/USER

ORA-31684: 对象类型 USER:"LHR" 已存在

处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT

处理对象类型 SCHEMA_EXPORT/ROLE_GRANT

处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE

处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

处理对象类型 SCHEMA_EXPORT/DB_LINK

处理对象类型 SCHEMA_EXPORT/SEQUENCE/SEQUENCE

处理对象类型 SCHEMA_EXPORT/TABLE/TABLE

处理对象类型 SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX

处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT

处理对象类型 SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

处理对象类型 SCHEMA_EXPORT/FUNCTION/FUNCTION

处理对象类型 SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT

处理对象类型 SCHEMA_EXPORT/PROCEDURE/PROCEDURE

处理对象类型 SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT

处理对象类型 SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

处理对象类型 SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

处理对象类型 SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_STAT_MAKER_AWARD_FX" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_FXDM_FX_MBR_MMKNG_FX_F" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"DPA"."TEST" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"DPA"."SP_FX_SF_CP_RL_D_SUM" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_FXDM_FX_MBR_MMKNG_AMNT_F_T" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_STAT_MAKER" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_FXDM_FX_MBR_MMKNG_F" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_FX_MBR_MMKNG_ROLE_MTH_H_N" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"TEST"."GBP_ZUOSHISHANG" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"DPA"."SP_GOLD_MBR_MMKNG_NGTN_F" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"DPA"."I_DW_SP_RPT_MMKT_NET_INFO_CNY" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"DPA"."I_DW_SP_RPT_MMKT_NET_INFO_FX" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"TEST"."NZD_ZUOSHISHANG" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_GOLD_MBR_MMKNG_NGTN_F_OLD" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_GOLD_MBR_MMKNG_NGTN_F_BK" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_STAT_MAKER_OPTION" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_STAT_MAKER_OPTION_N" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_STAT_MAKER_N" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"SOR"."BST_QT_ARCHIVE" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"SOR"."FX_BST_QT_ARCHIVE" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_HANDREPORT_MAKE_JPYAUD" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"TEST"."USD_ZUOSHISHANG" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_GOLD_MBR_MMKNG_NGTN_F" 已创建, 但带有编译警告

处理对象类型 SCHEMA_EXPORT/VIEW/VIEW

处理对象类型 SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT

ORA-39082: 对象类型 VIEW:"DPA"."V_IRS_OFST_DTLS_F" 已创建, 但带有编译警告

处理对象类型 SCHEMA_EXPORT/VIEW/COMMENT

处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

作业 "LHR"."SYS_IMPORT_FULL_01" 已经完成, 但是有 25 个错误 (于 15:59:01 完成)

[oracle@rhel6_lhr ~]$ ORACLE_SID=ora11g

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 5月 19 15:45:48 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

已连接到空闲例程。

15:45:48 SQL> startup

ORACLE 例程已经启动。

Total System Global Area 409194496 bytes

Fixed Size 2228864 bytes

Variable Size 301993344 bytes

Database Buffers 96468992 bytes

Redo Buffers 8503296 bytes

数据库装载完毕。

数据库已经打开。

15:46:24 SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------------------------------

/u01/app/oracle/oradata/ora11g/system01.dbf

/u01/app/oracle/oradata/ora11g/sysaux01.dbf

/u01/app/oracle/oradata/ora11g/undotbs01.dbf

/u01/app/oracle/oradata/ora11g/users01.dbf

/u01/app/oracle/oradata/ora11g/example01.dbf

/u01/app/oracle/oradata/ora11g/aa.dbf

已选择6行。

已用时间: 00: 00: 00.00

15:47:13 SQL> create tablespace DWII_CNY_BK_F_01 datafile '/u01/app/oracle/oradata/ora11g/DWII_CNY_BK_F_01.dbf' size 10M;

create tablespace DWII_DPA_F_01 datafile '/u01/app/oracle/oradata/ora11g/DWII_DPA_F_01.dbf' size 10M;

create tablespace DWII_DPA_I_01 datafile '/u01/app/oracle/oradata/ora11g/DWII_DPA_I_01.dbf' size 10M;

create tablespace DWII_DPA_S_01 datafile '/u01/app/oracle/oradata/ora11g/DWII_DPA_S_01.dbf' size 10M;

create tablespace DWII_SOR_F_01 datafile '/u01/app/oracle/oradata/ora11g/DWII_SOR_F_01.dbf' size 10M;

create tablespace DWII_SOR_I_01 datafile '/u01/app/oracle/oradata/ora11g/DWII_SOR_I_01.dbf' size 10M;

create tablespace DW_USER datafile '/u01/app/oracle/oradata/ora11g/DW_USER.dbf' size 10M;

create tablespace SQCHECK datafile '/u01/app/oracle/oradata/ora11g/SQCHECK.dbf' size 10M;

create tablespace SD_CNY_D_01 datafile '/u01/app/oracle/oradata/ora11g/SD_CNY_D_01.dbf' size 10M;

create tablespace SD_CNY_F_01 datafile '/u01/app/oracle/oradata/ora11g/SD_CNY_F_01.dbf' size 10M;

create tablespace SD_DPA_D_01 datafile '/u01/app/oracle/oradata/ora11g/SD_DPA_D_01.dbf' size 10M;

create tablespace SD_DPA_F_01 datafile '/u01/app/oracle/oradata/ora11g/SD_DPA_F_01.dbf' size 10M;

create tablespace SD_SORT_T_01 datafile '/u01/app/oracle/oradata/ora11g/SD_SORT_T_01.dbf' size 10M;

create tablespace DWII_FXDM_F_01 datafile '/u01/app/oracle/oradata/ora11g/DWII_FXDM_F_01.dbf' size 10M;

表空间已创建。

已用时间: 00: 00: 01.01

15:49:42 SQL> create tablespace SD_SOR_T_01 datafile '/u01/app/oracle/oradata/ora11g/SD_SOR_T_01.dbf' size 10M;

表空间已创建。

已用时间: 00: 00: 00.60

15:49:42 SQL>

表空间已创建。

已用时间: 00: 00: 00.45

15:49:43 SQL>

表空间已创建。

已用时间: 00: 00: 00.35

15:49:43 SQL>

表空间已创建。

已用时间: 00: 00: 00.47

15:49:44 SQL>

表空间已创建。

已用时间: 00: 00: 00.62

15:49:44 SQL>

表空间已创建。

已用时间: 00: 00: 00.55

15:49:45 SQL>

表空间已创建。

已用时间: 00: 00: 00.61

15:49:45 SQL>

表空间已创建。

已用时间: 00: 00: 01.76

15:49:47 SQL>

表空间已创建。

已用时间: 00: 00: 00.59

15:49:48 SQL>

表空间已创建。

已用时间: 00: 00: 00.66

15:49:48 SQL>

表空间已创建。

已用时间: 00: 00: 00.60

15:49:49 SQL>

表空间已创建。

已用时间: 00: 00: 00.51

15:49:50 SQL>

表空间已创建。

已用时间: 00: 00: 00.49

15:49:50 SQL>

表空间已创建。

已用时间: 00: 00: 00.59

15:49:51 SQL>

15:49:59 SQL>

15:50:00 SQL>

15:50:00 SQL> show parameter DEFERRED_SEGMENT_CREATION

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

deferred_segment_creation boolean TRUE

15:55:06 SQL> SET TERMOUT OFF;

16:04:10 SQL> COLUMN current_instance NEW_VALUE current_instance NOPRINT;

16:04:10 SQL> SELECT rpad(instance_name, 17) current_instance FROM v$instance;

已用时间: 00: 00: 00.00

16:04:10 SQL> SET TERMOUT ON;

16:04:10 SQL>

16:04:10 SQL> PROMPT

16:04:10 SQL> PROMPT +------------------------------------------------------------------------+

+------------------------------------------------------------------------+

16:04:10 SQL> PROMPT | Report : Tablespaces |

| Report : Tablespaces |

16:04:10 SQL> PROMPT | Instance : \&current_instance |

| Instance : ora11g |

16:04:11 SQL> PROMPT +------------------------------------------------------------------------+

+------------------------------------------------------------------------+

16:04:11 SQL>

16:04:11 SQL> SET ECHO OFF

16:04:11 SQL> SET FEEDBACK 6

16:04:11 SQL> SET HEADING ON

16:04:11 SQL> SET LINESIZE 180

16:04:11 SQL> SET PAGESIZE 50000

16:04:11 SQL> SET TERMOUT ON

16:04:11 SQL> SET TIMING OFF

16:04:11 SQL> SET TRIMOUT ON

16:04:11 SQL> SET TRIMSPOOL ON

16:04:11 SQL> SET VERIFY OFF

16:04:11 SQL>

16:04:11 SQL> CLEAR COLUMNS

columns 已清除

16:04:11 SQL> CLEAR BREAKS

breaks 已清除

16:04:11 SQL> CLEAR COMPUTES

computes 已清除

16:04:11 SQL>

16:04:11 SQL> COLUMN status FORMAT a9 HEADING 'Status'

16:04:11 SQL> COLUMN name FORMAT a30 HEADING 'Tablespace Name'

16:04:11 SQL> COLUMN type FORMAT a15 HEADING 'TS Type'

16:04:11 SQL> COLUMN extent_mgt FORMAT a11 HEADING 'Extent Mgt.'

16:04:11 SQL> COLUMN ts_size FORMAT 9,999,999,999,999 HEADING 'Tablespace Size'

16:04:11 SQL> COLUMN used FORMAT 9,999,999,999,999 HEADING 'Used (in bytes)'

16:04:11 SQL> COLUMN free FORMAT 9,999,999,999,999 HEADING 'Free (in bytes)'

16:04:11 SQL> COLUMN pct_used FORMAT 999 HEADING 'Pct. Used'

16:04:11 SQL>

16:04:11 SQL> BREAK ON report

16:04:11 SQL>

16:04:11 SQL> COMPUTE sum OF ts_size ON report

16:04:11 SQL> COMPUTE sum OF used ON report

16:04:11 SQL> COMPUTE sum OF free ON report

16:04:11 SQL> COMPUTE avg OF pct_used ON report

16:04:11 SQL>

16:04:11 SQL> SELECT

16:04:11 2 d.status status

16:04:11 3 , d.tablespace_name name

16:04:11 4 , d.contents type

16:04:11 5 , d.extent_management extent_mgt

16:04:11 6 , NVL(a.bytes, 0) ts_size

16:04:11 7 , NVL(a.bytes - NVL(f.bytes, 0), 0) used

16:04:11 8 , NVL(f.bytes, 0) free

16:04:11 9 , NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) pct_used

16:04:11 10 FROM

16:04:11 11 sys.dba_tablespaces d

16:04:11 12 , ( select tablespace_name, sum(bytes) bytes

16:04:11 13 from dba_data_files

16:04:11 14 group by tablespace_name

16:04:11 15 ) a

16:04:11 16 , ( select tablespace_name, sum(bytes) bytes

16:04:11 17 from dba_free_space

16:04:11 18 group by tablespace_name

16:04:11 19 ) f

16:04:11 20 WHERE

16:04:11 21 d.tablespace_name = a.tablespace_name(+)

16:04:11 22 AND d.tablespace_name = f.tablespace_name(+)

16:04:11 23 AND NOT (

16:04:11 24 d.extent_management like 'LOCAL'

16:04:11 25 AND

16:04:11 26 d.contents like 'TEMPORARY'

16:04:11 27 )

16:04:11 28 UNION ALL

16:04:11 29 SELECT

16:04:11 30 d.status status

16:04:11 31 , d.tablespace_name name

16:04:12 32 , d.contents type

16:04:12 33 , d.extent_management extent_mg

16:04:12 34 , NVL(a.bytes, 0) ts_size

16:04:12 35 , NVL(t.bytes, 0) used

16:04:12 36 , NVL(a.bytes - NVL(t.bytes,0), 0) free

16:04:12 37 , NVL(t.bytes / a.bytes * 100, 0) pct_used

16:04:12 38 FROM

16:04:12 39 sys.dba_tablespaces d

16:04:12 40 , ( select tablespace_name, sum(bytes) bytes

16:04:12 41 from dba_temp_files

16:04:12 42 group by tablespace_name

16:04:12 43 ) a

16:04:12 44 , ( select tablespace_name, sum(bytes_cached) bytes

16:04:12 45 from v$temp_extent_pool

16:04:12 46 group by tablespace_name

16:04:12 47 ) t

16:04:12 48 WHERE

16:04:12 49 d.tablespace_name = a.tablespace_name(+)

16:04:12 50 AND d.tablespace_name = t.tablespace_name(+)

16:04:12 51 AND d.extent_management like 'LOCAL'

16:04:12 52 AND d.contents like 'TEMPORARY'

16:04:12 53 /

Status Tablespace Name TS Type Extent Mgt. Tablespace Size Used (in bytes) Free (in bytes) Pct. Used

--------- ------------------------------ --------------- ----------- ------------------ ------------------ ------------------ ---------

ONLINE AA PERMANENT LOCAL 5,242,880 1,114,112 4,128,768 21

ONLINE DWII_DPA_S_01 PERMANENT LOCAL 10,485,760 1,048,576 9,437,184 10

ONLINE SD_DPA_F_01 PERMANENT LOCAL 10,485,760 1,048,576 9,437,184 10

ONLINE SYSAUX PERMANENT LOCAL 587,202,560 562,298,880 24,903,680 96

ONLINE DW_USER PERMANENT LOCAL 10,485,760 1,048,576 9,437,184 10

ONLINE SD_CNY_D_01 PERMANENT LOCAL 10,485,760 1,048,576 9,437,184 10

ONLINE SD_DPA_D_01 PERMANENT LOCAL 10,485,760 1,048,576 9,437,184 10

ONLINE USERS PERMANENT LOCAL 100,925,440 13,697,024 87,228,416 14

ONLINE DWII_CNY_BK_F_01 PERMANENT LOCAL 10,485,760 1,048,576 9,437,184 10

ONLINE DWII_SOR_I_01 PERMANENT LOCAL 10,485,760 1,048,576 9,437,184 10

ONLINE SYSTEM PERMANENT LOCAL 807,403,520 803,733,504 3,670,016 100

ONLINE EXAMPLE PERMANENT LOCAL 362,414,080 325,189,632 37,224,448 90

ONLINE DWII_SOR_F_01 PERMANENT LOCAL 10,485,760 1,048,576 9,437,184 10

ONLINE SQCHECK PERMANENT LOCAL 10,485,760 1,048,576 9,437,184 10

ONLINE SD_SORT_T_01 PERMANENT LOCAL 10,485,760 1,048,576 9,437,184 10

ONLINE SD_SOR_T_01 PERMANENT LOCAL 10,485,760 1,048,576 9,437,184 10

ONLINE DWII_DPA_I_01 PERMANENT LOCAL 10,485,760 1,048,576 9,437,184 10

ONLINE SD_CNY_F_01 PERMANENT LOCAL 10,485,760 1,048,576 9,437,184 10

ONLINE DWII_FXDM_F_01 PERMANENT LOCAL 10,485,760 1,048,576 9,437,184 10

ONLINE DWII_DPA_F_01 PERMANENT LOCAL 10,485,760 1,048,576 9,437,184 10

ONLINE UNDOTBS1 UNDO LOCAL 298,844,160 298,844,160 0 100

ONLINE TEMP TEMPORARY LOCAL 87,031,808 85,983,232 1,048,576 99

------------------ ------------------ ------------------ ---------

avg 30

sum 2,406,350,848 2,106,589,184 299,761,664

已选择22行。

16:04:12 SQL>

这里尤其指出的是在11.2.0.2以上有个新增的参数,SEGMENT_CREATION,如果设置其为n的话,ddl语句就不包含SEGMENT CREATION IMMEDIATE字段。如下:
。。。。
"ISSUE_TYPE" NUMBER(6,0) DEFAULT 0,
"CSTDTN_BANK_ID" NUMBER(6,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
。。。。。。。

。。。。。。。
"ISSUE_TYPE" NUMBER(6,0) DEFAULT 0,
"CSTDTN_BANK_ID" NUMBER(6,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

。。。。。。。

官网地址:

http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL300

TRANSFORM

Default: There is no default

Purpose

Enables you to alter object creation DDL for objects being imported.

Syntax and Description

TRANSFORM = transform_name:value[:object_type]

The transform_name specifies the name of the transform. The possible options are as follows:

SEGMENT_ATTRIBUTES - If the value is specified as y, then segment attributes (physical attributes, storage attributes, tablespaces, and logging) are included, with appropriate DDL. The default is y.

STORAGE - If the value is specified as y, then the storage clauses are included, with appropriate DDL. The default is y. This parameter is ignored ifSEGMENT_ATTRIBUTES=n.

OID - If the value is specified as n, then the assignment of the exported OID during the creation of object tables and types is inhibited. Instead, a new OID is assigned. This can be useful for cloning schemas, but does not affect referenced objects. The default value is y.

PCTSPACE - The value supplied for this transform must be a number greater than zero. It represents the percentage multiplier used to alter extent allocations and the size of data files.

Note that you can use the PCTSPACE transform with the Data Pump Export SAMPLE parameter so that the size of storage allocations matches the sampled data subset. (See "SAMPLE".)

SEGMENT_CREATION - If set to y (the default), then this transform causes the SQL SEGMENT CREATION clause to be added to the CREATE TABLE statement. That is, the CREATE TABLE statement will explicitly say either SEGMENT CREATION DEFERRED or SEGMENT CREATION IMMEDIATE. If the value is n, then the SEGMENT CREATION clause is omitted from the CREATE TABLE statement. Set this parameter to n to use the default segment creation attributes for the table(s) being loaded. (This functionality is available starting with Oracle Database 11g release 2 (11.2.0.2).)

The type of value specified depends on the transform used. Boolean values (y/n) are required for the SEGMENT_ATTRIBUTES, STORAGE, and OID transforms. Integer values are required for the PCTSPACE transform.

The object_type is optional. If supplied, it designates the object type to which the transform will be applied. If no object type is specified, then the transform applies to all valid object types. The valid object types for each transform are shown in Table 3-1.

Table 3-1 Valid Object Types For the Data Pump Import TRANSFORM Parameter

SEGMENT_ATTRIBUTESSTORAGEOIDPCTSPACESEGMENT_CREATION
CLUSTERXXX
CONSTRAINTXXX
INC_TYPEX
INDEXXXX
ROLLBACK_SEGMENTXXX
TABLEXXXXX
TABLESPACEXX
TYPEX

Example

For the following example, assume that you have exported the employees table in the hr schema. The SQL CREATE TABLE statement that results when you then import the table is similar to the following:

CREATE TABLE "HR"."EMPLOYEES" ( "EMPLOYEE_ID" NUMBER(6,0), "FIRST_NAME" VARCHAR2(20), "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE, "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE, "PHONE_NUMBER" VARCHAR2(20), "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE, "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE, "SALARY" NUMBER(8,2), "COMMISSION_PCT" NUMBER(2,2), "MANAGER_ID" NUMBER(6,0), "DEPARTMENT_ID" NUMBER(4,0) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 10240 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ;

If you do not want to retain the STORAGE clause or TABLESPACE clause, then you can remove them from the CREATE STATEMENT by using the Import TRANSFORMparameter. Specify the value of SEGMENT_ATTRIBUTES as n. This results in the exclusion of segment attributes (both storage and tablespace) from the table.

> impdp hr TABLES=hr.employees DIRECTORY=dpump_dir1 DUMPFILE=hr_emp.dmp TRANSFORM=SEGMENT_ATTRIBUTES:n:table

The resulting CREATE TABLE statement for the employees table would then look similar to the following. It does not contain a STORAGE or TABLESPACE clause; the attributes for the default tablespace for the HR schema will be used instead.

CREATE TABLE "HR"."EMPLOYEES" ( "EMPLOYEE_ID" NUMBER(6,0), "FIRST_NAME" VARCHAR2(20), "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE, "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE, "PHONE_NUMBER" VARCHAR2(20), "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE, "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE, "SALARY" NUMBER(8,2), "COMMISSION_PCT" NUMBER(2,2), "MANAGER_ID" NUMBER(6,0), "DEPARTMENT_ID" NUMBER(4,0) );

As shown in the previous example, the SEGMENT_ATTRIBUTES transform applies to both storage and tablespace attributes. To omit only the STORAGE clause and retain the TABLESPACE clause, you can use the STORAGE transform, as follows:

> impdp hr TABLES=hr.employees DIRECTORY=dpump_dir1 DUMPFILE=hr_emp.dmp TRANSFORM=STORAGE:n:table

The SEGMENT_ATTRIBUTES and STORAGE transforms can be applied to all applicable table and index objects by not specifying the object type on the TRANSFORMparameter, as shown in the following command:

> impdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp SCHEMAS=hr TRANSFORM=SEGMENT_ATTRIBUTES:n

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

17 + 11 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部