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

From: ericvdspoel <member_at_dbforums.com>
Date: 11 Jan 2002 07:12:36 -0500
Message-ID: <3c3ed6b4$1_at_usenetgateway.com>


Lowell Paden wrote:
> 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.comnews:6407a841.0108240028.fec8fde_at_p-
> osting.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

Lowell,

Thanks for your script it runs only one minute in a SAP environment. I have changed the script a little to generate a readable and runnable output script. Thans, Eric

rem GEN_SAP_rebuild_ind_in_wrong_TS.sql

prompt prompt -- Checking for proper index tablespaces ...

column line1 newline

SET PAGESIZE 0 SET HEADING OFF SET ECHO OFF SET FEEDBACK OFF SET VERIFY OFF spool do_SAP_rebuild_ind_in_wrong_TS.sql

SELECT 'SPOOL '||name||'_DO_SAP_rebuild_ind_in_wrong_TS.LOG ;' from sys.v_$database;

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 NOLOGGING ;' 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) = 'SAPR3' 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 /

SELECT 'SPOOL OFF' from dual /

spool off /

exit

--
Eric van der Spoel

Posted via dBforums
http://dbforums.com
Received on Fri Jan 11 2002 - 13:12:36 CET

Original text of this message