Does DBMS_SQL use any rollback segments?

From: Eric Fang <eric_fang_at_yahoo.com>
Date: Tue, 8 Aug 2000 16:23:26 -0700 (PDT)
Message-Id: <10583.114131@fatcity.com>

Hi, gurus:

   I want to resize the table which has a long column. Since the copy command don't have storage clause(I also don't want to use import/export), then the only way I can do is to
1) rename the old table to the temp table rename table_name to table_name_temp;

2) recreate the table with new storage size v_statement := 'CREATE TABLE '||table_name|| ' STORAGE(INITIAL '||v_segment_size||' NEXT '||   v_segment_size||' PCTINCREASE 0 ) '||
 ' AS SELECT * FROM
'||rec_segments.segment_name||'_TEMP'
||' where 0 =1 ';
dbms_sql(cid,v_statement, dbms_sql.native); 3) insert data:
v_statement := 'INSERT INTO '||table_name||

        ' SELECT * from '||table_name||'_TEMP'; dbms_sql(cid,v_statement, dbms_sql.native);

My question is :
in step 3, if the table table_name_temp is very big, then does the dbms_sql command use any rollback segments? If it does, then we need specify a big rollback segment for this statement.

Did anyone have the similar problem before? What's the best way to resize a table by using PL/SQL.

Thanks in advance.

Eric Fang

---

Do You Yahoo!?
Kick off your party with Yahoo! Invites. Received on Tue Aug 08 2000 - 18:23:26 CDT