dbms_sql from stored procedure

From: sarath kumar <sarath_kumar0_at_yahoo.com>
Date: Thu, 02 Jan 2003 13:00:04 -0800
Message-ID: <F001.0052611F.20030102130004@fatcity.com>

dear list,

i am inserting 90 million rows into a table using a stored procedure. i have a rough estimate of space 16270M. i dont want to add datafiles to this tablespace since i dont do any more inserts after this.

i created an intial of 250M and next of 250M after grabbing 62 extents it could no longer grab space of 250M size hence i have to change the next extent size to 50M after 73rd extent it could no longer grab 50M size hence i have to change the next extent to 10M after 94 extents all inserts are done. is there a way to change the next extent value through the same procedure. i tried this but not working

declare
cur_x number;
begin
for c1 in (select ...)loop
for c2 in (select ....)loop
  insert into x
   select * from y where col1=c1.sdate;
end loop;
commit;
select count(*) into cur_x from user_segments where   segment_name ='X';
if cur_x = 62 then
cursor1:dbms_sql.open_cursor;
dbms_sql.parse(cursor1,'Alter table x storage next(50M)',dbms_sql.native);
rows_processed:=dbms_sql.execute(cursor1); dbms_sql.close_cursor(cursor1);
end if;
if cur_x = 73 then
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1,'alter table x storage next
(10M)', dbms_sql.native);

rows_processed:=dbms_sql.execute(cursor1); dbms_sql.close_cursor(cursor1);
end if;
end loop;
end;

---

Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: sarath kumar
  INET: sarath_kumar0_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Thu Jan 02 2003 - 15:00:04 CST