Sarath,
Go locally managed tablespace and forget about extent management. It's just not worth it.
Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
-----Original Message-----
From: sarath kumar [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 02, 2003 4:00 PM
To: Multiple recipients of list ORACLE-L
Subject: dbms_sql from stored procedure
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
