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
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)
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)
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)
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)
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)
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)