Oracle-L: RE: dbms_sql from stored procedure

From: Koivu, Lisa <Lisa.Koivu_at_efairfield.com>
Date: Thu, 02 Jan 2003 13:48:56 -0800
Message-ID: <F001.00526260.20030102134856@fatcity.com>

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-----
Sent: Thursday, January 02, 2003 4:00 PM To: Multiple recipients of list ORACLE-L

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).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Koivu, Lisa
INET: Lisa.Koivu_at_efairfield.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------