Re: Does DBMS_SQL use any rollback segments?

Hi!

For the query constructed by DBMS_SQL
Oracle will use which optimizer mode?
Cost based or Rule based?

Thanks
------Original Message------
From: "Suzy Vordos" <lvordos_at_uswest.com> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: August 9, 2000 4:25:41 AM GMT
Subject: 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
> also send the HELP command for other information (like subscribing).

--
Author: Suzy Vordos
INET: lvordos_at_uswest.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).


______________________________________________