一、文档说明
1.0 需求：需要将生产环境PICC用户导出，在测试环境中docker 测试数据库导入，只需要表结构；
2.0 思路：根据开发人员提供的需求，数据库源端aix 11.2.0.4, 目标端11.2.0.3
工具：使用expdp进行导出，impdp进行导入，scp进行传输
expdp导出，开并行，只导出元数据，排除JOB等存储过程对象
scp服务器相关权限端口，账户密码索取
impdp导入前，存储空间，用户权限提前准备
二、操作记录
--第一次导入操作failed
--失败原因，导入表创建语法DDL失败，原因
1.导入环境为XE学习环境，数据库容量最大11g，如果空间足够，不会报错
2.导入建表语法，dump文件小于200M，导入建表语句还未插入数据，已占用11G存储，由于表的STORAGE(INITIAL 65536 NEXT 1048576)参数导致，对每个段都分配了空间，导致创建空表占用11g达到XE阀值报错
3 .测试11g新特效，延迟段创建对数据泵导入的对象无效，此参照只能影响手工新建的对象
）资料获取
数据泵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_objects )进行导出
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=
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/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/PRE_TABLE_ACTION
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/PROCEDURE/PROCEDURE
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/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
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/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Master table "SYS"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_02 is:
/home/oracle/tools/system01.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at Thu Nov :: elapsed : ）scp 省略 ） 导入操作 --docker 命令进行SQL*Plus
docker run -d --name "oradb" -v /home/dmuser/my_oracle_data:/u01/app/oracle IP:/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;
sqlplus as sysdba
create directories abc as '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-: unable to create INITIAL extent for segment in tablespace SYSTEM
--对system表空间进行扩容 --再次导入报错
CREATE TABLE "PICCPROD"."T_POLICY_CHECK_ITEM" ("ITEM_ID" NUMBER(,) NOT NULL ENABLE, "CUSTOMER_ID" NUMBER(,)
NOT NULL ENABLE, "CHECK_NUM" NUMBER(,) NOT NULL ENABLE, "NORMAL" CHAR( BYTE) DEFAULT 'Y' NOT NULL ENABLE,
"NOTES" VARCHAR2( BYTE), "CHARGE_FEE" NUMBER(,) DEFAULT NOT NULL ENABLE) PCTFREE PCTUSED IN
ORA-: Job is experiencing a resumable wait.
ORA-: The request exceeds the maximum allowed database size of GB --搜索MOS 提示，学习XE类 Express Edition (简化版本，数据库允许最大11g)
ORA-: The request exceeds the maximum allowed database size of GB (Doc ID 2414879.1)
[Release 11.2 to 12.2]
This is an Oracle Database XE environment, and Oracle Database XE has a limitation of GB of user data
疑问？测试库怎么有11g这么大？ SQL> select round( sum(bytes)//) m from dba_segments where owner not in('PICCPROD');
M
---------- --导入用户后 >11g出发XE峰值，报错
SQL> select sum(bytes)// from dba_segments;
SUM(BYTES)//
--------------------
11706.8125
--什么类型的对象占用空间
SQL> select sum(bytes//) ,segment_type from dba_segments where owner='PICCPROD' group by owner,segment_type;
SUM(BYTES//) SEGMENT_TYPE
-------------------- ------------------
TABLE ？dump文件大小182，导入后，表大小9G
oracle@c46bf408bfad:~/dmpfile$ ls
PICCPROD01.dmp picc.log
oracle@c46bf408bfad:~/dmpfile$ du -sm PICCPROD01.dmp
PICCPROD01.dmpfile 查询表数据：
select segment_name,segment_type,bytes// from dba_segments where owner='PICCPROD' and segment_type='TABLE' and rownum=;
SEGMENT_NAME SEGMENT_TYPE BYTES//
------------------------------ ------------------ ---------------
T_DIAGNOSIS_TYPE TABLE . select count(*) from PICCPROD.T_DIAGNOSIS_TYPE;
COUNT(*)
---------- 查询表的区的分布
SQL> select tablespace_name,segment_name,EXTENT_ID,BLOCK_ID,BYTES/,BLOCKS from dba_extents where owner='PICCPROD' and segment_name='T_DIAGNOSIS_TYPE';
TABLESPACE_NAME SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES/ BLOCKS
--------------- -------------------- ---------- ---------- ---------- ----------
LIFEDATA_T_L T_DIAGNOSIS_TYPE
LIFEDATA_T_L T_DIAGNOSIS_TYPE
LIFEDATA_T_L T_DIAGNOSIS_TYPE
LIFEDATA_T_L T_DIAGNOSIS_TYPE 尝试对表move（本次不再次对数据文件resize 进行分析，如果需要resize减少数据文件，需要从数据文件max_block_id 进行move，随后对数据文件进行resize）
alter table PICCPROD.T_DIAGNOSIS_TYPE move tablespace LIFEDATA_T_L;
*
ERROR at line :
ORA-: The request exceeds the maximum allowed database size of GB
*Move时，需要存在一倍对象空闲空间，此时空闲空间不足无法Move*
*释放空间，找出max最大对象
select segment_name,sum(blocks) from dba_extents where owner='PICCPROD' group by segment_name order by ;
SEGMENT_NAME SUM(BLOCKS)
-------------------------------------------
T_POLICY_AUTO
T_POLICY_PRODUCT
drop 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/,BLOCKS from dba_extents where owner='PICCPROD' and segment_name='T_DIAGNOSIS_TYPE';
TABLESPACE_NAME SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES/ BLOCKS
-------------------- -------------------- ---------- ---------- ---------- ----------
LIFEDATA_T_L T_DIAGNOSIS_TYPE
LIFEDATA_T_L T_DIAGNOSIS_TYPE
LIFEDATA_T_L T_DIAGNOSIS_TYPE
LIFEDATA_T_L T_DIAGNOSIS_TYPE *尝试对数据文件进行resize*
select file_id,bytes// from dba_data_files where tablespace_name='LIFEDATA_T_L';
FILE_ID BYTES//
---------- --------------- alter database datafile resize 5000M;
*
ERROR at line :
ORA-: 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/,BLOCKS from dba_extents where owner='PICCPROD' and segment_name='T_DIAGNOSIS_TYPE';
TABLESPACE_NAME SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES/ BLOCKS
-------------------- -------------------- ---------- ---------- ---------- ----------
LIFEDATA_T_L T_DIAGNOSIS_TYPE
LIFEDATA_T_L T_DIAGNOSIS_TYPE
LIFEDATA_T_L T_DIAGNOSIS_TYPE
LIFEDATA_T_L T_DIAGNOSIS_TYPE *数据库参数，延迟段创建
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-: invalid operation
ORA-: master table "SYS"."SYS_SQL_FILE_FULL_01" failed to load/unload
ORA-: error in exporting/importing data
ORA-: fatal Direct Path API error loading table "SYS"."SYS_SQL_FILE_FULL_01"
ORA-: The request exceeds the maximum allowed database size of GB
--
CREATE TABLE "PICCPROD"."T_POLICY_CHECK_ITEM" ("ITEM_ID" NUMBER(,) NOT NULL ENABLE,
"CUSTOMER_ID" NUMBER(,) NOT NULL ENABLE, "CHECK_NUM" NUMBER(,) NOT NULL ENABLE,
"NORMAL" CHAR( BYTE) DEFAULT 'Y' NOT NULL ENABLE, "NOTES" VARCHAR2( BYTE),
"CHARGE_FEE" NUMBER(,) DEFAULT NOT NULL ENABLE) PCTFREE PCTUSED IN
ORA-: Job is experiencing a resumable wait. ORA-: The request exceeds the maximum allowed database size of GB 为何impdp，提取ddl语句也报错，因为数据泵导入会在数据库内，创建相关对象，占用临时存储，数据库都满了，因此即使不导入数据，临时创建个对象都是报错的
--第二次操作，successful
总结：第一次导入失败，主要原因是，数据泵导入后，分配段初始化空间，避免导入分配第一个extent，即避免现象
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
TRANSFORM = SEGMENT_ATTRIBUTES:N
三、问题反思
TRANSFORM = SEGMENT_ATTRIBUTES:N 参数作用何在，有什么特性
实验测试：
.创建一个测试表a, 创建主键，创建一个索引
.对对象进行元数据导出
.实验参数，进行导入
.查询及验证表及索引是否存在
.查询表及索引的段是否分配
.impdp sqlfile 查询与表 and 索引 get ddl 差异
.创建一个测试表a, 创建主键，创建一个索引
SQL> create table yang.ceshi as select * from scott.emp;
SQL> alter table yang.ceshi add constraint pk_id primary key(empno);
SQL> create index yang.id2 on yang.ceshi(deptno); .对对象进行元数据导出
expdp \'/ as sysdba\' directory=dump dumpfile=yang%u.dmp logfile=yang.log SCHEMAS=yang include=TABLE:"IN('CESHI')" CONTENT=METADATA_ONLY .实验参数，进行导入
impdp \'/ as sysdba\' directory=dump dumpfile=yang%u.dmp logfile=yang1.log TRANSFORM = SEGMENT_ATTRIBUTES:N REMAP_SCHEMA=yang:scott .查询及验证表及索引是否存在
select object_name,object_type,status from user_objects where created>sysdate- OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
ID2 INDEX VALID
PK_ID INDEX VALID
CESHI TABLE VALID .查询表及索引的段是否分配
SQL> select SEGMENT_NAME,SEGMENT_TYPE,BLOCKS,EXTENTS,INITIAL_EXTENT,NEXT_EXTENT from user_segments where segment_name in(select object_name from user_objects where created>sysdate-); no rows selected .impdp sqlfile 查询与表 and 索引 get ddl 差异
[oracle@t2 tools]$ impdp \'/ as sysdba\' directory=dump dumpfile=yang%u.dmp logfile=yang1.log TRANSFORM = SEGMENT_ATTRIBUTES:N REMAP_SCHEMA=yang:scott sqlfile=test_table.sql --查询创建语法，进行说明：
第一部分 alter session level trace文件
第二部分，创建表语法，没有了storage语法
第三部分，创建索引，对索引取消并行，在真实导入数据过程中，parallel并行能帮助快速创建索引，创建索引后数据泵自动noparallel
第四部分，导入完毕后，对数据泵临时创建对象进行清理
[oracle@t2 tools]$ more test_table.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "SCOTT"."CESHI"
( "EMPNO" NUMBER(,),
"ENAME" VARCHAR2( BYTE),
"JOB" VARCHAR2( BYTE),
"MGR" NUMBER(,),
"HIREDATE" DATE,
"SAL" NUMBER(,),
"COMM" NUMBER(,),
"DEPTNO" NUMBER(,)
) ;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX
-- CONNECT SCOTT
CREATE UNIQUE INDEX "SCOTT"."PK_ID" ON "SCOTT"."CESHI" ("EMPNO") ; ALTER INDEX "SCOTT"."PK_ID" NOPARALLEL;
CREATE INDEX "SCOTT"."ID2" ON "SCOTT"."CESHI" ("DEPTNO")
; ALTER INDEX "SCOTT"."ID2" NOPARALLEL;
-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
-- CONNECT SYS
ALTER TABLE "SCOTT"."CESHI" ADD CONSTRAINT "PK_ID" PRIMARY KEY ("EMPNO")
USING INDEX "SCOTT"."PK_ID" ENABLE;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
DECLARE I_N VARCHAR2();
I_O VARCHAR2();
NV VARCHAR2();
c DBMS_METADATA.T_VAR_COLL;
df varchar2() := 'YYYY-MM-DD:HH24:MI:SS';
stmt varchar2() := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:
,:,:,:,:,:,:,:,:,:,:,:,NULL,:,:,NULL,:,:)';
BEGIN
DELETE FROM "SYS"."IMPDP_STATS";
i_n := 'PK_ID';
i_o := 'SCOTT'; EXECUTE IMMEDIATE stmt USING ,I_N,NV,NV,I_O,,,,,,,,,NV,NV,TO_DATE('2018-11-29 15:17:22',df),NV; DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
DELETE FROM "SYS"."IMPDP_STATS";
END;
/
DECLARE I_N VARCHAR2();
I_O VARCHAR2();
NV VARCHAR2();
c DBMS_METADATA.T_VAR_COLL;
df varchar2() := 'YYYY-MM-DD:HH24:MI:SS';
stmt varchar2() := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:
,:,:,:,:,:,:,:,:,:,:,:,NULL,:,:,NULL,:,:)';
BEGIN
DELETE FROM "SYS"."IMPDP_STATS";
i_n := 'ID2';
i_o := 'SCOTT';
EXECUTE IMMEDIATE stmt USING ,I_N,NV,NV,I_O,,,,,,,,,NV,NV,TO_DATE('2018-11-29 15:18:08',df),NV; DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
DELETE FROM "SYS"."IMPDP_STATS";
END;
/ ---查询表的ddl语法
select dbms_metadata.get_ddl('TABLE','CESHI','SCOTT') ddl_text from dual DDL_TEXT
-------------------------------------------------------------------------------- CREATE TABLE "SCOTT"."CESHI"
( "EMPNO" NUMBER(,),
"ENAME" VARCHAR2(),
"JOB" VARCHAR2(),
"MGR" NUMBER(,),
"HIREDATE" DATE,
"SAL" NUMBER(,),
"COMM" NUMBER(,),
"DEPTNO" NUMBER(,),
CONSTRAINT "PK_ID" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE INITRANS MAXTRANS COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE PCTUSED INITRANS MAXTRANS
NOCOMPRESS LOGGING
TABLESPACE "USERS" --表不存在storage属性后 insert 数据进行测试 SQL> insert into scott.ceshi select * from scott.emp; --查询表及索引的extent分配，发现表其实并没有extent storage属性，是怎么分配的呢？ SQL> select SEGMENT_NAME,SEGMENT_TYPE,BLOCKS,EXTENTS,INITIAL_EXTENT/ INITIAL_EXTENT_K,NEXT_EXTENT/ NEXT_EXTENT_K from
user_segments where segment_name in(select object_name from user_objects where created>sysdate-);
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS INITIAL_EXTENT_K NEXT_EXTENT_K
--------------- ------------------ ---------- ---------- ---------------- -------------
CESHI TABLE
PK_ID INDEX
ID2 INDEX --查询表空间属性 SQL> select tablespace_name from user_segments where segment_name in(select object_name from user_objects where created>sysdate-) group by tablespace_name; TABLESPACE_NAME
------------------------------
USERS SQL> select TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT/ INITIAL_EXTENT_K,NEXT_EXTENT/ NEXT_EXTENT_K,SEGMENT_SPACE_MANAGEMENT,ALLOCATION_TYPE,EXTENT_MANAGEMENT from dba_tablespaces where tablespace_name='USERS'; TABLESPACE BLOCK_SIZE INITIAL_EXTENT_K NEXT_EXTENT_K SEGMEN ALLOCATIO EXTENT_MAN
---------- ---------- ---------------- ------------- ------ --------- ----------
USERS AUTO SYSTEM LOCAL --表空间本地管理表空间，系统自动扩展分配， 8K,1M,8M,64M --可见，对于表来说，如果未配置storage属性，则表使用表空间的默认storage属性分配extents 大小
四、问题总结
1.根据问题现象，寻找解决方法，路不止一条，在能接受的范围内解决即可
2.根据问题现象，寻找合适的技术点，使用自己能运用且无风险的方案解决问题，测试后方可使用