impdp导入表结构和表数据_【impdp】IMPDP中的TRANSFORM参数--【数据泵】EXPDP导出表结构(真实案例)后传...

最新推荐文章于 2024-09-03 08:31:42 发布

大霞请留步

最新推荐文章于 2024-09-03 08:31:42 发布

阅读量319

点赞数

文章标签： impdp导入表结构和表数据

本文链接：https://blog.csdn.net/weixin_42366447/article/details/113386916

版权

[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

连接到: 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

已连接到空闲例程。

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 : ¤t_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

。。。。。。。

大霞请留步

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