impdp导入表结构和表数据_impdp导入表不创建segments

1)资料获取

数据泵INCLUDE and EXCLUDE对象类型视图

Export/Import DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects (Doc ID 341733.1)­­~database_export_objects /schema_export_objects /table_export_objects2)进行导出

SQL> create directory dump as '/home/oracle/tools';

expdp'/ as sysdba' directory=dump dumpfile=system%u.dmp logfile=system.log SCHEMAS=system EXCLUDE=SEQUENCE,TRIGGER,REF_CONSTRAINT,CONSTRAINT,PROCDEPOBJ CONTENT=METADATA_ONLY cluster=n parallel

--导出元数据，加了并行，但实质只导出一个dump文件,说明导出元数据只能串行导出--附上导出日志，如果愿意，后缀的导出类型，均可过滤，保留表、索引即可，最小化原则处理需求

Starting"SYS"."SYS_EXPORT_SCHEMA_02": "/******** AS SYSDBA" directory=dump dumpfile=system%u.dmp logfile=system.log SCHEMAS=system

EXCLUDE=SEQUENCE,TRIGGER,REF_CONSTRAINT,CONSTRAINT,PROCDEPOBJ CONTENT=METADATA_ONLY cluster=n parallel=2Processingobject type SCHEMA_EXPORT/USER

Processingobject type SCHEMA_EXPORT/SYSTEM_GRANT

Processingobject type SCHEMA_EXPORT/ROLE_GRANT

Processingobject type SCHEMA_EXPORT/DEFAULT_ROLE

Processingobject type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processingobject type SCHEMA_EXPORT/SYNONYM/SYNONYM

Processingobject type SCHEMA_EXPORT/TYPE/TYPE_SPEC

Processingobject type SCHEMA_EXPORT/TABLE/TABLE

Processingobject type SCHEMA_EXPORT/TABLE/PRE_TABLE_ACTION

Processingobject type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processingobject type SCHEMA_EXPORT/TABLE/COMMENT

Processingobject type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processingobject type SCHEMA_EXPORT/FUNCTION/FUNCTION

Processingobject type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processingobject type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processingobject type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Processingobject type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processingobject type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processingobject type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processingobject type SCHEMA_EXPORT/VIEW/VIEW

Processingobject type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT

Processingobject type SCHEMA_EXPORT/VIEW/COMMENT

Processingobject type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processingobject type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processingobject type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION

Processingobject type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

Master table"SYS"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded******************************************************************************Dump fileset for SYS.SYS_EXPORT_SCHEMA_02 is:/home/oracle/tools/system01.dmp

Job"SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at Thu Nov 29 14:48:14 2018 elapsed 0 00:00

3)scp 省略4) 导入操作--docker 命令进行SQL*Plus

docker run-d --name "oradb" -v /home/dmuser/my_oracle_data:/u01/app/oracle IP:10082/sath89/oracle-xe-11g--正常环境，登陆服务器端，即可使用impdp工具，本次场景为docker 有些特殊--创建表空间--源端用户所在表空间

SQL> select tablespace_name from dba_segments where owner='SYSTEM'group by tablespace_name;

TABLESPACE_NAME------------------------------SYSAUX

SYSTEM--目标端创建表空间

crate tablesapce SYSTEM datafile'/u01/app/oracle/oradata/XE/system.dbf'size 10m uniform size 128k autoextend on next 30m maxsize 10g;

sqlplusassysdba

create directories abcas 'xxx';--导入

impdp'/ as sysdba' dumpfile=PICCPROD%U.dmp directory=dump logfile=picc.log REMAP_TABLESPACE=LIFELOB_DATA:LIFEDATA_T_L,LIFEINDEX_T_L:LIFEDATA_T_L

--导入报错

ORA-01658: unable to create INITIAL extent for segment intablespace SYSTEM--对system表空间进行扩容--再次导入报错

CREATE TABLE"PICCPROD"."T_POLICY_CHECK_ITEM" ("ITEM_ID" NUMBER(10,0) NOT NULL ENABLE, "CUSTOMER_ID" NUMBER(10,0)

NOT NULL ENABLE,"CHECK_NUM" NUMBER(4,0) NOT NULL ENABLE, "NORMAL" CHAR(1 BYTE) DEFAULT 'Y'NOT NULL ENABLE,"NOTES" VARCHAR2(1000 BYTE), "CHARGE_FEE" NUMBER(10,2) DEFAULT 0 NOT NULL ENABLE) PCTFREE 10 PCTUSED 40IN

ORA-39171: Job isexperiencing a resumable wait.

ORA-12953: The request exceeds the maximum allowed database size of 11GB--搜索MOS 提示，学习XE类 Express Edition (简化版本，数据库允许最大11g)

ORA-12953: The request exceeds the maximum allowed database size of 11 GB (Doc ID 2414879.1)

[Release11.2 to 12.2]

Thisis an Oracle Database XE environment, and Oracle Database XE has a limitation of 11GB of user data

疑问？测试库怎么有11g这么大？

SQL> select round( sum(bytes)/1024/1024) m from dba_segments where owner not in('PICCPROD');

M----------

1708

--导入用户后 >11g出发XE峰值，报错

SQL> select sum(bytes)/1024/1024 fromdba_segments;

SUM(BYTES)/1024/1024

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

11706.8125

--什么类型的对象占用空间

SQL> select sum(bytes/1024/1024) ,segment_type from dba_segments where owner='PICCPROD'group by owner,segment_type;

SUM(BYTES/1024/1024) SEGMENT_TYPE-------------------- ------------------

9999TABLE

？dump文件大小182，导入后，表大小9G

oracle@c46bf408bfad:~/dmpfile$ ls

PICCPROD01.dmp picc.log

