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:
- Rebuild indexes on a Table in Oracle
- Analyze indexes and table (estimate statistics)
- 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:
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
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..
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