Re: Does DBMS_SQL use any rollback segments?

Hmmm, I recently did this same exercise using copy, and just created the new table prior to copy.

    create table new_table <cols, storage, tablespace, etc>

    set arraysize 200
    set copycommit 2000
    set long 99999  

    copy <...> insert new_table <...> using select from current_table

    rename current_table to old_table
    rename new_table to current_table

You probably know that copy will truncate LONG > 32K. To verify this didn't happen, I analyzed current_table and new_table, and if avg_row_len matched for both tables, I concluded the copy was OK.

You might also do exp rows=N then imp show=y. Then you'll have the complete table definition, column defaults, indexes, grants, etc, just to be certain something important to the table isn't overlooked.

Both copy and dbms_sql will use rollback, but with copy you can set copycommit #.

Suzy

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
Received on Tue Aug 08 2000 - 22:02:08 CDT