oracle@c46bf408bfad:~/dmpfile$ du -sm PICCPROD01.dmp182PICCPROD01.dmpfile

查询表数据：select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='PICCPROD' and segment_type='TABLE' and rownum=1;

SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024

------------------------------ ------------------ ---------------T_DIAGNOSIS_TYPE TABLE .5

select count(*) fromPICCPROD.T_DIAGNOSIS_TYPE;

COUNT(*)----------

0查询表的区的分布

SQL> select tablespace_name,segment_name,EXTENT_ID,BLOCK_ID,BYTES/1024,BLOCKS from dba_extents where owner='PICCPROD' and segment_name='T_DIAGNOSIS_TYPE';

TABLESPACE_NAME SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES/1024BLOCKS--------------- -------------------- ---------- ---------- ---------- ----------LIFEDATA_T_L T_DIAGNOSIS_TYPE0 128 128 16LIFEDATA_T_L T_DIAGNOSIS_TYPE1 144 128 16LIFEDATA_T_L T_DIAGNOSIS_TYPE2 160 128 16LIFEDATA_T_L T_DIAGNOSIS_TYPE3 176 128 16尝试对表move(本次不再次对数据文件resize 进行分析，如果需要resize减少数据文件，需要从数据文件max_block_id 进行move，随后对数据文件进行resize)

alter table PICCPROD.T_DIAGNOSIS_TYPE move tablespace LIFEDATA_T_L;*ERROR at line1:

ORA-12953: The request exceeds the maximum allowed database size of 11GB*Move时，需要存在一倍对象空闲空间，此时空闲空间不足无法Move*

*释放空间，找出max最大对象select segment_name,sum(blocks) from dba_extents where owner='PICCPROD' group by segment_name order by 2;

SEGMENT_NAME SUM(BLOCKS)-------------------------------------------T_POLICY_AUTO221312T_POLICY_PRODUCT309120drop table PICCPROD.T_POLICY_AUTO purge;

drop table PICCPROD.T_POLICY_PRODUCT purge;

alter table PICCPROD.T_DIAGNOSIS_TYPE move tablespace LIFEDATA_T_L;select tablespace_name,segment_name,EXTENT_ID,BLOCK_ID,BYTES/1024,BLOCKS from dba_extents where owner='PICCPROD' and segment_name='T_DIAGNOSIS_TYPE';

TABLESPACE_NAME SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES/1024BLOCKS-------------------- -------------------- ---------- ---------- ---------- ----------LIFEDATA_T_L T_DIAGNOSIS_TYPE0 34720 128 16LIFEDATA_T_L T_DIAGNOSIS_TYPE1 34736 128 16LIFEDATA_T_L T_DIAGNOSIS_TYPE2 34752 128 16LIFEDATA_T_L T_DIAGNOSIS_TYPE3 34768 128 16

*尝试对数据文件进行resize*

select file_id,bytes/1024/1024 from dba_data_files where tablespace_name='LIFEDATA_T_L';

FILE_ID BYTES/1024/1024

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

5 10000alter database datafile5resize 5000M;*ERROR at line1:

ORA-03297: file contains used data beyond requested RESIZE value--虽然数据为空，但是表对应的段，分配了存储，占用了存储*truncate table

SQL>truncate table PICCPROD.T_DIAGNOSIS_TYPE;

Table truncated.

SQL> select tablespace_name,segment_name,EXTENT_ID,BLOCK_ID,BYTES/1024,BLOCKS from dba_extents where owner='PICCPROD' and segment_name='T_DIAGNOSIS_TYPE';

TABLESPACE_NAME SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES/1024BLOCKS-------------------- -------------------- ---------- ---------- ---------- ----------LIFEDATA_T_L T_DIAGNOSIS_TYPE0 34720 128 16LIFEDATA_T_L T_DIAGNOSIS_TYPE1 34736 128 16LIFEDATA_T_L T_DIAGNOSIS_TYPE2 34752 128 16LIFEDATA_T_L T_DIAGNOSIS_TYPE3 34768 128 16

*数据库参数，延迟段创建

SQL>show parameter defer

NAME TYPE VALUE------------------------------------ ----------- ------------------------------deferred_segment_creation boolean TRUE--参数并未关闭--false，但是之前的导入还是创建表，因此延迟段创建参数，对于数据泵导入的对象是不起作用的*查询建表语句--导入

impdp'/ as sysdba' dumpfile=PICCPROD%U.dmp directory=dump logfile=picc%U.log sqlfile=sql01.sql

ORA-39002: invalid operation

ORA-31694: master table "SYS"."SYS_SQL_FILE_FULL_01" failed to load/unload

ORA-02354: error in exporting/importing data

ORA-39776: fatal Direct Path API error loading table "SYS"."SYS_SQL_FILE_FULL_01"ORA-12953: The request exceeds the maximum allowed database size of 11GB--CREATE TABLE"PICCPROD"."T_POLICY_CHECK_ITEM" ("ITEM_ID" NUMBER(10,0) NOT NULL ENABLE,"CUSTOMER_ID" NUMBER(10,0) NOT NULL ENABLE, "CHECK_NUM" NUMBER(4,0) NOT NULL ENABLE,"NORMAL" CHAR(1 BYTE) DEFAULT 'Y' NOT NULL ENABLE, "NOTES" VARCHAR2(1000BYTE),"CHARGE_FEE" NUMBER(10,2) DEFAULT 0 NOT NULL ENABLE) PCTFREE 10 PCTUSED 40IN

ORA-39171: Job isexperiencing a resumable wait.

ORA-12953: The request exceeds the maximum allowed database size of 11GB

为何impdp，提取ddl语句也报错，因为数据泵导入会在数据库内，创建相关对象，占用临时存储，数据库都满了，因此即使不导入数据，临时创建个对象都是报错的