Re: Table Fermentation

Comments in-line

rc_at_no.spam wrote:

> it is Oracle 8.1.7... but as far as I know the import and export work
> the same for any version of Oracle .. so the version if does not come
> into my question !!!!!!!!!!!!!!!!!!!

You didn't ask about import export which, you are correct work the same way. You asked about alternatives. Alternatives depend on the version.

> The compress=y on export will cause the table to be created in one
> extent during import, which will cause probelm for me becasue the
> entire table will not fit into one data file within the table space
>
> I thought this was a simple enough question as to how to reduce the
> number of extents used by tables.

Once again refer to what you originally asked. You asked:

"If I wanted to defrag the tablespace, can I export and import one table at a time or do I have to export all the tables in one go and import back in again"

Which led me to consider that there might be a far simpler, though version dependent solution.

So now to answering your question:

You can export and import single tables or lists of tables. The syntax is:

exp userid=username/password file=filename.dmp tables=(comma_delimited_list_of_tables)

But for what you are doing it would be really easy to just create a new tablespace and use ALTER TABLE MOVE. Certainly a lot faster with a bunch of small tables.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)