Re: Re-organizing tables containing LONG datatypes?

In article <stauffer-0112940955310001_at_stauffer.swarthmore.edu>, stauffer_at_cc.swarthmore.edu (Glenn Stauffer) writes:
|>I am re-organizing tables in an Oracle database to compress table extents,
|>move them to new tablespaces, and update storage parameters to properly
|>reflect the nature of the table data and usage.
|>
|>For most tables, I do this using a statement such as:
|>
|>CREATE TABLE NAME_TEMP
|> TABLESPACE NEW_TABLESPACE
|> PCTFREE 5
|> PCTUSED 90
|> STORAGE (INITIAL NEW_VAL NEXT NEW_VAL PCTINCREASE NEW_VAL)
|> AS SELECT * FROM NAME;
|>
|>The concepts manual tells me that a LONG field cannot be used in a CREATE
|>AS SELECT statement or an INSERT INTO SELECT statement.
|>
|>I expect that I will need to do the following to re-organize this table:
|> - unload the table data to a flat-file
|> - create the new table using the proper storage parameters and new tablespace
|> - load the data into the new table using sqlloader
|> - drop the old table
|> - rename the new table
|>
|>Is there an easier way to accomplish this task?
|>
|>Thanks for any suggestions.
|>Glenn Stauffer
|>DBA
|>Swarthmore College
|>

 In article <stauffer-0112940955310001_at_stauffer.swarthmore.edu>, stauffer_at_cc.swarthmore.edu (Glenn Stauffer) writes:
|>I am re-organizing tables in an Oracle database to compress table extents,
|>move them to new tablespaces, and update storage parameters to properly
|>reflect the nature of the table data and usage.
|>
|>For most tables, I do this using a statement such as:
|>
|>CREATE TABLE NAME_TEMP
|> TABLESPACE NEW_TABLESPACE
|> PCTFREE 5
|> PCTUSED 90
|> STORAGE (INITIAL NEW_VAL NEXT NEW_VAL PCTINCREASE NEW_VAL)
|> AS SELECT * FROM NAME;
|>
|>The concepts manual tells me that a LONG field cannot be used in a CREATE
|>AS SELECT statement or an INSERT INTO SELECT statement.
|>
|>I expect that I will need to do the following to re-organize this table:
|> - unload the table data to a flat-file
|> - create the new table using the proper storage parameters and new tablespace
|> - load the data into the new table using sqlloader
|> - drop the old table
|> - rename the new table
|>
|>Is there an easier way to accomplish this task?
|>
|>Thanks for any suggestions.
|>Glenn Stauffer
|>DBA
|>Swarthmore College
|>

Why not use EXPORT with COMPRESS=Y? Export the table, drop it, and import it.

If you don't want to use export for some reason, check out the COPY command in the SQLPLUS manual (not the SQL Language manual). You can pretent you are copying the table to a different tablespace but really loop back into the same database via a SQLNet connect string.

Jen

-- 
Jennifer Corliss is on an InterGalactic cruise...       |The opinions expressed 
                                                        |above may not be those
   ...in her office                                     |of my employer.