欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

impdp导入表不创建segments

程序员文章站 2022-10-30 13:16:01
一、文档说明 二、操作记录 --第一次导入操作failed --失败原因,导入表创建语法DDL失败,原因 1.导入环境为XE学习环境,数据库容量最大11g,如果空间足够,不会报错 2.导入建表语法,dump文件小于200M,导入建表语句还未插入数据,已占用11G存储,由于表的STORAGE(INIT ......

一、文档说明

   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新特效,延迟段创建对数据泵导入的对象无效,此参照只能影响手工新建的对象

 

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_objects  

2)进行导出
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=2 
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 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; 
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-01658: unable to create initial extent for segment in tablespace 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 40 in
ora-39171: job is experiencing a resumable wait.
ora-12953: the request exceeds the maximum allowed database size of 11 gb
 
--搜索mos 提示,学习xe类 express edition (简化版本,数据库允许最大11g)
ora-12953: the request exceeds the maximum allowed database size of 11 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 11 gb 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 from dba_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
-------------------- ------------------
                9999 table

?dump文件大小182,导入后,表大小9g                
oracle@c46bf408bfad:~/dmpfile$ ls
piccprod01.dmp  picc.log
oracle@c46bf408bfad:~/dmpfile$ du -sm piccprod01.dmp 
182     piccprod01.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(*) from piccprod.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/1024     blocks
--------------- -------------------- ---------- ---------- ---------- ----------
lifedata_t_l    t_diagnosis_type              0        128        128         16
lifedata_t_l    t_diagnosis_type              1        144        128         16
lifedata_t_l    t_diagnosis_type              2        160        128         16
lifedata_t_l    t_diagnosis_type              3        176        128         16



尝试对表move(本次不再次对数据文件resize 进行分析,如果需要resize减少数据文件,需要从数据文件max_block_id 进行move,随后对数据文件进行resize)
alter table piccprod.t_diagnosis_type move tablespace lifedata_t_l;
                    *
error at line 1:
ora-12953: the request exceeds the maximum allowed database size of 11 gb
*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_auto        221312
t_policy_product     309120
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/1024,blocks from dba_extents where owner='piccprod' and segment_name='t_diagnosis_type';
tablespace_name      segment_name          extent_id   block_id bytes/1024     blocks
-------------------- -------------------- ---------- ---------- ---------- ----------
lifedata_t_l         t_diagnosis_type              0      34720        128         16
lifedata_t_l         t_diagnosis_type              1      34736        128         16
lifedata_t_l         t_diagnosis_type              2      34752        128         16
lifedata_t_l         t_diagnosis_type              3      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           10000
alter database datafile 5 resize 5000m;
*
error at line 1:
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/1024     blocks
-------------------- -------------------- ---------- ---------- ---------- ----------
lifedata_t_l         t_diagnosis_type              0      34720        128         16
lifedata_t_l         t_diagnosis_type              1      34736        128         16
lifedata_t_l         t_diagnosis_type              2      34752        128         16
lifedata_t_l         t_diagnosis_type              3      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 11 gb
--
 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 40 in
ora-39171: job is experiencing a resumable wait.

ora-12953: the request exceeds the maximum allowed database size of 11 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 参数作用何在,有什么特性
实验测试:  
1.创建一个测试表a, 创建主键,创建一个索引
2.对对象进行元数据导出
3.实验参数,进行导入
4.查询及验证表及索引是否存在
5.查询表及索引的段是否分配
6.impdp sqlfile 查询与表 and  索引 get ddl 差异

 



1.创建一个测试表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);
          
2.对对象进行元数据导出
expdp \'/ as sysdba\' directory=dump dumpfile=yang%u.dmp logfile=yang.log schemas=yang include=table:"in('ceshi')" content=metadata_only

3.实验参数,进行导入
impdp \'/ as sysdba\' directory=dump dumpfile=yang%u.dmp logfile=yang1.log transform = segment_attributes:n remap_schema=yang:scott

4.查询及验证表及索引是否存在
select object_name,object_type,status from user_objects where created>sysdate-1

object_name object_type status
-------------------- ------------------- -------
id2 index valid
pk_id index valid
ceshi table valid

5.查询表及索引的段是否分配
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-1);

no rows selected

 6.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(4,0), 
"ename" varchar2(10 byte), 
"job" varchar2(9 byte), 
"mgr" number(4,0), 
"hiredate" date, 
"sal" number(7,2), 
"comm" number(7,2), 
"deptno" number(2,0)
) ;
-- 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(60); 
i_o varchar2(60); 
nv varchar2(1); 
c dbms_metadata.t_var_coll; 
df varchar2(21) := 'yyyy-mm-dd:hh24:mi:ss'; 
stmt varchar2(300) := ' 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,:
2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,null,:14,:15,null,:16,:17)';
begin
delete from "sys"."impdp_stats"; 
i_n := 'pk_id'; 
i_o := 'scott';


execute immediate stmt using 2,i_n,nv,nv,i_o,14,1,14,1,1,1,0,14,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(60); 
i_o varchar2(60); 
nv varchar2(1); 
c dbms_metadata.t_var_coll; 
df varchar2(21) := 'yyyy-mm-dd:hh24:mi:ss'; 
stmt varchar2(300) := ' 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,:
2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,null,:14,:15,null,:16,:17)';
begin
delete from "sys"."impdp_stats"; 
i_n := 'id2'; 
i_o := 'scott'; 
execute immediate stmt using 2,i_n,nv,nv,i_o,14,1,3,1,1,1,0,14,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(4,0),
"ename" varchar2(10),
"job" varchar2(9),
"mgr" number(4,0),
"hiredate" date,
"sal" number(7,2),
"comm" number(7,2),
"deptno" number(2,0),
constraint "pk_id" primary key ("empno")
using index pctfree 10 initrans 2 maxtrans 255 compute statistics
tablespace "users" enable
) segment creation deferred
pctfree 10 pctused 40 initrans 1 maxtrans 255
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/1024 initial_extent_k,next_extent/1024 next_extent_k from 
user_segments where segment_name in(select object_name from user_objects where created>sysdate-1);
segment_name segment_type blocks extents initial_extent_k next_extent_k
--------------- ------------------ ---------- ---------- ---------------- -------------
             ceshi                        table                8           1                          64                                1024
              pk_id                         index               8           1                          64                                 1024
                id2                             index              8           1                           64                                 1024

--查询表空间属性

sql> select tablespace_name from user_segments where segment_name in(select object_name from user_objects where created>sysdate-1) group by tablespace_name;

tablespace_name
------------------------------
users

sql> select tablespace_name,block_size,initial_extent/1024 initial_extent_k,next_extent/1024 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 8192 64 auto system local  --表空间本地管理表空间,系统自动扩展分配, 8k,1m,8m,64m

--可见,对于表来说,如果未配置storage属性,则表使用表空间的默认storage属性分配extents 大小

 

 


  

四、问题总结
1.根据问题现象,寻找解决方法,路不止一条,在能接受的范围内解决即可
2.根据问题现象,寻找合适的技术点,使用自己能运用且无风险的方案解决问题,测试后方可使用