Re: Does DBMS_SQL use any rollback segments?

Whether or not you using the SQL command line or DBMS_SQL will not affect whether rollback segments are used.

I can't find any information in the documentation regarding CTAS and the use of rollback, but it's probably there somewhere.

Doing a CTAS on a table, and monitoring v$transaction you can see that CTAS does use a minimal amount of rollback for the data dictionary entry.

Jared

On Tue, 8 Aug 2000, Eric Fang wrote:

> 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.
> http://invites.yahoo.com/
> --
> Author: Eric Fang
> INET: eric_fang_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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).
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon
jkstill_at_bcbso.com - Work - preferred address Received on Fri Aug 11 2000 - 10:05:19 CDT