impdp 虚拟列含包体数据导入

impdp 虚拟列含包体数据导入

1、先在目标端生成创建不含虚拟列的表

create table XBILL_RESTO2
(
  XBILL_dtl_id    VARCHAR2(15) not null,
  lc_code        VARCHAR2(5) not null,
  XBILL_hdr_id    VARCHAR2(15) not null,
  goods_id       VARCHAR2(10) not null,
  lotno_id       VARCHAR2(15),
  location_id    VARCHAR2(10),
  storeroom_cate VARCHAR2(10),
  planned_qty    NUMBER(22,5) default 0 not null,
  actual_qty     NUMBER(22,5) default 0 not null,
  pallet_id      VARCHAR2(10),
  nailbox_id     VARCHAR2(10),
  is_executed    VARCHAR2(1) default 'N' not null,
  device_sign    VARCHAR2(1)
)
partition by list (LC_CODE)
(
  partition XBILL_RESTO2_P001 values ('LC001')  ,
  partition XBILL_RESTO2_P002 values ('LC002')   ,
  partition XBILL_RESTO2_P003 values ('LC003')   ,
  partition XBILL_RESTO2_P004 values ('LC004')   ,
  partition XBILL_RESTO2_P005 values ('LC005')   ,
  partition XBILL_RESTO2_P006 values ('LC006')   ,
  partition XBILL_RESTO2_P007 values ('LC007')   ,
  partition XBILL_RESTO2_P008 values ('LC008')   
);

2、impdp只导入数据

impdp qn/qn directory=OGG_HIS   job_name=qn_job dumpfile=lmis_sd.dmp logfile=lmis_sd.log remap_schema=LMIS:QN,LMIS_HIS:QN   remap_tablespace=WMS:QN,P001:QN,P004:QN,P007:QN,P003:QN,IDX:QN,P006:QN,P008:QN,P005:QN,P002:QN tables=lmis.CMD_UPSHELF,lmis.XBILL_RESTO2,lmis.XBILL_INSTOREHOUSE_DTL,lmis.XFD_STOCK,lmis.CMD_OPERATION,lmis.XBILL_OUTSTOREHOUSE_DTL   content=data_only  TABLE_EXISTS_ACTION = truncate

select * from XBILL_RESTO2 时报 "ORA-28110: 策略函数或程序包 QN.PKG_POLICY 有错误"

理解policy，并创建一个policy：
https://www.2cto.com/database/201307/225044.html

QN@WWPL> select a.OBJECT_OWNER,OBJECT_NAME,POLICY_GROUP,POLICY_NAME from dba_policies a where a.OBJECT_OWNER not in('XDB');

OBJECT_OWNER                   OBJECT_NAME                    POLICY_GROUP                   POLICY_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
QN                             XBILL_INSTORE2                  SYS_DEFAULT                    CTX_OWNER
QN                             XBILL_INSTORE2                  SYS_DEFAULT                    CTX_LC
QN                             XBILL_MOVE2                     SYS_DEFAULT                    CTX_OWNER
QN                             XBILL_MOVE2                     SYS_DEFAULT                    CTX_LC
QN                             XBILL_OUTSTOREHOUSE_HDR         SYS_DEFAULT                    CTX_OWNER
QN                             XBILL_OUTSTOREHOUSE_HDR         SYS_DEFAULT                    CTX_LC
QN                             XBILL_RESTOCKING_HDR            SYS_DEFAULT                    CTX_OWNER
QN                             XBILL_RESTOCKING_HDR            SYS_DEFAULT                    CTX_LC
QN                             XFD_AREA                        SYS_DEFAULT                    CTX_LC
QN                             XFD_CONTAINER                   SYS_DEFAULT                    CTX_LC
QN                             XFD_GOODS                       SYS_DEFAULT                    CTX_OWNER
QN                             XFD_LOTNO                       SYS_DEFAULT                    CTX_OWNER
QN                             XFD_STAFF                       SYS_DEFAULT                    CTX_OWNERS
QN                             XFD_STAFF                       SYS_DEFAULT                    CTX_OWNER
QN                             XFD_SUPPLYSALESAGENCY           SYS_DEFAULT                    CTX_OWNER
QN                             REC_EM_CODE                    SYS_DEFAULT                    CTX_LC

select 'Dbms_Rls.drop_policy(''QN'','''||object_name||''','''||policy_name||''');' from dba_policies a where a.OBJECT_OWNER not in('XDB');  
Dbms_Rls.drop_policy('QN','XBILL_MOVE2','CTX_OWNER');
Dbms_Rls.drop_policy('QN','XBILL_MOVE2','CTX_LC');
Dbms_Rls.drop_policy('QN','XBILL_OUTSTOREHOUSE_HDR','CTX_OWNER');
Dbms_Rls.drop_policy('QN','XBILL_OUTSTOREHOUSE_HDR','CTX_LC');
Dbms_Rls.drop_policy('QN','XBILL_RESTOCKING_HDR','CTX_OWNER');
Dbms_Rls.drop_policy('QN','XBILL_RESTOCKING_HDR','CTX_LC');
Dbms_Rls.drop_policy('QN','XFD_AREA','CTX_LC');
Dbms_Rls.drop_policy('QN','XFD_CONTAINER','CTX_LC');
Dbms_Rls.drop_policy('QN','XFD_GOODS','CTX_OWNER');
Dbms_Rls.drop_policy('QN','XFD_LOTNO','CTX_OWNER');
Dbms_Rls.drop_policy('QN','XFD_STAFF','CTX_OWNERS');
Dbms_Rls.drop_policy('QN','XFD_STAFF','CTX_OWNER');
Dbms_Rls.drop_policy('QN','XFD_SUPPLYSALESAGENCY','CTX_OWNER');
Dbms_Rls.drop_policy('QN','REC_EM_CODE','CTX_LC');

删除policy:

BEGIN
  Dbms_Rls.drop_policy('QN',                    --要删除的Policy所在的Schema
                       'XBILL_INSTORE2', --要删除Policy的数据表(或视图)名称
                       'CTX_LC'                 --要删除的Policy名称
                       );

Dbms_Rls.drop_policy('QN','XBILL_MOVE2','CTX_OWNER');
Dbms_Rls.drop_policy('QN','XBILL_MOVE2','CTX_LC');
Dbms_Rls.drop_policy('QN','XBILL_OUTSTOREHOUSE_HDR','CTX_OWNER');
Dbms_Rls.drop_policy('QN','XBILL_OUTSTOREHOUSE_HDR','CTX_LC');
Dbms_Rls.drop_policy('QN','XBILL_RESTOCKING_HDR','CTX_OWNER');
Dbms_Rls.drop_policy('QN','XBILL_RESTOCKING_HDR','CTX_LC');
Dbms_Rls.drop_policy('QN','XFD_AREA','CTX_LC');
Dbms_Rls.drop_policy('QN','XFD_CONTAINER','CTX_LC');
Dbms_Rls.drop_policy('QN','XFD_GOODS','CTX_OWNER');
Dbms_Rls.drop_policy('QN','XFD_LOTNO','CTX_OWNER');
Dbms_Rls.drop_policy('QN','XFD_STAFF','CTX_OWNERS');
Dbms_Rls.drop_policy('QN','XFD_STAFF','CTX_OWNER');
Dbms_Rls.drop_policy('QN','XFD_SUPPLYSALESAGENCY','CTX_OWNER');
Dbms_Rls.drop_policy('QN','REC_EM_CODE','CTX_LC');

end ;

再查询时 就不报错了