Undo segment is getting full due to a procedure execution

Hi all,

We have a stored procedure which transfers data between two tables once in a month. oracle version is 10g

Months data will be around 11Gb. But we have an UNDO segment of just 2Gb max.

When ever the procedure executed the undo segment will become full and the procedure was still executinag even after 6 hours. Because of which we had to stop it by force.

Please suggest me if there is a way of writing this procedure more efficiently.

Please find the script below.

--creates a table
sql_stmt := 'create table '||V_TABLE_NAME||' tablespace test as ( select * from test1 where 1=2) ';

execute immediate sql_stmt;

-- copies the data to the above created table

insert_query := 'insert into '||V_TABLE_NAME||' (select * from test2 where lastmodified >= (select trunc(add_months(sysdate,-4)) from dual) and lastmodified < (select trunc(add_months(sysdate,-3)) from dual))';

execute immediate insert_query;

-- deletes from the copied data from the source table

delete_query := 'delete from test2 where lastmodified >= (select trunc(add_months(sysdate,-4)) from dual) and lastmodified < (select trunc(add_months(sysdate,-3)) from dual)';

execute immediate delete_query;

commit;

Many thanks,
Vinay

Can any body pls help.


[QUOTE=vinayhc;27118]Hi all,

We have a stored procedure which transfers data between two tables once in a month. oracle version is 10g

Months data will be around 11Gb. But we have an UNDO segment of just 2Gb max.

When ever the procedure executed the undo segment will become full and the procedure was still executinag even after 6 hours. Because of which we had to stop it by force.

Please suggest me if there is a way of writing this procedure more efficiently.

Please find the script below.

--creates a table
sql_stmt := 'create table '||V_TABLE_NAME||' tablespace test as ( select * from test1 where 1=2) ';

execute immediate sql_stmt;

-- copies the data to the above created table

insert_query := 'insert into '||V_TABLE_NAME||' (select * from test2 where lastmodified >= (select trunc(add_months(sysdate,-4)) from dual) and lastmodified < (select trunc(add_months(sysdate,-3)) from dual))';

execute immediate insert_query;

-- deletes from the copied data from the source table

delete_query := 'delete from test2 where lastmodified >= (select trunc(add_months(sysdate,-4)) from dual) and lastmodified < (select trunc(add_months(sysdate,-3)) from dual)';

execute immediate delete_query;

commit;

Many thanks,
Vinay[/QUOTE]

Hi Vinay,
If you don't have a choice to increase the undo tablespace please try to break the insert operations into smilar chunks and try to commit. Otherwise you can go for the insert options with paralell hint and try. You are using stored procedure. If it's possible to run a script from OS level you can try out the copy command as well. But precisely speaking if the size of the undo is intact then you have to break your operation and commit it.

Thanks & Regards
Arunabha Datta

[QUOTE=vinayhc;27118]Hi all,

We have a stored procedure which transfers data between two tables once in a month. oracle version is 10g

Months data will be around 11Gb. But we have an UNDO segment of just 2Gb max.

When ever the procedure executed the undo segment will become full and the procedure was still executinag even after 6 hours. Because of which we had to stop it by force.

Please suggest me if there is a way of writing this procedure more efficiently.

Please find the script below.

--creates a table
sql_stmt := 'create table '||V_TABLE_NAME||' tablespace test as ( select * from test1 where 1=2) ';

execute immediate sql_stmt;

-- copies the data to the above created table

insert_query := 'insert into '||V_TABLE_NAME||' (select * from test2 where lastmodified >= (select trunc(add_months(sysdate,-4)) from dual) and lastmodified < (select trunc(add_months(sysdate,-3)) from dual))';

execute immediate insert_query;

-- deletes from the copied data from the source table

delete_query := 'delete from test2 where lastmodified >= (select trunc(add_months(sysdate,-4)) from dual) and lastmodified < (select trunc(add_months(sysdate,-3)) from dual)';

execute immediate delete_query;

commit;

Many thanks,
Vinay[/QUOTE]