Re: Table Fermentation

<rc_at_no.spam> wrote in message news:3fd0b683.879673463_at_news.demon.co.uk...
> On Fri, 05 Dec 2003 08:25:54 -0800, Daniel Morgan
> <damorgan_at_x.washington.edu> wrote:
>
> >no-spam wrote:
> >
> >> Hi
> >>
> >> I have a table space with about 63 tables in it. Most of the tables
> >> are less than 100k rows.
> >>
> >> 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
> >>
> >> I do not want to use the compress=y option
> >>
> >> Are there additional command I have to issue re the table_space ?
> >>
> >> Thanks
> >
> >Is this: Guess what version I'm using day?
> >
> >And what do you mean by "not wanting to use" an option? Do you want us
> >to guess why too?
>
> Daniel
>
> 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 !!!!!!!!!!!!!!!!!!!
>
> 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.

You can export/import as few or as many tables as you like at a time the TABLES parameter is what you are after.

eg exp user/pass_at_db file=tables.dmp full=n tables=(tab1,tab2,tab3) compress=n

I do have a couple of questions though

1. Why do you wish to reduce the number of extents?
2. If you *do* wish to reduce the number of extents you will presumably be recreating the table first and then importing with ignore=y? If this is so then compress=y would be irrelevant since the create table statement in the export file would not complete successfully (table already exists).

Usually your best bet if you are concerned about fragmentation is to use locally managed tablespaces with a uniform extent size and max extents unlimited.

I smiled at the table fermentation typo :)

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************



--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************