|
Category :
Database
Resources -> Database Administration
DB Version
: Oracle 9i
OS Details :
Sun Solaris9
One of the most common
approaches by the developers when loading large volumes of data is to
break up the data in to ranges and commit every few hundreds or
thousands of rows. Something like this:
begin
i :=0
for sourcedata in (<select statement>)
loop
i := i+1;
if i > 1000 then
i := 1;
commit;
end;
--
-- do data processing and DML operations
--
end loop;
exception
when others then
rollback;
end;
/
The above method is adopted in
order to avoid space problems related to tables, indexes, temporary
areas or rollback. There are two problems with this approach : (1)
Frequent commit will cause performance problems and (2) Process
restart ability becomes a headache since you have to make sure that
the process when restarted will have to pick up from the previous
point of failure.
Starting 9i, we don't have to
worry about space issues. Oracle has introduced the concept of
resumable space management which when enabled allows for the process
to wait for a specified amount of time and will resume automatically
if the space issues have been resolved within that wait time.
Here's a simple example of using the feature:
(1) Make sure that your code
does not have has queries or operations against remote objects.
(2) To utilize this feature
the user needs the "resumable" system privilege.
(3) Prior to starting the data
loading process, you can set this for the session:
-- Set up timeout for 20
hours!
SQL> alter session enable resumable timeout 72000 name 'Project_name';
(4) You can setup an "after
suspend" trigger which can send you a page or an email notification in
the event of space issues.
(5) You can also use the
dbms_resumable package supplied by oracle to enable this feature from
within packages and procedures.
|
