Shell Programming and Scripting

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;

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;
/

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