Analyze the indexes and rebuild them


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Analyze the indexes and rebuild them
# 1  
Old 07-17-2006
Analyze the indexes and rebuild them

Hello UNIX and Oracle Gurus,

After doing an intensive search from different websites, the UNIX forum I am posting this message seeking help..

I am trying to accomplish the following tasks through the shell script:

1. Rebuild indexes on a Table in Oracle
2. Analyze indexes and table (estimate statistics)
3. Confirm that indexes are now in a valid state.

I know bits and pieces of the code...But I am having a hard time putting them together in one shell script to do all the activities...

Any help will be highly appreciated.

Thank you in advance,
Madhu

-------------------------------------------------------------------------
For step 1 -- rebuilding the indexes, I can use something like this:

Code:
SET LINESIZE 132 
SET PAGESIZE 0 
SET FEEDBACK OFF 

SPOOL IDXREBUILD.SQL 

SELECT 'ALTER INDEX '||OWNER||'.'||SEGMENT_NAME||' REBUILD UNRECOVERABLE TABLESPACE '||TABLESPACE_NAME|| 
' STORAGE ( '||CHR(10)||' INITIAL '||BYTES||' NEXT '||BYTES / 4||' PCTINCREASE '||PCT_INCREASE|| 
' MINEXTENTS 1 MAXEXTENTS 100) PCTFREE 0 ;' FROM DBA_SEGMENTS 
WHERE OWNER=UPPER('&SCHEMAOWNER') AND SEGMENT_TYPE='INDEX' ;

SPOOL OFF;

SET PAGESIZE 45;
SET FEEDBACK ON;

For step 2 -- Analyze indexes and table

Code:
create or replace procedure
        p_analyze_table_indexes(a_table_name       IN
user_indexes.TABLE_NAME%type)
        as
                v_cursorid              integer;
                status                  integer;
                cursor c_user_indexes is
                        select TABLE_NAME,INDEX_NAME
                        from user_indexes
                        where status='VALID'
                        and TABLE_NAME=upper(a_table_name);
                v_user_indexes  c_user_indexes%rowtype;
                begin
                        open c_user_indexes;
                                v_cursorid:=dbms_sql.open_cursor;
                        fetch c_user_indexes into v_user_indexes;
                        while ( c_user_indexes%found ) loop
                                dbms_sql.parse(v_cursorid,
                                'analyze index
'||v_user_indexes.INDEX_NAME||' ESTIMATE STATISTICS SAMPLE 40 PERCENT
                                        ',dbms_sql.native);
                                status:=dbms_sql.execute(v_cursorid);
                                          fetch c_user_indexes into
v_user_indexes;
                        end loop;
                        dbms_sql.parse(v_cursorid,
                        'analyze table '||v_user_indexes.TABLE_NAME||'
ESTIMATE STATISTICS SAMPLE 40 PERCENT
                                        ',dbms_sql.native);
                        status:=dbms_sql.execute(v_cursorid);
                close c_user_indexes;
                dbms_sql.close_cursor(v_cursorid);
                        exception
                        when others then
                                dbms_output.put_line('Error...... ');
                                dbms_sql.close_cursor(v_cursorid);
                                raise;
        end p_analyze_table_indexes;
/

For step 3 -- I believe this is the query that fetches whether a index is valid or not..

Code:
SELECT 
	C.TABLE_NAME,
             C.INDEX_NAME, 
	C.COLUMN_NAME

FROM   
USER_INDEXES I, 
USER_IND_COLUMNS C

WHERE  I.STATUS='VALID'
AND    I.INDEX_NAME = C.INDEX_NAME
AND    I.TABLE_NAME = UPPER('ACXIOM_PROSPECT_A')

ORDER BY C.INDEX_NAME, C.COLUMN_POSITION

Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Adding indexes with PERL

Hello. I'm trying to self learn Perl and am stuck. I have a data.csv file that contains the following: 5,10,15,20,15,30 1,2,3,4,5 3,10 11 I'm trying to get Perl to take the indexes and add them all together to get 134. It says I need to use split and invoke the file via <> (built-in... (2 Replies)
Discussion started by: Eric7giants
2 Replies

2. Shell Programming and Scripting

I need to find out disk usage for particular indexes for different hosts

Need disk utilisation script for particular indexes on few hosts (3 Replies)
Discussion started by: Krish5v
3 Replies

3. Shell Programming and Scripting

open files with multiple indexes

Hi, I want to work with multiple files which all contain 2 numbers. I tried to make a nested for loop but for some reason it doesn't recognize the $j as a number. The output is cannot open file `175-T-pvalue.xls'. How do I make sure that it takes the numbers from the inner loop as $j? ... (4 Replies)
Discussion started by: linseyr
4 Replies

4. Shell Programming and Scripting

remote/automated OS X folder label indexes triggered from PHP

I have a shell script that updates OS X color label indexes of folders/files as specified in an XML file. This works from terminal, but not when I trigger it from Apache/PHP. The server is not public, so not too worried about security. I've tried to allow access in sudoers, but no dice. I could use... (1 Reply)
Discussion started by: bradlecat
1 Replies

5. BSD

rebuild the portsystem

hi Howto rebuild the whole portsystem on freeBSD? THX (3 Replies)
Discussion started by: ccc
3 Replies

6. Shell Programming and Scripting

matrix indexes

I wanted to use matrixs in awk and got some problem, here is some of the script code, from the BEGIN tag: row_char="a";row_char="b";row_char="c";row_char="d";row_char="e"$ row_char="h";row_char="i";row_char="j";row_char="k"; from the proccess passage: sentence,1]=1; diffrence=4; i=7;... (2 Replies)
Discussion started by: tal
2 Replies

7. UNIX for Dummies Questions & Answers

using sed with indexes

Hi people, Is this possible and if so any tips are very welcome. Im trying to do the following: this is what I have: 800__1__ this is what I want: 8000010 12345678 Im... (1 Reply)
Discussion started by: seaten
1 Replies

8. UNIX for Advanced & Expert Users

best way to rebuild a kernel

i really have an issue with the otherwise outstading FreeBSD Handbook when it comes to kernel building. information on the proper steps to take is really confusing. i think that chapters 9 and 21 need to be combined to give a very concise format on how best to deal with kernel building/rebuidling... (1 Reply)
Discussion started by: xyyz
1 Replies
Login or Register to Ask a Question

Featured Tech Videos