--1)Primary table
-- 2)add PK for table T_FRANK
-- update field's type
--3)Foreign table
-- 4)add PK for table T_FRANK_TEST
--5)add FK for T_FRANK_TEST
--6)Create/Recreate indexes
--8) insert data to T_FRANK_TEST
--9) update data
--10) delete T_FRANK(cascade delete T_FRANK_TEST)
--11) delete the data and the table
--因为需要,Oracle有没有类似My SQL的drop table if exists这类的支持,只好自己写一个Declare了
--12) usage for CURSOR , LOOP and WHILE in DECLARE
下面是实际工作中遇到的问题:
For this sap invoice error, there are two work need to do: please follow below order to execute the statement(1 -> 2), thanks!
1> Data patch:
Delete the dirty data from the table INVOICE_ARTICLE_SIZE, in order to delete it quickly(the dirty data is too large(about53473089)), below was my prepared statement:
According to preliminary estimates, below statement need about 13 hours in my local env. May be it need few time in Production.
2> Hotfix:
add cascade delete for INVOICE_ARTICLE_SIZE, below is the SQL:(it also need long time to execute, it depends on number of the record in the size table.)
BTW, may be this data patch can’t finish today,
But our system will continue to generate dirty data before you do Hotfix,
Hence I suggest, you’d better execute below sql to check whether there are dirty data exist:
If above select result>0, it means there are dirty data exist, you can use below delete statement to delete it directly.
--13)待续。。。
create table T_FRANK
(
T_NO NUMBER not null,
T_NAME NUMBER
)
tablespace STAGING_TEST2_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1M
minextents 1
maxextents unlimited
);
-- 2)add PK for table T_FRANK
alter table T_FRANK
add constraint PK_T_FRANK primary key (T_NO)
using index
tablespace STAGING_TEST2_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- update field's type
alter table T_FRANK modify T_NAME VARCHAR2(30);
--3)Foreign table
create table T_FRANK_TEST
(
F_ID NUMBER not null,
T_NO NUMBER not null,
IS_MANDATORY CHAR(1) default '1',
QTY NUMBER default '0',
PRICE NUMBER(16,4),
LAUNCH_DATE DATE,
MODIFIER VARCHAR2(50)
)
tablespace STAGING_TEST2_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 1M
minextents 1
maxextents unlimited
);
-- 4)add PK for table T_FRANK_TEST
alter table T_FRANK_TEST
add constraint PK_T_FRANK_TEST primary key (F_ID, T_NO)
using index
tablespace STAGING_TEST2_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
--5)add FK for T_FRANK_TEST
alter table T_FRANK_TEST
add constraint FK_T_FRANK_TEST_T_FRANK foreign key (T_NO)
references T_FRANK (T_NO) on delete cascade;
--6)Create/Recreate indexes
create index T_FRANK_TEST_F_ID on T_FRANK_TEST (F_ID)
tablespace STAGING_TEST2_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
--7) insert data to T_FRANK
insert into T_FRANK values(NVL((select max(T_NO) from T_FRANK),0)+1,'Frank1');
insert into T_FRANK values(NVL((select max(T_NO) from T_FRANK),0)+1,'Frank2');
commit;
--8) insert data to T_FRANK_TEST
insert into T_FRANK_TEST values(NVL((select max(F_ID) from T_FRANK_TEST),0)+1,1,'1',1000,66.98,sysdate,'FrankWang');
insert into T_FRANK_TEST values(NVL((select max(F_ID) from T_FRANK_TEST),0)+1,2,'1',1000,66.98,sysdate,'FrankWang');
commit;
--9) update data
UPDATE T_FRANK_TEST t SET t.MODIFIER='FrankWang2' where t.f_id=2;
commit;
--10) delete T_FRANK(cascade delete T_FRANK_TEST)
delete from T_Frank f where f.t_no=1;
commit;
--11) delete the data and the table
--因为需要,Oracle有没有类似My SQL的drop table if exists这类的支持,只好自己写一个Declare了
declare
p_table varchar2(30):='T_FRANK_TEST';
v_count number;
begin
select count(1) into v_count from user_objects where object_name = upper('T_FRANK_TEST');
if v_count > 0 then
execute immediate 'drop table ' || p_table ||' cascade constraints';
end if;
end;
/
--12) usage for CURSOR , LOOP and WHILE in DECLARE
下面是实际工作中遇到的问题:
For this sap invoice error, there are two work need to do: please follow below order to execute the statement(1 -> 2), thanks!
1> Data patch:
Delete the dirty data from the table INVOICE_ARTICLE_SIZE, in order to delete it quickly(the dirty data is too large(about53473089)), below was my prepared statement:
According to preliminary estimates, below statement need about 13 hours in my local env. May be it need few time in Production.
DECLARE
--define batch delete number of invoices no
topnum constant int:=5;
----53473089.=5000*maxrecords=5000*10694, maxnum=10694
maxnum constant int:=2;
i int :=1;
new_invoice_no invoice_article_size.invoice_no%TYPE;
r_row invoice_article_size%rowtype;
--get top 500 record and stored to cursor
cursor my_cursor is
select * from invoice_article_size where invoice_no not in (select invoice_no from invoice) and rownum <= topnum;
TYPE t_invoice_no IS TABLE OF varchar2(20) INDEX BY BINARY_INTEGER;
j int :=1;
t_new_invoice_no t_invoice_no;
BEGIN
WHILE i<=maxnum LOOP
open my_cursor;
loop
fetch my_cursor into r_row;
-- not found, exit from cursor
exit when my_cursor%notfound;
--get invoce_no
t_new_invoice_no(j) := r_row.invoice_no;
dbms_output.put_line(t_new_invoice_no(j));
--delete it one by one and commit
delete from invoice_article_size where invoice_no = t_new_invoice_no(j);
commit;
end loop;
close my_cursor;
dbms_output.put_line(i);
--execute another loop
i := i+1;
END LOOP;
END;
2> Hotfix:
add cascade delete for INVOICE_ARTICLE_SIZE, below is the SQL:(it also need long time to execute, it depends on number of the record in the size table.)
alter table INVOICE_ARTICLE_SIZE
add constraint FK_INOVICE_ARTICLE_SIZE foreign key (INVOICE_NO,ARTICLE_NO,ERP_ORDER_ID)
references INVOICE_ARTICLE (INVOICE_NO,ARTICLE_NO,ERP_ORDER_ID) on delete cascade;
BTW, may be this data patch can’t finish today,
But our system will continue to generate dirty data before you do Hotfix,
Hence I suggest, you’d better execute below sql to check whether there are dirty data exist:
select count(1) from invoice_article_size ins where (ins.invoice_no not in(select invoice_no from invoice));
If above select result>0, it means there are dirty data exist, you can use below delete statement to delete it directly.
delete from INVOICE_ARTICLE_SIZE i where (i.invoice_no not in(select invoice_no from invoice));
commit;
--13)待续。。。