Re: Script: is the index in the TS it should be?

Here is an adaptation of one I use.
It creates statements to rebuild the indexes in the proper tablespace. I hope it runs faster than yours..

prompt
prompt -- Checking for proper index tablespaces ... define owner=yourschema
column line1 newline
select '-- tablespace: '||s.tablespace_name||', index: '||s.segment_name||', size: '||s.bytes/1024||'k, table: '||t.table_name||', table tablespace: '||t.tablespace_name line1,
 'alter index '||s.owner||'.'||s.segment_name||' rebuild tablespace '||substr(t.tablespace_name,1,length(t.tablespace_name)-1)||'I ;' from dba_segments s, dba_indexes i, dba_tables t where s.segment_name=i.index_name
and i.table_name=t.table_name
and s.segment_type='INDEX'
and upper(t.owner)=upper('&owner')
and i.tablespace_name !=
substr(t.tablespace_name,1,length(t.tablespace_name)-1)||'I' order by t.tablespace_name, t.table_name, s.segment_name /

"Eric van der Spoel" <spoele_at_commit.nl> wrote in message news:6407a841.0108240028.fec8fde_at_posting.google.com...
> db: Oracle 8/8i, a SAP-environment
>
>
> I got some new db's to administrate. Normally the indexes are in the
> same
> tablespace as the tables, except for the last letter ( z.b. a table is
> in the TS PSAPBTABD and the indexes on this table are in PSAPBTABI).
> However this is not the case in my new db's.
> Does anyone have a SQL-script that can check if the indexes on a table
> are
> in the indextablespace the should be?
> If have written a sql-sript myself wich runs for hours and with no
> result. So
> thats why this question.
>
> Please make a CC to spoele_at_commit.nl
>
> Thanks,
> Eric van der Spoel
>
> Script I use:
> rem Run by user SYSTEM or SYS
> rem
>
> set pagesize 200
>
> Column index_name Format A15
> Column tablespace_name Format A15
> Column table_name Format A15
>
> spool SAP_indexTS_ne_dataTS.log
>
> SELECT a.index_name ,
> a.tablespace_name,
> a.table_name ,
> b.tablespace_name
> FROM
> DBA_INDEXES a,
> DBA_TABLES b
> WHERE a.owner = 'SAPR3'
> AND a.table_name = b.table_name
> AND (SUBSTR(a.tablespace_name,1,length(a.tablespace_name)-1) <>
> SUBSTR(b.tablespace_name,1,length(b.tablespace_name)-1))
> ORDER BY a.index_name;
>
> spool off
>
> exit
Received on Fri Aug 24 2001 - 15:01:51 CEST