Hi all,
We experience issue during inserting data into remote Oracle instance via dblink.
Procedure returned error ORA-30036: unable to extend segment by 4 in undo tablespace 'UNDOTBS1'
ORA-02054: transaction 228.11.82305 in-doubt
Please find DDL below.
Any help?
BEGIN
ZVIT."PKG_DWH".DWH_CR_DEAL_TRUNCATE@CSBI;
merge into ZVIT.DWH_CR_DEAL@CSBI rr
using
(
select
ID ,
INPUTDATE ,
VALUEDATE ,
CLOSEDATE ,
DEALSTATE ,
DESCRIPTION ,
OPERATOR ,
DEALTYPEID ,
CONTRAGENTID ,
DEALNO ,
PARENTID ,
CHANGENO ,
STARTDATE ,
EXPECTEDCLOSEDATE ,
DEALDATE ,
SUBTYPENO ,
DEALCHANGEID ,
TOBOID ,
NEEDTOVERIFY ,
DEPARTMENTID ,
MNGPRODUCTID ,
MNGRESPONSCENTERID ,
SITEID ,
SELFDEPENDENT ,
INTERNALRATE ,
REGIONID ,
SYNCTIMESTAMP ,
PASSIVEMODEMASK ,
MNGDIVISIONID ,
CUSTOMERREF ,
CUSTOMERREFDATE ,
ACCOUNTMANAGERNAME ,
ACCOUNTMANAGERNAME2 ,
DISTRIBUTIONCHANELID ,
MIRPCODE1
from CREATOR.DEAL
) hh
on ( hh.id = rr.id )
when matched
then update set
-- rr.NAME = hh.NAME,
-- rr.ACTIVE = hh.ACTIVE
-- rr.ID rr.ID,
rr.INPUTDATE = hh.INPUTDATE,
rr.VALUEDATE = hh.VALUEDATE,
rr.CLOSEDATE = hh.CLOSEDATE,
rr.DEALSTATE = hh.DEALSTATE,
rr.DESCRIPTION = hh.DESCRIPTION,
rr.OPERATOR = hh.OPERATOR,
rr.DEALTYPEID = hh.DEALTYPEID,
rr.CONTRAGENTID = hh.CONTRAGENTID,
rr.DEALNO = hh.DEALNO,
rr.PARENTID = hh.PARENTID,
rr.CHANGENO = hh.CHANGENO,
rr.STARTDATE = hh.STARTDATE,
rr.EXPECTEDCLOSEDATE = hh.EXPECTEDCLOSEDATE,
rr.DEALDATE = hh.DEALDATE,
rr.SUBTYPENO = hh.SUBTYPENO,
rr.DEALCHANGEID = hh.DEALCHANGEID,
rr.TOBOID = hh.TOBOID,
rr.NEEDTOVERIFY = hh.NEEDTOVERIFY,
rr.DEPARTMENTID = hh.DEPARTMENTID,
rr.MNGPRODUCTID = hh.MNGPRODUCTID,
rr.MNGRESPONSCENTERID = hh.MNGRESPONSCENTERID,
rr.SITEID = hh.SITEID,
rr.SELFDEPENDENT = hh.SELFDEPENDENT,
rr.INTERNALRATE = hh.INTERNALRATE,
rr.REGIONID = hh.REGIONID,
rr.SYNCTIMESTAMP = hh.SYNCTIMESTAMP,
rr.PASSIVEMODEMASK = hh.PASSIVEMODEMASK,
rr.MNGDIVISIONID = hh.MNGDIVISIONID,
rr.CUSTOMERREF = hh.CUSTOMERREF,
rr.CUSTOMERREFDATE = hh.CUSTOMERREFDATE,
rr.ACCOUNTMANAGERNAME = hh.ACCOUNTMANAGERNAME,
rr.ACCOUNTMANAGERNAME2 = hh.ACCOUNTMANAGERNAME2,
rr.DISTRIBUTIONCHANELID = hh.DISTRIBUTIONCHANELID,
rr.MIRPCODE1 = hh.MIRPCODE1
where
-- rr.NAME != hh.NAME
-- OR rr.ACTIVE != hh.ACTIVE
rr.INPUTDATE != hh.INPUTDATE
or rr.VALUEDATE != hh.VALUEDATE
or rr.CLOSEDATE != hh.CLOSEDATE
or rr.DEALSTATE != hh.DEALSTATE
or rr.DESCRIPTION != hh.DESCRIPTION
or rr.OPERATOR != hh.OPERATOR
or rr.DEALTYPEID != hh.DEALTYPEID
or rr.CONTRAGENTID != hh.CONTRAGENTID
or rr.DEALNO != hh.DEALNO
or rr.PARENTID != hh.PARENTID
or rr.CHANGENO != hh.CHANGENO
or rr.STARTDATE != hh.STARTDATE
or rr.EXPECTEDCLOSEDATE != hh.EXPECTEDCLOSEDATE
or rr.DEALDATE != hh.DEALDATE
or rr.SUBTYPENO != hh.SUBTYPENO
or rr.DEALCHANGEID != hh.DEALCHANGEID
or rr.TOBOID != hh.TOBOID
or rr.NEEDTOVERIFY != hh.NEEDTOVERIFY
or rr.DEPARTMENTID != hh.DEPARTMENTID
or rr.MNGPRODUCTID != hh.MNGPRODUCTID
or rr.MNGRESPONSCENTERID != hh.MNGRESPONSCENTERID
or rr.SITEID != hh.SITEID
or rr.SELFDEPENDENT != hh.SELFDEPENDENT
or rr.INTERNALRATE != hh.INTERNALRATE
or rr.REGIONID != hh.REGIONID
or rr.SYNCTIMESTAMP != hh.SYNCTIMESTAMP
or rr.PASSIVEMODEMASK != hh.PASSIVEMODEMASK
or rr.MNGDIVISIONID != hh.MNGDIVISIONID
or rr.CUSTOMERREF != hh.CUSTOMERREF
or rr.CUSTOMERREFDATE != hh.CUSTOMERREFDATE
or rr.ACCOUNTMANAGERNAME != hh.ACCOUNTMANAGERNAME
or rr.ACCOUNTMANAGERNAME2 != hh.ACCOUNTMANAGERNAME2
or rr.DISTRIBUTIONCHANELID != hh.DISTRIBUTIONCHANELID
or rr.MIRPCODE1 != hh.MIRPCODE1
when not matched then insert (
-- rr.ID ,
-- rr.NAME ,
-- rr.ACTIVE
rr.ID ,
rr.INPUTDATE ,
rr.VALUEDATE ,
rr.CLOSEDATE ,
rr.DEALSTATE ,
rr.DESCRIPTION ,
rr.OPERATOR ,
rr.DEALTYPEID ,
rr.CONTRAGENTID ,
rr.DEALNO ,
rr.PARENTID ,
rr.CHANGENO ,
rr.STARTDATE ,
rr.EXPECTEDCLOSEDATE ,
rr.DEALDATE ,
rr.SUBTYPENO ,
rr.DEALCHANGEID ,
rr.TOBOID ,
rr.NEEDTOVERIFY ,
rr.DEPARTMENTID ,
rr.MNGPRODUCTID ,
rr.MNGRESPONSCENTERID ,
rr.SITEID ,
rr.SELFDEPENDENT ,
rr.INTERNALRATE ,
rr.REGIONID ,
rr.SYNCTIMESTAMP ,
rr.PASSIVEMODEMASK ,
rr.MNGDIVISIONID ,
rr.CUSTOMERREF ,
rr.CUSTOMERREFDATE ,
rr.ACCOUNTMANAGERNAME ,
rr.ACCOUNTMANAGERNAME2 ,
rr.DISTRIBUTIONCHANELID ,
rr.MIRPCODE1
)
values (
-- hh.ID ,
-- hh.NAME ,
-- hh.ACTIVE
hh.ID ,
hh.INPUTDATE ,
hh.VALUEDATE ,
hh.CLOSEDATE ,
hh.DEALSTATE ,
hh.DESCRIPTION ,
hh.OPERATOR ,
hh.DEALTYPEID ,
hh.CONTRAGENTID ,
hh.DEALNO ,
hh.PARENTID ,
hh.CHANGENO ,
hh.STARTDATE ,
hh.EXPECTEDCLOSEDATE ,
hh.DEALDATE ,
hh.SUBTYPENO ,
hh.DEALCHANGEID ,
hh.TOBOID ,
hh.NEEDTOVERIFY ,
hh.DEPARTMENTID ,
hh.MNGPRODUCTID ,
hh.MNGRESPONSCENTERID ,
hh.SITEID ,
hh.SELFDEPENDENT ,
hh.INTERNALRATE ,
hh.REGIONID ,
hh.SYNCTIMESTAMP ,
hh.PASSIVEMODEMASK ,
hh.MNGDIVISIONID ,
hh.CUSTOMERREF ,
hh.CUSTOMERREFDATE ,
hh.ACCOUNTMANAGERNAME ,
hh.ACCOUNTMANAGERNAME2 ,
hh.DISTRIBUTIONCHANELID ,
hh.MIRPCODE1
);
commit;
exception
when others then
ZOOM."PKG_ALERT".ALERT (
parSUBSOFTLISTID => 'A498C4B7C01F4EB99A3D304FAAC30D79',
parCUSTOMMSG => 'error ZOO.PKG_DWH - stage DWH_CR_DEAL ',
parERRMSG => substr(sqlerrm,1,2000),
parSQLCODE => substr(SQLCODE,1,2000)
);
END;
CREATE OR REPLACE PACKAGE BODY ZVIT."PKG_DWH" IS
PROCEDURE DWH_CR_DEAL_TRUNCATE IS
BEGIN
execute immediate 'truncate table ZVIT.DWH_CR_DEAL';
END;
END;
/
DROP TABLE ZVIT.DWH_CR_DEAL CASCADE CONSTRAINTS;
CREATE TABLE ZVIT.DWH_CR_DEAL
(
ID NUMBER(10),
INPUTDATE DATE CONSTRAINT NN_DEAL_INPUTDATE NOT NULL,
VALUEDATE DATE,
CLOSEDATE DATE,
DEALSTATE NUMBER(5) DEFAULT 0 CONSTRAINT NN_DEAL_DEALSTATE NOT NULL,
DESCRIPTION VARCHAR2(254 BYTE) DEFAULT '',
OPERATOR VARCHAR2(30 BYTE) DEFAULT COALESCE(SYS_CONTEXT('CX_B2_USER', 'USERNAME'), USER),
DEALTYPEID NUMBER(10) CONSTRAINT NN_DEAL_DEALTYPEID NOT NULL,
CONTRAGENTID NUMBER(10) CONSTRAINT NN_DEAL_CONTRAGENTID NOT NULL,
DEALNO VARCHAR2(20 BYTE),
PARENTID NUMBER(10),
CHANGENO NUMBER(10) DEFAULT 0,
STARTDATE DATE,
EXPECTEDCLOSEDATE DATE,
DEALDATE DATE,
SUBTYPENO NUMBER(10) CONSTRAINT NN_DEAL_SUBTYPENO NOT NULL,
DEALCHANGEID NUMBER(10),
TOBOID NUMBER(10) CONSTRAINT NN_DEAL_TOBOID NOT NULL,
NEEDTOVERIFY NUMBER(1),
DEPARTMENTID NUMBER(10) CONSTRAINT NN_DEAL_DEPARTMENTID NOT NULL,
MNGPRODUCTID NUMBER(10),
MNGRESPONSCENTERID NUMBER(10),
SITEID NUMBER(10) DEFAULT SYS_CONTEXT('CX_B2_USER', 'SITEID') CONSTRAINT NN_DEAL_SITEID NOT NULL,
SELFDEPENDENT NUMBER(1) DEFAULT 0 CONSTRAINT NN_DEAL_SELFDEPENDENT NOT NULL,
INTERNALRATE NUMBER(15,6),
REGIONID NUMBER(10),
SYNCTIMESTAMP DATE DEFAULT SYSDATE,
PASSIVEMODEMASK NUMBER(10),
MNGDIVISIONID NUMBER(10),
CUSTOMERREF VARCHAR2(128 BYTE),
CUSTOMERREFDATE DATE,
ACCOUNTMANAGERNAME VARCHAR2(30 BYTE),
ACCOUNTMANAGERNAME2 VARCHAR2(30 BYTE),
DISTRIBUTIONCHANELID NUMBER(10),
MIRPCODE1 NUMBER(10)
)
TABLESPACE VB_DATA
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOLOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
COMMENT ON TABLE ZVIT.DWH_CR_DEAL IS 'Сделки';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.ID IS 'Вн.код сделки';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.INPUTDATE IS 'Дата ввода';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.VALUEDATE IS 'Дата первого реального движения средств';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.CLOSEDATE IS 'Реальная дата закрытия';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.DEALSTATE IS 'Состояние сделки(0 оформляется, 1 действует, 2 закрыта, 3 будет закрыта, 4 отмена добавления(фактически удалена))';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.DESCRIPTION IS 'Описание';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.OPERATOR IS 'Кто отвечает (ввел)';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.DEALTYPEID IS 'Тип сделки';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.CONTRAGENTID IS 'Код контрагента';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.DEALNO IS 'Номер сделки';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.PARENTID IS 'Родительская сделка';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.CHANGENO IS 'Номер изменения';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.STARTDATE IS 'Дата отсечки документов (до этой даты нельзя привязать док.)';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.EXPECTEDCLOSEDATE IS 'Ожидаемая дата закрытия';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.DEALDATE IS 'Дата сделки';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.SUBTYPENO IS 'Подтип';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.DEALCHANGEID IS 'Код последнего изменения';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.TOBOID IS 'Код ТОБО';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.NEEDTOVERIFY IS 'Признак необходимости верификации';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.DEPARTMENTID IS 'ID отдела';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.MNGPRODUCTID IS 'ID продукта упр. учета';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.MNGRESPONSCENTERID IS 'ID ЦО';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.SITEID IS 'Код филиала';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.SELFDEPENDENT IS 'Признак консолидированной сделки';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.INTERNALRATE IS 'Ставка фондирования';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.REGIONID IS 'Регион';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.SYNCTIMESTAMP IS 'Дата и время последнего изменения';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.PASSIVEMODEMASK IS 'Маска свойств сделки';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.MNGDIVISIONID IS 'Код управленческого подразделения';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.CUSTOMERREF IS 'Номер заявки для загрузки из внешних систем';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.CUSTOMERREFDATE IS 'Дата заявки для загрузки из внешних систем';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.ACCOUNTMANAGERNAME IS 'Менеджер по продажам';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.ACCOUNTMANAGERNAME2 IS 'Менеджер по продажам2';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.DISTRIBUTIONCHANELID IS 'ID канала продаж';
COMMENT ON COLUMN ZVIT.DWH_CR_DEAL.MIRPCODE1 IS 'ID параметра Managerial Information Reporting';
CREATE INDEX ZVIT.DWH_CR_IN_DEAL_DEALDATE ON ZVIT.DWH_CR_DEAL
(DEALDATE, CLOSEDATE, DEALTYPEID, SITEID)
LOGGING
TABLESPACE VB_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE INDEX ZVIT.DWH_CR_I_DEAL_BYTYPEDATE ON ZVIT.DWH_CR_DEAL
(DEALTYPEID, DEALDATE)
LOGGING
TABLESPACE VB_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE INDEX ZVIT.DWH_CR_I_DEAL_CONTRAGENT ON ZVIT.DWH_CR_DEAL
(CONTRAGENTID, SITEID, DEALTYPEID, DEALSTATE)
LOGGING
TABLESPACE VB_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE INDEX ZVIT.DWH_CR_I_DEAL_DEALNO ON ZVIT.DWH_CR_DEAL
(DEALNO, DEALDATE, DEALTYPEID, SITEID)
LOGGING
TABLESPACE VB_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE UNIQUE INDEX ZVIT.DWH_CR_UQ_DEAL_ID_SITEID ON ZVIT.DWH_CR_DEAL
(ID, SITEID)
LOGGING
TABLESPACE VB_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE INDEX ZVIT.DWH_CR_I_DEAL_TYPESTATEOPER ON ZVIT.DWH_CR_DEAL
(DEALTYPEID, DEALSTATE, OPERATOR)
LOGGING
TABLESPACE VB_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE UNIQUE INDEX ZVIT.DWH_CR_PK_DEAL ON ZVIT.DWH_CR_DEAL
(ID)
LOGGING
TABLESPACE VB_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE UNIQUE INDEX ZVIT.DWH_CR_UNQ_DEAL_DEALNO ON ZVIT.DWH_CR_DEAL
(DECODE("DEALTYPEID",327,"DEALNO"||'!'||TO_CHAR("DEALDATE",'DD.MM.RRRR')||'!'||TO_CHAR("DEALTYPEID")||'!'||"CUSTOMERREF"||'!'||TO_CHAR("SITEID"),"DEALNO"||'!'||TO_CHAR("DEALDATE",'DD.MM.RRRR')||'!'||TO_CHAR("DEALTYPEID")||'!'||TO_CHAR("SITEID")))
LOGGING
TABLESPACE VB_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE INDEX ZVIT.DWH_CR_I_DEAL_PARENTID ON ZVIT.DWH_CR_DEAL
(PARENTID, SITEID)
LOGGING
TABLESPACE VB_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;