Re: # of Indexes

       Hi,
       Another way to move indexes to a different tablespace.
       1) From dba_indexes/dba_objects get a script  which has
       something like this:---
            "Alter index <index_name> rebuild tablespaces <ts_name> ;
       "
       select 'alter index' || index_name || ' rebuild tablespace
       <ts_name< ;'
       from dba_indexes where owner='<owner>' ;

       spool the above and run .
       This should rebuild all your indexes into a different
       tablespace.

            Hope this helps.

                           shreepad.




       From: ORACLE-L_at_fatcity.com AT INTERNET on 06/27/2000 11:19 AM

       To:   ORACLE-L_at_fatcity.com AT INTERNET_at_CCMAIL
       cc:    (bcc: Shreepad Vaidya/IN/ALLTELCORP)

       Subject:  Re: # of Indexes


       The easy way to do this is to spool the output of this
       statement;
       select 'alter index '||owner||'.'||index_name||' rebuild
       tablespace
       YOUR_TS_NAME;' from all_indexes;

       Then run the spooled file and this will rebuild all of your
       indexes in the
       the required tablespace.

       I believe you have to be in Oracle8 or 8i.

       Regards,
       Ruth G.
       ----- Original Message -----
       To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
       Sent: Tuesday, June 27, 2000 3:04 AM




       > Hi All,

       >    I have to move all indexes of a schema from one Tablespace

       to another


       > Tablespace (currently Tables as well as Indexes are in the

       same TS ;-((.


       >

       >

       >    But when I query DBA_OBJECTS/DBA_INDEXES for that schema

       the no of


       > indexes come to be 179. When I export the objects of that

       schema and


       > create the indexfile (by importing ) then no of Indexes is

       coming to be


       > 89 only. Could someone point out that why is this difference?

       Is this


       > because of System created indexes for unique/primery keys ?

       If yes then


       > is there any way out to move those indexes to diff Tablespace

       ?


       >

       > Thanks for help....

       > Rajesh

       > --

       > Author: Rajesh Dayal

       >   INET: Rajesh_at_ohitelecom.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: Ruth Gramolini
         INET: rgramolini_at_tax.state.vt.us

       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