Re: Index tablespace reorg

Dana Stockler wrote:
>
> Unknown wrote:
> >
> > Greetings all -
> >
> > I have a slew of indexes in a tablespace that I wish to reorganize
> > (and increase in size). I don't want to use ALTER TABLESPACE ADD DATAFILE
> > as I would rather just make a new, single file to hold all the information.
> >
> > Anyway, my problem is that the related data files are in various other
> > tablespaces and are not in need of reorganization. Is there an
> > easy way to generate the needed CREATE INDEX statements to recreate
> > the indexes after I drop/recreate the tablespace ? I am unaware of any way
> > to just export the index segments in that tablespace and re-import
> > them to the new tablespace.
> >
> > The only way I can think of doing this is via the DBA_IND_COLUMNS
> > and DBA_INDEX views and creating some SQL commands from that.
> > Am I totally confused ?
> >
> > Thanks in advance for any assistance.
> >
> > Brett Cunningham
> > zgeist_at_ee.net
>
> The alter index command can be used to rebuild an index, optionally
> in another tablesapce. The syntax is (if I remember correctly):
>
> ALTER INDEX REBUILD TABLESPACE <NewTablespaceName>
>
> This should have the effect you're after.
>
> -Dana

Correct me if I am wrong, but are you just looking for something that will give you the create index sql statements? If you are, you can get this from Export/Import. (his is works especially well if you made all of your indexes with one user, but it is not necessary.

Create an export file. If you do not need any data use the row=n switch. With this export file use the import command to create an index file. It would look something like

        import user/password file=export.dmp full=y indexfile=index.txt

This will create a text file of all of your create index commands, as well as your tables, but they will be remmed out.

HTH //Karl

-- 
====================================================
Karl M. Nordquist		karln_at_brightwood.com
Application Development
Bright Wood Corporation
Madras, Oregon
====================================================